PostgreSQL Features That Buy You a Fast Performance Win

There is a category of PostgreSQL performance work that costs nothing but attention - features already in the database, already free, that most clusters never switch on or never tune. This is not about buying a bigger instance or re-architecting anything. It is about the parameters you usually miss to tune, index-only scan you are missing because your index does not cover the query, the extended statistics you have never created so the planner keeps guessing wrong about correlated columns, the partition pruning that never kicks in because the partition key is not in the predicate, and the HOT updates you are throwing away because fillfactor is still at the default. Each of these is a switch or a one-line command, and each one can move a query by an order of magnitude. This talk walks through the highest-return features in PostgreSQL ordered by effort, with a measured before and after for every one. We will cover index-only scans and how to actually get them, extended statistics and why they remain the single most underused planner feature, partitioning done so that pruning fires, parallel query and when the planner refuses to use it, JIT and the workloads where you should turn it off, and HOT updates as free write throughput. I will be equally direct about the features people expect to help that will not, so you do not spend a sprint tuning something that was never your bottleneck. You should leave with a short, prioritized list you can apply to your own cluster tomorrow.

What we will cover

• Parameters you should always tune: The configuration parameters that must always be tuned, as the default values are not sufficient.
• Index-only scans: what blocks them, and the covering-index change that unblocks them
• Extended statistics: teaching the planner about correlated columns, with real plan changes
• Partitioning and pruning: why pruning silently does not fire, and how to make it
• Parallel query: when the planner uses it, when it will not, and the settings that gate it
• JIT: where it helps, where it hurts, and how to tell which case you are in
• HOT updates and fillfactor: free write throughput in exchange for empty space, with numbers
• Prepared statements and plan caching: the generic-plan trap and how to avoid it
• What will not help you: popular tuning advice that does nothing for most workloads
• A prioritized checklist, ordered by return on effort, safe to run against production