Postgres production tuning that cuts latency

postgresql.conf, in its default state, is designed to never fail an installation, not to absorb your traffic. The historical value of shared_buffers, 128 MB, allows Postgres to start on a minimal machine without reserving any critical resources. max_connections at 100 fits on a small shared server. Neither was chosen for your actual load.

So the problem is not that Postgres is poorly set by default: it's that it was never set for you. The following sections go through the settings in the order they pay off the most, from the most common bottleneck (the connections) to the slowest to appear (the WAL).

The first project is not memory, it is connections. Each Postgres connection opens a dedicated server process that consumes RAM and context CPU time, even if idle. Increasing max_connections to avoid "too many connections" type errors shifts the problem: beyond a certain number of simultaneous active connections, CPU contention degrades the latency of all requests, including the fastest.

The correct approach reverses the usual order: scale max_connections on the actual server concurrency, then absorb the application-side concurrency with a pooler like PgBouncer into pool_mode=transaction. The pooler multiplexes hundreds of client connections onto a handful of actually active server connections. Our dedicated article details the transaction mode mechanism and its limits (prepared statements, LISTEN/NOTIFY), and a comparison between PgBouncer, Supavisor and PgCat to choose the implementation.

max_connections is a "postmaster" context parameter: changing it requires a full server restart, not a simple reload. On Aurabase dedicated Postgres instances (Pro and Enterprise plans, one CNPG cluster per project), this setting is configured per plan tier rather than left at its default value. A restart is not a trivial operation to repeat in production, which justifies this choice in stages rather than a fixed value. The formula for choosing your own value, with its limits, is the subject of a separate article: size max_connections.

Four memory settings weigh more than all the others combined: shared_buffers, effective_cache_size, work_mem and maintenance_work_mem. The first three determine how much data Postgres keeps in memory before returning to disk; the fourth determines the speed of a VACUUM or index creation.

shared_buffers sets the internal cache shared by all connections. The benchmark commonly documented by the PostgreSQL project is approximately 25% of the RAM available on a dedicated database server. Beyond that, the gains decrease and the operating system's disk cache takes over. effective_cache_size does not allocate anything: it is an estimate, given to the query planner, of the total memory available for the cache (Postgres and OS combined). Undersizing it pushes the scheduler towards sequential scans whereas an index would largely cache; the current benchmark is between 50 and 75% of RAM.

work_mem is the most common trap. This is not a global limit. Each sort or hash operation in a query can consume its own share of it, and a query with multiple joins can reserve its own share several times. Too generous a value combined with a high max_connections can exhaust server RAM under concurrent load, even if each request taken in isolation seems reasonable. maintenance_work_mem, conversely, can remain significantly more generous: it only applies to maintenance operations (VACUUM, CREATE INDEX), which are rarely concurrent with each other.

Only shared_buffers requires a reboot. The other three are hot reloaded, including for an isolated session: SET work_mem = '64MB'; for the duration of a single greedy request, without affecting the global setting.

Never disable autovacuum in production, even temporarily to "free up resources" during a peak load. Postgres uses MVCC: each UPDATE and each DELETE leaves a dead line that only vacuum can recover. Without it, tables bloat, indexes degrade, and execution plans gradually deteriorate, with no errors visible until it's late.

The default value ofautovacuum_vacuum_scale_factor (20% dead rows before triggering) is suitable for a small table, not a write-intensive multi-million row table. On a table of 10 million rows, this 20% represents 2 million dead rows accumulated before the first pass. Lower this threshold table by table rather than changing the overall value of the entire database.

-- Lower the threshold on a write-heavy table, not overall
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

The Advisor integrated into Aurabase Studio checks this configuration at each project analysis, in the same way as tables without primary keys or non-indexed foreign keys. This is an explicit signal rather than a silent degradation discovered too late.

The majority of latency problems in production come neither from the CPU nor from the RAM: they come from an absent or poorly chosen index. Before touching a single parameter of postgresql.conf, EXPLAIN (ANALYZE, BUFFERS) on the query in question remains the most reliable diagnosis. It reduces Postgres request latency well before adding resources.

-- Diagnose a slow query before touching configuration
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

An Seq Scan on a multi-million row table, where an Index Scan was expected, almost always signals an index problem. Three causes come up most often: a missing index, a column type incompatible with the existing index, or obsolete statistics after a massive import without ANALYZE. Adding RAM or increasing work_mem sometimes hides this symptom on a small volume of data; the problem reappears as soon as the table grows.

To locate these requests without searching them one by one, pg_stat_statements aggregates the execution statistics of all the server's requests. A common pitfall: the extension must first be listed in shared_preload_libraries, a "postmaster" context parameter which requires a restart. Without this step, CREATE EXTENSION pg_stat_statements; silently succeeds but collects nothing.

# Requires a complete restart ("postmaster" parameter)
shared_preload_libraries = 'pg_stat_statements'

-- Once the server has restarted, in each database to audit:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

This is exactly the error that the Aurabase backend returns when this extension is absent: an explicit message rather than a silent empty list, which could be confused with "no slow query". The Studio Advisor goes further: it automatically classifies any request with an average time greater than 150 ms as warning, and beyond 500 ms as critical. These thresholds are based on the same pg_stat_statementsstatistics.

A checkpoint forces Postgres to write to disk all pages modified in memory since the previous one. By default, this writing may focus on too short a time window. The result is a noticeable spike in disk latency on the application side, the kind of periodic slowdown that's difficult to relate to a specific request.

checkpoint_completion_target controls the spread of this writing over the interval between two checkpoints. A detail missing from older checklists: PostgreSQL 14, released in 2021, changed its default value from 0.5 to 0.9. On a PostgreSQL 16 instance, such as dedicated Aurabase tenant clusters, this setting is therefore already correct by default; adjusting it manually only makes sense on a version prior to 14. See our comparison PostgreSQL 16 vs 17 vs 18 for other version changes that affect tuning.

max_wal_size acts in the same direction: a value that is too low triggers more frequent checkpoints than expected, even when checkpoint_timeout is not yet reached. Increasing it reduces the frequency of checkpoints, at the cost of longer recovery time after a crash since there are more WALs to replay. A compromise to be decided according to your tolerance for unavailability, not a universal value.

This checklist is not a one-off audit to be checked off once before going into production. A base that doubles in volume or traffic that triples makes the benchmarks chosen at startup obsolete, often without explicit error, just a progressive degradation of p95 latency.

Three signals merit continued monitoring. pg_stat_statements identifies requests that degrade over time. pg_stat_activity reports blocked or abnormally long running queries, and the ratio of active connections to max_connections anticipates saturation before it produces application-side errors.

Studio's Observability tab covers part of this foundation for any Aurabase project, with no third-party tools to install. It lists slow requests, allows you to cancel or terminate an active request by PID, and displays a pool saturation indicator which goes into warning above 80% usage. On a self-hosted instance, this same monitoring is built by hand, with pg_stat_statements activated and an external monitoring tool plugged into it.

Eight settings, in the order they pay off the most, with what you need to know before you touch them.

The thresholds cited here, such as the 150 ms and 80% pool saturation that the Aurabase Studio Advisor monitors, are a code-verified starting point, not a universal truth. Your actual charge remains the sole final judge. To precisely size max_connections rather than following a general rule, the dedicated article details the formula and its limits.

Three questions that systematically come up once the checklist is applied for the first time.