JSONB performance: declare access patterns to PostgreSQL query planner
A common drawback of storing data in JSON fields is limited planner visibility: the optimizer doesn't automatically inspect documents, so selectivity estimates for JSON (or JSONB or BSON) predicates are often weak when useful statistics aren’t available. That’s expected—if you choose JSON to avoid defining the full application schema in the database upfront, the database can’t infer everything about those fields.
But this isn’t an all-or-nothing tradeoff. Even in MongoDB, you still declare part of the schema in practice—especially filter fields—through indexes. PostgreSQL, gives you two levers:
- expression indexes (which provide both access paths and statistics)
- extended statistics (which improve estimates even without adding an index).
I'm using an example from an old article by Dan Robinson on the Heap blog: When To Avoid JSONB In A PostgreSQL Schema. At the time, PostgreSQL 9.4 didn't support extended statistics, but expression indexes could have solved this (example on db<>fiddle), avoiding the expensive nested-loop join and producing good cardinality estimates.
I've run the following in a lab:
CREATE TABLE measurements (tick BIGSERIAL PRIMARY KEY, record JSONB NOT NULL)
;
INSERT INTO measurements (record)
SELECT jsonb_build_object(
'value_1', trunc(2 * random())::int,
'value_2', trunc(2 * random())::int,
'value_3', trunc(2 * random())::int,
'scientist_id', trunc(10000 * random() + 1)::bigint
)
FROM generate_series(1, 100000)
;
CREATE TABLE scientist_labs (scientist_id BIGINT PRIMARY KEY, lab_name TEXT NOT NULL)
;
INSERT INTO scientist_labs (scientist_id, lab_name)
SELECT i,(ARRAY['California Scientific','Industrial Labs','Western Science People'])[(i % 3) + 1]
FROM generate_series(1, 10000) AS i
;
VACUUM (ANALYZE) measurements
;
VACUUM (ANALYZE) scientist_labs
;
Get the execution plan for this query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT s.lab_name, COUNT( * )
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE (record ->> 'value_1')::INT = 0
AND (record ->> 'value_2')::INT = 0
AND (record ->> 'value_3')::INT = 0
) AS m
JOIN scientist_labs AS s
ON s.scientist_id = m.scientist_id
GROUP BY s.lab_name
ORDER BY s.lab_name
;
The estimated cardinality for "measurements" after the filter is rows=1, which is why PostgreSQL chooses a Nested Loop. However, the actual row count is rows=12518.00, so "scientist_labs" is accessed loops=12518 times:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=5827.32..5827.34 rows=1 width=28) (actual time=145.667..166.436 rows=3.00 loops=1)
Group Key: s.lab_name
Buffers: shared hit=39186
-> Sort (cost=5827.32..5827.33 rows=1 width=20) (actual time=139.215..150.787 rows=12518.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 645kB
Buffers: shared hit=39186
-> Nested Loop (cost=0.29..5827.31 rows=1 width=20) (actual time=0.156..126.483 rows=12518.00 loops=1)
Buffers: shared hit=39186
-> Seq Scan on measurements (cost=0.00..5819.00 rows=1 width=105) (actual time=0.029..60.878 rows=12518.00 loops=1)
Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Rows Removed by Filter: 87482
Buffers: shared hit=1819
-> Index Scan using scientist_labs_pkey on scientist_labs s (cost=0.29..8.31 rows=1 width=28) (actual time=0.002..0.002 rows=1.00 loops=12518)
Index Cond: (scientist_id = ((measurements.record ->> 'scientist_id'::text))::bigint)
Index Searches: 12518
Buffers: shared hit=37367
Planning Time: 0.433 ms
Execution Time: 166.557 ms
(19 rows)
Compare the Planned rows with the actual rows:
This is not optimal but easily fixed by indexing the fields used to filter:
CREATE INDEX measurements_value_1_idx ON measurements (
((record ->> 'value_1')::INT)
);
CREATE INDEX measurements_value_2_idx ON measurements (
((record ->> 'value_2')::INT)
);
CREATE INDEX measurements_value_3_idx ON measurements (
((record ->> 'value_3')::INT)
);
ANALYZE measurements;
We index an expression to retrieve its value by using the full path, and specify the data type the filter uses. This is like declaring part of the schema for the "schemaless" datatype—the parts correspond to the access pattern.
ANALYZE also gathers statistics about the index keys for the query planner, which uses them for the query:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=4309.88..4309.88 rows=3 width=28) (actual time=96.348..96.403 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1977
-> HashAggregate (cost=4309.82..4309.85 rows=3 width=28) (actual time=96.310..96.362 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1977
-> Hash Join (cost=1395.30..4247.13 rows=12538 width=20) (actual time=25.260..80.897 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1977
-> Bitmap Heap Scan on measurements (cost=1099.30..3913.50 rows=12538 width=105) (actual time=2.671..25.013 rows=12518.00 loops=1)
Recheck Cond: ((((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_1'::text))::integer = 0))
Filter: (((record ->> 'value_3'::text))::integer = 0)
Rows Removed by Filter: 12593
Heap Blocks: exact=1819
Buffers: shared hit=1906
-> BitmapAnd (cost=1099.30..1099.30 rows=24880 width=0) (actual time=2.416..2.430 rows=0.00 loops=1)
Buffers: shared hit=87
-> Bitmap Index Scan on measurements_value_2_idx (cost=0.00..545.74 rows=49793 width=0) (actual time=1.210..1.213 rows=49896.00 loops=1)
Index Cond: (((record ->> 'value_2'::text))::integer = 0)
Index Searches: 1
Buffers: shared hit=43
-> Bitmap Index Scan on measurements_value_1_idx (cost=0.00..547.04 rows=49967 width=0) (actual time=1.144..1.147 rows=50332.00 loops=1)
Index Cond: (((record ->> 'value_1'::text))::integer = 0)
Index Searches: 1
Buffers: shared hit=44
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=22.575..22.584 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.025..11.113 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning Time: 0.278 ms
Execution Time: 96.487 ms
(34 rows)
The estimated cardinality of "measurements" (rows=12538) is close to the actual count (rows=12518), so the query planner chooses a Hash Join and reads "scientist_labs" only once.
The query planner estimates the predicate’s overall cardinality by multiplying the selectivity of each field. This works here because the fields are uncorrelated. Three separate indexes offer flexibility when queries don’t filter on all three fields, but require a bitmap combination. If the fields always appear together in the filter, a compound index would be better:
CREATE INDEX measurements_value_123_idx ON measurements (
((record ->> 'value_1')::INT)
,
((record ->> 'value_2')::INT)
,
((record ->> 'value_3')::INT)
);
ANALYZE measurements;
The query planner prefers this index, and it still estimates cardinality correctly: rows=12564 for rows=12518.00:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2922.62..2922.63 rows=3 width=28) (actual time=71.266..71.279 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1890 read=12
-> HashAggregate (cost=2922.57..2922.60 rows=3 width=28) (actual time=71.246..71.257 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1890 read=12
-> Hash Join (cost=500.48..2859.75 rows=12564 width=20) (actual time=17.645..59.549 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1890 read=12
-> Bitmap Heap Scan on measurements (cost=204.48..2526.04 rows=12564 width=105) (actual time=0.669..16.853 rows=12518.00 loops=1)
Recheck Cond: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Heap Blocks: exact=1819
Buffers: shared hit=1819 read=12
-> Bitmap Index Scan on measurements_value_123_idx (cost=0.00..201.34 rows=12564 width=0) (actual time=0.465..0.466 rows=12518.00 loops=1)
Index Cond: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Index Searches: 1
Buffers: shared read=12
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=16.956..16.959 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.016..8.454 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning:
Buffers: shared hit=26 read=1
Planning Time: 0.521 ms
Execution Time: 71.397 ms
(28 rows)
The same from the PostgreSQL extension visualization in Visual Studio Code:
Even when I disable index access, the cardinality estimate remains correct:
postgres=# set enable_bitmapscan to off;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS)
SELECT s.lab_name, COUNT( * )
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE (record ->> 'value_1')::INT = 0
AND (record ->> 'value_2')::INT = 0
AND (record ->> 'value_3')::INT = 0
) AS m
JOIN scientist_labs AS s
ON s.scientist_id = m.scientist_id
GROUP BY s.lab_name
ORDER BY s.lab_name
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=6215.58..6215.59 rows=3 width=28) (actual time=98.676..98.691 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1890
-> HashAggregate (cost=6215.53..6215.56 rows=3 width=28) (actual time=98.652..98.665 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1890
-> Hash Join (cost=296.00..6152.71 rows=12564 width=20) (actual time=22.378..85.932 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit
November 19, 2026
From instant Aurora PostgreSQL databases to production-ready in your VPC
Building a no-code AI app on Amazon Aurora DSQL with agent skills, plugins, and MCP
Performance impact of excessive logging in Amazon Aurora PostgreSQL
Announcing VillageSQL Server 0.0.8
Java4Shell Vulnerability: Percona Security Update
November 19, 2026 | Percona Security Team Percona is aware of the recently disclosed Java4Shell vulnerability (CVE-2026-114938), a critical security issue reportedly affecting OpenJDK and related Java distributions. The vulnerability involves memory corruption in Java’s Base64 decoder and may allow unauthenticated remote code execution (RCE) under certain conditions. Following the public disclosure, Percona’s Security … Continued
The post Java4Shell Vulnerability: Percona Security Update appeared first on Percona.
Why MySQL OR is slower than IN
Queries with OR conditions are commonly used to implement business logic. However, it might be slower than we expected. Although the query below scans only 1 million rows, it runs for 6,7254 seconds. [crayon-6afe45fcc8337811746452/] The CPU profile indicates that 99% of CPU time was spent in [crayon-6afe45fcc8341482406539-i/] on MySQL 8.4. [crayon-6afe45fcc8344072208938/] How MySQL evaluates OR conditions MySQL … Continued
The post Why MySQL OR is slower than IN appeared first on Percona.
Building PlanetScale with Insights Agent
November 18, 2026
How to use Amazon Aurora DSQL audit logs
Village News: MySQL News + Events (18 November 2026)
Welcome back to Village News, our curated roundup of MySQL and database news. This week, MariaDB shipped its Q4 maintenance releases across four LTS series, and OurSQL Foundation opened a call for MySQL-compatible projects to share its FOSDEM 2027 booth.
If you want to get these updates, just subscribe
TLAPS-Bench: Formally Proving Correctness Of Real-World System Specifications With TLA+ Proof System
TLA+ specification language has long been associated exclusively with TLC model checker in most people's minds. Model checking with TLC finds bugs fast, but it only covers the configuration you give it, and is cheap only for small configurations (3 nodes, value ranges "1..4", etc.). There is also the TLA+ Proof System, TLAPS, which checks a deductive proof, instead of enumerating states like model checking. After you write the proof in TLA+ as a hierarchy of numbered steps, TLAPS turns each step into proof obligations and hands them to backend provers. Being a prover, TLAPS can cover all configurations at once, but it requires a lot of expertise and effort. I had always stayed away from TLAPS, because I hadn't felt I am up to the challenge. You have to figure out the inductive invariant (see below), show every action preserves it, and divide the argument into obligations small enough for Z3, Isabelle, or LS4 to check. So in practice what ends up happening is that almost everyone model checks and almost nobody proves.
Well, like with most things in our industry, this changed drastically in the last year. Earlier this summer, my mind was blown when I saw Claude doing TLAPS proofs! Holy molly, I didn't have this on my bingo card for this year! I knew it would be smart to invest more on this, and it turns out Tianyin Xu, Qian Cheng, and Markus Kuppe were already working on it, and they had a paper in the works. I joined the team late, and I am not sure if I was able to contribute significantly. It was great to collaborate and associate with such a qualified team nevertheless.
The paper is now on arXiv, and the code is on GitHub. The paper asks how much of the proof burden the agents can carry today, and introduces a benchmark and a harness to investigate this. TLAPS-Bench is a benchmark of 265 proof targets from 95 TLA+ specs of real systems and protocols. Tilapia is the harness that runs agents on those targets, and it provides the proof checker, the model checkers, the proof libraries, and the guards that keep agents from cheating. We evaluated on the 70 hardest targets, and the best model proved 44.3% of them. I hope the labs use TLAPS-Bench to train agents to get a lot better at proofs, and at autoformalization in general, over the next few model releases.
What's new here?
Most verification benchmarks are limited proof-completion tasks. They hand the model a scaffold. Selene and FVELer pull lemmas out of seL4's Isabelle proof. CoqStoq and VeriSoftBench pull theorems out of finished Rocq and Lean projects. NTP4VC supplies the verification conditions. VeruSAGE-Bench and Laurel extract functions from systems that were already verified. The model gets the helper lemmas, the intermediate invariants, or the theorem dependency structure, and fills in the gaps. TLAPS-Bench gives it none of that.
The second difference is scope. Dafny, Verus, and Lean benchmarks target the functional correctness of a single function, API, or module. Even the ones that ask the model for an invariant (DafnyBench and VerusBench), want a loop invariant inside one function. None of these asks for the inductive invariant of a distributed protocol. In contrast, TLAPS-Bench attacks this head on. The properties in TLAPS-Bench are about the whole system over time: no two leaders in a term, committed entries survive crashes, transactions serialize, cache lines stay coherent.
A recent paper, TLA-Verus, advocates translating TLA+ specs into Verus and proves them there, stating incorrectly that TLAPS cannot prove arbitrary temporal properties. (Writing `BY PTL` on a proof step tells TLAPS to treat that step as propositional temporal logic and send it to LS4, an LTL decision procedure.) On the contrary, translating out of TLA+ also means leaving the temporal logic behind. Verus verifies Rust, and it knows nothing about [] and <>, so you get temporal logic only as a library via Anvil. Anvil built always, eventually, leads_to, and WF1 on top of Verus. Lean, which has been getting a lot of deserved coverage lately, is in the same position. Lean is a general purpose proof assistant, so to reason about a protocol you write your own state machine and your own temporal operators, or borrow from another framework, like Veil. In TLA+ the state machine comes included with the language. You write variables, an initial predicate, and actions as before/after relations, and "always", "eventually", "leads_to" come with the logic. A behavior is an infinite sequence of states, and the spec and the property you want of it are both formulas over those behaviors. Verus and Lean are built for different jobs, and they are good at them. But TLA+ is where the state machine and the temporal logic come built in.
Refinement is the other thing you get for free in TLA+. To show that an implementation satisfies its abstract spec, you just write an implication between two temporal formulas. For each variable in the abstract spec, you supply a "refinement mapping" that computes it from the implementation's variables. Then you prove Concrete => Abstract, with the same prover you use for everything else. In other systems, refinement is machinery you have to build (via establishing/proving a simulation relation between the two state machines). Since TLA+ hands refinement to you as implication, TLAPS-Bench can carry refinement targets next to the safety and liveness ones, like Wildfire's cache coherence protocol refining the Alpha memory model. Nice!
So TLA+ lets you state the global system properties you actually care about, and TLAPS proves it for every configuration rather than the few small ones TLC can enumerate. This combination is what our benchmark builds on. TLAPS-Bench gives the agent a spec and a target theorem such as Spec => []LeaderUniqueness. These targets come from real system specs (etcd Raft, HashiCorp Raft, ZooKeeper, CCF, MongoDB distributed transactions), and include liveness and refinement, not just safety. The full collection has 95 specs and 265 targets, but the easy ones have been taken out. Classics like Paxos, Bakery, and Byzantine Paxos are things current agents already prove, so they sit in the appendix. (I can't believe I typed that. We are talking about the agents proving Byzantine Paxos out of the box!) What remains is an active set of 70 hard targets across 12 specs. The results in our paper cover only this active set, and the best model proved 31 of the 70 targets. This leaves plenty of room in our benchmark for the AI labs to tackle.
Falsify before you prove
This is the idea I like best in the paper. It shows how versatile the TLA+ ecosystem is. TLC and Apalache read the same TLA+ module TLAPS is working on, and they shoot down a bad candidate invariant in seconds, before the prover spends anything on it. Let me explain.
Let's start with what an inductive invariant is, since the rest of the section depends on it. You have a safety property Inv, and you want it to hold in every reachable state. A model checker gets there by enumerating the reachable states. A proof cannot enumerate, so it uses induction, and for that you need an IndInv with three properties: Init => IndInv, IndInv /\ Next => IndInv', and IndInv => Inv. The first two say the states satisfying IndInv include all initial states and are closed under the transition relation, so they include every reachable state. The third says IndInv still fits inside the property you wanted. So IndInv is squeezed from both sides. It has to be loose enough to be closed under Next, and tight enough to stay inside Inv.
Finding an inductive invariant is a guess-and-check loop. You guess a candidate, try to prove it, fail, stare at the failed obligation, and guess again. On TLAPS alone, each turn of that loop is slow and expensive. A failed SMT obligation says "unknown", and you are left to work out whether the candidate is false, too weak, or just needs a better decomposition.
So the idea is to put a model checker in the loop. TLC answers "is this candidate even true?" in seconds, and when the answer is no, it hands back a counterexample. That is much richer feedback than "unknown", because it shows exactly which state breaks which conjunct.
Figure 3 shows this loop. Let's say the agent wants to prove mutual exclusion via a token. It proposes as inductive invariant that "exactly one node holds the token". TLC returns a violation trace where node A sends the token and, while the message is in transit, no node holds it. So the agent refines to "exactly one node or in-transit message holds the token." TLC finds no violation, and this tells the agent that the candidate is worth proving for any number of nodes.
Apalache, being symbolic, brings a real advantage to the table. Instead of listing states one at a time, Apalache compiles the spec and a bounded number of steps into SMT constraints and asks Z3 whether a violation exists anywhere in that bound. That is what we want here, because it can start from every state satisfying a predicate, not just the states you can afford to list.
Our recipe is three checks. Write the candidate as IndInv == TypeOK /\ H, where TypeOK pins the domain of every variable and H carries the content. First, start from the real initial states and check the candidate holds there in zero steps: that is Init => IndInv. Second, start from /any/ state satisfying the candidate, take one step, and check the candidate still holds: that is IndInv /\ Next => IndInv'. Third, start from any state satisfying the candidate and check the target property holds in zero steps: that is IndInv => Inv. The second check is the interesting one. Starting from all candidate states rather than only the reachable ones makes this a true test of inductiveness. These three TLAPS obligations are falsified cheaply before we pay to prove them.
Apalache's symbolic encoding comes with the constrain that it has to type the spec. TLA+ is untyped set theory: a variable can hold a number in one state and a set of records in the next, and nothing in the language complains. But SMT has to know, so Apalache asks you to annotate every constant and variable with a type and infers the rest. When Apalache can't type the spec, we fall back to TLC with a trick from Lamport: make the candidate its own initial predicate (INIT IndInv, INVARIANT IndInv). This is the same inductiveness test as Apalache's second check, but this time performed by brute force instead of symbolically. TLC has to list every state satisfying the candidate before taking a step, and that list explodes quickly. To keep this tractable, we put each conjunct right after the TypeOK clauses that bind its variables so TLC prunes early, and sample large domains with RandomSubset. (I guess this might sound like gooble-de-gook to you, but you would have appreciated this if you had worked hands-on with TLC.)
Again, all of this is for identifying the inductive invariant. TLAPS gives definitive irrefutable proof after we identify an inductive invariant.
You Can't Cheat the Tilapia
We named our agentic framework Tilapia (TLA+ Proof Infrastructure for Agents). Beside the phonetic fit, we also went for a subliminal message here. Tilapia is about the toughest fish out there. It tolerates low oxygen, murky water, wide temperature swings, and brackish water, and it eats nearly anything. It is the cockroach of the pond.
Tilapia gives each run its own Docker container with pinned versions of the four TLA+ tools: SANY for parsing, TLAPS for proofs, TLC and Apalache for model checking. The container also holds the TLAPS library and CommunityModules for set and sequence theorems, two skills that teach the agent the model checking recipes from the previous section, and a check_proof_bin command that gives the same verdict the final grading gives. A task is three modules: the model, the target properties, and a third one where the agent writes helper definitions, lemmas, and one proof per target.
The harness needed to be tough, because the agents tried to cheat. We saw three kinds in early runs, before the current checks were in place. An agent added ASSUME Useful == FALSE to the module and derived ZooKeeper's agreement theorem from it. An agent cited a helper lemma whose proof was PROOF OMITTED. An agent replaced /opt/tlapm/bin/tlapm with a shell script that printed "All 214 obligations proved" and exited 0.
Tilapia has three fences, one for each of those. The first is context integrity. The model and the target theorems sit in read-only modules, and SANY rejects any new ASSUME, AXIOM, CONSTANT, or VARIABLE in the agent's region. The agent can add definitions and lemmas, but it cannot change the context the target is proved in. The second is dependency tracking. SANY collects the lemma citations into a dependency graph, and a target counts only if its own proof checks and every lemma it cites, transitively, is proved too. The third is clean-room grading. The verdict comes from a fresh container with the original task files and unmodified tools, carrying over only the submitted module, so a patched tlapm or a helpful scratch wrapper never reaches the grader.
Proofmaxxing
So what happened when we ran this? We put four GPT models through Codex at max, high, and low reasoning effort, twelve configurations over the 70 targets. Every number is pass@3. Each target gets three independent runs, and it counts if any one of them proves it. Each run gets its own container, with an hour of TLAPS time per proof and a budget of two million output tokens per spec.
Max effort is where every model does its best. GPT-6.1 Sol proved 31 (44.3%). GPT-6 Astra proved 30 (42.9%), using about half Sol's tokens at three times the price. GPT-5.6 Terra proved 15 (21.4%) for and over 36 days of cumulative agent time. GPT-6 Luna proved 7 (10%). Sol and Astra both proved all 15 FLASH targets, eight of them liveness.
If you turn the effort down, things degrade quickly. Sol and Astra drop to 24 or 25 targets. Terra and Luna prove nothing at high or low effort. Below some reasoning threshold, you get no proof. Nobody proved the Wildfire refinement at any effort.
Lessons learned
Table 5 sorts every attempt that did not end in an accepted proof over all models, effort levels, and runs. The two big buckets are where the difficulty is: 1,455 incomplete proofs and 537 undecomposed proof attempts.
Incomplete proofs mean the invariant was never found. This is the biggest bucket. Either the agent could not find an inductive invariant strong enough for the target, or it proved a stack of helper lemmas and never connected them to the target. Here are two examples. On OpenAddressing's Consistent, Astra split the proof in two. The case with no hash collisions it proved with a dedicated invariant. The case with collisions and evictions it handed to a single SMT call citing three invariants it already had. Its own comment in the proof says that case needs a stronger invariant. So the accepted part of the proof only covers configurations where no collision ever happens. On Ivy TLB's NonStarvation, the agent proved the safety target with a 29-lemma inductive invariant, then tried to get liveness from that invariant plus the fairness assumptions. It skipped the progress lemmas that say the locks are eventually released and the loops terminate, and left a comment listing those missing steps. In both cases the agent knew what was missing, but could not produce it.
Undecomposed attempts mean the solver chokes. On Cahill SSI, Astra had a sound strategy, a ranking function, and it proved the supporting lemmas. Then it sent the entire inductive step to Z3 as a single obligation. Z3 said unknown, and the agent sent the same obligation again. Committing a transaction in that protocol can force other transactions to abort, while updating history, locks, and conflict flags. Each of those updates has to be shown to preserve the ranking argument. That is several pages of reasoning in one SMT query. The strategy was right, but it was not cut into pieces the solver could handle.
Abstraction wins. Cutting the obligation into pieces (decomposition) is not the only way to get the proof pass through the solver. Abstraction, in the modeling sense, asks what the property actually depends on and throws away everything orthogonal to it. Then the cases you would have argued one by one collapse into a single argument. Look at the two attempts at the ZooKeeper implementation. Terra reasoned directly over the implementation state, transaction origins and queued messages and synchronization state. It generated about 13,000 lines of intermediate proof, ran out of token budget, and proved neither leadership property. Astra first asked what leadership actually depends on, and found that a recorded vote and an eligible acknowledgment still in flight are the same thing: a claim of support for a leader. Once both are represented the same way, the synchronization and broadcast actions preserve the invariant for one common reason instead of a dozen separate ones, and both leadership properties follow. The level Astra chose to work at made the whole difference.
Bugs found
The specs in the benchmark are not guaranteed to be correct. They come from real systems, a few of them generated from source code, and most had been model checked a lot but never proved. Trying to prove them turned up 46 issues, 35 of which are now fixed. They came in three kinds.
The theorem was wrong. etcd Raft's LeaderCompletenessInv compared a leader's term against the term that created an entry, rather than the term that committed it. Raft allows an entry to be committed late, so the property as stated is false. The agent produced a 47-transition counterexample. A task can end this way, with a counterexample instead of a proof, as long as TLC or Apalache confirms the trace.
The model was wrong. SlateDB's WAL had a 58-step trace where garbage collection deletes a data entry, a delayed writer writes its fence into the vacated slot, and a writer replaying the log skips that fence and reaches READY without the deleted data. We reproduced the trace and reported it upstream. The fix validates the writer's epoch after replay, and on the revised model all four targets go through.
The configuration was hiding the problem. MongoDB's distributed transactions spec, one I wrote with Will Schultz, did not explicitly rule out a transaction starting at two routers with different read timestamps. The implementation rules this out, because a transaction stays pinned to the router that started it, and the spec took that as given instead of stating it. The model checking config used a single router, which supplied this assumption for free, so TLC was never in a position to notice, but the proof attempt called this assumption out explicitly.
Bounded model checking with a narrow config missed these. The proof attempt forced the question "why is this true in general?", and model checking with the right configuration produced the counterexample. Neither tool alone got there.
Proving gives you insight. A model checker can tell you when something breaks and hands you a trace. But a proof tells you why, and when the proof fails, it fails at a specific obligation, on a specific action, against a specific conjunct of the invariant. That points at the design assumption that is wrong, not just the symptom. Proofs are also incremental. TLAPS fingerprints the obligations it has already discharged, so after an edit only the affected steps need re-proving, and the ones that fail show which part of the argument the change broke. In contrast, a model checker reruns from scratch and hands you another trace.
Proofs people can follow
Another advantage of proof over model checking is that it leaves you with an inductive invariant, as a short statement of why the system works, and the lemmas get reused across targets and across revisions of the spec. This is awesome, but how readable are these? Sol's accepted max-effort proofs total over 15K lines. If agents produce proofs faster than engineers can follow them, the team incurs cognitive debt. You get a green checkmark backed by an argument nobody on the team can explain. So there is clear future work here: we need a user-friendly way to convey what a proof says. Surface the key invariants in plain language, tie each lemma to the design decision it relies on, and show which parts of the argument each action touches.
PlanetScale ranks #24 in Deloitte's Technology Fast 500
November 17, 2026
Use query statistics to find slow and expensive queries in Aurora DSQL
Beyond Disk and WAL: What Limits PostgreSQL Scaling?
The earlier tests improved read I/O and removed standby acknowledgment waiting, but the primary still waited for local WAL synchronization. Separate, later durable-regime traces located substantial delay in guest-OS write admission. This post returns to the diagnostic where I stopped waiting for commit durability to see what remained.
I used session-level synchronous_commit=off for the same 512-worker, 15+45-minute workload. This allows a successful commit response before WAL is durable: a crash can lose recently acknowledged transactions. Keep in mind that I do this in a lab to investigate PostgreSQL bottlenecks. It deviates from the initial comparison with HorizonDB's built-in high-availability and from the original durable-HA configuration. Still, let's see how far we can increase the throughput in this lab.
WAL, fsync and full_page_writes remained enabled. Both streaming replicas were retained. Automatic Patroni failover was separately paused for the diagnostic.
Verify the mode, then measure
The override was supplied through the workload's PGOPTIONS. Before launch, a test Pgtcl connection using that environment executed:
SHOW synchronous_commit;
Its recorded check was:
2026-11-08T08:08:47.692827+00:00 BENCH_COMMIT_MODE_CHECK adapter=Pgtcl effective=off
This records the adapter test connection's setting. The workload was launched with the same connection environment, while the server default remained on.
The result was:
2026-11-08T09:08:53.198898+00:00 Vuser 1:TEST RESULT : System achieved 66096 NOPM from 152007 PostgreSQL TPM
93.92% of sampled active-client observations were at LWLock:BufferContent. The question had moved from when WAL becomes durable to which backends needed conflicting access to an in-memory page. I wanted to identify that page before changing shared_buffers.
The page identification below comes from a later synchronous_commit=on follow-up.
What BufferContent protects
BufferContent is a wait for a lightweight lock protecting the contents of a cached relation page, normally 8 KiB. Shared holders can coexist. Exclusive access conflicts with other holders, even when the page is already in memory.
BufferMapping instead concerns associating a block with a buffer slot. BufferIO concerns buffer I/O completion.
For Oracle readers, LWLocks are closer to latches and mutexes than transactional enqueues: they protect shared-memory structures, not business rows until commit. PostgreSQL's heavyweight lock manager is the closer comparison for transaction and object locks. pg_locks and pg_blocking_pids() do not expose a content-LWLock holder.
A buffer pin is different again: it prevents recycling the slot, but does not grant exclusive access to its contents.
Oracle's buffer busy waits includes P1/P2/P3 parameters identifying file, block, and block class. PostgreSQL's pg_stat_activity exposes neither that page identity nor per-wait elapsed time for BufferContent. query_start times the statement, not its current wait. Oracle active-session history is sampled too. Samples and timed waits are different measurements.
Observe active backends
The collector grouped pg_stat_activity by backend type, state, and wait event. This query shows the active-client part:
SELECT wait_event_type,
wait_event,
count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY sessions DESC;
active describes statement execution status and includes statements currently waiting.
These are retained samples from the synchronous_commit=off measured window, November 8, 2026, 08:23:51-09:08:53 UTC:
| Observer timestamp, UTC | Phase | Active clients | At BufferContent | Percentage |
|---|---|---|---|---|
| 08:24:43.424849 | measurement | 513 | 476 | 92.79 |
| 08:34:43.913026 | measurement | 512 | 483 | 94.34 |
| 08:44:44.413830 | measurement | 512 | 491 | 95.90 |
| 08:54:44.923233 | measurement | 513 | 484 | 94.35 |
| 09:08:45.608882 | measurement | 513 | 470 | 91.62 |
The collector included all active client backends, including its own connection, without database/user filtering. These counts therefore differ from the 512 workload workers.
Across all 45 samples taken one minute apart inside the measured interval, there were 23,063 active-client observations:
| Wait | Observations | Share |
|---|---|---|
LWLock:BufferContent |
21,660 | 93.92% |
IO:DataFileRead |
981 | 4.25% |
| No reported wait | 376 | 1.63% |
Client:ClientRead |
25 | 0.11% |
IO:DataFileFlush |
10 | 0.04% |
IO:DataFileWrite |
7 | 0.03% |
IO:DataFileExtend |
2 | 0.01% |
IPC:BufferIo |
2 | 0.01% |
None of the 45 snapshots contained LWLock:WALWrite or IPC:SyncRep. The 93.92% is 21660 / 23063, the share of sampled active-client observations.
Background persistence continued. In-window WAL counters showed approximately 194.46 MiB/s generated and 220.76 syncs/s. WAL must still precede durable data-page writes.
A buffer is not necessarily a table's data page
Shared buffers can hold more than application table rows:
| Page contents | Examples |
|---|---|
| Table heap | Row versions, headers and item identifiers |
| Index | B-tree, GIN, GiST, SP-GiST, hash or BRIN pages, including metadata |
| TOAST relation | Out-of-line values and their index |
| System catalogue | Catalogue heap and index pages |
| Materialized view | Stored rows and indexes |
| Sequence | The sequence relation's state page |
| Relation metadata | Free-space-map and visibility-map pages |
| Unlogged relation | Working pages and initialization-fork pages |
Partitions store their own pages. The partitioned parent does not store heap rows. Temporary relations use backend-local buffers. WAL buffers and SLRU caches, such as transaction-status caches, are separate structures with different synchronization.
Relation forks and page identity
A fork is a physical part of a relation with a specific purpose:
| Number | Fork | File suffix | Contents |
|---|---|---|---|
| 0 | main |
none | Heap, index or other primary relation storage |
| 1 | fsm |
_fsm |
Free-space map |
| 2 | vm |
_vm |
All-visible and all-frozen information for heap pages |
| 3 | init |
_init |
Unlogged relation's initialization state after an unclean shutdown |
Not every relation has every fork. Indexes have no visibility map. See the file-layout documentation.
PostgreSQL's BufferTag identifies a page by tablespace, database, filenode, fork, and block number. All five matter: main-fork block 0 and VM-fork block 0 are different pages. Catalog OIDs and physical filenodes are distinct identifiers. A rewrite can change the filenode while preserving the OID.
Separate follow-up: identifying pages with synchronous_commit=on
For page-level diagnosis, I used a separate 512-worker pilot with synchronous_commit=on and normal HA. BufferContent was still visible in the capture-trigger samples at 5.47-18.75%, enough to trace page contention under ordinary durable commits.
1️⃣ Capture the page tag at the lock event. I traced selected PostgreSQL processes by PID with Linux perf. Uprobes are tracing points in userspace machine code. I checked their instruction locations and registers against the installed PostgreSQL executable.
At acquisition, semaphore-wait and release points, the probes recorded the lock address and the full page tag from its buffer descriptor. Pairing required the same page identity at the beginning and end of an interval.
Eight nominal three-second recordings sampled selected backends and background processes. This is a selected-field excerpt of the decoded, aggregated wait output:
{
"tag": [1663, 57465, 57491, 2, 10215],
"waited_loop_count": 78,
"loop_wait_ns": 2357679631
}
In order, that tag means tablespace 1663, database 57465 (tpcc), filenode 57491, fork 2, block 10215. Fork 2 was recorded by the probe.
2️⃣ Resolve the filenode through the catalogs. Connected to tpcc, the collector mapped captured filenodes through pg_class. This reduced lookup resolves the same identity:
SELECT n.nspname AS schema,
c.relname AS relation,
c.relkind,
am.amname,
pg_relation_filenode(c.oid) AS filenode
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_am am ON am.oid = c.relam
WHERE c.oid = pg_filenode_relation(1663::oid, 57491::oid);
The retained catalog row, reformatted from the collector's JSON:
schema | relation | relkind | amname | filenode
--------+----------+---------+--------+---------
public | stock | r | heap | 57491
The relation is public.stock. Fork 2 identifies its visibility map, so this is stock VM block 10215, not stock heap block 10215.
3️⃣ Keep the block identity in the results. These were the leading measured pages by cumulative completed semaphore-loop elapsed time. All have the same tablespace/database/filenode and VM fork:
| Stock VM block | Completed loops | Cumulative loop ms |
|---|---|---|
| 10215 | 78 | 2,357.680 |
| 10031 | 66 | 2,206.913 |
| 10032 | 63 | 2,076.327 |
| 10033 | 55 | 1,984.848 |
Stock's VM fork was 94,912,512 bytes, or 11,586 8-KiB pages, consistent with those block numbers.
Selected processes contributed these completed loops. The elapsed intervals include semaphore work, rescheduling, and probe overhead. Several loops can belong to one acquisition.
Captured holder operations on these pages included heap_update. The longer waiter intervals remain only partially attributed.
The identification chain in this normal-on pilot is event-time tag -> catalogue lookup -> stock VM block identity. pg_buffercache shows current cache contents, where slots can be reused and the view is not one atomic snapshot.
Why visibility-map pages can contend
The visibility map stores two bits per heap page: all-visible and all-frozen, used by index-only scans and vacuum. With an 8-KiB block and 24-byte page header:
(8192 - 24) bytes * 8 bits / 2 bits per heap page
= 32672 heap pages per VM page
= 255.25 MiB of heap address space per VM page
Vacuum sets these bits. Data modifications can clear them. Sessions updating unrelated rows on different heap pages can therefore need exclusive access to the same VM buffer. One metadata page can serialize updates across many heap pages.
A related PostgreSQL change
A July 2026 correctness fix, authored by Melanie Plageman and Andres Freund and included in PostgreSQL 17.11, registers modified VM blocks in heap WAL records. Previously, incremental backups could omit those changes, and clearing VM bits did not produce the full-page images needed for torn-page protection.
Correct logging requires holding the VM lock through heap/VM modification and WAL insertion, extending the critical section.
In the performance discussion, Melanie tested 32 clients clearing bits for different heap pages covered by one VM page, while a vacuum loop set bits again. She observed no degradation with mostly default settings, but 10-15% lower TPS and more BufferContent waits with synchronous_commit=off. Vacuum timing varied between runs.
Her test illustrates how a longer VM critical section becomes visible as BufferContent contention when commit waits are removed.
Where this test ends
Here is the full-run record, linked back to the decisions behind it:
| Configuration | NOPM | Leading active-client observation |
|---|---|---|
| Premium SSD v2, normal HA | 50,648 | DataFileRead, 79.6% |
| Ultra primary, normal HA | 55,784 | WALWrite, 83.18% |
Ultra primary, commit local |
54,556 | WALWrite, 87.29% |
Ultra primary, commit off |
66,096 | BufferContent, 93.92% |
| Full normal-HA repeat | 54,163 | WALWrite, 83.17% |
The off result is 1.305x the original baseline, a 30.5% increase. The primary used the highest nonbursting Ultra budget documented for that tested VM configuration, while commits no longer waited for local or remote durability.
That result combined higher storage provisioning, weaker commit durability, and temporarily paused automatic failover. The runs reused the dataset, with cache contents, dirty pages, checkpoint phase, and vacuum state evolving between tests.
Mean primary CPU utilization in the off window was 18.1%. Low aggregate CPU utilization can coexist with serialization around a few lock holders. Page-level attribution was collected in the normal-on follow-up and identified stock visibility-map pages.
The progression is from a device-read constraint, through a check of remote commit waiting, into OS writeback admission, and finally to shared-page coordination. Removing replica acknowledgment waiting left WALWrite dominant. Each step narrowed where to investigate next.
At the last boundary, more disk IOPS does not make a content lock grant conflicting access simultaneously or split one shared metadata page into independently locked pieces. More workers can instead add contention. The remaining question concerns the amount of shared work, the duration of critical sections, and how access is coordinated.
Keep durability, move the work
The storage and commit-mode experiments took this workload from 50,648 to 66,096 NOPM, a 30.5% increase. The highest result allowed recent transaction loss after a crash. The PostgreSQL primary still had to coordinate access to shared pages.
Bypassing commit durability was a diagnostic step. The production objective is to preserve durable commits while reducing the work and coordination concentrated on the writer.
One approach is disaggregated PostgreSQL storage. Dedicated services handle WAL durability, replication, and data-page materialization. Moving that persistence work can reduce compute-side writeback and buffer maintenance. That's the HorizonDB architecture, which achieved 8x higher throughput for this workload, with better resilience to failures.
SQL execution, transaction semantics, and page-content synchronization remain in PostgreSQL compute. The writer has more headroom for execution and scale-up, while storage services can scale independently across machines. The important change is where the work runs, with the durability guarantee preserved.
HorizonDB retains PostgreSQL’s foreground page synchronization and visibility-map maintenance, while offloading page persistence, checkpoint I/O, full-page-write protection, and WAL distribution. This can ease pressure on those critical sections and give the writer more capacity for transactions. That may explain why the workload achieves higher throughput than this PostgreSQL configuration can support.
If you have resolved similar WAL or BufferContent contention on PostgreSQL, I'd be interested in the configuration, schema, or implementation change, and the measurements that showed improvement, while retaining durable commits.
Fast Disks, Slow WAL Sync: Finding Linux Writeback Throttling
As described in the previous post, the primary now uses a high-I/O Ultra disk. Reads and writes took fractions of a millisecond, yet PostgreSQL WAL synchronization averaged 25-30 ms. Removing synchronous-standby waits didn't help: synchronous_commit=local still requires local WAL durability. With device-read constraints reduced and remote acknowledgments excluded, I traced the primary's synchronization call across the guest-OS boundary.
To investigate those durable-commit timings, I returned to synchronous_commit=on with normal HA for a full repeat, then used a separate pilot prepared for controller tracing.
Measure the synchronization, not only the disk
The relevant cumulative PostgreSQL counters are:
SELECT wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset
FROM pg_stat_wal;
With track_wal_io_timing=on, the interval mean is:
mean WAL sync ms = delta(wal_sync_time) / delta(wal_sync)
The full runs showed:
| Run | NOPM | Mean WAL sync ms | WALWrite observation share |
|---|---|---|---|
| Ultra, normal synchronous commit | 55,784 | 25.443 | 83.18% |
| Ultra, local commit | 54,556 | 29.550 | 87.29% |
| Full normal-HA repeat | 54,163 | 24.995 | 83.17% |
For example, the repeat's in-window counter endpoints contained 45,266 additional syncs and 1,131,416.247 additional milliseconds of synchronization time. Their ratio is 24.995 ms.
The repeat was measured on November 8, 2026, 10:26:56-11:11:58 UTC with the same 512-worker, 15+45-minute protocol. These durable-regime traces and the later pilot were collected after the asynchronous-commit experiment described in part 4.
Why request completion and fdatasync disagree
fdatasync must synchronize a file's data. With buffered I/O, that can involve preparing dirty pages, submitting writeback, waiting for previously submitted work, and completing the necessary persistence operations. The entire call can therefore include:
PostgreSQL calls fdatasync
dirty-page writeback and submission
admission waits before block dispatch
issued requests and their completion
remaining file-synchronization work
fdatasync returns
The application call includes work before device dispatch. PostgreSQL can queue other backends behind its WAL write lock while the holder is delayed in that earlier work. I traced the caller to locate the delay.
The captured delay was before dispatch
A 30-second capture during the full normal-on repeat, 10:48:02-10:48:33 UTC, observed 536 completed fdatasync calls, averaging 24.504 ms, in PostgreSQL processes other than the separately classified WAL writer and checkpointer.
Captured sleep/rescheduling time inside those calls was classified as:
| Captured stack location | Share |
|---|---|
| Pre-dispatch WBT admission | 96.43% |
| File-writeback completion | 3.36% |
| Block-flush completion | 0.20% |
An abbreviated stack, from the sleeping function towards its callers, was:
io_schedule
rq_qos_wait
wbt_wait
__rq_qos_throttle
blk_mq_submit_bio
...
ext4_do_writepages
WBT is Linux block-layer writeback throttling. Its admission rules can make a submitting process sleep before work reaches the device. Fast completion of admitted requests does not measure that earlier wait.
These percentages describe captured sleep/rescheduling inside PostgreSQL fdatasync calls, including the delay until a process runs again. The syscall trace covers PostgreSQL synchronization calls broadly, while pg_stat_wal measures WAL synchronization specifically.
A healthy latency controller can still have admission waits
WBT controls write admission using read-latency feedback and write-depth limits. A separate normal-on pilot prepared the instrumentation before launch. Its five short measured captures showed:
| Observation | Value |
|---|---|
| PostgreSQL fdatasync calls | 867 |
| Mean syscall elapsed time | 26.123 ms |
| WBT share of captured sleep/rescheduling | 96.404% |
| Controller timer decisions | 482, all LAT_OK
|
| Observed read minima / target | 0.100-0.213 ms / 2 ms |
| Maximum / normal / background admission limits | 191 / 96 / 48 |
Read latency was healthy in the captured windows while callers still slept in the admission machinery. The captures recorded controller limits and aggregate wait paths. Slow synchronization was also observed before and after the probes ran.
The running kernel was 7.0.0-1017-azure. The upstream WBT implementation and request-QoS implementation explain the policy.
What this establishes
In the captured durable regime, much of the synchronization delay was in Linux's write-admission path, before device dispatch.
Provisioning more disk IOPS leaves the guest-OS admission rule unchanged. The relevant question has changed from the disk's advertised capacity to the caller's progress through writeback. All captures used the original WBT policy.
HorizonDB delegates durable WAL handling and data-page persistence to dedicated storage services. This replaces the compute-local data/WAL writeback arrangement, where we observed Linux throttling, while retaining durable commits. This explains why the workload achieves higher throughput than this PostgreSQL configuration can support.
The lab also tested asynchronous commit, where background persistence continues while commits can return before WAL is durable. Part 4 returns to that result and follows BufferContent into page identification.
Faster SSDs for PostgreSQL: From Read I/O to WAL Waits
The baseline in the previous post reached 50,648 NOPM, with most active-client observations at IO:DataFileRead. To understand the next bottleneck, I first increased the storage budget, then checked the next boundary in the commit path: acknowledgment from another availability zone. The objective was to distinguish device capacity from waiting higher in the path.
The 95,000-warehouse dataset, 512 workers, 15-minute ramp-up, and 45-minute measurement remained. The primary still had 64 vCPUs, 512 GiB RAM, and 128 GiB of shared buffers.
What changed on the primary
I changed storage only on the primary because this step targeted the benchmark's foreground read I/O (IO:DataFileRead). In this HammerDB TPROC-C run, all transactions, including their SELECT statements, used the primary endpoint. The workload did not offload reads to replicas. Doing so would require changing the tested execution and checking its consistency assumptions.
I migrated the existing data to an Ultra disk, which supports up to 400K IOPS and 10 GB/s throughput at low latency. The IO limits are then determined by the non-bursting E64bds_v5 with NVMe: 294,800 IOPS and 7,800 MB/s throughput.
| Original primary (previous post) | Higher-I/O primary (this post) | |
|---|---|---|
| VM | E64ds_v5 | E64bds_v5, NVMe |
| CPU / memory | 64 vCPUs / 512 GiB | 64 vCPUs / 512 GiB |
| Data disk | Premium SSD v2 | Ultra Disk |
| Capacity | 20,480 GiB, or 20 TiB | 20,480 GiB, or 20 TiB |
| Provisioned IOPS | 80,000 | 294,800 |
| Provisioned bandwidth | 1,200 MB/s | 7,800 MB/s |
| Filesystem | ext4 | ext4 |
Both data and WAL were on that remote disk.
The two replicas retained their Premium SSD v2 disks at 80,000 IOPS / 1,200 MB/s, as my goal was only to accelerate the reads on the primary. Each benchmark used the same-zone D64ds_v5 client with 64 vCPUs / 256 GiB. The standbys still performed I/O to receive, flush, and replay WAL. Their storage could affect synchronous commit. The local-commit diagnostic below separates their acknowledgments from the primary's own synchronization.
Keep in mind that the usable budget depends on both the disk and the VM. Ultra Disks support up to 400,000 IOPS / 10,000 MB/s, but the E64bds_v5 NVMe configuration lists 294,800 IOPS / 7,800 MB/s for Ultra Disk and Premium SSD v2 without bursting. I provisioned that budget. Ultra Disk has been generally available since August 2019 and can be used for self-managed PostgreSQL on Azure VMs. They are now generally available also for Azure Database for PostgreSQL.
Premium SSD v2: waiting for reads
The first result covered in the previous post in this series was 50,648 NOPM (new orders per minute). During measurement, the primary data disk delivered approximately 51,130 read + 30,469 write IOPS, with 429 MiB/s reads + 412 MiB/s writes. Its guest busy-time counter was approximately 100%, and mean read/write completion times were 7.98 / 10.95 ms. The active-backend sample observations were:
| Wait | Share |
|---|---|
IO:DataFileRead |
79.6% |
LWLock:WALWrite |
10.8% |
IPC:SyncRep |
5.2% |
That is the read-I/O-heavy baseline from part 1, with guest IOPS close to the provisioned 80,000-IOPS budget. The guest counters describe device activity. The wait percentages are shares of sampled active-client observations.
Ultra Disk: faster reads, then WALWrite
After moving the primary to Ultra, I got 55,784 NOPM, a 10.1% increase. Read/write completion times were about 0.31 / 0.23 ms. DataFileRead dropped to 2.56% of active-backend observations. But the wait mix had changed:
| Wait | Share |
|---|---|
LWLock:WALWrite |
83.18% |
IPC:SyncRep |
7.53% |
IO:DataFileRead |
2.56% |
WALWrite was now the dominant wait, and that's why it doesn't fully benefit from the faster disks.
The Ultra primary delivered about 81,221 combined read/write IOPS and 976 MB/s, below its provisioned 294,800 IOPS / 7,800 MB/s. Guest busy time was 99.95%: I/O was almost continuous while the totals remained below the provisioned limits.
WALWrite means a backend is waiting for PostgreSQL's WAL write lock. The holder may be performing writes or waiting for synchronization. A long wait can therefore originate above device execution, even when admitted requests complete quickly.
Faster standby storage could reduce acknowledgment time, but that was a separate part of the path. Before attributing the remaining limit to cross-zone synchronization, I removed that wait from the diagnostic.
Remove the standby wait, for the investigation
The replicas were still on Premium SSD v2. I used session-level synchronous_commit set to local to remove their required acknowledgments from the commit path while retaining local durability and streaming replication. In the benchmark, the override was supplied through the workload's PGOPTIONS, so the worker connections inherited the setting.
Patroni automatic failover was separately paused for this bounded diagnostic. It's a lab to investigate what happens, not a production setup.
The result was that the throughput didn't increase, with 54,556 NOPM, versus 55,784 in the preceding run:
| Active-client observation | Ultra, commit on
|
Ultra, commit local
|
|---|---|---|
LWLock:WALWrite |
83.18% | 87.29% |
IPC:SyncRep |
7.53% | 0 observed |
IO:DataFileRead |
2.56% | 3.93% |
This change tested whether waiting for standby acknowledgments was holding back the workload. SyncRep disappeared from the samples, but throughput stayed near 55,000 NOPM and WALWrite remained dominant. Both standbys continued streaming, with a lag.
The name local explains why. It still waits for local durable WAL flushing. The WAL synchronization counter averaged 25.44 ms in the normal-HA run and 29.55 ms in the local-commit run.
The PostgreSQL commit path flushes local WAL before waiting for synchronous replication. WALWriteLock is released before the standby-acknowledgment wait reported as IPC:SyncRep. SyncRepLock protects the shared queue and state updates and is released while the backend waits on its latch. Group commit can make several transactions durable with a single synchronization.
What faster storage changed
The initial read waits fell substantially. WALWrite remained prominent after acknowledgment waits were removed. The investigation had moved from the device-read budget to what happened inside the primary's write/flush path.
These runs reused the dataset. Cache contents, dirty pages, checkpoint phase, and vacuum state evolved between tests. What we have proved is that even with the fastest disk and the lowest replication latency, we gained only 10%, far from the 8x throughput observed in HorizonDB.
HorizonDB maintains cross-zone durability using its shared WAL service, rather than waiting for a PostgreSQL standby to flush its private WAL disk. WAL replication happens outside the SQL writer, which explains why the workload achieves higher throughput than this PostgreSQL configuration can support.
I next traced the lock holder's path to understand why local synchronization averaged tens of milliseconds while device requests completed in fractions of a millisecond. Part 3 follows that delay into the Linux writeback path.
A 9-TiB OLTP Benchmark on PostgreSQL
At Microsoft Ignite, HorizonDB was announced to be 8x faster than traditional PostgreSQL, based on an I/O-bound HammerDB TPROC-C workload that showed 427,770 NOPM without specific tuning, 8x the 52,167 NOPM measured with self-managed PostgreSQL on the same compute.
I observed the same throughput on HorizonDB and reproduced the PostgreSQL side with a tuned HA configuration: 128 GB of shared buffers on huge pages, two standbys, and synchronous commit to at least one. The result was 50,648 NOPM, very close to the published number. But the interesting question is not the number itself. What is PostgreSQL waiting for at that throughput, and what happens when that constraint is removed? This post identifies the first bottleneck. The following three remove the bottlenecks one by one until the limit moves inside PostgreSQL itself.
The workload
Here is the definition of the benchmark workload I've run:
| Setting | Tested value |
|---|---|
| HammerDB | 6.1.1, direct CLI |
| Workload | PostgreSQL TPROC-C, function-based driver |
| Dataset | 95,000 warehouses, nonpartitioned |
| Reported size after loading | About 8.92 TiB |
| Workload concurrency | 512 virtual users, plus the monitor |
| Ramp-up / measurement | 15 / 45 minutes |
| Warehouse selection | All warehouses enabled |
| Keying and thinking time | Disabled |
| Connection pooling / time profiling | Disabled |
| Driver-requested vacuum | Disabled, while server autovacuum remained enabled |
| Transaction counter | Timestamped samples every 10 seconds |
HammerDB reports new orders per minute (NOPM) and PostgreSQL transactions per minute (TPM). These are the relevant driver choices, apart from connection details:
dbset db pg
dbset bm TPC-C
diset tpcc pg_count_ware 95000
diset tpcc pg_storedprocs false
diset tpcc pg_partition false
diset tpcc pg_driver timed
diset tpcc pg_rampup 15
diset tpcc pg_duration 45
diset tpcc pg_allwarehouse true
diset tpcc pg_keyandthink false
diset tpcc pg_connect_pool false
diset tpcc pg_timeprofile false
diset tpcc pg_vacuum false
vuset vu 512
The database was loaded before the timed test (with some pain, as described in a previous blog post, because loading nine terabytes takes a long time and HammerDB can't resume a failed load).
A dataset larger than the caches
The primary had 512 GiB of RAM, including 128 GiB of shared buffers. Even the machine's total memory was a small fraction of the database size. This is how we define an I/O bound workload: the working set is larger than any cache. All warehouses were enabled, and the measured read waits and sustained physical I/O showed frequent reads beyond the caches.
The baseline machines and durability
The initial deployment used three PostgreSQL VMs across three Azure availability zones:
- Each database VM: E64ds_v5, 64 vCPUs / 512 GiB RAM.
- Each data disk: 20,480 GiB Premium SSD v2, provisioned at 80,000 IOPS / 1,200 MB/s.
- Data and WAL: the same ext4 filesystem.
- Client in the primary's zone: D64ds_v5, 64 vCPUs / 256 GiB RAM.
- PostgreSQL 17.11, Patroni 4.1.5, Ubuntu 24.04.
- Three separate etcd members for the distributed configuration store.
This configuration used synchronous_commit=on, fsync=on and full_page_writes=on. A commit required local WAL synchronization and durable WAL acknowledgment from at least one of the two cross-zone standbys. Azure's torn-write prevention requires a supported NVMe controller, kernel and filesystem, with the application issuing aligned atomic direct writes. This run used PostgreSQL 17's ordinary buffered writes, so full-page logging remained enabled for torn-page recovery.
The standby acknowledgment confirms a flush, not replay. Network transport, standby storage, and acknowledgment processing can all contribute to the wait.
Continuous WAL archiving was disabled for this lab.
The PostgreSQL profile
I used the following PostgreSQL profile for the baseline and the later tests. The commit-mode overrides are described in the posts where they apply:
| Area | Important settings |
|---|---|
| Memory |
shared_buffers=128GB, work_mem=16MB, effective_cache_size=352GB
|
| Shared-memory backing | Explicit 2-MiB huge pages, huge_pages=on
|
| Connections | max_connections=640 |
| WAL |
wal_compression=zstd, wal_buffers=64MB, max_wal_size=64GB
|
| Synchronization |
wal_sync_method=fdatasync, fsync=on, full_page_writes=on
|
| Checkpoints |
checkpoint_timeout=30min, checkpoint_completion_target=0.9
|
| Planner / I/O |
random_page_cost=1.5, effective_io_concurrency=64, jit=off
|
| Query parallelism | max_parallel_workers_per_gather=0 |
| Autovacuum | 8 workers, 10-second naptime, vacuum scale factor 0.01 |
| Autovacuum cost control | Limit 3000, delay 2 ms |
| Monitoring |
track_io_timing=on, track_wal_io_timing=on
|
effective_cache_size is a planner estimate. WAL volume can trigger checkpoints before the 30-minute timeout.
Result: 50,648 NOPM (New Orders per Minute)
The measured interval ran from November 6, 2026, 23:21:13 UTC to November 7, 00:06:15 UTC. The result message was:
TEST RESULT : System achieved 50648 NOPM from 116618 PostgreSQL TPM
To observe active backends, the collector used grouped pg_stat_activity data:
SELECT count(*) AS sessions,
wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY sessions DESC;
Repeated measured-window samples gave:
| Wait | Active-client observation share |
|---|---|
IO:DataFileRead |
79.6% |
LWLock:WALWrite |
10.8% |
IPC:SyncRep |
5.2% |
DataFileRead means PostgreSQL is waiting for a relation-file read, which may be served by the operating-system cache or the device. The guest disk counters confirmed substantial physical read activity during this run:
| Primary data-disk observation | Value |
|---|---|
| Read / write IOPS | Approximately 51,130 / 30,469 |
| Read / write bandwidth | Approximately 429 / 412 MiB/s |
| Mean read / write request completion | 7.98 / 10.95 ms |
| Guest busy time | Approximately 100% |
Delivered IOPS were near the provisioned disk budget. Together, the read waits, I/O rate, and request latency put the device-read path first in the investigation.
Next: change the storage constraint
HorizonDB combines shared buffers and local NVMe caching with a distributed page-storage fleet. Cache misses are served by this fleet, rather than the single provisioned data disk used in this test. This explains why the workload achieves higher throughput than this PostgreSQL configuration can support.
On PostgreSQL, increasing the storage budget would let me observe how the remaining waits changed. LWLock:WALWrite already accounted for 10.8% of active-client observations and could become the next bottleneck as read waits decreased.
In part 2, Faster SSDs for PostgreSQL: From Read I/O to WAL Waits
, I move the primary to Ultra SSD and then remove standby acknowledgment waiting to examine the primary's own path. In this series, the goal is to identify what limits PostgreSQL throughput as these constraints are reduced or bypassed. The investigation starts with disk reads, checks remote commit acknowledgments, follows local synchronization into the OS, and ends with shared-page coordination inside PostgreSQL.
Add your organization’s runbooks and policies using Retrieval Augmented Generation (RAG) on Amazon Aurora PostgreSQL
Build an AI-powered DBA assistant for Amazon Aurora and Amazon RDS with Kiro
How VillageSQL does documentation
Text, Filters, and Now Vectors in One Postgres Index
Connect Your AI Agents to CedarDB Cloud
When we launched CedarDB Cloud, we teased a dedicated space to give your agents safe access to your data.
Today we’re launching CedarDB Cloud’s integrated MCP server. Connect Claude, Claude Code, or ChatGPT once, and ask about your data, build dashboards, or write data-driven applications in plain language directly from your web browser.
Your agents are most impactful when they get access and can react to your real data. But that’s also where a wrong query, prompt injection, or hallucination does the most damage. So we built the integration around a simple rule:
Your agents get their own database user
… and we don’t hide that user from you.
Every request your agent sends is evaluated as if it was sent by that user. By default, this user has access to nothing. On your project’s Agents tab, you can decide per table what it may do: No access at all, read, or write.
CedarDB checks these permissions on every single query. No prompt, injected or not, can talk an agent to do something you don’t want it to do.
Need finer control? The agent user is a regular database role called cedar_mcp, so you can use
GRANT to set exactly what it may do,
and row-level security to limit which rows it sees.
For example, you can let agents file support tickets without reading anyone else’s, or only show them orders from one region:
-- Agents can add tickets, but not read existing ones
REVOKE ALL ON support_tickets FROM cedar_mcp;
GRANT INSERT ON support_tickets TO cedar_mcp;
-- Agents only see orders from the EU
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY agents_eu_only ON orders TO cedar_mcp USING (region = 'EU');
And if you want agents gone, switch them off for the project or disconnect a single client.
What Your Agents Can Do
Answer questions. Agents find their way around an unfamiliar schema and answer questions that would take you several queries.
Build dashboards. Claude turns query results into charts and interactive dashboards you can share, and they stay connected to your live data.
Write code against your schema. Claude Code writes application code, migrations, and tests that match your actual tables, and checks its queries against your data while it works.
Get started
- On your project’s Agents tab, select Switch on and choose the tables agents may access.
- Add
https://api.app.cedardb.cloud/mcpas a connector in Claude, or as an MCP server in Claude Code or ChatGPT. - Sign in with your CedarDB Cloud account, allow the connection, and start asking.
Our docs walk you through each client step by step.
New to CedarDB Cloud? Your first project is free for 30 days.
Introducing Insights Agent
November 16, 2026
PostgreSQL large objects: Best practices for Amazon Aurora and RDS
Inside Kvrocks
How Valkey/Redis data lives on RocksDB Based on my Percona Live 2026 talk “On-disk and Redis Compatible: Evaluating Kvrocks.” I will cover benchmarking Kvrocks in a separate post. We all run Valkey or Redis for well-known reasons: all the data lives in RAM, and it’s fast. Storing data in RAM is of course expensive. … Continued
The post Inside Kvrocks appeared first on Percona.
November 15, 2026
Talks: Please Implement A Quorum Database
November 13, 2026
Fix Amazon Aurora MySQL error 63955: Virtual index ID overflow
Improve RDS Proxy multiplexing for PostgreSQL, Part 1: Common causes
Improve RDS Proxy multiplexing for PostgreSQL, Part 2: Diagnose and monitor
Will your MongoDB upgrade break? Check it with UCFM
You were assigned one of the most common, yet feared, tasks in the life of a database administrator: Upgrades are needed for many reasons, whether to fix an existing bug or to keep the environment under support and eligible to receive security fixes. Either way, the task is yours. The binary swap is the easy … Continued
The post Will your MongoDB upgrade break? Check it with UCFM appeared first on Percona.
Village News: MySQL News + Events (13 November 2026)
Welcome back to Village News, our curated roundup of MySQL and database news. This week, PostgreSQL shipped updates fixing 52 security vulnerabilities and retired PostgreSQL 14, and the FOSDEM M-Ecosystem devroom opened its call for participation.
If you want to get these updates, just subscribe to the blog.
Enjoy!
Just Because You Can, Doesn’t Mean You Should: Re-evaluating Galera Node Weights
We all know that when using a cluster based on Galera (Codership) such as Percona XtraDB Cluster (PXC), it is recommended to deploy an odd number of nodes. The generic answer to this recommendation is that otherwise the risk of having an inconsistent cluster (like split-brain) is higher. (see Get started with Percona XtraDB Cluster … Continued
The post Just Because You Can, Doesn’t Mean You Should: Re-evaluating Galera Node Weights appeared first on Percona.