Six months after launch, one partition ballooned to 800 million rows. The growth forecast missed the mark. Every fix demanded a maintenance window the team dreaded. This scenario plays out in production systems far too often.
Ruslan Tolkachev laid out a sharper approach in his Explain Analyze post. Partition by the primary key. Let a lightweight background service watch sizes and adjust boundaries based on actual data growth. Queries stay clean. Pruning happens automatically. The partition column never leaks into application logic.
“Partition by the primary key, not by created_at, and let a background service manage boundaries based on observed growth,” Tolkachev wrote. The pattern extends to hash and list schemes with adjusted operations.
His insight lands at a moment when organizations wrestle with tables measured in billions of rows. Recent analyses echo the pressure. A January 2026 DataExpert analysis showed partitioning cuts scanned data, speeds queries and trims cloud bills when pruning works. Yet poor key choice turns the feature into overhead.
Consider the classic mistake. Teams partition orders by month on created_at. The database demands the partition key sit inside the primary key or any unique constraint. So PRIMARY KEY (id) becomes PRIMARY KEY (id, created_at).
Id loses its uniqueness guarantee. The optimizer shifts from const or eq_ref access to ref lookups. Cardinality estimates weaken. Plans degrade across the query. Point lookups that once hit one row now scan potentially many. Applications must inject dates everywhere to restore performance. A storage choice mutates into a query contract.
But. The real pain surfaces in pruning. A query like SELECT * FROM orders WHERE id = 12345 scans all 36 monthly partitions if the key is time-based. No error appears. Just unexplained slowness. Engineers add date filters to dashboards, audit pages, admin tools, migration scripts. Lint rules follow. Code reviews nag about the missing filter.
Tolkachev’s alternative keeps PRIMARY KEY (id) intact. Range partitioning uses the auto-incrementing ID. Every lookup already filters on id. Pruning occurs for free. Time-based retention still works. A service queries SELECT MAX(id) FROM orders WHERE created_at < NOW() - INTERVAL '1' YEAR to derive safe drop boundaries at runtime.
The service monitors the active catch-all partition holding MAXVALUE. When it grows too large, it issues ALTER TABLE ... REORGANIZE PARTITION. The operation stays metadata-only if the new partition starts empty. Old partitions drop cleanly. Concurrency guards prevent races. Time alignment derives from live data rather than brittle upfront projections.
This design avoids the six-month surprise of an overloaded future partition. No more guessing row counts per month. Observation drives splits. Retention policy drives drops.
Database vendors push time-based defaults. Tools such as pg_partman and TimescaleDB assume a time column in the key. They simplify some cases yet import the leakage problem Tolkachev flags. For strictly time-scoped workloads the tools shine. Mixed OLTP patterns demand the primary-key approach.
Microsoft's March 2026 Well-Architected guidance stresses matching strategy to access patterns. Horizontal partitioning by row ranges or hashes improves scalability when queries align. Vertical splits by column usage reduce I/O for hot fields. The document warns against one-size-fits-all schemes.
Industry voices in 2025 and 2026 reinforce selectivity. A Spinnaker Support overview from April 2025 notes partitioning boosts availability and manageability yet carries limits. Large tables need careful pruning guarantees. An Aerospike blog from September 2025 highlights balancing distribution against data locality to dodge hotspots.
PostgreSQL's own documentation, updated for version 18, cautions that poor partition column choice harms planning and execution time. The manual advises analyzing which column appears in queries that matter most. Sound familiar?
The Service That Watches
Implementation looks straightforward once the key decision locks in. A cron job or queue worker polls partition sizes. Thresholds trigger action. For range on ID, split the future partition before it overflows. For hash, monitor skew and reshape only on first imbalance. List partitions promote values from a default bucket once they dominate.
Guards matter. Schema locks during reorganization must stay brief. The service runs at low priority. It logs every boundary change for audit. Tests simulate years of growth in minutes to validate logic.
Teams that adopt this pattern report fewer late-night incidents. Queries behave as they did before partitioning. Indexes stay effective. Joins avoid surprises. Maintenance becomes predictable background noise rather than calendar-blocking events.
Of course trade-offs exist. Non-key queries still scan every partition. The service adds operational surface. Very small tables gain nothing. Pure analytical workloads with strong time filters may prefer vendor time-partition tools.
Yet for transactional systems that grow without bound, the primary-key-plus-service model removes a common source of technical debt. It treats partitioning as data management first, query optimization second. The key stays where it belongs: in the schema, not scattered through application code.
Recent discussions on X underscore the interest. Engineers continue trading stories of partitioning gone wrong and the relief when automation takes over boundary work. One May 2026 post highlighted a Postgres cookbook drawn from three years of production mistakes, signaling the topic's enduring relevance.
The question Tolkachev poses cuts through the noise. What column already appears in every query that matters? Answer that, design around it, automate the rest. The table manages itself. Engineers sleep better.
WebProNews is an iEntry Publication