PostgreSQL 18: Every Performance Improvement You Need to Know

Short Query Throughput: 20–30% Improvement for OLTP Workloads

The most broadly applicable improvement in PostgreSQL 18 is a 20–30% increase in short query throughput — queries that complete in under a millisecond. The gains come from reductions in per-query overhead in the planner and executor code paths: specifically, reduced memory allocation and freeing in the expression evaluation engine, and improvements to the B-tree index code path that reduce CPU cache misses during lookups. For OLTP workloads running thousands of short SELECT, INSERT, and UPDATE queries per second, these improvements translate directly to higher queries-per-second capacity without any schema or query changes.

The gains are most pronounced on workloads that execute the same parameterised queries repeatedly — which describes most production application database traffic. In published benchmarks using pgbench on commodity hardware, PostgreSQL 18 achieves 22–28% higher TPS than PostgreSQL 17 at equivalent concurrency levels. The improvement is smaller for long-running analytical queries where the per-query overhead is a small fraction of total execution time.

-- Benchmark your workload with pgbench before and after upgrade
-- Run on a clone of production with the same data volume

-- Simple TPS benchmark (read-write mix, 10 connections, 60 seconds)
pgbench -h localhost -U postgres -d mydb \
  -c 10 -j 4 -T 60 \
  --progress=5

-- Custom workload benchmark using your actual query patterns
pgbench -h localhost -U postgres -d mydb \
  -c 20 -j 8 -T 120 \
  -f my_workload.sql

-- Record results for comparison between PG17 and PG18 clones
-- Expected improvement: 20-30% TPS increase for short-query OLTP workloads

Vacuum Improvements: Less Background I/O, Faster Table Maintenance

PostgreSQL's MVCC model requires periodic vacuum operations to reclaim space from dead tuples (rows that have been updated or deleted). In high-write environments, autovacuum can consume significant I/O bandwidth and sometimes interfere with application query performance. PostgreSQL 18 introduces two vacuum improvements that reduce this overhead.

First, vacuuming of indexes is now more selective — the system tracks which index pages contain dead tuple references and only scans those pages, rather than scanning the entire index. For large indexes on high-write tables, this can reduce vacuum I/O by 40–60%. Second, the vacuum progress reporting in pg_stat_progress_vacuum now includes the count of dead tuple references removed from each index, giving DBAs accurate visibility into where vacuum time is actually being spent.

For teams that have tuned autovacuum aggressively (low autovacuum_vacuum_scale_factor, high autovacuum_vacuum_cost_limit) to keep pace with high write rates, PostgreSQL 18's improved vacuum efficiency means the same work gets done with less I/O impact on concurrent queries.

-- Monitor vacuum progress and efficiency in PostgreSQL 18
SELECT
  schemaname,
  relname,
  phase,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  num_dead_tuples,
  num_index_cleanup_passes
FROM pg_stat_progress_vacuum;

-- Check autovacuum tuning for high-write tables
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Aggressive autovacuum for high-write tables (set per-table)
ALTER TABLE high_write_table SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum when 1% of rows are dead
  autovacuum_vacuum_cost_limit = 400       -- allow more I/O bandwidth for vacuum
);

Related: the Database FinOps guide, why your AWS RDS bill might be high

Query Planner Improvements: Better Statistics and Plan Stability

PostgreSQL 18 extends extended statistics (introduced in PostgreSQL 10) to cover more expression types and correlation patterns that the planner previously could not capture. The most impactful addition is support for statistics on expressions used in partial indexes — previously, the planner had to estimate selectivity for partial index predicates using only the base column statistics, often producing significantly wrong row count estimates for queries on large tables with selective partial indexes. PostgreSQL 18 also improves the planner's handling of parameterised nested loop joins with large inner relations, reducing cases where the planner chose a nested loop strategy that performs well on the query cache but catastrophically on a cold start.

For teams that have added extensive CREATE STATISTICS definitions to work around planner estimation errors, PostgreSQL 18 may allow removing some of those manually-added statistics — the planner captures more of those correlations automatically.

-- PostgreSQL 18: view extended statistics including expression statistics
SELECT
  stxname,
  stxkeys,
  stxkind,
  stxexprs,  -- new in PG18: expressions covered by the statistic
  stxrelid::regclass AS table_name
FROM pg_statistic_ext
ORDER BY stxrelid;

-- Check if a partial index's predicate has statistics in PG18
-- This helps the planner estimate selectivity accurately
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

-- PG18 automatically collects statistics on the status = 'pending' predicate
-- Verify with pg_statistic_ext after ANALYZE
ANALYZE orders;
SELECT * FROM pg_statistic_ext WHERE stxrelid = 'orders'::regclass;

Logical Replication Reliability: Key for Zero-Downtime Migrations

PostgreSQL 18 includes a set of logical replication improvements that directly affect the reliability of zero-downtime major version upgrades and cross-cluster migrations. The most important change is improved handling of large transactions in the logical replication stream. In PostgreSQL 17 and earlier, very large transactions (bulk inserts or updates affecting millions of rows) could cause logical replication lag to spike significantly, sometimes causing replica slots to fall behind far enough to require a full resync.

PostgreSQL 18 introduces streaming of large in-progress transactions to replicas before commit, significantly reducing the lag spikes caused by large transaction commits. This matters for teams planning a PostgreSQL 18 upgrade via logical replication: the upgrade migration itself creates large transactions (schema changes, initial data sync), and improved large-transaction handling makes the migration more reliable. Two-phase commit support in logical replication also improves in PostgreSQL 18, allowing distributed transactions to be replicated with full ACID guarantees.

