skip to content

Server Tuning

Tuning a PostgreSQL server means setting parameters in the right place, budgeting shared_buffers and work_mem, and capping connections and timeouts. Interviewers test whole-system reasoning.

part ofPostgreSQLread it →
on this pageshow

explore

questions

16

In PostgreSQL, what does max_connections limit, what is its default, and what does changing it require?

level: juniorimportance: must knowfreq 68%

answer

  1. who actually occupies a slot
  2. a start-time-only setting
  3. separate pools for background work
  4. SQLSTATE 53300 at the cap
  5. standby value versus primary

basics

~20 s

max_connections caps concurrent client backends, default 100. It has postmaster context, so a change needs a server restart, and a client arriving when every slot is taken is rejected with FATAL: sorry, too many clients already (SQLSTATE 53300).

solid answer

~40 s

`max_connections` is how many regular client backends PostgreSQL runs at once; the default is 100, though `initdb` may pick less if the kernel cannot support it. Autovacuum workers, background workers and WAL senders come from their own slot pools, so they do not use it up, while the last `superuser_reserved_connections` slots (3 by default) are kept for superusers out of the same 100. It has `postmaster` context: you can write it with `ALTER SYSTEM` or in `postgresql.conf`, but it applies only after a restart, because shared memory is sized from it. Past the limit there is no queue: the new client gets `FATAL: sorry, too many clients already` with SQLSTATE `53300`. On a streaming standby the value must be at least the primary's, or the standby will not allow queries.

code

sql · 10 lines
sql
SHOW max_connections;

SELECT current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS su_reserved,
       count(*) FILTER (WHERE backend_type = 'client backend') AS client_backends
FROM pg_stat_activity;

SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections', 'reserved_connections');

go deeper

for a junior

Know the default of 100, that the setting counts client sessions, and that a change needs a server restart rather than a reload.

for a middle

Explain which process pools sit outside the limit, why the context is postmaster, and which FATAL message and SQLSTATE a rejected client receives.

for a senior

Show you can change it on a replicated cluster safely: standbys first, restarts planned, and reserved slots kept for operators during an incident.

for a principal

Frame the number as a capacity budget shared by every service, operator tool and job, owned and allocated deliberately rather than raised on demand.

