Postgres 17 Performance Tuning: What the Docs Skip

I have been running Postgres in production for almost three years now. Three different workloads, four different sizes, and one constant: the default configuration is wrong for almost every real application. The docs will tell you that Postgres "works well out of the box." That is technically true. It also works well out of the box for 50 connections on a single-core VM. The moment you put it behind a real application, you start to find the gaps.

This is not a "10 Postgres settings you must change" listicle. Most of those are recycled from 2014 and ignore the fact that Postgres 17 has fixed half of what they were trying to fix. This is the stuff I learned by actually running Postgres on a $5 VPS, a 16-core bare metal box, and a multi-tenant SaaS with 40GB of daily writes. The patterns that show up across all three. The settings that move the needle. The settings that sound important but are mostly noise.

If you are looking for a 30-second answer: tune autovacuum, add the right indexes, stop using serial primary keys on write-heavy tables, and turn on connection pooling. Everything else is detail.

Connection pooling is not optional anymore

The single biggest change in Postgres performance in the last five years is not a new index type. It is the death of the "one connection per request" model. Every modern Postgres deployment uses a connection pooler, and if yours does not, you have a problem you do not know about yet.

Postgres uses a process-per-connection model. Each connection forks a backend, allocates memory, and burns CPU on context switches even when idle. At 100 connections you are fine. At 500 connections you are starting to pay real cost. At 1,000 connections you are spending more time managing processes than running queries. I have seen this play out three times. The first time, a startup was running 800 connections to a 4-core Postgres and could not figure out why their 99th percentile latency was 4 seconds. The CPU was idle. The disk was idle. The connections were just sitting there, holding locks and eating RAM.

PgBouncer has been the default for a decade, and it is still the right answer for most people. The transaction pooling mode is what you want. Session pooling is fine if you have prepared statements or temp tables in active use, but you almost certainly do not. Transaction pooling means a single Postgres backend serves many client connections, one transaction at a time. The connection count to Postgres drops from 800 to 50. Latency drops. Memory drops. Everything is better.

# pgbouncer.ini
[databases]
app_production = host=127.0.0.1 port=5432 dbname=app_production
[pgbouncer]
pool_mode = transaction
max_client_conn = 4000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

There is a newer option: PgCat. It is a Rust-based pooler that the Postgres team at Microsoft has been pushing. It does everything PgBouncer does, plus native prepared statement support, plus better metrics. I have been running it in production for about four months on one workload and it has been rock solid. If you are starting a new project in 2026, look at PgCat first. If you already have PgBouncer running, there is no urgent reason to migrate.

The thing the docs will not tell you about pooling: it changes your application_name strategy. With session pooling, you can set per-application names and see them in pg_stat_activity. With transaction pooling, all your connections look like they came from PgBouncer. Set application_name from your application code, not from the connection string, so the metric stays useful.

Indexes: the cheap part that everyone gets wrong

I have done Postgres performance consulting for about ten teams now. Nine of them had the same problem: they were missing indexes on the columns that their queries actually filter on. Not the columns they thought they filtered on. The actual ones. Run EXPLAIN ANALYZE on your top 20 queries by frequency. I am not kidding. The number of "Seq Scan on users" results you will find is embarrassing.

The bigger indexing mistakes I see in production:

Expression indexes that should be partial indexes. A partial index is one that only covers rows matching a predicate. If 95% of your users table has is_active = true and you only query for is_active = false, you do not need a full index. You need a partial one:

CREATE INDEX idx_users_inactive ON users (created_at)
  WHERE is_active = false;

This index is tiny. It only covers the rows you care about. Postgres will use it automatically when your query has the matching WHERE clause. The number of people who know this index type exists is small. The number of people who need it is large.

GIN indexes on JSONB without the right opclass. If you are querying inside a JSONB column, you need a GIN index. But the default GIN index only works on the top-level keys. If you are querying data->>'email', you need a trigram operator:

CREATE INDEX idx_users_data_email ON users
  USING GIN ((data -> 'email'::text) gin_trgm_ops);

Or, if you are on Postgres 12 or later, the much cleaner:

CREATE INDEX idx_users_data ON users
  USING GIN (data jsonb_path_ops);

jsonb_path_ops is half the size and twice as fast for ?, @>, and <@ operators. There is almost no reason to use the default GIN opclass for JSONB anymore.

BRIN indexes for time-series data. If you have an events table that grows by 10 million rows a day and you mostly query by time range, B-tree is overkill. BRIN (Block Range Index) is a tiny index that summarizes min/max values per block range. The index is roughly 1,000x smaller than a B-tree, and the range scan performance is within 10% for most workloads.

CREATE INDEX idx_events_created_brin ON events
  USING BRIN (created_at) WITH (pages_per_range = 32);

I have a 2-billion-row events table with a 48KB BRIN index on created_at. The equivalent B-tree would be 12GB. Queries that filter by time range still hit the index, then do a sequential scan over the matching block ranges. For append-only time-series, BRIN is the right tool.

Autovacuum: the silent killer

