Linux Performance Optimization Guide

PostgreSQL 15 OLTP Latency and Connection-Pool Optimization Plan

Assumptions and Open Questions

I'm proceeding on the assumptions below. Each question notes which recommendation changes if the answer differs.

⚠ Caution — durability: Instance-store NVMe is wiped on stop/start, host retirement, or hardware failure. A primary on instance store must have a streaming replica on separate hardware plus continuous WAL archiving (pgBackRest/WAL-G) to S3. Verify this before any tuning. Several changes below involve restarts.

Working hypothesis

The symptom chain is most likely:

1. Something slows server-side transaction time at 14:00 (checkpoint/writeback storm, batch job, lock contention, or PgBouncer CPU saturation).
2. By Little's law, needed server connections = TPS × transaction hold time. At 3,800 TPS, 200 connections run out once the average hold time exceeds ~52 ms.
3. Clients queue in PgBouncer, time out, and reconnect.
4. The reconnect storm overflows a small listen backlog. PgBouncer's default listen_backlog is 128, which produces the SYN-flood warnings.

So the SYN warnings and pool exhaustion are probably downstream symptoms. The plan fixes both layers and uses measurement to find the upstream trigger.

1. Triage & Baseline

1.1 One-time host facts

# Identity & topology
uname -r; lsb_release -d; nproc; lscpu | grep -E 'Model name|NUMA node|Thread|Hypervisor'
nvme list                                   # "Amazon EC2 NVMe Instance Storage" vs EBS
lsblk -o NAME,SIZE,MOUNTPOINT,FSTYPE,MODEL
findmnt -no SOURCE,OPTIONS /var/lib/postgresql
cat /sys/block/nvme*n1/queue/scheduler      # expect [none]
cat /sys/kernel/mm/transparent_hugepage/{enabled,defrag}   # Ubuntu default: [madvise]
sysctl vm.swappiness vm.dirty_ratio vm.dirty_background_ratio vm.dirty_bytes \
       net.core.somaxconn net.ipv4.tcp_max_syn_backlog net.ipv4.ip_local_port_range
swapon --show
ps -eo pid,comm | grep -E 'ext4lazyinit'    # background inode-table zeroing after fresh mkfs on instance store = I/O thief
grep -E 'PageTables|HugePages_Total|Hugepagesize' /proc/meminfo

# Service limits actually in effect (not /etc/security/limits.conf — systemd ignores that)
for p in $(pgrep -x pgbouncer) $(pgrep -o -x postgres); do echo "== $p"; grep -E 'open files|processes' /proc/$p/limits; done
systemctl show pgbouncer postgresql@15-main -p LimitNOFILE,TasksMax

# ENA (AWS) per-instance allowance drops — invisible to the kernel stack
ethtool -S ens5 | grep -E 'allowance_exceeded'

-- Postgres config snapshot (run as superuser)
SELECT name, setting, unit, source FROM pg_settings WHERE name IN
('shared_buffers','huge_pages','shared_memory_size_in_huge_pages','max_connections','work_mem',
 'checkpoint_timeout','max_wal_size','checkpoint_completion_target','wal_compression',
 'effective_io_concurrency','random_page_cost','autovacuum_vacuum_cost_limit','autovacuum_vacuum_cost_delay',
 'track_io_timing','log_checkpoints','idle_in_transaction_session_timeout','synchronous_commit');

1.2 Peak-window capture script (run 13:45–16:15 for at least 3 days)

sar on Ubuntu samples every 10 minutes by default, which is too coarse for a 10–15 minute spike. This script captures 1–5 s resolution for everything, including the Postgres and PgBouncer views that decide the diagnosis.

sudo apt-get install -y sysstat iotop-c linux-tools-common linux-tools-$(uname -r)
sudo sed -i 's/^ENABLED=.*/ENABLED="true"/' /etc/default/sysstat
sudo systemctl edit sysstat-collect.timer   # add the two lines below → 1-minute sar history
#   [Timer]
#   OnCalendar=
#   OnCalendar=*:*:00
sudo systemctl restart sysstat sysstat-collect.timer

