How Postgres 19 checks foreign keys without running SQL

Originally published at getnobody.org.

Every time you insert a row into a table with a foreign key, Postgres has to prove the referenced row exists and stop anyone from deleting it until your transaction ends. Up to Postgres 18, it did that by running a tiny SQL query for every single row. Postgres 19 stops doing that. I read the new code, then measured it.

The short version: on a 1 million row bulk insert, the foreign key check costs about 44% less time in Postgres 19. A second optimization that was pulled from 19 before release would have helped much more, but only when the foreign key values arrive in order.

The old path: a query per row

A foreign key is enforced by an internal AFTER INSERT trigger, RI_FKey_check in ri_triggers.c. In Postgres 18 that trigger builds this query once, saves the plan, and runs it through SPI (the server's internal SQL interface) for every inserted row:

SELECT 1 FROM ONLY parent x WHERE id = $1 FOR KEY SHARE OF x

FOR KEY SHARE is the important part. It locks the parent row so nobody can delete it or change its key while your transaction is open, but it still lets other sessions update the parent's non-key columns.

The plan is cached, but every row still pays for SPI setup, executor startup and teardown, snapshot handling and the permission checks, just to do one index lookup.

(Chart: see the original post.)

The new path: probe the index directly

Amit Langote's commit 2da86c1ef9 adds a fast path. Before touching SPI, RI_FKey_check now asks two questions:

if (ri_fastpath_is_applicable(riinfo) &&
    ri_FastPathCheck(riinfo, fk_rel, newslot))
    return PointerGetDatum(NULL);

ri_fastpath_is_applicable says no for two cases: when the referenced table is partitioned (the probe would need to be routed to the right partition), and for temporal foreign keys (range containment needs more than a single lookup). Later fixes added more fallbacks: the referenced index has to be a btree, and its collation must match the column's.

If the answer is yes, ri_FastPathCheck does what the query used to do, by hand:

1. Opens the parent table and its unique index.
2. Takes a snapshot. In the shipped version this happens after the table lock is granted. Taking it earlier opened a window where a parent row committed while you waited for the lock would be invisible, and the check would fail for a key that exists (commit 1390182683).
3. Builds scan keys from the new row's values and probes the btree with index_getnext_slot.
4. Locks the row it found with table_tuple_lock(..., LockTupleKeyShare, ...) in ri_LockPKTuple. That's the same lock FOR KEY SHARE takes.
5. If the lock had to follow an update chain to reach the newest row version, recheck_matched_pk_tuple checks that the key still matches. That is the fast path's version of the recheck the executor does for SELECT ... FOR UPDATE.

It also switches to the parent table owner's user id for the lookup, the same way the SPI path does, so permissions and row-level security behave the same.

The benchmark

A parent table with 1 million integer primary keys, and a child table with a foreign key to it. Each run inserts 1 million child rows in one INSERT ... SELECT, after a TRUNCATE and a CHECKPOINT. The same insert into a table with no foreign key is the baseline. Six runs per version; the charts show the median.

(Chart: see the original post.)

With scattered keys, the foreign key check itself (the time above the no-foreign-key baseline) drops from 2.17 s in 18.6 to 1.21 s in 19. The whole insert is 1.66x faster.

The control: turn the fast path off

To check the gain really comes from the fast path, I ran the same test with a partitioned parent table. The fast path refuses partitioned parents, so 19 falls back to SPI:

(Chart: see the original post.)

Two things show up here. The gain disappears when the fast path is off, so the fast path is what made the difference. And a foreign key to a partitioned table is expensive either way: about 3.5x slower than the SPI path on a plain table, because every check has to find the right partition first.

The batching that was pulled

Three days after the fast path, a second commit went further. Instead of probing the index once per row, it buffered up to 64 rows and checked them together. For single-column keys it passed all 64 values as one array scan key (SK_SEARCHARRAY), so the btree sorts them and walks its leaf pages once instead of descending from the root 64 times. The commit reported about 2.9x in total over the old code.

That batching is in the 19 betas but not in 19.0. On September 10 it was removed from REL_19_STABLE. Buffered checks have to survive across trigger calls and be flushed at the right moment, and that state has to be right under nested triggers, subtransactions, deferred constraints and SET CONSTRAINTS. Miss one case and a buffered check never runs: the transaction commits a real foreign key violation without an error. The commit message says it plainly: with 19 close to release, there wasn't time to be sure every case was covered. Batching stays on master for a later release. That's a hard call to make that close to release, and it's the right one.

My first measurement disagreed with the 2.9x. With scattered keys, master (batching included) is only about 2% faster than 19. The array scan only saves work when the 64 values land on the same few leaf pages. My keys were deliberately spread over the whole index, so every value still needed its own leaf.

So I reran with the keys in order (parent_id = 1, 2, 3, ...), which is what you get when child rows are loaded in parent order. Now batching does what the commit said: 701 ms against 1,021 ms for 19, and 2.8x against 18.6, close to the reported 2.9x.

What this means if you run Postgres

• Bulk loads into tables with foreign keys get faster in 19 for free, as long as the referenced table isn't partitioned and the key has a btree index (primary keys and unique constraints do).
• Foreign keys to partitioned tables don't get this. If you bulk-load into a table that references a partitioned table, the check still costs about 7 µs per row.
• Load order matters more after batching lands. Once batching ships in a later release, loading child rows sorted by parent key could make the check nearly twice as cheap again.
• Only the insert-side check changed. ON DELETE CASCADE, SET NULL and the other action triggers still go through SPI, as the original commit explains.

How I measured

• Machine: Apple M-series laptop. Each server ran in Docker (a Linux arm64 VM) with 4 CPUs and 4 GB of memory, one server at a time.
• Settings: shared_buffers = 1GB, max_wal_size = 8GB and checkpoint_timeout = 30min on every version. Everything else was left at the defaults.
• Versions:
  • 18.6 and 19beta3 are the official Docker images.
  • "19" is REL_19_STABLE at 8fbbb338be, which already has batching removed, built from source with -O2.
  • "master" is master at 4545cee, built the same way.
  • The 19beta3 image still includes batching and measured 1,529 ms on scattered keys, close to both 19 and master.
• Caveats:
  • The source builds use a different compiler setup from the packaged images. That probably explains part of the small difference in the no-foreign-key baseline (332 ms against 299 ms).
  • These are single-session numbers. Concurrent inserts and lock contention are a separate test.

All scripts, Dockerfiles and raw results are in the lab folder. Run ./run.sh <label> <image> and you get the same table.

Credit goes to Amit Langote, who wrote the fast path, the batching and roughly thirty follow-up commits to this code this year, and to everyone on pgsql-hackers who tested the betas hard enough to find the problems before release.