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:

• Azure Database for PostgreSQL Flexible Server, which runs the communtity PostgreSQL and extensions
• Azure HorizonDB (Preview), which is PostgreSQL with disaggregated storage

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 context
• pg_stat_checkpointer, which reports checkpoint requests, buffers written, and synchronization time
• pg_stat_wal, which reports WAL records, full-page images, bytes, writes, and synchronizations

Each 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.

Experimental method

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:

1. Issued CHECKPOINT and reset all shared statistics before table generation.
2. Ran one pgbench initialization step.
3. Issued CHECKPOINT, so that the statistics included the processing of dirty buffers created by that step.
4. Read pg_stat_wal, pg_stat_checkpointer, and pg_stat_io.
5. Reset the shared statistics before continuing to the next dependent step.

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.

Configuration

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.

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:

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.

Experiment 1: Generate the table data

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:

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:

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:

WAL plays a broader role in HorizonDB than in conventional PostgreSQL, and its generation is adapted to that role:

• PostgreSQL writes permanent relation pages at checkpoint or eviction. WAL protects changes until those page writes become durable and provides the change stream for crash recovery and replication.
• In HorizonDB's database-as-a-log architecture, compute sends WAL to durable storage instead of sending data pages. Storage can apply records asynchronously, or apply them to an earlier page version when that page is read. WAL is therefore part of both the write path and the page-read path.

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.

Experiment 2: Create primary keys

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.

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.

Experiment 3: Create foreign keys

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.

Experiment 4: VACUUM — the key experiment

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:

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:

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.

Experiment 5: Transactional workload

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:

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:

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:

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.

What the counters ultimately mean

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.

Conclusion

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.

Why?

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.

What?

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.)

We discovered a metastable behavior!

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.

Admission control can also interfere

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). 

So what?

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.

The composition problem

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 […]

SQL is quite flexible, making it easy to write a single query that works in two situations: one without a filtering parameter and one with a parameterized filter. For example, I came across a benchmark comparing MongoDB and PostgreSQL handling pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page typically uses only ORDER BY and LIMIT, while subsequent pages add a WHERE condition to continue from the last value returned.

In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios.

export async function getOrders(cursor) {
  const match = cursor ? { timestamp: { $gt: new Date(cursor) } } : {};
  const rows = await orders
    .aggregate([
      { $match: match },
      { $sort: { timestamp: 1 } },
      { $limit: PAGE_SIZE },
    ])

The PostgreSQL version uses a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way:

SELECT *
     FROM orders
     WHERE $1::timestamptz IS NULL OR "timestamp" > $1
     ORDER BY "timestamp" ASC
     LIMIT ${PAGE_SIZE}

If $1 is NULL, the predicate evaluates to true for every row, effectively disabling the filter and returning the first rows in timestamp order without applying any filter. When $1 has a value, it filters the results using that specific value, enabling a more targeted search.

However, expressing both use cases in a single generic predicate may result in an execution plan that is suboptimal for some parameter value.

I gave it a try:

drop table if exists orders;

create table orders (
  order_id text primary key,
  "timestamp" timestamptz not null
);

create index idx_orders_timestamp
  on orders("timestamp");

insert into orders
select
    'ORD-'||g,
    '2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;

analyze orders;

prepare getorders(timestamptz, int) as
select * from orders
     where $1::timestamptz is null or "timestamp" > $1
     order by "timestamp" asc limit $2
;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

Here is the execution plan:

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit (actual time=0.028..0.031 rows=10.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using idx_orders_timestamp on orders (actual time=0.027..0.029 rows=10.00 loops=1)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.117 ms
 Execution Time: 0.042 ms

This looks great because the planner was able to evaluate date'2026-01-01'::timestamptz is null during planning and simplify the predicate. The index scan is efficient, reading just about rows=10.00 entries.

Let's see what happens when PostgreSQL uses a generic plan instead of a custom one:

set plan_cache_mode to force_generic_plan;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

It's still an Index Scan, but without an Index Cond to narrow the scan range - the predicate is an after-scan Filter:

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Limit (actual time=71.945..71.948 rows=10.00 loops=1)
   Buffers: shared hit=4786
   ->  Index Scan using idx_orders_timestamp on orders (actual time=71.943..71.945 rows=10.00 loops=1)
         Filter: (($1 IS NULL) OR ("timestamp" > $1))
         Rows Removed by Filter: 525600
         Index Searches: 1
         Buffers: shared hit=4786
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning Time: 0.030 ms
 Execution Time: 71.971 ms

Since the generic plan must work for all possible values of $1, PostgreSQL cannot transform the predicate into an Index Cond on "timestamp". As a result, it reads 525600 unnecessary index entries, which are then removed by the filter.

While the default setting plan_cache_mode = 'auto' often works well, it relies on a heuristic: PostgreSQL executes the statement a few times with custom plans, then compares the estimated cost of a generic plan with the average estimated cost of the custom plans. The decision is based on estimates rather than actual execution times, which means it may occasionally select a generic plan even when a custom plan would perform better for a particular workload.

One alternative is to rewrite the predicate so that PostgreSQL can always generate an index condition.

reset plan_cache_mode;

prepare getorders_better(timestamptz, int) as
select * from orders
     where timestamp > coalesce($1, '-infinity'::timestamptz)
     order by "timestamp" asc limit $2
;

With the new query, there is always an Index Cond. The main difference is whether the scan range starts at -infinity or at the supplied parameter value:

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..174194.43 rows=5000000 width=19)
         Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..155802.94 rows=4471972 width=19)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
(3 rows)

postgres=# set plan_cache_mode to force_generic_plan;
SET

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

This revised query is effective because the Index Scan is optimal for pagination with order by "timestamp" asc limit $2, even when a null parameter is used. However, this isn't always the case, as a generic plan must estimate selectivity without knowing the actual parameter values. In some situations, this can lead to poor cardinality estimates and suboptimal plan choices.

I used prepared statements because they are the standard way to execute parameterized queries while avoiding SQL injection. However, you might face the same problem if you construct a custom query using a hardcoded value while keeping the generic predicate. For instance, you could include your parameters in a WITH clause's common table expression.

postgres=# explain
with param as ( select
 date'2026-01-01'::timestamptz as p1
)
select * from orders, param
     where p1::timestamptz is null or "timestamp" > p1
     order by "timestamp" asc limit 10
;

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.90 rows=10 width=27)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..210363.45 rows=4467609 width=27)
         Filter: ((('2026-01-01'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01'::date)::timestamp with time zone))
(3 rows)

Since ((('2026-01-01'::date)::timestamp with time zone IS NULL) is provably false, the OR expression could theoretically be simplified to a single predicate. However, PostgreSQL currently does not perform this simplification. The optimizer source code explicitly notes that OR branches proven to be always false could theoretically be removed, but this optimization is not currently implemented:

Currently, when processing OR expressions, we only return true when all of the OR branches are always false. This could perhaps be expanded to remove OR branches that are provably false. This may be a useful thing to do as it could result in the OR being left with a single arg. That's useful as it would allow the OR condition to be replaced with its single argument which may allow use of an index for faster filtering on the remaining condition.

This situation mirrors what happens when a prepared statement is executed using a generic plan. When using generic predicates, such as p1::timestamptz is null or "timestamp" > p1, which contain branches that might be false depending on parameter values, the planner cannot safely eliminate those branches to get a sargable condition. The same workaround applies: if you understand the specific parameter characteristics, write a more targeted query to enable index usage, maybe one per scenario.

For workloads where parameter values can lead to radically different optimal plans, relying on plan_cache_mode = auto may not always produce the desired result. In such cases, forcing custom plans with plan_cache_mode = force_custom_plan can be a simple way to guarantee that the optimizer considers the actual parameter values on every execution.

The final example shows that PostgreSQL's failure to convert OR expressions into indexable predicates extends beyond generic plans. Even when the value is known at planning time via a single-row CTE and not a parameter, PostgreSQL treats the OR expression as a filter instead of simplifying it. This indicates a broader optimizer limitation in OR-clause simplification that you should know and address when writing the SQL query.

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates.

SQL> create table franck ( val int unique );

Table created.

SQL> insert into franck values (-1) , (1) ;

2 rows created.

SQL> select val from franck;

       VAL
----------
        -1
         1

SQL> update franck set val=-val;

2 rows updated.

SQL> select val from franck;

       VAL
----------
         1
        -1

From a SQL perspective, this is expected behavior, but not all databases support it without raising an error:

• Db2, SQL Server, and Oracle handle it without error.
• PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint.
• MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'
• SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }
• MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

db.franck.createIndex({ val: 1 }, { unique: true });
db.franck.insertMany([ { val: -1 }, { val: 1 } ]);
db.franck.updateMany({},[ { $set: { val: {$multiply:[ "$val",-1 ]} } } ]);
MongoServerError: Plan executor error during update :: caused by :: E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect:

1. The initial index entries are: (-1): row #1 and (1): row #2
2. Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1
3. Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2
4. The final non-deleted index entries are: (-1): row #2 and (1): row #1

Even if the final state is valid, this execution would raise a duplicate key error at step 2 because two entries have the same value: (1): row #1 and (1): row #2. So Oracle does something smarter.

Let's look at the internals with another example: five rows with values from 1 to 5, and an update that increments them by one should create a temporary violation until the last row is updated.

Investigation with block dumps

My goal is to observe how a unique B-tree index evolves as Oracle executes a multi-row update that temporarily creates duplicate values.

The table contains five rows with values from 1 to 5, and a row-level trigger pauses for five seconds before each row update. This gives me enough time to dump the index leaf block and capture intermediate states in a second session.

I create the table:

drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 5');
create unique index FRANCK on FRANCK ( VAL );
create or replace trigger FRANCK_SLEEP 
 before update on FRANCK 
 for each row begin dbms_session.sleep(5); end;
/
show errors

Here are my table's rows in the order they are scanned:

SQL> column "DUMP(VAL,16)" format a18
SQL> column "DUMP(ROWID,16)" format a39
SQL> select val, dump(val,16), dump(rowid,16)
     from FRANCK 
;

       VAL DUMP(VAL,16)       DUMP(ROWID,16)
---------- ------------------ ---------------------------------------
         1 Typ=2 Len=2: c1,2  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,0
         2 Typ=2 Len=2: c1,3  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,1
         3 Typ=2 Len=2: c1,4  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,2
         4 Typ=2 Len=2: c1,5  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,3
         5 Typ=2 Len=2: c1,6  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,4

I've displayed the value in hexadecimal, as well as the ROWID because the index entries are key -> ROWID and I'll inspect them with a hexadecimal dump.

I locate the index block and my session's trace file:

alter session set tracefile_identifier = 'franck';

column fileno    new_value fileno
column block     new_value block
column tracefile new_value tracefile

select header_file as fileno ,
       header_block+1 as block
   from dba_segments
    where segment_type='INDEX'
      and segment_name='FRANCK'
      and owner=sys_context('USERENV', 'CURRENT_SCHEMA')
;

select value as tracefile
 from v$diag_info
 where name = 'Default Trace File'
;

Each time I want to dump the block and read the entries, I run the following in the session where the variables were defined:

alter system checkpoint;
alter system dump datafile &fileno block █
host tail -20 &tracefile | egrep "^row|^col"

I'll dump the block multiple times while another session runs update FRANCK set VAL = VAL + 1;, showing:

• the index entry (row# in the dump)
• with the 6-byte ROWID (data in the dump)
• and the key (col 0 in the dump).

Initial state

The index leaf block before the update contains one entry per table row:

row#0[8021] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

The six-byte data:(6) field is the ROWID stored in the unique index entry. The indexed value is stored in col 0 as it is a single-column index.

To help with the hexadecimal values, I've put them in a table to show the table rows and their index entries, with values in hexadecimal and decimal:

Running the update

I run the update in another session, and thanks to the trigger, it waits 5 seconds between each updated row:

update FRANCK set val = val + 1
;

The update starts the scan and processes the first table row, where the value 1 (c1 02) is incremented to 2 (c1 03).

Oracle first locates the index entry for the old value, acquires an exclusive lock on the row (the lock: 2 references ITL slot 2, whose transaction information identifies the modifying transaction), and marks it as deleted (-D- flag):

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

At this intermediate point, the old entry has been deleted, and no entry references the row containing the new value:

The next dump is interesting. Oracle does not add another entry for value 2 (c1 03). Instead, it updates the existing entry #1, which already has the right key c1 03 but for a different row. The old ROWID of entry #1 is replaced:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

There are now two index entries for rowid 69 00 00, one deleted and one current:

Notice that there is no longer any entry for rowid 69 00 01 because Oracle has changed the ROWID stored in the existing key for value 2 (c1 03) entry from 69 00 01 to 69 00 00. This is why there's no duplicate key situation: all keys in the index are unique.

If the update stops after modifying only the first row, it can create duplicate key issues because the first two rows share the same value. It can also cause logical corruption since the second row lacks an index entry. One possible approach is for Oracle to track these uniqueness conflicts as violation counts during statement processing and confirm that none persist at the end. This strategy would enable Oracle to proceed, expecting that further updates will restore consistency.

The update then continues with the next table row. Rowid 69 00 01 changes from 2 to 3.

I guess that Oracle attempts to locate the entry with key value 2 (c1 03) pointing to 69 00 01 for deletion. However, it finds the key but associated with a different ROWID. Since there's nothing to delete, it leaves it as-is but decreases the violation counter, which then returns to zero, because the current update removes the value that was a duplicate.

As we have seen before, Oracle updates the index entry for the new value c1 04 = 3 to point to rowid 69 00 01:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

Currently, there is an index entry for 69 00 01 with the value 3 (c1 04), but no entry exists for row 69 00 02, so continuing with my guess, the violation counter is incremented.

With this pattern, the ROWID associated with each key shifts one row earlier. The same pattern applies to keys 4 and 5. The violation counter may be decremented and then incremented again, remaining at 1.

After processing the row containing the value 4 (c1 05), the key with value 5 (c1 06) points to rowid 69 00 03. The violation counter remains at 1.

Once the row with value 5 (c1 06) is processed, Oracle encounters a situation where no existing key is reused. According to my guess, the violation counter decreases because the previous value no longer matches the current row. The new key value is 6 (c1 07), which did not previously exist. Only then is a new index entry created:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 06

row#5[7966] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 07

The final state is:

All rows and index entries are consistent, and the violation counter is back to zero. The index structure I examined never had duplicate keys in the block dumps. Oracle maintains key uniqueness by reusing entries and updating their stored ROWIDs. The method Oracle uses to monitor unresolved uniqueness conflicts until the statement finishes isn't clear from these dumps, but a violation counter appears to be a possible implementation.

UNIQUE vs non-UNIQUE index

Without grasping these internals, one might assume a UNIQUE index isn't necessary for a UNIQUE constraint. However, now we see the performance benefit of this update pattern: it modifies existing index entries rather than deleting the old value and inserting the new one. I tested this with one million rows:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create unique index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;

The index statistics display branch and leaf blocks along with entries. Following the update statement that affected all rows, the unique index only added a single deleted leaf entry (DEL_LF_ROWS):

    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       2220    1000001           1          5       2221

I tested the same process using a non-unique index:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create index FRANCK on FRANCK ( VAL );
update FRANCK set val 
                                        by Franck Pachot