PostgreSQL 18 release

TL;DR: Top 5 changes

  • Asynchronous I/O subsystem: Broad speedups for seq scans, bitmap scans, and VACUUM — no query changes needed.
  • Btree skip scans: Reuse multi-column indexes even when filtering by a non-leading column.
  • Right semi join plans: New plan type to speed up “exists-at-least-one” patterns.
  • Parallel GIN index builds: Faster builds for large text/jsonb arrays.
  • Virtual generated columns by default: Compute-on-read, simpler writes, optional STORED for heavy cases.

Quick wins

  • Turn on async I/O thoughtfully: Start with defaults; if your storage is fast but fragmented, raise io_combine_limit and validate with EXPLAIN (ANALYZE, BUFFERS).
  • Lean on skip scans: Before adding a new index on a trailing column, check if your existing multi-column btree already helps.
  • Parallelize GIN builds: Rebuild huge text/jsonb indexes faster during maintenance windows.
  • Prefer virtual generated columns: Keep writes light; switch to STORED only if read cost dominates.
  • Observe more, guess less: Use pg_stat_io, richer EXPLAIN details, and updated logs to guide tuning.

Overview

If PostgreSQL 17 felt fast, 18 feels like it found another gear. You get smarter plans, less waiting on disk, and more insight into what your database is actually doing — without rewriting queries.

Highlights include a brand-new asynchronous I/O subsystem, btree skip scans, right semi join plans, parallel GIN builds, OAuth auth, generated columns in logical replication, virtual generated columns by default, richer EXPLAIN/ANALYZE, and a wave of psql and tooling upgrades. The full list is long (and fun to read) — see the official notes. [1]PostgreSQL 18 Release Notes

Migration to Version 18

A dump/restore via pg_dumpall, pg_upgrade, or logical replication is required when migrating from previous major versions. Review compatibility changes carefully before upgrading.

  • Default initdb now enables data checksums (disable with --no-data-checksums; clusters must match for pg_upgrade).
  • Time zone abbreviation resolution prioritizes the current session before timezone_abbreviations.
  • MD5 password authentication is deprecated; CREATE ROLE/ALTER ROLE warn (configurable via md5_password_warnings).
  • VACUUM and ANALYZE now process inheritance children by default; use ONLY for former behavior.
  • COPY FROM no longer treats \. as EOF in CSV; psql still does for STDIN, and \. must be alone on its line.
  • Unlogged partitioned tables are disallowed.
  • AFTER triggers execute as the role active when events were queued.
  • Removal of non-functional rule privileges in GRANT/REVOKE.
  • Memory stats catalog adjustments (e.g., pg_backend_memory_contexts).
  • Full text search now respects the cluster’s default collation provider; for non-libc defaults, reindex FTS/pg_trgm after pg_upgrade.

Quick migration checklist

  • Back up: pg_dumpall > dump.sql (or per-db pg_dump), and/or ensure your backup tool has a fresh snapshot.
  • Initialize new cluster (checksums are enabled by default):
Terminal window
initdb -D /path/to/pg18
  • Stop old cluster and run pg_upgrade (consider -k for links and -j for parallelism):
Terminal window
pg_upgrade \
  -d /path/to/old/datadir \
  -D /path/to/pg18 \
  -b /path/to/old/bin \
  -B /path/to/pg18/bin \
  -j 4 -k
  • Post-upgrade analyze (and optionally reindex FTS/trgm if your cluster uses non-libc collations):
Terminal window
./analyze_new_cluster.sh
-- For non-libc default providers after pg_upgrade:
REINDEX (CONCURRENTLY) INDEX your_fts_or_trgm_idx;  -- repeat as appropriate
  • Review auth (MD5 is deprecated), time zone abbreviation behavior, and inheritance ONLY changes for VACUUM/ANALYZE.
  • Restart apps and monitor with the new pg_stat_io, logs, and EXPLAIN details.

Docs: [1]pg_upgrade, [1]initdb, [1]psql

Highlights by Area

Optimizer

  • Eliminates certain unnecessary self-joins (toggle with enable_self_join_elimination).
  • Smarter transformations: IN (VALUES ...) → = ANY(...); OR-to-array conversions for faster index processing.
  • Faster INTERSECT, EXCEPT, window aggregates, and view column aliasing.
  • Reorders SELECT DISTINCT keys to avoid sorts (toggle enable_distinct_reordering).
  • Drops GROUP BY columns functionally dependent on unique keys.
  • Pushes some HAVING on GROUPING SETS to WHERE.
  • Better row estimates for generate_series() with numeric/timestamp.
  • Introduces right semi join plans and incremental sort for merge joins.
  • Improved planning for many partitions; expanded partitionwise joins; refined partition cost estimates.
  • Better plan caching for SQL-language functions; clearer handling of disabled optimizer features.

