Monitor and maintain PostgreSQL: statistics, slow queries and VACUUM

A production PostgreSQL that slows down gradually, with tables that swell, queries that go from 5 ms to 500 ms and connections that pile up for no obvious reason, is the classic scenario when nobody is watching. PostgreSQL ships a complete statistics system that can diagnose each of these problems, but you still need to know what to look at and when to act.

This guide follows an observe → identify → explain → fix approach. Every command was tested on a real lab (PostgreSQL 18.6, Debian 13, a lab_admin database with 100,000 log rows). You will learn to read the statistics views, interpret an execution plan, detect bloat (the swelling of tables) and use VACUUM, ANALYZE and REINDEX at the right time.

Version used

All the outputs in this guide come from a single replay, done on 2026-10-20 on PostgreSQL 18.6 (package 18.6-1.pgdg13+2) under Debian 13, in the official image postgres:18@sha256:74935e72241653ca55e0414067e6d8763aceb8a810eb51b452253ec3dcfc4336, without network access. That container has no systemd: the restart in the pg_stat_statements step was done by restarting the container, not with systemctl restart. Your pids, dates and durations will differ; the row counts and block counts should match. The PG 18 novelties are flagged: EXPLAIN ANALYZE automatically includes BUFFERS, autovacuum_vacuum_max_threshold, richer I/O statistics in pg_stat_io.

What you will learn

• Diagnose current activity with pg_stat_activity (connections, states, blocked queries)
• Identify costly queries with pg_stat_statements (ranked by cumulative time)
• Read an EXPLAIN ANALYZE plan and understand Seq Scan, Index Scan and Hash Join
• Detect bloat (dead tuples) and watch autovacuum
• Maintain performance with VACUUM, VACUUM FULL, ANALYZE and REINDEX
• Observe I/O by backend type with pg_stat_io

In what context?

You need to monitor and maintain PostgreSQL in these situations:

• Your application slows down gradually and you suspect the database
• You want to set up proactive monitoring before problems show up
• Autovacuum does not seem to do its job: tables grow, queries slow down
• You need to identify the most costly queries to know where to optimize
• You are preparing a maintenance plan for a production database (VACUUM, REINDEX, ANALYZE)
• You are debugging a locking problem or connections that pile up

What this guide does not cover

Monitoring and maintaining means observing, then fixing on a running instance. Three neighbouring topics are covered elsewhere to avoid duplication: the tuning of memory and connection parameters in Configure PostgreSQL, backup with WAL archiving and PITR in Backup and restore (in French), and access control (pg_hba.conf, TLS, role separation) in Control access to PostgreSQL. You will naturally come back to them: a diagnosis often ends with a configuration adjustment.

Prerequisites

• PostgreSQL installed and the service running (see the Installation guide)
• Access to the superuser role (postgres)
• The basics of psql (see the Administer a PostgreSQL instance with psql guide)
• A database with data so that there is something to observe (the lab uses lab_admin with 100,000 rows)

To replay this guide identically, create the database and the dataset below (five customers, five orders and 100,000 log rows, split evenly across five customers and five actions). Create the database first:

sudo -u postgres createdb lab_admin

Then paste this script into sudo -u postgres psql -d lab_admin. It creates the app schema, its three tables and loads the data:

CREATE SCHEMA app;
CREATE TABLE app.clients (
    id    serial PRIMARY KEY,
    nom   text NOT NULL,
    email text NOT NULL UNIQUE,
    ville text
);
CREATE TABLE app.commandes (
    id        serial PRIMARY KEY,
    client_id integer NOT NULL REFERENCES app.clients (id),
    montant   numeric(10,2) NOT NULL,
    statut    text NOT NULL
);
CREATE TABLE app.logs (
    id        serial PRIMARY KEY,
    client_id integer NOT NULL,
    action    text NOT NULL,
    detail    text
);
INSERT INTO app.clients (nom, email, ville) VALUES
    ('Alice Martin',  'alice@exemple.fr',  'Lyon'),
    ('Bruno Durand',  'bruno@exemple.fr',  'Paris'),
    ('Chloé Petit',   'chloe@exemple.fr',  'Lille'),
    ('David Moreau',  'david@exemple.fr',  'Nantes'),
    ('Emma Laurent',  'emma@exemple.fr',   'Lyon');
INSERT INTO app.commandes (client_id, montant, statut) VALUES
    (1, 120.50, 'payee'), (1, 80.00, 'payee'), (2, 45.90, 'en_attente'),
    (3, 210.00, 'payee'), (5, 15.75, 'annulee');
INSERT INTO app.logs (client_id, action, detail)
SELECT i % 5 + 1,
       (ARRAY['login','logout','view','update','delete'])[(i / 5) % 5 + 1],
       'event_' || i
FROM generate_series(1, 100000) AS i;

Then run ANALYZE in a separate command, a few seconds after the load. Run in the same session right after the INSERT, it leaves n_live_tup, the live-row counter of the pg_stat_user_tables view (introduced further down), showing double the real figure (200,000 rows for logs during the test):

ANALYZE;

The approach: observe → identify → explain → fix

Diagnosing a PostgreSQL performance problem always follows the same order:

1. Observe: what is happening right now? (pg_stat_activity, connections, locks)
2. Identify: which queries consume the most? (pg_stat_statements, top 10)
3. Explain: why is this query slow? (EXPLAIN ANALYZE, execution plan)
4. Fix: add an index, rewrite the query, or maintain (VACUUM, ANALYZE, REINDEX)

Do not skip a step. A VACUUM FULL run blindly on a production table locks the table for reads and writes alike. An index added without having read the plan can make things worse.

Observe server activity

PostgreSQL maintains an internal statistics collector, exposed as views you can query in SQL. The first of them, pg_stat_activity, gives a snapshot of the moment: each connected process, its state and its query. Nothing to install, everything is available as soon as the server starts. This section covers the live diagnosis, the one you run when the application is slow right now.

