5.3Tuning & capacity design

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.