getnobody.org / labs

Postgres 19 foreign key fast path

Everything behind the post How Postgres 19 checks foreign keys without running SQL.

Files

• run.sh: starts one server in Docker, applies the settings, runs 6 inserts of 1M rows with and without a foreign key, prints CSV
• run-seq.sh: the same with sequential foreign key values
• run-part.sh: the same with a partitioned parent (use with BENCH_SQL=bench-part.sql)
• bench.sql and bench-part.sql: schema
• Dockerfile.rel19 and Dockerfile.master: build Postgres from a source checkout inside the container

Raw results (ms per 1M-row insert)

• Scattered keys: 18.6, 19beta3, REL_19_STABLE, master
• Sequential keys: 18.6, REL_19_STABLE, master
• Partitioned parent: 18.6, REL_19_STABLE

Run it

git clone --depth 1 --branch REL_19_STABLE https://github.com/postgres/postgres.git src-REL_19_STABLE
docker build -f Dockerfile.rel19 -t lab/pg:rel19 .
./run.sh pg18.6 postgres:18.6
./run.sh rel19 lab/pg:rel19 bash -c '[ -f /data/PG_VERSION ] || { initdb -D /data >/dev/null && echo "host all all all trust" >> /data/pg_hba.conf; }; exec postgres -D /data'

Tested with Docker on an Apple M-series laptop, 4 CPUs and 4 GB per server, one server at a time. Commits: REL_19_STABLE 8fbbb338be, master 4545cee.