Indexes

  • Btree skip scans let multi-column btree indexes serve queries filtering by later columns.
  • Non-btree unique indexes can be partition keys and used in materialized views (must support equality).
  • Parallel GIN index creation.
  • Sorted value builds speed up range-type GiST/btree index creation.

Btree skip scans: when they help (examples)

Skip scans allow PostgreSQL to use a multi-column btree index even if the query filters only on a non-leading column. This can reduce the need for additional indexes and speed up existing workloads, depending on data distribution.

Example setup:

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  order_date  date   NOT NULL,
  total       numeric(12,2) NOT NULL
);


-- Common index built for typical lookups per customer and time
CREATE INDEX orders_customer_date_idx ON orders (customer_id, order_date);

Queries that can now benefit without adding a separate (order_date) index:

-- Equality on trailing column can leverage skip scans on (customer_id, order_date)
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM orders
WHERE order_date = DATE '2025-01-01';


-- Range on trailing column may also benefit
EXPLAIN (COSTS OFF)
SELECT *
FROM orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
ORDER BY order_date
LIMIT 50;

Practical guidance:

  • Best when the number of distinct values of the leading column(s) is moderate; extremely high cardinality can erode the benefit.
  • Skip scans reduce the need for redundant indexes, but not always. If most queries filter by order_date alone, a leading (order_date, ...) index can still outperform.
  • Validate with EXPLAIN (ANALYZE, BUFFERS) to compare against a sequential scan on your data.

Why it matters

Fewer indexes, faster plans. Skip scans let you lean on existing multi-column btrees for more query shapes, which means less write overhead (fewer indexes to maintain) and simpler schema design.

Pitfalls

Skip scans aren’t a replacement for proper index design. Here’s when they break down and what to do instead:

High cardinality gotcha:

  • Problem: If customer_id has millions of distinct values, skip scans become inefficient. PostgreSQL must examine many index entries to find matching order_date values.
  • Solution: Use EXPLAIN (ANALYZE, BUFFERS) to compare. If skip scan shows high “rows examined” vs “rows returned”, consider a dedicated (order_date, ...) index.

Heavy workload gotcha:

  • Problem: If 80% of your queries filter by order_date alone, skip scans add unnecessary overhead compared to a purpose-built index.
  • Solution: Monitor query patterns. When trailing-column queries dominate, create (order_date, customer_id) or (order_date) indexes.

Performance decision framework:

-- Test your actual query patterns
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE order_date = '2025-01-01';


-- Look for these warning signs in skip scan plans:
-- • "Rows Removed by Filter" >> "returned rows"
-- • High buffer reads relative to result size
-- • Execution time consistently slower than seq scan

When to stick with skip scans: Occasional queries (< 20% of workload), moderate leading column cardinality (< 100K distinct values), acceptable performance for your SLA.

When to add dedicated indexes: Frequent queries, high-cardinality leading columns, performance-critical workloads, or when EXPLAIN shows poor efficiency ratios.

Real-world example

An analytics app stores orders with (customer_id, order_date) index. Product wants a monthly report by order_date only. Instead of adding a new (order_date) index, skip scans let the existing index serve those queries acceptably well. You keep writes cheap (no extra index maintenance) and production stays simpler. If reports become daily and heavy, re-evaluate a dedicated (order_date, ...) index.

Details: [1]PostgreSQL 18 Release Notes — Indexes

General Performance

  • New asynchronous I/O subsystem: better sequential scans, bitmap heap scans, vacuum, etc. Tunables: io_method, io_combine_limit, io_max_combine_limit. New view: pg_aios.
  • Faster, lower-memory hash joins and GROUP BY; improvements also benefit EXCEPT and subplan lookups.
  • Normal vacuum can eagerly freeze some all-visible pages (tunable via vacuum_max_eager_freeze_failure_rate).
  • vacuum_truncate server variable controls truncation behavior.
  • Defaults for effective_io_concurrency and maintenance_io_concurrency raised to 16.

Asynchronous I/O subsystem: how to benefit (examples)

PostgreSQL 18 backends can queue multiple reads, improving throughput for sequential scans, bitmap heap scans, and maintenance (e.g., VACUUM). It’s transparent to applications and configurable via server variables.

Typical tuning (choose OS‑appropriate values; see docs):