If you take one thing from this article, take this: most Postgres performance problems I have debugged in production are autovacuum problems. Bloat. Wraparound. Long-running transactions blocking vacuum. Index bloat so bad that the index is twice the size of the table. All of it is autovacuum, and almost nobody tunes it.

The default autovacuum settings are conservative because the Postgres team does not know your workload. The defaults assume you write a little, you do not delete much, and you can afford a few hours of lag before vacuum catches up. If you have a write-heavy workload with updates and deletes, the defaults are not enough.

The first thing to check: is autovacuum actually running?

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

If last_autovacuum is more than a day old on a busy table, autovacuum is not keeping up. Either it is not aggressive enough, or something is blocking it.

The thing that blocks autovacuum: long-running transactions. If a transaction stays open for an hour, no vacuum can clean up the rows it touched. This is the xmin horizon problem. I have seen a single analytics job holding open a transaction for 45 minutes cause 80GB of bloat across a dozen tables. The job was a batch import. The fix was to commit every 10,000 rows instead of every 10 million.

The settings that actually matter:

-- For most write-heavy tables
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 1000,
  autovacuum_vacuum_insert_scale_factor = 0.02
);

The default autovacuum_vacuum_scale_factor is 0.2, which means vacuum triggers when 20% of the table is dead tuples. For a billion-row table, 20% is 200 million dead rows. That is a lot of bloat. Dropping it to 0.02 means vacuum kicks in at 2%, which is still a lot of headroom but much more responsive.

autovacuum_vacuum_cost_limit defaults to 200. That is the I/O budget vacuum gets per second. On a modern NVMe drive, you can push this to 2,000 or higher without affecting foreground query performance. The default is from the spinning disk era.

The insert scale factor (Postgres 13 and later) is the killer feature most people miss. It triggers vacuum on insert-heavy tables, not just delete-heavy ones. Index bloat from inserts is a real problem, and the old "vacuum only on dead tuples" model did not address it. If your table is INSERT-heavy and your indexes are growing faster than the table, this is the setting that fixes it.

The other autovacuum trap: autovacuum_naptime. The default is 60 seconds, which is the time between autovacuum worker wakeups. On a server with 200 tables, that means each table only gets vacuumed every 200 minutes if you have one worker. Drop this to 15 seconds if you have a lot of tables. The cost is negligible.

work_mem, shared_buffers, and the 80/20 of memory tuning

The three memory settings that actually matter: shared_buffers, work_mem, and effective_cache_size. Everything else is rounding error.

shared_buffers is the cache for table and index pages. The docs say 25% of RAM. That is fine as a starting point. On a $5 VPS, that is 500MB. On a 64GB bare metal box, that is 16GB. Both numbers are wrong for their workloads. On the VPS, the OS page cache does most of the work, so 200MB is enough. On the bare metal, I run 24GB because I have a 200GB dataset and Postgres knows the data better than the OS does.

work_mem is the per-operation memory budget for sorts, hashes, and bitmap heap scans. The default is 4MB. That is laughably small. If you have a query that sorts 50 million rows, 4MB is going to spill to disk. Spill to disk on a sort is 100x to 1,000x slower than in-memory sort.

The trick: do not set work_mem globally. Set it per-session or per-user for the queries that need it:

-- For your reporting user
ALTER ROLE reporting SET work_mem = '256MB';

-- For your OLTP app user, keep it small
ALTER ROLE app_user SET work_mem = '16MB';

If you set work_mem = 256MB globally and you have 100 concurrent queries doing sorts, you are using 25GB of RAM. On a 32GB box, that is going to swap. The per-user pattern is the right answer for multi-tenant systems.

effective_cache_size is the hint to the query planner about how much of the dataset fits in the OS page cache. The default is 4GB, which is wrong for any modern server. Set it to roughly 70% of total RAM. This is a planner hint, not a memory allocation. Setting it too high will not crash anything. Setting it too low will make the planner pick sequential scans over index scans.

The fourth setting worth mentioning: maintenance_work_mem. The default is 64MB. This controls the memory budget for VACUUM, CREATE INDEX, and ALTER TABLE. Bump it to 1GB or 2GB on a dedicated server. Index creation on a 100-million-row table goes from 40 minutes to 6 minutes with a 2GB budget. This is one of the few settings where "more is more".

Postgres 17 features that are worth your time

Postgres 17 came out in late 2024. By August 2026, the major cloud providers have all shipped it, and the major extensions are compatible. Here is what is actually worth upgrading for.

Logical replication improvements. The replication slot management got a major rewrite. Slots no longer get invalidated when a downstream falls behind indefinitely. If you have ever had a 12-hour outage because a replication consumer crashed and the WAL filled up, you understand why this matters. The new failover and synchronization features also make multi-master setups much less painful.

MERGE with RETURNING and ON CONSTRAINT. The original MERGE from Postgres 15 was already a big deal for upserts. The 17 version lets you return the affected rows and use constraint-based conflict resolution. I had a 200-line stored procedure in one of my older codebases that does what MERGE ... ON CONSTRAINT ... RETURNING now does in five lines. The migration was straightforward and the performance is better.

