Ilya Kosmodemiansky: The “default” postgresql.conf

Part 1 of 4: how PostgreSQL configuration works, and connection settings

pg_settings, reload versus restart, max_connections, reserved connections and TCP keepalives

This is Part 1 of a four-part series based on the talk “The “default” postgresql.conf, step by step”, which I gave at PGConf.EU 2026 in Valencia on 21 October 2026. The slides can be found here.

A 45-minute talk leaves no room for test output, for the mechanism behind a setting, or for the questions people asked afterwards. A blog post has that room, so these posts contain a few extras that were not in the talk.

A new PostgreSQL installation starts with conservative defaults. They are chosen so that Postgres starts on almost any hardware, from a coffee machine to a small virtual machine, and they are not tuned for the server you actually run. The defaults improve from release to release, but you still need to read the documentation carefully to know which ones to change. Real servers have more memory, faster disks, unstable networks, connection poolers and applications that leave transactions open.

At Data Egret, we look after many PostgreSQL clusters. When we set up a new cluster or take over an existing one, we almost always start by applying the same set of changes. We call that set “our default postgresql.conf”. It is not a tuning guide for one specific workload. It is a baseline that is safe on most production servers and that a DBA who is new to PostgreSQL can apply and understand. Its values depend on the capabilities of your hardware and on common problems of a typical OLTP workload, not on one specific application. From this starting point, you adjust, test and improve further. This series goes through that baseline step by step: for each setting, what it does, what the default is, what we change it to and why, and what a change takes (a reload, a restart or just a SET in a session).

All defaults in this series are PostgreSQL 18 defaults, and all output was taken from PostgreSQL 18.6 from the community Debian packages.

The series has four parts. Each part covers one area of the configuration and ends with that area’s piece of the baseline, so you can read, test and apply one area at a time. With all the test output, the material is also too long for a single post.

Part 1: How PostgreSQL configuration works, and connection settings (this post)
Part 2: Memory, WAL and checkpoints (coming soon)
Part 3: Autovacuum and freezing (coming soon)
Part 4: Logging, statistics, replication and what is new since PostgreSQL 14 (coming soon)

399 settings, and the few that matter

PostgreSQL 18 has 399 settings. The number keeps growing: a fresh cluster from the Debian packages shows 271 in version 10, 348 in version 14 and 399 in version 18:

postgres=# SELECT count(*) FROM pg_settings; count ------- 399 (1 row)

(A build from source shows one setting less up to version 17, 270 and 347, because the Debian packages add extension_destdir.)

Nobody needs to touch 399 settings. Our baseline changes or checks about 60 of them. The rest are fine at their defaults, or matter only for special setups. Before we go through the 60, it helps to know where settings come from and how to work with them conveniently.

postgresql.conf and postgresql.auto.conf

PostgreSQL reads its settings from two files.

postgresql.conf is the main configuration file, and you edit it by hand. On Debian and Ubuntu it is in /etc/postgresql/<version>/<cluster>/; on most other systems it is in the data directory. PostgreSQL reads it at server start and at every reload.

postgresql.auto.conf lives in the data directory, and PostgreSQL writes it for you. ALTER SYSTEM SET name = value puts the value into this file, and ALTER SYSTEM RESET name removes it again. Tools write to it too: pg_basebackup -R, for example, puts the replication settings there. Do not edit it by hand while the server runs.

PostgreSQL reads postgresql.auto.conf after postgresql.conf, and within the files the last occurrence of a setting wins. So a value set with ALTER SYSTEM wins over postgresql.conf, and if a setting appears twice in postgresql.conf, the second line wins. This is a classic source of confusion: someone edits postgresql.conf, reloads, and nothing changes, because months ago someone else ran ALTER SYSTEM for the same setting.

If your configuration is managed by a tool such as Ansible or Patroni, you may not want anybody to write postgresql.auto.conf at all. Since PostgreSQL 17, allow_alter_system = off makes ALTER SYSTEM fail with an error. The documentation states clearly that this is not a security feature: a superuser can still change the configuration in other ways. It only protects your managed configuration from well-meant manual changes.

Besides the files, there are two more ways to change settings, and both can cause a lot of confusion:

