Alright, let’s talk about pgTune. You’ve probably heard of it. It’s the website where you plug in your server’s specs—CPU, RAM, the whole nine yards—and it spits out a custom-tuned postgresql.conf for you. It’s a fantastic tool, and I use it all the time. But here’s the critical bit you need to internalize: pgTune gives you a starting line, not a finish line. It’s like a master tailor giving you a brilliantly cut suit that’s still missing the final fittings. If you just copy-paste its output and walk away, you’re going to have a bad time. It doesn’t know your workload. It’s guessing based on averages, and your database is a beautiful, unique snowflake of chaos.

Why pgTune’s Output is an Approximation

Think of pgTune as a brilliant but slightly myopic architect. It can design a house with the right number of rooms for your family size (RAM), strong enough foundations for your plot of land (CPU/Disk), but it has no idea if you’re a concert pianist who needs a soundproof room or a master chef who needs a commercial-grade ventilation system. Your workload—the mix of reads vs. writes, the size of your working data set, the concurrency level—is that personal touch. pgTune’s formulas are based on common OLTP (Online Transaction Processing) workloads. If your use case is 90% massive analytical queries (OLAP), its suggestions will be… suboptimal.

The Non-Negotiable First Step: shared_buffers

This is usually the biggest change pgTune recommends, and it’s right. shared_buffers is the amount of memory PostgreSQL uses to cache data directly, avoiding a slow trip to disk. Before pgTune, the default was criminally low (like 128MB). pgTune will rightly suggest setting it to ~25% of your total RAM on a dedicated database server.

But why 25%? Why not 80%? Because the operating system also needs RAM for its own disk cache. PostgreSQL is really good at relying on the OS cache. It’s a tag-team effort: shared_buffers caches the hot data, and the OS cache handles the rest. Going over ~8GB on Linux or ~512MB on older Windows systems often has diminishing returns. Here’s what pgTune might output for a 16GB RAM server:

# Based on pgTune suggestion for 16GB RAM, DB Type: Web Application
shared_buffers = 4GB

This is a great starting point. But if you’re on a server with 64GB of RAM, blindly setting this to 16GB might be fine, but you should also be thinking about the other memory parameters.

The Memory Triptych: work_mem, maintenance_work_mem, and effective_cache_size

PgTune adjusts these, but you absolutely must sanity-check them.

  • work_mem: This is the memory available for each operation (e.g., a sort or a hash) within a query. The default is pathetically small (4MB). PgTune will increase it. The catch: this is per operation, per backend. A complex query might have several sorts, and if you have 50 concurrent users running complex queries, you can blow your total RAM. Pitfall: Setting this too high on a system with high concurrency is a fast track to your server swapping itself into a coma.

    # pgTune might suggest this for a 16GB RAM system
work_mem = 16MB
# But if you have high concurrency, you might dial it back
work_mem = 8MB

  • maintenance_work_mem: Memory for vacuuming, creating indexes, etc. This is a global operation, so you can be more generous. PgTune’s suggestion is usually sane.

    maintenance_work_mem = 1GB

  • effective_cache_size: This isn’t allocated memory! This is just a number you tell PostgreSQL, giving it a hint about how much total RAM the OS is likely to have available for disk caching. This helps the query planner decide if an index scan (which uses memory) is likely to be efficient vs. a sequential scan (which uses disk). Underestimating this makes the planner cowardly and prone to sequential scans. PgTune usually sets it to about 50-75% of total RAM, which is correct.

    effective_cache_size = 11GB

The Checkpoint Dance: checkpoint_completion_target

This is where pgTune earns its keep. The default settings for checkpoints (the process of writing dirty buffers to disk) are brutal for any modern hardware. The old defaults would slam your I/O system, causing periodic performance cliffs. PgTune sets checkpoint_completion_target to a higher value (like 0.9), which tells PostgreSQL to spread the checkpoint I/O out over almost the entire time between checkpoints. This smooths out I/O demand dramatically and is arguably one of the most important tuning fixes for most setups.

# pgTune's great advice
checkpoint_completion_target = 0.9

The Final Fitting: Observe and Adjust

This is the part you have to do. Take pgTune’s config. Apply it. Then, you must monitor.

  1. Is the system using the memory? Use pg_stat_statements to find queries doing massive sorts. Are they spilling to disk? Increase work_mem for that specific query or cautiously globally.
  2. Checkpoint still hurting? Look at pg_stat_bgwriter. A high ratio of buffers_checkpoint / (buffers_clean + buffers_backend) means checkpoints are doing most of the writing, which is what we want with a high completion target.
  3. Connection scaling? PgTune might suggest raising max_connections. Don’t blindly accept this. Every connection, even idle, has non-zero overhead. If you need high concurrency, use a connection pooler like pgbouncer. It’s the single best thing you can do after basic tuning.

PgTune gets you 80% of the way there. You, the human who knows what your application actually does, have to do the last 20%. It’s the difference between a database that runs and one that sings.