The operator's first instruments · PostgreSQL

PostgreSQL describes itself. Its configuration, its running sessions, its locks, its I/O and its table statistics are all queryable through ordinary SQL against catalogue views, which means that almost every question asked in the opening minutes of an incident has an answer available from a psql prompt.

This lesson covers the three views that answer the most questions per keystroke. The rest of the course adds more — pg_locks in Part IX, pg_stat_replication in Part XIV, pg_stat_statements in Part XVI — but these three come first because they are where you start.

pg_settings: what is this server configured to do

SHOW work_mem gives you a value. pg_settings gives you the value plus everything you need to reason about it: where it came from, whether you can change it now, and whether a change you already made has actually taken effect.

Read-only / Safesettings that are not at their built-in default— Captured on 18.6. How many rows this returns depends entirely on how the cluster was started; the point is the source column, not the count. Abbreviated here.

$ psql -U postgres -c "SELECT name, setting, source, pending_restart FROM pg_settings WHERE source <> 'default' ORDER BY source, name"

            name            |      setting       |       source       | pending_restart
----------------------------+--------------------+--------------------+-----------------
application_name           | psql               | client             | f
autovacuum_worker_slots    | 16                 | configuration file | f
DateStyle                  | ISO, MDY           | configuration file | f
default_text_search_config | pg_catalog.english | configuration file | f
dynamic_shared_memory_type | posix              | configuration file | f
listen_addresses           | *                  | configuration file | f
max_connections            | 100                | configuration file | f
max_wal_size               | 1024               | configuration file | f
shared_buffers             | 16384              | configuration file | f
wal_level                  | replica            | command line       | f
config_file                | .../postgresql.conf| override           | f
... (this cluster: 33 rows in total)

Four columns carry most of the value.

source distinguishes a deliberate choice from a default. Filtering to source <> 'default' is the fastest way to see what somebody actually configured on a server you have just inherited, and it is a much shorter list than the full parameter set.

context is the change-planning column from earlier in this part: postmaster needs a restart, sighup needs a reload, superuser and user can be set in a session.

pending_restart is the honesty column. It is t when the configuration file holds a value that differs from the one the server is running, for a parameter that needs a restart. This is the difference between a change you made and a change that is in effect, and it is the column that settles the question “did that change apply?”

sourcefile and sourceline name the file and line the value came from, which matters once postgresql.auto.conf and include files are in play. Part III builds on this.

# Has anything been edited but not yet restarted into effect?
psql -U postgres -c \
  "SELECT name, setting, sourcefile, sourceline
     FROM pg_settings WHERE pending_restart"

# Where did this specific value come from?
psql -U postgres -c \
  "SELECT name, setting, source, sourcefile, sourceline
     FROM pg_settings WHERE name = 'shared_buffers'"

pg_stat_activity: what is it doing right now

One row per backend, including the background processes. The columns that decide your next action are backend_type, state, wait_event_type and the two age calculations you derive from the timestamps.

The single most useful query in this course:

psql -U postgres -c \
  "SELECT pid,
          usename,
          application_name,
          client_addr,
          state,
          wait_event_type || ':' || wait_event AS waiting_on,
          now() - xact_start AS xact_age,
          now() - state_change AS in_state,
          left(query, 60) AS query
     FROM pg_stat_activity
    WHERE backend_type = 'client backend'
      AND pid <> pg_backend_pid()
    ORDER BY xact_start NULLS LAST"

Two details in that query are deliberate. Filtering to client backend removes the background processes, which are permanently in an Activity wait and would otherwise dominate the output — the reason was covered in the background-processes lesson. Excluding pg_backend_pid() removes your own session, which is always active and always running the query you are looking at.

The four states, and why they need different responses

`state`	What it means	The right response
`active`	Executing a query now	Look at `wait_event`: working, or blocked?
`idle`	Connected, no transaction open	Holds a process and its memory; harmless to the database
`idle in transaction`	Transaction open, doing nothing	Actively harmful. Holds locks and blocks cleanup
`idle in transaction (aborted)`	Same, after an error	Same harm; the client has not sent a rollback

