Evaluating PostgreSQL Performance Under I/O Modes and Configuration Variations

A structured, reproducible experimental study of PostgreSQL performance tuning under different I/O modes and configuration parameters, benchmarked with HammerDB v5.0 using a TPROC-C (TPC-C / OLTP) workload.

This repository accompanies the write-up in SSP_proj_report.pdf. It contains the full report, the data-collection scripts, all raw monitoring logs, the per-configuration HammerDB results, and the graph-generation notebooks.

Table of contents

• Overview
• What was measured
• Experiments and key results
• Repository layout
• Test environment & workload
• Baseline configuration
• Methodology
• How to reproduce
• Scripts & analysis pipeline
• Report
• Notes & limitations
• References

---

Overview

Modern data-intensive applications depend on predictable database performance, and PostgreSQL exposes several I/O-related controls that materially affect latency, throughput, and resource usage — yet their interactions are often unintuitive.

This project quantifies the effect of the following configuration choices on a single-node PostgreSQL deployment:

#	Experiment	Parameter tuned	Values tested
1	Shared buffer capacity	shared_buffers	64, 128, 256, 512, 1024, 2048 MiB
2	I/O method & worker count	io_method, io_workers	worker (3, 6, 9, 12) and sync
3	WAL size	max_wal_size	64, 128, 256, 512, 1024 MB
4	Checkpoint frequency	checkpoint_timeout	1, 2, 5, 10, 15 minutes
5	Commit behaviour	synchronous_commit	on, off

Each experiment varies one parameter at a time while everything else stays at the baseline, so measured differences can be attributed to that single setting.

What was measured

For every configuration the following metrics were collected:

• Throughput — transactions per minute (TPM) and new-order transactions per minute (NOPM), read directly from the HammerDB run output.
• Latency — average, P95 and P99 per TPC-C transaction type (NEWORD, PAYMENT, SLEV, DELIVERY, OSTAT), captured with HammerDB's per-virtual-user time profiler.
• CPU utilization — %usr, %system and total %CPU of all postgres processes, sampled every second with pidstat.
• Memory usage — total resident set size (RSS) of all postgres processes, converted to MB.
• Disk I/O — aggregate read/write throughput (MB/s) of postgres processes from pidstat.

Experiments and key results

The full tables (throughput, CPU, memory, disk I/O and per-transaction latency for every configuration) are in SSP_proj_report.pdf. The headline throughput numbers are:

Experiment 1 — Shared buffer size (best around 256–1024 MiB)

Shared buffers (MiB)	64	128	256	512	1024	2048
TPM	104,801	100,785	116,856	108,602	116,599	65,690
NOPM	45,531	43,774	50,823	47,217	50,708	28,535

Increasing shared_buffers improves caching up to a point, but 2048 MiB degrades performance sharply — total PostgreSQL memory reaches ~9.8 GB, starving the OS page cache and producing I/O stalls and heavy checkpoints (notably a huge SLEV latency spike).

Experiment 2 — I/O method & workers (default worker/3 wins)

I/O config	worker (3)	worker (6)	worker (9)	worker (12)	sync
TPM	100,785	29,224	23,486	28,108	40,093
NOPM	43,774	12,698	10,133	12,164	17,409

Adding I/O workers past the default adds severe CPU (total >200%) and memory overhead without helping throughput; sync I/O is lighter but still below the default worker configuration. SLEV latency explodes (seconds at P99) with 6–12 workers.

Experiment 3 — WAL size (max_wal_size) (best at 512 MB)

WAL size (MB)	64	128	256	512	1024
TPM	79,821	94,414	107,305	114,316	100,785
NOPM	34,774	40,966	46,640	49,668	43,774

Bigger WAL segments mean fewer checkpoints, but past ~512 MB the accumulating dirty pages cause infrequent, very heavy checkpoint bursts (lower write throughput, worse latency).

Experiment 4 — Checkpoint timeout (best at 1–2 min)

Checkpoint timeout	1 min	2 min	5 min	10 min	15 min
TPM	136,313	137,064	100,785	111,521	113,060
NOPM	59,023	59,532	43,774	48,447	49,142

Frequent small checkpoints smooth out I/O and give the best throughput/latency; the default 5 min is the worst case (large dirty-page batches, bursty writes, SLEV spikes), while longer intervals partially recover.

Experiment 5 — Synchronous commit (fastest, but weaker durability)

synchronous_commit	off	on (baseline)
TPM	159,905	100,785
NOPM	69,468	43,774

Turning synchronous_commit off (~+59% TPM) removes commit-time WAL-flush waits and cuts average latency (e.g. PAYMENT avg 12.96 → 4.59 ms), but at the cost of durability, higher CPU/memory, and more variable tail latency.

