Server: Engine db
Prepared by: Mouhamed Olossoumare
Date: July 15, 2026
Status: Baseline phase — no optimizations applied yet
Establish a rigorous, repeatable performance baseline for a stock (default-configuration) PostgreSQL installation before any tuning is applied. Every future optimization will be judged against the numbers produced by this runbook. A secondary goal is to quantify the write-performance trade-off between wide-row (one row, many columns) and narrow-row (many rows, few columns) data models to inform the analytics schema design.
| Property | Value |
|---|
| CPU | 1 vCPU (Regular, 2.0 GHz class) |
| RAM | 1.9 GiB (+ 8 GiB swap) |
| Disk | 50 GB virtual SSD volume (xvda), root partition 49 GB, ~38 GB free, ext4 |
| OS | Ubuntu 24.04.4 LTS, kernel 6.8.0-134-generic |
| PostgreSQL | 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1), fresh install, default configuration |
| Other services | Git, WireGuard (idle), fail2ban, OpenSSH — all confirmed inactive workloads |
Constraint notes:
vmstat 5 is monitored during every run; sustained non-zero si/so columns → discard and re-run.htop and iostat -x 5 before starting (CPU ~0%, disk idle).-T 600) so checkpoints and autovacuum are captured, not dodged.Run and archive the output of:
lsb_release -a && uname -r
psql --version
df -hT / && lsblk
free -h && nproc
ROOTDISK=$(lsblk -ndo pkname "$(findmnt -no SOURCE /)" | head -1)
cat "/sys/block/${ROOTDISK}/queue/rotational"
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE source NOT IN ('default','override');"
The last query proves the configuration is stock. Its output should be near-empty; anything listed becomes part of the documented baseline config.
Purpose: establish the hardware ceiling so database results can be attributed correctly (database limitation vs. disk limitation).
sudo apt install fio sysbench -y
# 1a. Random read/write at Postgres page size (8 KB)
fio --name=randrw --rw=randrw --bs=8k --size=2G --numjobs=2 \
--iodepth=16 --runtime=120 --time_based --direct=1 --group_reporting
# 1b. fsync latency (gates commit rate)
fio --name=fsynctest --rw=write --bs=8k --size=1G --fsync=1
# 1c. CPU reference
sysbench cpu run
# cleanup
rm randrw* fsynctest*
Record: read IOPS, write IOPS, mean and p99 latency (1a); fsync mean and p99 latency (1b); CPU events/sec (1c). The fsync mean latency defines the theoretical single-stream commit ceiling: 1 second ÷ fsync latency ≈ max commits/sec. All Phase 2 write results are interpreted against this ceiling.
Two dataset sizes bracket the memory boundary:
| Dataset | Scale | Approx. size | Tests |
|---|---|---|---|
| In-memory | -s 50 |
~750 MB | cached-workload performance |
| Larger-than-RAM | -s 200 |
~3 GB | disk-bound performance (primary optimization target) |
For each dataset:
sudo -u postgres createdb benchdb
sudo -u postgres pgbench -i -s <SCALE> benchdb
# Read/write (TPC-B-like), client sweep — 3 runs each
sudo -u postgres pgbench -c 1 -j 1 -T 600 -P 10 -r benchdb
sudo -u postgres pgbench -c 4 -j 1 -T 600 -P 10 -r benchdb
sudo -u postgres pgbench -c 8 -j 1 -T 600 -P 10 -r benchdb
sudo -u postgres pgbench -c 16 -j 1 -T 600 -P 10 -r benchdb
# Read-only variant (isolates cache/CPU from WAL/disk)
sudo -u postgres pgbench -c 4 -j 1 -T 600 -S -P 10 -r benchdb
Record per run: TPS, average latency, per-statement latency (-r), and the 10-second progress series (-P 10) — a sawtooth TPS pattern indicates checkpoint stalls, a primary tuning target.
Concurrent system monitoring (second terminal): vmstat 5 and iostat -x 5, logged to files.
Post-run database metrics:
-- Cache hit ratio (target after tuning: > 99%)
SELECT round(sum(blks_hit)*100.0/sum(blks_hit+blks_read),2) AS cache_hit_pct
FROM pg_stat_database WHERE datname='benchdb';
-- Checkpoint pressure: requested (forced) vs timed (scheduled)
SELECT * FROM pg_stat_bgwriter;
Question: Is it faster to write one row with N columns or N rows with one value column? This directly informs the analytics schema design.
PostgreSQL stores data in 8 KB pages. Every row carries a fixed overhead: a ~23-byte tuple header plus alignment padding and a 4-byte item pointer — call it ~28 bytes per row before any actual data.
Prediction: wide-row inserts should win significantly on insert throughput and total storage; narrow rows should win on single-attribute updates. The experiment quantifies by how much on this hardware.
-- Model A: wide
CREATE TABLE metrics_wide (
entity_id bigint PRIMARY KEY,
ts timestamptz NOT NULL DEFAULT now(),
m001 numeric, m002 numeric, /* ... */ m100 numeric
);
-- Model B: narrow (key-value / EAV style)
CREATE TABLE metrics_narrow (
entity_id bigint NOT NULL,
ts timestamptz NOT NULL DEFAULT now(),
metric_name text NOT NULL,
metric_value numeric,
PRIMARY KEY (entity_id, metric_name)
);
Identical logical payload: 10,000 entities × 100 metrics → 10,000 wide rows vs. 1,000,000 narrow rows.
| Metric | How measured |
|---|---|
| Insert throughput | pgbench custom script (-f insert_wide.sql / -f insert_narrow.sql), TPS over 5-minute runs, 3 repetitions |
| WAL generated | SELECT pg_current_wal_lsn(); before and after; delta via pg_wal_lsn_diff() |
| Final storage footprint | pg_total_relation_size() on both tables (table + index + TOAST) |
| Read cost (full entity fetch) | EXPLAIN (ANALYZE, BUFFERS) on "fetch all 100 metrics for one entity" |
| Update cost (single metric) | timed loop updating one column (wide) vs. one row (narrow); TPS + WAL delta |
Insert throughput will additionally be measured at three batch sizes for each model — 1 row per transaction, 100 per transaction, 1000 per transaction — because commit frequency (fsync, from Phase 1b) is expected to dominate small-batch results. This isolates schema shape effects from commit batching effects.
All results land in one table per phase:
| Test ID | Dataset | Clients | Run 1 TPS | Run 2 TPS | Run 3 TPS | Median TPS | Avg latency | Notes (swap? sawtooth?) |
|---|---|---|---|---|---|---|---|---|
| RW-s50-c4 | scale 50 | 4 | ||||||
| ... |
Plus the Phase 1 hardware ceiling table and the Phase 3 comparison table (insert TPS, WAL bytes, storage size, update TPS for wide vs. narrow at each batch size).
| Risk | Mitigation |
|---|---|
| Shared-tenancy cloud volume: neighbor activity or provider throttling skews runs | Fixed run lengths, 15–30 min cool-downs between heavy phases, 3 repetitions with median |
| Swap thrashing on 1.9 GiB RAM invalidates results | Live vmstat watch; discard any run with sustained si/so activity |
| Checkpoint timing luck | 10-minute runs; -P 10 progress series inspected for sawtooth |
| Config drift between baseline and post-optimization runs | Phase 0 fingerprint re-captured and diffed before the comparison campaign |
Next document in series: "Optimization Plan & Post-Tuning Comparison" — to be drafted after baseline sign-off.