The distinction between idle and idle in transaction is the most consequential thing on this page. An idle session is merely a process. An idle in transaction session is holding a transaction open, which means it is holding whatever locks it took, and — as Part VII and Part VIII will show — it is preventing VACUUM from reclaiming dead rows anywhere in the cluster, because a row version that transaction might still need cannot be removed.

A single connection left idle in transaction overnight is a routine cause of a table growing without bound while its row count stays flat.

# The query to run before you conclude anything about bloat or vacuum.
psql -U postgres -c \
  "SELECT pid, usename, application_name, state,
          now() - xact_start AS xact_age, left(query, 80) AS last_query
     FROM pg_stat_activity
    WHERE state LIKE 'idle in transaction%'
    ORDER BY xact_start"

wait_event_type and wait_event turn active from one state into many. A backend that is active might be computing, waiting on I/O, waiting for a lock, or waiting on a client that has stopped reading. Those are four different incidents. Part XVI covers wait events systematically; the shape to remember now is that active alone tells you almost nothing.

Terminating idle in transaction sessions as a routine cleanup

Finding a set of idle in transaction sessions and terminating them resolves the immediate symptom, and it is sometimes the correct emergency action. As a habit it causes two problems.

The application does not know its transaction was destroyed until it next tries to use the connection, and how it reacts is a property of the application rather than something you control. A client that retries the whole transaction is fine. A client that treats the error as fatal and exits is a second incident. A client that reconnects in a tight loop is a connection storm.

More importantly, terminating them removes the evidence of which application is leaking transactions, and it will happen again. The values worth capturing before acting are application_name, client_addr, the transaction age and the last query the session ran, because those identify the code path that opened the transaction and never closed it.

The durable fix is idle_in_transaction_session_timeout, which makes PostgreSQL end these sessions itself on a stated rule rather than leaving it to whoever is on call. Part IV covers choosing the value.

pg_stat_io: where is the I/O going

Introduced in PostgreSQL 16 and extended in 18, pg_stat_io breaks I/O down by which kind of process did it, what it was operating on, and why.

Read-only / SafeI/O attributed to process type and purpose— The context column separates ordinary access from bulk reads, vacuum work and bulk writes.

$ psql -U postgres -c 'SELECT backend_type, object, context, reads, writes, extends, hits FROM pg_stat_io WHERE reads > 0 OR writes > 0 ORDER BY backend_type'

    backend_type     |  object  | context  | reads | writes | extends | hits
---------------------+----------+----------+-------+--------+---------+-------
autovacuum launcher | relation | normal   |     1 |      0 |         |     0
autovacuum worker   | wal      | normal   |       |     11 |         |
autovacuum worker   | relation | normal   |   137 |      0 |       9 | 11248
autovacuum worker   | relation | vacuum   |    40 |      0 |       0 |   126
checkpointer        | relation | normal   |       |    389 |         |
checkpointer        | wal      | normal   |     0 |      2 |         |
client backend      | relation | normal   |   334 |      0 |       0 | 15381
client backend      | relation | bulkread |   215 |      0 |         |   345
client backend      | wal      | normal   |     0 |     38 |         |
standalone backend  | relation | normal   |   479 |   1038 |     598 | 92643
(10 rows)

The question this view answers that no host-level tool can: which part of PostgreSQL is generating this I/O, and for what purpose. iostat shows a busy device. This shows that the writes are the checkpointer rather than the application, or that the reads are an autovacuum worker rather than a query.

context is the column that repays study:

normal — ordinary buffered access through the shared pool.
vacuum — work done by vacuum, using its own restricted buffer ring.
bulkread — large sequential reads, also using a restricted ring so they cannot evict the working set.
bulkwrite — bulk loading operations such as COPY.

A low hit rate in bulkread or vacuum context is the ring buffer working correctly, not a cache problem. Reading a pooled hit ratio without this breakdown is how the wrong conclusion gets reached, which was the subject of the buffer-pool lesson.

