Author: Mouhamed Olossoumare
Date: July 18, 2026
Server: 1 CPU core, 2 GB of memory, 25 GB SSD disk, Ubuntu 24.04, PostgreSQL 16.14
Where we are: Testing finished · First tuning done and measured · Second tuning done and confirmed working in a quick test (forced checkpoints: 152 → 0) · Final full measurement scheduled
Before changing anything, I measured exactly how the server performs with factory settings — so that any improvement (or damage) from tuning could be proven with numbers instead of impressions. The testing found two factory settings that are wrong for our machine, answered an important table-design question with a controlled experiment, and revealed three free rules that speed things up more than any setting ever could.
The most important results:
shared_buffers and effective_cache_size) made cached reading 6% faster and fixed a deep structural problem, but also made disk-heavy reading 7% slower — an honest result that teaches us the standard advice doesn't fully fit small machines like ours.max_wal_size and related settings) is applied, running with zero downtime, and already confirmed working in a quick test: forced emergency checkpoints dropped from 4–5 per test to zero. The full formal measurement is scheduled next.Every number in this report comes from scripted tests that ran three times each (I report the middle result), with all raw logs saved and archived.
I recorded everything about the machine: operating system, kernel, PostgreSQL version, disk layout, and proof that all settings were factory defaults (checked via pg_settings, PostgreSQL's own list of every configuration value and where it came from). Why: a before-and-after comparison is only fair if the only thing that changed in between is our tuning. This "photo" gets retaken and compared before every new measurement.
Before blaming or crediting the database for anything, I measured the raw disk speed using fio (a standard disk-benchmarking tool). Why: if the database is slow, we need to know whether it's the database's fault or simply the disk's limit.
The key discovery concerns fsync — the operation where the database asks the disk to physically confirm "got it, this data is safe even if power is cut." Every database commit (a confirmed save) waits for one fsync. On our disk, an fsync takes about 2 milliseconds — which means a single program committing one record at a time can never exceed roughly 500–600 commits per second. That's physics, not configuration. (This prediction was later confirmed by the database tests, which landed exactly on that ceiling.)
| Hardware measurement | Result |
|---|---|
| Random read/write speed (IOPS — input/output operations per second) | ~2,930 reads + ~2,940 writes per second |
| fsync (disk save-confirmation) time | average 1.85 ms |
| Resulting commit ceiling | ~500–600 per second for a single connection |
I used pgbench — the benchmark tool that ships with PostgreSQL — which simulates many users doing bank-style transactions as fast as possible and reports TPS (transactions per second, the standard speed score). I tested two database sizes — one small enough to fit in memory (the best case) and one deliberately too big for memory (the realistic hard case) — with 1 to 32 simulated users at once.
Results with factory settings (TPS, median of 3 runs):
| Simulated users | Small database (fits in memory) | Big database (doesn't fit) |
|---|---|---|
| 1 | 627 | 474 |
| 4 | 1,294 | 1,180 |
| 8 | 1,514 | 1,329 |
| 16 | 1,648 (the peak) | 1,447 (the peak) |
| 32 | — | 1,404 (getting worse) |
| Reading only, 4 users | 12,565 | 7,917 |
What this tells us: performance peaks at about 16 simultaneous connections — beyond that, users wait twice as long and get less done. Reading is about 9x faster than writing. And when the data outgrows memory, reading slows by about a third — painful but survivable, because our disk is decent.
Two wrong factory settings were caught, with proof:
shared_buffers (the database's private memory cache) is far too small. PostgreSQL keeps its own cache of frequently-used data in a memory area called shared_buffers, and the factory size is a tiny 128 MB — set for computers from decades ago. Our cache hit ratio (the percentage of data requests found already in that cache instead of fetched from disk) measured only 84%; healthy is over 99% when the data fits in memory.max_wal_size (the size limit of the database's journal) is too small, causing panic housekeeping. PostgreSQL records every change first in its WAL — the Write-Ahead Log, a journal that guarantees no committed data is ever lost. Periodically, a checkpoint flushes the accumulated changes into the main data files so journal space can be recycled. Checkpoints come in two kinds: timed (calm, on schedule) and requested/forced (emergency — the WAL hit its max_wal_size limit before the schedule arrived). Our count: 152 forced checkpoints versus only 6 timed ones. The factory limit (1 GB) fills in ~2 minutes at our write speed, so the database spent every test firefighting.A real design question for our analytics tables: to store a record with 100 measurements, is it better to use one wide row with 100 columns, or 100 narrow rows with one measurement each? I saved the identical data both ways and measured everything.
| What was measured (same data both ways) | Wide (1 row) | Narrow (100 rows) | Winner |
|---|---|---|---|
| Loading speed (with batching) | 14,777 records/sec | 2,263 records/sec | Wide, 6.5x |
| WAL (journal) volume produced | ~12 MB | ~160 MB | Wide, 13x less |
| Disk space used | 11 MB | 96 MB | Wide, 9x smaller |
| Reading one full record back | 0.14 ms | 0.89 ms | Wide, 6.5x |
| Changing one single value | ~970/sec | ~940/sec | Tie |
The reason wide wins: PostgreSQL charges a fixed "packaging fee" for every row it writes — a tuple header (~28 bytes of internal bookkeeping), one WAL entry, and one index entry — so 100 narrow rows pay that fee 100 times, while one wide row pays it once. Even in the one category where narrow rows should have won (changing a single value), they only tied.
The biggest surprise wasn't about shape at all: simply grouping many records into one transaction — telling the database "here are 1000 records, commit them all at once" instead of committing each record separately — made loading 25 times faster. The reason: each commit waits for one ~2 ms fsync, so batching pays that toll once per thousand records instead of once per record. This is the single most valuable discovery of the whole project.
(These results were also reproduced almost identically on a second, different server — so this is how PostgreSQL works everywhere, not a quirk of our machine.)
Decision adopted: wide tables, and always save in batches.
My method: change one group of settings at a time using ALTER SYSTEM (PostgreSQL's command for making reversible configuration changes), save proof of exactly what changed, then re-run the identical tests and compare — with a strict rule that an improvement only counts if it beats the best result the old setup ever produced. Before each comparison, I also re-test the raw disk with fio, because on cloud servers the disk's speed can drift day to day (and it did — I caught a 6% slowdown one day and correctly blamed the disk instead of the tuning).
What changed:
| Setting | Plain meaning | Before | After |
|---|---|---|---|
shared_buffers |
The database's private memory cache | 128 MB | 512 MB (a quarter of the machine's RAM — the standard advice) |
effective_cache_size |
The database's assumption about total available caching (its own cache + the operating system's file cache); used by the query planner to choose strategies — it reserves nothing | 4 GB (a wrong guess on a 2 GB machine) | 1400 MB (honest) |
What happened:
| Test | Result |
|---|---|
| Reading cached data | 6% faster — a clean, provable win |
| Writing | Unchanged (expected — writing is limited by fsync and checkpoints, not by cache) |
| Reading disk-heavy data | 7% slower — a real loss, explained below |
| Cache hit ratio | 84% → 96% (small database) — misses cut almost 4x |
| Deep structural fix | buffers_backend (a counter of how often client connections were forced to flush data to disk themselves, instead of background processes doing it) dropped 13x — work moved to where it belongs |
The honest lesson: memory given to shared_buffers is memory taken away from the operating system's own file cache — the second caching layer that also serves the database. On data too big for any cache, that trade came out slightly negative. The textbook "25% of RAM" advice can overshoot on small machines — we may test a smaller value (384 MB) later as a refinement.
What changed:
| Setting | Plain meaning | Before | After |
|---|---|---|---|
max_wal_size |
Size limit of the WAL (journal) before a checkpoint is forced | 1 GB | 6 GB (chosen by measurement — see below) |
checkpoint_timeout |
How often scheduled checkpoints run | 5 min | 15 min |
checkpoint_completion_target |
Checkpoint style: how much of the interval to spread the writing over (0.9 = a gentle trickle across 90% of the time, instead of one intense burst) | default | 0.9 |
bgwriter_lru_maxpages |
The work quota of the background writer (the helper process that pre-cleans memory pages so nobody else has to); our stats showed it hit the old quota 40,000+ times, wanting to help but not allowed | 100 | 400 |
All changes applied live, with zero downtime (these settings only need a configuration reload, not a restart), and are fully reversible.
Finding the right max_wal_size — by measurement, not guesswork. I tested each candidate with a 10-minute maximum-pressure write test, counting forced checkpoints:
max_wal_size |
Forced checkpoints per 10-minute test |
|---|---|
| 1 GB (factory) | 4–5 |
| 4 GB | 2 — better, not cured |
| 8 GB | 0 — cured, but the WAL's disk footprint was too big for our 25 GB disk to hold safely |
| 6 GB (chosen) | 0 — cured, and fits the disk ✔ |
So the final value was picked with evidence at every step, including an honest constraint: the "perfect" size didn't fit our disk, and 6 GB delivers the same zero-emergency result within the space we have. A useful side discovery: if we ever upgrade this server, the most valuable upgrade for heavy writing is a bigger disk (checkpoint headroom), then more memory — not a faster processor.
What remains: the full formal measurement (about 6.5 unattended hours) to confirm the fix holds across every test condition and to measure whether calm checkpoints also made writing measurably faster.
shared_buffers value (Round 3).Our measured "engine limits" are the ~2 ms fsync, the single CPU core, and 2 GB of RAM. Concretely, we declare this server optimized when the final measurement campaign shows all of the following:
| # | Success criterion | How we'll know | Status |
|---|---|---|---|
| 1 | No more panic housekeeping | Forced checkpoints ≈ 0 across the full 6.5-hour campaign (was 152) | Confirmed in quick test; formal proof pending |
| 2 | Speed sits at the hardware's measured ceilings | Single-connection writes ≈ the fsync arithmetic (~500–600 TPS); cached reads at CPU saturation; the cached-vs-disk-bound gap explained entirely by measured disk latency | Already true — verified since baseline |
| 3 | Performance is smooth and boring | Throughput graphs flat (the "sawtooth" pattern gone); repeat runs agree within a few percent; no unexplained latency spikes | Repeatability passing; smoothness check pending |
| 4 | Every setting is a decision, not an accident | Six settings changed, each curing a diagnosed problem; every kept default declined with a written reason (work_mem, max_connections, fsync safety settings — all documented) |
Done |
| 5 | We know when to stop | The next candidate change either fails the arithmetic before testing, or tests inside the noise band — then tuning ends; anything further is superstition, and the remaining levers are hardware (disk, then RAM — priced in this report) or the three free application rules | Rule adopted; triggers after the final campaign |
The end state, in one sentence: writing runs at the disk's speed limit, reading at the processor's speed limit, housekeeping never panics, every changed setting fixed a proven problem, every kept setting was a deliberate choice — and going faster from here means better hardware or better application habits, not more tuning.
One honest decision will remain even on success: Round 1's trade-off (+6% cached reads, −7% disk-heavy reads). We either accept it as priced, or spend one final measured
Every test ran three times (middle result reported), for 10 minutes each, with system monitoring (vmstat, iostat — tools that watch memory, swap, and disk activity) recording everything. All raw logs are archived in timestamped folders. The environment "photo" is compared before every campaign to catch anything that changed besides our tuning. Predictions were written down before each test and scored honestly afterward — including the two that missed, both explained. One false alarm from our own monitoring was investigated down to its root cause (a log-reading bug) instead of being ignored or believed.
Supporting materials: the testing runbook, all campaign scripts, and the raw results folders.
| Term | Plain meaning |
|---|---|
| fsync | The disk physically confirming "this data is saved and survives a power cut." Every commit waits for one. ~2 ms on our disk. |
| commit / transaction | A confirmed save. A transaction can carry one record or thousands (that's batching). |
| TPS | Transactions per second — the standard database speed score. |
| WAL (Write-Ahead Log) | The database's journal: every change is written here first, guaranteeing committed data is never lost. |
| checkpoint | Periodic housekeeping that flushes accumulated changes from memory into the main data files. Timed = calm and scheduled; forced/requested = emergency, because the WAL hit its size limit. |
shared_buffers |
The database's private memory cache for frequently-used data. |
| cache hit ratio | Percentage of data requests served from shared_buffers instead of going to disk. >99% is healthy when data fits in memory. |
max_wal_size |
The WAL size limit that, when reached, forces an emergency checkpoint. |
effective_cache_size |
The planner's estimate of total available caching; an assumption, not an allocation. |
| background writer (bgwriter) | Helper process that pre-cleans memory pages; bgwriter_lru_maxpages is its per-cycle work quota. |
| fio / pgbench | Standard benchmarking tools for raw disks and for PostgreSQL, respectively. |
| IOPS | Input/output operations per second — raw disk speed at small-block work. |