Author: Bevan To
Editor: Vahe Aslanyan
Databases are the foundation of most modern software systems. Whether an application manages user accounts, processes financial transactions, stores product inventories, or serves social media content, nearly every request ultimately depends on retrieving or modifying data. As applications grow, database performance often becomes the primary factor determining overall system responsiveness and scalability.
Improving database performance involves much more than upgrading hardware or increasing available resources. Well-performing databases are the result of thoughtful schema design, efficient queries, appropriate indexing strategies, and intelligent caching. Poorly optimized queries or unnecessary database operations can significantly increase response times, consume excessive resources, and limit the application's ability to scale, even when powerful infrastructure is available.
The purpose of this handbook is to provide a practical guide to improving database performance through proven engineering techniques. It focuses on three key areas that have the greatest impact on application performance: indexing, query optimization, and caching. These techniques allow engineering teams to reduce query execution time, minimize unnecessary database workload, and improve application responsiveness without relying solely on additional hardware.
Throughout this handbook, readers will learn how databases execute queries, how indexes improve data retrieval, how execution plans reveal performance bottlenecks, and how different caching strategies can reduce repeated work. In addition to explaining these concepts, the handbook discusses common mistakes, performance trade-offs, and best practices that can be applied to a wide range of relational database systems.
This handbook is intended to serve as both a learning resource and a practical reference for engineers responsible for developing, maintaining, or optimizing database-backed applications. By understanding how databases process queries and how different optimization techniques affect performance, engineering teams can build applications that remain efficient, responsive, and scalable as data volumes and user demand continue to grow.
Database performance problems rarely occur because the database software itself is inherently slow. More often, performance degrades because one or more system resources become saturated as application traffic, data volume, or query complexity increases. These limitations are known as database bottlenecks.
A bottleneck occurs when a particular component limits the overall performance of the database. Increasing the capacity of other resources will provide little benefit until the primary constraint has been addressed. For example, adding additional application servers will not improve response times if the database is already operating at maximum capacity.
Understanding where these bottlenecks occur is the first step toward improving database performance. Effective optimization begins with identifying the resource limiting performance rather than making assumptions about the source of the problem.
The database processor is responsible for executing queries, sorting data, performing joins, evaluating conditions, and maintaining indexes.
High CPU utilization may occur when:
When CPU utilization remains consistently high, query response times often increase as requests wait for available processing resources.
Before upgrading hardware, engineering teams should first review slow queries and determine whether they can be optimized through improved indexing or query design.
Databases rely heavily on memory to cache frequently accessed data, store query execution information, and reduce expensive disk operations.
When sufficient memory is available, the database can retrieve much of its data directly from memory rather than reading from storage.
Memory bottlenecks may result in:
Monitoring memory utilization helps determine whether additional memory or improved query efficiency is needed.
Although modern storage devices are significantly faster than previous generations, reading data from disk remains much slower than retrieving data from memory.
Disk I/O becomes a bottleneck when the database performs excessive read or write operations.
Common causes include:
Applications experiencing high disk activity often benefit from query optimization, additional indexing, or improved caching before hardware upgrades are considered.
Every interaction between an application and its database requires network communication. As request volume grows, network latency and bandwidth limitations can begin affecting overall application performance.
Network bottlenecks commonly result from:
Reducing unnecessary network traffic by retrieving only the required data and implementing appropriate caching strategies can significantly improve performance.
Databases frequently process multiple transactions simultaneously. To maintain data consistency, they use locking mechanisms that prevent conflicting operations from modifying the same data at the same time.
Under heavy workloads, these locks can become a source of contention.
Examples include:
Excessive locking may cause queries to wait for other transactions to complete, increasing response times and reducing overall throughput.
Keeping transactions as short as possible helps minimize lock contention.
Every application request typically requires a database connection. As traffic increases, the number of concurrent connections can eventually exceed what the database server can efficiently manage.
Symptoms of connection bottlenecks include:
Most production applications address this issue through connection pooling, allowing multiple requests to reuse existing database connections instead of creating new ones for every operation.
Performance issues are often caused by a single constrained resource rather than every component operating inefficiently.
The following table summarizes several common bottlenecks and their typical symptoms.
BottleneckCommon SymptomsPotential ImprovementsCPUHigh processor utilization, slow query executionOptimize queries, improve indexing, reduce expensive operationsMemoryIncreased disk access, poor cache performanceIncrease available memory, optimize caching, reduce memory usageDisk I/OSlow reads and writes, high storage activityAdd indexes, optimize queries, reduce unnecessary disk operationsNetworkHigh latency, slow data transfersReduce result sizes, optimize queries, implement cachingLock ContentionWaiting transactions, reduced throughputShorten transactions, reduce conflicting updatesConnection LimitsConnection timeouts, slow responsesConfigure connection pooling, optimize connection management
Engineering teams should follow these practices when diagnosing database bottlenecks:
Database bottlenecks are a natural consequence of application growth. As workloads evolve, different resources may become the limiting factor at different stages of the application's lifecycle. By understanding the common sources of database performance constraints and measuring system behavior before making changes, engineering teams can apply targeted optimizations that improve performance while avoiding unnecessary infrastructure costs.
One of the most common mistakes in database optimization is attempting to improve performance without first identifying the actual source of the problem. Slow response times are often attributed to the database, but the underlying issue may instead be inefficient application code, poor query design, missing indexes, or unnecessary network communication.
Effective database performance engineering begins with measurement. Rather than making assumptions, engineering teams should collect performance data, establish baseline metrics, and identify the queries or operations responsible for the greatest resource consumption. Optimization efforts based on measurable evidence are far more likely to produce meaningful improvements than changes made through trial and error.
Measuring performance also provides a way to verify whether an optimization has achieved its intended result. Without a baseline for comparison, it is difficult to determine whether a change has actually improved performance or simply shifted the workload elsewhere.
Before making changes, engineers should understand how the database performs under normal operating conditions.
A performance baseline typically includes measurements such as:
These measurements provide a point of comparison for evaluating future optimizations. If performance degrades after a deployment or infrastructure change, engineers can compare current metrics against the established baseline to identify what has changed.
Database performance should be monitored continuously rather than only when problems occur.
Some of the most useful metrics include:
MetricWhy It MattersQuery LatencyMeasures how long individual queries take to execute.ThroughputIndicates how many queries or transactions the database processes over time.CPU UtilizationIdentifies whether query execution is saturating processor resources.Memory UsageShows whether sufficient memory is available for caching and query execution.Disk I/OReveals excessive reads or writes that may indicate inefficient queries.Active ConnectionsHelps identify connection bottlenecks or improperly configured connection pools.Cache Hit RatioMeasures how often requested data is served from memory instead of disk.
Monitoring these metrics over time helps engineering teams recognize trends before they develop into production issues.
Most relational database systems provide a slow query log, which records queries that exceed a specified execution time.
Rather than examining every database request, engineers can focus their attention on the operations consuming the most time and resources.
Slow query logs commonly reveal:
Reviewing slow query logs regularly is often one of the fastest ways to identify optimization opportunities.
Not every slow query deserves immediate attention.
A query that executes once per day may have little impact on overall system performance, while a query that runs thousands of times per minute can significantly affect the database even if each execution is only moderately slow.
When prioritizing optimization efforts, engineering teams should consider both:
Improving a frequently executed query often produces a much greater overall performance benefit than optimizing an infrequently used operation.
Optimization should always be validated with measurable results.
After implementing changes such as adding indexes, rewriting queries, or introducing caching, engineers should compare updated performance metrics against the original baseline.
Questions to evaluate include:
Comparing before-and-after measurements ensures that optimization efforts produce genuine improvements rather than simply changing how the workload is distributed.
Performance data is only valuable when interpreted correctly.
Some common mistakes include:
Avoiding these mistakes helps ensure that optimization efforts address the actual causes of poor performance.
Engineering teams should follow these best practices when measuring database performance:
Successful database optimization begins with understanding how the system behaves under real workloads. By collecting meaningful performance data, identifying the most expensive operations, and validating improvements through measurement, engineering teams can focus their efforts where they will have the greatest impact while avoiding unnecessary or ineffective optimizations.
Writing an SQL query is only part of how a database retrieves data. Before executing a query, the database determines the most efficient way to access the requested information. This decision-making process produces an execution plan, which describes the operations the database will perform to execute the query.
Execution plans are one of the most valuable tools for database performance engineering because they reveal exactly how the database processes a query. Rather than guessing why a query is slow, engineers can examine the execution plan to identify inefficient operations such as full table scans, unnecessary sorting, expensive joins, or missing indexes.
Learning to interpret execution plans allows developers to optimize queries based on measurable evidence instead of trial and error.
An execution plan is a step-by-step description of how the database intends to retrieve the requested data.
Depending on the database system, execution plans may be generated before execution (estimated plans) or after execution (actual plans).
An execution plan typically includes information such as:
Although the exact format varies between database systems, the underlying concepts remain largely the same.
One of the first things engineers should look for in an execution plan is whether the database is performing a table scan.
A table scan occurs when the database reads every row in a table to locate the requested data.
For example:
SELECT *
FROM Customers
WHERE LastName='Smith';
If the LastName column is not indexed, the database may examine every row in the Customers table before finding the matching records.
Table scans are not always bad. They are often acceptable for:
However, for large production tables containing millions of rows, full table scans frequently become a major performance bottleneck.
When an appropriate index exists, the database can often avoid scanning the entire table.
Execution plans commonly show one of two index operations:
OperationDescriptionIndex SeekNavigates directly to the required rows using an index.Index ScanReads much or all of an index to locate matching records.
An Index Seek is generally preferred because it allows the database to retrieve only the necessary rows.
An Index Scan is typically more efficient than a table scan but may still process a significant portion of the index if many records match the query.
Understanding the difference helps engineers determine whether additional indexing or query modifications are necessary.
When queries retrieve data from multiple tables, the database must determine how those tables should be combined.
Execution plans commonly use one of three join algorithms.
A Nested Loop Join compares rows from one table against another.
This method performs well when:
A Hash Join creates an in-memory hash table for one dataset before matching rows from another table.
Hash joins are commonly used for:
Although they require additional memory, hash joins often perform better than nested loops for large tables.
A Merge Join combines two datasets that are already sorted.
Because the input data is ordered, merge joins can efficiently process large result sets while minimizing additional sorting work.
Sorting can become an expensive part of query execution, particularly when processing large datasets.
Execution plans may include sort operations caused by:
ORDER BYGROUP BYDISTINCTIf sufficient indexes exist, the database may already retrieve rows in the required order, eliminating the need for an additional sorting step.
When sorting large volumes of data cannot be avoided, it may require significant CPU time and memory.
Most execution plans assign an estimated cost to each operation.
These values represent the database optimizer's estimate of the relative amount of work required to perform each step.
While cost estimates should not be interpreted as exact execution times, they help engineers identify which operations consume the greatest amount of resources.
High-cost operations often include:
Focusing optimization efforts on the highest-cost operations typically produces the greatest performance improvements.
Consider the following query:
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID=1500;
A simplified execution plan might appear as:
Index Seek
↓
Retrieve Matching Row
↓
Return Results
Now consider a query without an appropriate index:
SELECT*
FROM Customers
WHERE City='Chicago';
Its execution plan might resemble:
Table Scan
↓
Filter Rows
↓
Return Results
The second query requires significantly more work because every row must be examined before filtering the results.
When reviewing execution plans, engineering teams should:
Execution plans provide valuable insight into how a database processes queries behind the scenes. By learning to recognize inefficient operations and understanding why the database chooses a particular execution strategy, engineering teams can make informed optimization decisions that improve query performance, reduce resource consumption, and build applications capable of handling increasingly large workloads.
As databases grow, locating a specific record becomes increasingly expensive. Without an efficient way to find data, the database may need to examine every row in a table before locating the requested information. While this approach may be acceptable for small datasets, it quickly becomes a significant performance bottleneck as tables grow to millions of records.
An index is a data structure that allows the database to locate rows much more efficiently. Instead of searching the entire table, the database can use the index to quickly identify where the desired data is stored. Proper indexing is one of the most effective ways to improve query performance because it reduces the amount of data the database must examine during query execution.
However, indexes are not free. They require additional storage space and must be updated whenever data is inserted, modified, or deleted. Understanding how indexes work and when to use them is essential for designing high-performance database systems.
Without an index, the database often performs a full table scan, reading every row until it finds the requested records.
For example:
SELECT *
FROM Customers
WHERE Email='user@example.com';
If the Email column is not indexed, the database must compare every row in the Customers table before finding the correct record.
With an index on the Email column, the database can navigate directly to the matching row instead of searching the entire table.
For large datasets, this difference can reduce query execution time from several seconds to only a few milliseconds.
The most common type of database index is the B-Tree (Balanced Tree).
Rather than storing records sequentially, a B-Tree organizes indexed values into a hierarchical structure that allows the database to eliminate large portions of the dataset during each search.
A simplified representation looks like this:
[500]
/ \
[250] [750]
/ \ / \
... ... ... ...
When searching for a value, the database follows the appropriate branch of the tree until it reaches the desired record.
Because each comparison removes a large portion of the remaining search space, B-Tree indexes remain efficient even as tables grow to millions of rows.
B-Tree indexes are well suited for:
=)>, <, BETWEEN)ORDER BY)For these reasons, they are the default index type in most relational database systems.
A clustered index determines the physical order in which rows are stored within a table.
Because the table itself is organized according to the clustered index, each table can have only one clustered index.
For example, if a table is clustered by CustomerID, records are stored in ascending order of that column.
Benefits of clustered indexes include:
Since changing the clustered index requires reorganizing the table's data, it should generally be created on a stable column that is frequently searched.
A non-clustered index stores a separate structure containing indexed values along with references to the corresponding table rows.
Unlike clustered indexes, non-clustered indexes do not determine how the table itself is stored.
A table can have multiple non-clustered indexes, each supporting different query patterns.
For example:
EmailLastNameOrderDateWhen a query searches one of these columns, the database first locates the matching entry in the index before retrieving the complete row from the table.
Non-clustered indexes provide flexibility by allowing multiple access paths without reorganizing the underlying data.
Sometimes an index contains enough information to locate matching rows but not enough information to satisfy the entire query.
In these situations, the database performs an index lookup.
For example:
SELECT FirstName, LastName
FROM Customers
WHERE Email='user@example.com';
If the index contains only the Email column, the database uses the index to locate the correct row before reading the remaining columns from the table.
Although index lookups are generally much faster than full table scans, they still require additional work. Later sections will discuss covering indexes, which eliminate many of these extra lookups.
Indexes improve read performance but introduce additional work whenever data changes.
Whenever a row is:
the database must also update every affected index.
Applications with heavy write workloads may therefore experience slower insert and update performance if too many indexes exist.
For this reason, indexes should be added only when they provide measurable performance benefits.
Engineering teams should follow these guidelines when creating indexes:
Indexes are one of the most powerful tools available for improving database performance. By allowing the database to locate records efficiently, they reduce query execution time, minimize unnecessary data access, and improve overall application responsiveness. Understanding how indexes are structured and maintained provides the foundation for more advanced indexing strategies discussed in the following sections.
Creating an index can significantly improve query performance, but not all indexes are equally effective. The columns selected, the order in which they are defined, and the type of index all influence how efficiently the database can retrieve data. An index that greatly improves one query may provide little benefit or even negatively impact performance for another.
Choosing the right index requires understanding how an application accesses its data. Engineers should analyze common query patterns rather than indexing every column in a table. Well-designed indexes support the queries that are executed most frequently while minimizing unnecessary storage and maintenance overhead.
This section introduces several common index types and explains when each should be used.
A single-column index is created on one column within a table.
For example:
CREATE INDEX idx_customers_email
ON Customers (Email);
This type of index is most effective when queries frequently search, filter, or sort using a single column.
Common candidates include:
Single-column indexes are simple to maintain and often provide substantial performance improvements for straightforward queries.
Many production queries filter using multiple columns simultaneously.
For example:
SELECT *
FROM Orders
WHERE CustomerID=100
AND OrderDate>='2026-01-01';
Rather than creating separate indexes on both columns, a composite index stores multiple columns within a single index.
CREATE INDEX idx_orders_customer_date
ON Orders (CustomerID, OrderDate);
Composite indexes are particularly effective when the indexed columns frequently appear together in WHERE, ORDER BY, or JOIN clauses.
However, the order of the columns matters.
Consider the following index:
(CustomerID, OrderDate)
This index efficiently supports queries filtering by:
It does not efficiently support queries filtering only by OrderDate, because the database searches the index from left to right.
Choosing the correct column order is therefore one of the most important aspects of composite index design.
A unique index ensures that duplicate values cannot exist within an indexed column or combination of columns.
For example:
CREATEUNIQUE INDEX idx_users_email
ON Users (Email);
With this index in place, the database prevents two users from sharing the same email address.
Unique indexes serve two purposes:
They are commonly used for:
Whenever a column must contain unique values, a unique index is often the preferred solution.
Sometimes an index contains every column required to answer a query.
In this situation, the database can retrieve the result directly from the index without reading the underlying table.
This is known as a covering index.
Consider the following query:
SELECT FirstName, LastName
FROM Customers
WHERE Email='user@example.com';
If the index contains:
(Email, FirstName, LastName)
the database can return the requested data entirely from the index.
Because no additional table lookup is required, covering indexes often produce significant performance improvements for frequently executed queries.
However, including too many columns increases index size and maintenance costs, so covering indexes should be used selectively.
Indexes should be designed around how the application actually accesses data.
Questions to consider include:
WHERE clauses?Understanding these access patterns helps ensure that indexes provide measurable performance improvements rather than unnecessary overhead.
Indexes improve read performance but increase the cost of write operations.
Whenever data is inserted, updated, or deleted, every affected index must also be updated.
As additional indexes are added:
For applications that perform frequent writes, engineers should carefully evaluate whether each index provides enough benefit to justify its ongoing maintenance cost.
When selecting indexes, engineering teams should:
Selecting the appropriate indexes is one of the most impactful decisions in database performance engineering. By matching index design to real application workloads, engineering teams can significantly reduce query execution time, improve resource utilization, and support larger datasets without unnecessary increases in infrastructure costs.
Indexes are one of the most effective tools for improving query performance, but more indexes do not always result in a faster database. Every index consumes storage, requires maintenance, and increases the amount of work the database performs whenever data changes. While well-designed indexes can dramatically reduce query execution time, unnecessary or poorly planned indexes may degrade overall system performance.
Database performance engineering involves finding the right balance between read efficiency and write overhead. The goal is not to index every column, but to create indexes that provide measurable benefits for the application's most important queries.
Understanding the costs associated with indexing helps engineers make informed decisions about when an index is truly beneficial.
Every time a row is inserted, updated, or deleted, the database must update not only the table itself but also every index associated with the modified columns.
For example, if a table has five indexes, inserting a single record requires the database to:
As the number of indexes increases, write operations require more processing time.
Applications that perform frequent inserts or updates may experience noticeable slowdowns if too many indexes are maintained.
Indexes require additional disk space because they store copies of indexed values along with references to the corresponding table rows.
For small tables, this additional storage is usually insignificant. However, for databases containing millions of records, indexes can occupy a substantial amount of disk space.
Large indexes also affect:
Engineering teams should periodically review index storage usage to ensure that each index continues to provide sufficient value.
Indexes improve read performance at the expense of write performance.
Whenever indexed data changes, the database must reorganize portions of the index to maintain its structure.
Operations that become more expensive include:
For applications with heavy write workloads, excessive indexing may reduce overall throughput even if read queries become slightly faster.
This trade-off should always be considered when designing indexing strategies.
Not every index that exists is actually used.
Over time, applications evolve, queries change, and previously important indexes may no longer support active workloads.
Unused indexes continue to consume:
Most database systems provide tools that report index usage statistics, allowing engineers to identify indexes that are rarely or never accessed.
Removing unused indexes reduces maintenance overhead without affecting application performance.
It is possible for multiple indexes to provide nearly identical functionality.
For example:
(CustomerID)
(CustomerID, OrderDate)
In many cases, the composite index can also satisfy queries that search only by CustomerID.
Maintaining both indexes may provide little additional benefit while increasing storage requirements and write overhead.
Regular index reviews help identify redundant indexes that can be consolidated or removed.
A common mistake is creating indexes for every column in a table under the assumption that more indexes always improve performance.
In reality, over-indexing can produce several negative effects:
Indexes should always be created to support actual query patterns rather than hypothetical future use cases.
The ideal indexing strategy depends on the application's workload.
Applications that primarily perform read operations often benefit from additional indexes because faster queries outweigh the increased maintenance cost.
Applications that perform frequent writes generally require fewer, more carefully selected indexes to avoid slowing data modifications.
Engineering teams should evaluate each index by asking:
These questions help ensure that every index contributes meaningful value to the overall system.
When managing indexes, engineering teams should:
Indexes are essential for building high-performance databases, but they are not without cost. Every additional index increases storage requirements and adds work during data modifications. By carefully evaluating the benefits and trade-offs of each index, engineering teams can maintain fast query performance while minimizing unnecessary overhead and ensuring the database continues to scale efficiently.
Indexes can dramatically improve database performance when used correctly, but poorly designed indexes often provide little benefit and may even reduce overall performance. Many database performance issues are caused not by the absence of indexes, but by indexes that do not align with the application's query patterns.
Understanding common indexing mistakes helps engineering teams avoid unnecessary overhead while ensuring that indexes support the queries that matter most. Rather than adding indexes indiscriminately, developers should carefully evaluate how the database accesses data and design indexes that reflect real application workloads.
A common mistake is creating indexes on columns that contain only a small number of distinct values.
Examples include:
true / false)Active, Inactive)For example:
SELECT *
FROM Employees
WHERE IsActive = TRUE;
If nearly every employee is active, an index on IsActive provides little benefit because the database still needs to retrieve a large percentage of the table.
Indexes are generally most effective on high-cardinality columns, where values are relatively unique.
Examples include:
Higher-cardinality indexes allow the database to narrow the search much more efficiently.
Composite indexes depend heavily on the order of their columns.
Consider the following index:
(CustomerID, OrderDate)
This index efficiently supports queries such as:
SELECT *
FROM Orders
WHERE CustomerID = 150;
and
SELECT *
FROM Orders
WHERE CustomerID = 150
AND OrderDate > '2026-01-01';
However, it performs poorly for:
SELECT *
FROM Orders
WHERE OrderDate > '2026-01-01';
because the first indexed column (CustomerID) is not included in the search condition.
When creating composite indexes, the most selective or frequently filtered columns should typically appear first, based on the application's query patterns.
Indexes may become ineffective when functions are applied directly to indexed columns within a query.
For example:
SELECT *
FROM Customers
WHERE LOWER(Email) = 'user@example.com';
Although the Email column may be indexed, applying the LOWER() function prevents many database systems from using that index efficiently.
Instead, engineers should:
Allowing the database to compare indexed values directly generally produces much better performance.
Applications frequently retrieve data by joining multiple tables.
For example:
SELECT *
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
If the join columns are not indexed, the database may perform significantly more work while matching rows between tables.
Indexes should commonly exist on:
Proper indexing of join columns reduces execution time and improves the performance of complex queries involving multiple tables.
Another common mistake is assuming that every searchable column should have its own index.
While indexes improve read performance, each additional index increases:
Adding indexes without measuring their impact often leads to diminishing returns.
Indexes should be created only when they provide measurable improvements for frequently executed queries.
Indexes require ongoing maintenance as applications evolve.
Over time:
Indexes that were beneficial during early development may no longer be used in production.
Regularly reviewing index usage helps identify:
Periodic maintenance ensures that indexing strategies continue supporting current workloads.
To avoid common indexing mistakes, engineering teams should:
Indexes are among the most powerful tools for improving database performance, but their effectiveness depends entirely on thoughtful design. By avoiding common indexing mistakes and regularly reviewing how indexes are used in production, engineering teams can maximize query performance while minimizing unnecessary storage, maintenance, and write overhead.
Even with well-designed indexes, poorly written SQL queries can significantly reduce database performance. A query that retrieves unnecessary data, performs excessive computations, or scans large portions of a table consumes additional CPU time, memory, and disk resources. As applications grow and the number of concurrent users increases, inefficient queries can quickly become one of the largest contributors to slow response times.
Writing efficient queries is one of the simplest and most cost-effective ways to improve database performance. Small improvements to query structure can reduce execution time, lower resource consumption, and decrease the workload placed on the database server without requiring hardware upgrades or architectural changes.
Rather than focusing solely on making queries work correctly, engineers should also consider how efficiently the database can execute them.
A common mistake is retrieving every column from a table when only a few are actually required.
For example:
SELECT *
FROM Customers;
Using SELECT * causes the database to return every column, even if the application only needs a customer's name and email address.
A more efficient query is:
SELECT FirstName, LastName, Email
FROM Customers;
Retrieving only the required columns offers several benefits:
As a general rule, applications should avoid SELECT * in production code unless every column is genuinely required.
Queries should eliminate unnecessary rows as early as possible.
For example:
SELECT *
FROM Orders
WHERE Status = 'Completed';
Filtering with a WHERE clause allows the database to process only the relevant records instead of examining every row in the table.
Using indexed columns within filtering conditions can further improve performance by allowing the database to locate matching rows quickly.
Reducing the number of rows processed early in query execution often leads to faster joins, sorting operations, and aggregations later in the execution plan.
Returning thousands of rows in a single query increases response times, network usage, and application memory consumption.
Instead, applications should divide large datasets into smaller pages.
For example:
SELECT *
FROM Products
ORDER BY ProductID
LIMIT 50 OFFSET 0;
Pagination improves performance by:
For very large datasets, keyset pagination (also known as cursor-based pagination) is often more efficient than large offsets because the database does not need to skip an increasing number of rows.
Applications sometimes retrieve information that is already available or repeatedly execute the same query during a single request.
Examples include:
Reducing unnecessary database requests decreases overall workload and allows the database to spend more resources processing unique operations.
Whenever possible, reuse previously retrieved data or batch related queries together.
Even well-designed indexes cannot improve performance if queries prevent the database from using them.
Queries are generally more efficient when filtering directly on indexed columns.
For example:
SELECT *
FROM Orders
WHERE OrderID = 5000;
This query allows the database to use an index on OrderID.
In contrast, expressions that transform indexed columns may prevent efficient index usage, forcing the database to scan additional rows.
Writing queries that align with existing indexes helps maximize their effectiveness.
Queries often become slower as additional joins, subqueries, and calculations are introduced.
Before adding complexity, engineers should consider whether each operation is necessary.
Questions to ask include:
Simpler queries are generally easier for both developers and database optimizers to understand and execute efficiently.
When writing SQL queries, engineering teams should:
SELECT * in production queries.WHERE clauses.Efficient query design is one of the most impactful aspects of database performance engineering. Well-written queries reduce resource consumption, improve response times, and allow applications to scale more effectively as data volume and user traffic continue to grow. By consistently applying these principles, engineering teams can improve database performance without relying solely on additional infrastructure or hardware upgrades.
Modern applications rarely store all of their data in a single table. Instead, information is organized into multiple related tables to reduce redundancy and improve data consistency. As a result, queries frequently need to combine data from multiple tables or summarize large collections of records. These operations are performed using joins and aggregations.
While joins and aggregations are essential features of relational databases, they can also become significant performance bottlenecks if they process large datasets or lack appropriate indexes. Understanding how these operations work allows engineers to write more efficient queries and reduce unnecessary database workload.
The INNER JOIN returns only rows that have matching values in both tables.
For example:
SELECT Customers.FirstName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
In this query, only customers who have placed at least one order are returned.
INNER JOINs are generally efficient when:
Because the database ignores rows without matches, INNER JOINs often process less data than other join types.
A LEFT JOIN returns every row from the left table, even if no matching record exists in the right table.
For example:
SELECT Customers.FirstName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
This query returns all customers, including those who have never placed an order.
LEFT JOINs are useful when applications need to identify missing relationships, such as:
Although powerful, LEFT JOINs may process more data than INNER JOINs because they preserve every row from the primary table.
Aggregation functions summarize multiple rows into a single result.
Common aggregation functions include:
COUNT()SUM()AVG()MIN()MAX()For example:
SELECT COUNT(*)
FROM Orders;
or
SELECT AVG(TotalAmount)
FROM Orders;
Aggregations are widely used for:
As datasets grow, aggregation queries may become increasingly expensive because they often require scanning large numbers of records.
The GROUP BY clause divides rows into groups before performing aggregation.
For example:
SELECT CustomerID, COUNT(*)
FROM Orders
GROUP BY CustomerID;
This query calculates the total number of orders placed by each customer.
Grouping is commonly used to generate statistics such as:
Because grouping often requires sorting or hashing large datasets, it can become resource-intensive on large tables.
Proper indexing may reduce the amount of work required for grouping operations.
The HAVING clause filters aggregated results after grouping has been completed.
For example:
SELECT CustomerID, COUNT(*)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 10;
This query returns only customers who have placed more than ten orders.
Unlike the WHERE clause, which filters individual rows before grouping, HAVING filters entire groups after aggregation has occurred.
Using WHERE whenever possible reduces the number of rows processed before aggregation and generally produces better performance.
The ORDER BY clause sorts query results before they are returned.
For example:
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;
Sorting large result sets can consume considerable CPU time and memory.
Performance can often be improved by:
When an appropriate index already stores data in the desired order, the database may avoid performing an additional sort operation altogether.
Engineering teams should follow these practices when writing queries that use joins and aggregations:
WHERE before performing joins or aggregations whenever possible.GROUP BY.HAVING only for filtering aggregated results.Joins and aggregations are fundamental components of relational database systems, enabling applications to combine related data and generate meaningful summaries. When used efficiently, they support everything from simple user interfaces to complex analytical reporting. By understanding how these operations affect query execution, engineering teams can write SQL that remains efficient even as database size and application traffic continue to grow.
Not all performance problems are caused by missing indexes or insufficient hardware. In many cases, the underlying issue is an inefficient query design. Certain SQL patterns consistently lead to excessive database workload, unnecessary resource consumption, and poor scalability. These patterns are commonly referred to as query anti-patterns.
Query anti-patterns often produce acceptable performance during development when databases contain only a small amount of data. However, as applications grow and tables expand to millions of rows, these same queries can become significant bottlenecks.
Recognizing and avoiding these anti-patterns is an important part of database performance engineering. Small improvements to query design can often provide larger performance gains than adding additional hardware.
One of the most common database performance issues is the N+1 query problem.
This occurs when an application retrieves a collection of records and then executes an additional query for each individual record.
For example:
Instead of executing two queries, the application performs 101 database requests.
As the number of records increases, database traffic grows dramatically.
A more efficient approach is to retrieve all required information using a single query with an appropriate join.
For example:
SELECT Customers.FirstName, Orders.OrderID
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Reducing the number of database round trips often provides substantial performance improvements.
Another common anti-pattern is retrieving every column from a table when only a small subset is needed.
For example:
SELECT *
FROM Products;
This query returns every column, even if the application only displays the product name and price.
A more efficient alternative is:
SELECT ProductName, Price
FROM Products;
Selecting only the required columns reduces:
It also increases the likelihood that an existing index can satisfy the query without accessing the underlying table.
Subqueries can make SQL easier to write, but repeatedly executing the same subquery may significantly increase query execution time.
For example:
SELECT *
FROM Orders
WHERE CustomerID IN (
SELECT CustomerID
FROM Customers
WHERE Country = 'USA'
);
Depending on the database optimizer, repeatedly evaluating subqueries may require additional processing.
In many cases, rewriting the query as a join produces a simpler execution plan and improves performance.
The best approach depends on the database system and the execution plan, making it important to measure performance rather than assuming one form is always faster.
A Cartesian join occurs when two tables are combined without an appropriate join condition.
For example:
SELECT *
FROM Customers, Orders;
If the Customers table contains 1,000 rows and the Orders table contains 5,000 rows, this query produces 5 million result rows.
Cartesian joins usually occur accidentally when a join condition is omitted.
In most applications, joins should include an explicit relationship between the tables.
For example:
SELECT *
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Adding the correct join condition dramatically reduces the amount of work performed by the database.
Applications sometimes retrieve far more data than users actually need.
Examples include:
Over-fetching increases:
Using pagination, filtering, and selecting only the necessary columns helps eliminate unnecessary work.
Sorting large datasets can be expensive, particularly when the database must organize millions of rows before returning results.
For example:
SELECT *
FROM Orders
ORDER BY OrderDate;
If the application does not require ordered results, the sorting operation adds unnecessary overhead.
When sorting is required, indexing the sort column or limiting the result set through pagination can significantly improve performance.
Engineering teams should avoid these common query anti-patterns by following these guidelines:
SELECT * unless every column is required.Many database performance issues originate from inefficient query design rather than limitations in hardware or database software. By identifying common query anti-patterns and replacing them with more efficient alternatives, engineering teams can significantly reduce database workload, improve response times, and build applications that continue to perform well as data volumes and user traffic increase.
Caching is one of the most effective techniques for improving database performance. Instead of repeatedly executing the same database query, a cache stores previously retrieved data in a faster storage layer so that future requests can be served without accessing the database again. Since reading data from memory is significantly faster than reading from disk, caching can dramatically reduce response times and lower the overall workload on the database.
However, caching is not a solution for every performance problem. Caching data that changes frequently or is rarely accessed can increase system complexity without providing meaningful performance improvements. Effective caching begins with identifying which data benefits from being stored temporarily and understanding the trade-offs between performance and data freshness.
A properly designed caching strategy provides several advantages:
For applications with read-heavy workloads, caching can significantly increase throughput while delaying or eliminating the need for additional database infrastructure.
Not every piece of data should be cached. The best candidates are data that is read frequently but changes relatively infrequently.
Examples include:
These types of data are often requested repeatedly by many users, making them ideal candidates for caching.
Some data changes so frequently that maintaining a cache becomes more expensive than querying the database directly.
Examples include:
Caching highly dynamic data increases the risk of serving outdated information unless sophisticated invalidation mechanisms are implemented.
In these situations, direct database access or carefully controlled short-lived caching may be more appropriate.
The effectiveness of caching depends largely on the application's workload.
Read-heavy applications benefit the most from caching because the same information is requested repeatedly.
Examples include:
In these systems, a single cached result may serve thousands of users before the underlying data changes.
In contrast, write-heavy applications update data frequently.
Examples include:
Because cached data becomes outdated quickly, write-heavy applications often require more sophisticated cache management strategies or may cache only selected portions of their data.
Before introducing a cache, engineering teams should identify where repeated database work occurs.
Questions to consider include:
Monitoring query frequency and execution times helps identify the parts of an application that will benefit most from caching.
Caching should not be used simply because it is available.
In some situations, introducing a cache adds unnecessary complexity without providing meaningful performance improvements.
Examples include:
Engineers should first measure database performance before deciding whether caching is necessary. In many cases, optimizing queries or adding indexes may solve the problem more effectively than introducing a cache.
When deciding whether to cache data, engineering teams should:
Caching is a powerful optimization technique, but its effectiveness depends on choosing the right data to cache. By focusing on read-heavy workloads, identifying repeated database operations, and carefully evaluating data freshness requirements, engineering teams can significantly reduce database load while improving application responsiveness and scalability.
Choosing to cache data is only the first step in improving database performance. Equally important is selecting an appropriate cache strategy, which determines how data moves between the application, cache, and database. Different strategies offer different trade-offs between performance, consistency, implementation complexity, and reliability.
There is no universal caching strategy that works for every application. The most appropriate approach depends on factors such as how frequently data changes, how important data consistency is, and whether the application is primarily read-heavy or write-heavy.
Understanding the strengths and weaknesses of each strategy helps engineering teams design caching systems that improve performance without introducing unnecessary complexity.
The cache-aside strategy is one of the most commonly used caching approaches.
When an application needs data, it first checks the cache.
Advantages of cache-aside include:
However, the first request for uncached data is always slower because it must retrieve information from the database.
With read-through caching, the application communicates only with the cache.
If the requested data is unavailable, the cache automatically retrieves it from the database before returning it to the application.
Unlike cache-aside, the application does not manage cache misses directly.
Advantages include:
The primary disadvantage is that cache infrastructure becomes more tightly coupled with the database, making the overall architecture slightly more complex.
A write-through cache updates both the cache and the database whenever data changes.
The sequence is typically:
This approach ensures that cached data remains synchronized with the database.
Advantages include:
The trade-off is increased write latency because every update must be written to both storage layers before the request is considered complete.
A write-behind (or write-back) strategy delays database updates.
Instead of writing immediately to the database:
This approach can significantly improve write performance because users do not wait for database operations to complete.
However, write-behind introduces additional risks.
If the cache fails before the pending updates reach the database, recent changes may be lost unless appropriate durability mechanisms are implemented.
For this reason, write-behind caching is typically used only when high write throughput is more important than immediate persistence.
Most cached data should not remain in memory indefinitely.
A Time-to-Live (TTL) defines how long a cached item remains valid before it expires automatically.
For example:
Selecting an appropriate TTL requires balancing two competing goals:
The optimal value depends on how frequently the underlying data changes and how much stale data the application can tolerate.
Different workloads benefit from different caching strategies.
StrategyBest ForPrimary Trade-OffCache-AsideGeneral-purpose applicationsFirst request incurs a cache missRead-ThroughApplications with centralized cache managementMore complex cache infrastructureWrite-ThroughSystems requiring strong cache consistencyIncreased write latencyWrite-BehindHigh write-volume applicationsRisk of delayed persistenceTTL ExpirationAutomatically refreshing cached dataBalancing freshness and performance
Many production systems combine multiple strategies. For example, an application may use cache-aside for read operations while relying on TTL expiration to periodically refresh cached data.
When implementing caching strategies, engineering teams should:
Selecting the appropriate cache strategy is just as important as deciding what data to cache. By understanding how different strategies manage reads, writes, and data expiration, engineering teams can reduce database workload, improve application responsiveness, and maintain an appropriate balance between performance, consistency, and operational complexity.
Caching can significantly improve application performance, but it also introduces a new challenge: ensuring that cached data remains accurate. When information changes in the database, outdated copies may still exist in the cache. If these stale entries are not updated or removed, users may receive incorrect or outdated information.
This problem is known as cache invalidation. It is often considered one of the most difficult aspects of caching because it requires balancing two competing goals: maximizing cache efficiency while maintaining data consistency. An effective invalidation strategy ensures that cached data remains useful without allowing outdated information to persist longer than necessary.
The simplest cache invalidation method is Time-to-Live (TTL).
Each cached item is assigned an expiration time. Once the TTL expires, the item is automatically removed or marked as invalid. The next request retrieves fresh data from the database and stores a new copy in the cache.
For example:
DataExample TTLProduct catalog30 minutesWeather information10 minutesApplication settings1 hourNews headlines5 minutes
TTL is easy to implement and requires minimal coordination between the application and the cache.
However, there is a trade-off:
Choosing an appropriate TTL depends on how frequently the underlying data changes.
Instead of waiting for cached data to expire naturally, applications can remove or update cache entries immediately after modifying the database.
For example, when a user updates their profile:
This approach ensures that outdated information is not served after an update.
Explicit invalidation is commonly used for:
Although this method provides better consistency than relying solely on TTL, it requires additional application logic to keep the cache synchronized with the database.
Large distributed systems often use event-driven invalidation.
Instead of directly removing cache entries, applications publish events whenever important data changes.
For example:
Database Updated
│
Publish Event
│
Message Broker
│
Cache Service
│
Invalidate Cache
Multiple services can subscribe to these events and invalidate their own caches automatically.
Event-driven invalidation is particularly useful in microservice architectures where several applications depend on the same underlying data.
Advantages include:
The trade-off is increased architectural complexity due to the need for event messaging infrastructure.
A newly deployed application or recently restarted cache begins empty, resulting in a large number of cache misses.
This situation is sometimes called a cold cache.
To reduce the performance impact, many systems perform cache warming, which preloads frequently accessed data before users begin making requests.
Common examples include:
By loading this information in advance, applications avoid placing unnecessary load on the database immediately after startup.
A cache stampede occurs when a popular cache entry expires and many requests attempt to retrieve the same data simultaneously.
Without protection, each request queries the database independently, creating a sudden spike in database traffic.
Common techniques for preventing cache stampedes include:
Preventing cache stampedes helps maintain stable database performance during periods of heavy traffic.
Different applications require different invalidation approaches.
StrategyBest ForAdvantagesConsiderationsTTL ExpirationFrequently read, moderately changing dataSimple implementationMay temporarily serve stale dataExplicit InvalidationApplications requiring fresher dataImmediate consistency after updatesRequires additional application logicEvent-Driven InvalidationDistributed systems and microservicesKeeps multiple caches synchronizedMore complex infrastructureCache WarmingFrequently accessed contentReduces startup latency and cache missesRequires identifying commonly accessed data
Many production systems combine multiple techniques. For example, an application might use explicit invalidation after updates while still assigning TTL values as a safeguard against stale entries that are not explicitly removed.
Engineering teams should follow these practices when managing cache invalidation:
Cache invalidation is an essential component of any caching strategy. While caching improves performance by reducing database access, an ineffective invalidation strategy can undermine application correctness by serving outdated information. By selecting the appropriate invalidation techniques and combining them when necessary, engineering teams can achieve both high performance and reliable data consistency as their applications scale.
Caching is a powerful performance optimization technique, but it is not a substitute for good database design or efficient queries. Poorly implemented caching can introduce unnecessary complexity, consume excessive memory, and even make applications less reliable by serving outdated or inconsistent data. Many performance issues associated with caching arise not from the cache itself, but from how it is used.
Understanding common caching mistakes helps engineering teams build caching systems that improve performance while avoiding unintended side effects. Rather than treating caching as a universal solution, developers should carefully evaluate where it provides measurable value and how it will be maintained over time.
One of the most common mistakes is attempting to cache every piece of application data.
Not all information benefits from caching. Storing rarely accessed or frequently changing data consumes memory without significantly reducing database workload.
For example, caching:
often provides little benefit while increasing cache size and maintenance costs.
Instead, caches should focus on high-value data that is accessed repeatedly by many users.
Caching improves performance by temporarily storing data, but cached information can become outdated if it is not refreshed appropriately.
For example, an application may cache product prices for several hours even though prices change every few minutes. Users may then see incorrect pricing because the cache no longer reflects the current database state.
Engineering teams should carefully evaluate:
Balancing performance and data freshness is one of the most important aspects of cache design.
Simply adding a cache does not guarantee better performance.
A cache that rarely serves requests provides little value while still consuming memory and increasing application complexity.
One of the most important cache metrics is the cache hit rate, which measures how often requested data is successfully retrieved from the cache.
For example:
A consistently low hit rate may indicate:
Monitoring cache effectiveness helps engineers determine whether the caching strategy is actually improving application performance.
Another common mistake is introducing caching before optimizing the underlying database queries.
Caching can reduce how often a slow query executes, but it does not eliminate the inefficiency itself.
If the cache fails, expires, or is cleared, the application immediately falls back to executing the original slow query.
Engineers should therefore:
Caching should enhance an already optimized system rather than compensate for poor database design.
Applications often cache personalized information such as user profiles, shopping carts, or account settings.
If cache keys are not designed correctly, one user may accidentally receive another user's cached data.
For example, using:
profile
as a cache key is unsafe because every user shares the same entry.
Instead, cache keys should uniquely identify the requested data:
profile:user:1024
Proper cache key design is essential for maintaining both correctness and security.
Optimizing database performance is an ongoing process rather than a one-time task. As applications evolve, query patterns change, datasets grow, and user traffic increases, optimization strategies should be revisited regularly. The following checklist summarizes the key concepts covered throughout this handbook and provides a practical reference for evaluating the performance of a relational database system.
While every application has unique requirements, these guidelines represent widely accepted best practices for improving query performance, reducing unnecessary database workload, and maintaining responsive applications as they scale.
SELECT * has been replaced with explicit column selection where appropriate.Completing this checklist does not guarantee perfect database performance, but it greatly increases the likelihood that a database will remain efficient, maintainable, and capable of supporting future growth. Regularly revisiting these items helps engineering teams identify emerging bottlenecks before they become production issues and ensures that optimization efforts continue to align with the evolving needs of the application.
Database performance is one of the most important factors affecting the speed, scalability, and reliability of modern applications. Regardless of how well an application is designed, inefficient database operations can become a significant bottleneck as data volume and user traffic increase. Optimizing database performance is therefore not simply about making individual queries faster. It is about building systems that continue to perform efficiently as workloads evolve.
Throughout this handbook, we explored the core principles of database performance engineering. We began by examining common database bottlenecks and emphasizing the importance of measuring system performance before making optimization decisions. Understanding where time and resources are being spent allows engineering teams to focus their efforts on changes that provide measurable improvements rather than relying on assumptions.
We then examined indexing, one of the most effective techniques for accelerating data retrieval. Well-designed indexes allow databases to locate records efficiently, reduce unnecessary table scans, and improve the performance of searches, joins, and sorting operations. At the same time, we discussed the trade-offs associated with indexing, including increased storage requirements and slower write operations, demonstrating why indexes should be created thoughtfully rather than indiscriminately.
Efficient query design is equally important. Retrieving only the required data, minimizing unnecessary joins and sorting, avoiding common query anti-patterns, and reviewing execution plans all contribute to faster query execution and lower resource consumption. Small improvements to frequently executed queries can often produce substantial gains in overall application performance.
Caching provides another powerful optimization technique by reducing repeated database work. When implemented appropriately, caching decreases query volume, improves response times, and enables applications to serve significantly more users without increasing database capacity. However, effective caching requires careful consideration of cache strategies, expiration policies, and invalidation techniques to ensure that performance improvements do not come at the expense of data accuracy.
Perhaps the most important lesson throughout this handbook is that database optimization is an iterative process. Applications grow, workloads change, and new bottlenecks emerge over time. Techniques that are effective during one stage of an application's lifecycle may require adjustment as data volumes and traffic patterns evolve. Continuous monitoring, performance measurement, and regular optimization are therefore essential components of long-term database performance engineering.
There is no single optimization technique that solves every performance problem. Instead, successful database engineering relies on combining efficient schema design, well-planned indexing, optimized queries, intelligent caching, and ongoing monitoring into a cohesive strategy. By applying the principles presented in this handbook, engineering teams can build database-backed applications that remain responsive, scalable, and maintainable while supporting increasing numbers of users and growing volumes of data.
%402x.svg){kind=icon}