How PostgreSQL tells every connection its cache is stale

What happened

I once worked on a cache that sat in front of a database. It answered quickly and sometimes wrongly.

I redesigned it as three isolated segments with version-based invalidation. Each entry remembers the version of the data it came from, and once the data has a newer version, the old entry stops counting. The hit rate went from 84.3% to 98.1%, and the database answered about 131 million fewer queries a week.

Looking back, storing things was the easy part of that cache. Most of the effort went into deciding when to throw an entry away.

Later, when I started reading PostgreSQL’s source, I found the same problem inside every connection.

Each PostgreSQL connection keeps its own copy of the catalog. A change is announced at commit through one shared ring of messages, and each connection reads the ring before it trusts its copy.

Why every connection keeps its own cache

Think of a library where every reader keeps a notebook of the facts they look up often, so they don’t have to walk back to the shelves each time. PostgreSQL connections work the same way.

Each connection gets its own server process, a backend, with private memory. What the database knows about itself is in the system catalog, a set of ordinary tables. pg_class has a row per table, index or view, and pg_attribute has a row per column.

To run even SELECT * FROM orders, a backend has to know what orders is: its columns, their types, its indexes. It could read all of that from the catalog tables for every query, but that would be far too slow.

Instead, each backend keeps three caches of its own:

• the catalog cache (catcache) holds single catalog rows looked up by key, such as “the pg_class row of table 16384”. Most code gets to it through a layer called the syscache.
• the relation cache (relcache) holds one assembled description per table or index, with everything needed to open it.
• the plan cache holds the saved plans of prepared statements, which depend on the tables they read.

None of this is shared. When one connection changes the catalog, every other connection’s caches are wrong, and nothing has told them yet.

Collecting changes until commit

When a backend inserts, updates or deletes a catalog row, it doesn’t flush anything straight away. Under PostgreSQL’s rules, the old row stays valid until the current command ends.

What the backend does instead is record an invalidation message for each cache that could hold the row. It keeps that list until the change is final.

An update counts as a delete plus an insert, and the two fold into one message when the cache’s key didn’t change. A change to a row of pg_class, pg_attribute or pg_index, or to a foreign key’s row in pg_constraint, also records a relcache message for the table that row describes. A table gets at most one of those per command.

What happens to the list depends on how things end:

• A command ends. The backend applies that command’s messages to its own caches and nobody else’s (CommandEndInvalidationMessages). That’s why your next statement already sees the column you added. Other backends can’t see your uncommitted rows, so there’s nothing to tell them.
• The transaction commits. The commit is recorded first, then every message goes into the shared queue (AtEOXact_Inval), and only after that are the locks released.
• The transaction aborts. Nothing is sent, and the backend undoes its own cache changes. An aborted subtransaction does the same for its part, and a committed one hands its list to its parent.

The order at commit protects both sides. If the messages went out before the commit was recorded, another backend could reload the catalog and still find the old rows. If they went out after the locks were released, a backend waiting for the table could get them only after it had used its stale entry.

The commit’s WAL record carries the same messages, so a standby replays them for its own connections.

The ring of messages

The messages go into the shared invalidation queue, sinval for short, in shared memory. There’s one for the whole server, covering every database. Each message carries a database id, and a backend skips messages for other databases.

The code describes it as an infinite list of numbered messages. In memory it’s a ring of 4,096 slots (MAXNUMMESSAGES), and message n sits in slot n modulo 4,096. A message is 16 bytes, so the whole ring is about 64 KB.

Three numbers keep track of it:

• maxMsgNum, the write position, where the next message goes;
• nextMsgNum, one per backend, the next message that backend hasn’t read;
• minMsgNum, the oldest message anyone still needs.

A new connection starts with its read position at the write position. Its caches are empty, so it has nothing to catch up on.

The main kinds of message are:

• catcache: rows with this hash may be stale in this cache;
• catalog: forget everything cached from this whole catalog;
• relcache: this table’s description is stale, or every table’s when the id is 0;
• smgr: close your file handles for this table; this kind is never skipped by database;
• relmap and snapshot: smaller kinds, for the map of a few catalogs’ files and for the snapshot used to read the catalog.

A catcache message doesn’t carry the key, only a hash of it, because a table name wouldn’t fit in 16 bytes. The receiver removes every entry in that cache with the same hash. Once in a while that catches an unrelated row whose key hashes the same, a false positive that costs one reload.

