Postgres observability for AI agents

A client that does not repeat itself

Every assumption underneath conventional database monitoring comes from the same place: the client is a program, the program was deployed, and the set of statements it can emit was fixed at that moment. A release changes the set. Nothing else does. That single property is why a top-ten-by-total-time list is a useful artefact, why a statement identifier is worth keying a baseline on, and why a connection pool sized last quarter is still the right size today.

An agent breaks the property rather than stressing it. The statements it emits are a function of a conversation, so the set is open-ended, and it grows in a direction nobody chose. Four consequences follow, and they arrive in a particular order.

The set of distinct statements stops being bounded, which matters because the place the server keeps them is not. Nobody is watching any individual response, so the usual detector for a slow query, a person waiting for a page, is absent. Sessions get shorter, sometimes to the length of one tool call. And an agent will run an expensive query a thousand times in an afternoon without ever noticing it did so, because there is nothing in the loop that gets bored.

None of these are new failure modes. Every one of them existed before, in a batch job with dynamic SQL or in an analyst with a psql prompt. What is new is the rate, and the honest reason to write this page rather than point at the older material is that the rate is enough to move each of them from a curiosity to the first thing to check.

Everything is keyed on who asked

Before any of the measurement below is possible, the agent needs an identity of its own, because every view that could answer a question about it is grouped by role.

pg_stat_statements holds one row per combination of database, user and statement. pg_stat_activity reports a role and an application name per backend. pg_stat_database counts per database. If the agent connects as the same role as the application, none of those can separate them, and the first question anybody asks during an incident, whether this was us or the model, has no answer at all.

So the role comes first, and it is worth carrying a little more than a password. A role can hold its own settings, applied at connection time, and that is the cheapest control surface in the server. Everything on this page runs on a scratch database, so the fixture the rest of it uses is set up in the same breath:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE TABLE public.reading (
  reading_id bigint      PRIMARY KEY,
  sensor_id  integer     NOT NULL,
  taken_at   timestamptz NOT NULL,
  value      numeric     NOT NULL
);
INSERT INTO public.reading
SELECT g, g % 500, now() - (g * interval '1 second'), random() * 100
FROM generate_series(1, 200000) g;
CREATE INDEX reading_sensor_id_idx ON public.reading (sensor_id);
ANALYZE public.reading;

CREATE ROLE reporting_agent LOGIN;
GRANT SELECT ON public.reading TO reporting_agent;
ALTER ROLE reporting_agent SET application_name          = 'assistant';
ALTER ROLE reporting_agent SET statement_timeout         = '15s';
ALTER ROLE reporting_agent SET idle_session_timeout      = '60s';
ALTER ROLE reporting_agent SET temp_file_limit           = '256MB';
ALTER ROLE reporting_agent SET default_transaction_read_only = on;

SELECT pg_stat_statements_reset();

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname = 'reporting_agent';

Five settings, none of which required a restart, a reload or a line in the configuration file, and all of which arrive automatically on every connection that role makes. The application name is the one people leave out, and it is the one that makes a running backend attributable in the two seconds you have during an incident.

Where a blanket grant is what you actually want rather than a table list, pg_read_all_data has existed since PostgreSQL 14 and reads everything without being able to write anything. It is a larger grant than most agents need and a much smaller one than superuser, which is the comparison that usually matters, because superuser is what somebody reaches for when the table list turns out to be tedious.

One of those settings is not a control

default_transaction_read_only looks like the answer to the whole safety question and is not, for a reason worth seeing rather than being told.

SET default_transaction_read_only = on;
SET default_transaction_read_only = off;
SHOW default_transaction_read_only;

Both statements succeed and the session ends up writable. The setting is user-context, which means any session may change it, which means it is a default and not a boundary. It is still worth setting, because it turns an accidental write into an error rather than a change, and because an agent that has to explicitly disable read-only mode has crossed a line you can see in the log. It just is not the thing standing between the model and your data.

The thing standing between the model and your data is the grant, and the grant behaves the way a grant behaves:

SET ROLE reporting_agent;
INSERT INTO public.reading VALUES (999999, 1, now(), 1.0);

That is the same refusal a human would get, produced by the same mechanism, and it does not care what the model was told in its instructions. The general version of this argument, applied to actions rather than to statements, is on what it takes to let an agent near production; the database-side half of it is one GRANT and no cleverness at all.

