PostgreSQL 18
I/O attributed to one backend at last
For anyone who has watched one connection ruin a cluster and had only sampling to prove it.
Reference page, revised in place. Last updated .
The question that used to need a guess
A cluster is slow, one connection is obviously responsible, and the question is how much of the storage it is using. Until now, PostgreSQL could not answer it. The I/O statistics view groups by process type, which puts every client connection on the server into one bucket. The statements extension attributes blocks to a statement, aggregated across every session that has ever run it, which is a different question with a similar shape. The per-table counters attribute to a table. Nothing attributed to a connection.
So the answer was assembled. Sample the activity view often enough to see what the session was running, look up those statements in the extension, divide by how many sessions were running them, and present the result with a caveat. It worked well enough to argue with, which is a low bar, and it fell apart entirely when the expensive session was doing something that the extension had not seen before.
PostgreSQL 18 reports it. Not through a view, which is the first thing to know, but through a function you call with a process identifier.
SELECT object, context, reads, writes
FROM pg_stat_get_backend_io(pg_backend_pid());ERROR: function pg_stat_get_backend_io(integer) does not exist
LINE 2: FROM pg_stat_get_backend_io(pg_backend_pid());
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.What a single session accounts for
The sequence below runs in one session: create a table, write eighty thousand rows into it, then ask the server what that session did. The pause is not decoration. A backend accumulates its counters locally and publishes them to shared memory on a short interval, so a read taken in the same breath as the work reports nothing at all, and the first time you try this without a pause you will conclude the function is broken.
CREATE TABLE staging_rows (row_id bigint, body text);
INSERT INTO staging_rows SELECT g, md5(g::text) FROM generate_series(1, 80000) AS g;
SELECT pg_sleep(1);
SELECT object, context, extends, pg_size_pretty(extend_bytes) AS extended, writes, hits
FROM pg_stat_get_backend_io(pg_backend_pid())
WHERE extends > 0 OR writes > 0
ORDER BY object, context;
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written, wal_buffers_full
FROM pg_stat_get_backend_wal(pg_backend_pid());
SELECT pg_stat_reset_backend_stats(pg_backend_pid());
SELECT pg_sleep(1);
SELECT sum(extends) AS extends_after_reset, sum(hits) AS hits_after_reset
FROM pg_stat_get_backend_io(pg_backend_pid());CREATE TABLE
INSERT 0 80000
pg_sleep
----------
(1 row)
object | context | extends | extended | writes | hits
----------+---------+---------+----------+--------+-------
relation | normal | 749 | 6008 kB | 0 | 81981
wal | normal | | | 2 |
(2 rows)
wal_records | wal_fpi | wal_written | wal_buffers_full
-------------+---------+-------------+------------------
80094 | 2 | 7519 kB | 0
(1 row)
pg_stat_reset_backend_stats
-----------------------------
(1 row)
pg_sleep
----------
(1 row)
extends_after_reset | hits_after_reset
---------------------+------------------
0 | 163
(1 row)There is a lot in that, and most of it is the kind of thing that was previously a matter of opinion.
The session grew its table by seven hundred and forty-nine blocks, six megabytes, and wrote none of them out itself, because the checkpointer had not yet been asked to and nothing had evicted them. It hit the buffer pool eighty-two thousand times. It generated eighty thousand log records and seven and a half megabytes of write-ahead log, and the log accounting is available from a second function in the same family, which matters because log volume per session was previously not measurable at all outside the statements extension.
The reset at the end does what it says, and the second read shows something more interesting than a row of zeros. The extension count is genuinely back to nothing. The buffer hits are already at a hundred and sixty-three, because the two statements that performed the reset and then read the result did their own work, and that work is attributed to the same session. Per-backend counters include the cost of asking about them, which is small and is the sort of thing worth knowing before you build a measurement harness on top of it.
Rows exist before there is anything to put in them
A subtlety that catches collectors rather than people. The function returns the full grid of object and context combinations for any live backend, whether or not that backend has done anything.
SELECT count(*) AS rows_for_a_backend_that_has_done_nothing
FROM pg_stat_get_backend_io(pg_backend_pid()); rows_for_a_backend_that_has_done_nothing
------------------------------------------
8
(1 row)Eight rows from a session that has run one statement. So a query that calls this function for every backend on a busy server produces eight rows per connection before filtering, which on a cluster with several hundred connections is a few thousand rows assembled on every scrape. That is not expensive in absolute terms and it is not free either, and it is the reason to filter on the server rather than in the collector.
The numbers do not outlive the session
This is the limitation that decides how the feature can be used, and it is the one thing on this page that nothing in the release notes says out loud.
The demonstration needs a second connection that is not the one asking. Below, a second session is opened, made to do a measurable amount of work, and observed from the first. Then it disconnects and is observed again.
CREATE EXTENSION dblink;
SELECT dblink_connect('worker', 'dbname=' || current_database());
SELECT dblink_exec('worker', 'CREATE TABLE worker_rows (row_id bigint, body text)');
SELECT dblink_exec('worker', 'INSERT INTO worker_rows SELECT g, md5(g::text) FROM generate_series(1, 40000) AS g');
SELECT dblink_exec('worker', 'DO $sleep$ BEGIN PERFORM pg_sleep(1); END $sleep$');
CREATE TEMP TABLE observed AS
SELECT * FROM dblink('worker', 'SELECT pg_backend_pid()') AS t(backend_pid integer);
SELECT sum(extends) AS extends_we_can_see, sum(writes) AS writes_we_can_see
FROM pg_stat_get_backend_io((SELECT backend_pid FROM observed));
SELECT dblink_disconnect('worker');
SELECT pg_sleep(1);
SELECT count(*) AS rows_after_it_disconnects
FROM pg_stat_get_backend_io((SELECT backend_pid FROM observed));CREATE EXTENSION
dblink_connect
----------------
OK
(1 row)
dblink_exec
--------------
CREATE TABLE
(1 row)
dblink_exec
----------------
INSERT 0 40000
(1 row)
dblink_exec
-------------
DO
(1 row)
SELECT 1
extends_we_can_see | writes_we_can_see
--------------------+-------------------
376 | 2
(1 row)
dblink_disconnect
-------------------
OK
(1 row)
pg_sleep
----------
(1 row)
rows_after_it_disconnects
---------------------------
0
(1 row)While the session was connected, another session could see exactly what it had done: three hundred and seventy-six relation extensions and a couple of log writes. A second after it disconnected, the same call returns nothing at all. Not zeros, which would at least be a row. Nothing.
That makes this a live diagnostic and not a history. A connection that arrives, consumes a great deal of I/O and goes away leaves no trace here, and a scraper on a one-minute interval will miss most short-lived sessions entirely. It also means the counters cannot be used to answer any question phrased as “which connections cost the most yesterday”, which is the question people ask first.
What it is genuinely good for is the incident in progress. A session is holding the cluster hostage, it is visible in the activity view, and you can now say with a number how much of the storage it is using rather than inferring it. It is also the honest way to settle an argument between two teams about whose connection pool is doing the reading, because it attributes rather than apportions.
Getting anything durable out of it means sampling: call the function for the backends in the activity view on an interval and keep the answers, accepting that anything shorter-lived than the interval is invisible. That is more or less how active session history has always been built, and this release gives that technique real numbers to collect instead of estimates.
Designing the sampler
Since the counters vanish with the session, everything durable built on them is a sampler, and the design of that sampler decides what questions it can answer.
Start from the activity view rather than from a list of process identifiers. The activity view is the only place that says which backends exist, what database and user each belongs to, and what it is currently running, and none of that is available from the I/O function itself. Joining them gives a row that is actually interpretable: this much I/O, by this user, running this statement.
Take the difference between samples rather than the level. A long-lived pooled connection accumulates from the moment it was opened, so its raw counters describe a workday rather than a moment, and a ranking built on them puts whichever connection has been alive longest at the top regardless of what it is doing. The reset function is the alternative and it is the wrong tool here: resetting a backend’s counters destroys information that anything else sampling the same cluster was relying on.
Choose the interval against the sessions you are trying to catch. A connection pool that keeps sessions for hours is sampled adequately at a minute. An application that opens a connection per request and closes it is invisible at any interval you would be willing to run, and for that shape the per-statement counters in the statements extension remain the right source, because they survive the session that produced them.
Expect the numbers to disagree slightly with the cluster-wide ones. The per-backend counters cover backends that exist at the moment of the sample; the shared ones cover everything that ever ran, including processes that are not backends at all. Summing the first and comparing it to the second is a reconciliation that will never quite balance, and chasing the difference is a way to lose a day.
The questions it settles, and the ones it does not
Worth being explicit about the boundary, because the feature is easy to over-sell to a team that has wanted it for years.
It settles attribution between connections that exist right now. Which of the three application pools connected to this cluster is doing the reading, whether the reporting user or the transactional user is responsible for the current load, whether a single stuck session is holding the storage or whether the load is spread across fifty: all of those become one query instead of an argument.
It settles the pooler question in particular, which comes up constantly and has never had an answer. A pooled connection is shared by many application requests, so per-statement attribution cannot tell you which pool member is expensive, and per-process attribution now can. On a cluster fronted by several poolers with different configurations, that is the difference between tuning the right one and tuning all of them.
It does not settle anything historical. It cannot tell you what a connection did before the last sample, what a disconnected session did at all, or how today compares with last week, and building a history on top of it means accepting whatever the sampler missed. It also says nothing about why: the counters describe volume and not intent, so a backend reading a great deal may be running a legitimate report or a missing index, and answering that is still a question for the plan.
What it costs to turn on
Nothing, in the sense that there is nothing to turn on. The counters are maintained for every backend regardless, which is the same trade the server makes everywhere else in the statistics family, and the reset function is there because a long-lived pooled connection otherwise accumulates a lifetime of activity that says nothing about what it is doing now.
The cost worth planning for is the sampling, not the feature. Calling the function once per backend on a large server is thousands of rows per sample, and a sample interval short enough to catch the sessions this exists to find is short enough to matter. Start with the sessions the activity view says are actually doing something, rather than with all of them.