Investigate a pre-initdb'd container image to cut per-job PostgreSQL cluster-startup cost

[jnasbyupgrade]

Background

Pulled real step-level timing from actual pg-upgrade-test job runs in Postgres-Extensions/pg_count_nulls while investigating whether folding that job into the main test matrix was worthwhile (gh api .../actions/runs/<id>/jobs, real per-step timestamps, not estimates):

12 -> 18 leg (~58s total):

step	duration
Initialize containers	21s
Start PostgreSQL 12 (pg-start)	16s
Recreate old cluster (checksums)	3s
Install PostgreSQL 18 (apt-get install postgresql-18 postgresql-server-dev-18)	4s
Stop old / binary pg_upgrade / start new cluster	6s
Update extension + run suite	5s

10 -> 18 leg (~108s total): same shape — "Initialize containers" (33s) + "Start PostgreSQL 10" (39s) = 72s of 108s (67%), vs. the actual pg_upgrade binary step itself at only 12s.

Confirmed separately: pgxn/pgxn-tools already ships every supported PG major pre-installed with a warm package cache — every test job leg (10 through 18) starts its version via plain pg-start <version> with no visible apt-get step at all, and even the explicit apt-get install postgresql-18 postgresql-server-dev-18 in the upgrade job (needed because pg-start only manages one active cluster and doesn't pull dev headers) only costs 4-6s. So package installation is not the bottleneck.

The bottleneck is cluster startup (pg-start, ~15-40s depending on load) and container boot (~20-30s) — i.e. initdb + postmaster start, paid fresh on every single job, regardless of PG version.

Proposal

Investigate a container image with PostgreSQL data directories already initdb'd (for the supported major range), so a job's pg-start-equivalent step can skip initdb and just start an already-initialized cluster.

Open question, not yet resolved — investigate before concluding this is a real win: does pg_regress (or whatever wrapper pgxntool/pgxn-tools uses to drive it) already force its own fresh instance/cluster setup underneath, independent of whatever cluster state the container starts with? If so, a pre-initdb'd base image might not actually save anything on the actual test-running path, only on ad hoc psql-based scripts (like bin/test_existing's cluster manipulation) that don't go through pg_regress at all. Worth confirming empirically (timing a real pg-start + make test against a pre-initdb'd vs freshly-initdb'd data directory) before investing in building the image.

Explicit caution on implementation shape: whatever this ends up being, it should keep cluster startup on-demand/per-job — only the initdb'd on-disk state should be pre-baked, not a container that eagerly starts every version's postmaster at boot. Most jobs only need one version running (two for pg_upgrade legs); starting all of them unconditionally would waste more than it saves.

Related, but distinct

Postgres-Extensions/pgxntool#96 proposes a pre-baked container with build dependencies pre-installed (rsync, postgresql-server-dev-*, pg_tle build deps) — same investigation lineage (real step-timing data driving a shared-image idea), but a different cost target (apt-get/build-dep time, not initdb/cluster-startup time). Filing separately since the concerns and any eventual image contents are distinct, though they could plausibly land in the same image if both pan out.

Status

Idea + real data only, not scoped for implementation. Flagged during CI-cost investigation for pg_count_nulls's pg-upgrade-test job.