Stop blindly following PostgreSQL default configs: 5 settings to change today

Stop blindly following PostgreSQL default configs: five settings to change today if your database runs anything beyond a toy project. The defaults are safe starting points, not production targets, and they quietly assume modest hardware, conservative concurrency and a database small enough to fit comfortably in memory.

That assumption breaks hard in UK organisations running PostgreSQL for NHS Digital services, HMRC workloads, Monzo-like event pipelines or BBC-style media systems. A sensible config should match your hardware, data size and latency target, not resemble a file copied from a blog post written in 2014.

1. Tune shared_buffers before touching anything clever

shared_buffers is PostgreSQL's main cache for table and index pages. The famous rule of thumb is 25% of RAM, but treating that as a universal command is just cargo-culting with extra steps. On a dedicated database server, 25% can be sensible. On a container with a strict memory limit, or a host running PostgreSQL beside other memory-hungry services, it may be reckless.

Start by measuring rather than guessing. pg_buffercache shows cached PostgreSQL pages, while pg_stat_database exposes block reads and writes. A high hit ratio with plenty of free memory may justify a larger buffer; lots of eviction pressure or noisy neighbours may not. Keep enough RAM available for the operating system, connection processes and application-side caching.

SHOW shared_buffers;

SELECT datname,
       blks_hit,
       blks_read,
       ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

SELECT pg_size_pretty(sum(pg_total_relation_size(relid))) AS total_size,
       pg_size_pretty(sum(pg_total_relation_size(relid) /
                        current_setting('block_size')::bigint * pg_relation_size(relid) /
                        pg_relation_size(relid))) AS placeholder
FROM pg_catalog.pg_statio_user_tables;

The second query above is needlessly clever and deliberately shows why you should not paste mystery SQL. Use pgstattuple or your monitoring system instead. The point is simple: tune shared_buffers against observed cache behaviour and available memory, then change one setting at a time.

2. Set effective_cache_size to tell PostgreSQL what the OS may cache

effective_cache_size does not allocate memory. It tells the planner how much cached data it can reasonably expect from PostgreSQL's buffers plus the operating system. Get it wrong and the planner may choose a sequential scan when an index scan would be better, or vice versa.

Unlike shared_buffers, this setting can safely reflect a larger slice of RAM because it is only a planning estimate. A common starting point is around 50% of RAM on a dedicated host, but mixed application and database servers need a more conservative value. Measure with EXPLAIN (ANALYZE, BUFFERS) before and after changing it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, account_id, created_at
FROM payments
WHERE created_at >= '2026-01-01'
  AND account_id = 1042;

ALTER SYSTEM SET effective_cache_size = '8GB';
SELECT pg_reload_conf();

Do not turn every sequential scan into an index scan. Large analytical scans often beat a scattering index lookup, and PostgreSQL's planner should be allowed to choose based on realistic statistics. If query plans regress after the change, restore the previous value and inspect the statistics instead of raising random thresholds.

The safe deployment pattern

Put PostgreSQL settings in version-controlled infrastructure code, not a mysterious dashboard. For a managed instance such as AWS RDS, Azure Database for PostgreSQL or Google Cloud SQL, use the provider's parameter group where possible. For a self-hosted UK deployment, use postgresql.conf and an explicit configuration-management tool. Record the reason for each non-default value, because an unexplained setting will outlive the person who understands it.

3. Raise max_connections only when you have proved the limit is the problem

PostgreSQL connections are not free. Each active session can consume memory, and more connections increase contention, context switching and scheduler work. The default often looks generous for a small monolith, but teams then compensate with connection-pooling software that opens hundreds or thousands of sessions anyway.

Changing max_connections is not the same as increasing throughput. If application workers, background jobs and reporting tools all open pools, the first fix is to reduce unnecessary concurrency and size pools from the workload. Use pg_stat_activity to identify idle transactions, long-running sessions and connection states before deciding whether the server needs more slots.

SELECT state,
       application_name,
       COUNT(*) AS connections,
       MAX(EXTRACT(EPOCH FROM (now() - state_change))) AS max_age_seconds
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, application_name
ORDER BY connections DESC;

