Reading a backend's memory from SQL

The allocator was always there; the window was not

PostgreSQL allocates almost nothing with a bare malloc. Memory belongs to a context, contexts nest inside one another, and a context is freed as a whole when the work it belongs to finishes. A query’s execution state lives in one, a plan in another, the catalog cache in a long-lived one that outlives both. That design is why a backend can abort a statement without leaking: the context goes and everything in it goes with it.

It is also why “this connection is using two gigabytes” is a question with a real answer rather than a shrug. The server knows exactly which context holds the memory. Until PostgreSQL 14 it would only say so to a debugger attached to the process, or to a core dump.

Two things arrived together in that release. A view showing the contexts of the session reading it, and a function that asks another backend to write its contexts to the server log. The System Views section of the release notes lists them as consecutive entries, and the second is the one that makes the first operationally useful, because the session using all the memory is rarely the session you are sitting in.

What a backend is holding, and the name collision underneath it

SELECT name, parent, level,
       pg_size_pretty(total_bytes) AS total,
       pg_size_pretty(used_bytes)  AS used
FROM pg_backend_memory_contexts
WHERE total_bytes > 100000
ORDER BY total_bytes DESC;

        name        |      parent      | level |  total  |  used  
--------------------+------------------+-------+---------+--------
 CacheMemoryContext | TopMemoryContext |     1 | 1024 kB | 580 kB
 MessageContext     | TopMemoryContext |     1 | 128 kB  | 43 kB
 Timezones          | TopMemoryContext |     1 | 102 kB  | 99 kB
(3 rows)

An idle session holds very little, and what it does hold is mostly the catalog cache, which is the price of having looked anything up. The gap between total and used is the allocator’s own slack: blocks it has taken from the operating system and not yet handed out. A context whose used figure is far below its total has been large in the past and shrunk, which is a different situation from one that is large now.

The obvious next step is to build the tree, and this is where the view’s shape on 14 lets you down. The parent is given as a name, and names are not unique.

SELECT name, count(*) AS contexts, pg_size_pretty(sum(total_bytes)) AS total
FROM pg_backend_memory_contexts
GROUP BY name
HAVING count(*) > 1
ORDER BY count(*) DESC;

    name     | contexts | total  
-------------+----------+--------
 index info  |       82 | 148 kB
 ExprContext |        6 | 48 kB
(2 rows)

Dozens of contexts share one name, all of them children of the catalog cache, one per index the session has touched. Told that a context’s parent is called CacheMemoryContext you know which one is meant, because there is only one of those. Told that a context’s parent is called index info you know nothing, and any query that tries to sum a subtree by walking the name column is summing the wrong thing.

The working technique on 14 is to aggregate by name and level rather than to reconstruct ancestry. It answers the question you usually have, which is which kind of thing is large, and it does not pretend to a precision the view cannot supply.

What the same query does on 18

PostgreSQL 18 reshaped the view around exactly this problem, and in doing so removed the column.

SELECT name, parent, level,
       pg_size_pretty(total_bytes) AS total,
       pg_size_pretty(used_bytes)  AS used
FROM pg_backend_memory_contexts
WHERE total_bytes > 100000
ORDER BY total_bytes DESC;

ERROR:  column "parent" does not exist
LINE 1: SELECT name, parent, level,
                     ^
HINT:  Perhaps you meant to reference the column "pg_backend_memory_contexts.ident".

The replacement is a path: an array of identifiers from the root down to the context, which is unambiguous in the way a name is not, plus a column naming the allocator type.

SELECT name, type, level, path,
       pg_size_pretty(total_bytes) AS total
FROM pg_backend_memory_contexts
WHERE total_bytes > 100000
ORDER BY total_bytes DESC;

        name        |   type   | level |  path  | total  
--------------------+----------+-------+--------+--------
 CacheMemoryContext | AllocSet |     2 | {1,19} | 512 kB
 MessageContext     | AllocSet |     2 | {1,8}  | 128 kB
 Timezones          | AllocSet |     2 | {1,25} | 102 kB
(3 rows)

Look at the level column in both outputs, because this is the part that breaks quietly rather than loudly. On 14 the root context is at level zero. On 18 it is at level one, and everything below it has shifted by one with it. A query filtering on level = 1 to get the top-level contexts returns the root’s children on 14 and the root itself on 18, and it errors on neither.

