PostgreSQL Performance Tuning (2026): What Matters First in Postgres 18

Most Postgres performance tickets I have looked at over the years were not tuning problems. They were one query doing a sequential scan of a table that grew a hundred times since someone wrote it, or an autovacuum that could not keep up with a hot table, sitting behind a postgresql.conf that somebody had already "tuned" by copying a blog post. This post explains PostgreSQL performance tuning in the order that pays off on Postgres 18: what each of the famous settings (shared_buffers, work_mem, effective_cache_size, autovacuum) actually controls, which index type fits which query, what the new asynchronous I/O does and does not change, why max_connections is not a throughput knob, and what to leave alone. Every default below comes from the PostgreSQL 18 documentation, and the current minor release is 18.6, released on 13 August 2026 according to the project's versions feed.

What should you tune first in PostgreSQL?

Queries and indexes, by a wide margin. A missing index turns a 0.1 ms lookup into a scan of every page in the table, and no memory setting closes a gap of that size. In the companion guide, How to Find Slow Queries in Postgres with pg_stat_statements and EXPLAIN ANALYZE, the same LIMIT 20 query on a two-million-row table went from 62.5 ms with a sequential scan to 0.098 ms with a composite index. That is more than 600x from one CREATE INDEX.

So the order I work in is:

1. Find the queries that consume the most total time (pg_stat_statements), read their plans, and fix the plan with an index, a rewrite or fresh statistics.
2. Set the memory parameters to sane values for the machine, once.
3. Make sure autovacuum keeps up with the tables that change most.
4. Cap and pool connections.
5. Only then look at I/O settings, checkpoints and the rest.

Steps two to four are mostly a one-off exercise per server size. Step one never ends, because the application keeps changing. That is also why a database needs the same discipline as the rest of the platform: query stats collected continuously, plans reviewed when they change, and configuration in version control rather than in somebody's memory.

shared_buffers and effective_cache_size: how much memory to give Postgres

shared_buffers is the shared page cache that every backend reads through. The PostgreSQL 18 resource docs give the default as typically 128MB and say that on a dedicated server with 1GB or more of RAM a reasonable starting value is 25 percent of memory. They also say more than 40 percent is unlikely to work better, because Postgres relies on the operating system's page cache too. Data you read is often cached twice, once in shared buffers and once in the kernel, so a bigger shared_buffers is not free memory.

effective_cache_size is the setting people most often misunderstand. It allocates nothing. The query planning docs are explicit that it is used only for estimation: it tells the planner how much of the data is likely to be cached between shared buffers and the kernel, and a higher value makes index scans look cheaper. The default is 4GB. On a dedicated 64GB machine, 4GB is a significant underestimate, and setting it to roughly half to three quarters of RAM is a common convention.

random_page_cost (default 4.0) sits next to it. The docs explain that 4.0 already assumes most random reads hit cache, and that a lower value makes the planner prefer index scans. On SSD or NVMe storage, many operators drop it to around 1.1. That number is community practice rather than something the docs prescribe, so change it, then check that the plans for your top queries actually got better.

work_mem: the setting that multiplies

work_mem (default 4MB) is the memory a single sort or hash operation can use before spilling to temporary files. The trap is in the word "single". The docs warn that a complex query can run several sort and hash operations at once, each with its own allowance, and that many sessions can do so concurrently, so total memory can be many times work_mem. Hash operations get more: hash_mem_multiplier defaults to 2.0, which means a hash join or hash aggregate can use twice work_mem.

That is why I never set a large global work_mem. A value like 1GB looks harmless until fifty connections each run a report with three hash joins. The safer pattern is a modest global value and a higher one where it is needed:

• per role, for a reporting user: ALTER ROLE reporting SET work_mem = '256MB';
• per transaction, around a known heavy query: SET LOCAL work_mem = '512MB';

You can tell whether work_mem is too small for a query from its plan. A sort that says Sort Method: external merge Disk: ... spilled; one that says quicksort or top-N heapsort with a memory figure did not. temp_blks_written in pg_stat_statements shows the same thing across all queries.

maintenance_work_mem (default 64MB) is the equivalent for VACUUM, CREATE INDEX and adding foreign keys. Raising it to 1GB or more speeds up index builds noticeably. Be aware that each autovacuum worker can use up to that amount (or autovacuum_work_mem, if you set that separately), so multiply by autovacuum_max_workers when you budget memory.

Autovacuum tuning in Postgres 18