SELECT application_name,
       client_addr,
       state,
       query_start,
       LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

If you genuinely need a larger ceiling, change it with the workload in mind and reload through a controlled maintenance window where required. PostgreSQL will not make every existing pool instantly smaller; clients also need matching pool limits. For most web applications, a single well-sized pool per service is healthier than several layers each multiplying connections.

4. Use synchronous_commit deliberately for durability and latency

synchronous_commit controls when PostgreSQL reports a transaction as committed. The default is usually on, which is the right choice for financial records, NHS data, order state and other workloads where losing the final acknowledged transaction is unacceptable.

Teams sometimes disable it to improve throughput, but that is not a free performance win. It changes the failure contract: a host or storage failure can lose committed transactions even though the client received success. That may be acceptable for disposable analytics events or rebuildable caches, provided the product owner and operational runbook explicitly accept the trade-off.

Leave it enabled for critical systems and improve the surrounding path instead. Use durable storage, test restore procedures and monitor commit and flush latency. If some workloads genuinely tolerate weaker durability, isolate them rather than weakening the primary database for everyone.

SHOW synchronous_commit;
SHOW wal_level;
SHOW archive_mode;
SHOW archive_command;

SELECT datname,
       xact_commit,
       xact_rollback,
       blks_read,
       blks_hit,
       temp_files,
       temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

Also check replication and archiving configuration. A fast local commit is worthless if backups, point-in-time recovery or replicas have not been tested. UK teams should remember data residency and retention obligations too: a backup strategy must match GOV.UK, NHS or sector-specific requirements, not merely satisfy a generic tutorial.

5. Set a realistic work_mem and control temporary-file explosions

work_mem is the memory available for sort and hash operations within one query operation. The trap is that it can be consumed by multiple workers, multiple nodes in one query, or several concurrent queries. A modest server-wide default multiplied by aggressive parallelism can become a memory incident.

Do not globally set a huge value because one report once spilled to disk. Find the queries creating temporary files, inspect their execution plans and decide whether to improve the index, rewrite the query or give that workload a controlled memory allowance. Per-session overrides are often better than turning every query into a potential memory hog.

SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes,
       ROUND(temp_bytes / 1024.0 / 1024.0 / 1024.0, 2) AS temp_gb
FROM pg_stat_database
WHERE temp_bytes > 0
ORDER BY temp_bytes DESC;

SET LOCAL work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, COUNT(*)
FROM delivery_events
GROUP BY status
ORDER BY COUNT(*) DESC;

RESET work_mem;

For a known reporting user or job, set work_mem in the session or role configuration. For unpredictable ad hoc queries, cap it globally and use statement timeouts to stop a runaway request monopolising the server. A setting is only useful when it is tied to a workload and a limit.

Change one thing, then prove it worked

The five settings worth reviewing first are shared_buffers, effective_cache_size, max_connections, synchronous_commit and work_mem. They are not magic numbers, and they should not be changed in one giant batch. Capture a baseline, make one deliberate change, reload where supported, and compare latency, throughput, cache hit ratio, temporary files and connection pressure.

Postgres defaults are a respectable baseline. Blindly following them on production hardware is lazy configuration management. The right PostgreSQL configuration is the one that respects your memory, durability requirements and workload while giving you evidence when performance shifts.

Frequently asked PostgreSQL configuration questions

Should shared_buffers always be 25% of RAM?

No. It is a useful starting estimate for a dedicated server, not a law. Account for the database host's total memory, PostgreSQL overhead, operating-system cache and other resident processes. Verify the result with cache and eviction metrics.

Is increasing max_connections the quickest way to improve performance?

Usually not. More sessions can increase contention and consume more memory. Inspect active and idle connections, remove wasteful pools and size application concurrency to the server's capacity before raising the ceiling.

Can turning off synchronous_commit safely improve PostgreSQL throughput?

Only for workloads that explicitly tolerate losing recently acknowledged commits after a crash. Keep it enabled for financial, healthcare and other critical transactions, then optimise the commit path and test backups and recovery instead.