This is an update to my post last week Partitioning a Huge Table where I talk about taking an existing table and making it partitioned. My largest complaint in that post was that it was difficult to do online because rebuilding a clustered index on a huge table required reading or writing a lot of […]
The post Partitioning a Huge Table Quickly first appeared on Michael J. Swart.Data compression and data encoding get thrown around as if they meant the same thing. And to be fair, if you go by the textbook definition of Shannon Entropy, they actually do. Both change the byte representation of input data to achieve a size reduction. But if you leave the text book behind and apply them in a system that stores and processes a lot of data, encoding and compression are two different tools that solve two different problems.
First, let’s have a look at both to see what justifies a distinction in practice.
Encoding schemes are narrow specialists. Each one is built around a specific, well-understood pattern in the data and therefore needs a good understanding of it. It can, e.g., detect a set of arbitrary values with few distinct ones, numbers clustered in a small range, or long runs of identical values. Because the scheme knows exactly what kind of redundancy it’s looking for, applying and reversing it is cheap, often just a handful of instructions per value. Cheap enough that many of these schemes can be vectorized with SIMD.
That narrowness has a second, more important consequence: because the transform is so simple and structured, you can often operate directly on the encoded representation without ever decompressing it. A filter can run as an integer comparison instead of a string comparison, or skip a whole block by comparing its bounds, all without materializing a single decoded value. That’s not a nice-to-have side effect, it’s the entire point. In contrast to compression, an encoding is not only used to shrink data, but to make data operations faster. It does not aim for the smallest size, but for the representation best for data processing.
As encodings only apply to one specific property of the data, you need a big toolbox to use them meaningfully. The ones we implement in CedarDB include dictionary encoding, single-value encoding, frame-of-reference (FOR), and truncation (dropping unused high-order bytes of an integer)1, and we pick the one that best fits the data whenever we transform a set of cooled values to our analytics-optimized layout. Why picking the right one matters becomes clear when looking at the schemes in detail.
A widely used and easy to understand encoding is dictionary encoding, which we already covered in our post on string compression2. Dictionary encoding replaces every value with a small fixed-width integer key that points into a table of the distinct values actually present in the column. This does not only reduce the size of the data, as each distinct string is only stored once and each occurrence replaced by a 1 to 3 byte integer. It also allows for comparisons of string values based on their integer keys, so a costly string comparison turns into a cheap integer comparison instead.
If you not only assign any key to strings, but do so in string-sorted order, you can even answer inequality comparisons or sort entire string arrays by their integer keys. While this adds additional overhead during encoding, it only needs to sort the unique strings and will pay off quickly. However, this is not change friendly as a new value in the middle will invalidate all keys behind it, requiring a re-encoding of the entire block, so this is only worth doing for truly cold data.
While dictionary encoding is great when a column has few distinct values, it starts falling apart on something like a timestamp or order ID column where every value can be different. Luckily, a large number of distinct values does not automatically mean more entropy, and we can utilize a different pattern in the data instead. While the domain of timestamps is huge and spans most of Earth’s past and future history, the values you see in practice are often much closer together. Frame-of-reference (FOR) encoding targets exactly this pattern: values that are almost all distinct, but are clustered tightly relative to each other. Think of a column that stores timestamps scattered throughout the last year. Without encoding, each one needs 8 bytes to store its timestamp as an absolute value. However, the differences between them and their minimum are small enough to fit in far fewer bytes.
For FOR encoding, one can store the minimum value once in the column’s header and then every value as a fixed-width delta from that minimum, so a value that would cost 8 bytes raw might cost only 2 or 3 bytes encoded. CedarDB actually goes a level further and subdivides a column into smaller sets of about a thousand values each, each with its own local minimum and byte width. This helps prevent a handful of outliers from forcing the entire column into a wider representation and instead keeps their impact localized. Furhter, if the data is at least roughly sorted by the encoded value, it can allow for using even smaller deltas. Because the reference value is stored once and the deltas are fixed-width, decoding is just “add the minimum back,” which vectorizes trivially, and a range filter can check a block’s min/max header before touching a single value, skipping the block entirely if it can’t possibly match.
Each encoding relies on one specific property of the data distribution, and, as we’ve seen on the timestamp example, an encoding that is fitting for one distribution might not work at all for a different one.
In contrast, compression algorithms like zstd, LZ4, or gzip couldn’t care less what your data means. They don’t know if they’re looking at a column of timestamps, a paragraph of English text, or a JPEG. They operate on raw bytes and find redundancy statistically, through techniques like LZ77-style back-references and entropy coding, rather than through knowledge of a specific data type’s structure. That generality is the whole selling point. A single compressor works on anything because it isn’t restricted to one narrow pattern. Instead, it can often find and eliminate redundancy that a type-specific encoding leaves on the table entirely, such as cross-value patterns, repeated substrings and skewed byte-value distributions.
The cost of that generality shows up at read time. A general-purpose compressor produces an opaque block of bytes. You cannot do binary search on the data, there is no per-value random access and no comparing two values without decoding both of them first. To read anything out of a compressed block, you decompress the whole block back to its original bytes and then operate on that. That’s a perfectly fine trade-off when you’re reading a file end to end, but it’s a costly when a query only needs to check one predicate against a hundred out of a million values in that block.
Talk is cheap, so we ran the numbers. We compared the encoding schemes above against zstd on synthetic data in standalone C++ experiments, then compared the impact of encoding and compression on CedarDB against the ClickBench dataset.
We generated a column of 300 distinct URL paths (16-45 bytes each, sharing locale prefixes and category words), duplicated with a Zipfian skew across 2 million rows, and compared plain dictionary encoding against zstd, including nesting both by zstd-compressing the dictionary’s integer ID array.
| Representation | Size | Ratio vs. raw |
|---|---|---|
| Raw (length-prefixed strings) | 54.0 MB | 1.00x |
| Dictionary encoding | 4.0 MB | 13.47x |
| zstd (level 3) | 5.7 MB | 9.45x |
| zstd (level 19) | 2.8 MB | 19.64x |
| Dictionary + zstd on the ID array (layered) | 2.2 MB | 24.77x |
Zstd alone beats plain dictionary encoding on ratio on one of its highest levels, because it’s finding byte-level redundancy that a fixed-width integer substitution can’t. But layering zstd on top of the dictionary’s already-narrow ID array beats even zstd-19-on-raw, because a column of small fixed-width integers is denser, more regular input than variable-length text. The key idea here is to treat them as separate tools: encode first, then compress the (now much smaller and more regular) result.
The size alone undersells the real story. Let’s look at a simple equality filter (WHERE path = '...'), matching ~300k rows:
| Method | Time |
|---|---|
| Dictionary-encoded | 0.4 ms |
| Zstd-compressed | 64.1 ms |
168x. Not because zstd’s decompressor is slow in absolute terms, it decompresses the whole 54 MB column in about 40 ms, but because a filter against zstd-compressed data has no choice but to fully materialize the column before it can compare a single value. In contrast, the dictionary-encoded filter never needs to leave the encoded representation.
For frame-of-reference, we’ll use 5 million int64 values simulating a timestamp column. The values overall trend upwards, with a slight jitter applied for randomness. Despite both, all values remain within a 500k-wide range. This results in about 19 bits of entropy out of the 64 bits used for each value.
| Representation | Size | Ratio vs. raw |
|---|---|---|
Raw int64 array |
40.0 MB | 1.00x |
| Frame-of-reference | 20.0 MB | 2.00x |
| zstd (level 3) | 10.4 MB | 3.84x |
| zstd (level 19) | 8.9 MB | 4.52x |
| FOR + zstd on the deltas (layered) | 9.5 MB | 4.20x |
Let’s again look at a simple filter query (value BETWEEN ...) matching ~1% of rows:
| Method | Time |
|---|---|
| Frame-of-reference | 3.2 ms |
| Zstd-compressed | 56.5 ms |
17.8x. Smaller than for dictionary encoding, but the trend is the same: an encoding you can filter in place beats one you have to fully unpack first, regardless of who wins on raw bytes.
Compressing data with zstd isn’t free either. Compressing either column at zstd level 19 took 32.6 seconds for the string column and 19.1 seconds for the numeric one, several orders of magnitude slower than encoding, which stayed below 30ms for both.
While synthetic benchmarks are nice to drill down on specifics, what matters is the effect in practice. We’ll use ClickBench’s hits table, a ~100M-row table of semi-real web analytics events, and compare encoding and compression in CedarDB. We load the data twice, once without zstd compression and once with. The cedardb_compression_infos system view gives us an insight on how each column is compressed and encoded. All four columns below get the identical lightweight encoding regardless of whether zstd runs afterward, so what changes is purely compression_ratio, the extra layer zstd adds on top:
| Column | Chosen Encoding | Encoding Ratio | Compression Ratio | Total Ratio |
|---|---|---|---|---|
watchid |
uncompressed | 1.00x | 1.00x | 1.00x |
isrefresh |
truncate | 2.00x | 15.6x | 31.2x |
useragentminor |
sorted-string dictionary | 15.7x | 4.80x | 75.4x |
counterid |
FOR/truncate/dictionary | 4.95x | 63.0x | 312.0x |
For watchid, a near-unique 64-bit ID, neither layer finds anything to work with, and neither encoding nor compression is used. Some data just has high entropy. isrefresh, a heavily skewed flag column, sits at the other end: encoding already achieves a 2x reduction by truncating to a narrower type, but zstd’s entropy coding finds another 15.6x on top of that. counterid is the most interesting, as it chooses three different encodings for different sections of the column. And it shows a clear advantage of using a second-level compressor such as zstd. While dictionary encoding, e.g., can reduce the larger counter to a small fixed-width ID, it can’t do anything about runs of that same ID repeating across many consecutive rows. Encoding can only apply one method at a time to allow efficient operations on the encoded data. Zstd, however, can exploit this additional redundancy, which is where its extra 63x on top of the encoding’s own 4.95x comes from. Across the whole table, that combination shrinks hits from the 21.4 GiB encoded size (compression=none) to 7.88 GiB (compression=zstd), a 2.72x reduction. And the encoding size is already significantly smaller than the 75.56 GiB CSV, which means compression and encoding combined can achieve a 9.59x total size reduction.
And the beauty of it is that compression has no impact on the hot path of data processing, as once the data is in memory, the representation is identical whether data was compressed on disk or not. Compression’s only performance touchpoint is loading cold, non-buffer-resident data from disk. That cost depends on a lot of external factors, such as the number of cores, the throughput of the disk used, and the compression rate. Zstd’s own published benchmarks put single-core decompression at roughly 1.5-2 GB/s, so unpacking even a large batch of touched pages costs single-digit milliseconds. Overall the impact can range from a small penalty for machines with few cores but fast SSDs to a performance improvement for big machines with slow disks. Combined with the cost savings for less storage consumption, there is little downside to using zstd on top of an encoding if it leads to a significant reduction in storage size. For CedarDB, we employ zstd compression if it will further reduce the encoded data on disk by at least 20%.
| Encoding | Compression | |
|---|---|---|
| Data-type aware | Yes | No |
| Typical ratio | Good, pattern-specific | Better, general |
| Cost to apply | Very low | Low to very high |
| Queryable without decode | Yes | No |
| Best fit | Hot, query-touched data | Cold storage, transfer |
Given all that, the trade-off isn’t really a trade-off. Encoding and compression aren’t competing for the same job. They’re complementing each other.
How we handle things here at CedarDB is to always keep data encoded, compress only for storage. Every column always gets the lightweight encoding applied wherever we can, because that’s what the query engine operates on directly. With encoding, a reduction in size is just one of the benefits, as it allows more data to be resident in memory, but it is not the only one. Zstd is layered on top of that encoded representation purely for on-disk storage, and only when it’s actually worth it. That is, if it significantly reduces the size on disk.
So what can you take away? Encode always, compress only where it earns its CPU cost, and don’t see the two as competitors. Instead, let the two layers do the part they’re actually good at.
Want to see how well your data compresses in a modern database system? Give CedarDB a try.
The code below allows you to reproduce the two microbenchmarks.
dict_vs_zstd.cpp
// dict_vs_zstd.cpp
//
// Self-contained experiment comparing dictionary encoding against
// zstd compression on a synthetic categorical string column of website urls
//
// Build:
// g++ -O2 -std=c++20 dict_vs_zstd.cpp -o dict_vs_zstd -lzstd
//
// Run:
// ./dict_vs_zstd
//
#include <zstd.h>
#include <algorithm>
#include <chrono>
#include <cmath>
#include <cstdint>
#include <cstdio>
#include <cstring>
#include <random>
#include <string>
#include <string_view>
#include <vector>
using Clock = std::chrono::steady_clock;
static double ms_since(Clock::time_point t0) {
return std::chrono::duration<double, std::milli>(Clock::now() - t0).count();
}
// A single global RNG seeded with a fixed value so the whole experiment
static std::mt19937 rng(42);
// ---------------------------------------------------------------------------
// Step 1: build ~300 distinct URL-path strings with realistic shared
// structure (locale prefix + category words + fixed suffix), lengths roughly
// in the 10-60 byte range.
// ---------------------------------------------------------------------------
static std::vector<std::string> build_distinct_values(size_t target_count) {
// Locale prefixes: shared substrings across many rows (like "/us/", "/de/").
static const std::vector<std::string> locales = {
"us", "uk", "de", "fr", "jp", "cn", "in", "br", "au", "ca"
};
// Category words of varying length so concatenations span ~10-60 bytes
static const std::vector<std::string> categories = {
"tv", "phones", "books", "toys", "music", "tools", "office", "health",
"beauty", "games", "home", "kids", "shoes", "sports",
"outdoor-camping-gear", "kitchen-and-dining", "automotive-parts",
"womens-fashion-clothing", "mens-fashion-clothing",
"electronics-and-computers", "garden-and-patio-furniture",
"baby-and-toddler-supplies", "pet-supplies-and-accessories",
"movies-and-tv-shows", "software-and-video-games",
"arts-crafts-and-sewing", "musical-instruments", "office-products",
"industrial-and-scientific", "collectibles-and-fine-art"
};
// Occasionally vary the trailing "page" so not every path ends the same way
static const std::vector<std::string> suffixes = {
"index.html", "landing.html", "page.html"
};
std::vector<std::string> values;
values.reserve(target_count);
std::vector<std::string> shuffled_locales = locales;
std::vector<std::string> shuffled_categories = categories;
// Deterministically enumerate locale x category combinations (10 x 30 = 300)
std::uniform_int_distribution<size_t> suffix_pick(0, suffixes.size() - 1);
for (const auto& loc : locales) {
for (const auto& cat : categories) {
if (values.size() >= target_count) break;
std::string s = "/" + loc + "/" + cat + "/" + suffixes[suffix_pick(rng)];
values.push_back(std::move(s));
}
}
return values;
}
// ---------------------------------------------------------------------------
// Step 2: Zipf sampler over `n` ranks (rank 0 = most frequent)
// ---------------------------------------------------------------------------
struct ZipfSampler {
std::vector<double> cumulative; // cumulative probability per rank
std::uniform_real_distribution<double> unif{0.0, 1.0};
explicit ZipfSampler(size_t n, double s = 1.0) {
std::vector<double> weights(n);
double sum = 0.0;
for (size_t r = 0; r < n; ++r) {
weights[r] = 1.0 / std::pow(static_cast<double>(r + 1), s);
sum += weights[r];
}
cumulative.resize(n);
double running = 0.0;
for (size_t r = 0; r < n; ++r) {
running += weights[r] / sum;
cumulative[r] = running;
}
}
size_t sample(std::mt19937& gen) {
double u = unif(gen);
auto it = std::lower_bound(cumulative.begin(), cumulative.end(), u);
return static_cast<size_t>(it - cumulative.begin());
}
};
// A no-op sink to prevent the optimizer from eliding work whose result we never use
static volatile uint64_t g_sink = 0;
int main() {
constexpr size_t kDistinctValues = 300;
constexpr size_t kNumRows = 2'000'000;
// Take average of 5 runs
constexpr int kRepeats = 5;
// ---- Build dictionary values ----
std::vector<std::string> values = build_distinct_values(kDistinctValues);
printf("Built %zu distinct values (target %zu)\n", values.size(), kDistinctValues);
size_t min_len = 1e9, max_len = 0;
for (auto& v : values) { min_len = std::min(min_len, v.size()); max_len = std::max(max_len, v.size()); }
printf("Value length range: %zu - %zu bytes\n", min_len, max_len);
// ---- Sorted dictionary ----
std::vector<std::string> dict_sorted = values;
std::sort(dict_sorted.begin(), dict_sorted.end());
// Map from insertion-order index -> sorted-dictionary ID
std::vector<uint16_t> orig_to_sorted_id(values.size());
for (size_t i = 0; i < values.size(); ++i) {
auto it = std::lower_bound(dict_sorted.begin(), dict_sorted.end(), values[i]);
orig_to_sorted_id[i] = static_cast<uint16_t>(it - dict_sorted.begin());
}
// ---- Sample 2M row assignments with a Zipf skew over insertion order ----
ZipfSampler zipf(kDistinctValues, /*s=*/1.0);
std::vector<uint16_t> row_ids(kNumRows);
for (size_t i = 0; i < kNumRows; ++i) {
size_t orig_idx = zipf.sample(rng);
row_ids[i] = orig_to_sorted_id[orig_idx];
}
// =========================================================================
// Representation 1
// RAW - concatenated strings, each with a uint16_t length prefix
// =========================================================================
// Precompute per-sorted-ID string_views to avoid repeated hashing/lookup.
std::vector<std::string_view> id_to_str(dict_sorted.size());
for (size_t i = 0; i < dict_sorted.size(); ++i) id_to_str[i] = dict_sorted[i];
size_t raw_size = 0;
for (uint16_t id : row_ids) raw_size += 2 + id_to_str[id].size();
std::vector<char> raw_blob;
raw_blob.reserve(raw_size);
for (uint16_t id : row_ids) {
std::string_view s = id_to_str[id];
uint16_t len = static_cast<uint16_t>(s.size());
raw_blob.insert(raw_blob.end(), reinterpret_cast<char*>(&len), reinterpret_cast<char*>(&len) + 2);
raw_blob.insert(raw_blob.end(), s.begin(), s.end());
}
printf("Raw blob built: %zu bytes\n", raw_blob.size());
// =========================================================================
// Representation 2
// DICTIONARY ENCODING
Azure HorizonDB exposes the familiar PostgreSQL statistics views because it is fully compatible with PostgreSQL. However, its compute and storage architecture differs from traditional PostgreSQL. I was curious whether these architectural differences appear in standard PostgreSQL statistics. I performed the same pgbench initialization steps and transactional workload on:
This is not a performance or cost comparison. The instances are not equivalent in compute capacity. The objective is to compare what PostgreSQL itself reports through the cumulative statistics views:
pg_stat_io, which groups I/O operations by backend type, object, and contextpg_stat_checkpointer, which reports checkpoint requests, buffers written, and synchronization timepg_stat_wal, which reports WAL records, full-page images, bytes, writes, and synchronizationsEach experiment below follows the same structure: the raw output, a table of the counters that matter, and the architectural signal that can reasonably be inferred from them.
The experiments follow the natural pgbench initialization order and are state-dependent: table generation, primary keys, foreign keys, and VACUUM each operate on the result of the preceding phase. The central question is why the same wal_fpi counter records full-page images for two reasons: checkpoint-based torn-page protection in conventional PostgreSQL and delivery of a base page image to HorizonDB storage.
I initialized the same pgbench scale factor on both systems: -s 800. I first recreated the empty pgbench tables with pgbench -iIdt -s 800, then ran the initialization phases separately. The scale factor creates 80 million rows in pgbench_accounts and approximately 10 GB of heap data.
For the initialization phases, I:
CHECKPOINT and reset all shared statistics before table generation.pgbench initialization step.CHECKPOINT, so that the statistics included the processing of dirty buffers created by that step.pg_stat_wal, pg_stat_checkpointer, and pg_stat_io.The final transactional workload differs slightly: the statistics had just been reset after the VACUUM phase. I then issued a checkpoint and ran pgbench without a final checkpoint.
I prepared the following query to read the IO statistics:
prepare delta_stat_io as
select
pg_size_pretty(reads * op_bytes) as read,
pg_size_pretty(writes * op_bytes) as write,
pg_size_pretty(extends * op_bytes) as extend,
pg_size_pretty(hits * op_bytes) as hits,
*
from pg_stat_io
where row(
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
) <> row( reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, hits, evictions, reuses, fsyncs, fsync_time )
order by coalesce(reads, 0) + coalesce(writes, 0) desc
;
I did not enable track_io_timing and track_wal_io_timing, so timing was not collected. I am interested in the number of calls, blocks, and bytes.
I set up the two instances with shared buffer allocations that are intentionally close so that the I/O patterns can be compared: 12 GB for PostgreSQL and 11 GB for HorizonDB.
Conventional PostgreSQL buffers pages in two memory pools: userspace in PostgreSQL shared buffers and kernel space in the operating-system filesystem cache. HorizonDB avoids this double caching and provides compute replicas with a local NVMe page cache, allowing a larger share of RAM to be allocated to shared buffers. I provisioned Azure HorizonDB (Preview) with 2 vCores and 16 GiB RAM. Its 11241MB setting represents approximately 70% of that memory. Because PostgreSQL relies on the filesystem cache and allocates 25% of RAM to shared buffers, I provisioned Azure Database for PostgreSQL Flexible Server with 12 vCores and 48 GiB RAM.
| Parameter | PostgreSQL | HorizonDB |
|---|---|---|
shared_buffers |
12GB | 11241MB |
effective_cache_size |
36GB | 11241MB |
Note that effective_cache_size does not allocate memory. It is an estimate used by the planner. On conventional PostgreSQL, it typically includes the expected contribution of the operating-system filesystem cache, in addition to shared_buffers.
Among the other parameters, the most important difference is full_page_writes. In PostgreSQL, it is on, so the first modification of a page after a checkpoint can log a full-page image rather than just the change vector. This allows recovery to restore a page affected by a partial write. HorizonDB protects against torn pages in the distributed storage layer, so it is set to off to reduce the WAL generated.
The WAL-file-management parameters also differ:
| Parameter | PostgreSQL | HorizonDB |
|---|---|---|
wal_init_zero |
on | off |
wal_recycle |
on | off |
max_wal_size |
2GB | 12GB |
checkpoint_timeout |
10min | 200s |
data_checksums |
on | off |
restart_after_crash |
on | off |
fsync |
on | on |
wal_sync_method |
fdatasync | fdatasync |
These settings do not fully describe the storage implementation, but they show that HorizonDB does not manage WAL files and checkpoint scheduling exactly as conventional PostgreSQL does.
This first experiment establishes the baseline: the same logical work, the same buffer manager, and the first visible divergence in WAL composition.
I generated the heap data without indexes:
checkpoint;
select pg_stat_reset_shared();
\! pgbench -iIG -s 800
checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();
PostgreSQL Flexible Server output:
generating data (server-side)...
done in 203.84 s (server-side generate 203.84 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
80009422 | 361 | 12160725691 | 560783 | 561412 | 778 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 11 | 0 | 0 | 0 | 121159 | 49123 | 1311879
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+---------+------------+---------+----------+----------+-----------+--------+-------
| 10 GB | | | checkpointer | relation | normal | | 1311879 | 1311879 | | 8192 | | | | 63
296 kB | 0 bytes | 10 GB | 631 GB | client backend | relation | normal | 37 | 0 | 0 | 1311855 | 8192 | 82658063 | 0 | | 0
64 kB | 0 bytes | 8192 bytes | 19 MB | autovacuum worker | relation | normal | 8 | 0 | 0 | 1 | 8192 | 2384 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 136 kB | autovacuum worker | relation | vacuum | 0 | 0 | 0 | 0 | 8192 | 17 | 0 | 0 |
(4 rows)
HorizonDB output:
generating data (server-side)...
done in 144.40 s (server-side generate 144.40 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
81321067 | 1312186 | 12544965939 | 0 | 2220 | 2125 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 2 | 0 | 0 | 0 | 109454 | 2 | 1311878
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
---------+---------+----------+--------+-------------------+----------+---------+-------+---------+------------+---------+----------+----------+-----------+--------+-------
| 10 GB | | | checkpointer | relation | normal | | 1311878 | 1311879 | | 8192 | | | | 0
72 kB | 0 bytes | 32 kB | 17 MB | autovacuum worker | relation | normal | 9 | 0 | 0 | 4 | 8192 | 2212 | 0 | | 0
32 kB | 0 bytes | 10 GB | 631 GB | client backend | relation | normal | 4 | 0 | 0 | 1311855 | 8192 | 82656973 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 40 kB | background worker | relation | normal | 0 | 0 | 0 | 0 | 8192 | 5 | 0 | | 0
(4 rows)
At the PostgreSQL buffer-manager level, these executions are nearly identical:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| Relation blocks extended | 1,311,855 | 1,311,855 |
| Relation size extended | 10 GB | 10 GB |
| Shared-buffer hits | 82,658,063 | 82,656,973 |
| Hit volume | 631 GB | 631 GB |
| Checkpointer buffers written | 1,311,879 (~10 GB) | 1,311,878 (~10 GB) |
The PostgreSQL query layer created the same number of relation pages, performed nearly the same number of shared-buffer accesses, and passed essentially the same number of dirty buffers to the checkpointer.
HorizonDB retains PostgreSQL's checkpointer process and buffer-management accounting. The statistics show an active checkpointer processing the same volume of dirty buffers. On HorizonDB, those writes maintain the compute replica's local SSD page cache but do not make relation pages durable. Durability and high availability are offloaded to the storage layer.
The volume processed by the checkpointer is the same, but the synchronization is not:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
Checkpointer sync_time
|
49,123 ms | 2 ms |
Relation fsyncs
|
63 | 0 |
Only PostgreSQL Flexible Server exposes conventional relation-file synchronization activity. Therefore, the writes counter cannot be interpreted the same way on both systems. It records a buffer write operation visible to PostgreSQL. On HorizonDB, that operation can populate or update the local cache without participating in durability.
The WAL remains at the core of durability. Both systems generated approximately 12 GB of WAL:
pg_stat_wal column |
PostgreSQL Flexible Server | HorizonDB |
|---|---|---|
wal_bytes |
12,160,725,691 | 12,544,965,939 |
wal_records |
80,009,422 | 81,321,067 |
wal_fpi |
361 | 1,312,186 |
wal_buffers_full |
560,783 | 0 |
wal_write |
561,412 | 2,220 |
wal_sync |
778 | 2,125 |
WAL plays a broader role in HorizonDB than in conventional PostgreSQL, and its generation is adapted to that role:
Why wal_fpi is low on PostgreSQL and high on HorizonDB?
In PostgreSQL, a large heap load creates many new pages but logs very few full-page images in the WAL. The pages are created directly in shared buffers, and the WAL records describing their creation are sufficient to reconstruct them during recovery. Full-page images are generated when an existing page is read into shared buffers and modified after a checkpoint, because recovery then needs a reliable base image to apply incremental changes. In this case, the base is an empty page.
The high wal_fpi on HorizonDB may be surprising, especially since full_page_writes = off. These images were not produced by the "first modification after checkpoint" rule. Because pages from shared buffers are not written to storage as relation files, brand-new pages never reach the storage layer. HorizonDB therefore sends the full image rather than an incremental change vector. The 1,312,186 FPIs are close to, but not exactly equal to, the 1,311,855 blocks extended. Other activity in the interval accounts for the aggregate counters not being one-to-one. The resulting WAL volume is essentially the same on both systems.
This phase shows the first clear divergence in WAL generation. Building the B-tree indexes creates new index pages and can modify those pages again as the build proceeds.
\! pgbench -iIp -s 800
checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();
Because the preceding statistics reset occurred after the data-generation checkpoint, this interval excludes the heap-loading phase.
PostgreSQL Flexible Server output
creating primary keys...
done in 123.93 s (primary keys 123.93 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+------------+------------------+-----------+----------+----------------+---------------
1975171 | 2187495 | 2506687103 | 0 | 459 | 459 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 2 | 0 | 0 | 0 | 84374 | 588 | 1311559
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
------------+---------+------------+---------+-------------------+----------+----------+-------+---------+------------+---------+----------+--------+-----------+--------+-------
| 10 GB | | | checkpointer | relation | normal | | 1311559 | 1311559 | | 8192 | | | | 46
136 kB | 0 bytes | 0 bytes | 124 MB | client backend | relation | normal | 17 | 0 | 0 | 0 | 8192 | 15897 | 0 | | 0
40 kB | 0 bytes | 0 bytes | 1928 kB | background worker | relation | normal | 5 | 0 | 0 | 0 | 8192 | 241 | 0 | | 0
8192 bytes | 0 bytes | 8192 bytes | 7720 kB | autovacuum worker | relation | normal | 1 | 0 | 0 | 1 | 8192 | 965 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 736 kB | autovacuum worker | relation | vacuum | 0 | 0 | 0 | 0 | 8192 | 92 | 0 | 0 |
0 bytes | 0 bytes | | 5117 MB | client backend | relation | bulkread | 0 | 0 | 0 | | 8192 | 654924 | 0 | 0 |
0 bytes | 0 bytes | | 5129 MB | background worker | relation | bulkread | 0 | 0 | 0 | | 8192 | 656552 | 0 | 0 |
(7 rows)
HorizonDB output
creating primary keys...
done in 76.90 s (primary keys 76.90 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
78812 | 219396 | 844376348 | 0 | 594 | 527 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 1 | 0 | 0 | 0 | 61364 | 1 | 1091695
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
------------+---------+---------+---------+-------------------+----------+---------+-------+---------+------------+---------+----------+--------+-----------+--------+-------
| 8529 MB | | | checkpointer | relation | normal | | 1091695 | 1311555 | | 8192 | | | | 0
96 kB | 0 bytes | 0 bytes | 3762 MB | client backend | relation | normal | 12 | 0 | 0 | 0 | 8192 | 481524 | 0 | | 0
8192 bytes | 0 bytes | 32 kB | 1132 MB | autovacuum worker | relation | normal | 1 | 0 | 0 | 4 | 8192 | 144890 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 6628 MB | background worker | relation | normal | 0 | 0 | 0 | 0 | 8192 | 848358 | 0 | | 0
(4 rows)
The logical work here is different from the heap load. Building a B-tree requires scanning the existing table, sorting the keys, and writing index pages. The statistics reflect another cache difference. On PostgreSQL Flexible Server, the table scan appears in the bulkread context. PostgreSQL uses a small ring of shared buffers for large scans so it does not displace useful pages from both shared buffers and the filesystem cache. HorizonDB reports the scan in the normal context. With one compute page-cache hierarchy rather than PostgreSQL plus the filesystem cache, it does not need the same protection against polluting two caches.
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| shared-buffer hits | 10 GB bulkread
|
11 GB normal
|
| WAL records | 1,975,171 | 78,812 |
| Full-page images | 2,187,495 | 219,396 |
| WAL volume | 2.51 GB | 844 MB |
| Checkpointer writes | 1,311,559 | 1,091,695 |
Relation fsyncs
|
46 | 0 |
The whole table was cached on both systems, so the indexes were built essentially from memory.
Both systems created the same indexes, yet HorizonDB generated about one third of the WAL volume and one tenth of the full-page images. The gap is wider than during heap generation. B-tree construction creates new index pages and subsequently modifies pages created during the build. On conventional PostgreSQL, modifications after the checkpoint can trigger full-page images because full_page_writes = on. On HorizonDB, new pages require base images, while later modifications can be represented by incremental WAL once storage has a valid base. HorizonDB still shows substantial checkpointer activity — more than one million dirty buffers processed — but avoids most checkpoint-driven FPI overhead.
One accounting detail is worth noting before it is misread. In the HorizonDB output, writes = 1,091,695 and writebacks = 1,311,555. These are different PostgreSQL accounting events and must not be added together to estimate physical storage traffic. Neither is necessarily a unique durable page write, particularly with a distributed storage layer underneath.
In HorizonDB, the PostgreSQL instance on compute still manages buffers, WAL, and checkpoints. Durability and recovery protection are offloaded from the traditional compute-side combination of relation-file writes, fsync operations, and checkpoint-driven full-page images to the storage layer.
I created the foreign keys as the next natural pgbench initialization step:
\! pgbench -iIf -s 800
checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();
Foreign-key creation primarily validates existing data. Because this article focuses on writes and full-page images, this read-oriented phase adds no useful architectural signal. I keep it in the sequence because the following VACUUM operates on the database state it produced, but omit its statistics.
This is the most revealing experiment of the article.
The preceding checkpoint establishes a clean recovery boundary, and VACUUM then revisits nearly every page in the database. If a checkpoint-related page-protection mechanism exists, it must appear in the WAL statistics here.
VACUUM is also one of PostgreSQL's most disliked operational costs because its work can generate substantial I/O and WAL activity and is difficult to predict. Offloading durability work and avoiding checkpoint-driven FPIs make that maintenance path lighter and more predictable, even though VACUUM remains part of PostgreSQL itself.
\! pgbench -iIv -s 800
checkpoint;
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();
PostgreSQL Flexible Server output
vacuuming...
done in 91.63 s (vacuum 91.63 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-------------+------------------+-----------+----------+----------------+---------------
1311609 | 1311543 | 1128836191 | 0 | 466 | 466 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 2 | 0 | 0 | 0 | 61867 | 707 | 1311543
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
---------+---------+---------+------------+-------------------+----------+---------+-------+---------+------------+---------+----------+---------+-----------+--------+-------
| 10 GB | | | checkpointer | relation | normal | | 1311543 | 1311543 | | 8192 | | | | 31
0 bytes | 0 bytes | 344 kB | 10 GB | client backend | relation | normal | 0 | 0 | 0 | 43 | 8192 | 1330219 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 10 GB | client backend | relation | vacuum | 0 | 0 | 0 | 0 | 8192 | 1341529 | 0 | 0 |
0 bytes | 0 bytes | 0 bytes | 8376 kB | autovacuum worker | relation | normal | 0 | 0 | 0 | 0 | 8192 | 1047 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 8192 bytes | autovacuum worker | relation | vacuum | 0 | 0 | 0 | 0 | 8192 | 1 | 0 | 0 |
(5 rows)
HorizonDB output
vacuuming...
done in 4.97 s (vacuum 4.97 s).
CHECKPOINT
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
994350 | 74 | 58687203 | 0 | 65 | 60 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
0 | 1 | 0 | 0 | 0 | 56522 | 1 | 994230
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
| 7767 MB | | | checkpointer | relation | normal | | 994230 | 994230 | | 8192 | | | | 0
88 kB | 0 bytes | 8192 bytes | 667 MB | autovacuum worker | relation | normal | 11 | 0 | 0 | 1 | 8192 | 85341 | 0 | | 0
0 bytes | 0 bytes | 248 kB | 15 GB | client backend | relation | normal | 0 | 0 | 0 | 31 | 8192 | 1947191 | 0 | | 0
(3 rows)
Here are the interesting statistics:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| WAL records | 1,311,609 | 994,350 |
| Full-page images (FPI) | 1,311,543 | 74 |
| WAL bytes | 1.13 GB | 58.7 MB |
| Checkpointer buffers written | 1,311,543 | 994,230 |
In PostgreSQL, WAL FPI corresponds to the number of buffers written. While this strong correlation suggests that checkpoint-triggered full-page images are in use, these aggregate counters do not confirm page-by-page accuracy: wal_fpi tracks images in WAL, whereas buffers_written reflects checkpointer write operations. This pattern aligns exactly with what full_page_writes = on aims to achieve after a checkpoint: the initial modification of a page logs a complete image, ensuring recovery does not depend on potentially partial relation-page writes.
HorizonDB shows the opposite pattern, with nearly one million dirty buffers handled by the PostgreSQL checkpointer, yet only 74 full-page images were produced. The checkpointer remains operational, and its writes primarily update the cache state instead of following PostgreSQL's conventional durable relation-file recovery method.
The WAL volume makes the effect concrete: the same maintenance operation produced roughly twenty times less WAL on HorizonDB (1,128,836,191 bytes versus 58,687,203 bytes).
This single experiment explains most of the WAL differences observed in the other phases. It isolates the recovery semantics from the workload itself: both systems modified a large number of existing pages after a checkpoint, but only conventional PostgreSQL had to protect them with full-page images.
The checkpointer counters confirm that the buffer processing is real on both sides:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| Buffers written | 1,311,543 | 994,230 |
Checkpointer sync_time
|
707 ms | 1 ms |
Relation fsyncs
|
31 | 0 |
HorizonDB offloads the filesystem-oriented durability work traditionally associated with checkpoints: relation-file synchronization and checkpoint-driven full-page-image logging. Compute-side writes can still be useful for the local SSD cache, while page durability and crash recovery are handled in the storage layer.
Initialization exercises involve bulk operations for a specific purpose. This phase verifies if the VACUUM observation applies also to regular OLTP activity. I executed the built-in pgbench transaction for 15 minutes:
checkpoint;
\! pgbench -n -c 10 -T 900
select * from pg_stat_wal;
select * from pg_stat_checkpointer;
execute delta_stat_io;
select pg_stat_reset_shared();
The statistics had already been reset by the final statement of Experiment 4. The explicit checkpoint shown here is included in this interval, which matches num_requested = 1 in both outputs.
Unlike the initialization phases, I did not issue a final checkpoint before reading the statistics. The counters therefore show work performed during the 900-second interval, but not necessarily the eventual processing of every page dirtied by it.
PostgreSQL Flexible Server output
pgbench (16.2, server 17.10)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 800
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 44053
number of failed transactions: 0 (0.000%)
latency average = 203.822 ms
initial connection time = 2294.257 ms
tps = 49.062339 (without initial connection time)
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
472835 | 85311 | 228228681 | 0 | 46112 | 46112 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
1 | 1 | 0 | 0 | 0 | 25 | 1 | 31920
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
------------+---------+---------+-------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
318 MB | 0 bytes | 8312 kB | 18 GB | client backend | relation | normal | 40714 | 0 | 0 | 1039 | 8192 | 2386621 | 0 | | 0
| 249 MB | | | checkpointer | relation | normal | | 31920 | 31904 | | 8192 | | | | 0
8192 bytes | 0 bytes | 72 kB | 99 MB | autovacuum worker | relation | normal | 1 | 0 | 0 | 9 | 8192 | 12702 | 0 | | 0
0 bytes | 0 bytes | 0 bytes | 31 MB | autovacuum worker | relation | vacuum | 0 | 0 | 0 | 0 | 8192 | 3904 | 0 | 0 |
(4 rows)
HorizonDB output
pgbench (16.2, server 17.9 (Azure HorizonDB (1b3bcd789c4)(release)))
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 800
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 900 s
number of transactions actually processed: 44530
number of failed transactions: 0 (0.000%)
latency average = 201.639 ms
initial connection time = 2302.203 ms
tps = 49.593642 (without initial connection time)
wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time
-------------+---------+-----------+------------------+-----------+----------+----------------+---------------
466156 | 1273 | 35362656 | 0 | 44789 | 44789 | 0 | 0
(1 row)
num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+----------------
4 | 1 | 0 | 0 | 0 | 890 | 12 | 79389
(1 row)
read | write | extend | hits | backend_type | object | context | reads | writes | writebacks | extends | op_bytes | hits | evictions | reuses | fsyncs
---------+---------+------------+--------+-------------------+----------+---------+-------+--------+------------+---------+----------+---------+-----------+--------+-------
| 620 MB | | | checkpointer | relation | normal | | 79389 | 79448 | | 8192 | | | | 0
321 MB | 0 bytes | 8448 kB | 18 GB | client backend | relation | normal | 41101 | 0 | 0 | 1056 | 8192 | 2421167 | 0 | | 0
0 bytes | 0 bytes | 8192 bytes | 141 MB | autovacuum worker | relation | normal | 0 | 0 | 0 | 1 | 8192 | 18044 | 0 | | 0
(3 rows)
The logical workload executed by both systems was almost identical:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| Transactions | 44,053 | 44,530 |
| Average latency | 203.8 ms | 201.6 ms |
| TPS | 49.1 | 49.6 |
| Client reads | 318 MB | 321 MB |
| Shared-buffer hits | 18 GB | 18 GB |
| WAL records | 472,835 | 466,156 |
I am not using this as a performance result. It matters because it establishes that the two systems executed comparable transactional work and produced comparable buffer activity.
The difference is entirely in WAL composition:
| Metric | PostgreSQL | HorizonDB |
|---|---|---|
| Full-page images | 85,311 | 1,273 |
| WAL volume | 228 MB | 35 MB |
The WAL-record counts are similar, while PostgreSQL generated about 67 times more full-page images and 6.45 times the WAL volume. The similar transaction, buffer-access, and WAL-record counts indicate comparable logical work. They do not make the environments identical. The server versions, compute capacity, and checkpoint schedules differ, with one timed checkpoint on PostgreSQL Flexible Server and four on HorizonDB. Within those limits, the result is consistent with the different page-durability mechanisms observed in the preceding phases.
The PostgreSQL execution path stays recognizable on both systems:
| Observation | PostgreSQL | HorizonDB |
|---|---|---|
| Relation reads visible | Yes | Yes |
| Shared-buffer hits visible | Yes | Yes |
| WAL writes visible | Yes | Yes |
| Checkpointer writes visible | Yes | Yes |
Relation fsyncs
|
None in interval | None |
This confirms that the behavior observed during VACUUM is not limited to bulk or maintenance operations. During normal transactional processing, HorizonDB still relies on PostgreSQL WAL generation and checkpoints but avoids full-page-image logging after checkpoints.
The names and views of PostgreSQL processes remain familiar, but their actual meanings have shifted. A write showing up in the HorizonDB checkpointer indicates that PostgreSQL processed a dirty buffer and might have updated the local SSD cache on the compute replica. However, this does not mean a typical local relation-file write made the page durable.
This distinction aligns with the ARIES recovery algorithm, which allows the database to use a no-force policy—meaning commit doesn't require all pages to be written to their durable locations as long as the WAL is durable. HorizonDB extends this separation: compute relation writes serve as cache updates, while the durable WAL and page states are stored in the storage layer.
The role of WAL also evolves. In traditional PostgreSQL, WAL protects changes until relation pages are durable, supporting crash recovery and replication. In HorizonDB's database-as-a-log architecture, WAL becomes the primary write stream and can directly participate in reading a page by applying changes to an earlier stored version.
The experiments reveal two storage architectures beneath the same PostgreSQL database engine, with the same SQL processing and transaction semantics.
In PostgreSQL Flexible Server, dirty relation pages flow from shared buffers to durable relation files. Checkpoints coordinate those writes and full-page images protect recovery from partial page writes. WAL accompanies the data path mainly for crash recovery and replication.
In HorizonDB's compute instance, relation writes maintain a local SSD cache and are not the durability path. WAL is made durable in the storage layer, where it is also used to materialize pages read by the compute layer. A read can start from an earlier page image and apply WAL up to the requested LSN. Full-page images establish base versions for pages that storage has not seen before, rather than protecting local relation-file writes at every checkpoint boundary.
The familiar checkpointer and WAL counters therefore remain useful, but they describe logical PostgreSQL work at an architectural boundary. In HorizonDB, durability is offloaded, compute writes serve the cache, and WAL is part of both writing and reading the database.
This week, I presented this paper at ICCCN'26. This is joint work with Aleksey Charapko (University of New Hampshire) and my MongoDB colleagues Matt Broadstone, Daniel Gomez Ferro, and Akshat Vig. The paper investigates how to build a metastability tolerant execution control system (ECS) for a database.
Modern databases are complex networked systems serving mixed workloads: short queries (that want an answer in milliseconds) sitting next to analytics jobs (that want the CPU for multiple seconds). The arrival rate of requests is effectively unbounded, but of course, the server's resources are not. And, unfortunately, elastic scaling does not save you here. Scaling takes minutes, whereas, overload takes seconds. Admission control tries to guard the front door (more on this later), but the component that mediates contention once requests reach the backend is the execution control system (ECS).
Unlike a closed system OS scheduler, which strives for fairness and completeness by giving runtime for every thread, faced with an open environment the ECS can only afford to protect short latency-sensitive queries and shed the excess, pushing the burden of waiting back across the network to the clients. This doesn't mean that long tasks are starved, as they can retry until capacity permits execution.
However, shedding load across a network is risky business. Clients do not see the server slow down, instead they time out and retry aggressively. Moreover, workloads are also unpredictable. A query that looks short may hang on a lock or blow up into a scan. The combination of delayed signals, retries, and misclassification makes the cloud databases a fertile ground for failures.
The specific failure we worry about here is metastability: the system gets pushed into a degraded state, and the degraded state sustains itself even after the original trigger is removed. The mechanisms you build for resilience (the retries and the queues) turn into positive feedback loops after a trigger (overload, cache failure, etc). This is not a rare exotic problem. Since production systems would have already been hardened to handle the obvious failures, what remains is these hard-to-detect emergent failures. The OSDI'22 study, where Aleksey was a coauthor, collected 22 metastable incidents across 11 organizations and found that at least 4 of the 15 major AWS outages in the preceding decade were metastable failures, with durations running from 1.5 to 73 hours. Retries were the sustaining mechanism in more than half of the studied incidents. There is no single reset button in a distributed system, so the ECS must break the feedback loop without things escalating into metastable failures.
The ECS mechanism looks deceptively simple. A ticket is a permit to occupy a thread, and there are fixed ticket pools that cap concurrency. If a task cannot get a ticket, it queues up. There are two queues: high priority for short tasks, low priority for long ones. Since you cannot classify a task's cost a priori (the query optimizer's estimate is merely a suggestion), every task starts in the high-priority queue and gets demoted only when it proves itself long by exceeding the brief execution time assigned to short tasks. The queues are bounded, and when they fill, we shed tasks. Shed happens (pun intended!) either at the entry or at mid-stream at the demotion time.
However, the trouble starts in the execution of the policy, deciding how many tickets each queue gets and where the demotion threshold sits. Production systems are too complex, and metastable failures are too well hidden during normal operation, so these prevent us to tune things by trial and error. So we need to trace how overload propagates through queues and retry logic before deployment.
To address this problem, we (well, Aleksey Charapko) built MESSI (MEtaStability SImulator), a discrete-event simulator written in Go. MESSI models a system as a graph: Logic Nodes hold the decision logic (where this work goes next), and Processors simulate execution (delays for both service time and queuing time). The runtime is scriptable, so you can inject failures, slowdowns, and configuration changes mid-run and watch how the system responds. MESSI proved to be crucial for our exploration of the ECS design space, which is full of interacting variables and hidden feedback loops. Without cheap rapid iteration we would not have isolated the mechanisms that matter for metastability tolerance. (I talked about MESSI earlier last month, when making a case for simulation-driven resilience for agentic data systems.)
Our first dynamic policy was reasonable-sounding: each queue independently probes its ticket count up and down, keeping changes that improve the ticket-acquisition rate, an easy-to-observe quantity that intuitively tracks throughput.
However, it turns out under overload, the ticket-acquisition metric lies. A ticket bounds wall-clock time, not the CPU time. With one core and two tickets, each task gets 5ms actual runtime in its 10ms window. (You want some concurrency to avoid IO blocking, and to enable CPU to be productive by switching to another task). Now, consider one core and ten tickets: each task gets about 1 ms of CPU and 9 ms of waiting inside its 10 ms window, then each releases the ticket to re-acquire it later. Although the ticket acquisition rate scaled by 5x here, the actual progress is capped at most at 10 ms of service per 10 ms, no matter how many tickets you issue. That means, the metric was not actually measuring progress, but it was measuring churn.
So under overload, the long queue, which always has a deep wait set under overload, inflated its tickets to pump its metric. The extra threads crowded the shared CPU, which made the short tasks start to queue up, which caused the short queue inflate its own tickets in response. Each policy's corrective action degraded the other's environment, which triggered more corrective action. The escalation stopped only when the long queue hit its static ticket cap. That cap did not fix the feedback loop, but it just put a ceiling on how bad the loop could get. This is how easy it is for a metastability failure to raise out of two individually sensible controllers. Metastability often happens to reasonably designed systems whose parts are reasonable separately.
The fix we applied is to freeze the long queue's tickets at a low static value and probe only the short queue. This leaves a single decision site: with one controller instead of two, there is no race between competing corrections. However, starving the long queue would waste capacity in light load, so we compensated by making the demotion threshold dynamic, again with one rule. If the short queue is empty, raise the demotion threshold by 10% (let longer tasks enjoy high priority while there is room), and if there are any tasks waiting in short queue lower the threshold by 10% (demote more aggressively, protect the fast lane).
Note what changed. The control signal went from a gameable one (acquisition rate, inflatable by churn) to an ungameable one (is anyone actually waiting). And instead of two controllers fighting over a shared resource, there is one controller and one signal.
A typical deployment puts an admission control service in front of the database, rejecting requests when a latency signal exceeds a limit. Our experiments also found that admission control and the ECS destructively interfered.
Here is the intuition. Admission control cannot tell short tasks from long, so it sheds indiscriminately, dropping exactly the short tasks the ECS exists to protect. Worse, the latency signal it relies on can be corrupted by the ECS: a genuinely short task that accrues queueing delay gets demoted and exits the system labeled long, so the short-task latency metric looks healthy precisely when short tasks are suffering. Therefore the control loop at the admission control and the ECS, reacting at similar speeds to each other's output, can produce a sawtooth oscillation where goodput never reaches what the ECS achieves alone. These two well-meaning defenses, each individually stabilizing, jointly do worse than either.
After identifying the problem, the fixes are easy. Here are what the potential fixes would look like. Stop guessing from the outside, and have the ECS export a distress signal (short tasks hurting, and my own knobs are exhausted), and let admission control reject only while that signal is up. Or make the outer admission control loop deliberately sluggish, an order of magnitude slower than the ECS's own convergence time, so the two controllers cannot destructive interfere in resonance sawtooth manner (i.e., the outer defense should engage only after the inner defense has demonstrably run out of moves).
I have two takeaways from the project.
First, performance IS availability. We are used to treating them as separate concerns, one for the performance team's dashboards and one for the postmortems. But metastability erases that boundary, as it can turn a performance problem into an availability problem under certain conditions. These failures live on a spectrum rather than a binary outcome, and traditional formal methods, which excel at safety and correctness, are not built to capture that complexity.
Second, you should simulate before you deploy. Simulations explore many failure modes quickly, and more importantly they surface behaviors you did not think to test for (nobody writes a unit test for "the metric rewards churn"). And simulation is cheap. One person working a few hours per week can model a lot (especially with MESSI). If you don't do the simulations, the alternative is discovering your feedback loops in production.
I want to end with a decompositional framing investigation of the problem. Both failures we described arose from composition. Each component's corrective action degraded its neighbor's operating conditions, and after a shock, neither could stabilize because neither's assumptions held while the other was also trying to "recover".
Last week, I reviewed a recent line of work that frames metastability exactly this way, as a sin of composition among individually self-stabilizing components. Each component gets a potential function, a measure of its distance from a good state that its corrective actions are supposed to decrease, plus an explicit statement of the environment it assumes while correcting. A metastable fault arises when components are wired so that one's correction raises another's potential, and the fault becomes a failure when the schedule keeps selecting those destabilizing interactions.
Let's reposition our results back through that lens. Ticket-acquisition rate was an invalid potential function: it improved while the true distance from health grew, because churn inflates it. Wait-set occupancy, the signal behind our threshold fix, is a valid one: it is zero exactly when short tasks are fine, and no amount of churn can fake it. And the fix itself is a layered composition: Freezing the long queue's tickets deleted one controller's ability to disturb the shared resource. Gating the threshold-raise on "short wait set empty, and it has stayed empty" means the upper layer acts only after the lower layer has demonstrably converged. That is healing bottom-up.
The same recipe suggests a principled fix for the admission control interference: have the ECS export a distress bit (short tasks hurting and my own knobs are exhausted) instead of letting admission control infer health from a corruptible latency signal, and make the outer loop deliberately slower than the inner loop's convergence time so the two cannot resonate.
Note that the theoretical framework tells you what properties a good potential function must have, but it cannot provide you one. And it is tricky to find the right metric. We found the right signal by watching the whole system lie to us in simulation. It would not be possible to find it by local reasoning about components. The theory names the sin of composition, but only the simulation catches you when and how that sin manifests.
1. What it is about This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful. Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, … Continued
The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.
Percona Server for MongoDB 8.3 is available today as a Technical Preview. It is not for production. It is for your lab, your staging cluster, and your benchmark harness – and for sharing with us what works and what does not. Especially if this version is your segue to leverage upcoming full-text and vector search … Continued
The post Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on Percona.
Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data. That’s a great idea. In fact, I’d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it’s a good candidate […]
The post Partitioning a Huge Table first appeared on Michael J. Swart.
