How real data, not convention, shaped four different PostgreSQL partitioning strategies for the largest tables in a production database, and the two bugs we caught before they ever reached production.

Key Takeaways

• Designed partitioning strategies for the four largest tables in a production database schema, deliberately not using the same approach for all four.
• Foreign key dependency analysis surfaced 35 and 59 dependent relationships on two of the four tables, a hard structural constraint that determined migration order and complexity, not a matter of team preference. 
• Real production data revealed severe skew, one event alone accounted for roughly 13% of a table’s entire volume, informing a hybrid partitioning design rather than a naive even split.
• Built a zero-downtime migration pattern combining real-time trigger-based sync with a self-unscheduling batched backfill, so no write is ever. missed during the transition
• Two tables with more than one foreign key relationship to the same parent were sharing a single helper column, silently corrupting one relationship’s data with another’s.

The Starting Point

This work surfaced a longer-horizon question: four of the platform’s largest tables were approaching the size where normal maintenance, index rebuilds, and even routine queries were starting to feel the weight of hundreds of millions of rows in a single, flat table. Partitioning was the obvious answer in principle. In practice, “just partition it” is not a strategy, it’s a direction, and the actual strategy depends entirely on how a specific table is shaped and queried.

Finding the Right Strategy Per Table, Not One Strategy for All

SELECT count(*)
FROM pg_constraint
WHERE contype = 'f'
   AND confrelid = '"GlobalEvent"'::regclass;

SELECT
    count(*) AS distinct_workspaces,
    quantile(0.5)(cnt) AS median_rows_per_workspace,
    quantile(0.95)(cnt) AS p95_rows_per_workspace,
    max(cnt) AS largest_workspace_rows,
    sum(cnt) AS total_rows
FROM (
    SELECT eventId, count() AS cnt
    FROM sandbox.event_min
    GROUP BY eventId
);

Range partitioning by date, sized to actual growth. For the two tables with no incoming dependencies and a clear time dimension, monthly range partitions were the natural fit, but not a flat monthly scheme from day one. Real row-count data showed a steady, multi-year growth ramp; the earliest months held a few hundred rows each, the most recent months held millions. A flat monthly split would have produced mostly empty early partitions and dramatically oversized recent ones. The final design uses one wide “legacy” partition to absorb the sparse early history, switching to true monthly partitions only from the point the data justifies it.

Hybrid list and hash partitioning, sized to the skew. For the tenant-keyed tables, the fix was a hybrid scheme: dedicated, individual partitions for the confirmed largest workspaces, so no single tenant’s data ever dominates a shared bucket. The long tail of ordinary workspaces is then distributed evenly across hash buckets, sized against their own, much flatter distribution.  Isolating the largest workspaces this way brought the remaining skew ratio down from over 200x to roughly 32x, a real, measured improvement, not a guess.

Zero-downtime migration, not a maintenance window. Every migration follows the same pattern: build the new partitioned table alongside the live one, attach a trigger that mirrors every insert, update, and delete into the new table in real time, then run a batched backfill for historical rows on a schedule that automatically stops itself once it detects nothing is left to copy. The live table never stops serving traffic, and the cutover itself is a single, brief transaction that simply renames the two tables and promotes the new one into place.

Guarding against no-op writes, sized to each table’s real update rate. The same real-time sync trigger can either write unconditionally or check first whether anything actually changed. That guard has a real cost, so rather than adding it everywhere by default, we checked each table’s actual update intensity before deciding.

Deciding, per table, whether a no-op write guard is worth its cost:

SELECT relname, n_tup_upd, n_live_tup,
    ROUND(n_tup_upd::numeric / NULLIF(n_live_tup, 0), 2)
        AS updates_per_live_row
FROM pg_stat_user_tables
WHERE relname = 'GlobalActivity';

One table came back at 0.29 updates per live row, low enough that the guard’s overhead wasn’t worth adding. The other three came back meaningfully higher, one as high as 7 updates per live row, confirming the guard would pay for itself many times over on those specific tables. Applying the same fix everywhere, regardless of whether the underlying table actually needed it, would have been the easier path and the wrong one.