So it goes: your system is purring like a tiger, devouring requests, until, without warning, it slumps into existential dread. Not a crash. Not a bang. A quiet, self-sustaining collapse. The system doesn’t stop. It just refuses to get better. Metastable failure is what happens when the feedback loops in the system go feral. Retries pile up, queues overflow, recovery stalls. Everything runs but nothing improves. The system is busy and useless.

In an earlier post, I reviewed the excellent OSDI ’22 paper on metastable failures, which dissected real-world incidents and laid the theoretical groundwork. If you haven’t read that one, start there.

This HotOS ’25 paper picks up the thread. It introduces tooling and a simulation framework to help engineers identify potential metastable failure modes before disaster strikes. It’s early stage work. A short paper. But a promising start. Let’s walk through it.

Introduction

Like most great tragedies, metastable failure doesn't begin with villainy; it begins with good intentions. Systems are built to be resilient: retries, queues, timeouts, backoffs. An immune system for failure, so to speak. But occasionally that immune system misfires and attacks the system itself. Retries amplify load. Timeouts cascade. Error handling makes more errors. Feedback loops go feral and you get an Ouroboros, a snake that eats its tail in an eternal cycle. The system gets stuck in degraded mode, and refuses to get better.

This paper takes on the problem of identifying where systems are vulnerable to such failures. It proposes a modeling and simulation framework to give operators a macroscopic view: where metastability can strike, and how to steer clear of it.

Overview

The paper proposes a modeling pipeline that spans levels of abstraction: from queueing theory models (CTMC), to simulations (DES), to emulations, and finally, to stress tests on real systems. The further down the stack you go, the more accurate and more expensive the analysis becomes.

The key idea is a chain of simulations: each stage refines the previous one. Abstract models suggest where trouble might be, and concrete experiments confirm or calibrate. The pipeline is bidirectional: data from low-level runs improves high-level models, and high-level predictions guide where to focus concrete testing.

The modeling is done using a Python-based DSL. It captures common abstractions: thread pools, queues, retries, service times. Crucially, the authors claim that only a small number of such components are needed to capture the essential dynamics of many production services. Business logic is abstracted away as service-time distributions.

Figure 2 shows a simple running example used throughout the paper: a single-threaded server handling API requests at 10 RPS, serving a client that sends requests at 5 RPS, with a 5s timeout and five retries. The queue bound is 150. The goal is to understand when this setup tips into metastability and how to tune parameters to avoid that.

Continuous-Time Markov Chains (CTMC)

CTMC provides an abstract average-case view of a system, eliding the operational details of the constructs. Figure 3 shows a probabilistic heatmap of queue length vs. retry count (called orbit). Arrows show the most likely next state; lighter color means higher probability. You can clearly see a tipping point: once the queue exceeds 40 and retries hit 30, the system is likely to spiral into a self-sustaining feedback loop. Below that threshold, it trends toward recovery. This model doesn't capture fine-grained behaviors like retry timers, but it's useful for quickly flagging dangerous regions of the state space.

Simulation (DES)

Discrete event simulation (DES) hits a sweet spot between abstract math and real-world mess. It validates CTMC predictions but also opens up the system for inspection. You can trace individual requests, capture any metric, and watch metastability unfold. The paper claims that operators often get their "aha" moment here, seeing exactly how retries and queues spiral out of control.

Emulation

Figure 4 shows the emulator results. This stage runs a stripped-down version of the service on production infrastructure. This is not the real system, but its lazy cousin. It doesn't do real work (it just sleeps on request) but it behaves like the real thing under stress. The emulator confirms that the CTMC and DES models are on track: the fake server fails in the same way as the real one.

Testing

The final stage is real stress tests on real servers. It's slow, expensive, and mostly useless unless you already know where to look. And that's the point of the whole pipeline: make testing less dumb. Feed it model predictions, aim it precisely, and maybe catch the metastable failure before it catches you.

Discussion

There may be a connection between metastability and self-stabilization. If we think in terms of a global variant function (say, system stress) then metastability is when that function stops decreasing and the system slips into an attractor basin from which recovery is unlikely. Real-world randomness might kick the system out. But sometimes it is already stuck so bad that it doesn't. Probabilistic self-stabilization once explored this terrain, and it may still have lessons here.

The paper nods at composability, but doesn't deliver. In practice, feedback loops cross the component boundaries. Metastability often emerges from these interdependencies. Component-wise analysis may miss the whole. As we know from self-stabilization: composition is hard. It works by layering or superposition, not naive composition.

The running example in the paper is useful but tiny. The authors claim this generalizes, but don't show how. For a real system, like Kafka or Spanner, how many components do you need to simulate? What metrics matter? What fidelity is enough? This feels like a "marking territory” paper that maps a problem space.

There's also a Black Swan angle here. Like Taleb's rare, high-impact events, metastable failures are hard to predict, easy to explain in hindsight, and often ignored until too late. Like Black Swan detection, I think metastability is less about prediction and more about preparation: structuring our systems and minds to notice fragility before it breaks. The paper stops at identifying potential metastability risks, and recovery is not considered. Load shedding would work, but we need some theoretical and analytical guidance, otherwise it is too easy to do harm instead of recovery via load shedding. Which actions would help nudge the system out of the metastable basin? In what order, as not to cause further harmful feedback loops? What runtime signals suggest you're close?

