General methods that scale with computation will inevitably displace hand-engineered domain knowledge! Sutton’s Bitter Lesson hangs like a Sword of Damocles over all of us. This paper, which just dropped, applies that lesson to data agents, and says that as LLMs improve, they will quickly absorb the agent scaffolding researchers have spent the last few years painstakingly building. It argues that researchers should instead work on building curated contextual information about the data environment (aka. persistent semantic context) to help data agents be efficient across many queries.

The Bitter Lesson for Data Agents

The evaluation section aims to capture the Bitter Lesson in action. The authors compared general coding agents (the Codex harness with no task-specific engineering) against state-of-the-art human-designed data agents on two benchmarks, TAG-Bench and DAB. They use the same models on both sides, so the only variable is the scaffolding.

They find that:

• Although the human-designed Agentar-Scale-SQL won on accuracy and token efficiency with o3, when using GPT-5.6 Sol that flips and the plain coding agent surpasses on both counts. Bam!
• The number of back-and-forth turns an agent needs to solve a query drops from 23.2 with o3 to 6.0 with GPT-5.6 Sol. The newer models get the analytical logic right quickly, skipping the trial-and-error steps. Kaboom!

Side Remark: Interestingly, the authors use these efficiency gains to bury their own prior work (in this case Ion and Matei's) from just a year ago. "Supporting Our AI Overlords" argued database systems would be overwhelmed by "agentic speculation": massive bursts of inefficient queries from confused models. Now they say the opposite, that models formulating correct answers efficiently "directly challenges the premise of recent work [11]". Them are fighting words. Why bury your own work when others would happily do it for you? For the record, I still think the direction in the Overlords paper is valid, and we should work on designing data systems for bursty AI workloads. Cheaper per query is not the same as less work for the database. This is Jevons paradox. If a query costs a fraction of what it used to, we will point many more agents at the data, running longer tasks, in parallel, in the background. Secondly, the main point of the Overlords paper was that queries are varied, yet our databases are built for repetitive workloads. Fewer turns per query does not make the queries look any more alike, so the load still arrives in bursts, and data systems will still need to be designed for it.

Ok, those efficiency gains are splendid, but they also expose new bottlenecks. Schema exploration increases from 16% of the turns with o3 to 25% with GPT-5.6 Sol. While it shrank in absolute terms, it now becomes the biggest remaining slice. The failure analysis also says the same thing. Once execution and coding errors are largely eliminated, over 60% of the remaining failures for GPT-5.6 Sol are semantic mistakes, such as not knowing the organization's idiosyncratic business definitions, join keys, or undocumented schemas. The model got smarter, but it still doesn't know about the intricacies of your data environment.

This motivates their research agenda proposal, which they test at small scale. When the authors let the AI "self-curate" a persistent context document by exploring the databases and 12 sample queries, and injected that text into the initial prompt, agent accuracy jumped by up to 19 percentage points.

But these improvements come with a price tag. Table 2 shows the cost of building these contexts, and some of them are very expensive. In their tiny setting (just 12 sample queries and 12 datasets) the schema-focused context took nearly an hour (3,400 seconds) and $9.60 to build, and GEPA cost $12.16. If you extrapolate that to an enterprise environment with terabytes of data and thousands of tables, the overhead becomes astronomical.

The Proposed Research Agenda

To address these overheads and the model's lack of environmental knowledge, the authors propose building a persistent semantic context layer. This context would be built offline, stored by the database system, and served to the agent to prevent redundant exploration. Section 3 outlines this agenda, dividing the problem into maintaining semantic consistency and designing efficient physical data structures.

Unfortunately, this section is the weakest part of the paper. Even when taking into account this is a position paper, the proposal remains superficial. It lists high-level categories like "consistency scope" or "consistency models" without offering technical solutions or even analysis.

There may also be a deeper problem here. The paper itself shows that agents can author their own context offline. If we take the Bitter Lesson to heart, why do we need the database community to build the semantic context layer? With improved models, agents will likely figure out their own memory formats, how to keep them consistent, and how to lay them out on disk, which is all of Section 3. No?

How Do We Actually Implement the Semantic Context Layer?

I actually like this problem a lot. The problem is real, and the economics get worse the bigger the organization. The same thing shows up one level above, in software engineering. Here hundreds of engineers point models at a large existing codebase, and every session spends time/money again to rediscover the conventions and ownership boundaries the organization already knows. The costs become prohibitive quickly.

The paper outlines several architectural directions for implementing this persistent semantic context layer natively within future data systems. One approach is to build the layer as a dependency graph, where business definitions and schema rules are linked as traceable nodes. Another option is treating the semantic layer like a live materialized view, using database events to trigger targeted AI rewrites whenever the underlying data shifts. The idea is to shift AI memory from a static text file into a metadata-driven component of the database.

Of course, I have my thoughts and biases on this. I think TLA+ deserves serious consideration at this semantic context layer. As Boris Cherny recently highlighted, formal specification/verification languages like TLA+ should no longer be seen as niche. He showed how he uses Opus 5.5 to verify a codebase, finding hidden bugs and race conditions with TLA+ in just a few short prompts. These models are now fluent enough in TLA+ that you do not need to be an expert in the language to get value out of it.

While TLA+ is most famous for checking the interleaving and ordering of events in distributed systems, at its core it is just set theory plus temporal logic. That makes it versatile. It can model schema properties, define strict relations between data facts, and map out structural dependencies. More importantly, it enables you to capture not just static safety invariants, but also the temporal properties the system must guarantee over time.

This flexibility makes TLA+ a compelling answer to the paper's consistency problem. Instead of treating the persistent semantic context as Markdown text, an AI agent could continuously translate natural-language business rules into explicit TLA+ relations. If an underlying data-access protocol is modified or a core business metric is redefined, the system can use the TLA+ model to show exactly which downstream semantic dependencies break. Beyond keeping memory consistent, this formal foundation also helps the agent answer queries. Before executing a complex analytical query, the agent can check its proposed logic against the TLA+ invariants to rule out superfluous join paths. For example, it would immediately catch that querying for records where a "delivery timestamp" precedes an "order timestamp" violates a temporal invariant, and fix its filtering logic before ever touching the database.

One More Thing about the Bitter Lesson

I hate that the Bitter Lesson is so real, and so bitter. However, as I noted in my recent post on LLMs, these models excel at "high-throughput mediocrity". The Bitter Lesson applies best where statistical approximation is acceptable and the domain is flexible. When you need absolute correctness and performance, I still think human-engineered precision can hold the line. I still believe in the importance of good algorithms and abstractions. So, even if it proves futile, I keep looking for ways to push back against the Bitter Lesson, partly as an act of defiance, and partly as my sworn duty as a loyal member of the systems craft club. Grrr.

Which brings me back to the title: What happens when the model eats the stack?

I guess it takes a massive core dump!

Ba dum tss... I'll see myself out.

PS: My colleague Jesse's review of the paper is also worth reading.

Enabling data checksums is strongly advised to identify corruption originating from the storage or I/O layer, which can silently lead to incorrect query results. Although PostgreSQL performs basic sanity checks on the page header without checksums, it lacks a cryptographic verification of the page contents. As a result, many types of silent corruption could go unnoticed and produce inaccurate query outcomes.

In PostgreSQL 18, by default, data pages across database clusters include a checksum that is verified each time a page is read from disk and recalculated when written. Since checksum is for all databases in a cluster, this applies broadly. However, if you've upgraded from earlier versions, your databases likely lack checksums. You can add checksums using pg_checksums, but the database must be shut down, and can be a time-consuming process.

PostgreSQL 19 will support enabling checksums online, allowing them to operate in the background while the application remains active, possibly with throttling to lessen workload impact. Here's an example.

Corruption with checksums

To set up a demonstration database without checksums, I initialized it using the --no-data-checksums option with initdb:

podman run -d --replace                        \
 -e POSTGRES_PASSWORD=xxxxxxx                  \
 -e POSTGRES_INITDB_ARGS="--no-data-checksums" \
postgres:19beta3

I created a table with one row containing the 'Hello World!' text:

postgres=# create table hackme as select 'Hello World!' as value
;
CREATE TABLE

postgres=# select distinct value from hackme
;
    value
--------------
 Hello World!

(1 row)

I ensure the dirty page is written to and flushed from the shared buffers:

postgres=# checkpoint
;
CHECKPOINT

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

postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
            1200 |               0 |               0

(1 row)

There's no encryption in PostgreSQL, so the data is visible in the file:

postgres=# select current_setting('data_directory')||'/'||pg_relation_filepath('hackme'::regclass) as file
;
                    file
--------------------------------------------
 /var/lib/postgresql/20/docker/base/5/16454

(1 row)

postgres=# \gset

postgres=# \setenv file :file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello World!^@^@^@

postgres=#

With filesystem access, I can modify the data to simulate storage corruption:

postgres=# \! LC_ALL=C sed 's/World!/Hacker/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=#

When PostgreSQL reads the file again, it doesn't detect that the page was modified outside the instance and displays corrupted data:

postgres=# select distinct value from hackme
;
    value
--------------
 Hello Hacker

(1 row)

This is a major problem. Data can be corrupted at any layer below the PostgreSQL instance, and this corruption goes undetected.

Enabling checksums online

Without stopping the instance, I enable checksums:

postgres=# show data_checksums
;
 data_checksums
----------------
 off

(1 row)

postgres=# select pg_enable_data_checksums()
;
 pg_enable_data_checksums
--------------------------

(1 row)

The checksum process is in progress and you can track the updates:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname |  phase   | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
 101 |     0 |         | enabling |                 |                |                 |                |              |

(1 row)

postgres=# show data_checksums
;
 data_checksums
----------------
 inprogress-on

(1 row)

Over time, the database is protected by checksums:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+-------+-----------------+----------------+-----------------+----------------+--------------+-------------

(0 rows)

postgres=# show data_checksums
;
 data_checksums
----------------
 on

(1 row)

Checksums are enabled without any downtime.

Corruption with checksums

I do the same as before, flushing the shared buffers and modifying the file directly:

postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
             991 |               0 |               0

(1 row)

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=# \! LC_ALL=C sed 's/Hacker/Franck/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Franck^@^@^@

Now that each page has a checksum, a read detects any corruption:

postgres=# select distinct value from hackme
;
ERROR:  invalid page in block 0 of relation "base/5/16454"

You can now switch to a standby node or restore from a backup.

Detecting corruption early is crucial for successful recovery. pg_basebackup performs checksum verification:

\! pg_basebackup  -zFtar -D  /var/tmp/backup

WARNING:  checksum verification failed in file "./base/5/16388", block 0: calculated 764A but expected 163F
WARNING:  file "./base/5/16388" has a total of 1 checksum verification failure
WARNING:  1 total checksum verification failure
pg_basebackup: error: checksum error occurred

If you back up your database with a tool that doesn't check backups, validate the restore test with pg_checksums --check.

Conclusion

Data checksums are vital for PostgreSQL, transforming silent storage issues into detectable errors for investigation and recovery. Without them, corrupted data may go unnoticed, as page-header checks can't detect arbitrary page changes.

Enable checksums during cluster setup. For existing clusters, online activation avoids maintenance but isn't zero-impact:

• pg_enable_data_checksums() and pg_disable_data_checksums() need superuser access and must run on the primary. Changes are propagated to standbys via WAL.
• The operation uses two background-worker slots. Ensure max_worker_processes has enough headroom.
• It waits for open transactions and temporary tables in all databases. Long sessions or tables can delay indefinitely.
• Checksums are applied when enabling, but reads verify them only after final transition to on.
• A crash or restart during inprogress-on requires restarting from scratch.
• Standbys may need forced restart points, blocking WAL replay and causing lag, possibly stalling a primary. Reducing max_wal_size beforehand can mitigate this.

Checksums are best seen as part of a larger corruption-detection strategy: enable early, monitor transition, validate backups and replicas, and plan around transaction lifetimes and workload.

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';