The server command line. Settings passed to the postgres process at startup, with -c name=value (for example through pg_ctl -o or a systemd unit), override both files, including ALTER SYSTEM. They stay in effect until the server is restarted without them: a reload does not change them, and pg_settings.source shows command line. We ran into this ourselves while preparing the talk. Debian’s pg_ctlcluster passes -c config_file=/etc/postgresql/18/main/postgresql.conf this way, and some container images and service files pass more.
Databases, roles and sessions. Many settings can also be set with ALTER DATABASE … SET, ALTER ROLE … SET or SET in a session. Which settings allow this depends on their context, see below.

postgresql.conf also supports includes. include 'file' reads another file at that point as if its lines were written there, and fails if the file is missing. include_if_exists 'file' does the same but only logs a missing file. include_dir 'directory' reads every file ending in .conf in that directory, in file name order. Debian’s postgresql.conf ends with include_dir = 'conf.d', which is a good place for your own settings.

When we edit postgresql.conf by hand, we change values where they are and do not reorder the file. Anybody who knows the stock file then finds every setting where they expect it, and a diff against the stock file stays readable.

Single quotes, and why a reload is safer than a restart

Values that are not plain numbers or simple words must be in single quotes:

work_mem = '128MB' log_line_prefix = '%m %p %u@%d '

Double quotes are a syntax error. What happens next depends on whether you reload or restart. Let us add work_mem = "128MB" to the end of postgresql.conf and look at pg_file_settings first. This view shows what the configuration files contain right now, before anything is applied:

postgres=# SELECT sourcefile, sourceline, applied, error FROM pg_file_settings WHERE error IS NOT NULL; sourcefile | sourceline | applied | error -----------------------------------------+------------+---------+-------------- /etc/postgresql/18/main/postgresql.conf | 890 | f | syntax error (1 row)

A reload with this file is harmless. The server logs the error, keeps running and applies nothing:

LOG: syntax error in file "/etc/postgresql/18/main/postgresql.conf" line 890, near token """ LOG: configuration file "/etc/postgresql/18/main/postgresql.conf" contains errors; no changes were applied

A restart with the same file is not harmless. The server stops and does not come back:

LOG: syntax error in file "/etc/postgresql/18/main/postgresql.conf" line 891, near token """ FATAL: configuration file "/etc/postgresql/18/main/postgresql.conf" contains errors

(The line number differs because we appended the line once more for the restart test.)

So make it a routine: after editing the file, check pg_file_settings for errors, then reload, and restart only when a setting really needs it. A mistake found on reload costs you nothing. The same mistake found on restart is an outage.

pg_settings: the best source of truth you have

Whatever the source of a value, the pg_settings view shows the result: one row per setting, with the value that is in effect and where it came from. Here is checkpoint_timeout on a server where postgresql.conf sets it to 60min:

postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_settings WHERE name = 'checkpoint_timeout'; -[ RECORD 1 ]---+--------------------------------------------------------- name | checkpoint_timeout setting | 3600 unit | s category | Write-Ahead Log / Checkpoints short_desc | Sets the maximum time between automatic WAL checkpoints. extra_desc | context | sighup vartype | integer source | configuration file min_val | 30 max_val | 86400 enumvals | boot_val | 300 reset_val | 3600 sourcefile | /etc/postgresql/18/main/postgresql.conf sourceline | 267 pending_restart | f

The columns that matter most in daily work:

setting and unit: the value is always shown in the setting’s base unit. Here 3600 seconds, not 60min. shared_buffers is shown in 8 kB pages, so 16384 means 128 MB.
boot_val: the compiled-in default, 300 seconds. Comparing setting with boot_val shows at a glance what was changed.
source, sourcefile, sourceline: where the value comes from, down to the file and the line. With includes and postgresql.auto.conf in play, this answers “why is this value not what I wrote?” in seconds. Only superusers and roles with the privileges of pg_read_all_settings see the file and line.
context: what a change takes. More on this in a moment.
pending_restart: t if the file has a new value that the server has not applied yet because it needs a restart.