-- postgresql.conf or via ALTER SYSTEM (requires reload/restart depending on setting)
ALTER SYSTEM SET io_method = 'posix';         -- pick the method suitable for your platform
ALTER SYSTEM SET io_combine_limit = 32;       -- number of adjacent reads to combine
ALTER SYSTEM SET io_max_combine_limit = 128;  -- hard upper bound for combining
SELECT pg_reload_conf();

Example workload that benefits (large range scan on time-series):

CREATE TABLE events (
  id       bigserial PRIMARY KEY,
  ts       timestamptz NOT NULL,
  user_id  bigint NOT NULL,
  payload  jsonb
);


CREATE INDEX events_ts_idx ON events (ts);


-- Range scan over recent data
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE ts >= now() - interval '7 days';

Observability:

-- See asynchronous I/O file handles/queues
SELECT * FROM pg_aios LIMIT 20;


-- High-level I/O bytes and timings
SELECT backend_type, context, read_bytes, write_bytes, extend_bytes
FROM pg_stat_io
ORDER BY read_bytes DESC
LIMIT 10;

Tips:

  • Start with defaults; adjust combine limits if you observe fragmented I/O patterns.
  • Use EXPLAIN (ANALYZE, BUFFERS) to validate improvements (look at read times and buffer hits/misses).
  • Maintenance tasks like VACUUM (VERBOSE) also benefit by issuing concurrent reads more efficiently.

Docs: [1]PostgreSQL 18 Release Notes — Asynchronous I/O, [1]Monitoring Statistics, [1]Runtime Configuration — Resources

Why it matters

It’s a free performance upgrade for read-heavy workloads and maintenance: more throughput with the same hardware and no app changes.

Pitfalls

  • Overly aggressive combine limits can backfire on highly random I/O; measure and dial in.
  • Always test on staging with representative data and load before rolling changes to production.

Real-world example

A multi-tenant SaaS runs periodic range scans and nightly VACUUM on large tables. Enabling async I/O with a modest io_combine_limit reduces wall time for the scans and VACUUM cycles without code changes. Observability via pg_stat_io confirms more bytes read per second and fewer waits; EXPLAIN shows lower I/O timings while plans remain stable.

Monitoring

  • Finer-grained log_connections and stage duration reporting; %L log prefix for client IP.
  • log_lock_failures for NOWAIT lock failures.
  • pg_stat_all_tables adds total times for vacuum/analyze and auto variants.
  • Delay time reporting across logs, pg_stat_progress_vacuum/analyze, and VERBOSE output.
  • ANALYZE VERBOSE: WAL/CPU/avg read stats; autovacuum/VACUUM report full WAL buffer count.
  • Per-backend I/O and WAL statistics with reset functions.
  • pg_stat_io reports bytes read/written/extend; adds WAL activity rows; track_wal_io_timing refocused here.
  • Checkpointer stats additions and SLRU writes coverage.
  • Query id jumbling improvements.
  • Memory context type and path columns.

Privileges

  • New pg_get_acl() and has_largeobject_privilege(); default privileges for large objects via ALTER DEFAULT PRIVILEGES.
  • Predefined role pg_signal_autovacuum_worker to signal autovacuum workers.

Server Configuration

  • OAuth authentication method support (pg_hba, libpq options, oauth_validator_libraries).
  • TLS updates: ssl_tls13_ciphers; ssl_groups default includes X25519; ssl_ecdh_curve renamed to ssl_groups (compat preserved).
  • 256-bit cancel request keys (requires wire protocol 3.2).
  • autovacuum_worker_slots caps background workers enabling runtime increases of autovacuum_max_workers.
  • Fixed dead tuple threshold for autovacuum via autovacuum_vacuum_max_threshold.
  • max_files_per_process now limits only backend-opened files.
  • num_os_semaphores exported; extension_control_path configurable.

Streaming Replication & Recovery

  • Auto-invalidate inactive replication slots with idle_replication_slot_timeout.
  • max_active_replication_origins decouples origin limit from slot count.

Logical Replication

  • Generated columns can be replicated; publish_generated_columns controls behavior when no column list is specified.
  • Default CREATE SUBSCRIPTION ... STREAMING is now parallel.
  • ALTER SUBSCRIPTION can change slot 2PC behavior; conflict logging and pg_stat_subscription_stats columns added.

Utility & SQL

  • Virtual generated columns (on read) now supported and default; STORED remains available.
  • RETURNING can reference old/new across DML including MERGE.
  • CREATE FOREIGN TABLE ... LIKE local tables; LIKE and text-position functions support nondeterministic collations.
  • Built-in collation provider PG_UNICODE_FAST (codepoint order, natural-language case mapping).
  • VACUUM/ANALYZE ONLY for partitioned tables; functions to modify/clear relation/attribute stats.
  • file_copy_method controls DB/TB file copy vs clone behavior.