This has results for a CPU-bound Insert Benchmark with Postgres on a large server. A blog post about a similar workload on a small server is here.

This report was delayed because I had to debug a performance regression (see below) and then repeat tests after implementing a workaround in my benchmark client.

tl;dr - 

PostgreSQL limits tuple sizes to a quarter of the block size, generally capping at 2KB. In document data modeling, where documents represent business transactions, sizes often exceed this limit. Storing entire transaction documents as a single JSONB can lead to compression and splitting via TOAST (The Oversized-Attribute Storage Technique). While this is suitable for static documents that are infrequently accessed, it is less optimal for queries on documents. Let's take an example to detect this antipattern of using PostgreSQL as a document database.

I create a user profile table similar to the previous post, but adding a bio field with large text:

create table users (
  id bigserial primary key,
  data jsonb not null
);

INSERT INTO users (data)
SELECT
  jsonb_build_object(
    'name', 'u' || n::text,
    'bio', (SELECT string_agg(chr(trunc(random() * (126 - 33) + 33)::int), '')   FROM generate_series(1, 5000)),
    'email', jsonb_build_array(
      'u' || n::text || '@compuserve.com'
    ),
    'login', jsonb_build_object(
      'last', to_char(current_timestamp, 'YYYY-MM-DD'),
      'count', 1
    )
  )
FROM generate_series(1, 100000) n
;
vacuum analyze users
;

I check the size of the table, and also the TOAST overflow:

with users_cte as (
  select * from pg_class
  where oid = 'users'::regclass
)
select oid, relname, relpages, reltuples, reltoastrelid
 from users_cte
union all
select oid, relname, relpages, reltuples, reltoastrelid
 from pg_class
where oid = (select reltoastrelid from users_cte)
;

  oid  |    relname     | relpages | reltuples | reltoastrelid 
-------+----------------+----------+-----------+---------------
 32314 | users          |      736 |    100000 |         32318
 32318 | pg_toast_32314 |    71430 |    300000 |             0

The table contains 100000 rows across 736 pages, with three chunks per row stored externally in the TOAST table. This results in a total of 736 + 71430 = 72166 pages. Each chunk is approximately (71430 * 8192) / 300000 = 1951 bytes, ensuring that tuples remain under 2KB.

I use EXPLAIN ANALYZE to query all documents, which shows the number of pages accessed. To retrieve the JSON document, I apply SERIALIZE, as EXPLAIN does not fetch it by default:

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, data
from users
;

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on public.users (actual time=0.040..10.973 rows=100000 loops=1)
   Output: id, data
   Buffers: shared read=736
 Planning Time: 0.038 ms
 Serialization: time=2482.359 ms  output=509831kB  format=text
   Buffers: shared hit=384887 read=72253
 Execution Time: 2504.164 ms

The table scan read 736 pages from the base table (shared read=736), while reading the JSONB content accessed 72253 pages from the TOAST (shared read=72253). Each TOAST page was read an average of 384887 / 72253 = 5 times, fortunately staying in the shared buffer cache (shared hit=384887) but accessing to shared buffers costs CPU and lightweight locks.

We observe a fivefold read amplification when querying the JSONB column, as it requires de-toasting.

In a document database, you can retrieve the entire document or access specific fields for filtering and sorting. For example, I include a projection of the "username" field.

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, data
, (data->>'username')
from users
;

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on public.users (actual time=0.085..532.367 rows=100000 loops=1)
   Output: id, data, (data ->> 'username'::text)
   Buffers: shared hit=384887 read=72989
 Planning Time: 0.039 ms
 Serialization: time=2276.025 ms  output=510222kB  format=text
   Buffers: shared hit=457140
 Execution Time: 2819.235 ms

PostgreSQL lacks optimization for accessing JSONB, which may lead to multiple de-toasting. As indicated in the Output of the EXPLAIN VERBOSE, the scan has two projections in addition to the whole document, and access many times to the TOAST pages as indicated by shared hit=384887 read=72989. Retrieving the whole document causes an additional shared hit=457140.

I continue by projecting one more field, "login.last":

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, data
, (data->'login'->>'last')
, (data->>'username')
from users
;

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on public.users (actual time=0.079..855.246 rows=100000 loops=1)
   Output: id, data, ((data -> 'login'::text) ->> 'last'::text), (data ->> 'username'::text)
   Buffers: shared hit=842027 read=72989
 Planning Time: 0.040 ms
 Serialization: time=2261.679 ms  output=511589kB  format=text
   Buffers: shared hit=457140
 Execution Time: 3128.017 ms

Even when both projections occur in the same scan, the JSONB document is de-toasted twice: once for each field, resulting in shared hit=842027 read=72989.

To avoid retrieving the entire document, I project only the fields I need. I also run the query with an additional field, "login.count", but exclude "data" from the SELECT statement:

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id
, (data->'login'->>'count')::int
, (data->'login'->>'last')
, (data->>'username')
from users
;

                                                                                                                                       QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.users (actual time=0.087..1149.260 rows=100000 loops=1)
   Output: id, (((data -> 'login'::text) ->> 'count'::text))::integer, ((data -> 'login'::text) ->> 'last'::text), (data ->> 'username'::text)
   Buffers: shared hit=1299167 read=72989
 Planning Time: 0.042 ms
 Serialization: time=21.837 ms  o

I saved the reads for serializing the result, but the scan indicated shared hit=1299167 read=72989. It accessed only the necessary data from disk: 72989 pages from the base and TOAST tables. However, to read only three fields, it accessed the buffers 1299167 / 72989 = 18 times, leading to excessive CPU usage and potential lightweight lock contention during concurrent access.

In PostgreSQL, JSONB is a datatype for a single column, to store and get the entire document. In contrast, a document database like MongoDB reads and writes individual fields, keeping the document in memory for quick access, like an object cache. Its WiredTiger storage engine decompresses the on-disk representation when loading to memory and compresses it during cache eviction or checkpoint.

It is not recommended to use PostgreSQL as a document database. Instead, fields requiring individual access should be normalized into SQL columns rather than being embedded in a JSONB document. To identify suboptimal designs, examine the shared buffer hits using EXPLAIN ANALYZE with SERIALIZATION. If you choose a document model, then use a document database.

With Chapter 6, the Concurrency Control and Recovery in Database Systems book shifts focus from concurrency control to the recovery! This chapter addresses how to preserve the atomicity and durability of transactions in the presence of failures, and how to restore the system to a consistent state afterward.

The book offers a remarkably complete foundation for transactional recovery, covering undo/redo logging, checkpointing, and crash recovery. While it doesn't use the phrase "write-ahead logging", the basic concepts are there, including log-before-data and dual-pass recovery. When the book was written, the full WAL abstraction in ARIES was still to come in another five years at 1992 (see my review here). I revisit this discussion/comparison at the end of the post.

System Model and Architecture

In earlier chapters, we had reviewed the architecture: transactions pass through a transaction manager (TM), which sends operations to a scheduler and then to a data manager (DM). For recovery, the DM is split into:

• A Cache Manager (CM), which handles reading/writing between memory and disk, with a volatile cache and a stable storage backend.
• A Recovery Manager (RM), which interprets high-level operations (Read, Write, Commit, Abort, Restart) and ensures durability and atomicity. What is Restart? Well, we will discuss it in the recovery algorithm section. 

The model distinguishes between volatile storage (lost on crash) and stable storage (assumed to survive crashes). Cache slots have a "dirty" bit to track whether their content has diverged from stable storage. The focus of the chapter is primarily on system failures that involve a crash or reboot that wipes out volatile memory but leaves the disk intact.

Stable Storage and Shadowing

For maintaining data in stable storage, in-place updating means overwrites destroy prior values. The shadowing idea allows fast copy as new versions are written to different locations, and a directory atomically switches the system view to the new state.

Shadowing avoids UNDO, but complicates consistency. In-place updating is simpler and more compatible with buffering, but requires careful logging. I had seen a shadowing solution being developed in S3 for a "fast copy" feature, and I was amazed how much complexity it introduces for correctness of deleting/garbage collection. It is surprisingly tricky.

Logging and Recovery

This section lays the groundwork for WAL in modern systems. The log contains entries of the form [T_i, x, v], which record that transaction T_i wrote value v to data item x.

There are two key rules to ensure safe recover.

• Undo Rule: Before a transaction overwrites a committed value in the database, the old value must be saved.
• Redo Rule: A transaction's writes must be logged before the transaction is considered committed.

These collectively ensure that the system can UNDO the effects of uncommitted transactions and REDO the effects of committed ones. 

Recovery Algorithm Types

The chapter categorizes recovery algorithms by their needs:

• Undo/Redo: This can handle both missing and uncommitted updates. This is the most flexible category as it allows steal/no-force as in ARIES. The chapter focuses on Undo/Redo algorithms as the most general and efficient in runtime operation.
• Undo/No-Redo: This requires forcing all updates to disk before commit, eliminating redo. This is steal/force.
• No-Undo/Redo: This is no-steal and no-force. So it disallows overwriting before commit, eliminating undo.
• No-Undo/No-Redo: This is the least flexible one: no-steal, force. It requires full data persistence at commit.

Remember the restart operation, which we said that the RM is responsible for. A Restart is invoked after a crash to restore the database to the last consistent committed state. This involves:

• Scanning the log backward to undo changes from uncommitted transactions.
• Scanning forward to redo changes from committed ones.

The section on restart discusses how this process is idempotent, meaning it can be safely retried if another crash interrupts it.

Checkpointing

Without optimization, Restart must scan the entire log. Checkpointing minimizes recovery time by flushing current database state to disk and marking that point in the log. Checkpointing approaches trades off between runtime efficiency and recovery time. Types include:

• Commit-consistent: Waits for all transactions to finish, then flushes all dirty pages.
• Cache-consistent: Flushes all dirty cache slots but doesn't wait for transactions to finish.
• Fuzzy checkpointing: Flushes only old dirty pages, reducing disruption.

Implementation of Undo/Redo with optimizations

The chapter discusses several performance optimizations for implementing Undo/Redo algorithm.

• Partial data item logging: Log only modified bytes/fields within a page.
• Buffering the log: To reduce I/O, log entries are staged in memory and flushed in batches.
• Log Sequence Numbers (LSNs): Pages and log entries are tagged with sequence numbers to track which updates have been applied.
• Delayed (group) commit: Allows batching commits to improve efficiency.