-- PostgreSQL 18: monitor logical replication lag with improved metrics
SELECT
  slot_name,
  plugin,
  active,
  restart_lsn,
  confirmed_flush_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- Create a logical replication slot for a zero-downtime upgrade
-- (on the source PostgreSQL 17 instance)
SELECT pg_create_logical_replication_slot('pg18_upgrade_slot', 'pgoutput');

-- On the PostgreSQL 18 target: create subscription
CREATE SUBSCRIPTION pg18_sub
  CONNECTION 'host=pg17-source dbname=myapp user=replicator'
  PUBLICATION pub_all_tables
  WITH (slot_name = 'pg18_upgrade_slot', streaming = on);  -- 'streaming = on' is PG18

-- Monitor replication lag during migration
SELECT
  subname,
  received_lsn,
  latest_end_lsn,
  pg_wal_lsn_diff(latest_end_lsn, received_lsn) AS bytes_behind
FROM pg_stat_subscription;

Related: our database migration service

pg_stat_io and Enhanced I/O Monitoring

PostgreSQL 18 expands the pg_stat_io view introduced in PostgreSQL 16 with additional I/O context types and more granular reporting. The expanded view now covers backend reads and writes separated by context (relation, temp file, WAL, SLRU), making it possible to identify precisely whether I/O overhead is coming from relation reads (working set larger than shared_buffers), WAL writes (high-write workload hitting checkpoint I/O limits), or temporary file usage (sorts and hash joins spilling to disk). This level of I/O visibility was previously only available through OS-level profiling tools like iostat and iotop, which cannot attribute I/O to specific PostgreSQL operations.

For teams tuning shared_buffers, work_mem, and checkpoint parameters on RDS or self-managed instances, pg_stat_io in PostgreSQL 18 provides the direct evidence needed to justify configuration changes rather than relying on indirect signals from query execution plans.

-- PostgreSQL 18: expanded pg_stat_io
SELECT
  backend_type,
  object,
  context,
  reads,
  read_time,
  writes,
  write_time,
  extends,
  hits,
  evictions,
  reuses
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads + writes DESC;

-- Interpret I/O by context:
-- context = 'normal' + object = 'relation': heap/index reads (check shared_buffers if high)
-- context = 'normal' + object = 'wal': WAL write volume (tune wal_buffers if high)
-- object = 'temp relation': sort/hash spills (increase work_mem if high)

-- Reset stats for a clean measurement window
SELECT pg_stat_reset_shared('io');

PostgreSQL 18 and pgvector: AI Workload Improvements

PostgreSQL 18 includes improvements to the HNSW index implementation in pgvector (via the pgvector extension update that ships alongside it) that reduce memory usage during index builds and improve recall stability under concurrent writes. 8 (which requires PostgreSQL 14+) already delivers approximately 471 queries per second at 99% recall for 1M 1536-dimensional vectors. The combination of PostgreSQL 18's short-query throughput gains and the updated pgvector HNSW implementation produces measurable throughput improvements for RAG pipeline workloads where embedding lookups execute as short queries.

Additionally, PostgreSQL 18's improved planner statistics capture better estimates for hybrid queries that combine vector similarity searches (ORDER BY embedding <-> $1) with standard SQL predicates — a common pattern in production RAG systems that filter by user_id, document_type, or date range alongside the vector similarity. Better planner estimates mean the system is more likely to choose the correct index strategy for these hybrid queries without manual hinting.

-- pgvector: HNSW index for semantic search (works in PG18 with improved performance)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Hybrid query: vector similarity + SQL filter (PG18 planner handles this better)
SELECT
  id,
  title,
  embedding <-> $1 AS distance
FROM documents
WHERE
  user_id = $2
  AND document_type = 'report'
  AND created_at > NOW() - INTERVAL '90 days'
ORDER BY embedding <-> $1
LIMIT 10;

-- Monitor HNSW index effectiveness
SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%hnsw%';

Related: pgvector vs Pinecone

How to Test PostgreSQL 18 Safely Before Upgrading Production

PostgreSQL 18 is in beta as of May 2026. The general availability release is expected in Q3 2026. The correct approach is to begin testing now against a clone of your production data, so that when the stable release lands, you have already validated your application compatibility and benchmarked the performance improvement for your specific workload.

The testing process: restore a recent production snapshot to a PostgreSQL 18 instance (using pg_upgrade or a logical dump-restore), run your application test suite against it, execute your most critical queries with EXPLAIN ANALYZE to verify the planner makes equivalent or better choices, and run a pgbench benchmark against your actual schema and query patterns to measure the throughput improvement. The most common compatibility issues on the path to PostgreSQL 18 are: deprecated functions or syntax removed between your current version and 18, changed default parameter values (check the release notes), and extension compatibility — verify every extension you use has a PostgreSQL 18-compatible version before committing to the upgrade timeline.

# Create a PostgreSQL 18 test instance from a production snapshot

# 1. Dump production database (PostgreSQL 17)
pg_dump -h prod-db -U postgres -d myapp -Fc -f myapp_prod.dump

# 2. Restore to PostgreSQL 18 test instance
pg_restore -h pg18-test -U postgres -d myapp myapp_prod.dump

# 3. Run pg_upgrade compatibility check (dry run, no changes)
pg_upgrade \
  -b /usr/lib/postgresql/17/bin \
  -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/main \
  -D /var/lib/postgresql/18/main \
  --check  # dry run only

# 4. Verify extension compatibility
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

# 5. Run workload benchmark against PG18 test instance
pgbench -h pg18-test -d myapp -c 20 -j 8 -T 300 -f production_workload.sql