Partitioning Mistakes That Make PostgreSQL Queries Slower

Teams partition a table expecting a speedup and sometimes get the opposite: a dashboard query that took 40 ms now takes 400 ms, planning time doubles and a lookup by id probes sixty indexes. None of this means partitioning is broken. It means one of a small number of design mistakes was made, each recognizable in an EXPLAIN output and each with a fix. This article lists them in order of how often they occur, what the plan shows and what to do. It belongs to the series that table partitioning in PostgreSQL that actually pays off opens.

Mistake 1: the key is not in the filters

The table is partitioned by month, and the busiest query is "orders for customer 42". There is no date in the filter.

Append  (actual time=0.3..9.8 rows=37 loops=1)
  ->  Index Scan using orders_2024_01_customer_idx on orders_2024_01 ...
  ->  Index Scan using orders_2024_02_customer_idx on orders_2024_02 ...
  ... (58 more partitions)

Sixty index probes where a single index would do one. Each is cheap (a few page reads, mostly cached) but together with planning for sixty partitions the query costs ten to twenty times what it did. The fix options:

• Add a time condition where the application can: "customer 42's orders in the last two years" prunes to 24 partitions at most, and "last 90 days" to four.

• Choose a different key if customer-centric queries dominate: list or hash by customer, with time as a secondary level if retention matters.

• Keep the table unpartitioned and handle retention another way.

Diagnose by listing the key queries and checking each for a key filter. If the majority lack one, the key is wrong.

Mistake 2: too many partitions

Daily partitions kept for ten years make 3,650 tables. Each query's planning step touches the partition descriptor, and plans that cannot prune completely multiply the work. Symptoms: Planning Time of tens of milliseconds for a simple query, high memory use in backends (the relcache and plan memory scale with partitions), slow pg_dump, slow catalog operations, slow autovacuum launching. Examples:

Planning Time: 38.2 ms
Execution Time: 1.1 ms

Fixes: use coarser intervals (monthly, then yearly for old data), keep fewer years online by archiving, or merge old partitions: detach several small ones, load them into one larger table with the right range and attach. Aim for a total in the low hundreds for tables on hot paths. Prepared statements amortize planning, so check that your driver actually reuses them.

Mistake 3: lookups by id probe everything

With PRIMARY KEY (id, occurred_at), WHERE id = 42 has no partition key, so every partition's unique index is probed. The cost scales with the partition count. For tables where point lookups by id are frequent (an API fetching one record), this is a serious regression, and no tuning fixes it. Solutions: carry the timestamp with the id in the API (a composite identifier), use time-ordered ids so the partition can be derived, keep a mapping table, or do not partition this table. See indexes and primary keys on partitioned tables.

Mistake 4: conditions that defeat pruning

The design is right and the queries are not: WHERE date(occurred_at) = '2026-04-12', WHERE occurred_at::text LIKE '2026-04%', a comparison of a timestamptz key with a timestamp constant in a way that adds a cast on the column, or a condition buried in a materialized CTE. The plan shows every partition. Rewrite as range comparisons on the bare column with matching types. Add a test that runs EXPLAIN on key queries and asserts the number of partitions scanned, so a refactor cannot silently undo pruning. Details in partition pruning in PostgreSQL and checking it works.

Mistake 5: tiny partitions

A partition of a few megabytes costs the same planning and catalog overhead as one of 10 GB. A system that partitions per tenant for thousands of small tenants creates thousands of near-empty tables. They fragment cache, multiply autovacuum work and slow every cross-tenant query. Fix: put small tenants in a shared default or hash group, and give only the large ones their own partition. Or use a different isolation approach (row-level security with a tenant column and a composite index).

Mistake 6: updating the partition key

An UPDATE that changes the key to a value in a different partition moves the row, executed as delete from one and insert into the other. It works (since version 11) and is costly: two writes, dead tuple, index updates and locks on both partitions. A design where the key changes often (for example, partitioning by status that goes pending, processing, done) is a misuse. Partition by an immutable attribute (creation time, tenant), and index mutable attributes. For queues, a partial index on the pending subset serves better than partitions by status.

Mistake 7: unnecessary joins across many partitions

Joining two partitioned tables without partition-wise joins enabled, or joining a partitioned table to itself on a non-key column, can produce plans with thousands of scan nodes. Planning time and memory explode with partition counts squared in the worst case. Enable enable_partitionwise_join only after testing, and make both tables partition the same way on the join key. Otherwise, filter each side by the key range first so fewer partitions participate.

Mistake 8: generic plans without run-time pruning

A prepared statement planned generically includes every partition in the plan, and pruning happens at execution. This works well, but the planner has no value to base its choices on, so it may choose a plan that is poor for a specific range. Compare EXPLAIN (GENERIC_PLAN) with the custom plan. If the generic plan is much worse, set plan_cache_mode = force_custom_plan for the role so each execution plans with the actual parameters, accepting the planning cost.

Mistake 9: no indexes inside partitions

New partitions get the parent's indexes automatically, but partitions created by other means (attached tables, old scripts) may not. A partition missing an index becomes a sequential scan in a plan that looked fine. Check that every partition has the same indexes:

SELECT c.relname AS partition, count(i.indexrelid) AS index_count
FROM pg_inherits h
JOIN pg_class c ON c.oid = h.inhrelid
LEFT JOIN pg_index i ON i.indrelid = c.oid
WHERE h.inhparent = 'event'::regclass
GROUP BY c.relname ORDER BY index_count, c.relname;

Partitions with a lower count than their siblings are the suspects.

Mistake 10: stale statistics on the parent

Autovacuum analyzes each partition but not the partitioned parent, whose statistics matter for plans that cover several partitions. After a big load, run ANALYZE event; on the parent as well as the partitions. Without parent statistics, estimates for queries across partitions can be poor, as in ANALYZE and why statistics go stale in PostgreSQL plans.

Measuring whether partitioning pays

Compare a partitioned copy with the original on the same data, for the same set of queries, in three ways: latency percentiles for the top ten queries, planning time and the maintenance operations (retention, vacuum of the hot slice, index build). If the partitioned version is slower for the important queries and the gains in maintenance are not worth it, the design is a net loss. Be honest in the comparison: include the cost of the lifecycle jobs and the monitoring.

When the design should be undone

Undo partitioning when pruning does not apply to most important queries and cannot be made to, when the key forces awkward lookups the application cannot change, when partition counts are beyond what planning can bear and consolidating does not fix it, when the lifecycle operations are a burden the team cannot sustain, or when the table has shrunk to a size where none of the benefits apply. Merging back is a migration like the one in moving an existing PostgreSQL table to partitions safely, in reverse: build the single table, copy, cut over. It is a legitimate outcome, and cheaper than living with a slower system. Compare alternatives, such as BRIN or partial indexes, before the next attempt.

What to do next

List your partitioned tables and, for each, the number of partitions, the share of important queries with a key filter and the planning time of the main query. A table that fails two of the three deserves a review. The manual's discussion is in partitioning best practices and the planner settings in planner method configuration. Next, start the replication cluster with replication in PostgreSQL from streaming to logical.