So this page carries two failure modes at once, which is unusual and worth stating plainly. Anything naming the parent column stops working with a message. Anything keyed on the level number keeps working and returns a different set of rows.

Getting it out of a session that is not yours

The view only ever shows the calling backend. For any other session, the function is the only route, and what it produces does not come back to you.

SELECT pg_log_backend_memory_contexts(pg_backend_pid());

 pg_log_backend_memory_contexts 
--------------------------------
 t
(1 row)

The boolean means the signal was delivered, not that anything was written. The contexts themselves are written to the server log by the target backend, one line per context, ending in a grand total, and on a busy cluster that is a large number of lines arriving at whatever destination your logging is configured for. Two consequences follow and neither is obvious from the function’s return value.

The first is that you need log access to use this at all, which on a managed service may mean the feature is effectively unavailable to you even though the function exists. The second is that the target backend does the writing, in the middle of whatever it was doing, so a session being investigated for using too much memory is also briefly a session doing a lot of logging.

Permission is the other thing to check before writing a runbook around it, and the check on 14 is not the one the syntax suggests. Granting execute on the function is accepted and does not help.

CREATE ROLE oncall LOGIN;
GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) TO oncall;

CREATE ROLE
GRANT

SET ROLE oncall;
SELECT pg_log_backend_memory_contexts(pg_backend_pid());

ERROR:  must be a superuser to log memory contexts

The same grant, on 18:

SET ROLE oncall;
SELECT pg_log_backend_memory_contexts(pg_backend_pid());

SET
 pg_log_backend_memory_contexts 
--------------------------------
 t
(1 row)

On 14 the superuser test lives inside the function body, so the grant is recorded and then ignored. From 15 the test is the ordinary one on the function, which is what makes a non-superuser role able to hold this. If your fleet spans both, the runbook needs two versions of its first step, and on the 14 half of it the first step is somebody with superuser.

Reading the three size columns without misleading yourself

The columns look like they should add up and they do not, in a way that matters when you are deciding whether a context is the problem.

The total is what the context has taken from the operating system. It is allocated in blocks, and blocks are handed out in a doubling sequence, so a context that needed slightly more than a block has taken twice what it needed and the excess is real memory the process is holding. The used figure is what has actually been given out to callers inside that block. The free figure is the difference, and the chunk count is how many separately freed pieces are sitting in the free lists waiting to be reused.

Two readings follow. A context whose total is large and whose used figure is small has been large and shrunk, and it will not give the memory back: contexts release blocks to the operating system only when the context is destroyed, so a session that ran one enormous sort keeps the footprint until whatever owns that context finishes. That is why a connection pool holding sessions open indefinitely accumulates a high-water mark rather than an average.

A context whose free chunk count is very high relative to its size is fragmenting, which usually means a workload allocating and freeing many small pieces of different sizes. It is rarely worth acting on directly and it is worth recognising, because it looks like a leak on a graph of resident memory and is not one.

What none of the three tells you is whether the memory is a problem. That is a question about the machine, and this view is how you find out which part of the process to blame once the machine has already told you there is one.

Worth a runbook entry rather than an alert

This is a diagnostic, not a metric. Nothing here belongs on a dashboard and nothing here belongs on a rule, because the view reports one session’s memory and a cluster has hundreds.

The alert that matters is on the process, not the context: resident memory per backend, from the operating system, which is what actually runs a machine out of memory. When one crosses a threshold, this view and this function are how you find out why, and the sequence is worth writing down in advance because it needs a process identifier and log access that nobody assembles calmly during an incident.

The usual culprits are worth knowing so the output can be read quickly. A very large execution context is usually a hash join or a sort with a generous work memory setting multiplied by parallel workers. A very large catalog cache is usually a long-lived connection in a pool that has touched a great many tables, which is a connection lifecycle problem rather than a query problem. A large context named after an extension is the extension’s own business, and the answer is usually in its documentation rather than in this view.

What it costs to look

Reading the view walks the current backend’s own context tree, which is a pointer chase over structures already in its memory. It is cheap, and it is charged to the session doing the asking.

The function is cheap for the caller and not free for the target, which is the asymmetry to keep in mind. The target does not stop what it is doing on request either: the signal is handled at the next point the backend checks for interrupts, so a session blocked on a lock may take a while to answer, and a session in a tight loop inside an extension may not answer at all. Call it once, read the log, and treat silence as information rather than as failure.