What's changed: Initial version
5.3Tuning & capacity design
Covers denormalization, intentionally introducing redundancy for performance; partitioning, splitting a large table; SQL tuning, revisiting the SQL statement itself; and bottleneck diagnosis—isolating the true cause of performance degradation and identifying the cause of a slow query.
For a database designer or DBA, addressing a performance problem does not end with "add an index." Sustainable tuning requires judgment about revisiting the table structure itself (denormalization, partitioning), judgment about how the SQL is written, and judgment about diagnosing where the true bottleneck actually lies. This section builds that design and diagnostic judgment.
5.3.1Denormalization and partitioning
- Denormalization is a design decision to intentionally combine and introduce redundancy into tables that normalization had decomposed, for the purpose of prioritizing read performance. For example, instead of fetching a product name via a JOIN every time, storing the product name redundantly in the order-line-item table itself reduces join cost. However, this means accepting the risk of an update anomaly: if the product name changes later, multiple places must be updated in sync or an inconsistency arises.
- Partitioning physically splits a single logical table into multiple regions (partitions) based on a specific key (date, region, etc.). Schemes include range partitioning (splitting by an interval such as a date range) and hash partitioning (splitting evenly by hash value). When a query's condition can be narrowed to a specific partition, only that partition needs to be read (partition pruning), which is faster than scanning the entire table. It also makes archiving/deleting old data by partition easier.
5.3.2SQL tuning and bottleneck diagnosis
- SQL tuning improves performance by revisiting how the SQL statement itself is written, without changing the execution plan or index configuration. Examples include narrowing a
SELECT *down to only the needed columns, rewriting a correlated subquery as a JOIN, or decomposing anORcondition that prevents multiple indexes from being used into aUNION. - Bottleneck diagnosis is the process of isolating, based on measured data, whether the cause of degraded performance lies in CPU (complex computation, heavy join processing), memory (disk swapping from insufficient sort/hash-join buffer), disk I/O (heavy physical reads from missing indexes), or lock waits (exclusion from concurrency control). Misidentifying the cause wastes time on an off-target countermeasure (e.g., adding indexes when the real cause is lock waits).
Most-tested: "denormalization prioritizes read performance and accepts redundancy, taking on the risk of update anomalies" and "partitioning uses partition pruning to read only the relevant region." Bottleneck diagnosis always tests the stance of isolating, from measured data, whether the cause is CPU, memory, disk I/O, or lock waits.
Suppose a SaaS provider's DBA is asked to look into a monthly report-generation batch whose runtime has been gradually growing until it now affects the service. Measuring the wait events of the running query first shows that both CPU usage and disk I/O usage remain low, while only lock-wait time is disproportionately long. This differs from the typical picture of "missing an index" or "insufficient memory," and instead isolates the true cause as concurrency control (exclusive locking). Digging further, the report batch was found to hold long-running read locks across all order rows for the target month while aggregating, conflicting with the normal order processing (INSERT/UPDATE) running at the same time. Jumping straight to "let's add more indexes" here would have no effect on a lock-wait bottleneck and would waste valuable time. Based on the measurements, the DBA adopted a table-structure redesign: converting the orders table to monthly range partitioning. This meant the report batch only needed to lock the partition for the target month (partition pruning also shrinks the lock scope itself), so it no longer conflicted with normal order processing against other months' partitions. In addition, the report's aggregation SQL itself was improved via SQL tuning that rewrote a correlated subquery as a JOIN, further shortening overall processing time. As in this case, the sustainable performance-design judgment is to first isolate, from measurement, where the bottleneck lies (CPU/memory/disk I/O/lock waits), and then choose denormalization, partitioning, SQL tuning, or some combination, according to that true cause.
| Technique | Aim | Risk / cost accepted |
|---|---|---|
| Denormalization | Reduces join cost, speeds up reads | Update anomalies (multiple places must be updated in sync) |
| Partitioning | Shrinks read and lock scope via partition pruning | Poor partition-key design yields no benefit |
| SQL tuning | Cuts wasted processing by revisiting how SQL is written | Limited effect if the root cause lies elsewhere (structure, locking) |
Trap: "The first countermeasure for performance degradation is always to add more indexes" is wrong—when the true cause is lock waits or insufficient memory, adding an index has no effect and wastes time. Also wrong: "denormalization violates normalization and must always be avoided"—denormalization is a legitimate design decision that deliberately accepts the risk of update anomalies in order to prioritize read performance, and should be adopted when the requirements call for it.
5.3.3Section summary
- Denormalization is a design decision prioritizing read performance and accepting update-anomaly risk. Partitioning splits data so partition pruning shrinks read and lock scope
- SQL tuning improves performance by revisiting how the SQL statement is written, without changing the execution plan or indexes
- Bottleneck diagnosis isolates whether CPU, memory, disk I/O, or lock waits is the true cause from measurement, before choosing a countermeasure
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. A monthly report-generation batch's runtime is gradually growing. Measurement shows both CPU usage and disk I/O usage remain low, while only lock-wait time is disproportionately long. Which countermeasure is most appropriate in this situation?
Q2. You are considering redesigning an order-line-items table to store the product name redundantly in the table itself, instead of fetching it via a JOIN each time. Which risk must you necessarily accept when adopting this denormalization?
Q3. With no room to change the execution plan or index configuration, you have a query fetching unnecessary columns via `SELECT *` and another query with an `OR` condition that prevents multiple indexes from being used. Which approach should be considered first to improve performance?
Keep track of your progress
The full study guide is free to read. Sign up free to practice with the question bank, track what you have read, review your mistakes, and highlight passages.