One of the five settings above is different in kind, and the difference is visible in the catalog:

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('statement_timeout', 'lock_timeout', 'transaction_timeout',
               'idle_in_transaction_session_timeout', 'idle_session_timeout',
               'temp_file_limit', 'block_size')
ORDER BY name;

Everything in that list with a user context can be raised by the session that holds it, so it bounds an accident rather than an adversary. temp_file_limit reports superuser, which means a role given a value by ALTER ROLE cannot raise it afterwards. That asymmetry makes it the most useful single setting on the list for this workload: it caps the disk a runaway sort or hash can consume, at a value the caller cannot argue with, and what a spill actually costs is the thing it is capping.

Note also which row is missing on the oldest of the three servers and present on the other two. transaction_timeout arrived in PostgreSQL 17, and it is the only one of the four time limits that bounds a whole transaction including the time the client spends thinking between statements. That is precisely the shape of an agent’s transaction, where the gap between two statements contains a model inference. Which limit catches which failure is set out in how to set statement_timeout safely, and the answer does change when the client’s think time is measured in seconds.

The statement table is a fixed-size cache

pg_stat_statements keeps at most pg_stat_statements.max entries, five thousand by default, fixed at server start. When more distinct statements appear than it holds, the least-executed entries are discarded and a counter in pg_stat_statements_info records that it happened.

For a deployed application that limit is generous, because the number of distinct statements is a property of the code. For an agent it is a budget being spent by something that does not know the budget exists. The question is therefore not whether five thousand is enough, it is how fast the agent creates entries, and that depends on something more specific than “it writes its own SQL”.

Statements are normalised before they are counted: constants are replaced with placeholders, so a thousand calls with a thousand different values collapse into one row. What does not collapse is a difference in the shape. Two statements selecting different columns from the same table are two entries forever. And until recently, two statements with different numbers of elements in a list were also two entries, which is the single most common way an agent generates shapes without meaning to, because a list is built from whatever the previous step returned.

SELECT count(*) FROM public.reading WHERE sensor_id IN (1);
SELECT count(*) FROM public.reading WHERE sensor_id IN (1, 2);
SELECT count(*) FROM public.reading WHERE sensor_id IN (1, 2, 3);
SELECT value FROM public.reading WHERE reading_id = 1;
SELECT value, taken_at FROM public.reading WHERE reading_id = 1;
SELECT value, taken_at, sensor_id FROM public.reading WHERE reading_id = 1;

SELECT calls, left(query, 64) AS query
FROM pg_stat_statements
WHERE query LIKE 'SELECT%public.reading%'
ORDER BY query;

Six statements went in. On 14 and on 17, six rows come back. On 18, five do: the two- and three-element lists have merged into one row with two calls, rendered with a placeholder and a comment showing that a list was folded. PostgreSQL 18 changed the identifier computation so that a list of constants is jumbled on its first and last element only, which makes the length of the list stop mattering.

Two details in that result are worth keeping. The single-element list did not merge with the longer ones, so a caller that alternates between one identifier and several still produces two entries rather than one. And the three statements differing only in their select list did not merge on any version, because that is a different shape by any definition. Folding lists removes the largest source of accidental cardinality and does not remove the category; the rest of what to expect from the identifier is in what a query fingerprint actually covers.

The practical order of operations is therefore: find out whether entries are being evicted at all, and only then decide whether to raise the limit, because raising it needs a restart and more shared memory while parameterising the caller needs neither. Why the view does not show everything covers the other three reasons an entry can be missing, which are more likely than eviction on a conventional workload and less likely on this one.

Nobody is going to file a ticket

The detector for a slow query in most organisations is a person. Somebody clicks something, waits longer than they expected, and says so. Every monitoring threshold anybody has ever set was calibrated, somewhere back along the chain, against that.

Remove the person and the signal does not degrade, it disappears. An agent waiting fifteen seconds for a query behaves exactly as it does waiting fifty milliseconds, except slower. It will not retry differently, escalate, or mention it in the summary it writes for you afterwards. There is no threshold to calibrate against because there is no complaint to calibrate to.

What replaces it is a budget declared in advance, which is what the role settings above are. A statement limit is not a safety net here, it is the specification: a limit of fifteen seconds is a statement that no question this agent asks is worth more than fifteen seconds, and every breach is a finding rather than an incident. Set it low enough that it fires occasionally. A limit that has never fired is a limit nobody has tested.

