Postgres 18 Upgrade

[cigamit]

Upgrade PostgreSQL 15 → 18

We currently run quay.io/sclorg/postgresql-15-c9s (dev compose) and PG15 in production.
PG15 goes EOL November 2027, so a major upgrade is due regardless. SCLorg skipped 17
but publishes actively-maintained 18 images (postgresql-18-c9s / postgresql-18-c10s),
and PG18 (released September 2025) is now a year old and several minor releases in.
Jumping straight to 18 gets us everything from 16 and 17 on the way and is supported
until November 2030.

Stack compatibility is already in place: psycopg 3.3.x has explicit PG18 support, and
Django 5.2 works against 18 (official support is declared in Django 6.0, which we're
heading toward anyway).

Benefits

Inherited from PostgreSQL 16

• pg_stat_io view — proper per-backend-type I/O observability
• Faster COPY (bulk loading) and expanded SIMD acceleration (JSON/ASCII processing)
• Vacuum freezing performance improvements
• Logical replication from standbys

Inherited from PostgreSQL 17

• B-tree multi-value index scans — WHERE id IN (...) / = ANY(...) executes as a
  single index scan instead of one descent per value. The ORM and our RBAC filtering
  generate large IN lists constantly; this is the biggest free win for API list-view latency.
• Vacuum memory rewrite (TidStore) — up to ~20x less memory for dead-tuple tracking,
  1GB cap removed, fewer index passes. Helps the tables that churn in place
  (main_unifiedjob, main_host, sessions); event partitions already dodge vacuum by
  being dropped whole.
• WAL write scalability — reduced WALWriteLock contention, up to ~2x write throughput
  under high concurrency. Directly relevant to concurrent bulk inserts from
  callback-receiver workers (the job-event firehose).
• Identity columns allowed on partitioned tables (relevant to our hand-rolled event
  partition DDL if we ever align it with Django 5's native PK handling)
• Incremental backup (pg_basebackup --incremental)
• Faster sequential scans and ANALYZE via streaming I/O

New in PostgreSQL 18

• Asynchronous I/O subsystem — up to 2–3x throughput on I/O-bound reads (seq scans,
  bitmap heap scans, vacuum). Default io_method = worker works in any container.
• B-tree skip scan — multicolumn indexes usable when the query doesn't filter on the
  leading column; rescues ORM queries that today fall back to seq scans
• pg_upgrade preserves planner statistics — coming from 15, we skip the traditional
  post-upgrade window of bad plans until a full ANALYZE completes (painful with large
  event tables). Also --swap mode and better parallelism for a shorter cutover.
• uuidv7() — timestamp-ordered, index-friendly UUIDs for future schema work
• Parallel GIN index builds (relevant if we ever index into JSONB event data)
• Better observability defaults — EXPLAIN shows buffer usage by default, improved
  per-connection I/O stats

Caveats / gotchas

• Data checksums are on by default at initdb in 18. pg_upgrade requires old and new
  clusters to match, and our PG15 clusters almost certainly have them off. The new cluster
  must be initialized with --no-data-checksums (or convert the old one first with
  pg_checksums). Affects production upgrade tooling; dev compose just starts a fresh volume.
• Do not set io_method = io_uring in shared config templates. Postgres fails at
  startup (no fallback) where io_uring is unavailable — and it is unavailable on our
  infrastructure by default:
  • RHEL/Rocky 9 kernels ship kernel.io_uring_disabled = 2 (disabled kernel-wide)
  • containerd/CRI-O default seccomp profiles block the io_uring syscalls (matters wherever
    pods set seccompProfile: RuntimeDefault or the restricted Pod Security Standard applies)
  • Stick with the default io_method = worker; it captures most of the AIO benefit.
    io_uring is an optional later opt-in for dedicated single-tenant DB nodes only
    (node sysctl + seccomp exception).
• MD5 password auth is formally deprecated in 18 (warns at startup). We should be on
  SCRAM already, but grep any pg_hba templates in the installer to confirm.
• Django 5.2 doesn't officially list PG18 (that lands in Django 6.0) — works in practice
  with psycopg 3.3, but run the full test suite against 18 before cutover.
• Backup/restore tooling (pg_dump/pg_restore in setup playbooks and the operator) must
  use PG18 client binaries against the 18 server.
• adminpack extension was removed (in 17) — only relevant if a deployment tool installed it.
• pg_upgrade goes 15 → 18 directly; no need to step through intermediate majors.

Tasks

• Bump dev compose image to quay.io/sclorg/postgresql-18-c9s and smoke-test the stack
• Run full API test suite against PG18
• Audit installer/operator upgrade path (checksums flag, client binary versions, pg_hba)
• Verify event-partition create/drop tooling against 18
• Load-test job-event ingest to confirm WAL/AIO gains

[blaipr]

Nice!