Database performance tuning and optimisation is the art of making your database respond faster and use fewer resources while handling the same workload. For the DBS-C01 exam, you need to understand how to identify slow queries, choose effective indexes, configure database parameters, and balance CPU, memory, and storage — because Amazon RDS and Aurora databases are billed by the hour, and a poorly tuned database wastes both time and money.
Jump to a section
A simple way to picture Database Performance Tuning and Optimization
Have you ever waited an age for your food at a busy restaurant, only to see it come out cold and wrong? What went wrong behind the kitchen doors? That slowdown is exactly what happens when a database is poorly tuned, and the fix is the same: you need to analyse and optimise how every part of the kitchen works together.
Think of the database as a restaurant kitchen. The chef (the database engine) can only cook meals as fast as the ingredients arrive. The pantry (the storage system) holds all the raw data. The menu (the queries) tells the chef what to prepare. The waiters (the application requests) bring in orders. If the menu asks for ingredients that are scattered all over the pantry, the chef spends ages running back and forth. That is a slow query. If the pantry has no labels or shelves, the chef cannot find anything quickly. That is a missing index. If the chef has too few stoves (CPU cores) or a tiny counter (memory), they cannot cook multiple orders at once. That is a resource bottleneck.
Performance tuning means checking each part: Are the queries written to grab only the ingredients they need? Are the shelves (indexes) organised so the chef can grab the right ingredient in a single step? Is the kitchen equipped with enough stoves and counter space (CPU, memory, I/O) for the rush hour? When you tune a database, you are essentially reorganising the kitchen, rewriting the menu, and rescheduling the prep work so every order comes out hot and fast — even when the restaurant is packed.
Database performance tuning and optimisation is the process of making a database run faster and more efficiently. The core goal is to reduce the time it takes to execute queries and to use the least amount of computing resources (CPU, memory, disk I/O) possible. A tuned database handles more users, processes more transactions, and costs less to run in the cloud.
Let's start with the most important piece: query tuning. A 'query' is a command you send to the database to ask for or change data, written in SQL (Structured Query Language). Query tuning means rewriting that command so the database can find the data more quickly. For example, if you ask for 'all orders from last year', a badly written query might check every single row of the orders table one by one — that is called a 'full table scan'. A tuned query would use a 'WHERE' clause to filter on the date column, and if you have an index (a sorted list) on that date column, the database can jump straight to the relevant rows. A common tuning technique is to avoid 'SELECT *' — asking for all columns when you only need three of them forces the database to fetch unnecessary data, which wastes I/O and memory.
Next is indexing. An 'index' is a data structure that acts like the index in the back of a textbook: it tells the database exactly where to find a row based on a column's value. Without an index, the database has to scan every row (a full table scan). With an index, it can jump directly to the matching rows. But indexes are not free — they take up storage space and slow down write operations (INSERT, UPDATE, DELETE) because the database has to update the index every time data changes. Performance tuning means choosing the right indexes: you want indexes on columns used in WHERE clauses and JOIN conditions, but you do not want too many indexes on tables that are heavily written to. A 'composite index' is an index on multiple columns, and the order of columns in the index matters — put the most selective column first. The exam often tests 'covering indexes', which are indexes that contain all the columns needed by a query, so the database never has to touch the actual table at all — that is called an 'index-only scan'.
Then there are 'parameter groups'. In Amazon RDS (Relational Database Service), a parameter group is a set of configuration settings that control how the database engine behaves. You can think of it as a settings file. Parameters include things like 'max_connections' (the maximum number of simultaneous connections), 'shared_buffers' (memory allocated for caching data), and 'work_mem' (memory for sorting operations). Tuning these parameters for your workload is critical. For example, if you set 'shared_buffers' too low, the database will hit the disk more often, making queries slow. If you set 'max_connections' too high, the database might run out of memory trying to serve all those connections. The exam expects you to know that modifying a parameter group often requires a 'reboot' of the database instance to take effect, and that you can use 'parameter group families' to manage different engine versions.
Resource optimisation covers CPU, memory, and storage. 'CPU utilisation' is the percentage of time the processor spends working. High CPU (consistently above 80%) means the database instance is under-powered — you might need to 'scale up' to a larger instance type with more CPU cores. 'Memory' is used for caching (like the buffer cache) and sorting operations. If the database runs out of memory, it starts swapping to disk, which kills performance. 'Storage I/O' measures how fast data can be read from or written to disk. 'Provisioned IOPS' (Input/Output Operations Per Second) gives you a guaranteed I/O speed, while 'gp2' and 'gp3' (general-purpose SSD) have burstable performance. Monitoring these metrics through Amazon CloudWatch helps you decide whether to resize the instance, add read replicas, or switch to faster storage.
Finally, 'connection pooling' is a technique where the database reuses existing connections instead of opening a new one for every user request. Opening a new connection is expensive (it takes CPU time and memory). Tools like RDS Proxy or PgBouncer sit between the application and the database, holding a pool of established connections. When the app needs to talk to the database, it borrows one from the pool instead of creating a new one. This reduces CPU overhead and the risk of running out of connections.
All of these pieces — query tuning, indexing, parameter groups, and resource optimisation — interact. For example, a well-indexed query reduces CPU usage because the database does less work. That means you might not need to scale up to a larger instance. The DBS-C01 exam tests your ability to diagnose symptoms (like high CPU or slow queries) and choose the right fix from these tools.
Identify the Symptom
Check CloudWatch metrics (CPU, Read/Write IOPS, DatabaseConnections) and slow query logs to understand what is going wrong. Without data, you cannot know whether the problem is a query, an index, or a resource limit.
Analyse the Slow Query with EXPLAIN
Run the EXPLAIN command on the slow query. Look for 'Seq Scan' (full table scan) which means the database is reading every row. If you see 'Index Scan' or 'Index Only Scan', the query is using an index. This tells you exactly where to focus your tuning.
Create or Modify an Index
If the EXPLAIN shows a sequential scan, create an index on the columns used in the WHERE clause and JOIN conditions. For queries with an ORDER BY, include that column in a composite index to avoid a separate sort step.
Tune the Parameter Group
Modify the RDS parameter group that is associated with your instance. Increase 'shared_buffers' (for PostgreSQL) or 'innodb_buffer_pool_size' (for MySQL) to cache more data in memory. Adjust 'max_connections' to prevent memory exhaustion from too many concurrent sessions.
Scale Resources or Add Read Replicas
If CPU or memory remains high after tuning queries and indexes, consider scaling up to a larger instance type. If the workload is read-heavy, add one or more read replicas in a different Availability Zone to distribute read traffic.
Enable Performance Insights and Monitoring
Turn on Performance Insights in RDS to continuously monitor database load. Set up CloudWatch alarms to notify you when CPU or IOPS exceed thresholds. Regularly review slow query logs and index usage statistics to catch issues early.
Imagine you are a junior database administrator at an e-commerce company called 'ShopFast'. The company's flagship product catalogue runs on an Amazon RDS for PostgreSQL database. One Monday morning, users start complaining that the search page takes over 10 seconds to load. The CEO is watching the site metrics, and every second of delay costs sales.
Your first step is to open Amazon CloudWatch and look at the database metrics. You see that CPU utilisation is at 95%, 'Read IOPS' is maxed out at the provisioned limit, and 'DatabaseConnections' is close to the maximum allowed by the instance. You also check the 'Slow query log' (which logs queries that take longer than a configured threshold, e.g., 5 seconds). You find one query repeated thousands of times:
SELECT * FROM products WHERE category = 'Electronics' ORDER BY price DESC;
This query fetches every column (SELECT *) and scans the entire 'products' table because there is no index on the 'category' column. The full table scan causes high read I/O and CPU.
Your next step is to fix this. You run EXPLAIN on the query to confirm the plan: it shows a 'Seq Scan' (sequential scan) on the products table. You create a composite index on (category, price) so the database can find the matching category rows and retrieve them already sorted by price. You also rewrite the query to avoid SELECT *: you list only the columns the application actually needs, like product_id, name, and price.
After creating the index, the query's execution time drops from 8 seconds to 50 milliseconds. CPU utilisation falls to 40%. But you are not done yet. You check the parameter group. The 'max_connections' parameter is set to 500, but the instance only has 4 GB of memory — each connection uses about 10 MB, so 500 connections would use 5 GB, causing memory pressure. You reduce 'max_connections' to 200 and enable RDS Proxy (connection pooling) so the application can reuse connections without hitting the limit.
Finally, you set up a 'Performance Insights' dashboard to monitor query latency and wait events. You schedule a weekly review of slow query logs and index usage statistics. From this point on, the catalogue page loads in under half a second, the CEO is happy, and you have learned that the most impactful tuning step is often creating the right index — but only after confirming the bottleneck through metrics.
The DBS-C01 exam loves to test your ability to diagnose and fix performance problems quickly. You will see scenario-based questions where a database is slow, and you must pick the correct action from four options. The traps are carefully designed.
One common question type gives you a CloudWatch metric and asks what to do. For example: 'CPU utilisation is at 90% and Read IOPS is at the provisioned limit. What should you do?' The correct answer is often 'Add a read replica' (for read-heavy workloads) or 'Scale up the instance type' (for CPU-bound workloads). A trap answer might be 'Add an index' — but adding an index does not directly reduce CPU or IOPS; it might even increase write overhead. Another trap is 'Increase provisioned IOPS' without checking if the bottleneck is actually I/O or CPU.
Another question type shows a slow query and asks you to identify the fix. They will present an EXPLAIN plan showing a 'Seq Scan' and ask what to do. The correct answer is to 'Create an index on the column used in the WHERE clause'. A trap answer might be 'Increase shared_buffers' — which might help a little but does not remove the full table scan.
Key concepts the exam tests repeatedly:
The difference between 'full table scan' (slow, reads all rows) and 'index scan' (fast, reads only matching rows).
The 'EXPLAIN' command and what each operation means (Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan).
Parameter groups: which parameters are static (require a reboot) versus dynamic (take effect immediately). For example, 'shared_buffers' in PostgreSQL is static; 'work_mem' is dynamic.
Read replicas: they reduce load on the primary by serving read traffic, but they do not improve write performance.
Multi-AZ deployments: they provide high availability (automatic failover) but do not improve performance unless you use Multi-AZ with read replicas.
Connection pooling: RDS Proxy is the AWS managed service to reduce connection overhead.
The exam also tests 'resource optimisation' scenarios where the database is running with low memory. They will describe swap usage and ask what to do. The correct answer is usually 'Increase the instance size' or 'Modify a parameter like shared_buffers to use more memory'. A trap is 'Add more storage' — storage size does not increase memory.
Finally, memorise this pattern: when a query is slow and the EXPLAIN plan shows a sequential scan on a large table, the fix is an index (assuming the query is selective enough). When CPU is high and the query is already indexed, the fix is scaling up or adding a read replica. When I/O is the bottleneck, consider increasing provisioned IOPS or switching to faster storage (e.g., io1 or io2 Block Express).
A full table scan (sequential scan) reads every row in a table; adding an index allows the database to jump directly to matching rows, dramatically reducing query time.
Indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations because the index must be updated with every data change.
A composite index on multiple columns should have the most selective column first to maximise its effectiveness in filtering rows.
Parameter groups control engine-level settings like shared_buffers and max_connections; some parameters require a database reboot to take effect (static), while others take effect immediately (dynamic).
High CPU utilisation is not always a hardware problem — poorly tuned queries, missing indexes, or too many connections are common causes that can be fixed without scaling up.
RDS Proxy (connection pooling) reduces the overhead of opening many database connections, which decreases CPU usage and prevents running out of connections.
Read replicas improve read performance by offloading SELECT queries to a separate instance, but they do not help with write performance.
Multi-AZ deployments provide high availability and automatic failover, not performance scaling.
The EXPLAIN command shows the query execution plan; looking for 'Seq Scan' indicates a full table scan that needs an index.
Performance Insights is a tool in RDS that visualises database load and helps you identify which queries are consuming the most resources.
These come up on the exam all the time. Here's how to tell them apart.
Index Scan
Uses an index to locate rows directly, skipping unrelated data.
Fast for selective queries that return a small percentage of rows.
Slower for write operations because the index must be maintained.
Full Table Scan
Reads every row in the table one by one.
Slow on large tables, even if only a few rows match.
No index maintenance overhead, so writes are unaffected.
Static Parameter
Requires a database reboot to apply the change.
Example: shared_buffers in PostgreSQL.
Typically controls memory allocation or other fundamental engine settings.
Dynamic Parameter
Takes effect immediately without a reboot.
Example: work_mem in PostgreSQL.
Controls session-level settings that do not require restarting the engine.
Read Replica
A separate database instance that only serves read queries.
Improves read performance by offloading the primary.
Can be promoted to a primary but is not for automatic failover.
Multi-AZ Deployment
A standby instance in a different Availability Zone for failover.
Does not improve query performance — it is for high availability.
Automatic failover if the primary instance fails.
General Purpose SSD (gp2/gp3)
Baseline performance with burst credits (gp2) or consistent baseline (gp3).
Best for workloads with moderate, bursty I/O demands.
Cheaper per GB compared to provisioned IOPS.
Provisioned IOPS (io1/io2)
Guaranteed, consistent IOPS regardless of disk size.
Ideal for high-performance, latency-sensitive workloads.
More expensive per GB but offers higher maximum throughput.
Mistake
Adding more indexes always speeds up queries.
Correct
Indexes speed up read queries but slow down write operations (INSERT, UPDATE, DELETE) because each index must be updated. Too many indexes on a write-heavy table can hurt overall performance.
Beginners assume indexes are always beneficial because they see query speed gains from a single index, but they overlook the cost of maintaining many indexes on high-write tables.
Mistake
If CPU is high, the only fix is to increase the instance size.
Correct
High CPU can also be caused by poorly written queries, lack of indexes, or too many connections. Fixing the query or adding indexes often reduces CPU usage without scaling up, saving money.
People think of hardware as the first lever to pull because it is a direct, simple action, but tuning is often a cheaper and more effective first step.
Mistake
Turning on Multi-AZ automatically improves database performance.
Correct
Multi-AZ provides high availability and automatic failover, not performance improvement. Read replicas (which are separate instances) improve read performance, but Multi-AZ alone does not add compute or I/O capacity.
The exam scenario often pairs Multi-AZ with 'disaster recovery' or 'availability', but beginners confuse it with 'scaling' because it involves multiple instances.
Mistake
RDS Parameter groups are automatically tuned for my workload, so I should not touch them.
Correct
Default parameter groups are conservative and designed to work with many workloads. They often need tuning for your specific use case, like increasing shared_buffers for a database that caches a lot of data or raising max_connections for an app with many users.
AWS documentation often says 'default settings are a starting point' but beginners assume 'optimised by default' because other cloud services like Lambda have sensible defaults for serverless.
Mistake
Using SELECT * is fine because the database will handle it efficiently.
Correct
SELECT * fetches all columns, wasting I/O, memory, and network bandwidth. Always list only the columns you need. The database cannot optimise a query when it does not know which columns are actually required.
Developers type SELECT * for convenience and do not realise that the database engine reads all column data from disk, even columns that are never sent to the application.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A full table scan is when the database reads every row of a table to find matching data. It is bad because it uses a lot of I/O and CPU, especially on large tables. Adding an index allows the database to skip the scan and jump directly to the rows you need.
Run the EXPLAIN command before the query. Look for 'Index Scan' (using an index) or 'Seq Scan' (full table scan). If you see 'Seq Scan', your query is not using an index.
No, too many indexes hurt write performance because every INSERT, UPDATE, and DELETE must update each index. Only create indexes on columns used in WHERE clauses, JOINs, or ORDER BY — and only if the query is actually slow.
A read replica is a separate database instance that handles read-only queries to offload the primary. Multi-AZ is a standby instance in a different Availability Zone for automatic failover during outages. Read replicas improve read performance; Multi-AZ improves availability.
You create or modify a custom parameter group in the RDS console, change the parameter value, then associate it with your database instance. Some parameters require a reboot to take effect; check whether the parameter is 'static' or 'dynamic'.
If the query returns a large percentage of rows (e.g., 50% of the table), the database might still decide to do a full table scan because scanning the index plus the rows is slower than just scanning the whole table. Indexes work best for highly selective queries that return few rows.
You've finished Database Performance Tuning and Optimization. Continue through the DBS-C01 study guide to build a complete picture of the exam.
Done with this chapter?