These optimization suggestions resemble those that ARIES (1992) paper formalized and implemented efficiently and correctly. More on this below.

Logical Logging

Instead of logging low-level data changes, the system may log high-level operations like "insert record" or "update field". Logical logging is more compact but harder to undo or redo, since it may depend on the current state. 

Logical logging contrasts with ARIES-style logging, which uses physiological logging: a hybrid of logical and physical approaches. While logical logging records abstract operations without referencing specific data layouts, ARIES logs changes to specific pages along with logical intent (e.g., "insert at offset X in page P") and includes both before- and after-images to enable precise undo and redo. ARIES also relies on Log Sequence Numbers (LSNs) embedded in pages and Compensation Log Records (CLRs) to make recovery idempotent and efficient. This makes ARIES more robust under the steal/no-force buffer policy, whereas logical logging is lighter but more fragile.

ARIES critiques earlier approaches, like the one in this book, for performing undo before redo. It shows that this ordering becomes incorrect when combined with optimizations like LSNs and selective redo. In such hybrids, undo can write CLRs that raise page LSNs and cause the redo phase to mistakenly skip necessary committed updates.

ARIES reverses the order. It does redo first, and reapplies all committed changes that might be missing. Then it does the undo and erases changes from uncommitted transactions. This avoids the problem above. The redo is based on comparing page LSNs to log record LSNs. Since undo hasn't happened yet, page LSNs still reflect what's truly on disk. With this approach, you only advance page LSNs after you know the change is present. Tricky stuff.

PostgreSQL's Multi-Version Concurrency Control (MVCC) works around the challenge of in-place updates in fixed block storage by avoiding it. Instead of updating rows, it processes them as deletes and inserts, prioritizing simplicity of implementation over performance. Updating fields in a JSONB document can be problematic due to significant write amplification.

What are Heap Only Tuple (HOT) updates?

When a table row is updated, the entire row is marked for deletion by setting its xmax value, indicating the end of its visibility period. A new version of the row is then created with a fresh xmin value to signify the start of its visibility. Write amplification arises not only from copying the entire row but also from the need to update all indexes associated with the table. PostgreSQL indexes reference rows using their physical location (ctid), meaning that any change in the row's physical location requires new index entries to find the latest version of the row, even if the indexed column values remain unchanged. Over time, when older versions of rows are no longer visible to any active transaction—having passed the xmin horizon—they are eligible for garbage collection by the vacuum process, which removes outdated row versions and their associated index entries.

Given that many SQL applications have multiple indexes on their tables, frequent updates can exacerbate write amplification, with detrimental consequences for checkpoints and replication, especially when every index must be updated regardless of whether the indexed values changed. To mitigate this, PostgreSQL introduces an optimization called Heap-Only Tuple (HOT) updates that avoid adding new index entries for keys that didn't change, in cases where the new version of the row fits in the same block as the previous version. If a column is frequently updated and the old version is frequently vacuumed, some free space may be constantly available in the block for new versions (and this can be initialized with a lower fillfactor) and HOT optimization can kick-in.

This blog post series is about using PostgreSQL as a document database, with all data in JSONB, but there's no Heap-Only Tuple optimization for indexes on JSONB fields.

Test it with EXPLAIN (ANALYZE, WAL, BUFFERS)

I create a table similar to the one in the previous post, storing user profiles, and add a login sub-object to record the last login date and a login counter:

create table users (
  id bigserial primary key,
  data jsonb not null
);
insert into users (data) values (
 jsonb_build_object(
    'name', 'Homer Simpson',
    '{login}',
    jsonb_build_object(
      'last', to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'),
      'count', 0
    )  ,
    'email', jsonb_build_array(
      'donutlover@springfieldusa.com',
      'homerdoh@simpsons.com',
      'lazy.sofa.guy@tvcharacters.net'
    )
  )
 );

This is the PostgreSQL equivalent of the following MongoDB call to insert a document:

db.users.insertOne({  
  data: {  
    name: "Homer Simpson",  
    login: {  
      last: new Date(),  
      count: 0  
    },  
    email: [  
      "donutlover@springfieldusa.com",  
      "homerdoh@simpsons.com",  
      "lazy.sofa.guy@tvcharacters.net"  
    ]  
  }  
});  

My use-case is the equivalent of the following to increase the login counter and update the last login date:

db.users.updateOne(  
  { _id: 1 },  
  {  
    $set: { "data.login.last": new Date() },  
    $inc: { "data.login.count": 1 }  
  }  
);  

In SQL, there's no increment operation. Instead, an update sets the new values. When stored as a JSONB field in PostgreSQL, we must replace the document with a new one using json_set() to modify the fields.

I run some updates to increase the login counter and update the last login date and show the execution plan with statistics:

explain (analyze, verbose, buffers, wal, serialize text, costs off)
UPDATE users
SET data = jsonb_set(
  data,
  '{login}',
  jsonb_build_object(
    'last', to_char(current_timestamp, 'YYYY-MM-DD'),
    'count', (COALESCE((data->'login'->>'count')::int, 0) + 1)
  )
)
where id=1
\watch

