Postgres / planner / partitioning
Partitioning Only Helps the Queries That Name the Key
That partitioning a big table makes queries on it faster. It makes exactly one class of query faster — the ones whose WHERE clause compares the partition key to something the pruning code can evaluate — and it makes every other query and every plan measurably worse. partprune.c is explicit about the tiers: "When pruning in the planner, we only support pruning using comparisons to constants. We cannot prune on the basis of anything that's not immutable." Stable expressions such as now() and external parameters are pushed to executor-startup pruning, and exec Params from a parameterised nested loop to per-scan pruning. Anything else — `occurred_at::date = DATE '…'`, `date_trunc('month', occurred_at) = …`, a filter on a non-key column — matches no partition key at all, so every partition is planned, opened and scanned. Meanwhile the planner builds paths for every partition that survives plan-time pruning, which the documentation states plainly: "Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning." Partitioning is a data-lifecycle and locality feature that buys query speed only for queries shaped to use it.
Partition pruning is the only thing partitioning does for a SELECT, and it happens in one of three places — during planning against Const values only, at executor startup for stable expressions and external parameters, or per outer row for exec Params — so a WHERE clause that wraps the partition key in a cast or a function prunes nothing and pays for every partition instead.
Where this is already explained
- The Free Space Map: Freed Is Not the Same as Available
That once VACUUM reports the dead tuples removed, the space is back in play, so a table that keeps growing must mean vacuum is not running. Freeing space and advertising it are two separate steps. VACUUM records each page's new free space at the bottom of the map immediately, but GetPageWithFreeSpace descends from the root, and the root is refreshed only by FreeSpaceMapVacuumRange — once per index-vacuum cycle and once when the scan ends. On an 8 GB table at default settings that is a single refresh 233 seconds in, and every row inserted before it extends the file. On-access pruning never refreshes the map at all, deliberately.
- Getting the Disk Back: VACUUM FULL, pg_repack and What They Really Lock
That VACUUM FULL costs you exactly as long as the rewrite takes, so a quiet window makes it safe, and that pg_repack is the lock-free version. Both halves are wrong. The ACCESS EXCLUSIVE request queues behind whatever query is already running, and because a request that conflicts with a waiting request must also wait, every query arriving after it queues too — so the outage starts before the rewrite does and lasts the running query plus the rewrite. pg_repack still needs ACCESS EXCLUSIVE twice, resolves the wait by cancelling and then terminating your backends, and needs twice the size of the table in free disk, which is the one thing you do not have.
2 published lessons depend on this concept, which is what moves it up the writing queue. Nothing is hidden behind this page — it has not been written.