Overall message: PostgreSQL tuning is multi-objective — throughput-maximising settings do not always minimise tail latency, and faster settings may weaken durability. Choose settings against explicit service-level priorities.

Repository layout

.
├── SSP_proj_report.pdf        # Full report: motivation, methodology, all result tables, conclusions
├── monitor.sh                 # Wrapper that records pidstat CPU / memory / I/O logs while a test runs
├── postgresql.conf.bak        # Snapshot of the PostgreSQL config used (base run)
├── README.md                  # This file
│
├── Base/                      # Baseline run (default config): raw logs + TPM metrics screenshot
│
├── Buffer Capacity/           # Exp 1 – shared_buffers (64–2048 MiB)
├── wal_buffer/                # Exp 3 – WAL size / max_wal_size (64–1024 MB)  ← see note below
├── Sync_vs_Worker/            # Exp 2 – io_method / io_workers (6, 9, 12 workers, sync)
├── checkpoint_timeout/        # Exp 4 – checkpoint_timeout (1, 2, 5, 10, 15 min)
└── Commit_Behaviour/          # Exp 5 – synchronous_commit (off); 'on' baseline lives in Base/
│
├── Scripts/
│   ├── extractor_cpu.py       # Parses pidstat CPU logs  → %usr, %system, %CPU
│   ├── extractor_mem.py       # Parses pidstat memory logs → total RSS (MB)
│   ├── extractor_io.py        # Parses pidstat I/O logs  → read/write MB/s
│   ├── extractor_time.py      # Parses HammerDB time-profile output → calls, avg, P95, P99
│   ├── vis.ipynb              # Graphs for Exp 1 (shared buffer)
│   ├── vis2.ipynb             # Graphs for Exp 2 (I/O workers)
│   ├── vis3.ipynb             # Graphs for Exp 4 (checkpoint timeout)
│   └── vis4.ipynb             # Graphs for Exp 3 (WAL size)
│
├── output_images/             # Final generated figures, one folder per experiment:
│   ├── shared_buffer/         #   CPU / memory / I/O / latency / TPM graphs
│   ├── wal_buffer/
│   ├── io_workers/
│   └── checkpoint_timeout/
│
└── HammerDB/                  # Git submodule → https://github.com/TPC-Council/HammerDB (v5.0)

Naming convention inside each experiment folder

Each experiment directory stores data in two layers:

Buffer Capacity/
├── 1024MiB/                          # one subfolder per tested value
│   ├── TPM_Metrics_1024MiB.png       # HammerDB result screenshot (TPM / NOPM)
│   ├── postgres_cpu.log              # raw pidstat CPU samples  (1 per second)
│   ├── postgres_memory.log           # raw pidstat RSS samples
│   ├── postgres_io.log               # raw pidstat I/O samples
│   └── time_profile.log              # HammerDB XtProf per-virtual-user latency report
│
└── postgres_cpu_log.txt              # aggregated summary of all values in the experiment
    postgres_mem_log.txt              # (combined output of the extractor scripts)
    postgres_io_log.txt
    time_profile_log.txt

The ## <value> headers in the aggregated .txt files identify which configuration each block belongs to.

⚠️ Naming note: the experiment directories use slightly different names than the report sections. The wal_buffer/ folder is the WAL-size experiment — i.e. max_wal_size was tuned (called "WAL size" in the report), not wal_buffers. Base/ holds the baseline, so values such as 128 MiB (Exp 1), worker(3) (Exp 2), 1024 MB (Exp 3), 5 min (Exp 4) and synchronous_commit=on (Exp 5) all refer to the same base run.

Test environment & workload

Test machine (single node):

Component	Detail
CPU	Intel Core i5-11260H, 6 cores / 12 threads @ 2.60 GHz
Memory	16 GB DDR4 (3200 MHz)
Storage	512 GB SK hynix BC711 NVMe SSD, ext4
OS	Linux Mint 22.3 (Ubuntu 24.04 base), kernel 6.17.0
Database	PostgreSQL 18.3
Benchmark	HammerDB v5.0 — TPROC-C

HammerDB workload settings (identical for every run):

• Schema: 25 warehouses, 5 virtual users for schema build
• Timed driver: 4-minute ramp-up, 10-minute test, 25 virtual users
• User delay: 500 ms · Repeat delay: 500 ms
• Time profiler (XtProf) enabled

Baseline configuration

The default/reference run used these settings (see postgresql.conf.bak):

Parameter	Value
shared_buffers	128 MB
io_method	worker (default)
io_workers	3
max_wal_size	1024 MB
checkpoint_timeout	5 min
synchronous_commit	on

Methodology

The original methodology (now expanded) is the heart of the reproducibility story:

1. Change one parameter at a time (e.g. shared_buffers, io_method/io_workers, max_wal_size, checkpoint_timeout, synchronous_commit).

2. Restart PostgreSQL.

3. Verify the parameter actually changed (e.g. SHOW shared_buffers;).

4. Set up the test in HammerDB (time-profile on, 4-min ramp-up, 10-min test, 25 users, etc.) — but don't run it yet.

5. Run monitor.sh in the folder where logs should be saved — it starts three pidstat samplers for postgres processes:

   pidstat -u -C postgres 1 > postgres_cpu.log &   # CPU
   pidstat -r -C postgres 1 > postgres_memory.log & # memory (RSS)
   pidstat -d -C postgres 1 > postgres_io.log &     # disk I/O

6. Immediately start the test in HammerDB.

7. When the HammerDB test ends, stop the script with Ctrl+C (SIGINT makes pidstat append its Average: summary lines before exiting).

8. Collect everything: the raw logs, the HammerDB time profile, and a screenshot of the TPM/NOPM metrics; then reduce the raw logs into aggregated summaries with the extractor scripts (see below).

How to reproduce

# 1. Clone with the HammerDB submodule
git clone --recurse-submodules https://github.com/Abhinav-Kumar012/SSP-project.git
cd SSP-project

# (if you cloned without submodules)
git submodule update --init --recursive

Then, for each experiment you want to re-run:

# 2. Edit the target parameter in postgresql.conf (conf.d), e.g.:
#    shared_buffers = 256MB
# 3. Restart PostgreSQL and verify the value took effect:
sudo systemctl restart postgresql
psql -c "SHOW shared_buffers;"

# 4. Start monitoring (writes postgres_cpu.log / postgres_memory.log / postgres_io.log)
./monitor.sh

# 5. In HammerDB: open the TPROC-C script, build the schema (25 warehouses),
#    configure the timed driver (4 min ramp-up, 10 min test, 25 VUs, XtProf on)
#    and run it. When it finishes, come back and Ctrl+C the monitor.

Reduce the logs and build the graphs:

# Reduce raw pidstat logs to aggregate numbers (same style as the *_log.txt files)
python3 Scripts/extractor_cpu.py < postgres_cpu.log
python3 Scripts/extractor_mem.py < postgres_memory.log
python3 Scripts/extractor_io.py  < postgres_io.log

# HammerDB time-profile → per-transaction calls / avg / P95 / P99
python3 Scripts/extractor_time.py < time_profile.log

# Re-generate the figures from the aggregate numbers (edit the value arrays as needed)
#   vis.ipynb  → shared-buffer graphs   (output_images/shared_buffer)
#   vis2.ipynb → I/O-worker graphs      (output_images/io_workers)
#   vis3.ipynb → checkpoint-timeout graphs (output_images/checkpoint_timeout)
#   vis4.ipynb → WAL-size graphs        (output_images/wal_buffer)

Notebook dependencies: pandas, matplotlib, seaborn, numpy.

Scripts & analysis pipeline

Script	Input	Output
extractor_cpu.py	pidstat -u log	Summed %usr, %system, %CPU across postgres processes
extractor_mem.py	pidstat -r log	Per-process RSS (MB) + total PostgreSQL memory footprint
extractor_io.py	pidstat -d log	Total read / write throughput (kB/s and MB/s)
extractor_time.py	HammerDB time-profile output	Per-transaction CALLS, AVG, P95, P99 + overall weighted average latency

The notebooks combine the numbers extracted above (TPM/NOPM come from the HammerDB output screenshots) and emit the output_images/* figures used in the report.

Report

SSP_proj_report.pdf contains the full paper:

• Abstract, problem statement, objectives & scope
• Experimental design: hardware, workload, per-experiment setup
• Detailed result tables and analysis for all five experiments
• Data-collection and analysis description (the pidstat sampling procedure)
• Conclusions, future work, and references

Notes & limitations

• Results are for a single-node laptop setup with one workload shape (TPC-C, 25 warehouses / 25 VUs) — they are illustrative of trade-offs, not universal tuning guidance.
• Resource logs are process-level aggregates of every process whose command name contains postgres (as filtered by pidstat -C postgres).
• Each configuration was run once; the report does not claim statistical replication across repeated runs.
• Storage behaviour and checkpoint dynamics dominate most of the observed effects (NVMe SSD, no disk scheduler).

References

• PostgreSQL documentation — https://www.postgresql.org/docs/
• TPC-Council / HammerDB source — https://github.com/TPC-Council/HammerDB
• HammerDB — http://www.hammerdb.com/
• S. Shaw, "HammerDB: A Better Way to Benchmark Your Open Source Database" (Percona Live)
• PostgreSQL configuration reference — https://postgresqlco.nf/

---

Project report and data collected as part of an SSP course project at IIIT Bangalore by Abhinav Kumar and Pratham Chawdhry.