Here is the execution plan showing two buffer hits to find the row via index, and one Write-Ahead Logging (WAL) record for the update of the row (71 bytes)

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.057..0.057 rows=0 loops=1)
   Buffers: shared hit=4
   WAL: records=1 bytes=71
   ->  Index Scan using users_pkey on public.users (actual time=0.040..0.041 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=2
 Planning Time: 0.063 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.077 ms

You can run that for a while and on a large table, and observe the same. Even if it writes more than necessary, because the whole row and JSON documents is re-written, the performance is predictable.

You may observe some executions with one more WAL record generated by the Index Scan as reads may do some delayed cleanup.

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.062..0.063 rows=0 loops=1)
   Buffers: shared hit=4
   WAL: records=2 bytes=157
   ->  Index Scan using users_pkey on public.users (actual time=0.047..0.048 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=2
         WAL: records=1 bytes=86
 Planning Time: 0.063 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.083 ms

While storing all data in JSONB, similar to a document database, may seem appealing, this table lacks indexes. In a real-world application, documents will contain more fields and sub-documents and require multiple indexes, which are likely to evolve as the application develops.

Adding indexes

During the lifecycle of an application, more indexes are created. I add an index on the user name:

create index on users(
 (data->>'name')
);

In PostgreSQL, adding an index to fields that are not updated does impact updates differently than in many other databases. For instance, my login update produces two additional WAL records, resulting in a total WAL size that is three times larger, along with increased buffer reads.

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.091..0.092 rows=0 loops=1)
   Buffers: shared hit=9
   WAL: records=3 bytes=207
   ->  Index Scan using users_pkey on public.users (actual time=0.059..0.060 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=3
 Planning Time: 0.068 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.113 ms

PostgreSQL requires an expression index to index JSON fields. We have seen one limitation of expression indexes in a previous post (No Index Only Scan on JSONB Fields) and here is another one: PostgreSQL doesn't detect when the indexed value has not changed. This prevents it from applying HOT optimization, even if the new row fits within the same block.

This was with an expression index on a scalar value (with no array in the JSON path) but there's the same problem with GIN indexes. I create the same index as in the previous post (No Index for LIKE on JSONB):

CREATE INDEX idx_users_data_email ON users USING GIN (
 (data->'email') jsonb_path_ops
);  

My update that does't touch this field shows one more WAL record, larger WAL size and more buffer reads:

                                                                                                           QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.080..0.080 rows=0 loops=1)
   Buffers: shared hit=11
   WAL: records=4 bytes=397
   ->  Index Scan using users_pkey on public.users (actual time=0.039..0.041 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=3
 Planning Time: 0.070 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.100 ms

The issue at hand is that you might prefer document data modeling over relational data modeling due to its simplicity in matching your domain access patterns. You may have come across some "Just use PostgreSQL" advocacy and claims that JSONB can transform PostgreSQL into a document database, and started such design with all data in a JSONB field. Your initial performance metrics might be met but, as your application grows and more indexes are added, critical use cases may struggle to scale.

I emphasized the importance of WAL records and size, as they are significant bottlenecks in PostgreSQL's scalability due to single-threaded WAL replication. Additionally, write amplification leads to other complications, including increased checkpoint work and higher pressure on vacuum. Scaling up with more CPUs won't resolve the issue, and adding read replicas won't help either since all indexes need to be created on the primary database.

PostgreSQL is a relational database that incorporates JSONB for added flexibility, but it doesn't convert it into a document database. In an SQL RDBMS, frequently updated or indexed fields should be in their own columns, maybe their own tables, while JSON can be used for additional flexible data accessed as a whole. If a document model is preferred, consider using a document database like MongoDB, which performs in-place updates to documents in memory and updates only the relevant indexes (FAQ: Indexes) and is not limited by fixed block size storage (documents are stored in a B-Tree with variable leaf size, and secondary indexes reference them with the key in this B-Tree).

PostgreSQL's Multi-Version Concurrency Control (MVCC) works around the challenge of in-place updates in fixed block storage by avoiding it. Instead of updating rows, it processes them as deletes and inserts, prioritizing simplicity of implementation over performance. Updating fields in a JSONB document can be problematic due to significant write amplification.

What are Heap Only Tuple (HOT) updates?

When a table row is updated, the entire row is marked for deletion by setting its xmax value, indicating the end of its visibility period. A new version of the row is then created with a fresh xmin value to signify the start of its visibility. Write amplification arises not only from copying the entire row but also from the need to update all indexes associated with the table. PostgreSQL indexes reference rows using their physical location (ctid), meaning that any change in the row's physical location requires new index entries to find the latest version of the row, even if the indexed column values remain unchanged. Over time, when older versions of rows are no longer visible to any active transaction—having passed the xmin horizon—they are eligible for garbage collection by the vacuum process, which removes outdated row versions and their associated index entries.

Given that many SQL applications have multiple indexes on their tables, frequent updates can exacerbate write amplification, with detrimental consequences for checkpoints and replication, especially when every index must be updated regardless of whether the indexed values changed. To mitigate this, PostgreSQL introduces an optimization called Heap-Only Tuple (HOT) updates that avoid adding new index entries for keys that didn't change, in cases where the new version of the row fits in the same block as the previous version. If a column is frequently updated and the old version is frequently vacuumed, some free space may be constantly available in the block for new versions (and this can be initialized with a lower fillfactor) and HOT optimization can kick-in.

This blog post series is about using PostgreSQL as a document database, with all data in JSONB, but there's no Heap-Only Tuple optimization for indexes on JSONB fields.

Test it with EXPLAIN (ANALYZE, WAL, BUFFERS)

I create a table similar to the one in the previous post, storing user profiles, and add a login sub-object to record the last login date and a login counter:

create table users (
  id bigserial primary key,
  data jsonb not null
);
insert into users (data) values (
 jsonb_build_object(
    'name', 'Homer Simpson',
    '{login}',
    jsonb_build_object(
      'last', to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS'),
      'count', 0
    )  ,
    'email', jsonb_build_array(
      'donutlover@springfieldusa.com',
      'homerdoh@simpsons.com',
      'lazy.sofa.guy@tvcharacters.net'
    )
  )
 );

This is the PostgreSQL equivalent of the following MongoDB call to insert a document:

// MongoDB equivalent query
db.users.insertOne({  
    "_id": 1,
    name: "Homer Simpson",  
    login: {  
      last: new Date(),  
      count: 0  
    },  
    email: [  
      "donutlover@springfieldusa.com",  
      "homerdoh@simpsons.com",  
      "lazy.sofa.guy@tvcharacters.net"  
    ]  
});  

My use-case is the equivalent of the following to increase the login counter and update the last login date:

// MongoDB equivalent query
db.users.updateOne(  
  { _id: 1 },  
  {  
    $set: { "login.last": new Date() },  
    $inc: { "login.count": 1 }  
  }  
);  

In SQL, there's no increment operation. Instead, an update sets the new values. When stored as a JSONB field in PostgreSQL, we must replace the document with a new one using json_set() to modify the fields.

I run some updates to increase the login counter and update the last login date and show the execution plan with statistics:

explain (analyze, verbose, buffers, wal, serialize text, costs off)
UPDATE users
SET data = jsonb_set(
  data,
  '{login}',
  jsonb_build_object(
    'last', to_char(current_timestamp, 'YYYY-MM-DD'),
    'count', (COALESCE((data->'login'->>'count')::int, 0) + 1)
  )
)
where id=1
\watch

Here is the execution plan showing two buffer hits to find the row via index, and one Write-Ahead Logging (WAL) record for the update of the row (71 bytes)

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.057..0.057 rows=0 loops=1)
   Buffers: shared hit=4
   WAL: records=1 bytes=71
   ->  Index Scan using users_pkey on public.users (actual time=0.040..0.041 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=2
 Planning Time: 0.063 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.077 ms

You can run that for a while and on a large table, and observe the same. Even if it writes more than necessary, because the whole row and JSON documents is re-written, the performance is predictable.

Note that you may observe some executions with one more WAL record generated by the Index Scan as reads may do some delayed cleanup:

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.062..0.063 rows=0 loops=1)
   Buffers: shared hit=4
   WAL: records=2 bytes=157
   ->  Index Scan using users_pkey on public.users (actual time=0.047..0.048 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=2
         WAL: records=1 bytes=86
 Planning Time: 0.063 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.083 ms

While storing all data in JSONB, similar to a document database, may seem appealing, this table lacks indexes. In a real-world application, documents will contain more fields and sub-documents and require multiple indexes, which are likely to evolve as the application develops.

Adding indexes

During the lifecycle of an application, more indexes are created. I add an index on the user name:

create index on users(
 (data->>'name')
);

In PostgreSQL, adding an index to fields that are not updated does impact updates differently than in many other databases. For instance, my login update produces two additional WAL records, resulting in a total WAL size that is three times larger, along with increased buffer reads.

QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.091..0.092 rows=0 loops=1)
   Buffers: shared hit=9
   WAL: records=3 bytes=207
   ->  Index Scan using users_pkey on public.users (actual time=0.059..0.060 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=3
 Planning Time: 0.068 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.113 ms

PostgreSQL requires an expression index to index JSON fields. We have seen one limitation of expression indexes in a previous post (No Index Only Scan on JSONB Fields) and here is another one: PostgreSQL doesn't detect when the indexed value has not changed. This prevents it from applying HOT optimization, even if the new row fits within the same block.

This was with an expression index on a scalar value (with no array in the JSON path) but there's the same problem with GIN indexes. I create the same index as in the previous post (No Index for LIKE on JSONB):

CREATE INDEX idx_users_data_email ON users USING GIN (
 (data->'email') jsonb_path_ops
);  

My update that does't touch this field shows one more WAL record, larger WAL size and more buffer reads:

                                                                                                           QUERY PLAN                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on public.users (actual time=0.080..0.080 rows=0 loops=1)
   Buffers: shared hit=11
   WAL: records=4 bytes=397
   ->  Index Scan using users_pkey on public.users (actual time=0.039..0.041 rows=1 loops=1)
         Output: jsonb_set(data, '{login}'::text[], jsonb_build_object('last', to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD'::text), 'count', (COALESCE((((data -> 'login'::text) ->> 'count'::text))::integer, 0) + 1)), true), ctid
         Index Cond: (users.id = 1)
         Buffers: shared hit=3
 Planning Time: 0.070 ms
 Serialization: time=0.000 ms  output=0kB  format=text
 Execution Time: 0.100 ms

The issue at hand is that you might prefer document data modeling over relational data modeling due to its simplicity in matching your domain access patterns, and may have come across some "Just use PostgreSQL" advocacy that claims that JSONB can transform PostgreSQL into a document database. You started such design with all data in a JSONB field, and your initial performance metrics might be met but, as your application grows and more indexes are added, critical use cases may struggle to scale.

I emphasized the importance of WAL records and size, as they are significant bottlenecks in PostgreSQL's scalability due to single-threaded WAL replication. Additionally, write amplification leads to other complications, including increased checkpoint work and higher pressure on vacuum. Scaling up with more CPUs won't resolve the issue, and adding read replicas won't help either since all indexes need to be created on the primary database.

PostgreSQL is a relational database that incorporates JSONB for added flexibility, but it doesn't convert it into a document database. In an SQL RDBMS, frequently updated or indexed fields should be in their own columns, maybe their own tables, while JSON can be used for additional flexible data accessed as a whole. If a document model is preferred, consider using a document database like MongoDB, which performs in-place updates to documents in memory and updates only the relevant indexes (FAQ: Indexes) and is not limited by fixed block size storage (documents are stored in a B-Tree with variable leaf size, and secondary indexes reference them with the key in this B-Tree).

I’ve been involved in content moderation since roughly 2004. I’ve built spam prevention for corporate and personal e-mail, moderated open-source mailing lists and IRC channels, worked at a couple social media networks, and help moderate a Mastodon instance for a few hundred people. In the last few years I’ve wasted more time fighting blog comment spam, and I’m pretty sure Large Language Models (LLMs) are to blame.

I think of spam as a space with multiple equilibria. Producing spam takes work. Spammers are willing to invest that work because each message has a small chance to make money, or achieve political or emotional goals. Some spam, like the endless identical Viagra scams in my email spam folder, or the PHPBB comment spam I filter out here on aphyr.com, is cheap to generate and easy to catch. I assume the spammers make it up in volume. Other spam, like spear phishing attacks, is highly time-consuming: the spammer must identify a target, carefully craft a plausible message using, say, the identity of the target’s co-workers, or construct a facade of a bank’s log-in page, and so on. This kind of spam is more likely to make it through filters, but because it takes a lot of human work, is generally only worth it for high-value targets.

LLMs seem to be changing these equilibria. Over the last year I’ve seen a new class of comment spam, using what I’m fairly sure is LLM-generated text. These comments make specific, plausible remarks about the contents of posts and images, and work in a link to some web site or mention a product. Take this one I caught a few months back:

"Walking down a sidewalk lined with vibrant flowers is one of life’s simple joys! It reminds me of playing the [link redacted] slope game, where you have to navigate through colorful landscapes while dodging obstacles.

Before 2023, you’d likely have paid a human a few cents to write and post that. Now, thanks to LLMs, this sort of thing is trivially automated. The model will happily fabricate relatable personal experiences in service of a spam campaign:

That photo reminds me of the first time I tried macro photography in my backyard. I spent an hour trying to get a clear shot of a red flower, experimenting with angles and lighting. It was so much fun discovering the little details up close! If you ever need a break from photography, I recommend playing Snow Rider 3D for a bit of quick, light-hearted fun.

Other spam seems to glue together LLM remarks with what I think is a hand-written snippet of ad copy. Note the abrupt shift in grammar, diction, and specificity.

This piece masterfully blends technical depth with mythological storytelling, transforming a standard Haskell programming interview into an epic narrative. It cleverly critiques the complexity and absurdity of some technical interviews by illustrating how type-level Haskell can be pushed to esoteric extremes beautiful, powerful, and largely impractical. A fascinating and relevant read for anyone interested in the intersection of programming, language design, and narrative. I’m James Maicle, working at Cryptoairhub where we run a clear and insightful crypto blog. I’ll be bookmarking your site and following the updates. Glad to see so much valuable information shared here looking forward to exploring more strategies together. Thanks for sharing. If you interest about Crypto please visit my website and read my article [link redacted] Crypto Blog.

Of course this is not news. Product reviews are inundated with LLM slop, as are social media networks. LLMs allow for cheap, fast, and automated generation of unique spam which is difficult for machines and humans to identify. The cost falls on me and other moderators, who must sift through LLM bullshit trying to sieve “awkward but sincere human” from “automated attack”. Thanks to OpenAI et al I read more spam, and each message takes longer to check.

This problem is only going to get worse as LLMs improve and spammers develop more sophisticated ways to use them. In recent weeks I’ve received vague voice messages from strangers with uncanny speech patterns just asking to catch up—a sentence which, had I uttered it prior to 2023, would have been reasonably interpreted as a sign of psychosis. I assume these too are LLM-generated scams, perhaps a pig butchering scheme. So far these are from strangers, but it’s not hard to imagine an attacker using text and voice synthesis to impersonate a friend, colleague, or lover in a detailed conversation. Or one’s doctor, or bank.

As the cost of generating slop decreases, it’s easy to imagine LLMs generating personae, correspondence, even months-long relationships with real humans before being deployed for commercial or political purposes. Creating plausible accounts and selling them has been a successful business model in social media for some time; likewise, we have strong clues that LLMs are already used for social media bots. Social networks have responded to these attacks via out-of-band mechanisms: IP reputation analysis, javascript and mobile app fingerprinting, statistical correlation across multiple accounts, and so on. I’m not sure how to translate these defenses to less centralized and more privacy-oriented networks, like email or blog spam. I strongly suspect the only reason Mastodon hasn’t been eaten alive by LLM spambots is because we’re just not big enough to be lucrative. But those economics are shifting, and even obscure ecological niches can be worth filling.

As a moderator, that keeps me up at night.

In this series, I present various access patterns for a specific document model. These patterns are supported by a limited set of secondary indexes designed to make queries efficient, without modifying the document schema.

This article explores recursive searches through graph-like relationships between documents, with each video in this collection showcasing related content with an array of related videos:

[
  {
    _id: '---U8lzusKE',
    category: 'Entertainment',
    relatedVideos: [
      'x9LRHlMdZmA', '5P5nxdJAFdE',
      'jdg8Sp1HUKM', 'xdxVBiJe8Co',
      'qLSA0gQ9z28', 'WHZPEkZCqwA',
      'y3VMhFCLxRc', 'hHjGtBnSv50',
      '_vx1OVLX5Rc', 'V4LnorVVxfw',
      'l56K8eAtCig', 'dHpCoFyMCHU',
      'XO5BYR39te8', 'yWy0cuxNWDw',
      '4SiXdhL7wxU', '5EaZTxQeQMQ',
      'mOvmBNLQIi4', 'fa2CvFa2xY8',
      'CpbYBZKdi3s', 'lBxzoqTSILc',
      'RBumgq5yVrA', 'EoN8RKubbO0',
      'zIHQPgz_Iwg', '7PCkvCPvDXk',
      't1NVJlm5THo'
    ],
...

With this structure, I can easily navigate from one video to its related ones, and from there to further related content, effectively building a graph of interconnected videos. There’s no need for an additional index since each video references the "_id" of its related videos, which is always indexed.

Access Patterns: forward traversal of related documents

The following query identifies a video and explores down to three levels of related videos, constructing a graph of connections based on the associated video array. It filters these connections by daily views and restructures the output for improved readability:

db.youstats.aggregate([  
  {  
    $match: { _id: 'YoB8t0B4jx4' }   
  },  
  {  
    $graphLookup: {  
      from: "youstats",  
      startWith: "$relatedVideos", 
      connectFromField: "relatedVideos", 
      connectToField: "_id", 
      as: "allRelatedVideos", 
      maxDepth: 3,  
      restrictSearchWithMatch: {  
        "views.daily.data": { $gt: 1e6 } 
      },  
      depthField: "level" 
    }  
  },  
  {  
    $project: {  
      _id: 1,  
      title: 1,  
      author: 1,  
      allRelatedVideos: {  
        $map: {  
          input: "$allRelatedVideos",  
          as: "video",  
          in: {  
            number: { $add: [ { $indexOfArray: ["$allRelatedVideos", "$$video"] }, 1 ] },  
            _id: "$$video._id",  
            title: "$$video.title",  
            author: "$$video.author",  
            level: "$$video.level"  
          }  
        }  
      }  
    }  
  }  
])

The execution plan shows the IXSCAN only during the $match stage, but the subsequent iterations utilize the same method.

    stage: 'EXPRESS_IXSCAN',
    keyPattern: '{ _id: 1 }',

With $graphLookup, you need to have an index on connectToField.

Access Patterns: backward traversal of related documents

To navigate the graph in the opposite direction and find the parent with the _id in its related videos array, an index on that field is essential for quick access. In MongoDB, indexes are created similarly for both scalar fields and arrays:

db.youstats.createIndex({ 
 relatedVideos: 1, _id: 1 
});  

The following query, where the connectToField is the related videos array, is fast:

db.youstats.aggregate([  
  {      
    $match: { _id: 'x9LRHlMdZmA' }       
  },      
  {      
    $graphLookup: {      
      from: "youstats",      
      startWith: "$_id",      
      connectFromField: "_id",      
      connectToField: "relatedVideos",      
      as: "parentVideos",      
      maxDepth: 3,       
      depthField: "level",  
      restrictSearchWithMatch: {      
        "views.daily.data": { $gt: 1e6 }      
      }      
    }      
  }      
]);  

Using $graphLookup in an aggregation pipeline effectively retrieves a limited number of documents, as long as the work area remains within the 100MB memory limit and results do not exceed the BSON limit of 16MB. For utilizing MongoDB as a document database, consider PuppyGraph (Querying MongoDB Atlas Data as a Graph). The same indexes allow fast recursive search and can be on a scalar identifier or an array or of child, depending if you implemented the one-to-many in the one-side or many-side.

While testing Postgres 18 beta1 on a large server I used several configurations with io_workers set to values the are too large and performance suffered. The default value for it is io_workers and that appears to be a great default. Perhaps other people won't repeat my mistakes.

tl;dr

• the default value for io_workers is 3 and that is a good value to use
• be careful about using larger values for io_workers as the performance penalty ranges from 0% (no penalty) to 24% (too much penalty