pg_stat_activity: who is doing what right now

pg_stat_activity is the first view to check. It shows all active connections, their state, the current query and for how long it has been running:

SELECT pid, usename, datname, state,
       LEFT(query, 60) AS query,
       backend_start::timestamp(0),
       state_change::timestamp(0)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

 pid | usename  |  datname  | state  |                query                 |    backend_start    |    state_change
-----+----------+-----------+--------+--------------------------------------+---------------------+---------------------
 118 | postgres | lab_admin | active | SELECT pid, usename, datname, state,+| 2026-10-20 14:53:04 | 2026-10-20 14:53:04
     |          |           |        |        LEFT(query, 60)               |                     |
(1 row)

The + sign at the end of a line shows that the displayed query contains a line break: psql splits it across several display lines, because LEFT(query, 60) only truncates the length of the text, not its line breaks.

Key columns:

Column	Meaning
pid	Backend process identifier
state	Connection state (active, idle, idle in transaction)
query	Last query executed (or in progress)
wait_event_type	Type of wait (Lock, IO, etc.), NULL if not waiting
backend_start	When the connection was opened
state_change	When the last state change took place

Swipe to see more

The different backend types

PostgreSQL runs several processes permanently. pg_stat_activity shows them all:

SELECT backend_type, count(*)
FROM pg_stat_activity
GROUP BY backend_type
ORDER BY count DESC;

         backend_type         | count
------------------------------+-------
 io worker                    |     3
 walwriter                    |     1
 autovacuum launcher          |     1
 client backend               |     1
 background writer            |     1
 checkpointer                 |     1
 logical replication launcher |     1
(7 rows)

Only the client backend rows correspond to user connections. The others are system processes internal to PostgreSQL.

Active, idle and idle in transaction connections

For a quick overview of connection health:

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count DESC;

Two terms come back in the table below and throughout the guide. When a row is modified or deleted, PostgreSQL keeps its old version: it is a dead tuple, kept as long as a running transaction may still see it, then removed by VACUUM. As long as it has not been removed, it takes up space: that is bloat, the swelling of the table.

State	Meaning	Action if too many
active	Running a query	Normal, unless too long
idle	Connected, doing nothing	Misconfigured connection pool
idle in transaction	Open transaction, nothing running	Dangerous, holds back dead tuple cleanup
idle in transaction (aborted)	Transaction in error, not rolled back	Very dangerous, close immediately

Swipe to see more

idle in transaction: what paralyses cleanup

Autovacuum keeps running, but an old idle in transaction connection prevents it from removing the dead tuples created after the start of that transaction: they might still be visible to its snapshot, that is, the view of the database it froze when it began (backend_xmin in pg_stat_activity is the identifier of the oldest transaction that this connection still needs to see: as long as it is old, no more recent dead row can be removed). On an active database, this causes massive bloat within a few hours. Configure idle_in_transaction_session_timeout to limit the duration.

Share of connections in use

To watch how close you are to the max_connections limit:

SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn,
       count(*) * 100 / current_setting('max_connections')::int AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

 total | max_conn | pct_used
-------+----------+----------
     1 |      100 |        1
(1 row)

The backend_type = 'client backend' filter matters: without it, pg_stat_activity also counts the internal processes (autovacuum, checkpointer, walwriter…) that are not subject to max_connections.

Field rule of thumb: beyond 80% of client connections, consider a connection pooler (PgBouncer) or raise max_connections.

Detect blocked queries (wait_event)

To identify the connections waiting for a lock or a resource:

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       LEFT(query, 60) AS query,
       now() - state_change AS duration
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state != 'idle'
ORDER BY duration DESC NULLS LAST;

If wait_event_type is Lock, the connection is waiting for another one to release a lock. Cross-check with pg_locks to identify the blocker:

SELECT l.pid, l.locktype, l.mode, l.granted,
       a.usename, LEFT(a.query, 50) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.backend_type = 'client backend'
LIMIT 5;

 pid |  locktype  |      mode       | granted | usename  |                    query
-----+------------+-----------------+---------+----------+----------------------------------------------
 118 | relation   | AccessShareLock | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,+
     |            |                 |         |          |
 118 | relation   | AccessShareLock | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,+
     |            |                 |         |          |
 118 | virtualxid | ExclusiveLock   | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,+
     |            |                 |         |          |
 118 | relation   | AccessShareLock | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,+
     |            |                 |         |          |
 118 | relation   | AccessShareLock | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,+
     |            |                 |         |          |
(5 rows)

granted = f means that the lock is waiting: this is the blocked connection.

Kill a query: pg_cancel_backend, pg_terminate_backend

Two options to unblock a situation:

Function	Action	Risk
pg_cancel_backend(pid)	Cancels the running query	The connection stays open
pg_terminate_backend(pid)	Terminates the whole connection	Risk of losing an uncommitted transaction

Swipe to see more

-- Cancel cleanly (try this first)
SELECT pg_cancel_backend(1234);

-- If that is not enough, terminate the connection
SELECT pg_terminate_backend(1234);

pg_terminate_backend in production

Use pg_cancel_backend() first, it is less destructive. pg_terminate_backend() is the last resort: the connection is cut, the current transaction is rolled back, and the client receives a connection error.

Identify the costly queries

pg_stat_activity shows the present moment, not the history. To know which queries cost the most over time, you need an extension: pg_stat_statements, shipped with PostgreSQL but disabled by default. For each query it accumulates the number of calls, the total time and the blocks read, which lets you sort by real impact rather than by gut feeling. Enabling it requires a restart, so plan for it before going to production.

pg_stat_statements: enable and query

pg_stat_statements is a central monitoring extension. It collects aggregated statistics on all the queries run: number of calls, mean time, total time, rows returned.

