Swapping a Primary Key on a 4M-Row Table: Why I Took 10 Minutes of Downtime

In a strategic decision to address performance issues caused by the primary key configuration on a 4-million-row table, the author swapped the primary key, necessitating a brief, controlled downtime rather than opting for a zero-downtime migration process. The table in question, critical for aggregating CI job metrics, had primary key columns that contributed to unnecessary data duplication and aggregation inefficiencies, leading to query timeouts in production. By removing two columns from the primary key, which were redundant due to the implementation of a dedicated runner-analytics view, the author simplified the table structure, reducing costs associated with excess cardinality. Although the standard approach would involve creating a shadow table to maintain continuous availability, the non-critical nature of the job-metrics view allowed for a straightforward key swap that involved a brief, ten-minute downtime. This decision was facilitated by disabling the frontend view and halting the aggregation service, ensuring no disruption to end-users. The operation included consolidating rows under the new key, dropping unnecessary indexes, and reconstructing the trigger to align with the updated schema, ultimately enhancing performance without affecting customers. The experience highlighted the importance of separating schema changes from data backfill operations and emphasized that downtime can be an acceptable trade-off, especially when it simplifies the process and accelerates delivery of improvements.

No tracked trend matches for this post yet.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.