PostgreSQL setup checklist

This is a simple and short checklist for setting up and configuring PostgreSQL instances.

These settings should at least be looked at before running a PostgreSQL server.

• checkpoint_segments

PostgreSQL 9.5 and later use a max_wal_size that corresponds to approximately checkpoint_segments = 20, so that's probably a good setting to use in older versions.

• listen_addresses

Set to * if necessary. The more restrictive, the better. (Change requires restart.)

• max_connections

It is arguably better to set this to a higher value and then restrict the number of connections via superuser_reserved_connections, per-role settings, etc. That way, additional connections can be added at run time. (Change requires restart.)

• shared_buffers

Recommended between 10% and 25% or RAM. Tend toward lower end on write-heavy system. Some claim that it shouldn't be more than 8 GB on Linux, but there are also reports that much larger values can be useful. Very high settings should probably be tested.

• superuser_reserved_connections

At times, you might need a lot more than the default 3. (Consider: administrator session, monitoring system, trending system, base backup + WAL streaming, etc.) Suggestion: 20. Also check max_connections relative to this.

Even if replication is not in use, it makes sense to prepare some settings for it, in case replication or incremental backups are to be added later.

• hot_standby

Set to on everywhere.

• hot_standby_feedback

Set to on for read-only secondaries. Can usually be on except in special circumstances (delayed replication).

• max_replication_slots

Set to the same value as max_wal_senders. There is a difference, but it's not important enough to compute and maintain two different values. The default in PostgreSQL 10 is 10, which should be sufficient for a start. In <=9.6, the default is 0, so it needs to be changed.

• max_wal_senders

Give plenty of room, like 10. Keep in mind to keep room for rebuilding standbys while other standbys are running, etc. The default is the same as for max_replication_slots; see above.

• wal_keep_segments

Not needed if replication slots are used. For PostgreSQL <9.6, pg_basebackup does not use replication slots, so set to 1000 or so.

• wal_level

Set to replica/hot_standby, or logical if appropriate.

Note: For hot standby, the following settings on a standby must be the same or higher than on a primary:

• max_connections
• max_locks_per_transaction
• max_prepared_transactions
• max_worker_processes

These settings need not be changed for a first setup, but are useful when tuning performance or resource utilization. In some cases, their usefulness depends on the application.

• checkpoint_timeout

Depends on user requirements, but something like 30min could be useful for high-performance sites.

• checkpoint_completion_target

There is probably no reason not to always set this to 0.9, but the default 0.5 should also work fine in most cases.

• effective_cache_size

Change on systems with very high or low amount of RAM, or when tuning index usage.

• effective_io_concurrency

See PostgreSQL documentation. Needs individual tuning.

• random_page_cost

Can be lowered, perhaps to 1.1 or so, when using SSDs or a fully cached database.

• maintenance_work_mem

To tune vacuum or reindexing performance. Careful on low-memory (virtualized) systems.

• track_functions

Set to all if user-defined functions are in use.

• work_mem

To tune query performance. Also useful to set from application code case by case.

• vacuum_cost_limit

Ensure this is not set to the default value of 200. This value is almost always too conservative for today's hardware. Better values are likely closer to 1000 or higher. If it's set to 200 it likely means nobody changed it, rather than that's the correct value.

• autovacuum_vacuum_cost_limit

If not set to -1, see above.

• wal_buffers

Auto-tuned since 9.1, usually not necessary to set explicitly.

All other changes to memory, cost, or statistics settings should be accompanied by an explanation.

The more logging the better.

log_autovacuum_min_duration = 1000ms
log_checkpoints = on
log_line_prefix = '%m [%p]: user=%u,db=%d '
log_lock_waits = on
log_min_duration_statement = 1000ms
log_temp_files = 1024kB
log_timezone = 'something useful'

Note that the defaults are not very useful.

• log_connections = on, log_disconnections = on

Will cause increased log volume and are not always interesting, but useful for debugging at times.

• log_hostname = on

If host names are used internally, then this is useful.

• log_statement = ddl

For checking schema changes, for getting context if locks cause site problems, etc.

• log_duration

Prefer log_min_duration_statement.

Useful extensions to install:

Very useful:

• pg_stat_statements
• pgstattuple (for bloat information)

For performance analysis and tuning:

• auto_explain (enable on demand)
• pg_buffercache

For rare debugging:

• pageinspect
• pg_freespacemap
• pgrowlocks
• pg_visibility