PostgreSQL tuning

The PostgreSQL image tunes PostgreSQL to its container every time it starts. Enter the container's limits and volumes below to see the settings the server will run with, where each one comes from, and the warnings the image prints. Running a server of your own? Download the same settings as one include file. The page address keeps your inputs, so you can share a view or come back to it later.

Memory limit

GB

--memory, or mem_limit in Compose.

CPUs--cpus, rounded up.

/dev/shm

MB

--shm-size. Docker gives 64 MB unless told otherwise.

Data volume

GB

Leave it empty if you do not know it.

ConnectionsTS_TUNE_MAX_CONNS, 200 when empty.

Background workersTS_TUNE_MAX_BG_WORKERS, 64 when empty. One for each tenant database.

Write-ahead log

With the dataOwn volume

The tuner is told of a quarter of the data volume.

TimescaleDB edition

TSLApache

The default, which compresses old history.

More inputs

Variables and an argument that replace what the entrypoint reads from the container.

TS_TUNE_MEMORYTune for this much memory instead, such as 8GB. The memory warning still compares with the container's limit.

TS_TUNE_NUM_CPUSTune for this many CPUs instead.

TS_TUNE_WAL_DISK_SIZEThe WAL disk size to tune for, such as 50GB.

max_connections argumentThe value of a -c max_connections= after the image name. It wins over TS_TUNE_MAX_CONNS.

The tuner prints: Recommendations based on 4.00 GB of available memory and 2 CPUs for PostgreSQL 18

Settings the server runs with

The server reads postgresql.conf, where the tuner writes, then the policy files, then andromeda-derived.conf, then its command line. The last value it reads wins. Each line names where its value comes from and what it replaces.

max_connections = 200                        # tuner, postgresql.conf
shared_buffers = 1GB                         # tuner, postgresql.conf
work_mem = 5734kB                            # entrypoint, andromeda-derived.conf; replaces 8MB from the tuner
maintenance_work_mem = 512MB                 # tuner, postgresql.conf
effective_io_concurrency = 256               # policy, 10-andromeda.conf; matches 256 from the tuner
max_worker_processes = 69                    # tuner, postgresql.conf
max_parallel_workers_per_gather = 1          # tuner, postgresql.conf
max_parallel_workers = 2                     # tuner, postgresql.conf
wal_buffers = 32768kB                        # entrypoint, andromeda-derived.conf; replaces 16MB from the tuner
checkpoint_completion_target = 0.9           # tuner, postgresql.conf
max_wal_size = <the tuner's max_wal_size, at most 16384MB>  # entrypoint, andromeda-derived.conf; replaces <60 percent of the WAL disk, rounded up to whole 16MB segments> from the tuner
min_wal_size = <the tuner's min_wal_size, at most 2048MB>  # entrypoint, andromeda-derived.conf; replaces <half the tuner's max_wal_size> from the tuner
random_page_cost = 1.1                       # policy, 10-andromeda.conf; matches 1.1 from the tuner
effective_cache_size = 3GB                   # tuner, postgresql.conf
default_statistics_target = 100              # tuner, postgresql.conf
jit = off                                    # tuner, postgresql.conf
autovacuum_max_workers = 10                  # tuner, postgresql.conf
autovacuum_naptime = 1min                    # policy, 10-andromeda.conf; replaces 10 from the tuner
default_toast_compression = lz4              # tuner, postgresql.conf
max_locks_per_transaction = 128              # tuner, postgresql.conf
timescaledb.max_background_workers = 64      # tuner, postgresql.conf
timescaledb.last_tuned = <the time the tuner ran>  # tuner, postgresql.conf
timescaledb.last_tuned_version = '0.19.0'    # tuner, postgresql.conf
log_line_prefix = '%m [%p] %q%u@%d '         # policy, 10-andromeda.conf
log_autovacuum_min_duration = 0              # policy, 10-andromeda.conf
log_checkpoints = on                         # policy, 10-andromeda.conf
log_lock_waits = on                          # policy, 10-andromeda.conf
log_temp_files = 0                           # policy, 10-andromeda.conf
log_min_duration_statement = 1s              # policy, 10-andromeda.conf
lc_messages = 'C'                            # policy, 10-andromeda.conf
track_activity_query_size = 8kB              # policy, 10-andromeda.conf
track_io_timing = on                         # policy, 10-andromeda.conf
track_wal_io_timing = on                     # policy, 10-andromeda.conf
pg_stat_statements.max = 20000               # policy, 10-andromeda.conf
maintenance_io_concurrency = 256             # policy, 10-andromeda.conf
wal_compression = lz4                        # policy, 10-andromeda.conf
checkpoint_timeout = 15min                   # policy, 10-andromeda.conf
autovacuum_vacuum_cost_limit = 3000          # policy, 10-andromeda.conf
reserved_connections = 3                     # policy, 10-andromeda.conf
idle_in_transaction_session_timeout = 10min  # policy, 10-andromeda.conf
idle_session_timeout = 1h                    # policy, 10-andromeda.conf
client_connection_check_interval = 2s        # policy, 10-andromeda.conf
timescaledb.telemetry_level = off            # policy, 10-andromeda.conf
autovacuum_work_mem = 32MB                   # entrypoint, andromeda-derived.conf
io_workers = 3                               # entrypoint, andromeda-derived.conf
temp_file_limit = <5 percent of the data volume>  # entrypoint, andromeda-derived.conf