Here is ALTER SYSTEM in action. After ALTER SYSTEM SET work_mem = '64MB' and a reload, both views point to postgresql.auto.conf:

postgres=# SELECT sourcefile, sourceline, name, setting, applied FROM pg_file_settings WHERE name = 'work_mem'; sourcefile | sourceline | name | setting | applied --------------------------------------------------+------------+----------+---------+--------- /var/lib/postgresql/18/main/postgresql.auto.conf | 3 | work_mem | 64MB | t (1 row) postgres=# SELECT name, setting, unit, source, sourcefile FROM pg_settings WHERE name = 'work_mem'; name | setting | unit | source | sourcefile ----------+---------+------+--------------------+-------------------------------------------------- work_mem | 65536 | kB | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf (1 row)

If in doubt, always check pg_settings. It is the only place that tells you the truth about the running server.

Contexts: do I need to restart?

The context column is the one beginners overlook most often, and it answers the most practical question: what do I have to do for my change to take effect? There are seven contexts. On 18.6 they are distributed like this:

postgres=# SELECT context, count(*) FROM pg_settings GROUP BY 1 ORDER BY 2 DESC; context | count -------------------+------- user | 151 sighup | 104 postmaster | 69 superuser | 49 internal | 20 superuser-backend | 4 backend | 2 (7 rows)

Context	What a change takes	Examples
`postmaster`	a server restart	`shared_buffers`, `max_connections`, `listen_addresses`
`sighup`	a reload: `pg_ctl reload` or `SELECT pg_reload_conf()`	`checkpoint_timeout`, `autovacuum_naptime`, `log_line_prefix`
`superuser`	a reload, or `SET` in a session by a superuser or a role granted `SET` on the parameter (15+)	`log_min_duration_statement`, `temp_file_limit`
`user`	a reload, or `SET` by any user; also `ALTER ROLE … SET` and `ALTER DATABASE … SET`	`work_mem`, `statement_timeout`, `search_path`
`superuser-backend`	a reload or the connection request (for example `PGOPTIONS`), by a superuser or a role granted `SET`; affects new sessions only	`log_connections`, `log_disconnections`
`backend`	the same, but any user may set it at connection start; affects new sessions only	`ignore_system_indexes`, `post_auth_delay`
`internal`	cannot be changed: fixed at build time or at `initdb`, or computed by the server	`block_size`, `server_version`, `data_checksums`

A postmaster setting changed in the file and reloaded shows up with pending_restart = t. Here we changed shared_buffers to 1GB and reloaded:

postgres=# SELECT name, setting, unit, pending_restart FROM pg_settings WHERE name = 'shared_buffers'; name | setting | unit | pending_restart ----------------+---------+------+----------------- shared_buffers | 16384 | 8kB | t (1 row)

The server still runs with 16384 pages of 8 kB, the default 128 MB, and tells you that it is waiting for a restart. A monitoring check on pending_restart catches the “I changed it but forgot to restart” case before somebody wonders why the new value has no effect.

Now, off we go with the settings themselves.

listen_addresses

listen_addresses defines the TCP/IP addresses the server listens on. The default is localhost, which accepts TCP connections from the same machine only. A change needs a restart.

We usually set one of two values:

listen_addresses = '*' when clients connect over the network. * means all IPv4 and IPv6 interfaces.
listen_addresses = '127.0.0.1' when PostgreSQL runs together with PgBouncer on the same host, and only PgBouncer connects to it.

If PostgreSQL listens on an external IP address, protect that address with a firewall and with pg_hba.conf. pg_hba.conf decides which users may connect to which databases from which addresses and how they authenticate, but an open port still accepts connection attempts from anyone, and that alone is an easy target for password guessing.

What about Unix sockets? There are arguments that a Unix socket performs better than TCP over localhost, and for local connections it does avoid the TCP stack. We still prefer TCP in most setups, because of keepalives: they exist only in TCP. A session connected through a Unix socket reads all tcp_keepalives_* settings as 0, whatever postgresql.conf says. More on keepalives at the end of this post.

max_connections

Every client connection makes PostgreSQL start a new backend process. These processes are not light: each one has its own memory, and an active process can use at most one CPU core at a time. With 32 cores, at most 32 queries really run at the same moment, and everything else waits.

