a curated list of database news from authoritative sources

July 31, 2026

Percona Database Performance Blog

Stored Procedures memory consumption in Percona Server for MySQL

1. What it is about This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful. Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, … Continued

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

by Bogdan Degtyariov
PlanetScale Blog

Massively parallel Postgres backups

PlanetScale backs up petabyte-scale sharded Postgres databases in hours using parallel infrastructure, object storage, and WAL replay.
by Ben Dicken

July 30, 2026

Percona Database Performance Blog

Percona Server for MongoDB 8.3 Technical Preview Is Now Available

Percona Server for MongoDB 8.3 is available today as a Technical Preview. It is not for production. It is for your lab, your staging cluster, and your benchmark harness – and for sharing with us what works and what does not. Especially if this version is your segue to leverage upcoming full-text and vector search … Continued

The post Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on Percona.

by Radoslaw Szulgo

July 29, 2026

July 28, 2026

Michael J. Swart - Database Whisperer

Partitioning a Huge Table

Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data. That’s a great idea. In fact, I’d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it’s a good candidate […]

The post Partitioning a Huge Table first appeared on Michael J. Swart.
by Michael J. Swart
AWS Database Blog - Amazon Aurora

Building scalable applications on Amazon Aurora DSQL

In this post, we provide practical guidance for designing applications that scale effectively with the Amazon Aurora DSQL distributed architecture. You will learn how to identify common patterns that limit scalability, apply proven design patterns that distribute workload efficiently, and implement transaction strategies optimized for Aurora DSQL. We cover primary key selection, schema design principles, indexing strategies, and multi-Region optimization, while maintaining full ACID (atomicity, consistency, isolation, and durability) compliance across AWS Regions.
by Tejas Dubey

July 27, 2026

Franck Pachot

WHERE $1::timestamptz IS NULL OR "timestamp" > $1

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

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

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

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

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

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

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

I gave it a try:

drop table if exists orders;

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

create index idx_orders_timestamp
  on orders("timestamp");

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

analyze orders;

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

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

Here is the execution plan:

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

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

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


set plan_cache_mode to force_generic_plan;

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

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

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

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

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

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


reset plan_cache_mode;

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

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

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

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

postgres=# set plan_cache_mode to force_generic_plan;
SET

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

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

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

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

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

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

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

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

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

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

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

by Franck Pachot

July 26, 2026

Franck Pachot

Following ROWIDs Through an Oracle Unique Index Update

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

SQL> create table franck ( val int unique );

Table created.

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

2 rows created.

SQL> select val from franck;

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

SQL> update franck set val=-val;

2 rows updated.

SQL> select val from franck;

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

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

  • Db2, SQL Server, and Oracle handle it without error.
  • PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint.
  • MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'
  • SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }
  • MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 } 
db.franck.createIndex({ val: 1 }, { unique: true });
db.franck.insertMany([ { val: -1 }, { val: 1 } ]);
db.franck.updateMany({},[ { $set: { val: {$multiply:[ "$val",-1 ]} } } ]);
MongoServerError: Plan executor error during update :: caused by :: E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

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

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

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

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

Investigation with block dumps

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

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

I create the table:

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

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

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

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

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

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

alter session set tracefile_identifier = 'franck';

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

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

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

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

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

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

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

Initial state

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

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

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

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

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

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

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

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

table value table rowid index entry index key index data
1 = c1 02 69 00 00 #0 c1 02 = 1 69 00 00
2 = c1 03 69 00 01 #1 c1 03 = 2 69 00 01
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

Running the update

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


update FRANCK set val = val + 1
;

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

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

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

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

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

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

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

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

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 01 #1 c1 03 = 2 69 00 01
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

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

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

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

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

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

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

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

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 02 #2 c1 04 = 3 69 00 02
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

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

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

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

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

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

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

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

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

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

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

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

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 01 #2 c1 04 = 3 69 00 01
4 = c1 05 69 00 03 #3 c1 05 = 4 69 00 03
5 = c1 06 69 00 04 #4 c1 06 = 5 69 00 04

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

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

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

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

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

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

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

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

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

The final state is:

table value table rowid index entry index key index data
2 = c1 03 69 00 00 #0 deleted c1 02 = 1 69 00 00
2 = c1 03 69 00 00 #1 c1 03 = 2 69 00 00
3 = c1 04 69 00 01 #2 c1 04 = 3 69 00 01
4 = c1 05 69 00 02 #3 c1 05 = 4 69 00 02
5 = c1 06 69 00 03 #4 c1 06 = 5 69 00 03
6 = c1 07 69 00 04 #5 c1 07 = 6 69 00 04

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

UNIQUE vs non-UNIQUE index

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

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

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


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

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

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

July 24, 2026

Franck Pachot

B-tree block split: what's the impact?

Everybody knows that B-tree indexes provide logarithmic lookups, but what does that actually mean in PostgreSQL?
How many index entries fit in a page? When does a page split? When does the tree gain another level? How much additional WAL is generated? How many more buffers are touched during an index lookup?
PostgreSQL exposes enough information to observe all of this directly. One command and two extensions included in PostgreSQL contrib are particularly useful:

  • EXPLAIN (analyze, buffers, wal) for observing the buffer activity and WAL generated by DML on indexes
  • pageinspect for inspecting internal page structures
  • pgstattuple for gathering statistics about index pages

By inserting rows one at a time and collecting B-tree statistics after each insert, we can watch the index grow from a single page to a multi-level B-tree and correlate page splits with WAL generation and buffer accesses.

Inspecting the B-Tree Metadata with bt_metap()

The pageinspect extension is a low-level debugging tool that exposes information stored in table and index pages.
Block 0 of every B-tree index is the metapage. It stores information about the root page, the current tree depth, and other metadata.

postgres=# create extension if not exists pageinspect;
CREATE EXTENSION

postgres=# \x
Expanded display is on.

postgres=# select * from bt_metap('demo_pkey');
-[ RECORD 1 ]-------------+-------
magic                     | 340322
version                   | 4
root                      | 53194
level                     | 3
fastroot                  | 53194
fastlevel                 | 3
last_cleanup_num_delpages | 0
last_cleanup_num_tuples   | -1
allequalimage             | t

postgres=# \x
Expanded display is off.

The values come directly from BTMetaPageData:

  • magic: A constant (340322) used to verify the file is a B-tree.
  • version: The B-tree layout version.
  • root: The block number of the "true" root. It can change when the root block is split to add a level.
  • level: The distance from the true root to leaf pages
  • fastroot and fastlevel: the block number and level of the effective root from which B-tree searches start. They can let searches skip upper levels that contain only a single page (more info in the [README].(https://github.com/postgres/postgres/blob/REL_19_BETA2/src/backend/access/nbtree/README#L362))
  • last_cleanup_num_delpages: Pages deleted during the last VACUUM.
  • allequalimage: Optimization flag indicating if the index can use "deduplication" safely.

B-tree searches start at fastroot rather than necessarily at the true root. Starting from a page at fastlevel, PostgreSQL follows downlinks until it reaches the leaf page containing the relevant key range.

Collecting Statistics With pgstatindex()

Where pageinspect exposes internal metadata, pgstattuple can scan the index and provide aggregated statistics related to bloat and fragmentation.

postgres=# create extension if not exists pgstattuple;
CREATE EXTENSION

postgres=# \x
Expanded display is on.

postgres=# select * from pgstatindex('demo_pkey');
-[ RECORD 1 ]------+----------
version            | 4
tree_level         | 3
index_size         | 586776576
root_block_no      | 53194
internal_pages     | 351
leaf_pages         | 71276
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 69.04
leaf_fragmentation | 49.84

postgres=# \x
Expanded display is off.

This function returns:

  • tree_level: the level of the root page, with leaf pages at level 0. It is therefore also the number of downward links followed from the root to reach a leaf page.
  • index_size: Total size in bytes calculated from the total number of pages
  • internal_pages: counts of non-leaf pages, so the branches including the root, found during the scan.
  • leaf_pages: counts the leaf pages found during the scan, so the ones storing the index entries with the heap tuple identifier of the row (TID)
  • empty_pages: half-dead pages, which are empty but still present in the tree
  • deleted_pages: pages deleted from the tree and available for reuse
  • avg_leaf_density: the average percentage of usable leaf-page space occupied by index tuples. A low value can indicate unused space or bloat, although page splits also naturally leave free space.
  • leaf_fragmentation: Measures how many leaf pages are physically out of order.

Test setup

I've run the following to insert rows and display the B-tree statistics after each insert:

-- create a table with an index (the primary key)
drop table demo;
create table demo ( id uuid primary key);

-- get B-tree statistics
create extension if not exists pgstattuple;
select * from pgstatindex('demo_pkey');
create extension if not exists pageinspect;
select * from bt_metap('demo_pkey');

-- insert a row with random value for the indexed column,
-- display the index statistics and repeat
\x
explain ( analyze, buffers, wal, costs off )
insert into demo (id) values ( uuidv4() )
\;
select * from pgstatindex('demo_pkey')
\watch i=0.01

I used the following AWK script to parse the result, display one line per row inserted, and flag when the WAL size generated by the insert is different from the preceding:

awk -F"|" '
/Insert/{ rows=rows + 1 }
/WAL/{ wal = $2 }
/Buffer/{ buf = $2 }
$1~/^[a-z_]+ */ { sub(/ *$/,"",$1 ) ; val[$1]=$2 }
/^$/ && val["tree_level"]!="" {
 key=wal " " val["tree_level"]
 if ( key != prev ) {pre="+"} else {pre=" "}
 printf "%1s %8d rows, %4d levels, %4d internal, %6d leaf, %6.2f MB, %3d%%, last insert: %-28s %-50s\n", pre, rows, val["tree_level"], val["internal_pages"], val["leaf_pages"], val["index_size"] / 1024 / 1024, val["avg_leaf_density"], wal, buf
 val["tree_level"]=""
 prev=key
}
' | grep -C3 '^+'

The First Split

In this test, the first 291 index entries fit in a single page - 0 internal pages, only one leaf, which is also the root:

+        1 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   0%, last insert:    WAL: records=3 bytes=233     Buffers: shared hit=4 dirtied=3 written=2
+        2 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   0%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
         3 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
         4 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
         5 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,   1%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
--
       289 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
       290 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2
       291 rows,    0 levels,    0 internal,      1 leaf,   0.02 MB,  99%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=2

In this test, most inserts generate two WAL records: one for the heap insertion and one for the index insertion. We see the average leaf density increase steadily to 99% as the root page fills.

The page is now full and must be split to insert the 292nd index entry. PostgreSQL creates a new root page above two leaf pages, resulting in a B-tree with one level:

+      292 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=3 bytes=1057    Buffers: shared hit=3 dirtied=2 written=2
+      293 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=5
       294 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       295 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  50%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       296 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  51%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
--
       321 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       322 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       323 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3

The split generates three WAL records instead of two. The next insert touches five shared buffers, while subsequent inserts stabilize at three: the heap page, the root page, and one leaf page.

This continues until those pages are full and must be split, with one more leaf page and one more WAL record for the one that had to split:

       323 rows,    1 levels,    1 internal,      2 leaf,   0.03 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      324 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=3 bytes=3799    Buffers: shared hit=5 dirtied=1 written=1
+      325 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       326 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       327 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       328 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  37%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
...
       645 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       646 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       647 rows,    1 levels,    1 internal,      3 leaf,   0.04 MB,  74%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      648 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  55%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=5 dirtied=1 written=1
+      649 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  55%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       650 rows,    1 levels,    1 internal,      4 leaf,   0.05 MB,  56%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+      651 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=5 dirtied=1 written=1
+      652 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       653 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       654 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
       655 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  45%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
...
      1256 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1257 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1258 rows,    1 levels,    1 internal,      5 leaf,   0.05 MB,  86%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+     1259 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=3 bytes=3799    Buffers: shared hit=5 dirtied=1 written=1
+     1260 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1261 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1262 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
      1263 rows,    1 levels,    1 internal,      6 leaf,   0.06 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3

This pattern continues until, in this test, the root page is full of downlinks to 291 leaf pages, so that it must be split into two internal pages, with a new root above, increasing the level to 2:

     61343 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
     61344 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
     61345 rows,    1 levels,    1 internal,    291 leaf,   2.29 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=3
+    61346 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=4 bytes=6017    Buffers: shared hit=6 dirtied=4 written=3
+    61347 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=6
     61348 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61349 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61350 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
...
     61408 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61409 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61410 rows,    2 levels,    3 internal,    292 leaf,   2.31 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    61411 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=6 dirtied=1 written=1
+    61412 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61413 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61414 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61415 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
...
     61543 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61544 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61545 rows,    2 levels,    3 internal,    293 leaf,   2.32 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    61546 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=3 bytes=3775    Buffers: shared hit=6 dirtied=1 written=1
+    61547 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61548 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61549 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     61550 rows,    2 levels,    3 internal,    294 leaf,   2.33 MB,  72%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4

The split that increased the level wrote 4 WAL records. The next inserts then filled one page, writing 2 WAL records (leaf + heap). In this test, inserts now touch four shared buffers instead of three because the index has one additional level. Leaf splits that do not have to split an internal page write 3 WAL records.

At this point, the tree contains three internal pages: one root page at level 2 and two branch pages at level 1. The leaf pages are at level 0.

When a branch page at level 1 becomes full, it is split. This does not increase the tree level as long as the new downlink can be inserted into the root page at level 2:

     80439 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80440 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80441 rows,    2 levels,    3 internal,    398 leaf,   3.14 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
+    80442 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=4 bytes=7415    Buffers: shared hit=8 dirtied=2 written=2
+    80443 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80444 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80445 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4
     80446 rows,    2 levels,    4 internal,    399 leaf,   3.16 MB,  69%, last insert:    WAL: records=2 bytes=143     Buffers: shared hit=4

When there are so many branch pages at level 1 that their downlinks fill the root at level 2, the root must split. This happened in my example when the index reached 417.92 MB with 53193 leaf pages and 300 internal pages.

This is when I ran bt_metap() and pgstatindex() and obtained the result shown at the beginning of this post, with a 3-level B-tree.

Conclusion

B-tree indexes grow through a series of page splits. In this test, most inserts are inexpensive, touching one leaf page and generating two WAL records. Occasionally, a leaf page becomes full, triggering a split and additional WAL. More rarely, a branch page splits. Rarest of all are root splits, which add another level to the tree.

This experiment illustrates the write amplification inherent in B-trees, but also shows why they scale so well. The first additional level appeared after only a few hundred rows. The next required more than sixty thousand rows. The third required an index over 400 MB in size.

Page splits occur often enough to keep the tree balanced, while increases in tree level happen much more gradually. Even large PostgreSQL indexes therefore remain shallow. Their internal pages are also likely to remain cached because there are relatively few of them. For indexed queries that return table rows, the more significant I/O cost is often fetching heap pages rather than traversing the B-tree itself.

by Franck Pachot
Percona Database Performance Blog

Alert on CVEs in Your Percona Tools for MongoDB on Day One

TL;DR: Starting with PBM 2.15.0 and PCSM 0.9.0, every release artifact – binary tarballs, RPM and DEB packages, and Docker images – ships a CycloneDX 1.6 Software Bill of Materials in JSON. Scan it with Trivy, Grype, or any CycloneDX-compatible tool. For Docker images, the fastest path is a Trivy image –sbom-sources oci <image>. There … Continued

The post Alert on CVEs in Your Percona Tools for MongoDB on Day One appeared first on Percona.

by Oleksandr Miroshnychenko
Murat Demirbas

Aurora DSQL: Scalable, Multi-Region OLTP

The Aurora DSQL paper finally dropped. Reading it yesterday was an interesting experience, because I spent two years (2022-23) working with the AWS team that designed and built Aurora DSQL. Since I have been very familiar with the architecture, the paper's overview description of the system didn't excite me much. And if I am being honest, reading the paper also felt a bit dry, most likely because I am not doing the usual extra thinking to explore/understand the ideas in the paper. But taking a step back today, and leaving my subjective experience aside, I will try to elaborate on how the DSQL architecture is actually built on a set of aggressive and opinionated engineering bets. 

If I had to explain this architecture in a single sentence (just as I used to do for other teams at AWS) it would be this: We took a traditional monolithic database and blew out every single component into an independent, horizontally scalable service.


Exploding the Monolith

DSQL divides the database into the following specialized services:

  • Query Processors (QP): Stateless virtual machines that run a custom PostgreSQL engine to parse queries and buffer writes locally.
  • Storage Nodes: Sharded nodes that hold the data and use multiversion concurrency control (MVCC) to serve historical data to QP instantly.
  • Adjudicators: The conflict-resolution layer that checks if a transaction handed of to them by QP is safe to commit.
  • Journals: A highly available replication log that durably saves transactions across zones or regions. (Good for short term durability before this reaches the storage nodes. See my MemoryDB review.) 
  • Crossbars: The routing layer that reads the updates from the Journals and sends changes to the right Storage Nodes.


The Big Architectural Bets in DSQL Design

Well, in addition to fully committing to the disaggregation principle, here are the other big bets in DSQL design.

The Synchronized Clock Bet: To get reads without coordination, DSQL relies entirely on highly synchronized physical clocks, in this case AWS TimeSync. A QP just checks its local clock and asks storage for data from that exact microsecond.

No Pessimistic Locking: Optimistic Concurrency Control (OCC) may lead to high abort rates for databases, but DSQL makes it work by pairing it with MVCC under Snapshot Isolation. Because readers look at a snapshot of the past, read-write conflicts are impossible. Awesome, but what about write-skew? When needed, customers should just use FOR UPDATE, and also design their schemas to force write-write conflicts for business logic violations.

Eventual Consistency is Dead: DSQL provides strong consistency (linearizability), arguing that developers simply cannot write correct business logic on eventually consistent systems. It’s a subtle point, but linearizability (a guarantee about single-object real-time operations) and snapshot isolation (a guarantee about multi-object transaction visibility) control different things, as Jepsen's consistency models outline. DSQL offers a consistent snapshot for your snapshot-isolated transactions, and that individual key operations strictly respect real-time ordering.

Forcing Guardrails: DSQL hard-caps transactions at 3,000 rows and 10MiB. The paper cites Little's Law to justify this, essentially forcing users to accept smaller transactions in exchange for highly predictable stable tail latency.

Linearized 2PC: For transactions that span multiple Adjudicators, traditional Two-Phase Commit (2PC) is too slow over wide area networks as it requires 2RTTs. DSQL uses a "Warp-inspired" trick where Adjudicators vote, but only the leader writes the final commit to its single Journal. This avoids coordinating multiple logs.


The Payoff

Independent Scalability: Compute, commit logic, and storage are completely separated. If you need more read capacity, you add storage nodes. If you have a spike in connections, the system spins up more QPs. You can also tune/optimize them separately, for example, potentially reconfigure adjudicator-range placement based on access patterns.

0-RTT Consistent Reads: Because the QP assigns a local timestamp and storage handles the rest, reading data requires zero coordination with a leader. It is almost as fast as your network latency to the storage node. This is a big win because in OLTP SQL, reads are significantly more common than writes. Even most writes (like UPDATES or INSERTS with unique indexes) are actually reads first.

1-RTT (or 1.5 RTT) Commits: Whether you write one row or a hundred, coordination only happens once at commit time, costing just 1 RTT (or slightly more for a multi-shard commit).

No "Slow Lock Holder" Problem: Because there are no pessimistic locks, a developer going to lunch with an open transaction terminal (exact quote from the paper) cannot bring down the database. Readers never block writers, and writers never block readers.


While it is hard to critique my old team, this won't be a true Murat Buffalo (now Bay Area?) review without pointing out the tradeoffs and shortcomings in the paper. Because of the long read-modify-commit duration during a transaction, DSQL may be prone to write-write conflicts on hot keys. This limits how many back-to-back operations you can squeeze into a single row (which is bad application design anyway). Under heavy contention, transactions will abort, whereas a traditional database may have queued them up. If two regions write to the same key, OCC will abort one of them, and unfortunately this conflict is only detected at commit time, meaning you pay the WAN network latency penalty before finding out you have to retry.

While the paper couldn't provide extensive evaluation, quantitative data on user adoption or business payoff, it does provide a candid Lessons Learned section which talks about some friction with respect to Foreign Key Constraints and high-locality sequences.


Building at Scale

I have one final takeaway that is somewhat counterintuitive: building a completely novel global production database didn't actually feel like as much effort as it should have.

Yes, it was a lot of work, but the execution felt surprisingly smooth after the team got going. I attribute this to two things: a great upfront design, and a deliberate choice to avoid reinventing the wheel. Rather than building everything from scratch, the team used the PostgreSQL engine for SQL parsing, execution, and the client protocol, while discarding its local storage and transaction processing layers. We didn't build a new replication log; we used AWS's existing internal Journal service. We were also equipped with hard-earned lessons from preceding AWS database projects, like JournalDB and the unfortunately named QLDB (Quantum Ledger Database).  Beyond the tooling, the team dynamics were excellent. Marc Brooker is technically brilliant, and he did a masterful job leading a talented team of principal engineers. Our weekly whiteboard thinking sessions were a lot of fun. Because the foundational architecture was so well-designed, the actual development felt significantly easier than at least what I would expect for a product of this magnitude. (But then again, I wasn't with the team for the last year of the project!)

by Murat (noreply@blogger.com)
PlanetScale Blog

Postgres backups under the hood

With backups being such a vital part of keeping your data safe, how do they actually work?
by Josh Brown