1. Add the extension to shared_preload_libraries (restart required):

   First check the current value so you do not overwrite modules that are already loaded:

   SHOW shared_preload_libraries;

   Then add pg_stat_statements to the existing list:

   -- If the value was empty:
   ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
   -- If other modules were already loaded (e.g. auto_explain):
   -- ALTER SYSTEM SET shared_preload_libraries = 'auto_explain,pg_stat_statements';

   sudo systemctl restart postgresql@18-main

2. Create the extension in the database to monitor:

   CREATE EXTENSION pg_stat_statements;

   CREATE EXTENSION

   Check that the extension is installed and note its version:

   SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

         extname       | extversion
   --------------------+------------
    pg_stat_statements | 1.12
   (1 row)

Do not overwrite shared_preload_libraries

Adding to shared_preload_libraries requires a restart (postmaster parameter). It is a one-time operation. Warning: ALTER SYSTEM SET replaces the entire value, so if other modules are already loaded (auto_explain, pg_stat_kcache…), add pg_stat_statements to the list, do not replace it. The extension must then be created in every database you want to monitor.

The metrics that matter

The pg_stat_statements view exposes several dozen columns. Six are enough day to day, and the trap is to look only at the mean time: a 2 ms query called 500,000 times weighs more than an 800 ms query run twice a day.

Column	Meaning	Use
calls	Total number of executions	Identify the most frequent queries
mean_exec_time	Mean time per execution (ms)	Find the queries that are slow one by one
total_exec_time	Cumulative total time (ms)	Find those that consume the most resources
rows	Total number of rows returned	Detect queries that return too much data
shared_blks_hit	Blocks read from cache	Measure cache efficiency
shared_blks_read	Blocks read from disk	Identify I/O-intensive queries

Swipe to see more

Top 10 most costly queries

The query to keep at hand, given further down, sorts by cumulative time and is limited to the current database, thanks to the filter on dbid that excludes the activity of the other databases in the instance.

The extension only counts what runs after it is loaded: the view therefore holds no useful statistics as long as you have run nothing. Generate some activity with three queries repeated by the psql \watch command (i= interval in seconds, c= number of repetitions), whose output is discarded with \o /dev/null:

\o /dev/null
SELECT count(*) FROM app.logs WHERE action = 'login' \watch i=0.1 c=20
SELECT c.nom, count(*) FROM app.logs l JOIN app.clients c ON c.id = l.client_id GROUP BY c.nom ORDER BY c.nom \watch i=0.1 c=10
SELECT * FROM app.clients WHERE ville = 'Lyon' \watch i=0.1 c=5
\o

Then query the view. The result has five rows and not ten: LIMIT 10 is a ceiling, and only five normalized queries have been recorded since the load.

SELECT calls,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       round(total_exec_time::numeric, 1) AS total_ms,
       rows,
       LEFT(query, 80) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

 calls | mean_ms | total_ms | rows |                                      query
-------+---------+----------+------+----------------------------------------------------------------------------------
    10 |  16.516 |    165.2 |   50 | SELECT c.nom, count(*) FROM app.logs l JOIN app.clients c ON c.id = l.client_id
    20 |   6.453 |    129.1 |   20 | SELECT count(*) FROM app.logs WHERE action = $1
     1 |   8.347 |      8.3 |    0 | CREATE EXTENSION pg_stat_statements
     5 |   0.025 |      0.1 |   10 | SELECT * FROM app.clients WHERE ville = $1
     1 |   0.011 |      0.0 |    1 | SELECT extname, extversion FROM pg_extension WHERE extname = $1
(5 rows)

In this output, the join (10 calls, 16.5 ms on average, 165.2 ms in total) costs more than the count (20 calls at 6.5 ms, or 129.1 ms) even though it is called half as often: it is total_ms that ranks, not the number of calls. The CREATE EXTENSION also appears in the list, and the literal value 'login' has indeed been replaced by $1.

What to look at first:

• High total_ms: the query that consumes the most server time overall, this is your priority
• High mean_ms + low calls: a query that is slow on its own (missing index?)
• Very high calls + low mean_ms: a query called in a loop by the application (possibly N+1)

Note that pg_stat_statements normalizes queries: literal values are replaced by $1, $2, etc. This allows identical calls to be grouped.

Reset the statistics

After an optimization (adding an index, rewriting a query), reset to start from zero:

SELECT pg_stat_statements_reset();

The statistics start accumulating again immediately.

Diagnose with EXPLAIN ANALYZE

Once a slow query is identified, you still need to understand why. The execution plan is the answer: it is the sequence of operations the planner chose to produce the result, from the table scan to the join. Reading it is how you find out whether an index is missing, whether the statistics are wrong, or whether a sort spills to disk. Adding an index without having read this plan amounts to treating a symptom at random. This section focuses on reading a plan for monitoring purposes; the full approach to choosing and creating an index, and to understanding why PostgreSQL ignores one, is in Indexes and EXPLAIN ANALYZE (in French).

EXPLAIN vs EXPLAIN ANALYZE

The difference is decisive: EXPLAIN settles for an estimate without running anything, while EXPLAIN ANALYZE really runs the query to measure the time spent in each node. On an UPDATE or a DELETE, the modification is therefore applied: wrap it in a BEGIN and a ROLLBACK if you do not want to keep it.

Command	What it does	Runs the query?
EXPLAIN	Estimates the execution plan	No
EXPLAIN ANALYZE	Runs it and measures the real time	Yes
EXPLAIN (ANALYZE, BUFFERS)	Adds I/O buffer statistics (included by default since PG 18)	Yes

Swipe to see more

PostgreSQL 18: BUFFERS included automatically

Since PostgreSQL 18, EXPLAIN ANALYZE automatically includes the BUFFERS statistics. You no longer need to add it explicitly, but the syntax remains compatible.

Read a plan: Seq Scan, Index Scan, Hash Join

