Partitioning a 940-Million-Row Multi-Tenant Invoice Table Online

The proposed scheme is probably backwards for this workload, and the cost is easy to state.

What pruning requires. PostgreSQL prunes a partition only when the query constrains that partition key at plan time. If created_at is the top-level split, a query that filters only on tenant_id must attach and scan every monthly partition - after three years that is 36 top-level partitions, each with hash children. The tenant-facing invoice list is typically "all invoices for this tenant, most recent first", which often has no date predicate or only an open-ended one. You would replace one 940-million-row scan with 36 partial scans of the same total size plus planning overhead. That is a regression, not a fix.

The inversion. Make tenant_id the top-level partition via hash (32 or 64 partitions, chosen so each is comfortably smaller than memory-resident working set), then range-partition each hash partition by month. Now:

• Tenant-filtered queries prune to exactly one hash partition, then to the months the query names - typically one or two.
• Even distribution: hash partitioning spreads the anchor tenant's 41% across partitions, whereas time-only partitioning keeps the largest tenant's writes concentrated in the newest month's partition and concentrates vacuum there.
• Retention and archival stay available at the month level inside each hash partition.

Constraints to check before committing. Every unique constraint and primary key on a partitioned table must include the partition key, so the PK becomes (tenant_id, id) and any global uniqueness assumption breaks - audit which code relies on id alone. Foreign keys referencing invoice_line need the same treatment. Foreign keys from invoice_line are fine but add per-partition overhead.

Partition count discipline. Total partitions multiply: 32 hash x 24 months = 768. Planning time and catalog size grow with that number, and autovacuum has one worker per partition. Prefer 16-32 hash partitions and keep only 13-18 months as live children, detaching older months to archive tables rather than keeping hundreds of empty partitions alive.

Migration sequence.

1. Create the partitioned table invoice_line_p with identical columns plus the new PK, and create partitions ahead of time. Do it in a maintenance-friendly order: newest months first, since those carry the write traffic.
2. Install a trigger on the old table that writes to the new table (or, better, route application writes to the new table and keep the trigger only as a safety net for stragglers). A trigger adds per-write overhead; measure it and keep the window short.
3. Backfill in batches ordered by tenant, smallest tenants first, 10k-50k rows per statement with a commit between batches. Do the anchor tenant last so it does not monopolise I/O for weeks. Track progress in a control table so a restart resumes rather than rescans. Watch replica lag and pause if it exceeds a few seconds.
4. Catch up with logical replication or the trigger, verify row counts and checksums per partition.
5. Cut over with a short transaction: take ACCESS EXCLUSIVE on the old table, rename invoice_line to invoice_line_old and invoice_line_p to invoice_line, commit. This is sub-second if no long-running transaction is queued ahead of it. Set lock_timeout = 3s and retry the rename in a loop so a lock conflict aborts the attempt rather than blocking the platform.
6. Keep the old table read-only for a defined rollback period, then drop it. Do not drop it the same night.

Prepared statements. The planner chooses a generic plan after five executions by default, which cannot prune on a parameterised tenant_id. Set plan_cache_mode = force_custom_plan for the tenant-facing role, or have the ORM append a constant predicate per request. Also raise plan_cache_mode awareness in code review: any query with a LIMIT over a partitioned table is vulnerable to slow generic plans. Test with EXPLAIN (ANALYZE) under realistic parameters, not with literals.

Autovacuum. Per-partition settings: lower autovacuum_vacuum_scale_factor to about 0.02 and autovacuum_analyze_scale_factor to 0.01 on hot partitions, and raise autovacuum_max_workers so small partitions are not starved by the big ones. Confirm with pg_stat_user_tables that dead tuples stay bounded per partition.

Analytics. Move those jobs to a physically separate reporting cluster fed by logical replication. Long lock-holding scans on the OLTP cluster will otherwise collide with the cutover transaction and with autovacuum, and no partitioning scheme fixes that.

Pruning succeeding but latency not improving means the cost is inside the partition, so work through the layers.

Hypothesis 1 - the index is not selective for that tenant. If the query is WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 50 and the index is (tenant_id, created_at), this should be a fast index scan. If the index was carried over as (tenant_id, id) or (created_at), the plan becomes a bitmap heap scan or a sort of the tenant's full partition. Check with EXPLAIN (ANALYZE, BUFFERS) and look at rows-removed-by-filter. Fix: a composite index matching the filter and sort order exactly, built concurrently.

Hypothesis 2 - the partition is simply large. 41% of 940M is about 385M rows; split across 32 hash partitions that is still 12M rows per partition, and the tenant's own rows all land in one of them (by definition of hash). If the query shape demands scanning a large fraction of that tenant's rows, no prune helps. Consider further sub-partitioning by month for that tenant's hot data, or a covered index that satisfies the query without heap access (INCLUDE columns).

Hypothesis 3 - tuple bloat inside the partition. A partition that receives 41% of updates may be the one partition autovacuum cannot keep up with. pg_stat_user_tables.n_dead_tup and last_autovacuum on that specific partition will show it. If dead tuples are high, the fix is vacuum tuning or a lower fillfactor on that partition, not a query rewrite.

Hypothesis 4 - generic plan or row estimate. With 32 partitions, planning takes longer and an underestimated row count can flip a nested loop into a disaster. Inspect pg_stat_statements for that query under the tenant role: if plan_cache_mode is still auto, force custom plans and compare.

Hypothesis 5 - contention, not scan cost. If p95 is slow but median is fine, look at lock waits (pg_locks), connection pool saturation, and I/O latency on the volume holding the hot partition. One hot partition concentrates writes; spreading the anchor tenant across several hash partitions is not possible without changing the hash key, so the alternative is a per-tenant sub-scheme for the top few tenants.

Narrow it in that order: plan first, statistics second, vacuum third, contention last. Capture the actual plan from the tenant's role with real parameters before assuming anything - the common error here is optimising the query the team wrote rather than the query the ORM sends.