In PostgreSQL 18 this view gained read_bytes, write_bytes and extend_bytes, replacing the single op_bytes column that 16 and 17 carried. A monitoring query written against 16 or 17 that selects op_bytes will fail on 18 — the same class of post-upgrade breakage as the pg_stat_bgwriter column moves, and worth checking at the same time.

A first-five-minutes sequence

The value of a fixed sequence is that each step rules something out, so you are narrowing rather than wandering.

# 1. Is the server up and accepting connections at all?
pg_isready

# 2. Is it in recovery? A standby, or still replaying after a crash.
psql -U postgres -tAc 'SELECT pg_is_in_recovery()'

# 3. How many sessions, and in what states?
psql -U postgres -c \
  "SELECT state, count(*) FROM pg_stat_activity
    WHERE backend_type = 'client backend' GROUP BY state ORDER BY count(*) DESC"

# 4. Is anything blocked, and how long has the oldest transaction been open?
psql -U postgres -c \
  "SELECT pid, state, wait_event_type, wait_event,
          now() - xact_start AS xact_age, left(query, 60) AS query
     FROM pg_stat_activity
    WHERE backend_type = 'client backend' AND xact_start IS NOT NULL
    ORDER BY xact_start LIMIT 10"

# 5. Where is the disk going?
psql -U postgres -c \
  "SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
     FROM pg_database ORDER BY pg_database_size(datname) DESC"

Step 2 is the one people omit and regret. pg_is_in_recovery() returning true means this server is a standby or is still replaying, and a great many symptoms — writes being refused, data appearing stale, queries being cancelled — have that single explanation. Establishing it in one query at the start saves a long detour.

Where these numbers come from, and when they reset

pg_settings is generated on demand from the server’s in-memory configuration, so it is always current and costs nothing.

The statistics views are different. Since PostgreSQL 15 the cumulative statistics live in shared memory rather than in a separate collector process, which removed a source of latency and a temporary file. They are accumulated by each backend and periodically reported.

Two consequences matter. First, statistics are cumulative since the last reset, not a rate. A large reads figure means nothing without knowing the window, which is why stats_reset appears on every statistics view and why the right way to read them is to take two samples and subtract. Second, they are persisted at clean shutdown and reloaded at startup, so a planned restart preserves them — but a crash does not, and they start from zero.

That second point is a small trap during incident review. Statistics that look implausibly low after an unplanned restart are not evidence that the workload changed; they are evidence of the restart.

There is one further subtlety in pg_stat_activity. It reports a snapshot taken at the start of the current transaction, so within a single transaction repeated queries return the same rows. In an interactive psql session each statement is its own transaction, so this is invisible. Inside an explicit BEGIN, polling it in a loop returns the same answer every time. Setting stats_fetch_consistency to none changes this behaviour for monitoring queries that need a fresh reading each time.

Production discipline

Check pending_restart after every configuration change. It is the one column that distinguishes an intended change from an applied one.
Filter pg_stat_activity to client backend before counting anything. Background processes are permanently in an Activity wait and will distort any count that includes them.
Treat idle in transaction as a finding, never as noise. It holds locks and blocks cleanup cluster-wide, and it explains symptoms that appear entirely unrelated.
Capture application_name, client_addr and transaction age before terminating anything. Terminating removes the evidence of which code path is responsible.
Read statistics as two samples, not one number. They are cumulative since stats_reset, and a crash resets them to zero.
Run pg_is_in_recovery() early. It explains a large family of symptoms in a single query.

Cross-course references

Observability for Production Sysadmins — Part LIX (Database observability) covers turning these views into exported metrics, and Part XII (PromQL foundations) covers computing rates from cumulative counters, which is precisely what these views require.
Linux for Production Sysadmins — Part XLI (Disk performance) covers the host-level I/O picture that pg_stat_io attributes to a cause.
Git, CI/CD & GitOps — Part CVI (Change management) covers recording a configuration change so that pending_restart has something to be reconciled against.