$gnu_today Open Source, Linux & Programming Since 2010
Home / Open Source / PostgreSQL Performance Tuning
Open Source

PostgreSQL Performance Tuning

·2 min read

PostgreSQL ships with conservative defaults designed to start on a Raspberry Pi without crashing. Any real server can benefit from ten lines of configuration changes. Tuning beyond that depends on the workload — but the first ten lines almost always pay off.

The starting ten

For a server with 16 GB RAM and an SSD, dedicated to Postgres:

# postgresql.conf
shared_buffers = 4GB               # ~25% of RAM
effective_cache_size = 12GB        # ~75% of RAM — hint to the planner
maintenance_work_mem = 1GB          # VACUUM, CREATE INDEX
work_mem = 32MB                     # per-sort/hash; total = work_mem * connections * N
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1              # SSD — default 4 assumes spinning disk
effective_io_concurrency = 200      # SSDs can do parallel reads
default_statistics_target = 100
max_connections = 100               # use a pooler for more

After editing, restart: systemctl restart postgresql. Some settings (work_mem, others) take effect via reload.

Connection pooling

Postgres is process-per-connection — every backend is a real OS process consuming several MB. 100 idle connections is fine; 10,000 is a system that will explode. Use a pooler:

  • PgBouncer — tiny, transaction-mode pooling, the boring default.
  • Pgpool-II — heavier, supports query routing.
  • Built into your framework: rails connection pool, asyncpg pool, etc.

Indexes

Every column used in a WHERE, JOIN, or ORDER BY of a non-trivial query is a candidate for indexing. Default is B-tree; other types are situational:

  • B-tree: equality, ranges, ORDER BY. The default.
  • Hash: equality only. Rarely worth it over B-tree.
  • GIN: arrays, jsonb, full-text search.
  • BRIN: huge tables with naturally ordered data (time series). Tiny on disk.
  • Partial: WHERE status = 'active' — index a slice of the table.
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_events_data ON events USING gin(data jsonb_path_ops);
CREATE INDEX CONCURRENTLY idx_orders_active ON orders(created_at) WHERE status = 'active';

CONCURRENTLY avoids locking the table during index creation. Slower, but the database stays usable.

EXPLAIN ANALYZE: the only tuning tool you need

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Read it from the bottom up. Costs are in arbitrary units; what matters are actual times and row counts vs. estimates. If "actual rows" is wildly different from "estimated rows," run ANALYZE on the table. If you see Seq Scan on a large table for a selective predicate, you're missing an index.

Autovacuum

Postgres uses MVCC — updates and deletes leave dead row versions that vacuum reclaims. Autovacuum handles this automatically. For high-write tables, tune per-table:

ALTER TABLE busy_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

What to monitor

  • pg_stat_activity: who's connected and what they're doing.
  • pg_stat_statements: top queries by total time. Enable the extension.
  • Cache hit ratio: SELECT sum(heap_blks_hit)::float / NULLIF(sum(heap_blks_hit)+sum(heap_blks_read),0) FROM pg_statio_user_tables; — aim for > 99%.
  • Replication lag (if applicable).

Related