PostgreSQL Deep Dive
by Sergey Okinchuk
When the Planner Is WrongMemory Settings

Chapter Ten · Configuration and Performance

Configuration and Performance

pg-primary has 64 GB of RAM and is running shared_buffers = 128MB, because that is what the package shipped and the install left behind. Two application servers open 400 connections against a max_connections of 200. And Nadia knows which pages users complain about, not which statements are slow. Six topics fix all three.

6 topics

The last five chapters explained the machine: how a row is physically stored, why a version of it survives the update that replaced it, what vacuum is cleaning up, how an index finds a tuple, and how the planner prices one access path against another. That knowledge ends at the edge of a single query. It says nothing about a server whose memory settings were chosen for a laptop, nothing about four hundred processes competing for sixteen cores, and nothing about which of ten thousand statements a day is worth reading a plan for in the first place.

This chapter opens the operations half of the book, and it starts where every real Postgres installation starts: with defaults that were chosen so the server would start anywhere, on hardware that deserves better. The three settings that matter most are all memory. The single largest architectural change is a pooler. And the two extensions that turn a support ticket into a query id cost a restart and a few percent of a core.

By the end, Cartwheel has memory sized to its hardware, pgbouncer-01 in transaction mode turning 400 client connections into 40 server ones, a ranked list of the statements consuming the primary's time, a dozen counters on a dashboard, a benchmark that moves one setting per run and writes the number down, and a configuration file where every non-default line has a sentence explaining itself. What it still does not have is a way to make delivery_events manageable, which is where Chapter 11 begins.

What this chapter changes on pg-primary, in the order it changes it
Packaged defaultschosen so the server starts anywhere
→
Memory sized to the hardwareshared_buffers · work_mem · the OS cache
→
400 clients → 40 server connectionsthroughput up on hardware that did not change
→
Statements ranked, plans captureda restart and a few percent of a core
→
A dashboard and a justified configno number quoted out of context

Topics in This Chapter

Topic 50
Memory — shared_buffers, work_mem, and the OS Cache
Three pools that have nothing to do with each other: a shared cache of 8 KB slots, a per-operation grant that multiplies by concurrency, and the kernel cache underneath both. Why 25% of RAM is a starting point, why effective_cache_size allocates nothing, and how a global work_mem of 256 MB becomes 200 GB.
Memory Tuning
Topic 51
Connections, and Why You Need a Pooler
A connection is an operating-system process, so throughput falls once active backends pass the core count and raising max_connections only permits the fall to be steeper. PgBouncer's three pool modes, the exact list of session features transaction mode takes away, and how to size a pool from cores rather than clients.
Connection Pooling
Topic 52
Finding Slow Queries — pg_stat_statements and auto_explain
One extension aggregates every statement by normalized shape; the other logs the plan of the slow executions that never reproduce by hand. Why total time beats mean time, what track_io_timing settles, and the honest cost of each — including the instrumentation auto_explain applies to statements it never logs.
Query Diagnostics
Topic 53
The Statistics Views — What to Monitor
Several hundred counters exist and about a dozen predict an outage. Which views are cumulative and which are photographs of this instant, where the blocker's pid lives, what a rising ratio of forced to timed checkpoints means, and where the checkpoint counters moved in 17.
Monitoring
Topic 54
Benchmarking Honestly with pgbench
The default script is a write-heavy banking simulation with a hotspot, and it resembles most applications not at all. Custom scripts with production-shaped parameter skew, why a run must contain several checkpoints, and what to do about the fact that pgbench reports no percentiles.
Measurement
Topic 55
A Configuration Baseline That Isn't Cargo Cult
Cartwheel's postgresql.conf line by line, with a sentence of justification on each and the famous-but-wrong ones called out. What random_page_cost should be on NVMe, why the timeouts belong on roles rather than in the file, and why a line that restates a current default should be deleted.
Configuration