PostgreSQL Performance Tuning: Tips & Tricks

Tip: shared_buffers = 25% of RAM

shared_buffers = 2GB  # For an 8GB server

Don't set it too high — the OS also needs memory for caching.

Gotcha: work_mem is Per-Operation

work_mem = 64MB

Each sort or hash operation gets this much memory. With 100 connections, that's 6.4GB total.

Tip: effective_cache_size = 75% of RAM

This tells the planner how much memory is available for caching. It doesn't actually allocate memory.

Gotcha: random_page_cost for SSDs

random_page_cost = 1.1  # Default is 4.0 (for spinning disks)

SSDs have similar random and sequential read speeds. Lower the cost to favor index scans.

Tip: EXPLAIN ANALYZE for Real Query Plans

EXPLAIN ANALYZE SELECT * FROM posts WHERE category_id = 1;

Shows actual execution time, not just estimates.

Gotcha: Autovacuum is Your Friend

Don't disable autovacuum. It reclaims dead tuples and updates statistics. Tune it instead:

autovacuum_vacuum_scale_factor = 0.1  # Vacuum after 10% dead tuples

Tip: pg_stat_statements Extension

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC;

Find your slowest queries.

Gotcha: Connection Overhead

Each PostgreSQL connection uses ~10MB of memory. Use PgBouncer for connection pooling if you have many connections.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Tip: work_mem Is Per Operation, Not Per Query

Setting work_mem = 64MB means each sort, hash join, or aggregate gets 64MB. A query with 4 sort operations uses 256MB. Monitor temp_files to tune this.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL performance tuning is a journey, not a destination. I've learned that the most impactful changes are: (1) setting effective_cache_size to the OS page cache size, (2) configuring work_mem per operation (not per query), and (3) monitoring temp_file_limit to catch queries spilling to disk. I start every performance investigation by checking pg_stat_activity for running queries and pg_stat_user_tables for sequential scans.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)