The second half of the replacement is watching what is running rather than what has finished, because an agent’s worst statement may never finish at all. pg_stat_statements records a statement when it completes, which means a query that has been running since before you looked is invisible there and perfectly visible in the activity view. Sampling that view on a schedule is what turns it into a history, and that is active session history, which is the same technique applied to the same gap for an entirely different reason.

A thousand cheap queries outrank nothing

Here is the ranking failure that this workload produces and a deployed application almost never does.

Every list of expensive statements is sorted by total time, because total time is what the server spent and what a human is waiting on. A statement taking three milliseconds and running a thousand times accumulates three seconds, which places it nowhere on any such list. The same statement, if it reads a substantial fraction of a table from cache each time, may be the largest consumer of memory bandwidth on the machine while remaining invisible to every dashboard that ranks by time.

Buffer accesses are the honest denominator, and the arithmetic is one multiplication. A page is block_size bytes, eight kilobytes on every ordinary build, and the statistics view counts pages hit and pages read per statement across all its calls. So:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       shared_blks_hit + shared_blks_read AS blocks,
       pg_size_pretty((shared_blks_hit + shared_blks_read)
                      * current_setting('block_size')::bigint) AS buffer_traffic,
       left(query, 44) AS query
FROM pg_stat_statements
ORDER BY (shared_blks_hit + shared_blks_read) DESC
LIMIT 8;

A statement touching fifty thousand buffers per call moves four hundred megabytes through shared buffers each time it runs. Called once an hour that is nothing. Called a thousand times in an afternoon it is four hundred gigabytes of memory traffic and a cache that now holds this agent’s working set instead of the application’s, and the time column will have reported a few seconds throughout, because reading from cache is fast.

The reason this is an agent problem specifically is that PostgreSQL has no result cache. Asking the same question twice does the work twice. A human analyst notices they already ran that query; a loop does not, and neither does a model that has forgotten the earlier turn. Ordering by blocks rather than by time is a two-word change to a query somebody already has, and it is the only ranking on which this failure appears at all.

Connections that live for one tool call

An agent invoked as a short-lived process, once per tool call, connects and disconnects at a rate no deployed application would. Each connection is a fork, an authentication round trip and a fresh empty plan cache, and none of that is charged to any statement.

The per-database counters have measured it since PostgreSQL 14, and two derived numbers are worth more than the raw ones:

SELECT datname,
       sessions,
       round((session_time / nullif(sessions, 0))::numeric, 1) AS mean_session_ms,
       round((100.0 * active_time / nullif(session_time, 0))::numeric, 1) AS pct_executing,
       sessions_abandoned, sessions_fatal, sessions_killed
FROM pg_stat_database
WHERE datname = current_database();

Mean session duration in the tens of milliseconds says every connection is being thrown away, and the fraction of connected time actually spent executing says how much of the cost was overhead. A long-lived pooled application sits at a high fraction with long sessions. A per-tool-call agent sits at a low fraction with short ones, and the difference between those two shapes is the thing to alert on rather than the connection count, which looks the same in both cases if you sample it at the wrong instant. The columns and what each one counts are covered in the session statistics added in 14; on 18 the connection stages can also be timed individually in the log, which is how long each phase of a connection took rather than how long the whole thing took.

Putting a pooler in front is the obvious fix and it brings one consequence worth knowing before you do it. In transaction pooling, a backend is handed to whichever client needs it next, so the per-role settings above still apply, because they are attached at connection time to the pooler’s connection, while anything the agent sets during a session does not survive past its transaction. It also means a backend in the activity view is not reliably the agent’s for longer than one statement, which is the attribution problem connection pooling and PgBouncer exists to cover.

What none of this tells you

Three limits, stated because the alternative is implying they are not there.

The statistics views record what was executed and never why. An agent’s statement carries no link to the question that produced it, no conversation identifier and no turn number, and nothing in the server will give you one. The best available approximation is the application name, which you can set per connection to carry a session identifier of your own, and that is a convention you are maintaining rather than a property of the database.

Plan choice is per session and per prepared statement, so an agent that reconnects constantly plans everything fresh and an agent that holds a connection eventually gets a generic plan for a parameterised statement, which is a regression with no deployment behind it. Which of those two is happening to you is a function of how the agent was built, and neither is visible from the database without asking.

And the honest one. Everything above is derived from what PostgreSQL measures and from how these mechanisms are documented to behave, and the receipts on this page were produced on 14, 17 and 18. What is not here is any claim about how agent workloads behave in aggregate, because that would require a population of them observed over time, and we do not have one. Treat the shapes described here as the ones the server is equipped to show you, and measure your own.