Choosing storage

The question arrives in a form that cannot be answered: what storage should we use for PostgreSQL? Local NVMe, SAN, cloud block storage, something distributed. Someone has a strong opinion, someone else has a budget, and the discussion usually resolves on whichever of those is louder.

This lesson does not name a winner, because the answer genuinely depends on your durability requirements, your write pattern and your failure tolerance. What it does give you is the measurement that settles most of the argument, and the questions that disqualify a candidate before performance is even discussed.

The measurement

PostgreSQL’s write path has one blocking dependency on storage: at commit, the WAL record must be on durable media before the client is told the transaction succeeded. Everything else in the write path is buffered, deferred or asynchronous. That one wait is the number to measure.

pg_test_fsync measures it directly. Run it in the data directory, so it exercises the actual filesystem PostgreSQL will use.

$ cd $PGDATA && pg_test_fsync -s 3

Compare file sync methods using one 8kB write:
      open_datasync                      1199.916 ops/sec     833 usecs/op
      fdatasync                          1170.545 ops/sec     854 usecs/op
      fsync                               592.048 ops/sec    1689 usecs/op
      fsync_writethrough                              n/a
      open_sync                           594.553 ops/sec    1682 usecs/op

Compare open_sync with different write sizes:
       1 * 16kB open_sync write           592.199 ops/sec    1689 usecs/op
       2 *  8kB open_sync writes          298.821 ops/sec    3346 usecs/op
       4 *  4kB open_sync writes          149.859 ops/sec    6673 usecs/op
       8 *  2kB open_sync writes           74.561 ops/sec   13412 usecs/op
      16 *  1kB open_sync writes           37.427 ops/sec   26718 usecs/op

Non-sync'ed 8kB writes:
      write                           4838411.475 ops/sec       0 usecs/op

Four things are worth reading out of that.

fdatasync costs 854 µs; fsync costs 1,689 µs. Roughly double, because fsync also flushes file metadata. This is why fdatasync is the default wal_sync_method on Linux — the WAL file’s metadata does not change on every write, so flushing it is wasted work.

An unsynced write is about 4,100 times faster than a synced one. Durability is not a modest overhead on writing. It is very nearly the whole cost of writing. Any comparison of storage platforms that does not measure with the sync is measuring the wrong thing.

The same 16 kB costs 1,689 µs as one write and 26,718 µs as sixteen 1 kB writes. Sync cost is per sync, not per byte. Hold on to that; it is the mechanism behind the most important result in this lesson.

write, fsync, close and write, close, fsync cost the same. So on this filesystem an fsync is honoured on a descriptor other than the one that did the writing. A platform where those differ materially is one to investigate before trusting.

From latency to throughput

The measurement above predicts something checkable. If a single client commits every transaction, and every commit waits for one sync at 854 µs, that client cannot exceed roughly 1,170 transactions per second no matter how fast the CPU is.

$ pgbench -c 1 -T 15 -N postgres

latency average = 1.187 ms
tps = 842.245987 (without initial connection time)

842 tps at 1.187 ms average latency, of which the 854 µs sync is about 72%. The prediction and the measurement agree, which is the point: the write rate of a single session is a property of storage sync latency, not of the server’s processing power.

That is the fact people reach for when they decide to turn durability off:

$ PGOPTIONS='-c synchronous_commit=off' pgbench -c 1 -T 15 -N postgres

latency average = 0.317 ms
tps = 3152.982032 (without initial connection time)

3.7 times faster. A tempting trade, and usually the wrong one, for a reason that is only visible when you also measure concurrency.

Group commit changes the shape of the problem

Because sync cost is per sync rather than per byte, concurrent commits can share a flush. PostgreSQL does this automatically.

$ pgbench -c 16 -j 4 -T 15 -N postgres  &&  pgbench -c 64 -j 8 -T 15 -N postgres

-- 16 clients
latency average = 1.695 ms
tps = 9438.617458 (without initial connection time)

-- 64 clients
latency average = 1.996 ms
tps = 32068.688818 (without initial connection time)

Put the four results side by side:

Sixty-four concurrent clients with full durability reached ten times the throughput of a single client with durability switched off, at an average latency only 68% higher than the single durable client.

What actually disqualifies a storage platform

Performance is the easy half of this decision and the half everyone discusses. The half that ends careers is guarantees. A platform that is fast and does not honour a sync is worse than a slow one, because it fails only during the incident you bought it to survive.

The questions worth asking, in the order that eliminates candidates fastest:

Does a returned fsync mean the data is durable? A write cache that acknowledges before persisting turns every durability guarantee in this course into a guess. Battery-backed and flash-backed caches are designed for exactly this and are fine; a volatile cache with write-back enabled is not.

Does the platform report I/O errors, or hide them? PostgreSQL relies on being told when a write fails. A layer that silently retries and eventually gives up without a durable error report leaves the server believing a checkpoint completed.

Can it tear an 8 KiB write? PostgreSQL’s answer to torn pages is full_page_writes, which writes the entire page to WAL the first time it is modified after a checkpoint. This is why full_page_writes = off is safe only where the storage guarantees atomic 8 KiB writes, and why almost nobody should turn it off.

What is its failure mode, not its failure rate? Local NVMe fails by becoming unavailable with the node. Networked storage fails by becoming slow, which is harder — a database on storage that has become slow does not stop, it degrades, backs up connections, and takes the application down with it.

Does it survive the loss you are designing against? Local storage does not survive losing the node. Replication is the answer to that, not storage. This is the point at which storage choice and Parts XIV and XV become the same conversation.

What to take from this

• Measure pg_test_fsync in the actual data directory before believing anything about a storage platform. Three seconds of measurement beats a datasheet.
• Sync latency bounds a single session’s commit rate. Concurrency, not reduced durability, is usually the larger and cheaper lever.
• synchronous_commit = off is a bounded, per-session trade you can reason about. fsync = off is not a trade.
• Platform selection is decided by guarantees — honest syncs, reported errors, understood failure modes — before it is decided by speed.
• Checksums and block size are initdb-time decisions. Get them right while the cluster is empty.

Cross-course references

• Linux for Production Sysadmins — Part XIII (Disks and block devices) and Part XLI (Storage Performance) cover measuring a device rather than trusting its datasheet, and Part XV (Fstab) covers the mount options that decide whether a flush is honoured.
• Ceph & Distributed Storage — Part II (Storage performance fundamentals) and Part LXIX (Disk performance) cover the same measurement on distributed storage, and Part IV (Failure domains) covers what a shared device does to an availability argument.
• Proxmox — Part V (Storage fundamentals) and Part VI (ZFS) cover hypervisor-layer caching, which is where an honoured fsync is most often lost.

Quiz