My VPS ran an autovacuum job for six straight hours last winter on a table with maybe four million rows, which is not a lot of rows, and I spent that evening convinced I’d found some exotic Postgres bug. I hadn’t. I’d just never tuned autovacuum past its defaults, because the defaults work fine right up until they don’t, and “fine” had quietly become “grinding through a table lock during my only maintenance window.”
That’s the thing about Postgres performance tuning parameters: almost nobody touches them until something hurts. I want to walk through the handful I actually adjust on every server I run, plus what caught my eye in PostgreSQL 19 Beta 4, released this week, because a couple of the changes land directly on the knobs I care about.
Autovacuum is the one that actually bites people
The default autovacuum_vacuum_cost_delay throttles vacuum aggressively so it doesn’t compete with your live traffic for I/O. That’s a reasonable default for a shared host you don’t control. It’s a bad default for a dedicated VPS where you’re the only tenant and dead tuples are piling up faster than the throttled vacuum can clear them.
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_naptime = '15s';
SELECT pg_reload_conf();
Raising the cost limit and dropping the delay lets vacuum work through more pages per cycle, which matters a lot once a table has any real write volume. autovacuum_naptime controls how often the launcher checks whether a table needs vacuuming at all; the default 60 seconds is fine for most workloads, but a table that gets bursts of updates benefits from checking more often.
PostgreSQL 19 Beta 4 actually touches this directly. The release notes mention “several fixes to the new autovacuum scoring system,” which replaced the older threshold-based trigger with something that weighs tables by how urgently they need attention instead of a flat percentage. I haven’t run it in production yet, betas don’t belong there, but I’ve been testing it against a staging clone of the same table that gave me that six-hour vacuum, and the scoring system picked it up for vacuum noticeably earlier than the old threshold would have.
work_mem, and the query that taught me to stop guessing
work_mem sets how much memory a single sort or hash operation gets before it spills to disk. The default, 4MB, is conservative enough to be safe on a shared box and too small for almost any real analytical query.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC;
If that plan shows Sort Method: external merge Disk: 84000kB, your sort spilled to disk instead of staying in memory, and that’s your signal to raise work_mem, not to add an index blindly. I used to jump straight to indexing every slow query. Sometimes the query is fine and the sort just needed more headroom.
SET work_mem = '64MB'; -- per session, test first
ALTER SYSTEM SET work_mem = '32MB'; -- global, be careful
The global setting multiplies per connection and per sort operation within a query, so a server running fifty connections each doing a two-way hash join can burn through memory fast if you set this too high. I test with a session-level SET against a copy of production data before I touch the global value.
shared_buffers is not “give Postgres all your RAM”
The advice to set shared_buffers to 25% of system RAM gets repeated everywhere, and it’s a reasonable starting point, but I’ve seen people push it to 60 or 70% assuming more is strictly better. It isn’t. Postgres relies on the operating system’s page cache as a second layer, and a shared_buffers value that’s too aggressive starves that cache, which can make things slower, not faster, especially on a box that’s also running your application.
ALTER SYSTEM SET shared_buffers = '2GB'; -- on an 8GB box
ALTER SYSTEM SET effective_cache_size = '6GB'; -- OS cache estimate
effective_cache_size doesn’t allocate anything. It’s a hint to the query planner about how much data is likely already cached somewhere, and it affects whether the planner favors an index scan over a sequential scan. I set it to roughly 75% of total RAM and haven’t had a reason to touch it since.
REPACK, the new command that replaces a maintenance script I’ve run for years
This is the change from PG19 that actually excited me. Reclaiming bloated table space has meant VACUUM FULL, which takes an exclusive lock and blocks reads and writes for the duration, or pg_repack, a well-maintained extension that does it online but still requires installing and trusting a third-party tool. PostgreSQL 19 adds REPACK as a first-party command.
REPACK TABLE orders;
Beta 4’s changelog lists several fixes to REPACK specifically: crashes on invalid indexes, incorrect behavior with materialized views, and permission and error-reporting corrections. That tells me the feature is getting real testing pressure before GA, which is exactly what I want to see before I trust a command that rewrites a whole table. I’m not running it against anything that matters yet, but it’s the first thing I’m testing once 19 goes stable, because it would let me delete a pg_repack cron job I’ve maintained across three different servers.
checkpoint_completion_target, the setting nobody mentions until a write spike hits
Postgres writes dirty pages to disk during a checkpoint, and by default it tries to finish that work quickly, which can cause a burst of I/O contention right when your application is also trying to write. checkpoint_completion_target tells Postgres how much of the interval between checkpoints it should spread that writing across.
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '4GB';
Raising it toward 0.9 spreads the write load across nearly the whole checkpoint interval instead of compressing it into a spike near the end. I found this one the hard way, watching iostat during a nightly batch import and seeing disk write throughput spike in a pattern that lined up exactly with checkpoint_timeout, which defaults to five minutes. max_wal_size works alongside it; a bigger WAL budget means fewer checkpoints overall, which is the other lever if checkpoints themselves, not their spread, are the problem.
I test changes like this with pg_stat_bgwriter before and after, specifically the ratio of buffers_checkpoint to buffers_clean. A checkpoint doing most of the writing, instead of the background writer handling it gradually, is the signal that this setting needs attention on a given server.
max_connections is usually the wrong lever
When an app starts throwing “too many connections” errors, the instinct is to raise max_connections. I did this on a client project two years ago, doubled it from 100 to 200, and the server got slower, not faster. Each connection carries its own memory overhead and its own backend process, and Postgres doesn’t scale linearly past a few hundred active connections on modest hardware. The actual fix was a connection pooler.
# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
PgBouncer in transaction mode lets a thousand application-side connections share twenty real Postgres backends, handing each one back to the pool the moment its transaction commits. That’s a very different problem from raising max_connections, and it’s the one that actually fixes the error message instead of just delaying it. I run PgBouncer on every Postgres box I manage now, even the small ones, because retrofitting it under load is a worse afternoon than setting it up in advance.
The features that got pulled, and why that’s a good sign
Beta 4 also reverted a handful of things that were planned for 19: SQL/PGQ property graph query support, online toggling of data checksums, and the ALTER TABLE ... MERGE PARTITIONS and SPLIT PARTITIONS commands. Watching a release cut scope this late used to worry me, it feels like something going wrong. Reading through the Postgres project’s own reasoning changed my mind: they’re explicit that reliability comes before the release calendar, and features that aren’t ready get pushed to a later major version instead of shipping half-finished. That’s a genuinely rare property in software release culture, and it’s a big part of why I still run Postgres on a VPS instead of reaching for a managed service with a bigger feature list and less of that discipline behind it.
What to actually do this week
Run EXPLAIN (ANALYZE, BUFFERS) against your three slowest queries and check for external merge Disk in the sort output before you touch work_mem. Then check pg_stat_user_tables for n_dead_tup on your largest tables; if that number is climbing faster than autovacuum is clearing it, that’s your autovacuum_vacuum_cost_delay problem before it becomes a six-hour vacuum on a Tuesday night.
I’ve written before about a Postgres foreign key mistake that made this exact vacuum problem worse than it needed to be, worth a read if you’re touching autovacuum settings anyway. The full 19 release notes are at postgresql.org/docs/19/release-19.html if you want the complete changelog, and more of the infrastructure work behind this blog is documented on my portfolio.