Constraints

  • Non-overlapping PRIMARY KEY/UNIQUE via WITHOUT OVERLAPS on last column.
  • CHECK and FK constraints can be NOT ENFORCED; new pg_constraint.conenforced.
  • Deterministic or identical nondeterministic collations required for PK/FK relationships (affects dump/upgrade correctness).
  • NOT NULL stored in pg_constraint (named constraints, adds NOT NULL to foreign tables and inheritance control).
  • ALTER TABLE supports [NO] INHERIT for NOT NULL; NOT VALID FKs on partitioned tables; constraint dropping ONLY on partitioned tables.

COPY

  • REJECT_LIMIT with ON_ERROR='ignore'; LOG_VERBOSITY=silent for ignored rows.
  • COPY TO from populated materialized views; disallow COPY FREEZE on foreign tables.

EXPLAIN

  • EXPLAIN ANALYZE includes BUFFERS automatically; WAL buffer count in EXPLAIN (WAL).
  • Fractional row counts; index lookups per index scan; memory/disk usage for Material/WindowAgg/CTEs; detailed window args.
  • Parallel Bitmap Heap Scan cache stats; disabled nodes flagged in ANALYZE output.

Data Types

  • Unicode 16; improved full case mapping and casefold(); conditional/title case.
  • jsonb nulls cast to scalar NULL; json{b}_strip_nulls supports removing null array elements.
  • Array helpers: array_sort(), array_reverse(); reverse(bytea); casts between integers and bytea.
  • Estonian stemming; improved XML error codes.

Functions

  • casefold() for sophisticated case-insensitive matching.
  • MIN/MAX on arrays and composite types.
  • EXTRACT(WEEK); improved negative EXTRACT(QUARTER).
  • Roman numerals in to_number() via RN.
  • UUID v7 uuidv7() (plus explicit alias uuidv4()); crc32()/crc32c(); gamma()/lgamma().
  • Named args for PL/pgSQL cursor => and for regexp-related functions.

psql

  • Parse/bind/close named prepared statements: \parse, \bind_named, \close_prepared.
  • Pipeline query commands and prompt/vars for pipeline status.
  • Prompt includes connection service name; expanded mode for list commands via x suffix.
  • Richer \conninfo; function leakproof indicator flags; partitioned access method details; default_version in \dx.
  • WATCH_INTERVAL variable.

Server applications & tools

  • initdb defaults to checksums (disable with --no-data-checksums); --no-sync-data-files added.
  • vacuumdb --missing-stats-only (superuser; with --analyze-only/--analyze-in-stages).
  • pg_combinebackup -k/--link; pg_verifybackup verifies tar backups.
  • pg_rewind uses dbname in --write-recovery-conf when provided; pg_resetwal --char-signedness.

pg_upgrade

  • Preserves optimizer statistics by default (except extended stats); --no-statistics disables.
  • Parallelize database checks via --jobs; directory --swap mode; --set-char-signedness option for CPU char signedness.

Extensions & ecosystem

  • New extensions: pg_logicalinspect, pg_overexplain; postgres_fdw_get_connections() adds columns including used_in_xact, closed, user_name, remote_backend_pid.
  • SCRAM passthrough for postgres_fdw and dblink (avoid storing creds; new libpq params scram_client_key, scram_server_key).
  • file_fdw adds on_error, log_verbosity, and reject_limit when ON_ERROR='ignore'.
  • passwordcheck gains min_password_length.
  • pgbench reports failed/retried/skipped txn counts per script.
  • isn adds server var weak.
  • btree_gist and amcheck improvements; pg_buffercache eviction helpers.
  • Extensions can add EXPLAIN options and use cumulative stats API.

pg_stat_statements & pgcrypto

  • Track CREATE TABLE AS and DECLARE queries; parameterize SET to reduce bloat; parallel activity columns; WAL buffers full.
  • pgcrypto: adds sha256crypt, sha512crypt, CFB mode; fips_mode(); builtin_crypto_enabled to disable non-FIPS crypto.

Practical recipes you can use today

Virtual generated columns (lighter writes)

Use virtual generated columns to compute values on read rather than on write. Great for keeping insert/update paths lean.

-- New tables
CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text NOT NULL,
  email_lower text GENERATED ALWAYS AS (lower(email)) VIRTUAL
);


-- Existing tables
ALTER TABLE users
  ADD COLUMN email_lower text GENERATED ALWAYS AS (lower(email)) VIRTUAL;


-- Switch to STORED if reads dominate and you want fixed read cost
ALTER TABLE users
  ALTER COLUMN email_lower SET STORAGE PLAIN;  -- and redefine as STORED if needed