## What max_connections counts PostgreSQL serves every client session with its own **backend process**, started by the postmaster when the client connects (the process model itself is covered under the architecture topic). `max_connections` is the number of these **regular client backends** that may exist at the same time. Its default is **100**; `initdb` may write a lower value if the kernel's shared-memory or semaphore limits cannot support 100. Not every server process takes one of those slots. At startup the server sizes one array of process entries as the sum of several separate pools: - **`max_connections`** — client backends: application sessions, `psql`, `pg_dump`, monitoring agents. - **Autovacuum worker slots** — in PostgreSQL 18 sized by `autovacuum_worker_slots` (default 16), so that `autovacuum_max_workers` can be raised up to it without a restart; before 18 the pool was sized by `autovacuum_max_workers` itself. - **`max_worker_processes`** (default 8) — background workers, the pool that parallel query workers and logical replication workers are taken from. - **`max_wal_senders`** (default 10) — the processes that stream WAL to standbys and replication clients. The consequence is useful: a burst of parallel query or a new replica does not lock an application out, and an application connection storm cannot steal autovacuum's slots. Auxiliary processes such as the checkpointer, background writer and WAL writer sit outside all of these pools. Inside `max_connections` sit two reserves. The last **`superuser_reserved_connections`** slots (default **3**) admit only superusers, and an optional **`reserved_connections`** band (default **0**) admits roles granted the predefined role `pg_use_reserved_connections`. Both reserves come **out of** `max_connections`, not on top of it, so with the defaults an ordinary role can fill 97 slots. ## Why a change needs a restart `max_connections` has the **`postmaster`** context, the strictest one: the value is read once when the server starts. The reason is shared memory. The server allocates per-process entries and the shared lock table, which holds `max_locks_per_transaction` (default 64) entries per server process or prepared transaction, as fixed structures at startup, and they cannot grow while it runs. You can still record a new value with `ALTER SYSTEM` or by editing `postgresql.conf`; a reload then reports that the parameter cannot be changed without restarting, and `pg_settings.pending_restart` shows the change waiting (the reload machinery itself belongs to the parameter-files topic). A safe sequence for raising it on a replicated cluster: 1. Raise it on every standby and restart each one. 2. Raise it on the primary and restart it. 3. Confirm with `SHOW max_connections` on every node. Lowering runs in the opposite order: primary first, then the standbys. ## What a client sees at the limit PostgreSQL never queues an incoming connection. When the slot it needs is not available, the new backend ends with a **FATAL** error before the session starts, and every variant carries SQLSTATE **`53300`** (`too_many_connections`): | Situation | Message | |---|---| | Every slot is in use | `sorry, too many clients already` | | Only superuser-reserved slots are free and the role is not a superuser | `remaining connection slots are reserved for roles with the SUPERUSER attribute` | | Only the `reserved_connections` band is free and the role lacks `pg_use_reserved_connections` | `remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role` | | A role's or database's own `CONNECTION LIMIT` is exceeded | `too many connections for role "…"` or `too many connections for database "…"` | Waiting for a slot is the job of an application-side pool or an external pooler, not of the server. ## Standbys must keep up A hot standby reconstructs the primary's transaction state from WAL, and it needs at least as many process entries as the primary had to do that. The manual's rule is blunt: on a standby, `max_connections` must be the **same or higher** than on the primary, otherwise queries will not be allowed on the standby. The same rule applies to `max_locks_per_transaction`. This is why a connection-limit change on a replicated cluster is a rolling, ordered restart rather than one edit. ## Practical reading of the number - **The default of 100 is modest on purpose**: each client backend is a process with its own memory, and beyond a few hundred sessions the usual answer is a pooler in front rather than a higher number. - **Count only client backends** when checking headroom; `pg_stat_activity` lists other process types too, distinguished by `backend_type`. - **Leave room for operators**: reserved slots, migrations and ad-hoc `psql` sessions all draw from the same number.

  • Does a streaming standby's replication connection use up one of the primary's max_connections slots?
    No. A replication connection is served by a WAL sender process, and WAL senders come from their own pool sized by `max_wal_senders` (default 10). The server sizes its process array as `max_connections` plus the autovacuum worker slots, `max_worker_processes` and `max_wal_senders`, so adding replicas cannot lock application clients out, and an application storm cannot block replication.
  • Why can max_connections not be changed with a reload like statement_timeout can?
    Because shared memory is sized from it when the server starts: the array of per-process entries and the shared lock table, which holds `max_locks_per_transaction` entries per server process or prepared transaction. Those structures are allocated once and cannot grow while the server runs, so the setting has `postmaster` context; a reload only leaves the new value pending until the next restart.

  • Autovacuum workers and WAL senders all use up max_connections slots.
  • max_connections can be raised with pg_reload_conf() and no restart.
  • When the limit is hit, new clients wait in a server-side queue.
  • A standby can run with a lower max_connections than its primary.
  • superuser_reserved_connections slots are added on top of max_connections.

Assumes PostgreSQL 18; autovacuum's separate slot pool is sized by autovacuum_worker_slots from 18 on and by autovacuum_max_workers before 18.

open as a page

In PostgreSQL, what does shared_buffers control, what is its default, and why does changing it require a server restart?

level: juniorimportance: must knowfreq 68%

basics

~10 s

shared_buffers sizes PostgreSQL's own cache of 8 kB data pages in shared memory, used by every backend. It defaults to 128MB and has postmaster context, because that segment is allocated once at server start.

open as a page

In PostgreSQL, which configuration files set server parameters, which one wins a conflict, and how do you see where a value came from?

level: juniorimportance: must knowfreq 64%

basics

~20 s

postgresql.conf, with any include or include_dir files, is read first, then postgresql.auto.conf, written by ALTER SYSTEM, whose entries win; the last entry for a parameter counts. SHOW gives the value; pg_settings adds source, sourcefile and sourceline.

open as a page

In PostgreSQL, how do statement_timeout, idle_in_transaction_session_timeout, transaction_timeout and idle_session_timeout differ in what they measure and what they end?

level: middleimportance: must knowfreq 64%

basics

~10 s

statement_timeout cancels one statement with ERROR 57014 and the session survives. idle_in_transaction_session_timeout, transaction_timeout (17+) and idle_session_timeout end the whole session with FATAL. All four are off by default and take milliseconds.

open as a page

In PostgreSQL, why is work_mem not a per-query or per-connection limit, and how does that change the way you choose its value?

level: middleimportance: must knowfreq 74%

basics

~20 s

work_mem (default 4MB) budgets each sort or hash operation in a plan; hash nodes get work_mem times hash_mem_multiplier (2.0). A query holds several budgets, per parallel worker, across many sessions, so keep it modest globally and raise it locally.

open as a page

In PostgreSQL, after changing a parameter in postgresql.conf, how do you tell whether a reload applies it or a restart is needed?

level: middleimportance: must knowfreq 74%

basics

~10 s