Writers add messages under a lock, at most 64 at a time (SIInsertDataEntries), so even a commit with thousands of messages never holds the lock for long. After each chunk, the writer sets a “you have messages” flag on every backend.

Reading the ring before trusting the cache

A backend has to read the ring before it trusts its caches.

Reading the ring is one function, AcceptInvalidationMessages() (inval.c:929). It calls ReceiveSharedInvalidMessages, which pulls messages 32 at a time from SIGetDataEntries and applies each one to the local caches.

A backend reads the ring at two moments:

• when a transaction starts, so every transaction begins with checked caches;
• when it locks a table it did not already hold in that mode, in LockRelationOid and its siblings in lmgr.c.

ALTER TABLE ... ADD COLUMN shows why the second one matters. It takes an ACCESS EXCLUSIVE lock, and the committing backend writes its messages before it lets go of that lock.

Whoever gets a lock on the table next reads the ring right after, and finds the messages already there. Once a backend holds the lock, it can trust its cache for that table.

If the backend already held that kind of lock, it skips the read. Nobody could have changed the table in a conflicting way while it held the lock.

Usually there’s nothing new, and the read costs almost nothing. SIGetDataEntries first looks at the backend’s own “has messages” flag without taking a lock, and returns at once when the flag isn’t set.

When there are messages, each one is applied in turn:

• A catcache message removes the matching rows, plus every cached list in that cache. A row in use at that moment is marked dead, and removed once it’s released.
• A relcache message drops the table’s description if nobody has it open, and rebuilds it if someone does.
• Each message also runs the callbacks registered for it. The plan cache registers some, so a relcache message marks every saved plan that reads the table as invalid. The plan is built again the next time it’s used.

Within one transaction’s messages, the catcache ones come first. That way a table’s description is never rebuilt from catalog rows that are about to be thrown away.

When the ring fills up

A writer never waits for a slow reader, or one idle connection could hold up every DDL on the server. Instead, PostgreSQL cleans up the ring and nudges the reader furthest behind. A reader that falls too far behind gets reset.

Cleanup (SICleanupQueue) runs when the ring holds 2,048 messages, again every 256 after that, and whenever a write would not fit. It does three things:

1. It finds the oldest message anyone still needs, which frees the slots before it.
2. It marks for reset any backend so far behind that the writer is about to overwrite a message it hasn’t read.
3. It signals at most one backend: of those more than 2,048 messages behind and not yet signalled, the one furthest behind. The signal asks it to catch up.

flowchart TD
  W["A commit writes messages"] --> Q{"Past the cleanup threshold<br/>(2,048, then every 256),<br/>or no room left?"}
  Q -->|no| G["Write them and go on"]
  Q -->|yes| C["Clean up the ring"]
  C --> L{"Is a reader about to lose<br/>a message it has not read?"}
  L -->|yes| R["Mark it for reset: at its next<br/>read it drops its whole cache"]
  R --> S{"Any reader over 2,048 behind<br/>and not yet signalled?"}
  L -->|no| S
  S -->|no| G
  S -->|yes| N["Signal the one furthest behind"]
  N --> G
  N -.-> K["The reader catches up<br/>while waiting for its client"]
  K -.-> P["Then it runs cleanup itself"]
  P -.->|signal passes on| S

The signalled backend deals with it while it waits for its client. If it’s inside an open transaction, it calls AcceptInvalidationMessages(). If not, it starts and commits an empty transaction, which reads the ring on the way in.

Once it has caught up, it runs cleanup itself, and that cleanup signals the next backend furthest behind. The source calls this a daisy chain. Usually only one signal is in flight at a time, so idle connections wake one after another and not all at once.

A reader that stays stuck after its signal is passed over. Later cleanups signal the next reader that hasn’t had one.

The last resort is the reset. A backend whose unread message has been overwritten can’t know what that message said. The only safe thing is to throw away its whole cache and reload entries from the catalog as they’re needed.

At its next read, a backend marked for reset gets no messages. It jumps to the write position and resets instead, through InvalidateSystemCachesExtended:

• every catalog cache is emptied;
• every table description nobody has open is dropped, and the open ones are rebuilt;
• every catcache callback runs with hash 0, and every relcache callback with InvalidOid, both meaning “everything”.

A reset is always correct, but for a while it’s slow. Every lookup in that connection goes back to the catalog tables until the caches fill up again.

Keeping your own cache honest

An extension may keep a cache of its own, with things like options worked out for a table, a parsed setting or a connection to another server. PostgreSQL doesn’t know about that cache, so it can’t flush it, but you can register a function for it to call when it applies a message.

