Auto-tuning

autopg turns three facts about your deployment into a full Postgres configuration: how much memory the host has, how many CPUs it has, and what kind of workload the database serves. This page covers how that mapping works and how to steer it.

The inputs

Workload types

AUTOPG_DB_TYPE is the single most important setting. It tells the tuner what the database is shaped like, which changes connection defaults, memory ratios, parallelism, and statistics targets.

WEB is the default. If your traffic is mostly transactional with frequent writes, OLTP. If you run big analytical queries over a large dataset, DW.

What gets tuned

From those inputs autopg writes, among others:

• shared_buffers — Postgres's own cache, roughly a quarter of RAM.
• effective_cache_size — the planner's view of total cache, roughly three quarters of RAM.
• maintenance_work_mem — memory for VACUUM, index builds, and similar, about 1/16 of RAM and capped.
• work_mem — per-operation memory, derived from RAM and the connection count so concurrent sorts do not exhaust memory.
• max_connections — the workload default, unless you set AUTOPG_NUM_CONNECTIONS.
• max_wal_size / min_wal_size — WAL sizing scaled to the workload.
• checkpoint_completion_target — set to 0.9 to spread checkpoints.
• random_page_cost — 1.1 on SSD and SAN, 4.0 on spinning disks, so the planner costs index scans correctly for your storage.
• effective_io_concurrency — higher on SSD and SAN, low on HDD (Linux).
• Parallelism — max_parallel_workers, max_parallel_workers_per_gather, and max_parallel_maintenance_workers scale with the CPU count.
• huge_pages — set to try when the host has 32 GB or more.

Overriding detection

Detection is right most of the time, but you control the box. When you want the database to plan for less than the host has, for example because other services share it, set the inputs explicitly:

environment:
  AUTOPG_DB_TYPE: oltp
  AUTOPG_TOTAL_MEMORY_MB: 8192
  AUTOPG_CPU_COUNT: 4
  AUTOPG_NUM_CONNECTIONS: 200
  AUTOPG_PRIMARY_DISK_TYPE: SSD

Any value you set is used verbatim. Anything you leave unset is detected. For the precise tuning rules, the generated config matches pgtune for the same inputs.

An example

On a 32 GB, 8-core SSD host running a web workload, the generated config looks roughly like:

shared_buffers = '8GB'
effective_cache_size = '24GB'
maintenance_work_mem = '2GB'
work_mem = '20971kB'
max_connections = 200
max_wal_size = '4GB'
min_wal_size = '1GB'
checkpoint_completion_target = 0.9
random_page_cost = 1.1
huge_pages = 'try'

Restart the same image on a 128 GB host and every one of those values is recalculated on boot.