Upgrading to PostgreSQL 18: Async I/O, UUIDv7, and a Zero Surprise Rollout

TL;DR: PostgreSQL 18 is an opportunity to improve I/O and identifier locality, not a reason to combine every schema change with a risky major-version cutover. Baseline the current system, test async I/O on your actual storage, decide whether UUIDv7 belongs in new or migrated tables, choose pg_upgrade or logical replication based on downtime and topology, rehearse the switch, and keep the old cluster recoverable until business verification completes.

Why this matters in 2026

PostgreSQL 18 shipped on September 25, 2025 and is now mature enough for production planning. Its headline changes include asynchronous I/O, UUIDv7 generation, retained optimizer statistics during pg_upgrade, virtual generated columns by default, and OAuth authentication support. The PostgreSQL 18 release notes are the authoritative list, but they do not tell you which changes are valuable for your workload.

The useful question is not whether PostgreSQL 18 is faster in a benchmark. It is whether your workload is limited by synchronous data-file reads, random primary-key locality, planning warmup after upgrades, or a feature that removes application-side work. A write-heavy event system may benefit from UUIDv7 locality. A memory-resident OLTP workload may see no meaningful async I/O gain. A read-heavy analytics system may need index and query changes first.

Major upgrades also change the operational blast radius. Extensions, logical replication slots, connection poolers, backup agents, monitoring collectors, and managed-service maintenance windows all participate in the event. The database is only one component of the upgrade.

Key terms and mental model

Use a layered mental model:

application connections -> pooler -> PostgreSQL 18
                                  -> planner and executor
                                  -> buffer cache and async I/O
                                  -> storage and WAL

If latency is dominated by pool exhaustion or lock contention, changing I/O mode will not fix it. If a write path is dominated by index page splits caused by random identifiers, UUIDv7 may help locality but does not remove the need for sensible fill factors, partitioning, or retention policy.

Baseline the current cluster

Capture seven days of representative metrics before upgrading: transaction rate, p50 and p95 latency by query family, buffer cache hit ratio, read and write IOPS, WAL volume, checkpoints, replication lag, lock waits, deadlocks, connections, and disk headroom. Save pg_stat_statements snapshots and identify the top queries by total time, calls, and shared block reads.

Also inventory the things that do not appear in a query dashboard:

• PostgreSQL extensions and their versions,
• client driver and ORM versions,
• pooler mode and prepared statement behavior,
• replication slots and subscribers,
• backup and point-in-time recovery tests,
• monitoring queries and alert thresholds,
• roles, authentication methods, and certificate rotation,
• cron jobs, migration runners, and maintenance scripts.

Run a restore test on the chosen PostgreSQL 18 image before touching production. A backup that restores only into the old image is not a migration plan. Validate extensions in a clean environment because a package available on a laptop may be missing from a managed service or a minimal container.

Test asynchronous I/O without guessing

PostgreSQL 18 exposes io_method values for synchronous I/O, worker-based asynchronous I/O, and io_uring where the server was built with the required library. The official resource configuration documentation describes the settings and defaults. The default worker mode is a sensible starting point; io_uring is an experiment to validate against your operating system and storage.

Do not benchmark only a sequential scan on an idle server. Test the queries that miss the buffer cache, the concurrent workload that competes for I/O, checkpoints, vacuum, index builds, and failover behavior. Run cold-cache and warm-cache variants. Record CPU, queue depth, read latency, and tail latency, not only throughput.

SHOW io_method;
SHOW io_workers;
SHOW effective_io_concurrency;
SHOW io_max_concurrency;

ALTER SYSTEM SET io_method = 'worker';
ALTER SYSTEM SET io_workers = 4;
SELECT pg_reload_conf();

Some settings require a server restart, so include a restart in the rehearsal. Avoid increasing every concurrency setting at once. More outstanding I/O can improve queue utilization or can saturate shared storage and harm tail latency. Change one variable, warm the system, replay the same workload, and compare distributions.

Decide where UUIDv7 belongs

UUIDv7 puts time information into the identifier while retaining the distributed generation properties teams expect from UUIDs. Its ordering can make B-tree insertion and range scans friendlier than fully random UUIDv4. It also reveals approximate creation time and should not be treated as a secret or authorization token.