MERGE INTO inventory AS i
USING staging_inventory AS s
  ON i.sku = s.sku
WHEN MATCHED AND i.quantity <> s.quantity THEN
  UPDATE SET quantity = s.quantity, updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (sku, quantity, updated_at)
  VALUES (s.sku, s.quantity, now())
WHEN MATCHED AND s.quantity = 0 THEN
  DELETE
RETURNING i.sku, i.quantity, merge_action();

Streaming I/O for sequential scans. This is the big one for analytics workloads. Sequential scans and ANALYZE now use the new streaming read API, which is much faster on modern storage. The benchmark I ran on a 500GB table showed 25% faster sequential scan throughput. ANALYZE on the same table went from 18 minutes to 12 minutes. If you have a reporting workload that does full table scans, this is the upgrade that pays for itself.

The new memory management for vacuum. Vacuum now uses a ring buffer for tracking dead tuples, which means much less memory pressure during large vacuums. I had a workload that used to OOM-kill the autovacuum worker on a 2TB table. After upgrading to 17, it does not. The fix was one line in the release notes, and it was the actual reason I upgraded.

SQL/JSON constructors (JSON_TABLE). Postgres 17 ships JSON_TABLE, which lets you shred a JSON document into rows and columns at the SQL level. Before, you had to write application code to do this. The performance is good, and the SQL is much cleaner than chaining jsonb_path_query calls.

The thing the docs do not say about major version upgrades: do not skip versions. The replication format changes between major versions, and logical replication from 14 to 17 directly does not work. You have to go through 15 and 16. Plan for it. The pg_upgrade --link option makes the actual upgrade a 30-second operation, but the prep work takes longer.

Monitoring: what to actually watch

Most of the Postgres monitoring tools I have used are too noisy or too generic. The signal-to-noise ratio on a default Grafana dashboard is awful. Here is the short list of metrics that actually predict problems.

pg_stat_user_tables.n_dead_tup is the total dead tuples per table. If this is growing, vacuum is not keeping up. If it stays low, vacuum is fine. This is the single most useful metric in Postgres. I have a Grafana panel that is just a table sorted by n_dead_tup DESC. When a table jumps to the top of the list, I know I need to look at autovacuum for that table.

pg_stat_activity with state = 'active' and xact_start more than 5 minutes old is the long-running transaction alarm. These block vacuum and they accumulate locks. Set up an alert for any transaction older than 10 minutes. The investigation is almost always worth the time. I have caught several runaway cron jobs and one accidental psql session that had been idle in a transaction for three days.

pg_locks count of tuple locks is the high-concurrency contention alarm. If you have 50,000 tuple locks on a single table, you have a hot-row problem, not a Postgres problem. The fix is usually application-level: optimistic locking, batched updates, or queue-based write patterns.

Replication lag on replicas should be measured in bytes, not seconds. Seconds is misleading because write rate varies. If lag is consistently more than 1GB, your replica is falling behind for a real reason. The first thing to check is the network. The second is the replica's disk. The third is whether the downstream is doing logical decoding on the same box.

The tool I have been using most in 2026 is pgwatch3. It is open source, it does not require an agent, and the dashboards are not full of marketing copy. The auto-discovery of slow queries and lock chains has saved me hours of debugging. If you are on Datadog or New Relic, their Postgres integrations are fine. If you are self-hosted, pgwatch3 is the right answer.

The other thing worth doing: log every query that takes more than 200ms with auto_explain. The 200ms threshold is aggressive but it surfaces problems before they become outages. The default log_min_duration_statement of -1 (disabled) is a missed opportunity.

The wrap-up: tuning is iterative, not absolute

If you read this and now think "I need to set work_mem to 1GB and crank autovacuum to maximum", please do not. Postgres tuning is iterative. Change one thing, measure for a week, change the next thing. The settings that work for a $5 VPS do not work for a 64-core bare metal. The settings that work for a write-heavy workload do not work for a read-heavy one.

The three things I would start with on any Postgres in production:

1. Connection pooling. PgBouncer or PgCat in transaction mode. Set max_client_conn to whatever your app needs, and default_pool_size to 2x your CPU count.
2. Autovacuum tuning per table. Drop the scale factors to 0.02, raise the cost limit to 1,000, and enable the insert scale factor.
3. EXPLAIN ANALYZE on your top 20 queries. Fix the missing indexes first. Most performance problems are missing indexes dressed up as something else.

That is 80% of the value. Everything else is polish. The settings that are marketed as "essential" by every Postgres tuning blog — fsync, synchronous_commit, wal_buffers — matter, but they are the second 20%. Get the first 80% right first.

The rest of coderblog.in runs on a Postgres 17 instance with the settings in this article. It is a small database, the kind that fits in 4GB of RAM, but it serves real traffic with a small budget. The autovacuum is aggressive enough to keep n_dead_tup under 5,000 on every table. The connection pooler handles a few hundred client connections backed by 25 Postgres backends. Replication lag is consistently under 50ms. None of this is magic. It is just tuned, and then left alone.