When to Partition in Postgres, and When Not To

TL;DR

• Don't partition a Postgres table just because it's big. On Aurora, the shared storage architecture already mitigates some of the classic physical I/O pain.
• Planning time is your new enemy. With hundreds of partitions, the query planner's overhead can exceed the execution time savings from partition pruning.
• The only bulletproof reasons to partition are: fast data archival (DROP PARTITION), isolating lock contention, targeted vacuuming, and enabling partition-wise joins. All are operational, not just performance-related.

---

I see the same pattern every quarter. A team's primary table crosses 100 million, or maybe 500 million, rows. Panic sets in. Someone reads a blog post from 2012, and suddenly PARTITION BY RANGE (created_at) is the solution to all life's problems. A few weeks later, they're in my office (virtually) wondering why their p99 query latency went up, pg_dump takes twice as long, and their planning times are through the roof.

Most advice on Postgres partitioning is dangerously outdated. It was written for a world of spinning disks and self-managed servers, where splitting a monolithic 2TB table file into 200 smaller 10GB files was a massive physical I/O win. On Aurora Postgres, that premise is flawed. Aurora's storage layer isn't a simple filesystem; it's a log-structured, distributed service that stripes your data across hundreds of storage nodes in 10GB protection groups. The concept of a single table being one massive, contiguous file to be read sequentially doesn't really exist. While partition pruning still reduces the logical I/O (fewer 8KB pages to fetch into the buffer cache), the physical I/O argument is severely weakened.

The real cost of naive partitioning isn't on disk; it's inside the planner.

The Planner's Burden

When you run a query against a partitioned table, the Postgres planner doesn't magically know which partitions to scan. It has to check. For each partition, it examines the table's partition bound constraints (stored in pg_class.relpartbound) against the WHERE clause of your query. If the predicate and the bounds overlap, the partition is added to the plan. If not, it's pruned.

This process is not free. It involves catalog lookups and CPU cycles. With a dozen partitions, it's negligible. With 500 daily partitions spanning over a year, this planning phase can become the most expensive part of your query.

Consider this simplified example on a heavily partitioned events table:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM events 
WHERE event_time >= '2023-10-26 00:00:00' 
  AND event_time < '2023-10-27 00:00:00' 
  AND user_id = 12345;

Planning Time: 85.341 ms
Execution Time: 1.152 ms

-> Append (cost=0.00..45.12 rows=1 width=128) (actual time=0.05..0.21 rows=10) 
    -> Index Scan using events_2023_10_26_user_id_idx on events_2023_10_26 ...

Look at those timings. 85 milliseconds to plan the query, and 1 millisecond to execute it. The planner spent 98% of the total time figuring out it only needed to scan one partition. This is what I call death by a thousand papercuts. Your simple, fast queries become slow because of the upfront planning tax. At Oracle, we solved this class of problem with Zonemaps—lightweight metadata storing the min/max values for columns in a data block. The optimizer could check this metadata instantly without the overhead of inspecting every partition's formal definition. Postgres's declarative partitioning isn't there yet; its pruning is more heavyweight.

The Four Real Reasons to Partition

If not for raw size, then when should you partition? Only when you can leverage one of these four operational benefits.

1. Bulk Data Deletion / Archival The absolute, number-one reason. Deleting millions of old rows with a DELETE FROM events WHERE created_at < 'some-date' is catastrophic. It generates immense WAL, bloats your table and indexes with dead tuples, and puts massive pressure on Autovacuum to clean it all up. The table remains huge and slow. In contrast, if your table is partitioned by RANGE(created_at), deleting that old data is a single, non-blocking, metadata-only command: DROP PARTITION events_old;. It's instantaneous. The storage is reclaimed without any VACUUM or WAL overhead. This is the only sane way to manage time-series data with a fixed retention window.

2. Lock Contention Isolation Under the hood, each partition is a separate table with its own relfilenode. This means concurrent operations on different partitions are less likely to conflict. Imagine an IoT workload inserting data for the current hour into measurements_2023_10_26_1500 while a backfill job is loading historical data into measurements_2023_01_15_1000. These operations won't be competing for the same page locks or relation-level locks, reducing contention and improving throughput on write-heavy tables. This is especially useful for isolating a 'hot' partition from 'cold' ones.

3. Scoped and Parallel Vacuum Autovacuum can process partitions individually. For a 5TB table with append-only data in old partitions but high update/delete churn in the most recent one, this is a lifesaver. Autovacuum can aggressively clean the small, hot partition frequently without needing to scan the entire 5TB of historical data on every run. You can even tune autovacuum storage parameters on a per-partition basis. Furthermore, with multiple partitions, VACUUM operations can run in parallel by different workers, significantly speeding up maintenance on very large tables.

4. Partition-Wise Joins & Aggregates This is an advanced but powerful technique. If you have two very large tables (e.g., orders and line_items) and you partition them both on the same key with identical bounds (e.g., PARTITION BY RANGE(order_date)), Postgres can perform a partition-wise join. Instead of one massive hash join that spills to disk, the optimizer can break the problem down, joining orders_2023_10 with line_items_2023_10, then orders_2023_09 with line_items_2023_09, and so on. These smaller joins are more likely to fit in work_mem and can be executed in parallel. The performance gain can be an order of magnitude, but it requires careful, synchronized schema design.

The Irreversible Mistake

All those benefits depend entirely on choosing the right partition key. And if you get it wrong on a multi-terabyte table, you can't just git revert. It's a near-permanent liability.

Let's say you partition your 10TB analytics table by HASH(user_id). Then, your product team asks for a report of all events from last month. Your query is WHERE created_at > '...'. Since created_at provides no information about user_id, the planner has no choice but to scan every single partition. You've paid the price of partitioning—higher planning time, more complex management—and gotten zero of the pruning benefits. You just made your queries slower.

Repartitioning a 10TB table is not a trivial maintenance task. It's a months-long migration project. You'll need to create a new partitioned table, set up triggers or a logical replication pipeline to keep it in sync with the old one, copy all the historical data, and then perform a risky, coordinated cutover. It's one of the highest-stakes mistakes a data platform team can make, with massive cost and downtime implications.

Unlike an application code bug, a bad schema decision at this scale is effectively permanent. Prevention is the only strategy.

What DeepSQL does about this

DeepSQL was built to prevent these irreversible database liabilities. Instead of guessing, we analyze your actual workload by inspecting pg_stat_statements and query history to identify the true predicate columns and join patterns. Our engine correlates this with vacuum stats, table growth rates, and lock waits to model the cost/benefit of a partitioning strategy. It only recommends partitioning if the analysis proves that your dominant query patterns align with a proposed key and the operational gains (like retention) are clear. If the analysis shows that partitioning would increase average planning time without significant execution gains, it will explicitly advise against it, preventing a costly mistake before it happens.