Here is a query on the app.logs table (100,000 rows) with no index on the filtered columns:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM app.logs WHERE action = 'login' AND client_id = 3;

 Seq Scan on logs  (cost=0.00..2234.00 rows=3877 width=25) (actual time=0.010..5.678 rows=4000.00 loops=1)
   Filter: ((action = 'login'::text) AND (client_id = 3))
   Rows Removed by Filter: 96000
   Buffers: shared hit=734
 Planning:
   Buffers: shared hit=77
 Planning Time: 0.402 ms
 Execution Time: 5.828 ms

Reading the plan:

Element	Value	Meaning
Seq Scan	-	Sequential scan of the whole table
cost=0.00..2234.00	-	Estimated cost (in arbitrary units)
rows=3877	estimated	Number of rows the planner expects
actual time=0.010..5.678	measured	Real time (first result → last result)
rows=4000.00	actual	Rows actually returned (PostgreSQL 18 shows the per-loop average with two decimals)
Rows Removed by Filter: 96000	-	96,000 rows read for nothing
Buffers: shared hit=734	-	734 blocks of 8 KB read from cache

Swipe to see more

The problem: PostgreSQL reads the whole table (734 blocks) to keep only 4% of the rows.

Fix it: add an index and compare

The query filters on two columns, so the index covers both. Their order in the index matters, whereas the order in which they are written in the WHERE is irrelevant to the planner: the index serves any prefix of its columns. You put first the column tested by equality and the most discriminating one, then the one with a possible range, in the order that serves the most queries. The full rule is in the Indexes and EXPLAIN guide (in French). On a production table, prefer CREATE INDEX CONCURRENTLY, which does not block writes during the build. Always measure before and after: a useless index slows down every write without speeding up a single read.

CREATE INDEX idx_logs_action_client ON app.logs (action, client_id);

Let's run the same query again:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM app.logs WHERE action = 'login' AND client_id = 3;

 Bitmap Heap Scan on logs  (cost=56.03..848.19 rows=3877 width=25) (actual time=0.148..0.681 rows=4000.00 loops=1)
   Recheck Cond: ((action = 'login'::text) AND (client_id = 3))
   Heap Blocks: exact=733
   Buffers: shared hit=733 read=5
   ->  Bitmap Index Scan on idx_logs_action_client  (cost=0.00..55.06 rows=3877 width=0) (actual time=0.094..0.094 rows=4000.00 loops=1)
         Index Cond: ((action = 'login'::text) AND (client_id = 3))
         Index Searches: 1
         Buffers: shared read=5
 Planning:
   Buffers: shared hit=22 read=1
 Planning Time: 0.120 ms
 Execution Time: 0.763 ms

PostgreSQL changes strategy: a Bitmap Index Scan first locates in the index the blocks that contain useful rows, then a Bitmap Heap Scan reads those blocks (all the node types are summarized in the table below).

Result: from 5.8 ms (Seq Scan) to 0.8 ms (Bitmap Index Scan), more than 7 times faster. Be careful about the reason: Heap Blocks: exact=733 and shared hit=733 read=5 show that the bitmap scan read 733 of the table's 734 blocks, almost as many as the Seq Scan. The 4,000 targeted rows (4%) are spread across almost every block. The gain comes from PostgreSQL no longer rechecking the filter on the 100,000 rows, not from a reduced read. An index only avoids reading blocks if the targeted rows are clustered or rare. The Index Searches: 1 line counts the descents into the index: it appears in PostgreSQL 18 output.

The common node types

A plan reads like a tree: each indented line is a node that feeds the one above it. Recognizing these eight nodes lets you interpret most plans. None is bad in itself: a Seq Scan on a 200-row table is the right choice; it is the combination of node and volume that reveals the problem.

Node	Meaning	When it appears
Seq Scan	Sequential scan, reads everything	No index, or small table
Index Scan	Reads through the index, then the table	Selective filter on an indexed column
Bitmap Index Scan	Builds a bitmap of the blocks to read	Moderately selective filter
Index Only Scan	Reads the index alone (covering)	All the columns are in the index
Hash Join	Join using a hash table	Joins on medium volumes
Nested Loop	Nested loop	Small volumes or a very selective index
Sort	Sort in memory or on disk	ORDER BY, DISTINCT, GROUP BY
HashAggregate	Aggregation by hashing	GROUP BY with few groups

Swipe to see more

Practical case: JOIN with aggregation