That is why a setting like max_connections = 1000 does not work in practice. It usually appears when an application keeps running out of connections, because of long transactions or because it handles its connection pool badly. Raising the limit treats the symptom. In the worst case, hundreds of backends then compete for a few CPU cores, and every query gets slower.

Our baseline keeps max_connections = 100 (the default) or sets 200, and puts PgBouncer or another connection pooler in front of PostgreSQL, with small pool sizes. A properly configured pooler serves many thousands of client connections with a few dozen PostgreSQL processes. Clients beyond the pool size wait in PgBouncer, not in PostgreSQL. PgBouncer’s default_pool_size is 20 per database and user pair.

A change to max_connections needs a restart. On a streaming replication standby, max_connections must be at least as high as on the primary.

Processes that do not need a connection slot

max_connections counts client backends only. A freshly started PostgreSQL 18 already runs several processes, and none of them uses a connection slot:

54 0:00 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/18/main/postgresql.conf 55 0:00 postgres: 18/main: io worker 0 56 0:00 postgres: 18/main: io worker 1 57 0:00 postgres: 18/main: io worker 2 58 0:00 postgres: 18/main: checkpointer 59 0:00 postgres: 18/main: background writer 61 0:00 postgres: 18/main: walwriter 62 0:00 postgres: 18/main: autovacuum launcher 63 0:00 postgres: 18/main: logical replication launcher

The postmaster, the first line, is the main server process. It accepts connections, starts a backend for each one and supervises all the other processes. Note the -c config_file=…: this is how Debian points the server to postgresql.conf in /etc.
The I/O workers are new in PostgreSQL 18. With the default io_method = worker, three of them (io_workers = 3) execute asynchronous reads on behalf of other processes.
The checkpointer writes all dirty buffers to disk at each checkpoint. Part 2 covers it in detail.
The background writer writes some dirty buffers ahead of time, so that backends find clean ones.
The walwriter flushes WAL from memory to disk every 200 ms (wal_writer_delay).
The autovacuum launcher starts autovacuum workers. Part 3 is about them.
The logical replication launcher starts the workers for logical replication subscriptions.

The stats collector, which older versions also showed here, is gone since PostgreSQL 15: statistics now live in shared memory. Shared memory is sized at server start for every connection and worker slot, so even idle connection slots cost some memory.

To see what is running, ask pg_stat_activity. Here is the same server with three client sessions, one of them running this query:

postgres=# SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1; backend_type | count ------------------------------+------- client backend | 3 io worker | 3 autovacuum launcher | 1 background writer | 1 checkpointer | 1 logical replication launcher | 1 walwriter | 1 (7 rows)

In this output, only the client backend rows use connection slots. All other types either have their own limits (autovacuum worker, background worker and walsender count against autovacuum_worker_slots, max_worker_processes and max_wal_senders) or are auxiliary processes that need no slot at all. They still use CPU and memory, so keep them in mind when you decide how high to set max_connections.

Reserved connections

When all connection slots are taken, a DBA still needs to get in and find out why. PostgreSQL keeps some slots in reserve for that, and both kinds are taken out of max_connections:

superuser_reserved_connections = 5 # default 3 reserved_connections = 10 # default 0, since 16

superuser_reserved_connections is the last resort: these slots accept superusers only. We raise it from 3 to 5 just in case.

reserved_connections, new in PostgreSQL 16, keeps slots for roles with the privileges of the predefined role pg_use_reserved_connections. This is what monitoring and DBA accounts that are not superusers need, and you should not run your monitoring as a superuser anyway:

GRANT pg_use_reserved_connections TO monitoring;

Having these less-privileged reserved connections helps you avoid gaps in the monitoring graphs exactly when you need proper observability.

With max_connections = 100 and the values above, ordinary roles get 85 connections. When only reserved slots are left, PostgreSQL accepts superusers and members of pg_use_reserved_connections; when only the superuser slots are left, superusers only. Both settings need a restart.

TCP keepalives

Imagine an application server that loses power, or a firewall that silently drops a connection. PostgreSQL does not notice. The backend waits for the next query from a client that no longer exists, holds its connection slot and, if a transaction was open, holds back vacuum.

