Postgres vs. File Systems: A Performance Comparison

scale	zfs/default	zfs/tuned	btrfs	ext4	xfs
100	61028	64057	33352	85291	85765
1000	32568	36066	27952	58736	58991
10000	3822	6068	6541	30495	30977

Table: read-write results

For the read-only tests, the results are (again) pretty even - ZFS is a bit slower than the other file systems, particularly for the larger scales, similarly to the smaller machine.

For read-write tests, the differences are much more dramatic. EXT4/XFS are the clear winner. ZFS keeps pace on the two smaller data sets (that fit into shared buffers / RAM), but on the scale 10000 it drops to only ~6k tps (compared to ~30k tps for ext4/xfs). Btrfs performs quite poorly—on the small/medium data sets it’s somewhat competitive with ZFS, but other than that it’s clearly underperforming.

Throughput over time

There’s one important thing the throughput results presented so far ignore—stability of the throughput, i.e. how it evolves over time. Imagine two systems running the same benchmark:

• system A does 15k tps consistently, with minimal variation during the benchmark run
• system B does 30k tps in the first half, and then gets stuck and does 0 tps

Both systems do 15k tps on average, and so would have the same chart in the charts presented so far. But I assume we’d agree system A has much more consistent and predictable performance.

So let’s look not just at the total throughput, but also at how throughput develops over time. The charts in this section show throughput for each second during a 60-minute read-write benchmark (blue), with a running average over 15-second intervals (red).

This should give you some idea how much the throughput fluctuates, and also trends / patterns (degradation over time, impact of checkpoints…).

i5 / scale 1000

All results in this section are from RAID0/striping setup. For scale 1000 (exceeds RAM, but small enough compared to wal_max_size), we get this:

--------

It’s immediately obvious that ZFS has exceptionally clean and consistent behavior - with the default config there’s some checkpoint impact, but disabling FPW makes that go away. As a DBA, this is what you want to see on your systems—minimum differences (jitter) during the whole benchmark run.

EXT4/XFS achieve higher throughput (~7.5k tps vs. 6.5k tps, so ~20% increase), but the jitter is clearly much higher. The per-second throughput varies roughly between 5k and 9k tps—not great, not terrible. There’s also clear checkpoint impact, but we can’t disable FPW to eliminate this—we could only make checkpoints less frequent by increasing the timeout / WAL size.

BTRFS has more issues, though—the throughput is lower than with ZFS while the jitter is much worse than with EXT4/XFS. On average (the red line) it’s not that bad, but it regularly drops close to 0, which implies high latency for some transactions.

i5 / scale 5000

Now let’s look at the “large” data set (much larger than RAM).

--------

The first observation is that the impact of checkpoints is much less visible—both for ZFS with the default config and EXT4/XFS. This is due to the dataset size and random access—this means the number of FPW produced can’t drop before hitting the WAL limit (and starting the next checkpoint).

For BTRFS, the situation is much worse than on the medium (scale 1000) data set—not only the jitter is even worse, but the throughput is gradually dropping over time—it starts close to 4000 tps, but at the end it drops to only about 2000 tps.

xeon / scale 1000

Now let’s look at throughput on the larger machine, with a single NVMe device. On the medium scale (larger than shared buffers, fits into RAM) it looks like this:

--------

On ZFS with default configuration, the checkpoint pattern is clearly visible. With the tuned configuration, the pattern disappears and the behavior gets much more consistent. Not as smooth as on the smaller machine with multiple devices, but better than the other file systems.

There’s very little difference between EXT4 and XFS, both in total throughput and behavior over time. The total throughput is better than with ZFS (40k vs 60k), but the jitter is more severe.

For BTRFS, the overall throughput is fairly low (~30k tps), while the jitter is somewhat better and worse than for EXT4/XFS at the same time. It seems mostly closer to the average throughput, but at the same time it often drops to 0.

xeon / scale 10000

On the large (~150GB) data set, the results look like this:

--------

This is similar to what we saw on the smaller machine—checkpoint pattern disappears, due to the amount of FPW we have to write to WAL.

On ZFS with the tuned configuration, the jitter increases significantly. I’m not sure why, but I’d say it’s still fairly consistent (compared to the other file systems).

For EXT4/XFS, the throughput drops to ~50% (compared to the medium scale) while the jitter remains about the same—I’d argue this is a pretty good result.

With BTRFS we see the same unfortunate behavior as on the smaller machine - with the large data set the throughput gradually decreases, with significant jitter.

Conclusions

So, what have we learned? The first observation is that there are significant differences between the evaluated file systems—it’s hard to pick a clear winner, though.

The traditional file systems (EXT4/XFS) perform very well in OLTP workloads, at least in total throughput. ZFS performs pretty well too—it’s a bit slower in terms of throughput, but the behavior is very consistent with minimal jitter (particularly with disabled full-page writes). It’s also quite flexible—for example it allows moving ZIL to a separate device, which should improve the throughput.

As for BTRFS, the results are not great—I did a similar OLTP benchmark a couple years ago, and this time BTRFS performed a bit better, in fact. However, the overall consensus seems to be that BTRFS is not particularly well suited for databases, and others observed this too. Which is a bit unfortunate, as some of the features (higher resilience, easy snapshotting) are very useful for databases.

The last thing to keep in mind when reading the results is to realize these are stress tests, fully saturating the I/O subsystem. That’s not what would be happening on most production systems—once the I/O gets saturated for extended periods of time, you’d be already thinking about upgrading the system to reduce the storage load. Which typically means the latencies should improve, etc.