A join followed by a grouping and a sort stacks three nodes, a very common case in production. Look above all at the Sort Method line: quicksort Memory indicates a sort in memory, whereas an external merge Disk would signal a spill to disk, hence a work_mem (the memory granted to each sort) that is too low, a setting covered in the Configuration guide.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.nom, l.action, count(*)
FROM app.logs l
JOIN app.clients c ON c.id = l.client_id
GROUP BY c.nom, l.action
ORDER BY count(*) DESC;

 Sort  (cost=2970.94..2971.01 rows=25 width=27) (actual time=21.549..21.551 rows=25.00 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=738
   ->  HashAggregate  (cost=2970.11..2970.36 rows=25 width=27) (actual time=21.536..21.539 rows=25.00 loops=1)
         Group Key: c.nom, l.action
         Batches: 1  Memory Usage: 32kB
         Buffers: shared hit=735
         ->  Hash Join  (cost=1.11..2220.11 rows=100000 width=19) (actual time=0.018..10.795 rows=100000.00 loops=1)
               Hash Cond: (l.client_id = c.id)
               Buffers: shared hit=735
               ->  Seq Scan on logs l  (cost=0.00..1734.00 rows=100000 width=10) (actual time=0.004..3.033 rows=100000.00 loops=1)
                     Buffers: shared hit=734
               ->  Hash  (cost=1.05..1.05 rows=5 width=17) (actual time=0.007..0.008 rows=5.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=1
                     ->  Seq Scan on clients c  (cost=0.00..1.05 rows=5 width=17) (actual time=0.003..0.004 rows=5.00 loops=1)
                           Buffers: shared hit=1
 Planning:
   Buffers: shared hit=136 read=1
 Planning Time: 0.238 ms
 Execution Time: 21.584 ms

Reading: PostgreSQL builds a hash table on clients (5 rows, fast), then scans logs sequentially, joining through the hash. The HashAggregate groups the 25 combinations (5 customers × 5 actions). The final Sort is in memory (26 kB), with no spill to disk.

Watch the tables and autovacuum

PostgreSQL never modifies a row in place: an UPDATE writes a new version and marks the old one as dead. These dead tuples take up space until VACUUM runs. This is the MVCC mechanism (multiversion concurrency control), which lets readers never block writers, but which requires regular cleanup. This section shows where to read the state of this cleanup, table by table.

pg_stat_user_tables: the health of the tables

The pg_stat_user_tables view centralizes the activity statistics of each table:

SELECT relname,
       seq_scan,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_live_tup,
       n_dead_tup,
       last_vacuum::timestamp(0),
       last_autovacuum::timestamp(0),
       last_analyze::timestamp(0)
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

  relname  | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum |    last_analyze
-----------+----------+----------+-----------+-----------+-----------+------------+------------+-------------+-----------------+---------------------
 commandes |        1 |        0 |         5 |         0 |         0 |          5 |          0 |             |                 | 2026-10-20 14:53:04
 logs      |       34 |        1 |    100000 |         0 |         0 |     100000 |          0 |             |                 | 2026-10-20 14:53:04
 clients   |       18 |       27 |         5 |         0 |         0 |          5 |          0 |             |                 | 2026-10-20 14:53:04
(3 rows)

Three signals deserve your attention in this output: the ratio of sequential scans to index scans, the volume of dead tuples, and the date of the last autovacuum run. The field rules of thumb below are not official PostgreSQL thresholds, adapt them to your workload:

Metric	Rule of thumb	Action
seq_scan >> idx_scan	Ratio > 10:1 on a large table	Missing index, check with EXPLAIN
High n_dead_tup	More than 20% of the table's rows (a heuristic, the dead_pct column computed further down)	Autovacuum behind, or unable to clean
last_autovacuum NULL	Table never cleaned	Check the autovacuum parameters

Swipe to see more

Detect bloat (dead tuple ratio)

Bloat, the swelling of tables defined above, is measured by the share of dead tuples (deleted rows or old versions of updated rows) in the table: they take up space without being reusable as long as VACUUM has not run.

To simulate bloat on the lab (50,000 UPDATEs + 10,000 DELETEs):

UPDATE app.logs SET detail = 'updated_' || id WHERE id <= 50000;
DELETE FROM app.logs WHERE id > 90000;

Then look at the ratio, without waiting: autovacuum wakes up every 60 seconds (autovacuum_naptime) and cleans the table on its own as soon as the trigger threshold, computed further down, is exceeded. If n_dead_tup is already 0, it ran before you.

Replaying the simulation does not give the output below again: on the second pass, the DELETE ... WHERE id > 90000 no longer deletes anything (the rows are already gone), and the ratio drops to 35.7% instead of 40.0%. To reproduce bloat after a VACUUM, delete another range, for example DELETE FROM app.logs WHERE id BETWEEN 80001 AND 90000;. Measured that way, after the initial simulation then a VACUUM app.logs: DELETE 10000, then n_live_tup = 80000, n_dead_tup = 10000 and a ratio of 11.1%, not 40%. It is lower because the 50,000 old versions of the updated rows disappeared with the VACUUM.

This 11.1% ratio stays below the 20% heuristic of the table above and below the autovacuum trigger threshold (on the order of 18,050 dead rows, that is 50 + 20% of the 90,000 rows counted at the last VACUUM, a calculation and not a measurement, detailed further down): autovacuum therefore does not run. To get back to a ratio close to 40%, replay the 50,000-row UPDATE before deleting the range.

SELECT relname, n_live_tup, n_dead_tup,
       CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 1)
            ELSE 0 END AS dead_pct,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

  relname  | n_live_tup | n_dead_tup | dead_pct | total_size
-----------+------------+------------+----------+------------
 logs      |      90000 |      60000 |     40.0 | 13 MB
 commandes |          5 |          0 |      0.0 | 32 kB
 clients   |          5 |          0 |      0.0 | 48 kB
(3 rows)

40% dead tuples on logs: the table takes up far more space than it should. This is a typical case where a VACUUM is needed, and a VACUUM FULL if you also want to give the space back to the system: both are presented in the "Maintain" section.

pg_stat_user_indexes: unused indexes

Unused indexes take up space and slow down writes (INSERT, UPDATE, DELETE). Identify them:

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY pg_relation_size(indexrelid) DESC;

      indexrelname      | index_size | idx_scan
------------------------+------------+----------
 logs_pkey              | 3320 kB    |        2
 idx_logs_action_client | 1176 kB    |        1
 clients_pkey           | 16 kB      |       27
 clients_email_key      | 16 kB      |        0
 commandes_pkey         | 16 kB      |        0
(5 rows)

An index with idx_scan = 0 (or very low over a long period) is a candidate for removal. Be careful: wait at least one full business cycle (month end, quarterly reporting…) before concluding.

Cache hit ratio: pg_statio_user_tables

The cache hit ratio measures the efficiency of shared_buffers, PostgreSQL's own memory cache. A low ratio means that PostgreSQL goes to disk too often:

SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       CASE WHEN heap_blks_hit + heap_blks_read > 0
            THEN round(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 1)
            ELSE 0 END AS cache_hit_pct
FROM pg_statio_user_tables
ORDER BY relname;

  relname  | heap_blks_read | heap_blks_hit | cache_hit_pct
-----------+----------------+---------------+---------------
 clients   |              1 |            52 |          98.1
 commandes |              0 |            10 |         100.0
 logs      |            736 |        339477 |          99.8
(3 rows)

On a hot OLTP database (steady transactional load), you generally look for a very high ratio, but always interpret it with context (disk, memory, workload). This counter does not distinguish what was read from the physical disk from what was already in the kernel cache (Linux page cache): a blks_read may in fact be served from memory without any real disk access.

At the database level:

SELECT datname,
       blks_read,
       blks_hit,
       round(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname NOT LIKE 'template%' AND blks_read > 0;

  datname  | blks_read | blks_hit | cache_hit_pct
-----------+-----------+----------+---------------
 postgres  |       347 |     4951 |         93.45
 lab_admin |      1304 |   865165 |         99.85
(2 rows)

99.85% on lab_admin: the cache works correctly for this lab. In production, a durably low ratio (below 90-95% on an OLTP workload, a common rule of thumb and not an official threshold) is a signal worth investigating: increase shared_buffers, check the memory sizing, or identify the queries that read massively (see the Configuration guide).

pg_stat_io: observe I/O by backend type

Knowing that the server reads a lot from disk is not enough, you also need to know who reads. An I/O spike caused by autovacuum does not call for the same reaction as a spike caused by application queries. The pg_stat_io view answers exactly that question, by breaking the counters down by process type.

Overview (PostgreSQL 16+)

pg_stat_io is a view introduced in PostgreSQL 16 that details I/O operations per backend_type / object / context combination. These are cluster-wide aggregated statistics, not I/O per individual connection. It complements the earlier partial views (such as pg_statio_user_tables) with a unified picture:

SELECT backend_type, object, context,
       reads, writes, hits
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads + writes DESC
LIMIT 8;

    backend_type    |  object  | context | reads | writes |  hits
--------------------+----------+---------+-------+--------+--------
 standalone backend | wal      | init    |       |      1 |
 checkpointer       | relation | normal  |       |   1996 |
 walwriter          | wal      | normal  |       |      4 |
 client backend     | wal      | init    |       |      1 |
 client backend     | wal      | normal  |     0 |   3365 |
 standalone backend | relation | normal  |   479 |   1038 |  92643
 client backend     | relation | normal  |   769 |      0 | 869308
 standalone backend | wal      | normal  |     3 |    760 |
(8 rows)

Reading: the client backend rows dominate the reads (769 reads) with an excellent cache ratio (869,308 hits). You have to read by object. On relation (the files of the tables and indexes), the checkpointer writes 1,996 blocks. On wal (the log), it is the client backend rows that write (3,365 in normal, plus 1 in initialization), then the standalone backend (760), the walwriter accounting for only 4. The standalone backend rows come from the initialization of the cluster (initdb), before the first start of the server.

Why the ranking looks wrong. It is not decreasing at a glance, and that is normal: reads + writes is NULL when reads is NULL, and with DESC PostgreSQL puts the NULL values first (NULLS FIRST is the default), hence the rows without reads at the top of the list (checked on the server). For a real ranking, use ORDER BY COALESCE(reads, 0) + COALESCE(writes, 0) DESC. Do not simply add NULLS LAST: it pushes to the end of the list the rows that only write (the checkpointer, the walwriter), which are nonetheless real writers, and they disappear from a LIMIT 8.

And autovacuum? No autovacuum worker row appears in these eight rows: during the replay, it had not cleaned anything yet, so there was no I/O to count. If autovacuum has already run on your instance, an autovacuum worker row may appear (remove LIMIT 8 to look for it).

This view is particularly useful to understand which type of process generates the I/O: user queries, autovacuum, the checkpointer?

New in PostgreSQL 18: I/O per individual backend and columns in bytes

PostgreSQL 18 enriches pg_stat_io with columns in bytes (read_bytes, write_bytes, extend_bytes) that complement the counters of number of operations. For genuine tracking per individual backend (by PID), PG 18 adds the pg_stat_get_backend_io() function and pg_stat_reset_backend_stats(), not to be confused with the pg_stat_io view, which remains aggregated by backend type.

Maintain: VACUUM, ANALYZE, REINDEX

The four maintenance commands answer distinct needs and do not have the same cost at all. VACUUM recycles space without blocking the application, VACUUM FULL gives space back to the system but locks the table, ANALYZE refreshes the planner statistics, REINDEX rebuilds fragmented indexes. Do not confuse them: the locking version (VACUUM FULL) is always the last resort, never the reflex. Before any operation that rewrites a table, check that a recent backup exists and that it restores: see Backup and restore (in French). The end of the section returns to autovacuum, which automates VACUUM and ANALYZE: how to check that it runs, which threshold triggers it and how to adjust it.

VACUUM: free reusable space

VACUUM marks dead tuples as reusable. It does not give space back to the operating system, it makes it available for future inserts into the same table.

VACUUM VERBOSE app.logs;

INFO:  vacuuming "lab_admin.app.logs"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO:  finished vacuuming "lab_admin.app.logs": index scans: 1
pages: 0 removed, 1101 remain, 1101 scanned (100.00% of total), 0 eagerly scanned
tuples: 60000 removed, 90000 remain, 0 are dead but not yet removable
removable cutoff: 809, which was 0 XIDs old when operation ended
new relfrozenxid: 760, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1101 pages set all-visible, 438 pages set all-frozen (0 were all-visible)
index scan needed: 441 pages from table (40.05% of total) had 60000 dead item identifiers removed
index "logs_pkey": pages: 415 in total, 27 newly deleted, 27 currently deleted, 0 reusable
index "idx_logs_action_client": pages: 147 in total, 42 newly deleted, 42 currently deleted, 0 reusable
avg read rate: 65.747 MB/s, avg write rate: 162.998 MB/s
buffer usage: 3624 hits, 144 reads, 357 dirtied
WAL usage: 2533 records, 327 full page images, 3287874 bytes, 0 buffers full
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s
[...]
VACUUM

60,000 dead tuples removed (the 50,000 old versions from the UPDATEs and the 10,000 rows of the DELETE), 90,000 rows remain, and the table's 1,101 pages were all scanned. The freed pages stay in the table file, but are now reusable. If autovacuum ran before you, the number of removed rows will be lower, even zero: it has already done the work. The [...] marker replaces the VACUUM pass over the associated TOAST table (the storage for large values), empty here.

VACUUM does not lock the table

VACUUM (without FULL) takes a ShareUpdateExclusive lock, it prevents neither reads nor writes. It can run in the background while the application works normally.

On a replicated instance, the cleanup done by VACUUM is also replayed on the standby (the copy that replays the primary server's logs), where it can conflict with a long read and cause it to be cancelled. This case, and the max_standby_streaming_delay setting that governs it, are covered in the PostgreSQL replication guide.

VACUUM FULL: compact the table

VACUUM FULL rewrites the table entirely to give space back to the operating system. It is the built-in way to reduce the physical size of the file.

Start by measuring the size of the table and its indexes. The plain VACUUM of the previous section returned nothing to the file system:

SELECT pg_size_pretty(pg_total_relation_size('app.logs'));

 pg_size_pretty
----------------
 13 MB
(1 row)

Then run the compaction and measure again:

VACUUM FULL app.logs;
SELECT pg_size_pretty(pg_total_relation_size('app.logs'));

VACUUM
 pg_size_pretty
----------------
 7928 kB
(1 row)

From 13 MB to 7,928 kB (about 7.7 MB), nearly 40% of space recovered at the file system level. VACUUM FULL also rebuilds the indexes, and the table now holds only 90,000 rows.

VACUUM FULL: exclusive lock + disk space

VACUUM FULL takes an exclusive lock (AccessExclusiveLock) on the table for the whole operation. No read or write is possible. On a 100 GB table, this can take minutes or even hours. Never run it at peak time.

VACUUM FULL writes a new complete copy of the table before deleting the old one. You therefore need free disk space approximately equal to the size of the table. Check the available space before starting the operation.

An alternative without a prolonged exclusive lock: pg_repack (a third-party extension) rebuilds the table online, taking an exclusive lock (ACCESS EXCLUSIVE, according to its documentation) only briefly, at the start and then at the end of the operation; in between, the table remains readable and modifiable.

Its conditions: the table must have a primary key, or at least a total unique index (without a WHERE clause, so covering all the rows) on a NOT NULL column. It also needs free disk space: the documentation states about double the size of the table and its indexes. During the operation, DDL commands (ALTER TABLE, CREATE INDEX) on the table are impossible. These points come from the pg_repack 1.5.3 documentation (the latest version released at the time of checking).

ANALYZE: update the planner statistics

The query planner uses statistics on the distribution of the data to choose the best plan. If these statistics are stale (after a large INSERT or DELETE), the planner may make bad choices.

ANALYZE app.logs;

ANALYZE is lightweight and takes no blocking lock. Autovacuum runs it automatically, but after a bulk data load, run it manually.

REINDEX: rebuild bloated indexes

B-tree indexes can become fragmented after many updates: this is fragmentation. REINDEX rebuilds the index from scratch, but its plain form takes a lock that blocks writes on the table during the rebuild (the CONCURRENTLY variant, below, avoids this):

REINDEX (VERBOSE) INDEX app.logs_pkey;

INFO:  index "logs_pkey" was reindexed
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s
REINDEX

Plain REINDEX

-- One index
REINDEX INDEX app.logs_pkey;

-- All the indexes of a table
REINDEX TABLE app.logs;

-- All the indexes of a database
REINDEX DATABASE lab_admin;

REINDEX CONCURRENTLY

-- Without locking writes (PG 12+)
REINDEX (CONCURRENTLY) INDEX app.logs_pkey;

-- The whole table
REINDEX (CONCURRENTLY) TABLE app.logs;

REINDEX CONCURRENTLY is recommended in production: it rebuilds the index without locking writes. A few caveats to know about:

• It is slower and more resource-hungry than a plain REINDEX (double build of the index)
• On failure (crash, cancellation), the index may be left in an INVALID state; you must then drop it manually (DROP INDEX) and rerun the REINDEX
• It temporarily needs about double the disk space of the index

Is autovacuum doing its job?

Autovacuum is the process that automatically runs VACUUM and ANALYZE. Check that it runs regularly:

SELECT relname, n_dead_tup,
       last_autovacuum::timestamp(0),
       last_autoanalyze::timestamp(0)
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Right after this lab's VACUUM FULL, the query returns no rows: no table has any dead tuples left.

 relname | n_dead_tup | last_autovacuum | last_autoanalyze
---------+------------+-----------------+------------------
(0 rows)

If last_autovacuum is NULL or old on a table with many dead tuples, autovacuum is behind. Possible causes:

• autovacuum_naptime too high (default: 60 s, fine for most cases)
• autovacuum_max_workers insufficient (default: 3)
• The threshold is not reached (50 + 20% of reltuples, the estimated number of rows in the table, by default: formula detailed further down)

If, on the contrary, last_autovacuum is recent while n_dead_tup does not go down, autovacuum runs but cleans nothing: an old idle in transaction connection is holding back the dead tuples that are still visible to it. Look for it in pg_stat_activity.

Key autovacuum parameters

All the autovacuum parameters are visible in pg_settings, with their effective value and description. Above all, remember the threshold and scale factor pair: it determines from what volume of dead tuples a table becomes a candidate for cleanup, and it is the one you adjust when a large table is never processed.

SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

Parameter	Default	Meaning
autovacuum	on	Enables/disables autovacuum
autovacuum_vacuum_threshold	50	Minimum number of dead tuples before VACUUM
autovacuum_vacuum_scale_factor	0.2	Fraction of the table to add to the threshold
autovacuum_analyze_threshold	50	Minimum number of modifications before ANALYZE
autovacuum_analyze_scale_factor	0.1	Fraction of the table to add to the threshold
autovacuum_max_workers	3	Maximum number of simultaneous workers
autovacuum_naptime	60	Delay between two cycles (seconds)
autovacuum_vacuum_max_threshold	100000000	Maximum threshold (new in PG 18)

Swipe to see more

Threshold formula (PostgreSQL 18 documentation): autovacuum triggers when the number of obsolete rows since the last VACUUM exceeds Minimum(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples). The scale factor applies to pg_class.reltuples, the estimate of the number of rows updated notably by VACUUM and ANALYZE, and not to n_live_tup.

Worked example: for a table whose reltuples is 100,000, with the defaults, 50 + 0.2 × 100,000 = 20,050 rows. This is an order of magnitude, not an exact threshold: reltuples and the obsolescence counters are estimates (the statistics system is eventually consistent, that is, it catches up with reality with a slight delay), and the daemon only checks at each pass (autovacuum_naptime).

New in PostgreSQL 18: autovacuum_vacuum_max_threshold

PostgreSQL 18 introduces autovacuum_vacuum_max_threshold, which caps the trigger threshold. Useful for very large tables: without a cap, a table of 500 million rows would only trigger autovacuum after 100 million dead tuples (50 + 0.2 × 500 million). The default of 100,000,000 is conservative: it changes nothing below 500 million rows, so lower it if your tables are very large.

Adjust per table

For a critical table with many writes, tighten the thresholds:

ALTER TABLE app.logs SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.02
);

These parameters apply only to this table, the others keep the global configuration.

Continuous monitoring: Prometheus metrics

For continuous monitoring in production, export the PostgreSQL metrics to Prometheus with postgres_exporter. The installation of the exporter and the full catalogue of its metrics are in the Prometheus exporters guide (in French): this table keeps only the alert thresholds specific to PostgreSQL monitoring.

Metric	Source	Alert threshold
pg_stat_database_blks_hit and pg_stat_database_blks_read	pg_stat_database	Ratio computed in PromQL, a significant drop from the baseline
pg_stat_user_tables_n_dead_tup	pg_stat_user_tables	> 10,000 per table (to adapt)
pg_stat_activity_count by state	pg_stat_activity	> 80% of max_connections (client backend)
pg_stat_statements_seconds_total (stat_statements collector, disabled by default)	pg_stat_statements	Top 5 rising
pg_stat_archiver_failed_count	pg_stat_archiver	> 0

Swipe to see more

A very useful alert: the number of idle in transaction connections staying above 0 for more than 5 minutes.

Troubleshooting

This table links the symptoms observed in production to the views and commands presented above. Two causes come up often: stale statistics that mislead the planner, and dead tuples that autovacuum could not clean. Always check these two points before blaming the hardware or the memory configuration.

Symptom	Likely cause	Solution
Queries getting slower and slower	Accumulated dead tuples + stale stats	VACUUM ANALYZE on the affected tables
Seq Scan on a large table	Missing index or stale statistics	Check with EXPLAIN, add the index, ANALYZE
pg_stat_statements empty	Extension not created in this database	CREATE EXTENSION pg_stat_statements;
Autovacuum runs but cleans nothing	Old idle in transaction connection holding back dead tuples	Identify with pg_stat_activity, close the connection
Table does not shrink after DELETEs	VACUUM does not give space back to the file system	VACUUM FULL (exclusive lock) or pg_repack
VACUUM FULL too long	Table > 10 GB	Use pg_repack (brief exclusive lock only at the start and the end; conditions and required disk space: see the box in the "VACUUM FULL" section)
Cache hit ratio < 90%	Often shared_buffers too small, but also a dataset larger than memory or large sequential scans	First check which queries read (pg_stat_statements), then adjust shared_buffers (starting point: 25% of RAM, see the Configuration guide)
REINDEX locks writes	Plain REINDEX takes a lock	Use REINDEX CONCURRENTLY
ERROR: relation "pg_stat_io" does not exist	PostgreSQL < 16	View available only since PG 16
ERROR: pg_stat_statements must be loaded via "shared_preload_libraries" on CREATE EXTENSION	Restart forgotten after the ALTER SYSTEM	Restart the service, then recreate the extension. The name of the systemd unit depends on the installation: postgresql@18-main is that of a Debian cluster installed from a package

Swipe to see more

Put it into practice

The lab has you load pg_stat_statements, fix a query that the planner reads sequentially, then recover the dead space that deletions left behind. The validation is not about the presence of an index: it queries the planner's answer (the plan chosen for the query) and the state of the table (space recovered). An index that the planner ignores fixes nothing, and the lab detects it.

Guided lab Practice

Learn how to do it

Make the server observable, read a plan, and reclaim dead space

$ dsoxlab run pg-06-observer

Environment: VM·Duration: ~40 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start pg-06-observer checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run pg-06-observer
dsoxlab check pg-06-observer

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Key takeaways

• pg_stat_activity is the first reflex: who is connected, in what state, for how long.
• pg_stat_statements identifies the costly queries, install it on every production database.
• EXPLAIN ANALYZE (with BUFFERS included in PG 18) is the reference tool to understand why a query is slow.
• Uncleaned dead tuples make tables swell (bloat), watch n_dead_tup in pg_stat_user_tables.
• VACUUM makes space reusable without locking. VACUUM FULL compacts but locks the table for reads and writes alike.
• Autovacuum is enabled by default, never disable it. It triggers at 50 + 0.2 × reltuples obsolete rows with the original settings (capped by autovacuum_vacuum_max_threshold in PG 18): adjust this pair per table if a large table is never processed.
• REINDEX CONCURRENTLY is preferable in production, but a failure can leave an INVALID index.
• The cache hit ratio is a useful but imperfect indicator, it does not distinguish the physical disk from the kernel cache. Interpret it in context.
• pg_stat_io (PG 16+) shows which type of process generates the I/O. PG 18 adds pg_stat_get_backend_io() for tracking per individual backend.

Knowledge check

Eight questions on what people wrongly believe about monitoring: what EXPLAIN ANALYZE really does, adding to shared_preload_libraries, the query to handle first, what VACUUM gives back (or not) to the system, the cause of an autovacuum that cleans nothing, the threshold that triggers it, a Seq Scan that is not always a problem, and the risks of REINDEX CONCURRENTLY.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →