All articles
Engineering14 min read

Tuning PostgreSQL 18 on Kubernetes: tracing a 35% speedup illusion to two throttled hosts

Eight benchmark rounds on CloudNativePG and PostgreSQL 18.1 uncovered two physical nodes oscillating down to 1.2 GHz, showed why commit_delay cut write throughput by 21%, halved WAL writes with zstd, lifted reads 21% with prepared statements, and mapped the 66k TPS single-instance plateau.

  • Leaflow Engineering· Leaflow Team

While benchmarking a CloudNativePG cluster (PostgreSQL 18.1, one primary and two replicas) on Kubernetes, we raised the per-instance CPU quota from eight to sixteen cores. The rolling reschedule produced a counterintuitive split: primary throughput fell, while read-only replica throughput jumped 35%.

Container metrics showed PostgreSQL consuming only 8.2 cores at peak load. An unsaturated workload cannot gain 35% throughput simply by moving from eight to sixteen cores. Comparing host placement before and after the reschedule explained the illusion right away: the primary had landed on a throttled host while a read replica moved off a throttled host onto a healthy one. Tracing from cpufreq down to model-specific frequency registers (MSR) uncovered two physical hosts idling back to 1.2 GHz and dropping clocks repeatedly under sustained load.

Fixing the benchmark harness before tuning

We ran eight pgbench rounds across two phases: three early exploratory runs labeled R1 to R3 in the tables and charts below, followed by five controlled experiments labeled E1 to E5 after standardizing the harness.

Those first three exploratory runs (R1 to R3) suffered from a flawed methodology: every parameter change ran a fresh pgbench -i initialization followed by a single sample. During an early single-client stability probe against a replica, three runs with identical settings swung across 1,577, 1,676, and 3,531 TPS, a 2.2x spread (excluded from the high-concurrency summary table below). No tuning decision made inside that noise floor holds up.

Starting with the first controlled experiment (E1), we enforced three rules:

  • Run pgbench scale=200 once and reuse the exact same persistent dataset across every round.
  • Warm up shared_buffers after every restart, run each scenario twice, and record the median.
  • Use pgbench (pure index lookups and updates) strictly for CPU, fsync, and protocol overhead, and test sequential scans separately for storage and asynchronous I/O.

Run-to-run variance immediately tightened to 1% to 3%, making every subsequent delta readable.

Same CPU and microcode, 2.34x slower single-core benchmark

To rule out PostgreSQL and Kubernetes entirely, we ran a single-threaded CPU-only compute benchmark across all five physical nodes and recorded loaded clocks (sorted fastest to slowest below; the loaded frequencies are observations taken during that benchmark and differ slightly in sampling cadence from the pinned-core taskset -c 2 traces later):

Node (anonymized)CPU modelMicrocodeBIOS versionSingle-thread benchmarkLoaded frequencyStatus
node-2E5-2680 v30x492.16.0 (2022)481 ms2,711 MHzHealthy
node-1E5-2680 v40xb000040-485 ms2,953 MHzHealthy
node-3E5-2680 v30x49-550 ms2,500 MHzHealthy (base clock, no turbo)
node-4E5-2698 v40xb0000402.19.0 (2023)900 ms1,868 MHzOscillating
node-5E5-2680 v30x492.11.0 (2019)1,127 ms1,268 MHzOscillating

node-2 and node-5 share the same E5-2680 v3 processor, the same 0x49 microcode, and the same Linux 6.12.73 kernel, yet single-core runtime differed by 2.34x (481 ms vs 1,127 ms).

Single-thread compute benchmark latency across five nodes and pinned-core MSR frequency sampling
Single-thread compute benchmark times across all five hosts and taskset -c 2 MSR multiplier sampling (labels in Chinese)

We then ruled out eleven hypotheses across three layers (three hardware thermal and power checks, six OS and firmware baseline checks, and two direct OS overrides, matching the funnel diagram below):

Checking thermal throttling, RAPL power caps, and scaling governors
cat /sys/devices/system/cpu/cpu0/thermal_throttle/core_throttle_count
grep -H . /sys/class/powercap/intel-rapl:*/constraint_*_enabled
cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
ipmitool dcmi power reading
  • Layer 1: Hardware thermals and power (3 ruled out):
    • Thermal throttling: core_throttle_count = 0, and the slow hosts ran cooler (52°C) than the healthy hosts (55°C).
    • RAPL power limits: every intel-rapl constraint reported enabled = 0.
    • Out-of-band power cap: ipmitool returned Power cap set feature is not enabled.
  • Layer 2: OS and firmware baseline (6 ruled out):
    • All five hosts matched on kernel version (6.12.73-amd64), boot parameters (no intel_pstate= or cpufreq= overrides), all-core governor = performance, cpupower-performance.service and irqbalance, plus identical CPU silicon and microcode between node-2 and node-5.
  • Layer 3: Direct OS overrides (2 ruled out):
    • Switching intel_pstate from passive to active on node-4 (E5-2698 v4) slowed the benchmark by 9.4% (reverted immediately).
    • Writing 3,300,000 to scaling_min_freq across all 48 hardware threads on node-5 succeeded without error, yet idle clocks still dropped to 1.2 GHz with zero runtime improvement (also reverted).
Eleven hypotheses eliminated layer by layer before pointing to BIOS System Profile
Layer-by-layer elimination of eleven thermal, power, OS, and driver hypotheses leading to BIOS firmware control

Pinning a busy loop to CPU 2 with taskset -c 2 while sampling scaling_cur_freq alongside rdmsr (0x199 IA32_PERF_CTL for requested multiplier and 0x198 IA32_PERF_STATUS for actual hardware multiplier) exposed the underlying behavior:

Pinned-core load with live frequency and MSR multiplier sampling
taskset -c 2 yes > /dev/null &
for i in 1 2 3 4 5; do
  freq=$(cat /sys/devices/system/cpu/cpu2/cpufreq/scaling_cur_freq)
  req=$(( (0x$(rdmsr -p 2 0x199) >> 8) & 0xff ))
  act=$(( (0x$(rdmsr -p 2 0x198) >> 8) & 0xff ))
  echo "sample $i: ${freq} kHz | req=${req} | act=${act}"
  sleep 1
done

Sample output:

MSR sampling on throttled node-5 vs healthy node-2
# node-5 (throttled host)
idle cpu2 = 1,200,062 kHz
sample 1: 2,499,976 kHz | req=28 | act=25
sample 2: 3,113,208 kHz | req=33 | act=29   # brief single-core turbo burst (non-atomic reads)
sample 5: 1,199,946 kHz | req=12 | act=12   # dropped to minimum P-state while saturated

# node-2 (healthy host)
idle cpu2 = 2,900,021 kHz                   # pinned high even when idle
samples 1-5: 2,883,520 to 2,899,999 kHz | req=33 | act=29

Two hardware details stand out here:

  • Healthy node-2 holds a steady requested multiplier of 33 (3.3 GHz) and an actual multiplier of 29 (2.9 GHz), which is the all-core turbo bin of an E5-2680 v3 with deep C-States disabled. With deep C-States active, node-5 can briefly spike to 3.11 GHz on one core, but spends most of its time hovering near 1.2 GHz as if running powersave.
  • Seeing req=12 on sample 5 under the performance governor means the OS request itself was clamped, typically because firmware tightened bios_limit via ACPI _PPC. We missed inspecting /sys/devices/system/cpu/cpu2/cpufreq/bios_limit during this pass and will verify it during the next maintenance window.

Once every writable OS interface was ruled out, the most likely root cause is Dell's default BIOS System Profile left on Performance Per Watt (DAPC), which also matches the BIOS version difference across hosts. ipmitool cannot change this option, and because production maintenance windows restrict reboots, we documented the Dell Redfish verification and update path for the scheduled window (on Dell 13G servers, PerfOptimized manages sub-attributes automatically; overriding sub-fields such as ProcCStates individually requires setting SysProfile to Custom):

Checking and updating Dell BIOS System Profile over Redfish
# 1. Inspect current BIOS attributes to confirm whether SysProfile is DAPC
curl -sk -u <user>:<pass> \
  https://<idrac-host>/redfish/v1/Systems/System.Embedded.1/Bios | jq '.Attributes | {SysProfile, ProcPwrPerf, ProcCStates}'

# 2. Switch to PerfOptimized and schedule a reboot job for the maintenance window
curl -sk -u <user>:<pass> -X PATCH \
  https://<idrac-host>/redfish/v1/Systems/System.Embedded.1/Bios/Settings \
  -H 'Content-Type: application/json' \
  -d '{"Attributes":{"SysProfile":"PerfOptimized"}}'

curl -sk -u <user>:<pass> -X POST \
  https://<idrac-host>/redfish/v1/Managers/iDRAC.Embedded.1/Jobs \
  -H 'Content-Type: application/json' \
  -d '{"TargetSettingsURI":"/redfish/v1/Systems/System.Embedded.1/Bios/Settings"}'

When a cluster co-locates distributed block storage on the same physical hosts, clock throttling hits more than database pods: storage daemon checksumming and encoding paths are single-core sensitive as well, raising write latency across the entire pool.

Eight benchmark rounds: what broke and what held up

Below is the complete eight-round pgbench record (c=32 and c=64 denote concurrent client connections). R1 to R3 are the early exploratory runs before dataset fixation and carry high variance, while E1 to E5 are controlled experiments:

RoundPhaseKey changeWrite c=32Write c=64Primary read c=32Primary read c=64Replica read c=64Notes
R1UnfixedInitial baseline (max_wal_size=8GB)4,3186,21958,42955,91575,619High-noise baseline
R2Unfixedmax_wal_size=24GB + zstd + aggressive bgwriter4,1136,02255,65255,52274,602Major WAL I/O drop, lower low-concurrency reads
R3UnfixedReverted bgwriter to defaults3,9125,81952,24954,78866,557High-noise phase, kept conservative defaults
E1FixedControlled baseline (8 vCPU)3,8786,16057,20455,02273,155Variance tightened to 1%-3%
E2FixedEnabled commit_delay=1700µs3,0645,68857,55054,52870,014Write c=32 dropped -21%, reverted at once
E3FixedScaled CPU from 8 to 16 cores-----Reschedule moved primary onto slow host; invalidated
E4FixedAffinity off slow hosts + 16 vCPU3,5855,61552,64455,95198,859Replicas +35% from leaving slow hosts
E5FixedScaled CPU back to 12 cores3,7945,66551,64152,83895,686Matches 16 cores, confirming CPU is not the bottleneck

Both write and primary read numbers in E4 and E5 came in roughly 7% to 10% below E1 (for example, 51,641 vs 57,204 on c=32 primary reads). Checking pod placement explained it: E4 and E5 placed the primary on non-turbo node-3 (2,500 MHz), whereas E1 ran on node-1 (2,889 MHz during that run, roughly a 13% clock gap between healthy hosts).

Eight-round pgbench OLTP throughput evolution across write and read workloads
Eight rounds of pgbench OLTP throughput: commit_delay regressed writes by 21% in E2, while moving off throttled hosts lifted replicas 35% in E4

These eight rounds disproved one popular tweak (commit_delay), ruled out two newer features that did not fit the workload (PostgreSQL 18 AIO and HugePages), and left aggressive bgwriter tuning reverted to conservative defaults.

Regression under commit_delay: write throughput dropped 21%

Running pg_test_fsync directly against the separately mounted WAL volume (backed by three-replica distributed block storage) exposed the write floor:

Measuring dedicated WAL volume fsync latency
pg_test_fsync -f /var/lib/postgresql/wal/test_fsync.out
Sync methodAverage latencyops/secRelative to default
fdatasync (PG default)3,445 µs290Fastest
open_datasync3,499 µs286Tied
open_sync10,989 µs913.2x slower
fsync13,871 µs724.0x slower

Dividing the measured numbers makes the bottleneck plain:

  • Single-client writes measured 259-262 TPS (3.85 ms per transaction), matching the 3.45 ms fdatasync ceiling. Single-client writes are bound directly by volume fsync latency.
  • At c=64, writes reached 6,160 TPS. Dividing 6,160 by 290 fsync/s gives roughly 21 transactions per flush, meaning PostgreSQL was already batching about 21 commits naturally inside each 3.45 ms flush window.

Adding an extra 1,700 µs commit_delay simply inserted 1.7 ms of artificial wait time, cutting c=32 write throughput by 21%.

It also clarifies the cluster-wide write amplification math: 3.45 ms is the synchronous latency of a single WAL volume committing across three storage replicas plus two storage-network round trips, independent of PostgreSQL streaming replication. Across one primary and two replicas using asynchronous streaming replication, every committed transaction ultimately writes 3 PostgreSQL instances × 3 block-storage replicas = 9 persisted copies. That does not block the primary's asynchronous COMMIT, yet it triples background IOPS contention on the shared storage pool and indirectly raises flush latency.

pg_test_fsync latency results and 3x3 cluster write amplification topology
Single-volume pg_test_fsync latency measurements alongside the 3x3 write amplification path across streaming replication and distributed storage

Skipping PostgreSQL 18 AIO (io_uring) and HugePages

Our container image ships with liburing linked and io_method=io_uring ready to enable, yet testing led us to leave io_method, io_workers, and io_combine_limit at defaults:

  • PostgreSQL 18 asynchronous I/O covers sequential scans, bitmap heap scans, and VACUUM, not index point lookups. Because pgbench runs entirely through indexes, AIO cannot help it.
  • The active working set fits comfortably inside 4 GB of shared_buffers. Even when we built an 11 GB table specifically to force sequential storage reads, the second and third scans served straight from the container's Linux page cache (1.8 s wall time, only 154 ms in actual read I/O, effectively measuring 6 GB/s of memory bandwidth).

We also left HugePages disabled because each host reserved only 2 GiB, which is smaller than our 4 GB shared_buffers.

As for the aggressive bgwriter settings tried during exploratory run R2, the noisy early harness (and noisy R3 revert in the table above) prevents calling it a strict falsification, yet low-concurrency reads did dip 13% to 19% with no write gain, so we kept PostgreSQL defaults (bgwriter_delay=200ms, lru_maxpages=100, lru_multiplier=2.0).

Three changes that delivered measurable gains

Raising max_wal_size from 8 GB to 24 GB with zstd WAL compression

The initial baseline (R1) used max_wal_size = 8GB, which filled up continuously during benchmarks, firing 30 forced WAL checkpoints and triggering heavy Full Page Writes (FPW). Raising max_wal_size to 24 GB and turning on wal_compression = zstd cut write I/O sharply under the same load:

  • Forced WAL checkpoints: 30 → 0
  • Full Page Writes (FPW): 162,579 → 47,898 (-70.5%)
  • Total WAL generated under identical writes: 5,934 MB → 2,614 MB (-56%), driven by both eliminated checkpoint FPW and zstd compression
Before and after comparison of checkpoints, WAL volume, and full page writes
Checkpoints, total WAL volume, and Full Page Writes before and after raising max_wal_size to 24 GB with zstd compression

Mapping the 66k TPS single-instance read plateau with prepared statements

Even under maximum read pressure, a single container used only 8.2 of 16 vCPUs while plateauing around 50k to 60k TPS. Checking four suspects in turn ruled out server saturation:

  • CFS CPU throttling: only 10 throttled periods out of 7,059 (0.14%).
  • Cross-node networking: same-node direct reads hit 62,376 TPS vs 56,463 TPS across nodes, placing cross-node network overhead at about 10%.
  • Internal lock contention: repeated snapshots of active sessions showed 130 / 130 waits on Client/ClientRead, with zero internal lock queuing.
Inspecting active session wait events during benchmark runs
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;
  • Client generator headroom: the exact same load generator pushed 98,859 TPS across two replicas simultaneously, proving the client was not capped.

Comparing all three pgbench query protocol modes (-M simple, extended, and prepared) over same-node direct connections at c=64 revealed the gap:

Benchmarking prepared protocol mode directly against a pod
pgbench -h <pod-ip> -U postgres -S -c 64 -j 16 -T 60 -M prepared pgbench
Protocol modeTPSAverage latencyvs simple
simple52,3251.223 msBaseline
extended59,0971.083 ms+12.9%
prepared63,4491.009 ms+21.3%

Switching to prepared bypassed repeated SQL parsing for a +21.3% lift (extended still sends Parse each time, yet skips text escaping and saves protocol overhead for +12.9%). Sweeping concurrency under prepared:

  • c=64: 63,449 TPS (1.009 ms)
  • c=96: 62,910 TPS (1.526 ms)
  • c=128: 66,453 TPS (1.926 ms)
  • c=192: 63,866 TPS (3.006 ms)

Throughput flattened into a 63k-66.5k TPS ceiling, beyond which extra concurrency only added linear latency. Dividing 8.2 cores across 56k-62k TPS puts server-side CPU cost at roughly 130-145 µs per point lookup, meaning more than 85% of the 1 ms round trip goes to socket transit and scheduling for ultra-short queries. That matches the 130/130 Client/ClientRead observations and let us settle per-instance CPU limits at 12 cores (8.2 peak cores plus system process headroom).

Query protocol comparison and concurrency sweep from 64 to 192 clients
Throughput across simple, extended, and prepared protocols and concurrency scaling from c=64 to c=192

Read-write splitting (-rw to -ro) and fixing a Kubernetes affinity deadlock

CloudNativePG automatically creates a read-write Service (<cluster>-rw) and a read-only Service (<cluster>-ro). Two read-only replicas delivered 95,686 TPS (1.8x the primary), whereas application traffic had previously pointed everything at -rw. Routing read-only queries to -ro was the largest immediate capacity win.

While steering pods away from the throttled hosts, we also caught a self-inflicted availability trap. Combining podAntiAffinity: required (all three instances must land on distinct hosts) with nodeAffinity: required (restricting pods to the three healthy hosts) locks three pods onto exactly three machines: if any single healthy host fails, the replacement pod stays stuck in Pending indefinitely.

Downgrading nodeAffinity to preferred (weight 100) resolved the deadlock:

Downgrading nodeAffinity from required to preferred
affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
    podAntiAffinityType: required
    nodeAffinity:
        preferredDuringSchedulingIgnoredDuringExecution: # required -> preferred to avoid indefinite Pending on host loss
            - weight: 100
              preference:
                  matchExpressions:
                      - key: kubernetes.io/hostname
                        operator: In
                        values: [node-1, node-2, node-3]

Under normal operation, instances stay on the three full-clock hosts; if one fails, Kubernetes can temporarily schedule onto a slower host to preserve cluster availability.

Current production parameters and next steps

Sanitized production settings (CloudNativePG 1.30, PostgreSQL 18.1, Guaranteed QoS):

CloudNativePG production parameters
spec:
    instances: 3
    resources:
        requests: { cpu: '12', memory: 16Gi } # 8.2 cores measured at peak, 12 cores for system process headroom
        limits: { cpu: '12', memory: 16Gi }
    postgresql:
        parameters:
            max_connections: '500'
            shared_buffers: '4GB'
            effective_cache_size: '12GB'
            work_mem: '16MB'
            maintenance_work_mem: '1GB'
            max_wal_size: '24GB' # raised from 8GB to eliminate forced WAL checkpoints
            min_wal_size: '4GB'
            wal_compression: 'zstd' # cuts total WAL writes by 56% alongside fewer FPW
            checkpoint_completion_target: '0.9'
            checkpoint_timeout: '15min'
            commit_delay: '0' # enabling regressed writes by 21%
            random_page_cost: '1.1'
            effective_io_concurrency: '200'
            bgwriter_delay: '200ms' # kept at default
            bgwriter_lru_maxpages: '100'
            bgwriter_lru_multiplier: '2.0'

For now, preferred node affinity keeps database pods off the two throttling hosts. During the next data-center maintenance window with iDRAC access, we will inspect bios_limit and BIOS SysProfile, switch both hosts to PerfOptimized across a reboot, re-run single-core clock and distributed-storage fsync benchmarks, and remove the nodeAffinity block altogether.

References


These notes come from day-to-day maintenance of our public cloud and model inference infrastructure. If you maintain an open-source project (especially AI agents, MCP tooling, or self-hosted software) and need hourly cloud servers for demos or DeepSeek and model API credits for testing, try the Leaflow Console or reach out via our About page.

TagsPostgreSQLKubernetesLinux PerformanceDatabasesInfrastructure