Correlating PostgreSQL evidence with the operating system · PostgreSQL

PostgreSQL knows what it is waiting for. The operating system knows what the machine is doing. Neither is sufficient, and joining them is one column.

The join

Read-only / Safefour active backends and what they are waiting on— EXECUTION VALIDATED on 18.6 during pgbench -c 8 -T 15.

$ SELECT pid, state, wait_event_type, wait_event, left(query,32) AS query
FROM pg_stat_activity WHERE backend_type='client backend' AND state='active' LIMIT 4;

 pid | state  | wait_event_type |  wait_event   |              query
-----+--------+-----------------+---------------+----------------------------------
880 | active | LWLock          | WALWrite      | END;
881 | active | Lock            | transactionid | UPDATE pgbench_branches SET bbal
882 | active | IO              | WalSync       | END;
883 | active | LWLock          | LockManager   | UPDATE pgbench_branches SET bbal

Read-only / Safethe same pids in the operating system— EXECUTION VALIDATED on 18.6, taken immediately after.

$ ps -o pid,stat,time,rss,vsz,comm -p 880,881,882,883

    PID STAT     TIME   RSS    VSZ COMMAND
  880 Ss   00:00:00 70800 229688 postgres
  881 Ds   00:00:00 66188 229708 postgres
  882 Ss   00:00:00 69132 229708 postgres
  883 Ss   00:00:00 71720 229688 postgres

Read-only / Safeand /proc, for one of them— EXECUTION VALIDATED on 18.6.

$ grep -E '^(VmRSS|Threads|voluntary_ctxt|nonvoluntary_ctxt)' /proc/880/status

VmRSS:	   70800 kB
Threads:	1
voluntary_ctxt_switches:	6657
nonvoluntary_ctxt_switches:	22

Threads: 1 — one thread per backend, the process model from Part I, seen from the other side.

Choosing the OS instrument from the wait event

The wait event class tells you which OS tool will have something to say:

Wait event class	Look at	With
`IO/DataFileRead`	Disk read latency and queue depth	`iostat -x 1`, `biolatency`
`IO/WalSync`	fsync latency	`iostat -x 1`, `pg_test_fsync`
`LWLock`	CPU and context switches	`top`, `vmstat 1`, `/proc/PID/status`
`Lock`	Nothing in the OS. Application contention	Part IX
`Client`	Network and the application	`ss -tn`, application logs
`NULL` (running)	CPU saturation	`top`, `mpstat`, `perf`

That mapping saves the most common wasted hour in database troubleshooting: reaching for iostat when the wait events say Lock. The disk is not the problem, has never been the problem, and will look fine no matter how long you stare at it.

The system-level numbers that matter

vmstat 1 5            # r, b, si, so, us, sy, wa
iostat -x 1 5         # r_await, w_await, %util, aqu-sz
free -m               # and the caveat below
ss -s                 # connection counts and states
df -h /var/lib/postgresql /var/lib/postgresql/*/main/pg_wal

The ones worth knowing by heart:

vmstat si/so non-zero — swapping. Lesson XI-06.
vmstat b — processes blocked on I/O.
vmstat wa — CPU time waiting on I/O.
iostat r_await/w_await — per-request latency. The number that corresponds to IO/* wait events.
df on pg_wal separately — lesson XII-03’s outage.

Do not add up RSS across backends

The four backends above report 66–71 MB RSS each. Summing them gives ~275 MB, and the true usage is far less, because shared_buffers is mapped into every backend and counted in every RSS.

Lesson XI-01 measured this directly: nine backends summing to 728 MB of RSS on a cluster with 128 MB of shared_buffers.

The consequences are practical:

A monitoring system summing per-process RSS reports memory that does not exist, and the error grows with backend count.
PSS from /proc/PID/smaps_rollup divides shared pages properly and is the right number — though this course could not capture it, because reading it was denied in the container.
Inside a container, free reports the host’s memory, not the cgroup limit. Lesson XI-06 measured free reporting 47 GB inside a container. Read /sys/fs/cgroup/memory.max and memory.current instead.

The evidence that only exists at the moment it happens

Some of the most valuable diagnostic evidence cannot be recovered afterwards, which changes how an incident should be handled.

pg_stat_activity is a snapshot. Once a query finishes there is no record it ran, unless it exceeded a logging threshold.

Wait events have no history, as lesson XVI-04 established.

ps state, /proc contents, socket states — all instantaneous.

So the first action during a database incident is not to fix it. It is to capture, because fixing destroys the evidence:

# capture first, in this order, before changing anything
psql -c "\copy (SELECT now(), * FROM pg_stat_activity) TO 'act.csv' CSV HEADER"
psql -c "\copy (SELECT now(), * FROM pg_locks) TO 'locks.csv' CSV HEADER"
psql -c "\copy (SELECT now(), * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 50) TO 'stmts.csv' CSV HEADER"
ps auxf > ps.txt; vmstat 1 10 > vmstat.txt; iostat -x 1 10 > iostat.txt
df -h > df.txt; free -m > free.txt

Thirty seconds of capture buys the ability to answer “what actually happened” afterwards. Without it, the post-incident review has the log, whatever the monitoring system happened to sample, and people’s memories — and the question “was it the same as last time?” becomes unanswerable, which means the underlying cause never gets fixed because it is never characterised.

The related habit is to record what you changed and when, in the same place. An incident where three people made changes in parallel and the system recovered is an incident nobody learns anything from, because the recovery cannot be attributed. A timestamped note per action costs nothing during the incident and is the difference between a review that produces a fix and one that produces a shrug.

What to take from this

pg_stat_activity.pid is the OS pid. That is the whole join.
Two instruments sampled microseconds apart need not agree. Sample close together and profile rather than snapshot.
Let the wait event class choose the OS tool. Lock means the OS has nothing to say.
Know vmstat si/so/b/wa and iostat r_await/w_await.
Never sum RSS across backends — shared_buffers is counted in each. Measured: 728 MB of RSS over 128 MB of buffers.
Inside a container, free reports the host. Read the cgroup files.
Capture before you fix. The best evidence exists only while the problem does.

Cross-course references

Linux for Production Sysadmins — Part XXXVIII (Linux performance fundamentals), Part XL (Memory Performance) and Part XLI (Storage Performance) cover the host-side instruments this lesson pairs with the database’s own.
Observability for Production Sysadmins — Part LI (Correlating metrics, logs, and traces) and Part LVI (Linux observability) cover putting both on one timeline, which is what makes the correlation an argument rather than a coincidence.
Docker & Containers — Part XV (Resource controls) covers why host figures and container figures disagree, and which one the OOM killer uses.

The join

Choosing the OS instrument from the wait event

The system-level numbers that matter

What to take from this

Cross-course references

Quiz