This is the first start on a new data directory. Every start tunes again over what the last one wrote, and keeps an earlier value that is within 5 percent of the new one.

ALTER SYSTEM and -c flags after the image name win over every file here.

For a server of your own

andromeda-tuning.conf holds these settings, shared_preload_libraries and the extensions the broker creates in each tenant database. Include it from the end of postgresql.conf on a PostgreSQL 18 server.

andromeda-tuning.conf

# andromeda-tuning.conf: the settings the Andromeda PostgreSQL image runs with,
# for a PostgreSQL 18 server of your own. They are what the image gives a
# new data directory when it runs timescaledb-tune with
#   --memory=4096MB --cpus=2 --max-conns=200 --max-bg-workers=64
#
# Include this file from the end of postgresql.conf, so that its values win
# over the lines above it:
#   include 'andromeda-tuning.conf'
# ALTER SYSTEM and -c flags still win over it.

# TimescaleDB must be preloaded. pg_stat_statements keeps the statement
# statistics that pg_stat_statements.max below sizes, and is optional. Keep
# any other library your server preloads in this list.
shared_preload_libraries = 'timescaledb,pg_stat_statements'

# The broker creates these extensions in each tenant database, so the server
# must ship them: postgis, timescaledb, ltree, pg_trgm, btree_gist, btree_gin.

# Each value names where the image takes it from: the tuner, the entrypoint
# or the image's fixed policy.
# A line that starts with # gives the rule for a value that depends on a
# size the page was not given.
max_connections = 200                        # tuner
shared_buffers = 1GB                         # tuner
work_mem = 5734kB                            # entrypoint
maintenance_work_mem = 512MB                 # tuner
effective_io_concurrency = 256               # policy
max_worker_processes = 69                    # tuner
max_parallel_workers_per_gather = 1          # tuner
max_parallel_workers = 2                     # tuner
wal_buffers = 32768kB                        # entrypoint
checkpoint_completion_target = 0.9           # tuner
#max_wal_size = <the tuner's max_wal_size, at most 16384MB>  # entrypoint
#min_wal_size = <the tuner's min_wal_size, at most 2048MB>  # entrypoint
random_page_cost = 1.1                       # policy
effective_cache_size = 3GB                   # tuner
default_statistics_target = 100              # tuner
jit = off                                    # tuner
autovacuum_max_workers = 10                  # tuner
autovacuum_naptime = 1min                    # policy
default_toast_compression = lz4              # tuner
max_locks_per_transaction = 128              # tuner
timescaledb.max_background_workers = 64      # tuner
log_line_prefix = '%m [%p] %q%u@%d '         # policy
log_autovacuum_min_duration = 0              # policy
log_checkpoints = on                         # policy
log_lock_waits = on                          # policy
log_temp_files = 0                           # policy
log_min_duration_statement = 1s              # policy
lc_messages = 'C'                            # policy
track_activity_query_size = 8kB              # policy
track_io_timing = on                         # policy
track_wal_io_timing = on                     # policy
pg_stat_statements.max = 20000               # policy
maintenance_io_concurrency = 256             # policy
wal_compression = lz4                        # policy
checkpoint_timeout = 15min                   # policy
autovacuum_vacuum_cost_limit = 3000          # policy
reserved_connections = 3                     # policy
idle_in_transaction_session_timeout = 10min  # policy
idle_session_timeout = 1h                    # policy
client_connection_check_interval = 2s        # policy
timescaledb.telemetry_level = off            # policy
autovacuum_work_mem = 32MB                   # entrypoint
io_workers = 3                               # entrypoint
#temp_file_limit = <5 percent of the data volume>  # entrypoint

The image's own files

The lines the tuner writes into postgresql.conf, the file the entrypoint writes beside it, and the policy files in /etc/postgresql/andromeda.conf.d, as the image holds them.

The entrypoint runs timescaledb-tune with:

• --memory=4096MB
• --cpus=2
• --max-conns=200
• --max-bg-workers=64

postgresql.conf, the tuner's lines

