pg_optimize.sh applies a proven PostgreSQL performance configuration to a server and certifies that it works. It encodes the findings of the analytics-engine-db-001 benchmark and tuning project (July 2026): six settings that cured two diagnosed factory-default problems (an undersized database cache and chronic forced checkpointing), scaled automatically to the target machine's RAM and disk. It is integrated into SISA (Server Initial Setup Application) as a role script, making PostgreSQL optimization one menu choice in the standard provisioning flow. What took two weeks to discover is applied and verified in about an hour.
Root access, and at least 5 GB of free disk. The PostgreSQL role/installation should be completed before running this one — the script refuses to run if PostgreSQL is not reachable. The full run installs fio via apt if missing. Because the full mode includes a 10-minute unattended stress test, launch SISA inside a tmux session when choosing it.
The standard path is through SISA:
sudo ./main.sh # launch SISA
→ option 11 # pg_optimize — PostgreSQL tuning & certification
→ option 1 or 2 # pg_optimize's own mode menu (below)
SISA option 11 hands control to pg_optimize.sh, which then presents its two modes:
Option 1, Full Run (~1 hour, mostly unattended, recommended): measures the machine's disk speed limit, applies the six settings scaled to its RAM and disk, then runs a 10-minute stress test and prints a PASS/FAIL certificate proving the tuning works on this machine.
Option 2, Apply Settings Only (~2 minutes): skips all measurement and testing — computes the settings for this machine, applies them, restarts PostgreSQL, and verifies they took. No certificate; use when the recipe is already trusted.
Step 1 fingerprints the machine: RAM, cores, free disk, PostgreSQL version, disk type. It refuses to continue without root or a reachable PostgreSQL, and records any pre-existing non-default settings before touching anything.
Step 2 measures the disk's fsync latency with fio — the time the disk takes to confirm a durable write. Every PostgreSQL commit waits on one fsync, so this number is the machine's physical single-stream commit ceiling (roughly 1 second ÷ fsync latency). It is measured first so results can be judged against this machine's actual capability.
Step 3 computes and applies the six settings, scaled to the detected hardware, using ALTER SYSTEM (fully reversible with ALTER SYSTEM RESET <name>):
| Setting | Rule | Rationale (measured in the project) |
|---|---|---|
shared_buffers |
25% of RAM | fixes the 84% cache-hit-ratio disease |
effective_cache_size |
70% of RAM | honest planner estimate of total caching |
max_wal_size |
6 GB if ≥12 GB free, 4 GB if ≥8, 2 GB if ≥5, refuse below | cures forced checkpointing within the disk budget |
checkpoint_timeout |
15 min | fewer, calmer scheduled checkpoints |
checkpoint_completion_target |
0.9 | spreads checkpoint writes into a trickle |
bgwriter_lru_maxpages |
400 | the background writer's quota, previously exhausted 33–42k times per campaign |
PostgreSQL is then restarted (required for shared_buffers) and each setting is verified individually with SHOW; any mismatch aborts the script loudly.
Step 4 certifies the result: a scale-50 pgbench database is created, statistics are reset, and a 10-minute 16-client write stress test runs. Two health signals are then compared against the project's known-good signatures — forced checkpoints must be at most 1 (a stock server shows 4–5 in this test), and the cache hit ratio must be at least 93% (stock shows ~84%). The practice database is dropped afterward.
Step 5 prints CERTIFICATE.txt: the machine's specs, its measured fsync ceiling, the applied settings, the stress-test scores, and a PASS or FAIL verdict. FAIL means this machine deviates from the known-good behavior, and the failing metric indicates where to investigate. The certificate ends with the three application rules the project proved matter more than any setting: connection pools capped near 16, inserts batched at 100+ records per transaction, and wide-row tables for multi-metric records.
Every run writes an evidence directory to ~/pg-optimize-results/<timestamp>/ containing the fingerprint, the before/after configuration snapshots and their diff, the fio and pgbench logs, the run log, and the certificate. Exit code 0 means success (PASS in full mode); exit code 2 means the smoke test failed certification; exit code 1 means a setup or apply step failed — SISA can surface these codes to report role success or failure in its own flow.
The scaling rules were validated on a 1-core / 2 GB machine; on much larger servers the first certificate deserves a skeptical read rather than blind trust, and the project's documented caveat travels with the config: when the working set exceeds total RAM, 25% for shared_buffers can trade a few percent of disk-bound read throughput for its gains (measured at −6% on the reference machine — dissolved entirely by a RAM upgrade). The script overlays settings on whatever configuration exists; it records but does not remove prior tuning. Within a SISA provisioning sequence, run it after the PostgreSQL installation role and before loading production data. Rollback of everything it set: ALTER SYSTEM RESET each of the six names, then restart PostgreSQL.