Postgres Partitioning: Patterns That Actually Help and the Ones That Don't

Partitioning is the Postgres feature most likely to be applied for the wrong reason. The reflexive narrative is that large tables need partitioning, and once a table crosses some threshold of row count or disk size, partitioning is the right next move. The honest framework is workload-shaped rather than size-shaped: certain access patterns benefit dramatically from partitioning, certain access patterns are made strictly worse by it, and the operational complexity it adds is permanent and non-trivial. The decision to partition should follow from query patterns, not from a row-count threshold.

This post covers what declarative partitioning actually does at the planner level, the three patterns where it pays for its complexity, the patterns where it backfires, the migration cost that compounds with table size, and the maintenance discipline that determines whether partitioning helps or quietly degrades over time.

What partitioning actually does

Postgres declarative partitioning, available since version 10 and substantially improved through version 16, replaces a single table with a partitioned table that has child tables holding subsets of rows. Queries against the parent table are routed by the planner to relevant child tables based on the partition key. The key benefit is partition pruning: queries with a WHERE clause on the partition key can skip entire child tables, both at planning time (constant pruning) and at execution time (runtime pruning). This is the source of essentially all of partitioning's performance advantages, and queries that do not benefit from pruning do not benefit from partitioning.

The three partition strategies are list, range, and hash. Range is the most common and most useful — partitioning by a date column with monthly or weekly partitions is the textbook case. List partitioning is useful for natural categorical splits like region or tenant when the cardinality is small and stable. Hash partitioning distributes evenly without a natural key but loses pruning benefits because random keys cannot be locked to specific partitions in queries.

The three patterns where partitioning earns its complexity

The first pattern is time-series data with retention policies, where old partitions need to be dropped wholesale. Dropping a partition is essentially free compared to a DELETE that processes individual rows. A 100GB time-series table with monthly partitions can drop a 10GB month in milliseconds; the same DELETE on an unpartitioned table can run for hours and produce vacuum bloat that takes days to recover from. This is the canonical case for partitioning, and it is the one that justifies the operational cost most cleanly.

The second pattern is workloads where most queries filter on a small range of the partition key, and the partition key has high enough cardinality that pruning eliminates substantial work. A user-events table partitioned by month, queried predominantly for the last 7 days, can prune to one or two partitions and skip dozens of others. The query plan shrinks proportionally; index sizes per partition are smaller and fit in shared buffers more easily.

The third pattern is when individual partitions need different physical configurations — different fillfactor, different tablespace, or different index sets. The recent partitions might be in a fast SSD tablespace with all indexes for write efficiency; old partitions might be in cheaper storage with only the indexes needed for analytical queries. This is genuinely useful for cost-optimized infrastructures with mixed workloads.

The patterns where partitioning makes things worse

Workloads where queries do not filter on the partition key get strictly worse. Every query has to consult the planner about which partitions to scan, and the answer is always all of them. The constant-time per-partition overhead in the planner becomes O(N) in the number of partitions, and tables with hundreds of partitions can spend a meaningful fraction of query time deciding which partitions to read before reading anything. The accumulated overhead of planning across many partitions is one of the most common causes of unexpected performance regression after partitioning.

Foreign key constraints across partitions become operationally awkward. Postgres does not allow foreign keys from regular tables to reference specific partitions; the reference must be to the partitioned parent. This works correctly but the constraint cannot be declared on a per-partition basis, and dropping an old partition with active foreign-key references requires explicit handling.

Unique constraints across the entire partitioned table require the partition key to be part of the constraint. A unique constraint on email_address alone is impossible if the table is partitioned on user_id; the constraint must be on (user_id, email_address). For tables with natural unique keys that are not the partition key, partitioning forces a schema change that may compromise data integrity guarantees.

The migration cost that compounds with table size

Migrating an existing unpartitioned table to a partitioned schema is one of the more expensive schema changes in Postgres. The standard approach involves creating the partitioned table with empty partitions, dual-writing to both tables for a period, copying historical data in chunks, validating consistency, and switching reads to the new table — all while the original table continues to receive traffic. For small tables this is straightforward; for tables of 100GB or more this is a multi-week project with real risk of corruption if the dual-write logic has bugs.

The lesson is that the time to decide on partitioning is when the table is small. The signals that suggest partitioning will be needed eventually are: the table is time-series shaped, the query patterns predominantly filter on time, and the retention policy is bounded. Adopting partitioning when the table is empty or small is essentially free; adopting it later costs an order of magnitude more than the original implementation.

Maintenance discipline that determines long-term success

Partitioned tables require maintenance that unpartitioned tables do not. New partitions need to be created ahead of time — pg_partman is the standard tool, but custom scripts work fine for small numbers of partitions. Old partitions need to be dropped or archived on schedule. Index creation across partitions needs to be coordinated, because CONCURRENTLY does not extend cleanly to partitioned tables in older Postgres versions and requires a partition-by-partition approach.

The pg_partman + cron pattern handles most of the maintenance for time-range partitioning. The discipline is to set retention policies in pg_partman.config and let the maintenance run as scheduled. The failure mode is operational drift: pg_partman silently fails for some reason, partitions stop being created, and writes start failing because there is no partition for the new data. Monitoring partition counts and most-recent-partition age catches this; aggressive alerting on either is the load-bearing observability for partitioned systems.

What we do across the four products

The four products run on SQLite, where partitioning takes a different shape — multiple databases attached via ATTACH DATABASE rather than declarative partitioning. WebhookVault is the most likely candidate for partition-style organization because of its time-series shape and 7-day retention, but the volume is well below where retention via DELETE becomes problematic; a single table with a partial index on captured_at handles current load comfortably. CronPing, DocuMint, and FlagBit have non-time-series core tables where partitioning would help nothing and complicate everything.

If any of the four products migrates to Postgres at scale, the partitioning question would be revisited specifically for WebhookVault's request capture table, with monthly range partitions and a 90-day retention policy as the likely starting point. The other tables would remain unpartitioned, because the access patterns do not benefit from pruning and the maintenance overhead would be pure cost.

The deeper point

Partitioning is a Postgres feature that exists because some workloads genuinely need it, not because every workload should adopt it. The decision is workload-shaped: time-series data with bounded retention and predominantly time-filtered queries is the textbook case; workloads outside that pattern usually do not benefit. The default for tables that do not match the textbook case should be no partitioning, even when the table is large, because the operational and planner costs of partitioning are real and the benefits do not appear unless the access patterns line up with the partition key.

The discipline that distinguishes successful partitioning adoptions from regretted ones is asking the question pruning-first: do most queries filter on the proposed partition key, and would pruning eliminate substantial work. If the answer is yes, partitioning helps. If the answer is no, partitioning adds cost without benefit, and the right move is better indexing, query optimization, or a different tool entirely.