TCP keepalives detect such dead peers. After a period without traffic, the kernel sends small probe packets, and if the other side does not answer, it closes the connection. PostgreSQL’s defaults for all three keepalive settings are 0, which means “use the operating system’s values”. On Linux those are 7200 seconds (two hours) before the first probe, then 9 probes 75 seconds apart. A dead connection survives for more than two hours.

Our baseline:

tcp_keepalives_idle = 5 # seconds without traffic before the first probe tcp_keepalives_interval = 1 # seconds between unanswered probes tcp_keepalives_count = 5 # unanswered probes before the connection is dropped

With these values, an idle connection to a dead peer is dropped after about 10 seconds: 5 seconds of silence, then 5 probes 1 second apart. All three settings change with a reload, or with SET in a session.

Why so aggressive? Even if the network between your application and your database is very good, it can become unstable suddenly, and when it does, you want dead sessions gone in seconds, not in hours. If your network is unstable all the time, 5 seconds helps even more.

Keepalives have limits worth knowing:

They fire only on idle connections. When data is in flight and the peer disappears, tcp_user_timeout covers that case.
A backend busy with a long query notices a vanished client only when it next talks to the socket, typically when it sends the result. Since PostgreSQL 14, client_connection_check_interval lets the backend check the connection periodically during a query.

Keepalives behind HAProxy

Many setups put HAProxy between applications and PostgreSQL, for example in front of a Patroni cluster. Here keepalives behave differently from what most people expect, and two things need care. We tested both with HAProxy 3.4 and PostgreSQL 18.

HAProxy terminates TCP. There are two separate TCP connections: client → HAProxy and HAProxy → PostgreSQL. PostgreSQL sees HAProxy as its client, so the tcp_keepalives_* settings cover only the HAProxy → PostgreSQL side. Keepalive probes are handled by the kernel and are never forwarded through a proxy. The client side needs its own keepalives: either option clitcpka in HAProxy (with clitcpka-idle, clitcpka-intvl and clitcpka-cnt for the timings, HAProxy 2.4 and later), or libpq’s keepalives_idle, keepalives_interval and keepalives_count in the application’s connection string.

HAProxy closes idle connections itself. After timeout client or timeout server without data, HAProxy drops the connection, and keepalive probes do not count as activity, only real data does. In TCP mode, timeout tunnel, if set, replaces both. Patroni’s sample haproxy.cfg uses 30 minutes, so an idle pooled connection dies after 30 minutes, and the application sees “server closed the connection unexpectedly”.

In our test, HAProxy ran with 10-second client and server timeouts, and we left a psql session idle for 15 seconds:

HAProxy configuration	Idle psql session after 15 s
plain TCP mode	dropped
with `option tcpka`	dropped
with `timeout tunnel 1h`	alive

Keepalives did not help at all. Only a longer timeout did. Keep HAProxy’s timeouts longer than any connection in your setup ever stays idle, for example longer than PgBouncer’s server_idle_timeout (10 minutes by default), or set timeout tunnel.

Summary of Part 1 (of 4)

Before you change anything:

Check pg_settings for the value in effect, and sourcefile and sourceline for where it comes from.
Remember that ALTER SYSTEM (postgresql.auto.conf) wins over postgresql.conf, and the command line wins over both.
Check pg_file_settings for errors, reload, and restart only when the context is postmaster. Watch pending_restart.
Quote values with single quotes.

The connection part of our baseline postgresql.conf:

listen_addresses = '*' # or '127.0.0.1' behind a local PgBouncer; restart max_connections = 100 # or 200, with a connection pooler; restart superuser_reserved_connections = 5 # restart reserved_connections = 10 # 16+; restart tcp_keepalives_idle = 5 # reload tcp_keepalives_interval = 1 # reload tcp_keepalives_count = 5 # reload

In Part 2 of 4 we move on to memory and the write-ahead log: shared_buffers, huge pages, work_mem, and how checkpoints are really triggered and paced. Part 3 covers autovacuum and freezing, and Part 4 covers logging, statistics, replication and what is new since PostgreSQL 14.