max_connections = 200			# (change requires restart)
shared_buffers = 1GB			# min 128kB
work_mem = 8MB				# min 64kB
maintenance_work_mem = 512MB		# min 64kB
effective_io_concurrency = 256		# 1-1000; 0 disables issuing multiple simultaneous IO requests
max_worker_processes = 69		# (change requires restart)
max_parallel_workers_per_gather = 1	# limited by max_parallel_workers
max_parallel_workers = 2		# number of max_worker_processes that
wal_buffers = 16MB			# min 32kB, -1 sets based on shared_buffers
checkpoint_completion_target = 0.9	# checkpoint target duration, 0.0 - 1.0
max_wal_size = <60 percent of the WAL disk, rounded up to whole 16MB segments>
min_wal_size = <half the tuner's max_wal_size>
random_page_cost = 1.1			# same scale as above
effective_cache_size = 3GB
default_statistics_target = 100	# range 1-10000
jit = off				# allow JIT compilation
autovacuum_max_workers = 10		# max number of autovacuum subprocesses
autovacuum_naptime = 10		# time between autovacuum runs
default_toast_compression = lz4	# 'pglz' or 'lz4'
max_locks_per_transaction = 128		# min 10
timescaledb.max_background_workers = 64
timescaledb.last_tuned = <the time the tuner ran>
timescaledb.last_tuned_version = '0.19.0'

andromeda-derived.conf

# Written by the entrypoint on every start; edits are lost. Override with ALTER SYSTEM.
autovacuum_work_mem = 32MB
io_workers = 3
temp_file_limit = <5 percent of the data volume>
wal_buffers = 32768kB
max_wal_size = <the tuner's max_wal_size, at most 16384MB>
min_wal_size = <the tuner's min_wal_size, at most 2048MB>
work_mem = 5734kB

10-andromeda.conf

# Fixed server policy, the same on every machine. Included after the values
# timescaledb-tune writes and before postgresql.auto.conf, so ALTER SYSTEM
# overrides it. Sets nothing the tuner derives from memory, CPUs, connections
# or disk, which it would otherwise silently undo.

# Reporting and logging
# %d names the database, and with one database per tenant, the tenant.
log_line_prefix = '%m [%p] %q%u@%d '
log_autovacuum_min_duration = 0
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_min_duration_statement = 1s
lc_messages = 'C'

# Statistics
# Generated statements are longer than the 1kB default.
track_activity_query_size = 8kB
track_io_timing = on
track_wal_io_timing = on
# Entries are kept per database, so one database per tenant multiplies the
# statements tracked. About 8MB of shared memory.
pg_stat_statements.max = 20000

# Storage
# Costs for solid-state storage, the only storage the image is tuned for.
random_page_cost = 1.1
effective_io_concurrency = 256
maintenance_io_concurrency = 256

# Write-ahead log
wal_compression = lz4
# Fewer timed checkpoints, each followed by a burst of full-page images.
checkpoint_timeout = 15min

# Autovacuum
autovacuum_vacuum_cost_limit = 3000
# The launcher visits every database once per nap, so with one database per
# tenant its cost grows with the tenants. With 300 idle tenant databases the
# tuner's 10s keeps the launcher starting workers without pause, and 1min cuts
# its CPU by a third to a half. A delivery queue under steady load then peaks
# under 10% larger, with the same delivery latency.
autovacuum_naptime = 1min

# Connections and sessions
# Slots only superusers and members of pg_use_reserved_connections may take,
# so an administrative or maintenance role still connects once a broker's
# pools hold every other slot.
reserved_connections = 3
idle_in_transaction_session_timeout = 10min
# Closes a forgotten interactive or tooling session before it holds a slot,
# reserved ones included, indefinitely. The broker's pools close a connection
# idle for 30 seconds and a transaction pooler's default server_idle_timeout is
# 10 minutes, so neither reaches it.
idle_session_timeout = 1h
# Cancels a statement whose client has gone instead of running it to the end.
client_connection_check_interval = 2s

# TimescaleDB
timescaledb.telemetry_level = off

Run the image with these inputs

These start a new data directory with the limits and settings above. The data and WAL volumes are as large as the filesystems they live on.

docker run

export POSTGRES_PASSWORD=$(openssl rand -hex 24)

docker run -d --name postgresql \
  --restart unless-stopped \
  --memory 4g --cpus 2 --shm-size 1g \
  -e POSTGRES_USER=andromeda \
  -e POSTGRES_PASSWORD \
  -e POSTGRES_DB=andromeda \
  -v postgresql-data:/var/lib/postgresql \
  -p 127.0.0.1:5432:5432 \
  ghcr.io/mskvarc/postgresql:18.6-timescaledb2.31.0-postgis3.6.4-tsl

docker-compose.yaml

# Compose reads POSTGRES_PASSWORD from .env beside this file.
services:
  postgresql:
    image: "ghcr.io/mskvarc/postgresql:18.6-timescaledb2.31.0-postgis3.6.4-tsl"
    restart: unless-stopped
    environment:
      POSTGRES_USER: "andromeda"
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?set POSTGRES_PASSWORD in .env}
      POSTGRES_DB: "andromeda"
    mem_limit: 4g
    cpus: 2
    shm_size: 1g
    volumes:
      - postgresql-data:/var/lib/postgresql

volumes:
  postgresql-data: