a curated list of database news from authoritative sources

November 20, 2026

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

Amazon Aurora PostgreSQL express configuration now supports VPC networking. You can move an existing cluster into your VPC with a single modify operation, keeping its data and endpoint, from the console, the AWS CLI, or an AI agent. This post shows how to move and verify a cluster, and how to let an agent do it with IAM guardrails.

Building a no-code AI app on Amazon Aurora DSQL with agent skills, plugins, and MCP

With AI coding agents and Amazon Aurora DSQL, you can turn data from existing business systems into working applications. This post shows how Kiro and the Aurora DSQL power take enterprise data extracts to a running catalog and inventory application through four prompts, covering schema design, data loading, IAM authentication, and concurrency handling.

Performance impact of excessive logging in Amazon Aurora PostgreSQL

Excessive database and audit logging can quietly become a performance bottleneck in Amazon Aurora and Amazon RDS for PostgreSQL. Using a reproducible pgbench example, this post shows when logging overhead is negligible and when it cuts throughput by about three times, how to spot it with wait events and log file evidence, and how to reduce it.

Announcing VillageSQL Server 0.0.8

VillageSQL Server 0.0.8 is now available. This release is about adding functionality to use MySQL extensions from applications.

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

Three fixes Insights Agent made to our own Rails API: a reordered feature flag check, an index that made a query slower, and an index that took a query from seconds to milliseconds.

November 18, 2026

How to use Amazon Aurora DSQL audit logs

Amazon Aurora DSQL audit logging records the SQL statements that run in your database and delivers them to Amazon S3. This post shows how to configure audit logging to meet regulatory requirements, explains what each recorded field proves in an audit, and shows how to query the logs in Amazon S3 with Amazon Athena.

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. 


November 17, 2026

Use query statistics to find slow and expensive queries in Aurora DSQL

When a code change slows your application, Amazon Aurora DSQL query statistics help you find the slow and expensive queries behind it, down to the exact SQL statement. This post walks through a performance investigation that pinpoints a missing index, fixes it with an asynchronous index build, and measures the improvement in latency and DPU cost.

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.

Build an AI-powered DBA assistant for Amazon Aurora and Amazon RDS with Kiro

Learn how to build a DBA assistant with Kiro that investigates and resolves Amazon Aurora PostgreSQL performance incidents through natural language. Using two MCP servers, the agent reads Amazon CloudWatch metrics and Database Insights, runs diagnostic queries, and surfaces fixes you apply through your change management process.

How VillageSQL does documentation

How VillageSQL writes its docs: a public repo, an AI agent that runs every example it writes on a live server, and a person who approves.

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

  1. On your project’s Agents tab, select Switch on and choose the tables agents may access.
  2. Add https://api.app.cedardb.cloud/mcp as a connector in Claude, or as an MCP server in Claude Code or ChatGPT.
  3. 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

Insights Agent connect to your codebase to turn slow queries and schema recommendations into pull requests.

November 16, 2026

PostgreSQL large objects: Best practices for Amazon Aurora and RDS

PostgreSQL large objects live outside your tables and are referenced only by an OID, so autovacuum never reclaims the orphans. This post shows how to monitor orphaned large objects on Amazon Aurora and Amazon RDS for PostgreSQL, run vacuumlo safely, set up lo_manage triggers, prevent OID exhaustion, and prepare for major version upgrades.

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

November 13, 2026

Fix Amazon Aurora MySQL error 63955: Virtual index ID overflow

An Amazon Aurora MySQL cluster can exhaust its virtual index ID limit, after which any CREATE INDEX that includes a virtual column fails with error 63955 until you rebuild the databases. This post shows how to recognize the error, why backups cannot clear it, and how to run a logical dump-and-restore migration with monitoring to catch it next time.

Improve RDS Proxy multiplexing for PostgreSQL, Part 1: Common causes

RDS Proxy multiplexes many client connections across a small pool of database connections, but connection pinning, pool fragmentation, and uncommitted transactions can silently disable that benefit for PostgreSQL workloads. Part 1 shows how to recognize each condition with Amazon CloudWatch metrics and the fix that restores multiplexing.

Improve RDS Proxy multiplexing for PostgreSQL, Part 2: Diagnose and monitor

Metrics alone can't always pinpoint what is reducing RDS Proxy multiplexing for PostgreSQL. Part 2 shows how to use Amazon CloudWatch Logs Insights with enhanced proxy logs to find the root cause, what healthy multiplexing looks like in metrics and logs, and how to set up CloudWatch alarms that catch regression.

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.