Postgres never updates a row in place. An UPDATE writes a new version and leaves the old one as a dead tuple, and VACUUM reclaims the space later. Autovacuum is the background process that does this, and also runs ANALYZE so the planner's statistics stay current. When it falls behind, tables bloat, index scans touch more pages, and plans go wrong because statistics are stale.

The trigger rule from the autovacuum docs is: vacuum a table when its dead tuples exceed autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the table's row count. Analyze runs at 10 percent change (autovacuum_analyze_scale_factor 0.1). For a 1,000-row table that is fine. For a 2-billion-row table it means waiting for 400 million dead rows before anything happens.

Postgres 18 addresses that directly with autovacuum_vacuum_max_threshold, which defaults to 100,000,000 and caps the computed threshold, so very large tables get vacuumed earlier without per-table settings. The 18 release notes also add autovacuum_worker_slots (default 16, set at server start), which lets you change autovacuum_max_workers (default 3) with a reload instead of a restart. And the release announcement says regular vacuums now freeze more pages proactively, which reduces how often an expensive aggressive vacuum is needed.

What I still do by hand is give the hottest tables their own settings:

-- Vacuum a hot, large table at 1% dead rows instead of 20%,
-- and let its vacuum work faster than the global cost limit.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit    = 1000
);

-- Check which tables are falling behind (dead tuples and last run).
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

The default cost settings are deliberately gentle: autovacuum_vacuum_cost_limit of -1 inherits vacuum_cost_limit (200) with a 2 ms delay. On modern storage that throttle is often the reason autovacuum cannot keep up, and raising the limit is usually safer than adding workers, because workers share the same overall cost budget.

Which Postgres index type should you use?

B-tree is the default and the right answer most of the time: equality, ranges, sorting, LIKE 'prefix%' with the right operator class. The others solve specific problems. The index types chapter lists six built-in methods; these are the four that come up in practice.

Index type	Good for	Watch out for	Source
B-tree	=, <, >, BETWEEN, ORDER BY, unique constraints. Multicolumn indexes can now be used more efficiently without an = condition on the leading column, via skip scan in 18	Column order still matters; skip scan helps when the leading column has few distinct values	Index types, 18 release
GIN	jsonb containment (@>), arrays, full-text search (tsvector), trigram search with pg_trgm	Slower writes; pending-list behaviour on heavy insert loads	GIN docs
BRIN	Very large, append-mostly tables where a column correlates with physical order, such as created_at on an event log	Useless when values are scattered; summarises ranges of pages rather than rows	BRIN docs
GiST	Geometric data, ranges, exclusion constraints, PostGIS	Lossy for some types, so rechecks show up in plans	Index types

BRIN deserves more use than it gets. Its size depends on pages_per_range rather than on the row count, so on a billion-row log table it can be orders of magnitude smaller than the equivalent B-tree while still letting a time-range query skip most of the table. For vector search with pgvector, which brings its own HNSW and IVFFlat index types, the tuning is different enough that I wrote a separate guide.

The other index rule is restraint. Every index costs write throughput and vacuum work, and an index the planner never chooses is pure overhead. pg_stat_user_indexes.idx_scan tells you which ones have not been used since the statistics were last reset.

Async I/O in Postgres 18: what io_method changes

The headline feature of Postgres 18 is a new asynchronous I/O subsystem. Before 18, a backend asked the kernel for one page and waited, relying on operating-system readahead to hide latency. Now it can have several reads in flight. The release announcement says benchmarks showed gains of up to 3x in certain scenarios when reading from storage, and that in 18 the covered operations are sequential scans, bitmap heap scans and vacuum.

Three settings control it, per the resource docs:

• io_method: worker (the default, a pool of I/O worker processes), io_uring (Linux only, and only if Postgres was built with liburing support) or sync (the pre-18 behaviour). It requires a restart.
• io_workers: the size of that worker pool, default 3.
• effective_io_concurrency: how many concurrent I/Os Postgres should aim for, now defaulting to 16, with a warning in the docs that unnecessarily high values raise latency for everyone.

The practical expectations are modest. Async I/O helps workloads that are waiting on storage reads: large scans and vacuum on cloud block storage with high per-request latency are the obvious winners. It does nothing for a working set that already fits in RAM, and it does not change index scans in 18. I leave io_method at worker, try io_uring on Linux hosts where the package supports it, and measure scan-heavy queries before and after rather than assuming the 3x.

Connection overhead: why max_connections is not a throughput knob