#!/usr/bin/env bash
# /usr/local/bin/peak-capture.sh   (cron: 45 13 * * * root /usr/local/bin/peak-capture.sh)
set -uo pipefail
D=/var/tmp/peak/$(date +%F); mkdir -p "$D"; cd "$D"
DUR=9000
PGB="psql -X -q -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -At -F,"
PG="psql -X -q -h /var/run/postgresql -U postgres -At -F,"

timeout $DUR vmstat -w -t 1                              > vmstat.log  &
timeout $DUR iostat -xmtz 1                              > iostat.log  &
timeout $DUR mpstat -P ALL 5                             > mpstat.log  &
timeout $DUR pidstat -h -u -d -w -l -C 'postgres|pgbouncer' 5 > pidstat.log &
timeout $DUR sar -n DEV,EDEV,TCP,ETCP,SOCK 5             > sar-net.log &
timeout $DUR sar -B -r -W 5                              > sar-mem.log &
timeout $DUR dmesg -Tw                                   > dmesg.log   &
nstat -n   # reset nstat history so the loop below prints deltas

end=$((SECONDS+DUR))
while (( SECONDS < end )); do
  ts=$(date +%T)
  { echo "== $ts"; ss -ltn '( sport = :5432 or sport = :6432 )'; ss -s | head -3; } >> ss.log
  { echo "== $ts"; nstat -z TcpExtListenOverflows TcpExtListenDrops TcpExtTCPReqQFullDoCookies \
      TcpExtTCPReqQFullDrop TcpExtSyncookiesSent TcpRetransSegs TcpExtTCPTimeouts TcpAttemptFails; } >> nstat.log
  { echo "== $ts"; grep -H . /proc/pressure/{cpu,memory,io} 2>/dev/null
    grep -E '^(Dirty|Writeback|PageTables|AnonHugePages):' /proc/meminfo
    grep -E '^(allocstall_normal|pgscan_direct|compact_stall|pgmajfault) ' /proc/vmstat; } >> mem-psi.log
  echo "$ts,$($PGB -c 'SHOW POOLS' | tr '\n' '|')"       >> pgb-pools.log
  $PG -c "SELECT '$ts', coalesce(wait_event_type,'CPU'), coalesce(wait_event,'running'), state, count(*)
          FROM pg_stat_activity WHERE backend_type='client backend' AND state <> 'idle'
          GROUP BY 2,3,4 ORDER BY 5 DESC"                  >> pg-waits.log
  $PG -c "SELECT '$ts', count(*) FILTER (WHERE state='idle in transaction'),
          max(now()-xact_start) FILTER (WHERE state<>'idle')
          FROM pg_stat_activity WHERE backend_type='client backend'" >> pg-xact.log
  sleep 5
done
wait
$PG -c "SELECT now(), * FROM pg_stat_bgwriter" > bgwriter-end.log

Interactive tools to use while the spike is live:

htop -d 10                                   # F2 → Display: "Detailed CPU time" to see st/irq/soft
top -H -b -n1 -o %CPU | head -30             # thread view: is one pgbouncer thread pinned at ~100%?
sudo iotop -oPab -d 5 -n 24                  # which PIDs write: checkpointer, walwriter, autovacuum, kworker/flush?
ss -ltn 'sport = :6432'                      # LISTEN: Recv-Q = current accept queue, Send-Q = backlog limit
ss -tan state time-wait | wc -l
journalctl -k --since "13:55" --until "16:15" | grep -Ei 'syn flooding|oom|hung_task|nvme|ext4|conntrack|blocked for'

1.3 Normal vs concerning values for this box (16 vCPU, 64 GB, local NVMe, OLTP)

2. Bottleneck Diagnosis

2.1 Decision tree

Work top-down using the capture from the spike window. The first branch separates PgBouncer-bound from Postgres-bound, because both produce the same symptom.

