Partitioned events table that never prunes

Pruning is a planner decision that requires the predicate on the partition key to be a recognisable range constraint on the column itself, evaluated against something the planner can fold.

• now() is stable, and PostgreSQL supports runtime pruning, so created_at >= now() - interval '7 days' is prunable - as long as the key column is compared directly.
• created_at::date >= current_date - 7 wraps the partition key in a cast. The expression is no longer a range constraint on created_at; the planner cannot map it to partition bounds, so all 25 children are scanned and the cast is evaluated row by row. That is why it is slower.

Rewrite:

SELECT ... FROM events
WHERE tenant_id = $1
  AND created_at >= date_trunc('day', now()) - interval '7 days';

Then check the three things that silently disable pruning in practice:

1. SHOW enable_partition_pruning; must be on.
2. The parameter type must match. If the driver sends $1 as text, the implicit cast on tenant_id can also poison the plan - cast explicitly at the API boundary.
3. Look at EXPLAIN for Subplans Removed on the Append node. If the number removed is 0, pruning is still off and the predicate is still non-sargable.

A unique constraint on a partitioned table must be enforceable per partition, so the constraint definition must include every partition key column. PRIMARY KEY (id) alone cannot work because nothing stops the same id appearing in two different monthly partitions. Options:

• PRIMARY KEY (id, created_at) - accepted, and it still gives you a usable (id, created_at) index per partition.
• Keep id from a sequence or UUID and accept that uniqueness is enforced by the application or by a separate lookup table.

Index strategy for the real workload:

• The dominant predicate is tenant + time range. Create, per partition, (tenant_id, created_at DESC). One composite index serves the filter, the range and the ordering; the existing created_at-only index is then usually redundant and should be dropped, because 25 partitions x 2 indexes means 50 index trees to maintain on every insert.
• Keep indexes few and wide rather than many and narrow: each additional partitioned index means a DDL operation across all children on every attach and a maintenance cost on every load.
• If the table is strictly append-only and time-ordered, consider BRIN on created_at in addition for whole-period scans, but do not replace the composite btree with it.
• Verify with EXPLAIN (ANALYZE, BUFFERS) after the change: you want a single partition, an Index Scan on the composite, and rows removed by the index condition, not by a filter.

DROP TABLE on a leaf partition is a metadata operation - fast, no bloat, no vacuum - which is exactly why partition-based retention beats DELETE. The risks are elsewhere:

1. Locking. DROP or ATTACH/DETACH takes ACCESS EXCLUSIVE on the parent, briefly blocking readers. Newer versions support DETACH PARTITION ... CONCURRENTLY, which takes a weaker lock; use it, then drop the detached table out of band. Ensure lock_timeout is set on the maintenance session so a failed attempt retries rather than queueing behind a long query and blocking everyone.
2. The DEFAULT partition is a trap. If any rows for a future month land in DEFAULT, attaching that month's new partition requires scanning DEFAULT under an ACCESS EXCLUSIVE lock, and it fails outright if conflicting rows exist. Pre-create partitions several months ahead and keep DEFAULT empty, or omit it.
3. Foreign keys and dependencies. Dropping a partition that other tables reference fails or requires cascade decisions; check dependents before the drop with a dry run in staging.
4. Archive first. Detach, verify the row count and that the data is safely copied to cold storage, then drop. Never drop straight from the parent in a single unrehearsed step.
5. Statistics and planning. After detaching, run ANALYZE on the parent; stale partition statistics produce plans that assume partitions you no longer have.