The safest migration is often to use UUIDv7 for new tables, new tenants, or new event streams while keeping existing primary keys stable. Changing a primary key touches foreign keys, indexes, URLs, caches, audit records, and external integrations. The index locality benefit is not automatically worth a full identifier rewrite.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE audit_events (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id uuid NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX audit_events_tenant_created_idx
  ON audit_events (tenant_id, created_at DESC);

Choose based on the access pattern. If you paginate by created_at, keep that column even when the ID is time ordered. If external clients can enumerate IDs, use authorization and opaque resource lookup rather than assuming UUID format provides protection. If data residency or privacy makes timestamps sensitive, decide whether the convenience is worth the information disclosure.

Use the right upgrade path

pg_upgrade is usually the shortest path when the old and new clusters can share storage or be connected to the same host. It converts system catalogs and reuses user data files, which makes the outage largely about final shutdown, upgrade work, validation, and connection switching. PostgreSQL 18 retaining optimizer statistics reduces the cold-planner surprise that many teams associate with major upgrades, but you should still run representative queries and refresh statistics as needed.

Logical replication creates a new PostgreSQL 18 cluster and copies changes while the old cluster continues serving writes. It is useful when you need a long rehearsal window, a different operating system, or a migration between providers. It adds replication lag, DDL coordination, sequence management, large-object considerations, and cutover complexity.

For managed services, the provider may offer a blue-green upgrade, read replica promotion, or engine-specific workflow. For example, Amazon RDS PostgreSQL release notes and Aurora PostgreSQL release notes have separate support timelines. Do not assume a feature available in community PostgreSQL 18 is enabled in your managed engine build.

Rehearse the cutover and rollback

A rehearsal should use a production-shaped copy, the same migration runner, the same connection pool settings, and the same observability. Measure the exact timeline from write freeze to application reconnection. Include DNS or endpoint changes, secrets rotation, connection draining, worker restarts, cache invalidation, and background job behavior.

For a logical migration, define the write boundary precisely. Stop new writes, drain in-flight transactions, confirm replication has caught up, reconcile sequences and row counts, switch the writer endpoint, and run smoke tests. Keep the old cluster read-only and recoverable until verification passes. If you allow writes back to the old cluster during troubleshooting, you have created a divergent data problem.

set -euo pipefail

pg_isready -h "$NEW_DB_HOST" -p 5432 -d "$DB_NAME"
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -f migrations/post-cutover-checks.sql
psql "$NEW_DATABASE_URL" -v ON_ERROR_STOP=1 -c "SELECT now(), version();"
curl --fail --retry 3 "https://api.example.com/health/database"
curl --fail --retry 3 "https://api.example.com/health/critical-flow"

The rollback decision should be time boxed. Define thresholds for failed writes, replication lag, query tail latency, job failures, and business transaction errors. A rollback that is merely “switch the connection string back” is unsafe after writes have reached the new cluster. Design rollback around the chosen path and whether bidirectional writes are possible, which they usually are not without a deliberate conflict strategy.

Validate query plans and application behavior

Compare EXPLAIN (ANALYZE, BUFFERS) for the top query families before and after the upgrade. Look for changed join choices, row estimates, sort memory, index scans, and buffer reads. A planner improvement on one query can regress another, so use a workload replay or a carefully selected corpus rather than a single query.

Test application behavior around timestamps, JSON, arrays, collations, generated columns, extension functions, and driver type parsing. If you use virtual generated columns, verify that the ORM understands them and that reads do not assume a stored value exists physically. If you plan OAuth authentication, treat it as a separate security project with provider and pooler testing.

Connection pools deserve explicit tests. A major upgrade often changes the endpoint or certificate, and stale pools can continue sending traffic to the old writer. Restart application workers as part of the switch, expose the database server version in safe diagnostics, and alert if any application instance is connected to the wrong cluster.

Handle extensions, authentication, and maintenance

Extensions deserve their own compatibility table. Record whether each extension is installed in the old cluster, available at the target version, supported by the provider, and required during startup. A missing extension can prevent a database from accepting connections or can leave application queries failing only on a rarely used route. Test extension creation and upgrade scripts in a clean PostgreSQL 18 cluster, not just an already populated staging database.

PostgreSQL 18 also adds OAuth authentication support. That can be valuable for central identity, but it changes the failure path for poolers, background workers, restore jobs, and break-glass access. Treat it as a separate rollout unless your upgrade requirement is specifically the authentication feature. Maintain a local emergency role with a documented, audited procedure and verify that monitoring and migration jobs can authenticate without an interactive browser flow.

Maintenance should be part of the capacity model. Index builds, vacuum, checkpoints, logical replication, and backups compete with application work. Measure the new cluster during ordinary maintenance, then schedule any post-upgrade reindex or statistics refresh deliberately. If async I/O reduces read waits but leaves vacuum or WAL pressure unchanged, the correct next action may be storage capacity or maintenance tuning rather than another database parameter.

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots
ORDER BY slot_name;

SELECT datname, numbackends, xact_commit, xact_rollback
FROM pg_stat_database
WHERE datname = current_database();

Capture these inventories before and after the switch. A migration report should show that the required extensions, slots, roles, and database settings exist on the target, not only that the application health endpoint returned 200.

Also verify the administrative path. Confirm that the team can connect using the normal pooler, a direct operator connection, the migration role, and the break-glass role. Test certificate expiration, password rotation, connection limits, and a failed authentication attempt. An engine upgrade that preserves application reads but breaks the rotation job will become an incident later, often during a weekend maintenance window when the original migration team is unavailable.

Capture the target's parameter file and provider events as part of the change record. If a managed service applies defaults or rejects a setting, you should be able to explain the final state. Keep the extension and parameter inventory close to the rollback instructions so the next operator can identify whether a failure is data, application, or platform related.

Have a named operator own the first maintenance cycle after launch. They should watch vacuum, checkpoints, replication, backups, query plans, and connection behavior while the application team validates user workflows.

Have a named operator own the first maintenance cycle after launch. They should watch vacuum, checkpoints, replication, backups, query plans, and connection behavior while the application team validates user workflows. Keep that observation window in the change record.

Tradeoffs and when not to do this

Do not enable async I/O, migrate all identifiers to UUIDv7, change authentication, and move providers in the same outage. Each feature has a different failure mode. Isolate the engine upgrade from application schema changes where possible, then adopt new features after you have a stable baseline.

Stay on your current major version temporarily if the provider does not support the extensions or replication topology you require, if you cannot restore backups in a test environment, or if your team has no rollback path after writes switch. PostgreSQL 18 will remain useful only when it is operated with the same discipline as the existing cluster.

Common failure modes

Teams often trust a successful pg_upgrade --check too much. It finds compatibility problems detectable by the tool, not every application, extension, query-plan, or operational issue. Run the full suite and workload replay.

Another failure is benchmarking only warm-cache queries. Async I/O matters most where the storage path is active, while warm-cache results mostly measure CPU and query planning. A third failure is assuming UUIDv7 fixes a bloated index. Measure page splits, index size, vacuum behavior, and write amplification before and after.

Logical replication migrations frequently miss DDL, sequences, or large objects. Make schema changes repeatable and reconcile counts and sequence values before cutover. Finally, do not delete the old cluster immediately. Preserve the recovery artifact for a period that matches your incident and compliance requirements.

Production readiness checklist

• PostgreSQL, extension, driver, ORM, pooler, and provider versions are inventoried.
• Backups restore successfully into a PostgreSQL 18 test cluster.
• Top query plans, latency distributions, lock waits, and I/O metrics have a baseline.
• Async I/O is tested on the actual storage and operating system.
• UUIDv7 adoption is limited to tables where locality and timestamp exposure are acceptable.
• The selected upgrade path has a rehearsed timeline and named owners.
• DDL, sequences, extensions, large objects, and replication slots are covered.
• Application pools, workers, jobs, certificates, and endpoints are switched deliberately.
• Cutover smoke tests verify writes, reads, auth, tenant isolation, and critical workflows.
• Rollback thresholds are written down and match the data movement model.
• Old data and backup artifacts remain recoverable after cutover.

Frequently Asked Questions

Is PostgreSQL 18 automatically faster because it has async I/O?

No. Async I/O gives the executor more ways to overlap data-file reads, but the result depends on cache hit rate, storage latency, query shape, CPU, concurrency, and configuration. A workload that is already memory resident may see little change. Benchmark cold and warm cache cases with production-shaped concurrency, then inspect tail latency and I/O queue behavior rather than relying on a single throughput number.

Should I convert every UUIDv4 primary key to UUIDv7 during the upgrade?

Usually not. A primary-key rewrite affects foreign keys, indexes, APIs, caches, audit data, and external references. Adopt UUIDv7 for new tables or high-ingest paths first, and measure locality benefits. Keep an explicit creation timestamp because UUIDv7 is not a replacement for a business time column. Also remember that UUIDv7 exposes approximate creation time, so it should not be treated as a secret.

Which is safer, pg_upgrade or logical replication?

Neither is universally safer. pg_upgrade has less moving data and a shorter planned outage, but it ties the operation closely to compatible binaries and storage. Logical replication gives you a longer overlap and a new environment, but it creates lag, DDL, sequence, and cutover responsibilities. Choose based on downtime, topology, provider support, and the rollback behavior you can rehearse.

Does pg_upgrade keep all query performance warm?

PostgreSQL 18 retains optimizer statistics during pg_upgrade, which avoids one common cold-planner problem. It does not preserve the operating system page cache, application caches, connection pools, or every runtime condition. Run representative queries after the switch, watch plans and latency, and let normal vacuum and maintenance complete before declaring the cluster fully stable.

Can I use PostgreSQL 18 features on a managed service immediately?

Only after checking the provider's engine version, extension list, parameter support, backup behavior, and maintenance workflow. Community PostgreSQL release availability and managed-service availability are separate schedules. Validate the exact service version in a staging environment, and confirm how the provider handles replicas, failover, storage, and rollback before committing to a feature such as async I/O configuration or OAuth authentication.

Conclusion and next steps

PostgreSQL 18 provides useful building blocks, but a safe upgrade is still an evidence problem. Baseline the old cluster, test async I/O and UUIDv7 separately, choose an upgrade path that matches your topology, and rehearse the exact cutover with a recoverable old state.

Use the zero-downtime migration guide to review expand and contract behavior, then compare your top query families with the PostgreSQL telemetry scaling guide. Your next concrete artifact should be a one-page cutover and rollback timeline.