PostgreSQL 18 is not a release I would treat as a weekend package bump. It is the current stable line, with 18.4 listed as the current minor release, and PostgreSQL 19 is already in beta for a planned September 2026 major release. That makes now a good time to rehearse 18 on real workloads, not because every team must rush, but because the upgrade contains enough operational behavior changes to reward early measurement.
The shape of the release
The headline feature is asynchronous I/O. PostgreSQL 18 added a new I/O subsystem for sequential scans, bitmap heap scans, vacuum, and related work. The project announcement says some read-heavy scenarios have shown up to 3x improvement, but I would not carry that number into a roadmap as a promise. I would use it as a reason to test the parts of my system that actually read a lot of data.
The quieter changes matter just as much. PostgreSQL 18 keeps optimizer statistics through pg_upgrade, adds skip scan support for multicolumn B-tree indexes, generates timestamp-ordered UUIDs with uuidv7(), enables page checksums by default for new clusters, and changes EXPLAIN ANALYZE so buffer details are part of the default story. That mix makes the release feel less like a single feature and more like a database operations release.
I would frame the rehearsal as five questions:
| Question | Why it matters |
|---|---|
| Does the upgrade path preserve plan quality? | Planner stats retention should reduce the post-upgrade wobble, but hot queries still need proof. |
| Which queries benefit from AIO? | The improvement is workload dependent, especially around scans and vacuum. |
| Do checksums change our provisioning story? | New clusters default to checksums, while pg_upgrade needs old and new clusters to agree. |
| Do indexes still match query shape? | Skip scan can make some existing multicolumn indexes more useful. |
| Are auth and password assumptions current? | MD5 password authentication is deprecated in 18 and warns more loudly on the path to 19. |
Step 1: rehearse the upgrade, not only the restore
A backup restore proves you can recreate data. An upgrade rehearsal proves you can move production behavior across versions. Those are not the same test.
For PostgreSQL 18, I would run at least one pg_upgrade --check, then a full upgrade rehearsal with the same filesystem strategy I expect to use in production. The --jobs flag can process multiple databases and tablespaces in parallel, so a multi-database server deserves timing with realistic concurrency. PostgreSQL 18 also documents faster upgrade behavior around many objects and retained statistics, which is exactly the sort of improvement that can hide edge cases until a real catalog is involved.
The checksum change is the first trap I would look for. New PostgreSQL 18 clusters initialize with page checksums by default. If the old cluster does not use checksums, the new cluster must be created with matching settings for pg_upgrade. That is a provisioning decision, not just a database decision, because it touches cluster creation scripts, managed service defaults, and disaster recovery runbooks.
Step 2: capture plans before the switch
Before I touch a production-like cluster, I want a short list of queries that represent the application. Not every slow query, and not every dashboard query. I want the queries that define user-visible latency, background job cost, and billing or analytics pressure.
For each query, capture the plan on the old version and on PostgreSQL 18:
EXPLAIN (ANALYZE, BUFFERS)
select *
from events
where account_id = $1
and created_at >= now() - interval '7 days'
order by created_at desc
limit 100;
I keep BUFFERS explicit even though PostgreSQL 18 makes buffer reporting easier with EXPLAIN ANALYZE, because it communicates intent in review. I want to see whether the plan changed, whether heap reads moved, whether index searches changed, and whether the estimate mismatch got better or worse.
Skip scan deserves specific attention. If a multicolumn B-tree index has a low-cardinality leading column and a selective later column, PostgreSQL 18 may be able to use that index in cases older versions skipped. That does not mean every compound index is suddenly good. It means the old rule of thumb, where the leftmost equality condition dominated the conversation, now needs more nuance.
Step 3: test AIO like a feature flag
The new io_method setting can use worker, io_uring, or sync, with worker as the default. io_uring also depends on build support, so I would not assume it exists just because the operating system is Linux. For a self-managed database, that means checking the build and planning a restart. For a managed database, it means reading the provider's exact PostgreSQL 18 support notes before assuming the setting is exposed.
My rehearsal would compare three workloads:
- A sequential scan over a large table that does not fit in cache.
- A bitmap heap scan used by a real filtered query.
- A vacuum window on a table that receives steady writes.
I would measure wall time, shared and read buffers, I/O wait, and whether other application traffic became noisier during the test. The win I care about is not a synthetic best case. It is lower tail latency or shorter maintenance under a production-shaped mix.
This is the same discipline I like for runtime hardening work. In my earlier post on the Node.js permission model in Node 24, the practical question was not whether the feature existed. It was which boundary it made enforceable in a real deployment. PostgreSQL 18 deserves the same treatment.
Step 4: decide where uuidv7 belongs
PostgreSQL 18 ships uuidv7(), which generates timestamp-ordered UUIDs. That is attractive for write-heavy tables because random UUIDv4 primary keys can be rough on locality. I would still avoid a blanket migration of existing primary keys. Primary keys are API contracts, replication contracts, and incident-response handles.
The sensible adoption path is narrower:
- Use
uuidv7()for new append-heavy tables where IDs are generated in the database. - Keep existing public identifiers stable unless there is a measured storage or index reason to migrate.
- Document whether application code or the database owns ID generation.
If the app already generates IDs at the edge, database-side uuidv7() may be the wrong boundary. If the database is the write authority, it is a strong default for new tables.
Step 5: turn PostgreSQL 19 beta into a warning system
PostgreSQL 19 beta is useful even if I would not run it in production. The beta release notes show the direction of travel: MD5 authentication warnings continue, RADIUS support is removed, JIT defaults change, autovacuum gets more controls, and AIO keeps receiving attention.
That tells me what to clean up while adopting 18. Remove MD5 password assumptions. Check auth integrations. Keep database parameters explicit instead of relying on defaults I do not remember setting. Watch for anything that makes a future 19 upgrade harder.
The best upgrade plan is boring in production because it was interesting in rehearsal. PostgreSQL 18 has enough real improvements to justify the work, but the payoff comes from measuring the exact queries, indexes, authentication paths, and provisioning scripts that your system already depends on.