• CacheRegisterSyscacheCallback(cacheid, func, arg) (inval.c:1815) runs func for each catcache message for that cache, with the hash.
• CacheRegisterRelcacheCallback(func, arg) (inval.c:1857) runs func for each relcache message, with the table’s OID.

The rules I follow:

1. Register once per backend. There’s no function to unregister. The slots are fixed: 64 catcache callbacks and 10 relcache callbacks per backend, shared with PostgreSQL’s own caches and every other extension that’s loaded. Running out is FATAL, and the connection ends.
2. Treat 0 and InvalidOid as “drop everything”. That’s what a reset sends.
3. In the callback, only mark entries stale. Don’t read the catalogs there; rebuild on the next lookup. The callback runs whenever the backend reads the ring, and that can be in the middle of your own catalog read.
4. A catcache callback gets a hash, not a key. Store each key’s hash when you build an entry, or flush everything for that cache.
5. Watch for entries born stale. Building an entry reads the catalogs, reading takes locks, and a lock reads the ring. That can run your callback for the very entry you’re building. PostgreSQL’s relcache builds again when that happens; most other caches rely on DDL taking an ACCESS EXCLUSIVE lock.

The sketch below shows the pattern. The my_ names are made up, and I haven’t compiled this exact code.

typedef struct MyEntry
{
    Oid     relid;          /* hash key: the table */
    bool    valid;
    int     setting;        /* worked out from the catalogs */
} MyEntry;

static HTAB *my_cache = NULL;
static uint64 my_inval_count = 0;

static void
my_relcache_callback(Datum arg, Oid relid)
{
    HASH_SEQ_STATUS status;
    MyEntry    *entry;

    my_inval_count++;
    if (relid == InvalidOid)    /* a reset: anything may be stale */
    {
        hash_seq_init(&status, my_cache);
        while ((entry = hash_seq_search(&status)) != NULL)
            entry->valid = false;
    }
    else if ((entry = hash_search(my_cache, &relid, HASH_FIND, NULL)) != NULL)
        entry->valid = false;   /* only mark it: no catalog reads here */
}

static MyEntry *
my_lookup(Oid relid)
{
    MyEntry    *entry;
    bool        found;

    if (my_cache == NULL)
    {
        HASHCTL ctl = {.keysize = sizeof(Oid), .entrysize = sizeof(MyEntry)};

        my_cache = hash_create("my cache", 64, &ctl, HASH_ELEM | HASH_BLOBS);
        CacheRegisterRelcacheCallback(my_relcache_callback, (Datum) 0);
    }

    entry = hash_search(my_cache, &relid, HASH_ENTER, &found);
    if (!found)
        entry->valid = false;

    while (!entry->valid)
    {
        uint64  before = my_inval_count;

        entry->setting = my_compute_setting(relid);  /* reads the catalogs */
        entry->valid = (my_inval_count == before);
    }
    return entry;
}

The counter guards against an entry born stale: if any callback ran during the build, the loop builds it again. If the build fails with an error, the entry stays invalid and the next lookup tries again.

postgres_fdw is a real example. Its connection cache registers catcache callbacks for pg_foreign_server and pg_user_mapping once, when it creates its hash table. Each connection it opens remembers the hash of its server’s key and of its user mapping’s key.

Its callback (connection.c:1295) treats hash 0 as “all”, and otherwise compares hashes.

A connection not used in the current transaction is closed at once. A connection in use is only marked, and closed when the transaction ends, because you can’t pull a connection out from under a running query.

Another cache in the same extension, the one that remembers which objects are safe to send to the remote server, does the simple thing. It empties itself whenever a foreign server’s row changes (shippable.c:64).

A worked example: one ALTER TABLE

Take two connections to the same database. Session B has been using orders:

PREPARE get_order(int) AS
  SELECT id, total FROM orders WHERE id = $1;
EXECUTE get_order(7);

B now holds the pg_class row of orders, a relcache entry for it, and a saved plan for get_order.

Session A runs one statement:

ALTER TABLE orders ADD COLUMN discount integer;

Inside A:

1. A takes an ACCESS EXCLUSIVE lock on orders. If B were inside a transaction that had read orders, A would wait here until it ended.
2. A inserts a pg_attribute row for discount. pg_attribute has two caches, by column name and by column number, so that’s two catcache messages. The row belongs to orders, so there’s one relcache message too.
3. A updates the pg_class row of orders, whose column count goes up by one. pg_class has two caches, by OID and by name. Neither key changed, so that’s one message each, and orders already has its relcache message.
4. A ends an internal command and applies the messages to its own caches. The rest of the statement sees the new column.
5. A commits. The commit is recorded, the messages go into the ring, and the lock is released.

By my reading of the source, five is the floor. A default, a constraint or an index touches rows in more catalogs, and each of those rows brings its own messages. I’d rather give you the kinds than a total I can’t defend.

Then B runs EXECUTE get_order(7);:

1. B starts a transaction and reads the ring, which now holds A’s messages.
2. The pg_class messages remove B’s cached row of orders. The pg_attribute messages name the new column, which B never cached. B’s rows for the old columns didn’t change, so they stay.
3. The relcache message drops B’s description of orders, which nobody has open between transactions. The plan cache’s callback marks get_order invalid.
4. EXECUTE finds the plan invalid. It locks orders, reads the ring again and finds nothing new, reloads orders from the catalog, and plans again.
5. B gets the same two columns, and it never runs the old plan against the changed table.

The same steps as a sequence diagram. In it, B reads the ring when it takes its lock; in a new transaction it has already read it once at the start.

sequenceDiagram
  participant A as Session A
  participant R as Shared ring
  participant B as Session B
  participant C as Catalog
  A->>C: ALTER TABLE orders ADD COLUMN discount
  Note over A: holds ACCESS EXCLUSIVE on orders
  A->>C: commit recorded
  A->>R: invalidation messages written
  Note over A: locks released
  B->>B: next query on orders
  B->>B: lock on orders
  B->>R: AcceptInvalidationMessages()
  R-->>B: the messages from A
  B->>B: stale entries for orders dropped
  B->>C: reload orders
  C-->>B: orders, now with discount

Gotchas

1. A catcache message carries a hash, not a key. Unrelated rows with the same hash are flushed too, which costs a reload and nothing else. Your own catcache callback gets the hash and no OID, so compare stored hashes or flush everything.

2. Any message to a cache drops every list in it. Some lookups ask a cache for all rows that match part of a key, and the answer is cached as a list; finding functions by name works this way. A list can’t be checked against one hash, so it goes. A burst of CREATE OR REPLACE FUNCTION empties those lists in every connection.

3. An idle connection lags. It reads the ring at its next transaction or new lock, or when it’s the one signalled. It acts on a signal only while it waits for its client, so a connection busy with one long query reads the ring only when it takes a new lock.

4. One huge DDL can reset many connections at once. Dropping a schema full of tables changes thousands of catalog rows, and the commit sends all their messages together. Connections that haven’t read along by the time the writer comes round can be reset, and then they all reload their caches at about the same time.

5. debug_discard_caches works only in assert builds. It throws caches away at every chance, to catch code that holds on to an entry too long. At 1, the regression tests run about 100 times slower. It works in builds with assertions, or with DISCARD_CACHES_ENABLED defined; anywhere else it stays 0, and setting it to anything else fails.

6. A prepared SELECT * fails after ADD COLUMN, on purpose. The new plan would return one more column, and PostgreSQL won’t change a prepared statement’s result shape. Name the columns, or prepare the statement again.

   ERROR:  cached plan must not change result type

The whole loop in one picture

session A, the writer
  change a catalog row
    -> one message per cache that could hold it
  end of each command
    -> apply them to A's own caches
  COMMIT recorded
    -> all messages into the ring
    -> locks released

the ring: one per server, 4,096 slots
  one write position
  one read position per backend

session B, a reader
  transaction start, or a new lock
    -> read the ring
    -> drop stale rows, descriptions, plans
  next use
    -> reload from the catalog tables

ring too full
  -> signal the furthest-behind reader not yet signalled
  -> a full ring behind: reset, reload all

Why this matters

If you write an extension, PostgreSQL can’t see any cache you keep, so follow the five rules in Keeping your own cache honest.

If you run many connections, each one keeps its own caches and pays for its own reloads. After a wave of resets, every one of them reads the same catalog rows again.

Huge catalog changes go down better at quiet times, or split into smaller transactions, so connections get a chance to catch up between commits. A connection pooler helps as well, since fewer backends means fewer caches to keep up to date.

Dig deeper

The long comment that opens inval.c is the best explanation of all this that I know. sinvaladt.c opens with the design of the ring. Both links, and every trail below, are at PostgreSQL 18.3.