Check pg_settings.context: postmaster parameters need a restart, while sighup and session-level ones apply on a reload. After pg_reload_conf(), pending_restart = true in pg_settings flags any value still waiting for a restart.

open as a page

In PostgreSQL, how do superuser_reserved_connections and reserved_connections keep an operator able to log in when application connections fill max_connections?

level: middleimportance: should knowfreq 34%

basics

~10 s

Both reserves come out of max_connections. The last superuser_reserved_connections slots (default 3) admit only superusers; the reserved_connections band below them (default 0) also admits roles granted pg_use_reserved_connections, such as an on-call or monitoring role.

open as a page

In PostgreSQL, why can maintenance_work_mem safely be set much larger than work_mem, and when should autovacuum_work_mem be set separately?

level: middleimportance: should knowfreq 42%

basics

~20 s

maintenance_work_mem (default 64MB) serves VACUUM, CREATE INDEX and ADD FOREIGN KEY, and a session runs only one such command at a time. Autovacuum may use it once per worker, so autovacuum_work_mem caps those workers separately.

open as a page

In PostgreSQL, what does ALTER SYSTEM actually change, and why would a team set allow_alter_system = off?

level: middleimportance: should knowfreq 46%

basics

~20 s

ALTER SYSTEM writes the setting into postgresql.auto.conf, which overrides postgresql.conf once reloaded or restarted. allow_alter_system = off (PostgreSQL 17+) blocks the command so an external config tool stays the single source of truth; it is not security.

open as a page

In PostgreSQL, a food-delivery microservice fleet hits 'sorry, too many clients already' at the lunch peak; why can raising max_connections from 200 to 2000 make things worse, and what do you do instead?

level: seniorimportance: should knowfreq 56%

basics

~20 s

Each admitted connection is a PostgreSQL backend process with its own memory, and more active backends than cores only add contention. Raising the cap needs restarts and invites memory exhaustion; budget per-service connections, cap roles and put a pooler in front instead.

open as a page

In PostgreSQL 18, what do io_method and io_workers control, and how do you choose between the worker, io_uring and sync methods?

level: seniorimportance: should knowfreq 34%

basics

~10 s

PostgreSQL 18 adds asynchronous reads: io_method picks how they run (worker, the default; io_uring on Linux builds with liburing; sync), and io_workers sets how many I/O worker processes serve the worker method (default 3).

open as a page

A 64 GB PostgreSQL server for a logistics workload lost every session when the Linux OOM killer struck during month-end reports; which memory settings do you examine, and what do you change?

level: seniorimportance: should knowfreq 48%

basics

~20 s

Add up the worst case: shared_buffers plus work_mem times hash_mem_multiplier per sort or hash node, per parallel process, per active session, plus autovacuum and maintenance memory. Cut the global work_mem, grant more only to reporting, and bound concurrency.

open as a page

On a PostgreSQL 18 ticketing site, ALTER SYSTEM plus pg_reload_conf() returned true, yet sessions still show the old value; how do you find out why?

level: seniorimportance: should knowfreq 52%

basics

~10 s

pg_reload_conf() returning true only proves the signal was sent. Check pg_settings (source, sourcefile, pending_restart), then pg_file_settings (applied, error), then role/database overrides, the parameter's context, and whether you changed the right node.

open as a page

In PostgreSQL, how do ALTER DATABASE SET, ALTER ROLE SET, SET and SET LOCAL rank against postgresql.conf, and when does each take effect?

level: seniorimportance: should knowfreq 40%

basics

~10 s

Each layer outranks the files: ALTER DATABASE SET, then ALTER ROLE SET, then ALTER ROLE IN DATABASE SET apply at login; SET lasts for the session; SET LOCAL lasts until the transaction ends.

open as a page

In PostgreSQL, when a client host vanishes without closing its TCP connection, how do the tcp_keepalives settings, tcp_user_timeout and client_connection_check_interval get its backend cleaned up?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

PostgreSQL turns on TCP keepalives for client sockets; tcp_keepalives_idle, _interval and _count (seconds, 0 = OS default) shorten detection of a silent dead peer, tcp_user_timeout caps unacknowledged sends, and client_connection_check_interval lets a busy query notice the loss.

open as a page

On Linux, why run PostgreSQL's shared memory on huge pages, and how do you size vm.nr_hugepages and confirm the server actually got them?

level: seniorimportance: nice to knowfreq 24%

basics

~10 s

Huge pages shrink page tables and memory-management CPU for a large shared_buffers. Size vm.nr_hugepages from postgres -C shared_memory_size_in_huge_pages, and check SHOW huge_pages_status, since the default huge_pages = try silently falls back.

open as a page