START: during the 14:00–16:00 spike…
│
├─ A. Is pgbouncer ≥ 80% of ONE core (pidstat) while Postgres backends are idle-ish
│     (SHOW POOLS: sv_idle > 0 but cl_waiting > 0)?
│     └─ YES → PGBOUNCER-CPU-BOUND. Fix: §3 P2 (so_reuseport multi-process). Stop here, retest.
│
├─ B. SHOW POOLS: sv_active == pool size AND cl_waiting > 0?
│     └─ YES → Postgres is holding connections too long. Go to C.
│         (Little's law: needed_conns = TPS × avg_xact_hold_time. 3,800 × 0.052 s ≈ 200.)
│
├─ C. What are active backends waiting on? (pg-waits.log, % of active samples)
│     ├─ CPU/running dominant + vmstat r > 24, us+sy > 80% ─────────────► CPU-BOUND (D1)
│     ├─ IO:DataFileRead dominant ──────────────────────────────────────► MEMORY/cache-bound (D2)
│     ├─ IO:WALSync / IO:WALWrite / LWLock:WALWrite dominant ───────────► I/O-BOUND (commit path) (D3)
│     ├─ IO:DataFileWrite / IO:BufFileWrite, Dirty > 2 GB, spike aligns
│     │   with a checkpoint start in the log ────────────────────────────► I/O-BOUND (writeback storm) (D3)
│     ├─ Lock:transactionid / Lock:tuple / Lock:relation ───────────────► APP CONTENTION (not kernel;
│     │                                                                    find blocker via pg_blocking_pids)
│     ├─ LWLock:BufferMapping / LWLock:LockManager ─────────────────────► TOO MANY ACTIVE BACKENDS (P1)
│     └─ Many "idle in transaction", wait=Client:ClientRead ────────────► APP holds txns open across
│                                                                          network round-trips (P3)
│
├─ D. Is the SYN-flood port 6432 or 5432, and does ListenOverflows rise only
│     AFTER cl_waiting rises?
│     ├─ After → reconnect storm is secondary. Fix backlog (N1) AND the root cause above.
│     ├─ Before / without pool pressure → NETWORK-BOUND accept path (D4):
│     │     check pgbouncer "Too many open files" in its log (LimitNOFILE), backlog=128,
│     │     ENA allowance counters, conntrack table full.
│
└─ E. Does the spike start at an exact wall-clock time every day (e.g. 14:00:00)?
      └─ YES → correlate with cron / pg_cron / pg_stat_statements top queries by total_exec_time
               for that window. A scheduled job is the trigger; tuning only buys headroom.

2.2 Per-resource confirmation (metric → threshold → confirmation)

3. Optimization Actions

Changes are ordered by impact ÷ risk. The Apply column says whether the change is online, needs a reload, needs a restart, or needs downtime.

Tier 1: Online, low risk, directly targets the symptoms

N1. PgBouncer listen backlog and file-descriptor limits (fixes SYN-flood on 6432)

The kernel caps the accept queue at min(app backlog, net.core.somaxconn). PgBouncer defaults to listen_backlog = 128, so raising sysctls alone does nothing.

# /etc/pgbouncer/pgbouncer.ini   [pgbouncer] section
listen_backlog = 4096        ; accept queue sized for reconnect bursts (default 128)
max_client_conn = 5000       ; clients wait cheaply in pgbouncer instead of being refused
tcp_keepalive = 1
tcp_keepidle = 60            ; reap dead app connections holding client slots
tcp_keepintvl = 10
tcp_keepcnt = 6

sudo systemctl edit pgbouncer
#   [Service]
#   LimitNOFILE=65536        # must exceed max_client_conn + server conns + margin; otherwise accept() fails → queue fills
sudo grep -i 'too many open files' /var/log/postgresql/pgbouncer.log   # check whether this already bit you

⚠ Caution: listen_backlog and LimitNOFILE only take effect on a PgBouncer restart, which drops connected clients. A plain reload is not enough. Do it in a low-traffic window, or use the P2 multi-process layout for rolling restarts.

N2. Kernel network sysctls

# /etc/sysctl.d/90-pg-oltp-net.conf        apply: sudo sysctl --system   (online)
net.core.somaxconn = 8192                  # ceiling for any listen() backlog (5.15 default 4096)
net.ipv4.tcp_max_syn_backlog = 8192        # half-open (SYN_RECV) queue; default is memory-scaled
net.ipv4.tcp_syncookies = 1                # keep ON as the safety net; the goal is to never need it
net.core.netdev_max_backlog = 16384        # only matters if softnet drops (see check below); harmless
net.ipv4.ip_local_port_range = 10240 65535 # more ephemeral ports if pgbouncer→pg or clients use TCP
net.ipv4.tcp_keepalive_time = 60           # detect dead peers in ~2 min instead of ~2 h 11 min
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 6
net.ipv4.tcp_slow_start_after_idle = 0     # pooled idle connections don't restart at cwnd=10 on burst

awk '{print $2}' /proc/net/softnet_stat | sort -u     # non-zero 2nd column = netdev backlog drops
sysctl net.netfilter.nf_conntrack_count net.netfilter.nf_conntrack_max 2>/dev/null  # if ufw/iptables loaded

If the SYN warning names 5432, Postgres's backlog is min(2 × MaxBackends, 10000), capped by somaxconn, so N2 is the fix there. Also move PgBouncer→Postgres traffic to the Unix socket (P1) so 5432 only carries admin and replication traffic.

M1. Memory writeback and swap sysctls (the most likely kernel-level cause of a periodic stall)

Defaults allow ~6 GB of dirty page cache before background flush and ~12 GB before foreground throttling. When a checkpoint's fsync hits that backlog, WAL fsyncs queue behind it and every commit stalls.

# /etc/sysctl.d/90-pg-oltp-vm.conf        apply: sudo sysctl --system   (online)
vm.dirty_background_bytes = 268435456   # 256 MB: start async flush early (replaces dirty_background_ratio=10)
vm.dirty_bytes = 1073741824             # 1 GB: cap dirty backlog so fsync never faces a GB-scale flush
vm.swappiness = 1                       # keep PG memory resident; still allows emergency swap
kernel.sched_autogroup_enabled = 0      # autogroup is for desktops; ensures fair CFS scheduling across backends

The *_bytes and *_ratio pairs are mutually exclusive. Setting bytes zeroes the ratio.

Conditional: apply only if allocstall/pgscan_direct grow during the spike.

vm.min_free_kbytes = 524288             # 512 MB free reserve → kswapd reclaims before backends hit direct reclaim

Kernel 5.15 trap: kernel.sched_migration_cost_ns and sched_min_granularity_ns are no longer sysctls (moved to /sys/kernel/debug/sched/ in 5.13). Tuning guides that set them in sysctl.conf fail silently on 5.15. Don't bother with them.

M2. Transparent Huge Pages

cat /sys/kernel/mm/transparent_hugepage/enabled   # Ubuntu default [madvise] is fine; if [always]:
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled   # online; avoids compaction stalls
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defrag

To persist, add transparent_hugepage=never to GRUB_CMDLINE_LINUX_DEFAULT in /etc/default/grub, then run update-grub. This takes effect at the next reboot. You don't need to reboot just for it.

D1. I/O scheduler and mount options

cat /sys/block/nvme1n1/queue/scheduler            # must be [none] for NVMe; any scheduler only adds latency

# /etc/udev/rules.d/60-nvme-sched.rules        apply: sudo udevadm control --reload && sudo udevadm trigger
ACTION=="add|change", KERNEL=="nvme[0-9]*n[0-9]*", ATTR{queue/scheduler}="none"

# /etc/fstab — PG data mount (instance store ⇒ nofail so boot doesn't hang if the volume is wiped)
UUID=<uuid>  /var/lib/postgresql  ext4  defaults,noatime,nodiscard,nofail,errors=remount-ro  0 2

sudo mount -o remount,noatime,nodiscard /var/lib/postgresql   # online
sudo systemctl enable --now fstrim.timer                        # weekly batch TRIM instead of inline discard

• noatime: eliminates metadata writes on reads.
• nodiscard: inline discard issues synchronous TRIMs that can add ms-scale latency to deletes and vacuum truncation.
• Keep barrier on (the default). Never use data=writeback or nobarrier on a primary.

S1. Postgres systemd limits

sudo systemctl edit postgresql@15-main
#   [Service]
#   LimitNOFILE=65536
#   TasksMax=infinity
# Takes effect at next PG restart (bundle with Tier 3).

Tier 2: PgBouncer and Postgres reload-level changes (targets root cause)

P1. Right-size the server pool

More active backends than about 3–4× cores makes things slower (LWLock contention, context switches, CPU cache thrash). Let PgBouncer queue instead.

# /etc/pgbouncer/pgbouncer.ini
pool_mode = transaction          ; ⚠ if currently "session": app-visible (SET, advisory locks, temp tables;
                                 ;   prepared statements need pgbouncer ≥1.21 + max_prepared_statements)
default_pool_size = 48           ; ~3× vCPU active backends per db/user pair
reserve_pool_size = 8
reserve_pool_timeout = 2
max_db_connections = 64          ; hard cap across all pairs
query_wait_timeout = 30          ; fail fast instead of indefinite pileup (default 120)
server_idle_timeout = 60

[databases]
billing = host=/var/run/postgresql port=5432 dbname=billing   ; Unix socket: no loopback TCP, no port exhaustion

Apply with sudo systemctl reload pgbouncer. Pool-size changes are online. If default_pool_size already exceeds 64 per database/user pair and tenants use separate users, size per pair so the sum stays at or below max_db_connections.

Sanity check against Little's law: 48 connections sustain 3,800 TPS only if the average transaction hold time is ≤ 12 ms. If pg_stat_statements plus app round-trips show more, the fix is shorter transactions (P3), not a bigger pool.

P2. PgBouncer CPU scale-out (only if decision-tree branch A fired)

; /etc/pgbouncer/pgbouncer-N.ini for N = 1..3, each with its own pidfile, logfile, unix_socket_dir
so_reuseport = 1                 ; kernel load-balances new connections on :6432 across processes
peer_id = N                      ; pgbouncer ≥1.19 so cancel requests route to the right process
; [peers] section listing all instances

Ubuntu 22.04 ships PgBouncer 1.16, which lacks peer_id. Install from the PGDG apt repo (apt.postgresql.org) to get it. Run each instance as a pgbouncer@N.service template unit.

⚠ Caution: This is a restart and architectural change. Test on staging first. It also enables rolling restarts later.

P3. Stop connection hoarding and add visibility

# /etc/postgresql/15/main/postgresql.conf       apply: SELECT pg_reload_conf();   (online)
idle_in_transaction_session_timeout = '15s'   # kills txns that hold a pooled conn while the app does HTTP etc.
lock_timeout = '5s'                           # app-level: prefer setting per role (ALTER ROLE app SET ...)
log_min_duration_statement = '250ms'
log_checkpoints = on
log_lock_waits = on
track_io_timing = on                          # gives blk_read_time/blk_write_time per statement
log_autovacuum_min_duration = '1s'

P4. Checkpoint and WAL smoothing (reload)

checkpoint_timeout = '15min'          # fewer, larger, smoother checkpoints (default 5min)
max_wal_size = '32GB'                 # stop WAL-volume-forced checkpoints mid-peak; size from WAL rate
min_wal_size = '4GB'
checkpoint_completion_target = 0.9    # already default in PG15; verify
wal_compression = lz4                 # PG15: less WAL bytes → fewer fsync MB (costs a little CPU)
checkpoint_flush_after = '256kB'      # default; ensures PG drives writeback incrementally (pairs with M1)
bgwriter_lru_maxpages = 400           # backends find clean buffers instead of writing dirty ones themselves
bgwriter_delay = '50ms'
effective_io_concurrency = 200        # NVMe
random_page_cost = 1.1                # NVMe; improves plan choice (test with EXPLAIN on top queries)
autovacuum_vacuum_cost_limit = 2000   # NVMe can absorb it; prevents bloat accumulating into peak
autovacuum_vacuum_cost_delay = '2ms'

Size max_wal_size from the peak WAL rate:

SELECT pg_current_wal_lsn();  -- run twice, 5 min apart during peak; diff with pg_wal_lsn_diff()

Set max_wal_size to at least 3× the WAL generated per checkpoint_timeout at peak. Ensure pg_wal has room: 32 GB plus headroom on a 300 GB device.

⚠ Caution: A larger max_wal_size and checkpoint_timeout means longer crash recovery (minutes). That is acceptable with a replica for failover.

Tier 3: Requires a Postgres restart (schedule a maintenance window)

M3/M4. Explicit huge pages for shared_buffers

With 200 backends each mapping a 16 GB shared_buffers in 4 KB pages, page tables can reach roughly 200 × 32 MB ≈ 6 GB. That inflates sy time and TLB misses.

SHOW shared_memory_size_in_huge_pages;   -- PG15 computes the exact number for you

# /etc/sysctl.d/90-pg-oltp-hugepages.conf
vm.nr_hugepages = 8400                   # = value above + ~3% headroom (example for shared_buffers=16GB)

# /etc/postgresql/15/main/postgresql.conf
shared_buffers = '16GB'                  # 25% of RAM; OS cache covers the rest
huge_pages = on                          # 'on' fails loudly if pages aren't available; 'try' silently falls back
effective_cache_size = '46GB'
max_connections = 120                    # pool max (64) + reserve + admin/replication/monitoring
work_mem = '16MB'                        # × active backends × sort nodes must fit; watch temp_files
maintenance_work_mem = '1GB'

sudo sysctl --system && grep HugePages_ /proc/meminfo   # confirm HugePages_Total reached target
sudo systemctl restart postgresql@15-main
grep HugePages_ /proc/meminfo                            # HugePages_Rsvd/Free should reflect PG use

⚠ Caution: Requires a PG restart (downtime ~seconds to a minute). Reserve huge pages right after boot or when memory is unfragmented, because allocation can partially fail on a long-running host. If HugePages_Total falls short, huge_pages=on refuses to start PG. Keep a huge_pages=try fallback line ready. Huge pages are removed from general memory, so 16.4 GB is no longer available as page cache.

Tier 4: Downtime or architectural (highest impact if I/O-bound)

D3. Put pg_wal on the second NVMe device (m5d.4xlarge has 2 × 300 GB)

This isolates WAL fsync latency from checkpoint and data-file writeback. It is the single most effective fix if IO:WALSync dominates.

sudo mkfs.ext4 -E lazy_itable_init=0,lazy_journal_init=0 /dev/nvme2n1   # no ext4lazyinit background I/O later
# fstab: UUID=<uuid> /var/lib/postgresql/wal ext4 defaults,noatime,nodiscard,nofail 0 2
sudo systemctl stop postgresql@15-main
sudo rsync -a /var/lib/postgresql/15/main/pg_wal/ /var/lib/postgresql/wal/pg_wal/
sudo mv /var/lib/postgresql/15/main/pg_wal /var/lib/postgresql/15/main/pg_wal.old
sudo ln -s /var/lib/postgresql/wal/pg_wal /var/lib/postgresql/15/main/pg_wal
sudo chown -R postgres:postgres /var/lib/postgresql/wal
sudo systemctl start postgresql@15-main

⚠ Caution: This requires full downtime. Losing either instance-store device loses the database, so replica and archiving are mandatory. Better alternative: fail over to a replica built with this layout, which gives near-zero downtime.

Also consider (not kernel)

• Route reporting and analytics queries to the replica.
• Reschedule the 14:00 batch.
• Batch billing writes.
• If CPU-bound after all the above, move to a 32 vCPU instance size.

4. Validation

4.1 Production baseline ("before")

Run peak-capture.sh for 3 consecutive weekdays before any change and record the headline numbers. Then apply one tier per day and re-capture, so each gain is attributable to a specific change.

# Headline extraction per day
grep -c 'SYN flooding' /var/tmp/peak/$DAY/dmesg.log
awk '/ListenOverflows/{s+=$2} END{print "listen_overflows",s}' /var/tmp/peak/$DAY/nstat.log
# PgBouncer: peak cl_waiting and maxwait parsed from pgb-pools.log
# App APM: p99 API latency 14:00–16:00 (source of truth for the SLO)

-- DB-side p99 via pg_stat_statements: reset at 13:55, read at 16:00
SELECT pg_stat_statements_reset();   -- 13:55
SELECT queryid, calls, round(mean_exec_time::numeric,2) mean_ms, round(max_exec_time::numeric,1) max_ms,
       round(stddev_exec_time::numeric,1) sd_ms, round((shared_blk_read_time+shared_blk_write_time)::numeric) io_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

4.2 Load test on a clone (proves the gain before the production peak)

Setup:

• Restore last night's backup onto an identical instance type. Instance store must match, because EBS numbers are not comparable.
• Put PgBouncer on the DB host, exactly as in production.
• Drive load from a separate client host so the TCP/SYN path is exercised.

Workload fidelity:

• Best option: replay captured production traffic (log_min_duration_statement=0 for 15 minutes off-peak, then pgreplay-go at 3× speed).
• Otherwise, build pgbench scripts from the top 10 pg_stat_statements entries, weighted by calls, with realistic random tenant and invoice IDs.

# Test 1 — steady peak: fixed arrival rate (open-loop), so latency includes queueing, like the real API.
# 400 clients > pool size deliberately, to exercise pgbouncer queueing.
pgbench -h db-clone -p 6432 -U bench -n \
  -f read_mix.sql@8 -f write_invoice.sql@2 \
  -c 400 -j 8 -R 3800 -T 1800 -P 10 \
  --latency-limit=150 -l --log-prefix=run_$(date +%s) billing

# p50/p95/p99/p99.9 from per-txn logs (3rd field = latency µs, measured from scheduled start → includes queue wait)
cat run_*.log* | awk '{print $3}' | sort -n | awk '{a[NR]=$1} END{
  printf "n=%d p50=%.1fms p95=%.1fms p99=%.1fms p999=%.1fms\n", NR,
  a[int(NR*.50)]/1000, a[int(NR*.95)]/1000, a[int(NR*.99)]/1000, a[int(NR*.999)]/1000}'

# Test 2 — reconnect storm: new TCP connection per transaction; reproduces the SYN-flood path.
nstat -n
pgbench -h db-clone -p 6432 -U bench -n -C -S -c 800 -j 16 -R 1500 -T 300 billing
nstat -z TcpExtListenOverflows TcpExtListenDrops TcpExtSyncookiesSent TcpExtTCPReqQFullDoCookies
dmesg -T | grep -c 'SYN flooding'

# Test 3 — checkpoint under load: force the worst case mid-Test 1
psql -c 'CHECKPOINT' billing    # at T+900s; p99 in the following 60 s must stay < 150 ms

Run Tests 1–3 against the untuned clone first to reproduce the 900 ms p99. If the clone doesn't reproduce it, the workload model is missing the trigger (see Q5). Then apply tiers one at a time and repeat.

4.3 Pass criteria

Prioritized Checklist

General rollback discipline:

• Before each tier, run cp -a on /etc/postgresql/15/main, /etc/pgbouncer, and /etc/sysctl.d to /root/tuning-backup/$(date +%F)/.
• Change one tier per day.
• If p99 or any pass metric regresses in the next peak window, revert only that tier.