ElyraSQL 1.12.0: Faster where it counts, and privileges that behave like MySQL's

Every release has a story, and this one started with someone else's. We'd been reading an article about pgrust: batching, operator fusion, SIMD, even a JIT for query expressions. The obvious question was: what can we steal? So before touching any code, we measured.

That measurement changed our plans. Behind a plain SELECT SUM(col) over 20 million rows, the profile looked like this:

• 32% in pread, the system call that reads pages from the file

• ~20% walking the B-tree

• ~10% decoding rows

• ~9% copying pages

The addition itself barely registered. We wrote the tightest possible loop over 20 million doubles as a benchmark: 12.5 ms with one accumulator, 2 ms with eight. The engine took over half a second. The lesson was humbling and useful: the arithmetic was never the problem. Everything around it was.

So 1.12.0 is mostly about "everything around it". Along the way we found some things that were plainly wrong, a few of them security-relevant, and fixed those too. Here's the tour.

Part 1: Making the fast paths faster

All the numbers below are from a 20-million-row table on an M4 Max, with a warm cache unless stated otherwise.

The page cache was a quarter of what it should be (and a ⟦TODO: heading truncated⟧

ElyraSQL stores data in redb, which keeps a cache of rece⟦nt pages. We used⟧ redb's default of 1 GiB. Our test table is 2.6 GB. When the table is bigger than the cache, a sequential scan finds n⟦othing it can reuse, so every⟧ query rereads every page through a system call. That happened even with the whole file sitting in the operatin⟦g system's page cache, on⟧ a machine with 128 GB of RAM.

The page cache now defaults to a quarter of the memory the⟦ machine has⟧. That's physical memory, or the container's cgroup limit when that is lower, so a 512 MiB container gets 128 MiB, not a gigabyte it doesn't have.

Query       Before    After
⟦TODO: first row missing⟧
COUNT(*)    461 ms    173 ms
GROUP BY    531 ms    251 ms

The cache fills only as pages are read, so a small databa⟦TODO: sentence truncated⟧re. ELYRASQL_PAGE_CACHE_MB still overrides it.

Expressions are compiled once, not interpreted twenty million times

This is where the pgrust article really helped, though not in the way we expected. We didn't build a JIT. What we found was simpler and sillier: for every row, the evaluator resolved each colum⟦n by name (into⟧ a fresh Vec), re-parsed every literal from text, and re-derived each comparison's collation.

Now an expression is compiled once per scan into a small tree with column indexes and parsed values. Operators still run through exactly the same code as before, so results and errors are identical; ⟦a test compa⟧res both paths across 24 expression shapes, error for error.

Query                          Before     After
SUM(col * 2 + 1)               1071 ms    697 ms
WHERE a > x OR b < y           1103 ms    664 ms
A filtered streaming SELECT    4.1 s      3.0 s

A primary-key range scans just that range

This one embarrassed us a little:

SELECT SUM(col) FROM t WHERE id > 10000000;

Half the table. It took three times longer than summing t⟦he whole table. The old⟧ path fetched every matching row into memory, decoded and held each one, and then aggregated them on one core. It n⟦ow runs the same⟧ decode-in-place scan as a full-table aggregate, over only the keys in range.

Query                          Before     After
SUM ... WHERE id > 10M         1761 ms    281 ms
COUNT(*) ... WHERE id > 10M    1731 ms    278 ms
Range GROUP BY                 1972 ms    477 ms

The columnar GROUP BY path had the same blind spot, and it was worse: it scanned the entire table no matter what the key filter said.

SELECT g, SUM(col) FROM t WHERE id = 777 GROUP BY g;   -- 200 ms → 0.2 ms
SELECT g, SUM(col) FROM t WHERE id > 15000000 GROUP BY g;

Two assumptions that turned out to be wrong

The columnar path, which extracts numeric columns into a⟦rrays and runs them through t⟧ight loops, was only used for two or more aggregates. The assumption was that the streaming path was just as fast for one. We measured: a lone SUM, MIN, COUNT or AVG is 2.1x faster on the columnar path (574 → 271 ms), and COUNT(*) 2.4x.

Parallelism was capped at 4 workers on the theory that scans are memory-bandwidth-bound. They're CPU-bound. On a 16-core machine, 8 workers beat 4 by 1.2 to 1.7x, and beyond 8, COUNT(*) got slower again. The default is now the number of cores, capped at 8.

The column cache, tightened

With the opt-in column cache (ELYRASQL_COLUMN_CACHE_MB), every value went through a per-cell type dispatch, and each aggregate re-read its column. So SUM(x), MIN(x), MAX(x) made three passes. Now a column goes to the aggregate in whole slices. Aggregates over the same column share one pass. Integer sums are exact but vectorisable, split into 32-bit halves instead of one 128-bit add per value. Large float batches are summed in eight independent lanes.

Query (cached)                  Before    After
SUM(col)                        19 ms     11 ms
SUM(col), MIN(col), MAX(col)    56 ms     11 ms

One honest caveat: a float batch of 1024 or more values can now differ from a strict left-to-right sum in the last digit. That's already true wherever partial sums from parallel workers are merged, and the eight-lane sum is, if anything, more accurate. Batches under 1024 values keep MySQL's exact order.

LOAD DATA: 76 seconds → 32

LOAD DATA INFILE turned the file into 50,000-row INSERT statements as SQL text, then handed that text back to the SQL parser. The profile showed the tokenizer spending more time on data we had generated ourselves than the insert spent storing it. Now the batches are built directly as statements, with no text round-trip. A test c⟦hecks that they are identi⟧cal to what the parser produced from the old text.

20 million rows went from 76 s to 32 s. MySQL 8.4 does it⟦ in 25.6 s; the rest of the gap⟧ is our storage writer's B-tree inserts. We tried other batch sizes (10k, 25k, 200k): 50,000 was the sweet spot, so it stays.

Point queries: 112 µs of CPU → 45

Tracing every storage read made by a single point query turned up some surprises:

• It read the table's view record twice (once to validate, once to execute), just to learn "this isn't a view".

• It looked up the user's privileges and roles in storage, even for an admin, with each read a hop to a blocking thread.

• Every UPDATE and DELETE listed every table definition in the database to find foreign keys pointing at its own table, so it got slower as your schema grew.

All of these are now cached. The cache is tied to a scheme layer, which is bumped after every commit that touches a schema key, on every path. (Remember that detail; it matters in Part 2.)

                                      Before     After
Point SELECT, server CPU              112 µs     45 µs
8 clients, queries/second             31,600     40,400
UPDATE by key with 200 tables, CPU    1.63 ms    1.08 ms

A warm point query now does exactly one storage read: the row.

Where that leaves us against MySQL

Same table, same machine:

Query                  MySQL 8.4    ElyraSQL 1.12
SUM(col)               860 ms       271 ms
GROUP BY g             2010 ms      ~250 ms
COUNT(*)               100 ms       ~200 ms
LOAD DATA, 20M rows    25.6 s       32 s

We're ahead on aggregates. MySQL is still faster at COUNT(*) and at bulk loading, and we'd rather tell you than round that away.

Part 2: Correctness first, even when nobody asked

Integer aggregates are exact, everywhere

Before any speed work, we checked the fast paths against MySQL and found they carried every numeric column as a double. Past 2^53, answers were quietly wrong:

-- values: 2^53 + 1 and 1
SELECT SUM(a), COUNT(*) FROM t;   -- was 9007199254740992, should be 9007199254740994

Worse, a table with a BIGINT UNSIGNED column had every ot⟦TODO: truncated⟧n those paths: SUM(a), COUNT(a) gave 120, 6 where the truth was 60, 3. The row decoder didn't recognise the unsigned value, and its fallback pushed the row again. Integers are now integers on every path. A value a fast path can't keep exactly sends the query to the general aggregator instead of being rounded.

As in MySQL, AVG over integers is now DECIMAL with four d⟦ecimal places⟧:

SELECT AVG(a) FROM t;  -- over (10, 21, 2^53+1, 1): 2251799813685256.2500

EXPLAIN no longer claims things that don't happen

Every columnar GROUP BY said Aggregate: columnar group, zone maps⟦, even with zone maps⟧ switched off. Now it names primary-key range, zone maps or columnar cache only when that is what will actually run.

A replica that didn't hear about changes

While profiling those schema reads, we noticed that a replica applies its primary's writes straight to storage, below the SQL layer. But the engine's schema caches were only invalidated by statements that went through the SQL layer. So we tested it with a real primary and a real replica:

-- on the primary
ALTER TABLE rt ADD COLUMN b INT DEFAULT 7;
-- on the replica, until it was restarted
SELECT b FROM rt;   -- ERROR: unknown column: b

Then the serious version:

-- on the primary, after the replica had already checked
GRANT SELECT(public) ON vault TO lim;

-- on the replica, as lim
SELECT secret FROM vault;   -- returned 'classified'

A replica that had once found "no column grants exist" ne⟦ver checked again. The sch⟧ema generation from Part 1 is now what every cache checks, and since it lives at the storage layer, replicas and cluster followers bump it too. There are tests for all of it, including one that starts a real primary and replica, and every one of them ⟦TODO: sentence truncated⟧

Column grants had a side door

A user granted only SELECT(pub) on a table was correctly ⟦TODO: truncated⟧ult. But:

SELECT sec FROM vault UNION SELECT 'x';        -- returne⟦TODO⟧
INSERT INTO mine SELECT id, sec FROM vault;    -- copied it into their own table

The column check returned early for any statement that wasn't a single SELECT. Now every table a statement reads is checked: set operations, subqueries, INSERT ... SELECT sources, and the tables of ⟦TODO: truncated⟧ includes the sneaky ones, like UPDATE vault SET pub = sec. A column-restricted table can only be read through a plain single-table SELECT of the columns you were granted.

Privileges now behave like MySQL's

This is the change you're most likely to notice. We ran the same privilege matrix against MySQL 8.4 and ElyraSQL, and they disagreed in five places. The worst was that a freshly created account coul⟦TODO: truncated⟧

Now:

CREATE USER 'app' IDENTIFIED BY 's3cret';      -- starts with nothing (USAGE)
GRANT SELECT ON orders TO 'app';               -- reads orders, and only orders
GRANT SELECT (id, total) ON orders TO 'audit'; -- just those two columns

Table and column grants now grant access by themselves, even after REVOKE SELECT ON ., which used to shut them off too. And as in MySQL, an UPDATE or DELETE with a WHERE needs SELECT on its table. Eight accounts across seven statements now give exactly MySQL's answers.

What we didn't break:

• Accounts created before 1.12 keep the read access they had. REVOKE SELECT ON . FROM 'u' brings one in line.

• Accounts configured at startup (--user, --auth) keep their configured tier. Speaking of which: --auth user:pass:write accounts couldn't write at all, since every INSERT, UPDATE and DELETE was ⟦TODO: truncated⟧.

One deliberate exception: if an account has a global SELECT and column grants on a table, the column grants still restrict it. MySQL would allow every column, but that combination is exactly how existing installs have been using column grants as a mask, and quietly widening access on upgrade isn't something we're willing to do.

And a test that failed every night at midnight in Oslo

A small one to finish. A time-zone test failed on CI at 2⟦TODO: time garbled ("2120")⟧. At +02:00, local time had passed midnight, so CURTIME() wrapped around while UTC_TIME() hadn't. The engine was right, and MySQL does the same. The test had simply never run between 22:00 and 24:00 UTC. It now accepts either consistent answer.

Upgrading

No on-disk format change: a 1.11.4 database opens in 1.12.0 as-is. Things to check:

• Upgrade your replicas as well as the primary.

• Scripts that CREATE USER and expect the account to read need a GRANT now.

• AVG over integers returns DECIMAL. A client that decodes it strictly as a float (sqlx f64, for example) must accept a decimal.

• The page cache may use more memory on a shared host. Ca⟦p it with ELYRASQL_PAGE_⟧CACHE_MB.

• Tools that match EXPLAIN's exact text should match the Aggregate: columnar group prefix.

docker pull ghcr.io/kwhorne/elyrasql:1.12.0

The full list is in the changelog, and the upgrade notes ⟦are in docs/installation.md⟧.

This release started with a question about SIMD and ended⟦ with a privilege model th⟧at matches MySQL's line for line. That's a fair summary of how we like to work: measure first, fix what's really there, and tell you honestly where we still lose. Thanks for running ElyraSQL, and happy querying. 🌿