Postgres runs one operating-system process per client connection. The default max_connections is 100, and the instinct when the application hits "too many clients" is to raise it to 1,000. That usually makes things worse. Each extra active connection competes for the same CPUs and the same lock and snapshot structures, and every one of them can claim work_mem per operation.

Andres Freund's analysis of connection scalability is the best primary write-up on this. He found that per-connection memory was smaller than ps suggests and was not the main limit; the cost of computing snapshots across many connections was, and Postgres 14 improved it. Active concurrency far beyond your core count still just adds queueing inside the database.

The fix is a pooler in front, so a few thousand application connections share a few dozen server connections. PgBouncer in transaction mode is the standard choice, and the trade-offs between its modes are covered in the connection pooling post. If you are already seeing the error, the too-many-clients guide walks through it.

A starting postgresql.conf for Postgres 18

This is where I would start on a dedicated 16 vCPU, 64GB Linux server with NVMe storage, behind PgBouncer. Treat each value as a hypothesis to measure, not a final answer.

# postgresql.conf starting point, PostgreSQL 18.6
# Dedicated host: 16 vCPU, 64 GB RAM, NVMe, PgBouncer in front.

# Memory
shared_buffers = 16GB              # ~25% of RAM, per the docs' guidance
effective_cache_size = 44GB        # planner estimate only, allocates nothing
work_mem = 32MB                    # per sort/hash node; raise per role, not globally
hash_mem_multiplier = 2.0          # default; hash nodes get 2x work_mem
maintenance_work_mem = 2GB         # faster CREATE INDEX and VACUUM

# Connections
max_connections = 200              # pooler handles the fan-in

# Planner, SSD storage
random_page_cost = 1.1             # community practice for SSD; verify plans after

# Async I/O (new in 18)
io_method = worker                 # default; io_uring only if built with liburing
io_workers = 3                     # default
effective_io_concurrency = 16      # default in 18

# Autovacuum
autovacuum_worker_slots = 16       # default; lets max_workers change on reload
autovacuum_max_workers = 5
autovacuum_vacuum_cost_limit = 1000
autovacuum_vacuum_max_threshold = 100000000   # default, new in 18

# Observability
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
track_io_timing = on               # I/O time in EXPLAIN and pg_stat_statements
log_min_duration_statement = 500ms

Postgres 19 is close. RC 1 was announced on 15 October 2026 with general availability planned for 19 November 2026, bringing REPACK (CONCURRENTLY), a pg_plan_advice extension for steering planner decisions, and parallel autovacuum. None of that changes the order above. Separately, the versions feed lists 12 November 2026 as the end of life for Postgres 14, so if you are still on 14 the upgrade is the most valuable tuning you can do this month, and since 18 pg_upgrade keeps planner statistics (extended statistics excepted, per the release notes), so the post-upgrade slowdown while ANALYZE catches up is mostly gone.

FAQ

What is a good value for shared_buffers?

The PostgreSQL 18 documentation suggests 25 percent of RAM as a starting point on a dedicated server with at least 1GB of memory, and says going beyond 40 percent is unlikely to help. The default is 128MB, which is far too small for any production server. Changing it requires a restart.

Should I increase work_mem?

Only where the plans show spills to disk, and preferably per role or per transaction. The value applies per sort or hash operation, not per query or per connection, so a large global value can multiply into an out-of-memory event under concurrency. Look for external merge in plans or nonzero temp_blks_written in pg_stat_statements.

Does Postgres 18 async I/O make everything faster?

No. In 18 it covers sequential scans, bitmap heap scans and vacuum, and it helps when those are waiting on storage. Cached workloads and index lookups see little difference. The project quotes up to 3x in certain benchmarks, which is a ceiling, not an expectation.

How many connections should Postgres have?

Fewer than most people configure. Active concurrency much beyond a small multiple of CPU cores adds contention without adding throughput. Keep max_connections in the low hundreds and put a pooler such as PgBouncer in front of applications that open many connections.

This quarter, on any Postgres I am responsible for, I would turn on pg_stat_statements and track_io_timing everywhere, fix the top five queries by total time, set per-table autovacuum on the three busiest tables, and plan the move from 14 or 16 to 18.6 rather than waiting for 19.0 to ship and settle. The memory settings get one careful pass and then stay in Git. The query work is the part that never stops, and the find-slow-queries guide is the routine for it.

Related: Should You Run Databases on Kubernetes in 2026? 66% Do, RDS Costs 1.7x, How to Run Zero-Downtime Postgres Migrations: Step by Step (2026) and How to Troubleshoot Linux Performance in 60 Seconds (2026 Checklist).