Configuration Tuning

pg tune turns a machine’s hardware specs and a workload class into a coherent set of postgresql.conf parameters. It works against both kinds of deployment pgcli manages: plain instances (written through the managed tuning block in postgresql.conf) and Patroni HA clusters (written to the DCS via edit-config, which rolls the change out to every member).

By default it only prints a recommendation — nothing is touched. --apply writes it; --revert removes a previously applied tune.

When to use it

• A new instance after install — the container defaults are safe, not fast. One pg tune -i <name> --apply gets the memory partition, WAL sizing and planner cost constants into the right ballpark for the hardware.
• After an up/down-size — RAM and CPU counts changed; re-run the same command and the derived values follow.
• When pg inspect flags settings — the inspection report can point at undersized shared_buffers / low cache-hit ratios; pg tune is the fix-it path.

Inputs

The specs default to a probe of the host the command runs on (total RAM from /proc/meminfo on Linux, CPU count from the runtime). Overrides:

Workload choice in one line: mostly short queries from an app → web/oltp; analytics and big scans → dw; a single-user app co-located with the DB → desktop; unsure → mixed.

The target server itself is probed for server_version_num and for every parameter’s current value, so the table shows CURRENT vs RECOMMENDED side by side and version-gated parameters (jit, io_method, …) only appear where they exist.

Applying

Managed instances (-i <name>)

pg tune -i myinst                       # recommendation table only
pg tune -i myinst --apply               # write it into pgcli's managed block
pg tune -i myinst --revert              # remove the applied tune again

--apply records the parameters in the instance’s config and writes them into the managed tuning block of postgresql.conf. The block is re-applied on every pg start, so the tuning survives restarts and reinstalls of the container. Parameters set via ALTER SYSTEM (i.e. present in postgresql.auto.conf) are skipped with a count — auto.conf always wins.

Changes to postmaster-level parameters (shared_buffers, wal_buffers, max_connections, …) need a restart: interactively you’re asked y/N; non-interactively pgcli only prints the pg stop && pg start hint and never bounces the database on its own. Everything else is applied with a config reload immediately.

Patroni clusters (--ha <scope>)

pg tune --ha app                        # read specs from the leader, print table
pg tune --ha app --apply --auto-restart # write to the DCS; Patroni rolls it out
pg tune --ha app --revert --auto-restart

Here the parameters go into the cluster DCS config via patronictl edit-config — the only sanctioned way to change postgresql.conf values in a Patroni world. Patroni then applies them on every member, rolling restarting for postmaster-level changes. Because that is disruptive, both --apply and --revert ask for confirmation first; --auto-restart skips the prompt (for scripts). If you decline, nothing is written to the DCS.

The recommendation table is computed from the host pgcli runs on — on a cross-host cluster the members may differ, so pass --ram/--cpus matching the smallest member.

Validation and --apply-conf

pg tune -i myinst --apply-conf /tmp/tune.conf              # save the recommendation…
$EDITOR /tmp/tune.conf                                     # …hand-edit it…
pg tune -i myinst --apply --apply-conf /tmp/tune.conf      # …apply the file instead

--apply-conf names a key = value file (postgresql.conf style: # comments, quoted values, units allowed). Without --apply the file is written (the header line records the inputs so the result is reproducible); with --apply the file is read and becomes the parameter source.

Every apply path validates first. Each pair is checked against the target’s pg_settings: unknown names (e.g. io_method on PG 15), non-boolean bools, values outside an enum’s set, and integers/reals out of [min_val, max_val] are listed per-parameter and the apply aborts wholesale — a partial write is the worst outcome for a tuning batch.

--dsn: recommendations only

pg tune --dsn postgres://user:pass@host:5432/db

Remote targets are probe-and-suggest only; --apply/--revert are refused, since writing a stranger’s server from a tuning heuristic is a decision pgcli will not make for you.

--web-tune: the interactive site

pg tune --web-tune                  # start server on http://127.0.0.1:10864
pg tune --web-tune --listen :8080   # custom port
pg tune --web-tune --open           # start server and open browser

Starts an HTTP server that serves the interactive tuning calculator. The page has a form for every input (version, OS, workload, RAM+unit, CPUs, connections, storage, db size), live recompute, and postgresql.conf / ALTER SYSTEM output with a copy button. No network access, no dependencies; the host’s RAM/CPU/OS prefill the form. The embedded calculator is a JS port of the Go algorithm; the page ships Go-computed golden fixtures and self-checks them on load, showing a red banner if the two implementations ever disagree.

Press Ctrl+C (or send SIGTERM) to stop the server gracefully.

How the numbers are derived

The formulas are faithful and deterministic (internal unit kB, flooring at each derivation step):

• shared_buffers = RAM/4 (desktop: /16); effective_cache_size = 3/4 RAM.
• maintenance_work_mem = RAM/16 (dw: /8), capped at 8 GB (2047 MB on Windows ≤PG17).
• work_mem = (RAM − shared_buffers) / ((max_connections + max_worker_processes) × 3), then scaled by workload (dw/mixed ½, desktop ⅙) and db-size (fits ×1.3, larger ×0.9), floor 4 MB.
• wal_buffers = 3% of shared_buffers, clamped to [32 kB, 16 MB].
• random_page_cost 4 for hdd and for dw-on-SSD (with a warning about catastrophic index scans), 1.1 otherwise; effective_io_concurrency by storage class (Linux only).
• Parallelism from CPU count (omitted below 4 cores); autovacuum_max_workers at ≥16/≥32 cores; version gates: jit=off ≥12, wal_compression on/lz4 ≥10/≥15, io_method/io_workers ≥18 (io_method is always worker — io_uring additionally needs a --with-liburing build and kernel support, and a mismatch makes the server fail to start, so it is not auto-recommended), desktop-only wal_level=minimal + max_wal_senders=0.

Per-parameter caveats (lz4 compile flag, the DW cost warning) are printed inline in the table; absurd machine sizes (<256 MB, >100 GB RAM) get a global warning.