Why it matters: fewer write-time CPU cycles; better for bulk loads. Pitfall: heavy read hotspots may prefer STORED.

Parallel GIN index builds (faster maintenance)

Large text/jsonb indexes can build faster with parallel workers.

-- Allow the server to use more workers for index builds
SET max_parallel_maintenance_workers = 4;  -- session-level; set in postgresql.conf for global


-- Non-concurrent builds can use parallelism
CREATE INDEX docs_content_gin ON docs USING GIN (to_tsvector('english', content));

Why it matters: shorter maintenance windows. Pitfall: parallelism generally applies to non-CONCURRENTLY builds; plan accordingly.

Docs: [1]GIN Indexes, [1]max_parallel_maintenance_workers

COPY FROM with REJECT_LIMIT (safer ingest)

Load imperfect CSVs without aborting the entire batch.

-- Stage into a temp/staging table first
CREATE TEMP TABLE stage_users (
  id bigint,
  email text,
  created_at timestamptz
);


COPY stage_users FROM '/path/to/users.csv'
  WITH (
    FORMAT csv,
    HEADER true,
    ON_ERROR 'ignore',
    REJECT_LIMIT 100,
    LOG_VERBOSITY 'silent'
  );


-- Inspect bad rows (e.g., by difference from source row count) before merging into target
INSERT INTO users (id, email)
SELECT id, email FROM stage_users
WHERE email ~ '^[^@]+@[^@]+$';

Why it matters: better DX for data onboarding. Pitfall: ensure you log/track rejects elsewhere if silent is used.

Docs: [1]COPY

Logical replication of generated columns

Replicate generated columns without recomputing on the subscriber.

-- Publisher
CREATE PUBLICATION app_pub FOR TABLE public.orders;
ALTER PUBLICATION app_pub SET (publish_generated_columns = true);


-- Subscriber
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=... dbname=... user=... password=...'
  PUBLICATION app_pub
  WITH (streaming = parallel);

Why it matters: simpler heterogenous replication; less custom logic on subscribers. Pitfall: confirm column lists/options per table.

Docs: [1]Logical Replication

EXISTS patterns and right semi join plans

Exists-at-least-one checks are everywhere; 18 can plan them more efficiently.

-- Classic EXISTS pattern
SELECT o.*
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.order_id = o.id AND i.sku = 'ABC'
);


-- Helpful indexes
CREATE INDEX ON order_items (order_id, sku);

Why it matters: faster existence checks on large tables. Pitfall: ensure join key indexes exist on the inner side.

Details: [1]PostgreSQL 18 Release Notes — Optimizer

VACUUM/ANALYZE ONLY for partitioned tables

Operate on the parent without touching children when needed.

VACUUM (VERBOSE, ONLY) sales_by_month;   -- parent only
ANALYZE (ONLY) sales_by_month;           -- parent only

Why it matters: targeted maintenance; avoids surprise work on children. Pitfall: remember autovacuum still manages children.

Docs: [1]VACUUM, [1]ANALYZE

RETURNING old/new for auditing and debugging

Use the new old/new aliases to capture before/after values in a single statement.

-- Update and capture both versions
UPDATE accounts AS a
SET balance = a.balance - 100
WHERE a.id = 42
RETURNING old.balance AS balance_before, new.balance AS balance_after;


-- Delete and capture the old row
DELETE FROM sessions s
WHERE s.last_seen < now() - interval '30 days'
RETURNING old.id, old.user_id, old.last_seen;

Why it matters: less boilerplate for audit logs; easier debugging. Pitfall: validate that client ORM/driver surfaces both aliases.

Docs: [1]UPDATE, [1]DELETE, [1]Generated Columns

Top 5 gotchas

  • MD5 auth is deprecated: Move to SCRAM; watch for deprecation warnings in role changes.
  • VACUUM/ANALYZE inheritance behavior changed: They now include children by default; use ONLY for the old behavior.
  • CSV \\. handling: COPY FROM no longer treats \\. as EOF; psql still does for STDIN, and it must be on its own line.
  • FTS/trgm + non-libc collations: After pg_upgrade, reindex affected indexes to avoid surprises.
  • Skip scans ≠ magic: Great for occasional trailing-column queries, but if >20% of your workload hits those columns or you have high-cardinality leading columns (>100K distinct values), you still need purpose-built indexes. Use EXPLAIN (ANALYZE, BUFFERS) to validate performance vs dedicated indexes.

Acknowledgments

Thanks to the PostgreSQL community of contributors, reviewers, and testers for another strong release. See the full contributor list in the official notes. [1]

References