PostgreSQL Performance Tuning

PostgreSQL performance tuning starts with five settings: shared_buffers, work_mem, the per-table autovacuum thresholds and cost limit, max_connections (fixed with a pooler, not a bigger number) and idle_in_transaction_session_timeout. Set each from measurement. Rank queries by total execution time in pg_stat_statements first, then change one setting at a time.

The expensive failures we see are rarely a missing setting. For an insurance services provider, autovacuum on the largest tables was being cancelled and restarted, long analytics sessions and idle-in-transaction connections pinned the oldest-transaction horizon, and cost-delay settings inherited from a much smaller deployment throttled autovacuum to a fraction of the disks' capacity. The database was within a few tens of millions of transactions of the point where PostgreSQL protects itself by going read-only. AceMQ froze the oldest tables first with targeted manual vacuums, ended the sessions pinning the horizon, retuned the cost settings to the hardware and set per-table thresholds. The cluster never went read-only. The write-up is in the wraparound remediation use case.

A second pattern: a retail technology provider saw response times drift upward over eighteen months and was about to buy hardware. An assessment ranked queries by total time, measured table and index bloat directly and profiled connections at the pooler. The constraint was query and bloat behavior, not capacity. The refresh was deferred and response times moved back toward their earlier baseline. See the PostgreSQL performance assessment.

Add pg_stat_statements to shared_preload_libraries (a restart is required), run CREATE EXTENSION pg_stat_statements, and sort by total_exec_time, not by the slowest single call. A 3 ms query run 40 million times a day outranks a report that takes four seconds once. Save the top 50 as a baseline before any change, and read plans with EXPLAIN (ANALYZE, BUFFERS). The pg_stat_statements documentation lists the columns.

Defaults are from the PostgreSQL 18 documentation. hash_mem_multiplier defaults to 1.0 on PostgreSQL 14 and 2.0 from 15, and autovacuum_vacuum_max_threshold exists only from 18.

This is an illustrative calculation, not a client result. Assume a dedicated 64 GB server, max_connections = 200, up to four sort or hash steps in a query, and the default hash_mem_multiplier of 2.0 (PostgreSQL 15 and later).

The work_mem budget is the memory left after shared_buffers and headroom, divided by connections × steps × the multiplier: (64 − 16 − 24) GB = 24,576 MB, and 24,576 ÷ (200 × 4 × 2.0) = 15.36, so 15MB. The 24 GB of headroom for the operating system, its page cache, maintenance and other processes is an assumption, not a documented figure. This is a worst case in which every connection runs four hash steps at once. A pooler that holds 50 active connections gives 24,576 ÷ (50 × 4 × 2.0) = 61 MB.

These are starting values to test, not targets. Compare temp_blks_written in pg_stat_statements and the plans from EXPLAIN (ANALYZE, BUFFERS) before and after, as above.

Autovacuum starts on a table when dead tuples exceed autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the table size. On PostgreSQL 17 and earlier a one-billion-row table therefore waits for about 200 million dead tuples; 18 caps the wait at 100 million. Override it per table:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 1000);

The values are an example, not a recommendation: size them from the table's churn. For wraparound, the routine vacuuming documentation says PostgreSQL warns when the oldest IDs are 40 million transactions from the limit and refuses new IDs below three million. Watch the age:

SELECT datname, age(datfrozenxid) FROM pg_database;

If age keeps rising, check the three blockers the documentation names: prepared transactions in pg_prepared_xacts, long-running transactions in pg_stat_activity and stale replication slots in pg_replication_slots. Once the server has stopped assigning IDs, run a database-wide VACUUM as a superuser; the documentation says not to use VACUUM FULL or VACUUM FREEZE in that situation.

Vacuum that cannot remove dead tuples leaves bloat in tables and indexes. Check pg_stat_user_indexes for indexes with idx_scan of zero across a full business cycle, and drop the redundant ones; they cost writes and vacuum time for nothing. Rebuild a bloated B-tree with REINDEX INDEX CONCURRENTLY, which does not block writes but takes longer, and drop the leftover INVALID index if it fails. See the REINDEX documentation. In the retail technology assessment, redundant indexes and indexes the planner never chose were reviewed alongside the bloat measurements.

max_connections is typically 100, and the documentation warns that a complex query can run several sort and hash steps at once, so total memory can be many times work_mem. Hundreds of application connections multiply that. Put a pooler such as PgBouncer in front and keep the server limit near what the CPUs can serve. In the retail assessment the application opened far more connections than the server had memory to serve efficiently, which was only visible when connections were profiled at the pooler.

PostgreSQL 14's final release is on 12 November 2026, per the versioning policy. The 17 release notes describe a new vacuum memory structure and say vacuum is no longer limited to one gigabyte when maintenance_work_mem or autovacuum_work_mem is higher. The 18 release notes add asynchronous I/O, B-tree skip scans and eager freezing. Neither gives a speedup figure, so measure your own workload:

• Save pg_stat_statements and wraparound age before the upgrade.
• After pg_upgrade on 17 or earlier, regenerate planner statistics with vacuumdb --all --analyze-only; the 17 documentation says they are not transferred. PostgreSQL 18 preserves them, but not extended statistics.
• Diff postgresql.conf against the old server so your autovacuum overrides survived.
• Replay the same queries and compare calls, mean time and temp files per query ID.

Bring in an outside engineer when a wraparound warning appears, autovacuum is cancelled repeatedly, a replication slot is holding back WAL, or a hardware purchase is proposed without evidence. AceMQ's PostgreSQL support is 24/7 with a named senior engineer, for self-managed and cloud-hosted PostgreSQL. For a one-off review, our PostgreSQL consulting starts with a PostgreSQL performance assessment. Version dates for 14 and later are in PostgreSQL end of life dates by version.

• PostgreSQL 18 documentation: Resource Consumption (shared_buffers, work_mem, hash_mem_multiplier, maintenance_work_mem)
• PostgreSQL 18 documentation: Automatic Vacuuming
• PostgreSQL 18 documentation: Routine Vacuuming (transaction ID wraparound)
• PostgreSQL 18 documentation: Query Planning (effective_cache_size)
• PostgreSQL 18 documentation: Connections and Authentication (max_connections)
• PostgreSQL 18 documentation: Client Connection Defaults (idle_in_transaction_session_timeout)
• PostgreSQL 18 documentation: pg_stat_statements
• PostgreSQL 18 documentation: REINDEX
• PostgreSQL 17 documentation: pg_upgrade
• PostgreSQL 14 documentation: Resource Consumption (hash_mem_multiplier default)
• PostgreSQL 17 release notes
• PostgreSQL 18 release notes
• PostgreSQL: Versioning policy, checked 22 October 2026