PostgreSQL tuning basics (memory, connections, checkpoints)

Topic: Databases core

Tune PostgreSQL for your workload by setting shared_buffers, work_mem, effective_cache_size, and checkpoint-related parameters. Use EXPLAIN ANALYZE to find slow queries; add indexes as needed. Use this when the database is slow or when sizing a new server.

Intent: How-to

Quick answer

  • shared_buffers: 25% of RAM (or up to 8GB for large servers). work_mem: per-operation memory (e.g. 64MB); increase for complex sorts/joins. effective_cache_size: estimate of OS cache (e.g. 50-75% of RAM).
  • Checkpoints: checkpoint_completion_target=0.9 spreads I/O. random_page_cost lower on SSD (e.g. 1.1). max_connections: set to what you need; use connection pooling so you do not need thousands.
  • Find slow queries: enable log_min_duration_statement; use pg_stat_statements. Add indexes for filtered/sorted columns; run ANALYZE after bulk load. Benchmark before and after changes.

Prerequisites

Steps

  1. Set memory parameters

    shared_buffers = 2GB (or 25% RAM, cap as needed). work_mem = 64MB (per sort/hash op). effective_cache_size = 6GB (hint for planner). Restart for shared_buffers.

  2. Checkpoints and I/O

    checkpoint_completion_target = 0.9. On SSD: random_page_cost = 1.1, effective_io_concurrency = 200. Reload after change.

  3. Connections and pooling

    max_connections = expected peak + buffer. Use PgBouncer or application pooling so the app does not open one connection per request. Tune pool size to match max_connections.

  4. Identify slow queries

    Set log_min_duration_statement = 1s; use pg_stat_statements. EXPLAIN ANALYZE slow queries; add indexes; run ANALYZE. Revert or tune further based on benchmarks.

Summary

Tune shared_buffers, work_mem, and effective_cache_size; set checkpoint and I/O parameters for your storage. Use connection pooling; find and optimize slow queries with EXPLAIN and indexes. Use this for performance and capacity planning.

Prerequisites

Steps

Step 1: Set memory parameters

Set shared_buffers, work_mem, and effective_cache_size; restart if required.

Step 2: Checkpoints and I/O

Tune checkpoint_completion_target and, on SSD, random_page_cost and effective_io_concurrency.

Step 3: Connections and pooling

Set max_connections; use a connection pooler so the app does not exhaust connections.

Step 4: Identify slow queries

Enable slow-query logging and pg_stat_statements; use EXPLAIN ANALYZE and indexes to optimize.

Verification

  • Parameters are applied (SHOW); slow queries are identified and improved; benchmarks show acceptable latency and throughput.

Troubleshooting

High connection count — Add pooling; reduce max_connections per app. Still slow — Check disk I/O (iostat); consider more RAM or faster storage; review query plans.

Next steps

Continue to