I'll be showcasing some exciting PostgreSQL work, featuring contributions from Microsoft engineers at pgconf.eu: Postgres 19, 20, & Beyond: Live Demos of New Features & Tools. During the demos, we'll explore many execution plans, including a new PostgreSQL 19 feature—the IO option for EXPLAIN.

EXPLAIN (ANALYZE) executes the query and reports runtime statistics. BUFFERS shows logical buffer activity: cache hits and reads. IO goes further, reporting how the read stream behaved: how far ahead PostgreSQL was able to prefetch, how many physical I/O requests were issued, their sizes, the level of concurrency, and how often the consumer had to wait.

For a long time, PostgreSQL relied primarily on the operating system and filesystem for read-ahead. PostgreSQL 17 introduced read streams, giving the executor its own streaming read-ahead mechanism. PostgreSQL 18 added asynchronous I/O infrastructure, including io_method implementations such as worker-based AIO and Linux io_uring. PostgreSQL 19 exposes this activity in EXPLAIN (ANALYZE, IO).

When PostgreSQL knows it will read multiple table blocks, for example during a Seq Scan, Bitmap Heap Scan, or Tid Range Scan, it can use a read stream. The stream looks ahead, combines nearby blocks into larger I/O requests, and keeps buffers pinned ahead of the consumer. That distance is reported in the Prefetch line.

Every time a buffer is handed to the scan node, whether it was already cached or had to be read from storage, PostgreSQL samples the current prefetch depth. This is why the Prefetch numbers are scoped to buffer consumption, not just to physical reads.

Physical I/O requests are reported separately on the I/O line. PostgreSQL records the number of I/O requests issued, the average number of blocks read per request, the number of other I/Os already in progress when a request was submitted, and how often the consumer encountered an I/O that had not yet completed.

Here is an example:

postgres=# explain (analyze, buffers, IO, verbose off, costs off)
postgres-# select * from demo
 ;
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on demo (actual time=0.703..1096.397 rows=1000000.00 loops=1)
   Prefetch: avg=34.56 max=68 capacity=71
   I/O: count=1825 waits=7 size=15.97 in-progress=3.70
   Buffers: shared hit=16310 read=29145
 Planning Time: 0.068 ms
 Execution Time: 1924.887 ms
(6 rows)

The scan touched 16310 + 29145 = 45,455 shared buffers. With the default 8 KB block size, that is about 355 MiB of table data. Of those buffers, 16310 were already in shared buffers, and 29145 had to be read.

The Prefetch line indicates how far ahead the read stream was able to stay:

• capacity=71 is the maximum number of buffers this stream was allowed to pin ahead of the consumer.
• max=68 indicates the stream reached a peak depth of 68 pinned buffers, close to the limit.
• avg=34.56 indicates that, across all 45,455 buffer hand-offs to the scan node, the stream had about 35 buffers pinned ahead on average.

The I/O line reports the physical reads:

• count=1825 indicates that PostgreSQL issued 1,825 distinct I/O requests.
• size=15.97 indicates that each request read about 16 blocks on average: 29145 / 1825.
• in-progress=3.70 indicates that, when a new I/O was submitted, about 3.7 other I/Os were already in progress on average.
• waits=7 indicates that only 7 of the 1,825 I/O requests had not completed by the time the consumer reached their first buffer.

That last point is important: waits means that, of the 1,825 I/O requests, only 7 were still unfinished when the scan needed them. The other 1,818 completed early enough that prefetching and asynchronous execution hid their latency.

Let's analyze these further. Little's Law states that the average number of items in a stable system (L) equals the product of the average arrival rate (λ) and the average time each item spends in the system (W).

Two counters are directly reported: in-progress=3.70, indicating the average number of concurrent I/O requests (L), and count=1825, representing completed requests over the total scan time of 1096.397 ms. Assuming I/O requests were issued and completed steadily during this period, the request completion rate λ = count / time = 1825 / 1.096397 s equals approximately 1664.5 requests per second. Applying Little's Law (L = λ · W), we find the average time a request spends in the system from submission to completion as W = L / λ = 3.70 / 1664.5, which is roughly 0.002223 seconds or 2.22 milliseconds.

This is a derived average, not an actual reported value. Using it as a per-request estimate, the reported waits=7—the number of requests the consumer reached before completing—sets an upper limit on total blocked time of 7 × 2.22 ms = 15.56 ms. This represents approximately 15.56 / 1096.397 = 1.42% of the scan's total elapsed time. Keep in mind, this is an upper bound, not an exact measurement, because waits counts events rather than durations. A wait only adds to the remaining latency of a request already in progress, which is at most its average latency of 2.22 ms.

Don't mistake this 1.42% for the total I/O. It's a Seq Scan with most of the work involving I/O, but this isn't visible in the foreground process because most I/O occurred in the background through a read stream that held about 35 buffers pinned ahead on average (avg=34.56). Multiple requests were usually active at once (in-progress=3.70). The I/O operations and row processing largely overlapped for nearly the entire 1096.397 ms. The 1.42% is an upper estimate—based on waits=7 and average I/O latency, not a direct measurement—representing the brief moments when the overlap broke: when the scan ran out of its prefetched buffers and had to pause for a specific request to finish.

In one sentence, this EXPLAIN output tells us that PostgreSQL kept roughly 35 buffers prefetched ahead of the sequential scan, combined 29,145 block reads into 1,825 larger I/O requests of about 16 blocks each, maintained about four concurrent reads on average, and stalled for at most ~1.42% of the scan's time waiting on the 7 I/O requests that weren't ready in time.

Welcome back. This issue covers five weeks rather than the usual one — the gap since the August issue took in Percona Live Amsterdam, the launch of the OurSQL Foundation, and the run-up to the MySQL Galera Cluster end of life on 30 September.

If you want to get

My literature search for recent work on composing self-stabilizing systems didn't yield anything useful. The layered stabilization idea was already in place by the early 2000s, and nothing fundamental seems to have been added since. Frustrating.

So I decided to attack the problem using the concrete example I have. I had composed a rely-guarantee TLA+ model of a retry storm as two components with contracts. That model reproduces metastable failure because the composition that workded from good states failed to work when a large shock removes the base case that let the two conditions hold each other up.

Searching for  rely-guarantee based composition from every state, turned up a 2017 control theory paper by Kim, Arcak and Seshia, "A Small Gain Theorem for Parametric Assume-Guarantee Contracts". This paper does roughly what I want: discharging circular reasoning between two components without layering or blocking. But it comes with some serious limitations. In their formalism, a component is an input-output relation on signals, and contracts relate an input bound to an output bound. This is a memoryless view of a component, so it is not possible to express backlog accumulating from previous rounds. That rules out queues, among other useful distributed systems concepts. It also has no connection to stabilization. The paper does not talk about a variant/potential function and convergence reasoning. But there are still pieces there worth stealing toward a compositional theory of self-stabilization and metastability. Below I try to work this out... somewhat unsuccessfully.

Understanding Parametric Assume-Guarantee Contracts

In our original model, the retrier's guarantee was conditional and partial: "if the queue is under 6, I send no retries". This contract does not say anything about when the queue is at 18. Since the "if" condition fails, the promise is vacuously satisfied and the component owes us nothing.

The parametric assume-guarantee paper's big idea is to write a whole family of contracts that cover everywhere, rather than writing one promise with a precondition.

Tired: If the queue is under 6, no retries.

Wired: Whatever the queue length $L$ turns out to be, I send at most $\lambda(L)$ retries.

Recall that my constants from the model are $S=3$ units of server capacity per round, $A_{max}=2$ maximum fresh arrivals per round, and a retry timeout of $T=2$ rounds, which makes the latency threshold $S \cdot T = 6$. This makes $\lambda(L) = \lfloor (L-6)/2 \rfloor$, which gives us:

The old contract is still in there, as the top row: $\lambda(6)=0$ says "queue under 6 means at most zero retries". Although the old contract is invalid at queue length of 18, under the parametrized assume-guarantee approach every row of the table gets a promise. So we get a bundle of ordinary contracts, one per badness level $p$:

$$\varphi_a = \bigvee_p \psi_a(p)$$

$$\varphi_g = \bigwedge_p \left( \psi_a(p) \Rightarrow \psi_g(\lambda(p)) \right)$$

The assumption side, $\varphi_a$, is a disjunction because the levels are alternatives. The environment will be at one of them, whichever one it happens to be. "Queue at most 6, or at most 8, or at most 10, or..." is satisfied by essentially any environment, so there is no envelope left to fall outside of.

The guarantee side, $\varphi_g$, is a conjunction over the same levels. Since the obligations are cumulative, we owe all of them at once. Rows whose condition is false cost us nothing, and since the levels are nested, several apply at once and the tightest wins. When queue is at 7, "at most 8" applies, and the component owes us at most 1 retry; "at most 10" also applies and it also owes us at most 2, but the first case already implies that. Monotonicity becomes key here.

Deriving the Small Gain Rule

What is the rule that says when such a loop settles. The paper calls this the small gain theorem. Let me start by explaining the intuition.

You have seen this happen, right? When a microphone gets in front of a speaker, the mic picks up sound, and the amp boosts it. The speaker plays this back, which the mic picks it up again. Each lap around that loop multiplies the sound, and you hear a high pitched squeal.

To quantify this we need one number per component: how much badness out per unit of badness in. That is the slope of the component's response function, and control theory calls it the component's gain.

When we chain the two components, and feed a nudge $x$ into the first, slope $g_1$, and $g_1 x$ comes out. When we feed that into the second, slope $g_2$, and $g_2 g_1 x$ comes out. One lap has multiplied the nudge by $g_1 g_2$. After $k$ laps the nudge is $(g_1 g_2)^k$ times its original size. If the product is under one, the laps shrink geometrically and the loop settles. If it is over one, it diverges. The proof is from the geometric series.

The small gain theorem is so elegant, it gives us a global result that covers every starting state at once. But the small gain setup is limited. In our case, two things stop us from using this shortcut.

First, this needs straight lines. Our retrier has a straight slope $1/2$, but our server does not. Its share of service goes as $f/(f+d)$, so its slope depends on where the queues are. So, there is no single number to multiply.

Second, and worse, the shortcut assumes badness is one number. Our system has two queues that behave differently: fresh work $q_f$, and duplicates $q_d$. A bound on one is not a bound on the other. So a lap around our loop takes a pair of numbers to a pair of numbers.

Underneath both limitations lies the memoryless view of a component I complained about in the introduction. In this setup a gain is an input-output relation: it says how much of what arrives is passed along. There is no slot in it for how much of my own backlog is still sitting here from previous rounds. Queues are mostly backlog, and that is what the next section is about.

Dealing with Two Queues and Four Slopes

Let's track both queues. We can write the round as a rule on the pair (fresh queue $f$, duplicate queue $d$) by applying arrivals, applying retries, applying the proportional service split to figure out the next pair. We then ask whether any pair maps to itself.

One pair does: $(f,d) = (8,4)$. Here the total queue is 12, so the three units of capacity split two to fresh and one to duplicates. Two fresh served cancels the two arrivals exactly. The retry rate is $(8-6)/2 = 1$, and one duplicate served cancels that exactly. So next rounds, the queues are still in balance.

The question is what happens if we start near this balance point. Start at $(9,4)$ and does the system fall back, or run away? To answer we need to know how a small nudge propagates.

I will save you the calculation but here is the table.

Let's start with the diagonal. Here we reason about what happens if we add one item to a queue, how much bigger does that queue get next round? For this reasoning, only the server is involved, and we get $11/12$ and $5/6$, which are the fraction of that item still sitting there next round.

Now, let's consider the off-diagonal, which is about cross-queue interation. If you add one item to this queue, how much bump would it cause for the other queue next round? The server is involved in this calculation because what one queue takes the other loses due to the split of work at the server. The retrier is also involved because its pending count tracks the fresh queue, and the retries it sends land in the duplicate queue. The number $7/12$ consists of $1/2$ from the retrier (with $T=2$, one extra item in the fresh queue eventually produces one extra retry, but spread over two rounds) plus $1/12$ from the server. The other off-diagonal number $1/6$ is from the server alone, due to the extra duplicate diluting fresh's share of the S=3 capacity split.

Tracking down the Instability

The paper's small gain theorem suggests us to multiply the gains around the loop and check that the product is under one.

Let's choose the two entries on the off-diagonal of the table. These say that a longer fresh queue makes more duplicates ($7/12$, the effect of $f$ on next $d$) and more duplicates starve the fresh service ($1/6$, the effect of $d$ on next $f$). Since these involve the interaction of the two components, let's call that coupling.  When we multiply them, we get $\frac{7}{12} \cdot \frac{1}{6} = \frac{7}{72} \approx 0.1$. That says, a nudge sent once around the loop returns a tenth of its size. This says the system is stable with a factor of ten to spare. But it is wrong, because it reads only two of the four numbers in that table.

The two numbers on the diagonal, $11/12$ and $5/6$, describe the other side of the coin: How much of each queue is still there next round, with the other queue playing no part. Recall that both of these come from the server alone. Let's call this one memory. The small gain theorem reads only the coupling and ignores the memory.

When we take the memory into account, the real per-round multiplier becomes $1.19$, which is above one, so almost any disturbance grows rather than quiesces.

We get that number through standard linear stability analysis. We look for a nudge $(x,y)$ that the table just scales by some factor $r$. With entries $a,c$ on top and $b,d$ below, that means $ax+cy=rx$ and $bx+dy=ry$. When we solve each for $y/x$, set them equal, and we get the table's characteristic polynomial: $$r^2 - (a+d)\,r + (ad - bc) = 0$$

The two roots of a quadratic add up to the negative of the middle coefficient and multiply to the constant term. So our two factors (eigenvalues) add to $a+d$ (trace) and multiply to $ad-bc$ (determinant).

The trace comes from the diagonal only: $11/12 + 5/6 = 1.75$. Coupling shows up in the determinant as a subtraction: $0.76 - 0.10 = 0.67$.

If we drop the coupling, the determinant returns to $0.76$ with the trace unchanged, giving us $0.92$ and $0.83$, both under one. If we restore the coupling, the determinant falls to $0.67$, which splits the same sum into $1.19$ and $0.56$, where one factor is above 1, spelling trouble. 

This arithmetic also explains the two known fixes. A retry budget zeroes the $7/12$ entry; fresh-first service zeroes the $1/6$. Either way nothing is subtracted from the determinant and the factors fall back to $0.92$ and $0.83$. Each queue still carries over more than 80% of itself every round, but with no coupling to feed that carryover the backlog drains 8% a round instead of growing 19%.

Capping the queues is another version of the same move. A cap of $M$ on the fresh queue means the retrier can never emit more than $(M-6)/T$ retries, which is a hard ceiling on the $7/12$ coupling entry. This is a form of retry budget again. The backlog drains only if the ceiling sits under the headroom: at $M=7$ the cap allows zero retries and every start drains, while at $M=8$ it allows one retry, and other attractors start appearing in the space. Doing a simulation sweep shows that above $M=8$, the cap bounds the divergence but does not prevent the failure. Instead of growing without limit, the queues climb to the ceiling and stay. At $M=40$ the system parks at $(39,38)$: of the three units  served per round, one does useful work and two go to duplicates of requests already in flight. That is the very definition of metastability.

The Upshot

The parametric assume-guarantee paper gave me a better way to write a component's promise as a family of contracts indexed by how bad the environment is. But it did not give me a recipe for composition for practical systems. Since the paper's model is memoryless and uses one scalar, it didn't apply to our example. I got the four slopes by writing out how both queues evolve together, which meant abandoning composition for that step. However, it's worth noting that every term in that table comes from a single component, and the $7/12$ is just the retrier's $1/2$ added to the server's $1/12$. So there may be a way to work composition out here in the future.

TL;DR Percona Server for MySQL 9.7.2-2 now supports DISTANCE() for vector similarity scoring directly in SQL (COSINE, EUCLIDEAN, MANHATTAN, DOT metrics). This is the compute primitive you need to rank or filter embeddings by similarity directly in SQL. ANN indexing (e.g. HNSW, IVF) is the next milestone for fast large-scale similarity search; and this function provides … Continued

The post DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7 appeared first on Percona.

TL;DR: We ported the original 1993 Doom’s game logic and renderer to SQL and ran it inside a database. The game loop runs at the original 35 FPS, while the renderer produces the complete 320x200 frame buffer at up to 60 Hz on my Laptop. Python only handles timing, reads the keyboard, and displays the bitmap it gets back. Multiplayer also works.

You can play it right now Deathmatch, four slots, first come first served.

SQLDoom on 🇪🇺 EU Servers

SQLDoom on 🇺🇸 US Servers

It’s the shareware version of the first episode. If all seats are taken, you land in the queue. If the queue is full, you can still poke around and query live game state via SQL while you wait.

SQLDoom

Last year, I published DOOMQL [Github]. It rendered some ASCII-art roughly resembling Doom at 30 FPS and people liked it a lot. But some people correctly pointed out that it is a lot closer to Wolfenstein 3D than Doom, since it uses a raycasting approach. Doom, on the other hand, uses BSP trees, which make correct depth ordering cheap enough to afford textures, arbitrary wall angles, and varying floor heights.

Well I couldn’t let this rest and after some tinkering (you guessed it, parental leave again), I can finally present the real Doom running entirely in SQL.

The rules

Let’s first establish a few baseline rules about what we want to achieve:

1. It should look like the real Doom. DOOMQL’s visual fidelity is pretty embarrassing in hindsight.
2. But more importantly, it also should feel like the real Doom. The original game is just raw fun.
3. The rendering must be purely SQL-based. The only acceptable SQL output is a table or a bitmap encoding exact RGB values for every pixel.
4. The game loop must also be purely SQL-based. It’s okay to use user-defined-functions inside the DB, though.
5. I’m allowed to write a client in another programming language, as long as it only takes care of parsing the input, driving the game tics, and rendering the output bitmap.

Architecture

Python is delibarately boring (Rule 5). A single script uses pygame to drive input, draw the output bitmap and trigger a game tic 35 times a second. Game logic, game state, and renderer live inside the database.

 Python
 input / timing / display
 | ^
 | |
 run game tic request frame
 | |
 v |
 +----------------+ +----------------+
 | | | |
 | SQL game logic | | SQL renderer |
 | | | |
 +-------+--------+ +--------+-------+
 | ^
 | |
 v |
 +-----------------------------+
 | |
 | game state tables |
 | |
 +-----------------------------+

The two paths are intentionally separate: The game logic runs on a fixed 35 Hz loop, while the renderer is a pure function of the game state tables and the client can ask for a new frame whenever it wants (i.e., as fast and often as possible).

Loading the Game Data

Conveniently, Doom’s .wad file format is actually is highly relational already.

Two VERTEXES are connected by a LINEDEF, which has two SIDEDEFs. SIDEDEF bound a SECTOR which can have THINGS in them, you get the idea. Translating the whole WAD into a database was surprsingly straightforward and took about 1000 lines of Python. Importing all of Doom 1 takes about 18 seconds on my laptop.

For example, here’s a query rendering E1M1 from a bird’s eye view:

WITH wall AS (
 SELECT round((v1.x + (v2.x - v1.x) * t / 32.0) / 48) AS col, -- 48 units per column
 round((v1.y + (v2.y - v1.y) * t / 32.0) / 96) AS row, -- chars are 2:1
 l.left_sd_id < 0 AS solid -- one-sided lines are pass-through
 FROM linedefs l, generate_series(0, 32) AS t -- walk each line in 32 steps
 JOIN vertexes v1 ON (v1.map_id, v1.id) = (l.map_id, l.v1_id)
 JOIN vertexes v2 ON (v2.map_id, v2.id) = (l.map_id, l.v2_id)
 WHERE l.map_id = 1
)
SELECT string_agg(CASE WHEN (col, row) IN (SELECT col, row FROM wall WHERE solid) THEN '#'
 WHEN (col, row) IN (SELECT col, row FROM wall) THEN '.'
 ELSE ' ' END, '' ORDER BY col)
FROM generate_series(-16, 79) AS col, generate_series(-51, -21) AS row
GROUP BY row ORDER BY row DESC;

Output:

 #####################
 # ..................#
 # . ...... .#
 # . ...... ###### .#
 ###### .. ## .#
 #####.. . .. ## ##
 # ####### ...... ###### ##########
 # ## # . ###.. ..##
 ################ ## # ###.........######## #####. ##
### ........... # ########..########.........### #### .## ######
# .. ########## #### ## ## #..... ####### ##
# . ##### ... ## ### #.....#..## ........ ##########..... ....##.#### ##
# . ###.### ...... ### . . ## ... ... . ...... .# ## ##
# . ##.. . ...... ## . . ## . . . .......... ## ## ##
# . ###.###### ... ###### #.....#..## ... .. #. ... .. # ## ##
# . ############ # .. .......... #.......... ...# # #
###......... # ##### ##### ##..... ... ##.### #
 ################ ####### ####.........##.##........####### .####### ### #
 ###########.################# # #### # # ##
 #.# ####...... # # . ### ##
 #.################# # ###########
 ####. .#### #..#
 ##### ######..######
 # . .. #
 # ##...## #
 # ## ## #
 ######..######
 ####
 #####
 #.. #
 #####

The Game Loop

It was important to me to actually port Doom, not only render frames that vaguely look like it. Of course, the visuals play a big part in that, but Doom also just feels awesome to play. Take a look at the following scene which is rule 2 in action (me having fun):

As you can see, there is a lot going on. Just in this short clip we see:

• Player input has to be polled and processed (walking, turning, shooting),
• enemies walk and attack,
• items are picked up,
• the rocket launcher fires projectiles that move,
• rocket explosions have a blast radius,
• enemy sprites have to be rendered,
• animations, view bobbing, and the HUD

And we don’t have a lot of time to process all of it: The original Doom ran on a fixed 35 Hz clock, so a tic has a budget of 1000 ms × 35 Hz = 28.6 ms. It also drew exactly one frame per tic, so it was capped at 35 FPS as well.

SQLDoom keeps the game logic at 35 Hz (so all the original constants still work), but decouples the drawing. The client can query (get it?) for a frame whenever it likes and we interpolate the camera position between tics. So there are two budgets we have to take care of:

• Running a tic every 28.6 ms (or it will feel just completely wrong)
• Rendering at least 35 frames a second (less is kind of okay, but won’t feel smooth)

The tic sequence

Game tics are inherently procedural. We have a sequence of things we have to do each time we run the tic. CedarDB has a scripting language called cedarscript, it closely resembles PL/pgSQL and allows us to plan beforehand what to do each tic.

Here is a small section of the tic function:

doom_cs_clock(map, p);
let mut plan = doom_cs_plan(map, p); -- returns a bitmask of functions to trigger

let use_queued = doom_tic_use(map, p, plan);
if (plan & 2) <> 0 OR use_queued { active = doom_cs_activate_specials(map); }
if (plan & 4) <> 0 OR active <> 0 { doom_cs_doors(map, p); }

doom_tic_move(map, p); -- full movement, or just turning
doom_cs_death(map, p); -- process deaths

plan = doom_cs_plan(map, p); -- the world moved; re-plan
plan = doom_tic_secrets(map, p, plan); -- secrets, walkover lines, pickups
plan = doom_tic_weapon(map, p, plan); -- weapon state, hitscan, damage
...
if sound_due { doom_cs_sound(map, p); } -- yes, we also play sounds
doom_cs_monsters(map, p); -- always
doom_cs_sector_fx(map, p); -- always
doom_cs_thing_physics(map); -- always

The python driver from above calls SELECT doom_run_game_tic(...) every 1/35 second.

Each of those called functions then execute a batch of SQL statements. Below is a part of the state machine of the monster AI.

-- Abridged from sql/runtime/functions/26_cs_monsters.sql.
WITH RECURSIVE
 monsters AS ( [...] ), -- who is alive, what kind, where
 los AS ( [...] ), -- visible, in_view_cone, dist: recursive, walks walls
 decision AS ( [...] ), -- one row per actor: its state and what it can see
 transitions AS (
 SELECT d.*,
 CASE
 WHEN NOT d.alive AND d.state NOT IN ('die', 'dead', 'xdeath') THEN
 CASE WHEN d.health < -d.max_health AND d.xdeath_frame IS NOT NULL
 THEN 'xdeath'::actor_state ELSE 'die'::actor_state END -- GORY EXPLOSION!
 WHEN d.state = 'stand' THEN
 CASE WHEN d.visible AND d.in_view_cone AND d.dist <= sight_range
 THEN 'see'::actor_state ELSE 'stand'::actor_state END
 WHEN d.state_tics > 1 THEN d.state -- animation still running
 WHEN d.state = 'see' THEN
 CASE WHEN d.visible AND d.dist <= d.attack_range
 AND d.attack_cooldown <= 0
 THEN 'missile'::actor_state ELSE 'see'::actor_state END
 [...] -- die, xdeath, missile, pain, barrel: 5 more
 ELSE d.state
 END AS next_state
 FROM decision d
 )
UPDATE monster_ai ai
SET state = n.next_state, state_tics = n.next_tics, seq_index = n.next_seq,
 fired_this_tick = n.advances AND n.lands_on_attack_frame
FROM next_values n
WHERE ai.map_id = n.map_id AND ai.thing_id = n.thing_id;

As you can see it encodes the behavior of the clip above: If an enemy takes extreme amounts of damage (CASE WHEN d.health < -d.max_health AND d.xdeath_frame IS NOT NULL) it violently explodes! (THEN 'xdeath'::actor_state).

Tic driver performance

Here’s a waterfall rendering of a game tic:

It’s actually the slowest game tic I was able to find. It’s in level E4M1 with 46 awake monsters all trying to rush at me through a currently opening door. It takes 10.45 milliseconds, so ~37% of the available tick budget.

A more typical tic with 6 monsters awake takes 2.15 milliseconds on average, or about 8% of the budget. Lots of headroom to spare!

To be honest, I was surprised how easy it is to express pretty complicated game logic in SQL. The game logic is just ~5900 lines of SQL. While this sounds a lot, it’s definitely less than the original C source code which does the same in about 9000 lines!

Also, it forces you to think differently. Instead of iterating over, e.g., enemies one-by-one you just write a simple UPDATE ... WHERE condition and let the database figure out how to best apply that - in parallel, automatically!

That also finally made the Entity Component System (ECS) pattern click for me. Here, each entity (player, monster, thing, …) has multiple components (position, sprite, stats, …) and a system (monster ai, move player, damage calculation) decides on how entities with a given set of properties interact with each other. ECS is a lot about data locality and how to iterate over entities that have a given set of components. Well, in SQL we are very used to data intensive processing! Every component becomes a table, and every system becomes an update or insert that just joins the tables it’s interested in with the entity as join key!

Rendering

Every frame is just a giant view that reads the level geometry and game state plus the player position as input and returns a complete framebuffer. Here’s a sketch of the whole rendering pipeline:

WITH RECURSIVE
 render_context AS (SELECT $1 AS map_id, $2 AS player_thing_id, $3 AS difficulty),
 pos AS (SELECT $4 AS x, $5 AS y, $6 AS z, $7 AS angle),
 visible_children AS ( ... ), -- walk the BSP, culling invisible segments
 clipped, projected, on_screen, -- project segments to screen space
 wall_parts, columns, fragments, -- one row per wall pixel
 panel_clips, plane_spans, ..., -- ceiling/floorclip as window functions, visplanes
 thing_pixels, sprite_fragments, -- sprites
 fragment_union, resolved, -- every candidate pixel, resolve for the nearest
 view_colored, ui_colored, -- COLORMAP, status bar
 framebuffer AS ( ... ) -- 64,000 rows of (x, y, rgb)
SELECT string_agg(rgb, ''::bytea ORDER BY y, x) AS frame_rgb
FROM framebuffer; -- 192,000 bytes, one row

The implementation is ~1300 lines of SQL (excluding comments) spread across 89 CTEs, so pretty complicated for a SQL query!

But despite looking like complete insanity, this pipeline is actually pretty close to what Doom does. SQL even has one advantage: The linux_doom source uses about 3300 lines (excluding comments) for its rendering engine. About 2.5x more lines than SQLDoom. Whether it was a good idea in the first place is a different question, and we’ll talk about that later.

Let’s first look at the most interesting parts of the rendering pipeline:

The left half shows bsp-based culling, the right half visualizes wall rendering and visplanes.

BSP traversal

Since nobody in 1993 had GPUs with hardware-accelerated Z-buffering, Doom had to get occlusion right by drawing in the correct order. The way Doom does it is pretty ingenious: It paints front to back and keeps track of which pixels it already painted (i.e., if I have already drawn a wall pixel, I don’t have to draw the monster behind it). But that’s easier said than done: We need an efficient way to order everything in the level by depth.

Doom gets this ordering by using precomputed BSP Trees baked into the doom.wad file. Every node of the tree is a line splitting the map in two. The map’s sectors thus get chopped up into a lot of subsectors which are on either side of those lines, and are then inserted into the tree so that we get the following properties:

1. each subsector is a leaf and
2. each subsector is convex (i.e., you can see any wall from anywhere inside it)
3. at every tree node, the entire subtree that is on the camera’s side is guaranteed to be in front of the subtree on the other side.

By recursively traversing the BSP tree, we thus get a front-to-back order of all subsectors. This gives us the rendering order directly: Once a screen region has been covered by something nearer, objects behind it can be skipped.

Here’s how this looks like in motion (you might have to view it in full screen):

On the left, subsectors are ordered front to back, while BSP branches out of view are eagerly culled. In the middle you can see the order that SQLDoom assigns each region. On the right, you see the resulting frame with walls colored according to the subsector they’re in.

The middle panel shows an optimization SQLDoom makes: For better performance we pre-compute all paths in the BSP tree once at load time. For a given position, every step along such a path is either taking the front (encoded as 0), or the back (encoded as 1). If we pack these decision into a bigint, and sort that lexicographically (order by), we get the right front to back ordering.

SELECT ssector_id, ROW_NUMBER() OVER (ORDER BY sort_key) AS bsp_seq
FROM (
 SELECT st.ssector_id,
 -- back = 1 at bit (40 - depth), front = 0.
 SUM(CASE WHEN st.side = fs.front_side THEN 0::bigint
 ELSE (1::bigint << (40 - st.depth)) END) AS sort_key,
 BOOL_AND(vc.keep) AS visible -- was any parent bbox culled?
 FROM node_path_steps st -- materialized view, every root-to-ssector path
 JOIN nodes n ON ...
 CROSS JOIN LATERAL (SELECT ... AS front_side) fs -- on which side are we?
 JOIN visible_children vc ON ...
 GROUP BY st.ssector_id
) s WHERE s.visible;

One sum() ... order by replaces the whole recursive descent! 40 bits should also be able to handle any map we throw at it: The deepest BSP-Tree is that of E4M8 and has just 32 levels. As long as your maps aren’t larger than 256 times the biggest vanilla map, you’re all sorted!

If you look carefully, you can see that our bsp traversal also handles culling: Conveniently, every node in the .wad also defines a bounding box of all of its children. If we can prove that our view frustum is entirely outside of that bounding box, we don’t have to consider that subtree for rendering - that is what visible_children.keep signifies. bool_and(vc.keep) thus drops all subsector where any ancestor doesn’t qualify.

Everything afterwards in the pipeline is just joined against bsp_seq so only visible subsectors are considered and in the right order.

Walls and Visplanes

Doom is kind of cheating, it looks 3D, but in reality it’s a 2.5D game. It’s essentially just a flat surface with perfectly vertical walls and ceilings always being parallel to the ground. This makes rendering far easier than in a real 3D engine:

1. Paint all walls (front to back, as discussed)
2. Everything that isn’t painted yet, is either a floor or a ceiling. Paint that.
3. Sprites (monsters, barrels, pickups) are flat images that always face you (think cardboard cutouts), so no complicated transformations here (except for when they overlap a wall, but we’ll get to that).

Walls

A wall occupies a set of contiguous screen columns, and within each column it is a contiguous span of pixels. So we can just paint walls one-by-one, front-to-back by expanding rows and columns via generate_series():

columns AS ( -- emit a row per screen column the wall w covers
 SELECT w.*, x AS col_x, ...
 FROM wall_parts_tex w
 CROSS JOIN LATERAL generate_series(
 GREATEST(0, FLOOR(w.screen_x1)::int),
 LEAST(screen_w - 1, CEIL(w.screen_x2)::int)) AS x
),
fragments AS ( -- one row per pixel the wall covers in this column
 SELECT c.col_x AS x, y, c.depth_x AS depth, c.u_i, c.v_i
 FROM clamped_spans c
 CROSS JOIN LATERAL generate_series(c.y_start, c.y_end) AS y
)

Doom uses two loops instead: R_RenderSegLoop to get the screen columns and R_DrawColumn to draw the pixels.

Rendering the walls cost us on average 1.7 ms.

Visplanes

Now that we have the walls out of the way, let’s talk about the fun part: The floors and ceilings, what Doom calls visplanes.

Unfortunately, Doom’s rendering algorithm doesn’t translate to SQL nearly as well since it’s highly imperative: Doom keeps two arrays, ceilingclip and floorclip which have one entry per screen column. They mark the band in each column that is still open (i.e., has to become floor or ceiling and hasn’t been painted yet) Whenever a new wall is painted, they are mutated until every pixel is filled. Not only does Doom mutate them, but it’s also very important to mutate them in the right order. It’s ingenious! In the end it’s just a flood fill algorithm, but everything looks 3D basically for free (in C, that is).

SQLDoom has to approach this problem differently, as we don’t have the concepts of loops or mutable state in SQL. So instead of looping, we turn to sorting and aggregating over those sorted runs - a poor man’s loop!

The things we iterate over here are called panels: One part of a wall appearing in one column of the screen. Some panels draw something: a solid wall (solid), the wall above a door (upper), or the wall part below a window or a parapet (lower), some panels are just there to influence how other panels are rendered: If you step out of a door below a balcony, there’s something above you and that has to end somewhere.

So for each screen column (col_x) we have an ordered list of panels from near to far. The clip state before a panel is thus defined entirely by the row preceding it. Do I smell window functions?

Since this is pretty hard to explain in text, let’s watch a video instead!

Here’s the (abbreviated) SQL query:

panel_clips AS (
 -- 1. the band as the NEARER panels left it
 SELECT p.*,
 COALESCE(MAX(CASE WHEN part IN ('solid','upper','upper_flush')
 THEN y_bot::int + 1 END) OVER w, 0) AS cc_before,
 COALESCE(MIN(CASE WHEN part IN ('solid','lower','lower_down')
 THEN y_top::int - 1 END) OVER w, screen_h - 1) AS fc_before
 FROM panel_seq p
 WINDOW w AS (PARTITION BY col_x ORDER BY depth_x, bsp_seq, part, seg_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
),
plane_spans_raw AS (
 -- 2. whatever the band leaves uncovered is a ceiling above the wall...
 SELECT col_x, fsec AS sector_id, f_ceil AS plane_z, 'ceil' AS plane,
 cc_before AS y0, -- from where nearer walls stopped
 f_ceil_y::int - 1 AS y1 -- down to this panel's own ceiling
 FROM panel_clips
 WHERE part IN ('solid','upper','upper_open','upper_flush')
 AND f_ceil_y::int - 1 >= cc_before -- nothing left open: skip
 UNION ALL
 -- ...and a floor below it
 SELECT col_x, fsec, f_floor, 'floor',
 f_floor_y::int AS y0, -- from this panel's own floor
 fc_before AS y1 -- down to where nearer walls stopped
 FROM panel_clips
 WHERE ...
)

We first calculate for every panel in the scene that potentially renders some pixels how much of the column is still unassigned. And the only pixels that already could be assigned are from all the panels closer (that’s the ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING term in (1)). Then we draw some pixels from the end of the previous panel until the beginning of the next panel (2). We do this both for ceilings and floors.

A pretty hacky way to disguise an imperative algorithm as set-based, right? Good thing we have window functions…

Rendering floors, ceilings and the sky typically costs about 3 ms.

The ugly part

Unfortunately, I had to lie to you: Walls, visplanes and sprite resolution don’t draw anything yet. They just emit candidates of the form

  Evaluating LLM Models for DBA Tasks Large language models are increasingly capable of performing practical systems-administration tasks. I wanted to understand how well they could handle something more specialized: database administration. To explore this, I developed a harness for evaluating the ability of different LLMs to execute real DBA tasks on remote systems. My … Continued

The post Evaluating LLM models for DBA tasks appeared first on Percona.

A while ago, we discussed the cross-site replication feature of the Percona PXC operator. Recently, a similar cross-site replication feature was introduced in the Percona (PS MySQL) operator v1.2.0, a topology based on Group Replication/InnoDB Cluster. In this blog post, we will explore how to add a DR Cluster to an existing DC Cluster to … Continued

The post Group Replication Beyond a Single Cluster: DC-DR with Percona (PS MySQL) Operator appeared first on Percona.

Learn why PXC accumulates thousands of gcache.page.* files, how frozen GCache purging causes disk growth, and how to safely recover and rejoin using IST.

The post Too many GCache Page Files in MySQL Data Directory appeared first on Percona.

In this blog post, we’ll compare pgBackRest’s compression algorithms and levels to find where spending more CPU stops buying a meaningfully smaller backup. The short version of the answer, which we’ll build up to with real numbers, is that Zstandard at a low level is the sweet spot, and its default (zst(3)) already sits right … Continued

The post pgBackRest Compression: How Much CPU Is a Smaller Backup Worth? appeared first on Percona.

This project teaches four of PostgreSQL's most powerful features by building something no sane person would ship:

• extensions — how you add new capabilities to PostgreSQL in C,
• expression indexes — how you index a computed value, not a stored one,
• custom operators — how you teach PostgreSQL new verbs like <@ and <->,
• specialized indexed types — how you invent a data type and the index that makes it fast.

The bad idea that ties them together: store every date as the literal ten characters MM/DD/YYYY and then make that terrible choice searchable.

⚠️ Do not do this in production. PostgreSQL already has a perfectly good
date type. We are torturing a string on purpose, because a bad-but-simple
example is the fastest way to see what each PostgreSQL feature is really
for. Every section below tells you the sensible thing to do first, then keeps
going for the lesson.

The story

Imagine you inherit an application from a team that stored all its dates as US text: 09/15/2026, 12/31/2025, and so on. You cannot change the column. The new feature request is deceptively small:

Find me everything that happened in September, any year.

That one sentence walks us straight through all four features. By the end you will have:

1. reached for an expression index (the correct, boring first answer),
2. discovered its limits and built a custom type with its own operators,
3. taught PostgreSQL a containment operator <@ for partial dates like 09/*/*,
4. added a similarity operator <-> and a GiST index that answers "nearest birthdays" without scanning the whole table.

Why is MM/DD/YYYY such a fun villain? Because it is not sorted on anything a computer likes. Sorting the text groups all the Januaries together, then all the Februaries, while the years jump around inside each group. It is, famously, used by almost nobody outside the United States:

But here is the twist that makes it worth studying: it is ordered on something. It is ordered month first, then day, then year. And it turns out that is exactly the order you want for a surprising number of real questions.

1. The sensible answer first: an expression index

You do not need any of this project to answer "everything in September." You need an expression index, and it is worth understanding why, because it is the tool you should actually reach for 95% of the time.

Here is the inherited table you are not allowed to change:

CREATE TABLE events_text (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    happened_on text NOT NULL   -- e.g. '09/15/2026'
);

An index normally indexes a column. An expression index indexes the result of a function applied to each row. So we write one small, strict, immutable function that turns the text into a real date, and index that:

CREATE FUNCTION us_text_to_date(value text)
RETURNS date
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
AS $$
    SELECT make_date(
        substring(value FROM 7 FOR 4)::integer,  -- YYYY
        substring(value FROM 1 FOR 2)::integer,  -- MM
        substring(value FROM 4 FOR 2)::integer   -- DD
    )
$$;

CREATE INDEX events_text_chronology_idx
ON events_text (us_text_to_date(happened_on));

make_date even rejects impossible dates like 02/30/2026 for free. Now queries that use the same expression hit the index:

SELECT *
FROM events_text
WHERE us_text_to_date(happened_on) >= date '2026-09-01'
  AND us_text_to_date(happened_on) <  date '2026-10-01';

The row on disk is still the ugly text 09/15/2026; the B-tree quietly stores the derived date. That is the whole point of an expression index: you keep your data as-is and index a better view of it.

There is one sharp edge: the query has to spell the expression exactly the same way as the index, or PostgreSQL won't use it. A cleaner habit is to give the derived value a name with a generated column, so every query refers to one plain column instead of repeating the function:

ALTER TABLE events_text
    ADD COLUMN happened_date date
    GENERATED ALWAYS AS (us_text_to_date(happened_on)) STORED;

CREATE INDEX events_text_chronology_idx
ON events_text (happened_date);

SELECT *
FROM events_text
WHERE happened_date >= date '2026-09-01'
  AND happened_date <  date '2026-10-01';

GENERATED ALWAYS AS ... STORED computes happened_date from happened_on on every insert or update and keeps it in sync automatically — you can't write to it directly, so it can't drift. Now the column, the index, and every query all speak the same simple name, and there is no way to accidentally miss the index by phrasing the expression differently. (The function must be IMMUTABLE, which ours is.) This is still just an expression under the hood; the generated column only gives it a stable, hard-to-misuse name. Verified on PostgreSQL 18, the query above plans as an Index Scan using events_text_chronology_idx with an Index Cond on happened_date.

Why STORED and not VIRTUAL? PostgreSQL 18 added VIRTUAL generated columns (computed on read, no storage) and made them the default. They sound like the perfect fit here, but two rules rule them out for this case:

• a VIRTUAL column's expression cannot call a user-defined function, and us_text_to_date is exactly that — PostgreSQL 18 rejects it with "Virtual generated columns that make use of user-defined functions are not yet supported";
• you cannot build an index directly on a VIRTUAL column at all ("indexes on virtual generated columns are not supported").

So STORED is the right tool: it pays a little disk to give us a real, indexable column. If your derived value used only built-in functions, VIRTUAL would be viable — but you would still index the underlying expression, not the virtual column itself.

For the pure "which month" question, you can even index the text directly, because every value has the same fixed width:

CREATE INDEX events_text_month_first_idx
ON events_text (happened_on text_pattern_ops);

SELECT * FROM events_text WHERE happened_on LIKE '09/%';

For production, stop here. Or better, store a real date and format it for display. Everything after this point exists to teach you what PostgreSQL lets you build when the boring answer is not enough — and to make the tradeoffs visible.

2. Why keep going? Because month-first is a real question shape

The joke is that US dates are "ordered on nothing." The deeper truth is that they are ordered on month, then day, then year, and some questions genuinely want that order.

• A vineyard asks which grape varieties get harvested latest in the season? The month and day matter first; the year just names the vintage.
• Who shares a birthday? Month and day matter; the birth year is often deliberately ignored.
• Anniversaries, holidays, seasonal maintenance — all care about where in the year something falls, not its position on a global timeline.

But notice the moment you take "where in the year" seriously, plain ordering stops being enough. Sorting says January comes before February comes before December, so on that line December looks as far from January as possible. Seasonally that is nonsense: a December holiday and a January one are practically neighbours. The question is no longer "what comes before what" but "how close are these two dates in the year?" — and closeness wraps around the calendar.

That is the real lesson hiding inside this silly format: advanced indexes are not only about linear sorting; they are also about distance. A B-tree is a sorting machine and can only ever put values on one line. To rank dates by seasonal nearness — with December next to January — we need an index that understands distance, which is exactly what GiST gives us. Keep that circular-month idea in the back of your mind; it is what motivates the KNN operator later.

So we will take the month-first order seriously and ask: what if MM/DD/YYYY were a native PostgreSQL type, with its own operators and its own index? This is not a claim that Americans invented the format for database search — the W3C notes it is a US convention and 03/04/02 is ambiguous across locales, and the international standard is ISO 8601 YYYY-MM-DD. It is our own playful reinterpretation, turned into a working search policy.

3. Feature: a specialized type (a PostgreSQL extension in C)

This is where the extension comes in. PostgreSQL was built to be extended: you can add a brand-new data type, written in C, that stores and indexes itself natively instead of piggy-backing on text.

The extension adds a type called mmddyyyy whose physical value is exactly the ten displayed bytes:

SELECT '09/15/2026'::mmddyyyy AS value,
       pg_column_size('09/15/2026'::mmddyyyy) AS bytes,
       '09/15/2026'::mmddyyyy::date AS native_date;

   value    | bytes | native_date
------------+-------+-------------
 09/15/2026 |    10 | 2026-09-15

Input is validated strictly: correct separators, leading zeros, real Gregorian month lengths, leap years, and years 0001..9999. The extension also gives you casts to and from date, accessors for month/day/year, comparison operators, and a default B-tree operator class. The pieces of an extension:

• mmddyyyy.control and sql/mmddyyyy--0.1.0.sql register the type, functions, casts, and operators with PostgreSQL.
• src/mmddyyyy.c implements the behavior in C.
• The Makefile compiles it against the PostgreSQL server headers.

Feature: custom operators (starting with sort order)

Here is the first genuinely surprising thing. A B-tree does not know how to compare your values by itself. It asks the type's operator class. So even though the bytes start with the month, we can tell the B-tree to sort them chronologically.

Plain text sorts left to right, so it gets this wrong:

SELECT '12/31/2025'::text < '01/01/2026'::text;  -- false (1 sorts after 0)

But mmddyyyy ships a comparison function, mmddyyyy_cmp, that reads out the year, month, and day and compares them in year, month, day order by calling compare_mmddyyyy:

if (left_year  != right_year)  return left_year  < right_year  ? -1 : 1;
if (left_month != right_month) return left_month < right_month ? -1 : 1;
if (left_day   != right_day)   return left_day   < right_day   ? -1 : 1;
return 0;

We register it as the type's default B-tree operator class, so the same visible spelling now sorts chronologically:

SELECT '12/31/2025'::mmddyyyy < '01/01/2026'::mmddyyyy;  -- true

Create the index with completely ordinary SQL — PostgreSQL picks the default operator class automatically:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    happened_on mmddyyyy NOT NULL
);

CREATE INDEX events_date_btree ON events (happened_on);

SELECT * FROM events
WHERE happened_on >= '09/01/2026' AND happened_on < '10/01/2026';

The lesson: "ordinary SQL" hides a custom operator. The syntax is normal; the ordering is something we defined in C.

4. Feature: a custom operator for a new question (<@)

The B-tree comparison operators compare two complete dates. They are great for "before September 1" or "between two dates." They cannot express the question we started with: does this date belong to September, regardless of day and year?

So we invent a second type, mmddyyyy_pattern, where any component can be * ("I don't care"), and a new operator <@ that reads as "the date is contained in the set the pattern describes."

SELECT '09/15/2026'::mmddyyyy <@ '09/*/*'::mmddyyyy_pattern;  -- true
SELECT '09/15/2026'::mmddyyyy <@ '*/15/*'::mmddyyyy_pattern;  -- true
SELECT '09/15/2026'::mmddyyyy <@ '10/*/*'::mmddyyyy_pattern;  -- false

This is component equality with wildcards. It is not LIKE, not a text prefix, not a range. The * becomes a bitmask inside the pattern, not a SQL wildcard.

5. Feature: a specialized index (GiST) to make <@ fast

We can now ask the question, but answering it quickly is a new problem. A normal compound B-tree on (month, day, year) is fast for month = 9 (the leading column) but useless for day = 15 alone, because matching rows are scattered across all twelve month ranges.

The reason is fundamental: a B-tree squeezes three columns onto one ordered line.

(01,01,0001), (01,01,0002), ..., (01,02,0001), ..., (12,31,9999)

month = 9 is one contiguous slice of that line. day = 15 is a little piece inside every month's slice — not contiguous, so the B-tree cannot jump straight to it.

GiST (Generalized Search Tree) is a different kind of index. Instead of one global order, every internal node stores a summary box that bounds its children in each dimension independently:

month {8,9,10}   day [1,31]   year [1980,2030]

That box promises: every date below me has a month in {8,9,10}, a day in [1,31], and a year in [1980,2030] — all at once, and independently. So any specified component can rule the whole subtree out:

• 09/*/* → month 9 is in the set → maybe, descend.
• 02/*/* → month 2 is not in the set → skip this entire subtree.
• */15/2050 → year 2050 is outside [1980,2030] → skip, even though the month was a wildcard.

One GiST index handles month-only, day-only, year-only, and any mix:

CREATE INDEX events_date_gist ON events USING gist (happened_on);

SELECT * FROM events WHERE happened_on <@ '09/*/*';
SELECT * FROM events WHERE happened_on <@ '*/15/*';
SELECT * FROM events WHERE happened_on <@ '09/*/2026';

That flexibility is the whole reason GiST exists, and it is why we needed a custom type: a specialized index and a specialized type are designed together.

6. Feature: an ordering operator (<->) for "nearest" search

<@ gives a yes/no answer. The next natural question has no yes/no answer: which dates are most similar to September 15, 2026? Should an old September date rank ahead of August 15 the same year? That is a policy, and we make it explicit with a distance operator <->:

SELECT happened_on, happened_on <-> '09/15/2026' AS distance
FROM events
ORDER BY happened_on <-> '09/15/2026'
LIMIT 10;

ORDER BY distance LIMIT k is k-nearest-neighbor (KNN) search. The distance we chose makes month dominate day, and day dominate year, with the month measured around the calendar circle so December and January are neighbours:

The weights create strict tiers: any month difference costs at least 32; any day difference at least 1; every possible year difference stays under 1. So this is seasonal similarity, not elapsed time. Wildcards contribute zero — distance from 09/*/* only measures how far your month is from September. Our GiST operator class registers <-> as an ordering operator and supplies a lower-bound distance for internal boxes, so PostgreSQL can walk the tree in distance order and stop after k rows instead of scoring every row.

7. How the GiST index actually works

The public value and the GiST key are deliberately different things:

flowchart LR
  Input["input 09/15/2026"] --> Parse["mmddyyyy_in validates"]
  Parse --> Heap["heap: 10 text bytes"]
  Heap --> Compress["GiST compress"]
  Compress --> Leaf["leaf point: month {9}, day 15, year 2026"]
  Leaf --> Union["union child keys"]
  Union --> Internal["internal M/D/Y summary box"]
  Pattern["pattern 09/*/*"] --> Consistent["consistent"]
  Internal --> Consistent
  Consistent --> Decision["descend or prune"]

The heap keeps the ten text bytes. The GiST stores a separate 8-byte summary key per entry, because pruning a subtree needs a compact bound. The three fixed-size C structures are:

Compile-time assertions (StaticAssertDecl) keep those layouts locked to the INTERNALLENGTH values declared in mmddyyyy--0.1.0.sql, so the C and SQL sides can never drift apart silently.

Months are a circle, not a line

This is the most interesting design detail, and one worth understanding.

Day and year are ordinary ranges: [min, max]. But months wrap around — a subtree holding only December and January dates is a tight seasonal cluster, yet a naive range would record it as [1, 12], the "any month" box that can never prune anything.

So the month component of the GiST key is stored as a 12-bit bitmap (month_mask): bit m−1 is set when some date below has month m. That makes the box for a December/January cluster print as {1,12} — two months — instead of the useless span [1,12]:

({9},[15,15],[2026,2026])     -- a leaf: exactly 09/15/2026
({6,7},[1,31],[1,748])        -- an internal box: June & July, days 1-31, years 1-748
({12,1},[1,31],[2000,2001])   -- a tight winter cluster, NOT "every month"

The bitmap also makes two GiST operations clean:

• union (combining child boxes) is a bitwise OR — exact and order-independent, exactly what GiST wants.
• distance looks only at months actually present, and measures each one around the circle, so the Dec/Jan box is genuinely near January.

On disk the layout stays 8 bytes: two int16 year bounds, one uint16 month bitmap, two uint8 day bounds.

The operator-class callbacks

A GiST operator class is a set of C functions PostgreSQL calls at the right moments. Ours:

PostgreSQL owns the hard parts — page layout, tree height, locking, WAL, crash recovery, concurrent splits, and the scan machinery. The operator class only supplies the domain logic. That division of labor is what makes GiST reusable for R-trees, full-text search, ranges, and this calendar experiment alike.

Reading a real GiST page

The benchmark uses the pageinspect extension to read block 0 (the root) directly, so you can see the summary boxes PostgreSQL built:

child_page  (1404,65535)
key         (happened_on)=("({6,7},[1,31],[1,748])")

The block number is the downlink to a child page; offset 65535 (0xFFFF) marks it as an internal downlink rather than a heap row. The key is that child's summary box in the ({months},[day_lo,day_hi],[year_lo,year_hi]) form produced by the type's output function.

8. See the difference: B-tree vs GiST on the full calendar

The heavy lab in lab/compare-indexes.sql generates every representable day from 01/01/0001 through 12/31/9999 — 3,652,059 rows — and builds one compound B-tree and one GiST over the same components:

CREATE INDEX calendar_days_mmddyyyy_btree
ON calendar_days (
    mmddyyyy_month(happened_on),
    mmddyyyy_day(happened_on),
    mmddyyyy_year(happened_on)
)
INCLUDE (happened_on);

CREATE INDEX calendar_days_mmddyyyy_gist
ON calendar_days USING gist (happened_on);

With sequential/bitmap scans and parallelism disabled to expose what each index must visit, one run showed the pattern clearly (exact numbers vary by hardware and build history — rerun the lab to regenerate them):

The honest summary: GiST's strength is flexibility, not raw speed. One GiST index answers many shapes of question. In exchange you pay with more storage, overlapping summary boxes, slower builds, and less efficient exact lookups. For a leading-column or fully-specified query, the B-tree still wins. Choosing the right index is about matching the tool to the questions you actually ask.

9. Run it yourself

Docker is the only prerequisite. Everything builds PostgreSQL 17 with the extension compiled under -Wall -Wextra -Werror, so a build failure is a real defect.

Fast correctness suite (73,049 dates, 1900–2099; verifies types, casts, B-tree plans, <@ counts, multi-page GiST, index-only KNN, and KNN vs a forced sequential scan):

bash ./test.sh

powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\test.ps1

Narrated demo (walks from the expression index up to GiST and KNN plans):

docker compose build
docker compose up -d --wait
MSYS_NO_PATHCONV=1 docker compose exec -T postgres \
  psql -X -U postgres -d mmddyyyy_lab -f /project/lab/demo.sql
docker compose down -v

Full 3.65M-row comparison (allow ~1 GiB of Docker disk headroom):

bash ./benchmark.sh

powershell.exe -NoProfile -ExecutionPolicy Bypass -File .\benchmark.ps1

The same suite runs automatically in CI on every push (.github/workflows/ci.yml).

What this project deliberately leaves out

To keep the source readable, some real-world concerns are out of scope:

• Input must be canonical MM/DD/YYYY; one-digit fields are rejected.
• No BC dates, infinite dates, time zones, or years outside 0001..9999.
• <@ uses generic selectivity estimates.
• No binary send/receive functions or extension upgrade scripts.
• The GiST split heuristic favors clarity over benchmark-tuned packing.
• The container builds PostgreSQL 17 only.

None of these change the lessons; they just keep the code small enough to read in one sitting.

Where to go next

• PostgreSQL docs: GiST and pageinspect.
• Try a vector approach: a cube-based GiST index over (cos θ, sin θ, scaled_year) is a great comparison exercise — see why raw Euclidean distance does not give the strict month > day > year tiers this project enforces.
• Read the source: src/mmddyyyy.c is heavily commented and meant to be read top to bottom.

Background on the format

• ISO 8601: the international YYYY-MM-DD standard.
• W3C date formats: locale ambiguity and US MM/DD/YY.
• Unicode CLDR patterns: locale-sensitive date patterns.
• MIT on the US date format: the tentative British-inheritance hypothesis.

Final thoughts

Comparing PostgreSQL with other databases while ignoring its extensibility framework misses what makes it different. PostgreSQL was built as an extensible system: you can register a new base type that becomes native, with its own input/output functions and operators backed by C functions, and teach an existing access method how to index that type through an operator class and its support functions. That is exactly what this project does — mmddyyyy is a base type, <@ and <-> are operators, and mmddyyyy_gist_ops is a GiST operator class. This is far more than a hook for supplying a sort or comparison callback.

The leverage is in the division of responsibilities. The whole extension is on the order of a few hundred lines of C, and none of it touches the hard parts of a database engine. I never wrote a line dealing with MVCC visibility, tuple locking, buffer management, WAL, or crash recovery. My code is responsible only for the domain logic — how a date is parsed, compared, bounded into a GiST key, and scored for distance. Everything that makes an index safe and fast under concurrency and failure — durability, transactional consistency, page-level locking, and recovery — stays in the PostgreSQL core and the GiST access method. You extend the semantics; PostgreSQL keeps the guarantees.

AI doomerism is everywhere these days. Every field, and recently humanity as a whole, has had its "we're finished" post. In contrast to the run-of-the-mill hot take, Jason Potts has written an economics paper on why academia is doomed. So let's dive in.

What does a university sell?

Potts says a university is really a platform. It is a hub that connects many different groups: undergrads, grads, teaching staff, research staff, employers, government, alumni, donors, parents, etc. Each group needs the others. Undergrad tuition helps pay for the research infrastructure for the professors whose research reputation drew the students there in the first place. International student fees provide funding for local students. Unfortunately, this isn't a diversified portfolio kind of situation, but more of a weakest-link setup. If you pull on one thread, several others start to come loose as well.

(Side remark: I will admit that I have always struggled to find the true customer the universities served. It is not the faculty, not even the students... This platform/hub explanation makes sense, of course. But I wonder if this might be a convenient cover up to make the disorganized/disarrayed/organically-complicated state of universities look better.)

Since the university has this multifaceted platform/hub status, free availability of teaching technology (like books, online classes, the internet) never hurt the university. Potts argues, the university never really sold content, it mainly sold matching and verification. A degree is a certification. It tells an employer that this person has some ability the employer can take for granted.

Potts argues that AI is the first technology to disrupt that promise directly. AI makes it cheap to produce essays, code, homework, which the shools use for grading and certification. Potts calls this signal collapse.

He highlights two other disruptive developments. First, the university gets cut out of its traditional brokering position. It used to connect students to teachers and the researchers to funding. Now a student can get free tutoring from LLMs, and a researcher can work alone with an AI assistant instead of a lab full of people. Second, Potts argues that AI changes where the money comes from. It used to come from knowing a fixed body of facts, but now it comes from being able to learn something new fast. (I don't buy the second half of this. I think the  comparative advantage shifted towards creativity and judgement, as I argue in the discussion at the end of this post.)

Potts splits the universities into three types: elite, specialist, and all else. Elite schools are mostly fine, because their real product was always the prestige/selectivity and the classmates you meet there. Specialist schools (like medicine) are OK because of their hands-on status and that outside boards do the checking for them. All others are in trouble.

The warrant was already broken

Potts treats the degree as a strong asset, a Hart asset that an institution must protect above everything else, and argues that this is now facing a strong threat with AI. But that is not true, as universities had already devalued their "credible warrant of quality" feature by diluting their degree programs starting around 2010.

I lived through this. University administrators got greedy and chased more enrollment and more tuition. As a result, programs multiplied, courses got easier, and grade inflation become the norm. This had the same effect Potts describes for AI: a good grade stopped meaning the student actually mastered the material. The credential lost its meaning from the inside, at the hands of the very institution meant to protect it. I saw this firsthand. The SUNY Buffalo CS degree lost real standing with companies like Bloomberg in New York over those years, many years before the AI threat materialized. Every company started their own rigorous interviewing process rather than taking university certification at face value.

That this devaluation already happened matters for what to do next. If the degree still had high prestige, one of Potts's fixes, bringing back the oral exam, would work cleanly. But a school that spent 15 years training students, parents, and employers to expect easy A's cannot win back trust just by adding oral exams. It has the uphill battle ahead to convince the market to trust it again. Trust is easy to lose, and hard to gain back. Unfortunately, this means that the ordinary school in Potts categorization has nothing left in reserve. It's the school least able to take this hit, and it's now going to get hit with the AI wave now. 

What should the universities do

I suggest something Potts does not discuss. The universities should lean heavily towards humans' comparative advantage over AI.

AI can already write good code and do decent technical work, but it keeps failing on things without clear right answers. This makes creativity, judgment, and the authentic human voice the scarce resource (where the opportunity cost is lowest). And these are what a university should be teaching toward.

Potts does not talk about the human side of the story at all. I spent sixteen years as a professor before moving to industry. What I remember most, and miss most, is watching a student's eyes light up when an idea finally clicks. A good teacher doesn't just regurgitate the course content, rather they pass on the love for the subject. When you see someone who has spent thirty years on hard problems still lighting up talking about them, you want that for yourself as well. No AI model can match that inspiration, and light that fire. My own advisor did that for me, and watching him approach problems shaped how I think to this day.

Wrestling with a problem, persistently trying out various strategies, being unafraid of making mistakes, and progressing incrementally to understand the underlying ideas produces a certain kind of endurance, which enables us to be comfortable with the struggle. 

--Francis Su

The best parts of any real education happen off the page. None of this shows up in a course catalog, and none of this can be faked. It can only be earned slowly through hard effort, through apprenticeship (which is Lindy), and often through osmosis from another caring human being.

Every time that a human being succeeds in making an effort of attention with the sole idea of increasing his grasp of truth, he acquires a greater aptitude for grasping it, even if his effort produces no visible fruit. 

--Simone Weil

When did a database change happen?

This isn't just academic. Incremental replication must order committed changes and resume exactly without locking writers. Migration validation checks if source and target are the same state, even as both change. Audit and incident analysis reconstruct who changed data, when, and when others could see it. Application-log correlation links request timestamps with database transactions and resulting commits.

It has business meaning. An editor changes a document at 10:00🕐, saves at 10:04🕐 to publish it, and gets confirmation at 10:05🕐. Other sessions can't see the uncommitted change at 10:00🕐, but later services might see this timestamp. Which moment should an updated_at field report?

If correlating with an application log, the request or statement time may be correct. If describing the user's experience, it may be wrong: users distinguish "I edited", "I saved", and "I published". These are separate business events. Commit visibility, durability, client acknowledgment, and downstream publication are separate system events.

In a nonblocking MVCC database, there is no universal updated_at. A row version can be created, stay private, become visible at commit, reach replicas, and be shown to users later. The correct timestamp depends on the application's question.

That sounds like one question, but it is at least five:

1. When did the transaction begin?
2. Which committed state did a statement read?
3. When was a new row version created?
4. When did the transaction become committed and visible?
5. Which durable log position protects that commit?

PostgreSQL presents different coordinates: xmin:xmax:xip_list for transaction visibility, an LSN for write-ahead log position, and a transaction ID for tuple version changes. None are wall-clock timestamps.

Oracle appears more unified, using the System Change Number (SCN) for read consistency, transaction commits, checkpoints, and recovery. However, claiming "Oracle has only an SCN" is inaccurate, as it also requires a transaction ID, undo address, and redo position. YugabyteDB employs HybridTime as the MVCC read and commit coordinator, but provisional writes initially have different timestamps. SQL Server, MySQL/InnoDB, and MongoDB/WiredTiger split these responsibilities.

The key comparison isn't "which database has the best clock?" but rather: "which coordinator answers which ordering question?"

AI disclaimer: I wrote this with a lot of help from GitHub Copilot. I used it to make the comparison more thorough and to check each equivalence against the original documentation and code. Any interpretation and remaining errors are my own.

One transaction has several times

Consider this deliberately generic sequence:

BEGIN
 -> choose read point
 -> UPDATE
 -> COMMIT (some databases may run the following in different order)
      -> make log durable (disk or replicas)
      -> make changes visible
 -> reply (successful commit)

Some of these events can coincide in one implementation, but they remain different promises:

The logical visibility rule is similar in all MVCC systems. Here, v is a candidate row or document version, and q is the query evaluating whether it can see that version:

visible (v,q) = 
  ownTransaction(v,q)
  OR
  ( committed(v) AND ( commitPoint(v) <= readPoint(q) ) )

This is a model, not a specific product implementation. PostgreSQL and InnoDB don't store commitPoint as a per-row scalar. They determine it from transaction ID, status, and active transactions in a snapshot. Oracle and YugabyteDB clarify the logical commit process, but all engines require the ownTransaction exception for uncommitted changes.

Why the commit coordinator is usually somewhere else

This separation exists for a physical reason. The final commit coordinate does not exist when the transaction modifies its first row. By commit time, one transaction may have changed millions of rows, and many dirty pages may already have left memory. Revisiting all of them would make commit latency a function of transaction size and would undermine write-ahead logging's no-force rule: commit should make the log durable, not force every data page.

The common answer is indirection. A row version records a transaction marker or provisional time. Commit publishes the outcome and final coordinate in transaction or log metadata. Readers resolve the marker through that metadata; cleanup later may copy enough info into blocks or final versions to avoid lookup.

The implementations differ, but the pressure is the same:

• PostgreSQL tuples retain xmin/xmax. Commit status lives in pg_xact, and the optional pg_commit_ts side data maps XID to wall-clock commit time.
• Oracle rows refer through an ITL entry and XID to transaction-table metadata where commit records the SCN. Block cleanout can happen later.
• YugabyteDB first writes provisional intents. The status tablet records one final commit HybridTime, and asynchronous apply later creates regular records at that time.
• InnoDB rows retain DB_TRX_ID and DB_ROLL_PTR. An internal transaction serialization number is assigned near commit for purge ordering, but it is not copied into each row version.

This explains retention limits. If an engine keeps the XID-to-commit-time mapping as auxiliary metadata, it can age independently of the business row. Recovering an exact commit time years later differs from deciding visibility while the version history is still live.

PostgreSQL: pg_current_snapshot() is a visibility boundary

PostgreSQL documents the text representation of a pg_snapshot as xmin:xmax:xip_list. For example:

select pg_current_snapshot();

 pg_current_snapshot
---------------------
 10:20:10,14,15

The three components have precise meanings:

• xmin is the lowest transaction ID that was still active. Lower IDs have completed, either by committing or rolling back.
• xmax is one past the highest transaction ID that had completed. IDs at or above it had not completed at snapshot time and are invisible to this snapshot.
• xip_list contains the top-level transactions that were still in progress between the two horizons. It does not list subtransaction IDs.

An ID between xmin and xmax that is absent from xip_list has completed. Its commit status then says whether it is visible or dead. This is why the snapshot is a visibility boundary over transaction identities, not a timestamp and not a list of all committed transactions.

There is also an unfortunate name collision. Snapshot xmin and xmax are horizons. Tuple xmin and xmax are transaction IDs in a row-version header: the inserting transaction and, normally, the deleting or superseding transaction. The visibility algorithm relates them, but they are not the same field.

XID order is first-write order, not commit order

A PostgreSQL transaction initially has a virtual transaction ID. A normal 32-bit XID is allocated from a cluster-wide counter when the transaction first writes to the database. A read-only transaction may never get one. Calling pg_current_xact_id() forces allocation; pg_current_xact_id_if_assigned() does not.

The documentation makes the ordering guarantee narrow: a lower XID started writing before a higher XID. It may have started the SQL transaction later, and it may commit much later.

This schedule is possible:

T1 first write   ->   XID 100    ->   remains open
T2 first write   ->   XID 101    ->   commits
Reader snapshot  ->   100:102:100

The reader can see committed work from 101 while 100 is still invisible. A single high-water mark could not describe that state; the exception list is the important part.

This ordering also explains PostgreSQL's famous transaction ID wraparound problem. The XID stored in tuple headers is only 32 bits. Normal XIDs are compared with modulo-2³² arithmetic, so any XID has about two billion values considered older and two billion considered newer. VACUUM must freeze sufficiently old tuple versions before they cross that half-range and appear to come from the future. PostgreSQL's public xid8 adds an epoch for observation, but ordinary heap tuple headers still carry the compact 32-bit XID.

Read time

At READ COMMITTED, each command starts with a new snapshot. Two SELECT statements in one transaction can therefore see different commits. At REPEATABLE READ and SERIALIZABLE, the transaction keeps the snapshot chosen for its first non-transaction-control statement. In all cases, the current transaction's earlier commands require additional self-visibility and command ID rules that are not serialized in the public xmin:xmax:xip_list string.

PostgreSQL can export this read point with pg_export_snapshot() and import it in another transaction with SET TRANSACTION SNAPSHOT. The token remains valid only while the exporting transaction stays open. Parallel pg_dump uses synchronized snapshots so all workers see identical contents, and pg_dump --snapshot can align a dump with another session or a logical replication slot. This is often the right coordinate for comparing a source and target during migration: first agree on the state being compared, then compare the rows.

Update time

An UPDATE normally marks the old tuple with the updater's XID in xmax and creates a replacement tuple with that XID in xmin. The transaction also emits WAL records for WAL-logged storage. At this point, another transaction cannot infer a commit time from the tuple. It sees an XID whose status may still be in progress, committed, or aborted.

Commit time and WAL time

PostgreSQL's pg_lsn is a 64-bit byte position in the WAL stream. WAL records are appended, and their insert positions increase monotonically. The following three positions are deliberately distinct:

select pg_current_wal_insert_lsn(),
             pg_current_wal_lsn(),
             pg_current_wal_flush_lsn();

• The insert LSN is the logical end after records have been inserted into shared WAL buffers.
• The write LSN is how far those buffers have been written out.
• The flush LSN is how far PostgreSQL knows the WAL is on durable storage.

An LSN sampled after an UPDATE does not identify the visibility of that update. Other backends write to the same WAL stream, so their records can be between this transaction's records. The tuple itself does not store its WAL LSN.

There is an important qualification to the slogan "an LSN is only a byte position." For a write transaction, the position of its commit record determines its order among other records in the WAL stream. PostgreSQL's logical decoding API provides a commit_lsn, and the documentation states that concurrent transactions are decoded in commit order.

So these are both true:

• A generic current LSN is not a transaction snapshot or a commit time.
• The LSN of a specific commit record is a useful order for committed change streams.

That order still does not say which client received its success response first. Group commit can flush several commit records together, and process or network scheduling can reorder the replies. With synchronous_commit set to off, PostgreSQL can report success before that commit record reaches durable storage. Logical decoding waits until the transaction has safely been flushed.

PostgreSQL marks the XID committed in pg_xact. If track_commit_timestamp is on, which is not the default, it also retains a wall-clock commit timestamp that can be queried with pg_xact_commit_timestamp(). The mapping is stored separately under pg_commit_ts and is WAL-logged for recovery and physical replication. It is not added to tuple headers, and vacuum routinely removes old entries once their XIDs are no longer needed. This is optional historical metadata, not the MVCC snapshot coordinate and not a permanent audit trail.

Replication adds more positions, not a global clock

Physical streaming replication turns one WAL position into a pipeline. On the primary, it inserts, writes, and flushes a record. A standby then receives, writes, flushes, and replays it. pg_stat_replication exposes the standby's write_lsn, flush_lsn, and replay_lsn as reported to the sender.

flowchart LR
    I[Primary insert] --> W[Primary write]
    W --> F[Primary flush]
    F --> R[Standby receive]
    R --> SW[Standby write]
    SW --> SF[Standby flush]
    SF --> A[Standby replay]
    A --> V[Visible to standby queries]

The synchronous_commit mode selects which boundary a committing session must wait for. In the usual synchronous-standby configuration:

These modes change acknowledgment and durability, not the transaction's MVCC snapshot. They also explain why "committed" needs a subject: committed in the primary's transaction state, durable locally, durable remotely, and visible on a standby are distinct observations.

PostgreSQL 19, still in beta as I write this, makes those boundaries directly waitable:

WAIT FOR LSN '0/306EE20';
WAIT FOR LSN '0/306EE20' WITH (MODE 'standby_flush', TIMEOUT '5s');

The default standby_replay mode is useful for read-your-writes on an asynchronous replica. Other modes wait for standby write, standby flush, or primary flush. This does not turn the LSN into an MVCC snapshot: the client must capture the relevant primary LSN, and WAIT FOR compares its numeric value without identifying the timeline. Promotion therefore requires the application to reconsider whether the token still belongs to the expected history.

After failover, an LSN alone is not a universal history identifier. PostgreSQL creates a new timeline when recovery diverges; positions before the fork share history, while post-fork records are identified by their timeline and LSN. Logical replication has another namespace: replication origins can remember a source LSN and source timestamp for replayed transactions, but those values remain coordinates of that source, not a new global commit clock.

Two-phase commit does not create one either. PREPARE TRANSACTION preserves a local XID under a caller-supplied global transaction identifier (GID); its changes remain invisible until COMMIT PREPARED. An external coordinator can use matching GIDs at several databases to obtain an atomic outcome, but each PostgreSQL cluster still has its own XIDs, WAL timelines, LSNs, and clocks.

Finally, now() is not commit time either. It is transaction_timestamp(), fixed at transaction start. statement_timestamp() marks receipt of the current command, and clock_timestamp() reads the changing wall clock. A default such as updated_at default now() therefore records neither the physical update instant nor the commit instant of a long transaction.

Oracle: one SCN family, but not one coordinate

Oracle's SCN is much closer to the single logical clock people often look for. Oracle defines it as a monotonically increasing logical timestamp that orders database events. The same concept appears in several places:

• a query SCN identifies the consistent point a statement must read;
• a transaction has a start SCN and change SCNs;
• commit generates and records a commit SCN;
• block and data-file checkpoint SCNs bound recovery work;
• Flashback and point-in-time recovery accept SCNs.

Those are related SCN values, not one value assigned at BEGIN and reused for every purpose.

Read time

At Oracle's default READ COMMITTED, a query is consistent to the SCN at which the statement opens. At SERIALIZABLE or READ ONLY, queries use the transaction's read point. If a current block contains changes that are too new, Oracle copies the block and applies undo to build a consistent-read clone.

The SCN still cannot be the entire visibility rule. A session must see its own uncommitted update and exclude another session's uncommitted update, even if both are reading with the same query SCN.

The useful difference is that Oracle exposes the SCN as a historical read coordinate. AS OF SCN asks for the committed state at one point, while VERSIONS BETWEEN SCN returns committed row versions over an interval:

select * from orders as of scn :read_scn;

select versions_startscn, versions_endscn, versions_xid, status
from orders versions between scn :scn_a and :scn_b;

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER can set the same read point for ordinary queries in a session. These features depend on retained undo, or on Flashback Archive when configured for longer history.

Update time

Oracle allocates a transaction ID at the first DML statement, when it assigns an undo segment and a transaction-table slot. The XID encodes:

undo segment number : slot number : sequence number

An update stores the old values in undo and records transaction information in the data block's interested transaction list (ITL). Rows changed by that transaction refer to its ITL entry. The ITL points through the XID and undo block address (UBA) to the transaction table and undo chain. The number of ITL entries is limited per block. Applying undo to restore a consistent read snapshot also restores previous ITLs, so the list virtually covers undo retention.

Commit time

At commit, Oracle generates a commit SCN and records the committed state in the undo segment's transaction table. LGWR writes the remaining redo and the transaction SCN to the online redo log. By default, the client waits for that redo to be durable; asynchronous commit options can weaken that coupling. Data blocks do not all have to be written at commit.

Oracle may clean transaction information from modified blocks during commit. If it does not clean a block, a later reader finds the XID in the ITL, checks the undo segment header for the transaction's status and commit SCN, and performs delayed block cleanout.

We can summarize it as: The Oracle transaction ID identifies the transaction-table entry from which a reader can discover commit status and commit SCN.

But "just an identifier" hides useful work. The XID also locates the undo segment and slot, distinguishes slot reuse through its sequence number, identifies row-lock ownership, and lets a transaction recognize its own changes. Once the transaction is committed, the commit SCN supplies the logical ordering test.

Putting a commit SCN in a table is a special operation

This indirection is what keeps Oracle commit fast. The database can publish the outcome in the undo-segment transaction table and make the redo durable without visiting every changed row or forcing every dirty block. Transaction-table slots and undo are reusable, and delayed cleanout is visibility machinery, not an indefinite audit history of exact row commit times.

Oracle does have a fascinating exception: USERENV('COMMITSCN'). It is absent from the general USERENV parameter list, but Oracle's current error reference documents two unusually strict rules:

• it may be invoked only once in a transaction (ORA-01721);
• it must be a top-level expression in an INSERT ... VALUES clause or the right-hand side of an UPDATE assignment (ORA-01725).

While COMMIT_SCN was used for trigger-based logical replication before Oracle acquired redo-based GoldenGate, Oracle Database 23.26.2's DBVERIFY executable is still a concrete consumer:

create table SYS_DBV<pid>$ (myscn number);
insert into SYS_DBV<pid>$ values (userenv('COMMITSCN'));
-- OCITransCommit
select myscn from SYS_DBV<pid>$;
drop table SYS_DBV<pid>$;

It reads the committed value back as an Oracle NUMBER, converts it to a packed SCN, and puts it in the verification context used by block checks. A live SQL trace and TKPROF run confirmed the lifecycle. Between the insert and commit, the server recursively executed UPDATE SYS_DBV<pid>$ SET MYSCN=:1 WHERE ROWID=:2, affecting the inserted row; DBVERIFY then selected the value, locked the table exclusively, and dropped it. This is an operational SCN boundary, not a feature self-test.

The 23.26.2 server binary also names a precommit ... commit scn patch callback. Together, those clues describe something very different from evaluating SYSDATE: one selected stored value is patched with the SCN known on the commit path. The restrictions are also the point. Oracle can arrange this for one explicit target; doing it implicitly to every ordinary row a transaction touched would destroy the fast-commit design.

Commit-SCN materialized-view logs apply the same idea at a system-maintained boundary. CREATE MATERIALIZED VIEW LOG ... WITH COMMIT SCN chooses them over timestamp-based logs. Current server strings show log rows carrying XID$$ and refresh SQL joining that XID to SYS.SNAP_XCMT$, whose observed columns map XID to COMMIT_SCN:

many MLOG$ change rows --XID$$--> one XID / COMMIT_SCN mapping

That is a scalable commit-time join, not a rewrite of the base-table rows. The mapping is maintained and purged for materialized-view refresh. It should not be treated as a permanent application audit table.

This is also separate from XStream and GoldenGate-style capture. XStream was introduced in 11g Release 2, and mines redo into logical change records delivered in committed transaction order. Commit-SCN materialized-view logs were added in 12c Release 1 for fast refresh. One did not simply replace the other: they are different consumers of commit ordering through different capture paths.

Oracle also has redo addresses

SCN is not a byte address in redo. V$LOGMNR_CONTENTS exposes the separation particularly well. For one mined change, it can report:

• SCN, START_SCN, and COMMIT_SCN for logical database time;
• XIDUSN, XIDSLT, and XIDSQN for transaction identity;
• UBAFIL, UBABLK, and UBAREC for the undo record;
• RBASQN, RBABLK, and RBABYTE for the redo byte address (RBA).

Oracle's term "log sequence number" names the generation of a redo log file. The RBA adds a block and byte offset to locate an individual redo record. In this sense, PostgreSQL's pg_lsn is closer to an Oracle RBA than to an Oracle SCN.

An SCN is also not an exact wall-clock timestamp. SCN_TO_TIMESTAMP() returns an approximation, usually with three-second precision, and the database retains the mapping for a limited period. ORA_ROWSCN is another trap: it is block-level unless the table was created with ROWDEPENDENCIES; only then does Oracle maintain row-level dependency information. Even in that fine-grained mode, ORA_ROWSCN is only a conservative value greater than or equal to the last modifying transaction's commit SCN, not necessarily that exact SCN. Flashback Version Query uses its own VERSIONS_* pseudocolumns instead.

RAC and distributed databases solve different clock problems

Oracle RAC has several instances opening one database. They must coordinate one database SCN domain across the cluster interconnect. Oracle has changed the implementation over time: older releases exposed MAX_COMMIT_PROPAGATION_DELAY, while current binary strings still name a "broadcast-on-commit SCN mode."

Database links connect independent databases, and the documented guarantee is weaker. Oracle says each system has its own SCN. The systems synchronize their SCNs at the end of each remote SQL statement and at the start and end of each transaction, but cannot keep them absolutely synchronized. A gap can therefore produce a remote read that is consistent yet slightly out of date. A dummy remote query or a transaction boundary forces another synchronization point.

Distributed two-phase commit adds common identity and outcome, not a permanent global clock for all work at all sites. The global transaction ID is the same across participants, and a commit-point site records the decisive outcome. For an in-doubt transaction, DBA_2PC_PENDING.COMMIT# exposes what the documentation calls its global commit number; COMMIT FORCE can even reuse the SCN observed at a site that already committed. That synchronizes the resolution of one distributed transaction. It does not give unrelated transactions in independent databases a total order.

A monotonic coordinate still has a finite representation

There is also a naming fossil. Modern Oracle documentation expands SCN as System Change Number, but the 23.26.2 Instant Client still carries the old ORA-08209 explanation: "The System Commit Number has not yet been initialized." This directly shows that System Commit Number existed in Oracle's terminology and survived in old error text. It is not enough to date a formal rename or to claim that early SCNs represented commits only.

Monotonic does not mean infinite. Oracle 12.2 increased the SCN capability: the documented ORA-24442 is raised when a newer database tries to transfer an SCN that exceeds what a pre-12.2 database or client can represent. Current binaries call this BigSCN, track SCN headroom, and include compatibility rollover paths. This differs from PostgreSQL XID wraparound because the SCN is a forward-moving logical time rather than a circular tuple identity. But RAC coordination and database-link synchronization can propagate higher observed SCNs, so capacity and compatibility are distributed-system concerns, not merely local counters.

YugabyteDB: commit HybridTime becomes the MVCC time

I include YugabyteDB because I have also worked with it, and its more modern distributed architecture makes time part of the consistency protocol, not just a diagnostic label. DocDB stores versions in an LSM tree whose key ends in a HybridTime. A hybrid logical clock (HLC) has a physical and a logical component. It follows causal order and is monotonic on each node, but its physical component should not be confused with a perfectly synchronized wall clock.

Read time

A distributed read chooses a hybrid time ht_read. Each tablet waits until that point is safe to read and normally includes a version when ht_record <= ht_read. Clock uncertainty can reveal a record that might have preceded the request even though its HybridTime is above the first read point; YugabyteDB then advances the read time and restarts the read. This is why the read protocol also carries safe time and local/global limits.

YSQL can also synchronize read points across sessions with the PostgreSQL-compatible pg_export_snapshot() and SET TRANSACTION SNAPSHOT syntax. The exporting transaction must remain open, and current YugabyteDB documentation limits export/import to REPEATABLE READ. This shares one distributed snapshot; it is not arbitrary historical time travel.

Update time: provisional HybridTimes

A distributed transaction does not put uncommitted values directly beside regular visible values. It writes provisional records to a separate RocksDB instance named IntentsDB. The documented primary-intent shape is:

DocumentKey, SubKeys..., LockType, ProvisionalRecordHybridTime
        -> TxnId, Value

The provisional HybridTime is not the commit time. Different intents in one transaction generally have different provisional HybridTimes. The transaction UUID ties them to one status record and lets the transaction see its own intents; other readers do not treat pending intents as committed values.

Commit time: one final HybridTime

The transaction manager asks a transaction status tablet to commit. That tablet chooses the current HybridTime while appending the committed status to its Raft log. Once the status change is replicated, the transaction has one commit HybridTime and all its provisional records become logically visible.

A reader that encounters a not-yet-cleaned intent asks the status tablet. If the transaction committed, the reader treats the intent as if it were already a regular record at the final commit HybridTime.

Cleanup is asynchronous. Each participant later Raft-replicates an apply record containing the transaction ID and commit HybridTime, removes the provisional records, and writes regular records to RegularDB with that final HybridTime. We can summarize it as: YugabyteDB stores a timestamp in both provisional and regular records, but the provisional write timestamp and the final commit timestamp are different.

Raft log positions remain a separate coordinate. Each tablet, including the status tablet, has its own Raft log and operation order. No single cluster-wide Raft byte position exists, analogous to a PostgreSQL WAL LSN. HybridTime provides the cross-tablet MVCC coordinate, while Raft provides replicated order and durability inside each tablet group.

HybridTime orders causality, not an omniscient wall clock

The HLC guarantee is precise. Events on one server receive increasing HybridTimes. If event A sends an RPC that leads to event B on another server, the clock value travels with the message, and B receives a greater HybridTime. That covers causal chains.

The implementation packs HybridTime into an unsigned 64-bit value: physical microseconds occupy the high bits, and 12 low bits hold the logical component. If the logical component fills, YugabyteDB carries it into the physical part. The source notes microsecond accuracy through 2100 and beyond. The 64-bit width provides headroom, but the clock algorithm is what guarantees monotonicity: when the physical clock goes backward, the last physical component is retained and the logical component advances.

Two nodes that have exchanged no relevant messages can still have physical clock skew. Their HLC values are comparable as tuples, but that numeric order does not prove a causal relationship or exact wall-clock order between the independent events. Once they communicate, the lower clock advances to the higher observed value.

This is why the read protocol cannot simply sample any node's HLC and declare the result complete. It calculates a global_limit from physical time plus the configured maximum clock skew, waits for tablet safe time, and restarts when it encounters a possibly earlier event above the chosen read point. Together, the timestamp and the uncertainty protocol provide the guarantee.

SQL Server: XSN, LSN, and rowversion

SQL Server exposes almost every possible source of naming confusion.

First, its traditional READ COMMITTED uses locks. Statement snapshots appear when READ_COMMITTED_SNAPSHOT (RCSI) is enabled, and transaction snapshots appear at SNAPSHOT isolation.

For row versioning, SQL Server assigns a transaction sequence number (XSN) when a participating transaction first accesses the version store. For a SNAPSHOT transaction, the engine also records the transactions active at the snapshot. It follows a row's version chain to the newest version whose XSN is below the reader's sequence and was not in that active set. This is conceptually close to PostgreSQL's horizons plus in-progress transactions.

RCSI chooses a new sequence point for each statement. SNAPSHOT keeps the transaction-level view. On update, SQL Server stores the previously committed row image in the version store and links the current row to it. Row-version metadata includes a transaction sequence number and a version pointer. Writers still use write locks, or transaction-ID locks with optimized locking.

SQL Server keeps these identifiers separate:

• transaction_id primarily identifies the transaction for locking and is unique only within an instance;
• transaction_sequence_num (XSN) identifies a transaction in row versioning and participates in snapshot visibility tests;
• LSN identifies a record in one database's transaction log.

Every new log record has a higher LSN than the preceding record. The DMV sys.dm_tran_database_transactions exposes begin, latest, savepoint, and commit LSNs for a database transaction. Change Data Capture makes the meaning especially explicit: __$start_lsn is the commit LSN, groups changes from one transaction, and orders transactions; __$seqval orders changes inside it. CDC stores a separate mapping from commit LSN to commit wall-clock time.

This is a more directly exposed version of the PostgreSQL qualification: an LSN is a log position, and the commit record's position can be used as commit order. It is still not the XSN snapshot boundary or a wall-clock time. A fully durable commit flushes the log before completion; delayed durability can return before it hardens the log.

Finally, the SQL Server rowversion data type is unrelated to all of this. It is an eight-byte database counter placed in rows that declare such a column. It advances on INSERT or UPDATE, even when values are unchanged. It is useful as an optimistic concurrency token, but it is neither a timestamp nor a commit sequence, and the deprecated synonym timestamp makes the name worse.

MySQL/InnoDB: a read view, an undo chain, and two logs

"MySQL" can use different storage engines, so this comparison focuses on InnoDB.

An InnoDB consistent read sees changes committed before its read point, excludes later and uncommitted transactions, and includes its own earlier statements. At the default REPEATABLE READ, the first consistent read establishes the transaction's snapshot. At READ COMMITTED, each consistent read gets a fresh snapshot.

Internally, the read view records transaction-ID limits and the active write transactions. It is the same broad strategy as PostgreSQL and SQL Server snapshot versioning: transaction assignment order plus an active exception set, not a commit timestamp in each row.

InnoDB updates clustered-index records in place. Each record has:

• DB_TRX_ID, the six-byte ID of the last transaction to insert or update it;
• DB_ROLL_PTR, a seven-byte pointer to the undo record from which an older version can be reconstructed.

At update time, the writer has an InnoDB transaction ID, creates undo, generates redo, changes the current record, and holds the conflicting lock. The ID does not become a commit timestamp when the transaction commits. Commit changes its status and makes the version eligible for new read views.

There is a second internal number, but it is easy to over-translate it. MySQL 8.4 source defines trx->no as a transaction serialization number, initially TRX_ID_MAX, assigned shortly before the transaction moves to COMMITTED_IN_MEMORY. InnoDB puts update undo into history in this order, and a read view's m_low_limit_no tells purge which older transaction histories no view still needs.

This is a commit-near ordering horizon, not a miniature Oracle SCN. It is not stored in clustered records, exposed as a stable application token, or used as wall-clock time. The source even notes that transaction numbers need not follow commit LSN order exactly when transactions use different rollback segments, although causal visibility still preserves the necessary order.

InnoDB's redo log has an ever-increasing LSN. MySQL 8.4 exposes current, flushed-to-disk, and checkpoint LSNs. As in PostgreSQL, redo from concurrent transactions can interleave. The LSN tracks recovery progress, not the read view or a row's commit time.

MySQL then adds a second log at the server layer. The binary log is used for replication and point-in-time recovery. MySQL caches a transactional workload and writes it there as a unit at commit. With the default binlog_order_commits=ON, storage-engine commits are serialized in binary-log order. If it is disabled, transactions in one group may commit in an order different from their binary-log positions.

When GTIDs are enabled, a binary-logged client transaction receives a value of the form:

source_uuid:sequence_number

The sequence number follows commit order on that source. It is an excellent replication identity and ordering coordinate, but it is not DB_TRX_ID, is not stored in each InnoDB row version, and does not define one scalar order across unrelated source UUIDs. original_commit_timestamp is separate wall-clock metadata propagated by replication.

The MySQL server coordinates its binary log and InnoDB through internal two-phase commit. Durability therefore depends on both sides, notably sync_binlog and innodb_flush_log_at_trx_commit, rather than on the MVCC transaction ID.

MongoDB/WiredTiger: three time domains in one stack

MongoDB belongs in this comparison because it was designed around replication, while WiredTiger offers a distinct OLTP storage choice underneath it. The database server and its storage engine do not expose the same time abstraction. At least three time domains coexist:

1. $clusterTime and operationTime are logical causal tokens returned to clients;
2. oplog OpTime orders replicated operations within a replica-set history;
3. WiredTiger transaction IDs and timestamps determine storage-engine visibility and history.

They often carry related BSON Timestamp values, but their roles are not interchangeable.

Read and update time

MongoDB's logical clock is Lamport-like. Servers and drivers gossip $clusterTime, advancing it when they observe a later value. Its BSON Timestamp contains seconds plus an ordinal, but it is an ordering token, not an elapsed-time measurement. operationTime lets a client carry the logical time of an acknowledged operation into a causally dependent one.

A read with read concern "snapshot" uses one atClusterTime. Outside a multi-document transaction, a client may supply it; otherwise, mongos, or a single-member replica set, selects a recent majority-committed snapshot. The storage engine implements that point using a WiredTiger read timestamp and a transaction snapshot.

WiredTiger first gives a writing transaction an internal transaction ID and puts each modification on an in-memory update chain. Snapshot visibility checks both that ID and, for timestamped data, the update's commit timestamp. An ordinary update is initially uncommitted, not automatically prepared. Prepare timestamp and durable timestamp are additional states used only when a transaction actually enters the prepared protocol.

For ordinary unprepared transactions, WiredTiger is no-steal at the transaction level: writes first live in memory and are not written to disk before the whole transaction commits. Rollback can mark those in-memory updates aborted instead of physically undoing pages. The tradeoff is a hard cache constraint. MongoDB aborts an uncommitted transaction that creates excessive WiredTiger cache pressure, and returns TransactionTooLargeForCache for a transaction too large to ever fit. Prepared transactions are a separate protocol with additional persistence rules; they should not be used to describe every ordinary update.

The visible BSON document contains none of this metadata. It has no automatic transaction ID, read timestamp, or commit timestamp field. An ObjectId may encode approximate client-side creation time, and an application may add updatedAt, but neither is database commit time.

Commit, oplog, and durability

On a replica set, the oplog is the ordered history of logical writes. Its ts field is a BSON Timestamp; MongoDB guarantees oplog timestamp uniqueness within one mongod. An OpTime pairs that timestamp with the election term:

OpTime = { ts: Timestamp(seconds, ordinal), t: election_term }

MongoDB supplies logical timestamps from this domain to WiredTiger as commit timestamps for replicated changes. WiredTiger then installs the timestamp on the transaction's internal updates; reconciliation can persist it in an on-disk time window. Multi-document transactions may package many changes into applyOps records, so an oplog entry is not necessarily one BSON document change.

A transaction spanning shards adds distributed prepare and commit coordination. Participants can prepare at different timestamps, and the coordinator chooses one commit timestamp that makes the transaction visible across its participants. Each shard still has its own replica-set oplog; no single byte position covers the whole sharded cluster.

Replica-set status makes the pipeline visible through distinct applied, written, durable, and majority-committed OpTime values. WiredTiger also has a journal LSN for local crash recovery. That LSN is not the oplog token used by replication or change streams. The oplog's separate wall dates and status fields such as lastCommittedWallTime are wall-clock observations, not substitutes for OpTime.

What survives later?

WiredTiger does retain timestamp metadata internally while versions need it. In-memory updates have transaction and timestamp fields. The current on-disk value can carry a time window, and the history store keys older values by B-tree, record key, start timestamp, and a uniqueness counter; its value also carries stop and durable timestamps. None of that becomes a queryable field in the BSON document.

The retention boundaries have different names:

• the oldest timestamp is the earliest point at which the application may start a new timestamped read;
• the pinned timestamp also accounts for already-running readers and is the real garbage-collection floor;
• the stable timestamp is the upper boundary of the state considered stable. Rollback to stable removes updates beyond it after rollback or recovery.

History-store versions disappear when no supported read can need them. Oplog entries disappear when the capped oplog rolls past its retention window. A regular document can therefore outlive every system-maintained path from that document to its original commit OpTime. Long-term audit still requires an application field or a separately retained change history.

The same questions, side by side

The table deliberately avoids forcing one-to-one equivalence. Oracle's commit SCN and YugabyteDB's commit HybridTime participate directly in MVCC time. PostgreSQL's commit-record LSN, SQL Server's CDC LSN, and MySQL's GTID are useful for change streams, but they don't make the snapshot stored by a reader.

Can a regular row tell me its exact commit coordinate later?

Usually not. "The engine used this metadata" and "the application can recover it forever from the current row" are very different statements.

The "later" in that question matters. MVCC metadata is retained to serve active or supported historical reads; logs are retained to satisfy recovery and replication policy. Neither lifetime automatically matches an audit requirement.

Wall-clock time is another coordinate

Wall-clock time is useful for audit and diagnosis, but it is a poor substitute for transaction order:

• PostgreSQL now() is transaction start; optional commit timestamps are separate and retained only for a limited transaction-ID horizon.
• Oracle SCN-to-timestamp conversion is approximate and retained for a limited time.
• YugabyteDB HybridTime embeds a physical component but also a logical counter and clock-uncertainty protocol.
• SQL Server CDC maps commit LSN to tran_end_time rather than pretending the LSN is a date.
• MySQL propagates an original commit timestamp separately from GTID, binlog position, InnoDB transaction ID, and redo LSN.
• MongoDB exposes wall-clock dates beside oplog and replica-status OpTime values; the BSON timestamp's seconds-plus-ordinal representation remains a logical replication coordinate.

Two wall-clock readings can be equal, and clocks can be corrected. A client can receive commit responses in an order different from the log's commit records. An updated_at value is normally evaluated while the statement runs, before commit. If an application needs both explanation and deterministic processing, store the timestamp for the intended business event and consume changes with the engine's transactional ordering coordinate.

The practical rule

Before comparing two database numbers, name the promise you need:

1. For repeatable visibility, keep or export a database snapshot.
2. For change-stream order and restart, keep a commit LSN, binlog position/GTID, or the database's CDC token.
3. For durability, wait for the relevant WAL, redo, or Raft flush/apply position required by the configured policy.
4. For optimistic application updates, use an explicit version token and do not call it commit time.
5. For human audit time, store a timestamp, but keep a transactional token as the tie-breaker when order matters.
6. For ordering across independent systems, use a protocol that propagates source identity and causality. Do not compare unrelated XIDs, LSNs, SCNs, oplog positions, or wall clocks as if they shared a namespace.

PostgreSQL's snapshot and WAL LSN are kept separate because visibility and recovery are two distinct processes. This isn’t just about missing metadata. It’s fundamental to how PostgreSQL is designed. WAL recovery updates the physical database by reapplying logged page changes, while MVCC visibility is determined afterward based on heap tuple transaction markers, transaction status, and the reader's snapshot. Importantly, recovery doesn’t need to process every unfinished transaction or undo heap changes before the database can show a consistent state.

That separation benefits the conservative recovery approach. It ensures recovery happens only after a system failure, making a smaller contract more valuable—especially in an open-source database that runs across various operating systems, filesystems, storage solutions, extensions, and support models. It also reduces dependencies for extensions. Usually, a new data type or operator class can reuse existing heap MVCC and index access methods without creating new transaction visibility or crash recovery mechanisms. PostgreSQL indexes typically point to heap tuples and do not contain visibility data themselves. Instead, index-only scans refer to the heap's visibility map.

The boundary is not magic. A genuinely new table or index access method may need its own WAL and visibility work. PostgreSQL provides generic WAL records and custom WAL resource managers for that purpose. The extensibility benefit is that these responsibilities are explicit and localized, not that recovery is free.

Oracle handles many logical ordering challenges in the SCN domain, but XID, undo, and redo addresses are still important. YugabyteDB keeps a final temporal coordinate with committed versions because distributed MVCC requires it, while Raft order stays local to each tablet. SQL Server and InnoDB demonstrate even more valid combinations of these elements. MongoDB/WiredTiger presents them all together in a single stack: logical cluster time, replication OpTime, internal MVCC timestamps, and a separate journal position.

A transaction does not happen at one time. It crosses several boundaries, and each database gives those boundaries different names.

References

This article has been heavily reviewed by AI from the following sources.

... (truncated)

Focusing on Percona Server 8.4.11-11 My previous post (Performance Progression of Percona Server for MySQL 8.4) did a brief review of the performance changes in Percona Server for MySQL 8.4 released in 2026. I recommend reading it first to better understand the material in this post. Version 8.4.11-11 includes patches that deliver significant improvements in … Continued

The post Performance improvements in Percona Server 8.4.11-11 appeared first on Percona.

Percona and HexaCluster have partnered to remove the hardest part of an open source database migration: getting off Oracle, SQL Server, DB2 or Sybase ASE with confidence, on a predictable timeline, without a multi-year consulting program. Percona brings open source expertise, its own distributions, operators and enterprise support. HexaCluster brings the assessment and migration engineering … Continued

The post Percona and HexaCluster: Faster, Safer Oracle Migration appeared first on Percona.