OFFSET is simple to implement, but the database still needs to read and discard rows before reaching the requested page. Keyset pagination improves this by beginning after the last row from the previous page. Since different databases have various query planner optimizations and index access methods, the only way to ensure correctness is to examine the execution plan.
The usual example, which I take from Vlad Mihalcea's blog post, orders posts by date and uses the identifier as a unique tie-breaker:
ORDER BY created_on DESC, id DESC
The next page should begin after the last row, and the most effective way to specify this in SQL is through row comparison:
WHERE (created_on, id) < (:last_created_on, :last_id)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
There are two details that make the difference between a direct index seek and an index scan with a filter:
The second point is easy to miss. A query might look flawless and produce correct results, yet still omit the second index column because it compares a bigint with a numeric. Whether you're a developer or an agent, always verify this by checking the execution plan.
This is the table and index used in the original example:
CREATE TABLE post (
created_on timestamp(6),
id bigint NOT NULL,
title varchar(255),
PRIMARY KEY (id)
);
CREATE INDEX idx_post_created_on_id
ON post (created_on DESC, id DESC);
The complete reproductions are available for PostgreSQL 18 and PostgreSQL 15 which I used in a Linkedin discussion.
SQL can compare rows. The SQL standard defines the comparison predicate, including greater than, with row value constructors:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::bigint )
ORDER BY created_on DESC, id DESC
LIMIT 50;
PostgreSQL puts the complete row comparison in the Index Cond:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (ROW(created_on, id) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::bigint))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=60 read=4
Planning Time: 1.925 ms
Execution Time: 0.308 ms
This is what I want for pagination. The B-tree navigates to the composite value and reads the next 50 entries in index order. No sort or filter is needed. It seeks directly to the index entry of the first row to fetch, and it reads only what is necessary for the result.
The id is not optional here. Many posts can have the same timestamp. Pagination needs a total and stable order, so the last ordering column must make the key unique.
A row comparison is equivalent, for non-null values, to:
created_on < :last_created_on
OR (
created_on = :last_created_on
AND id < :last_id
)
This query returns the same rows:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2019-10-02 21:00:00'
OR (
created_on = timestamp '2019-10-02 21:00:00'
AND id < 4951::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
But PostgreSQL keeps the expression as a filter, after scanning all rows:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Filter: ((created_on <
'2019-10-02 21:00:00'::timestamp without time zone)
OR ((created_on =
'2019-10-02 21:00:00'::timestamp without time zone)
AND (id < '4951'::bigint)))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=3
Planning Time: 0.133 ms
Execution Time: 0.049 ms
This small test does not show a performance problem because the first entries happen to qualify. The important difference is the plan operation: Filter is not Index Cond.
With many rows sharing the cursor timestamp, or with a cursor deep in the index, this form may scan and reject many entries before returning 50 rows. The row comparator gives PostgreSQL the composite boundary directly.
Do not replace it with one of these:
-- Too restrictive
created_on < :created_on AND id < :id
-- Too broad
created_on < :created_on OR id < :id
-- Also wrong: it loses older rows having a larger id
created_on <= :created_on AND id < :id
Lexicographic order needs the equality prefix:
created_on < :created_on
OR (created_on = :created_on AND id < :id)
This is logically equivalent to the row comparison, but database query planners rarely recognize x < y OR x = y as a single bound.
Now I change only one value:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::numeric )
ORDER BY created_on DESC, id DESC -------------
LIMIT 50;
The column is bigint, but the value is numeric. Here is the PostgreSQL plan:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (created_on <=
'2019-10-02 21:00:00'::timestamp without time zone)
Filter: (ROW(created_on, (id)::numeric) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::numeric))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=9
Planning Time: 0.288 ms
Execution Time: 0.107 ms
The significant lines are:
Index Cond: (created_on <= ...)
Filter: (ROW(created_on, (id)::numeric) < ...)
The index stores id as bigint, but the comparison casts the indexed column to numeric. The second column is no longer usable as the bigint B-tree boundary.
PostgreSQL still extracts a safe condition from the leading column:
created_on <= :last_created_on
It must be <=, not <, because rows at the same timestamp can qualify when their identifier is lower. PostgreSQL then evaluates the original row expression as a filter to preserve the correct result.
The optimizer source calls this a lossy version of the row comparison. It means that the index condition identifies a superset of the required rows and the complete condition must be rechecked.
The fix is only a cast, but it must be on the value:
WHERE (created_on, id)
< (:last_created_on::timestamp, :last_id::bigint)
Do not cast the indexed column (except if it is indexed with an expression-based index):
-- Avoid this
WHERE (created_on, id::numeric)
< (:last_created_on, :last_id::numeric)
For a column defined as bigint, the application should bind the cursor as int8/bigint, not as numeric or BigDecimal when that changes the PostgreSQL parameter type.
A prepared statement makes the contract explicit:
PREPARE next_page(timestamp, bigint) AS
SELECT id, created_on
FROM post
WHERE (created_on, id) < ($1, $2)
ORDER BY created_on DESC, id DESC
LIMIT 50;
A prepared statement or function is the best way to ensure the execution plan uses the right data type, regardless of what is passed.
The previous fiddle helps you inspect the plan, but its data doesn't magnify the difference. This creates one million rows, with 500,000 rows sharing each timestamp:
DROP TABLE IF EXISTS post;
CREATE UNLOGGED TABLE post (
id bigint PRIMARY KEY,
created_on timestamp NOT NULL,
title text
);
INSERT INTO post
SELECT g,
timestamp '2024-01-01'
- ((g - 1) / 500000) * interval '1 day',
'post ' || g
FROM generate_series(1, 1000000) AS g;
CREATE INDEX post_seek_idx
ON post (created_on DESC, id DESC);
VACUUM (ANALYZE) post;
Run the three alternatives separately:
-- Exact composite boundary
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::bigint)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Only a leading-column boundary because bigint is compared with numeric
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::numeric)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Exact logical expansion, but commonly retained as a filter
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2024-01-01'
OR (
created_on = timestamp '2024-01-01'
AND id < 100::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
The first query can position near (2024-01-01,99). The other two may start near the beginning of the 500,000 entries for 2024-01-01 and filter almost all of them.
Always run this with your distribution and parameters. A query is not efficient because it contains Index Scan. Inspect Index Cond, Filter, rows removed, buffers, and the number of entries actually read.
The short answer depends on whether “support” means syntax or a direct composite index boundary.
| Database | Ordered row syntax | Composite-index behavior for pagination |
|---|---|---|
| PostgreSQL | ✅ | Yes , with compatible B-tree operators and matching datatypes |
| YugabyteDB | ✅ | The original PostgreSQL-style pattern is still not an exact DocDB seek. A new hash-aware form exists |
| MySQL | ✅ | Yes when the row is the leftmost index prefix. Important limitation when it follows separate key predicates |
| MongoDB | ❌ | The exact $or can use two index scans combined with SORT_MERGE
|
| Oracle | ❌ | Use scalar predicates. Typically a leading range plus filter, or bounded UNION ALL branches |
| SQL Server | ❌ | The exact OR can become multiple ranges in one ordered Index Seek
|
Accepting the syntax is not a sufficient test because the goal of this pagination filter is to read only what is necessary. The execution plan is the evidence. Here are some detailed tests
I wrote about this in Efficient pagination in YugabyteDB & PostgreSQL.
The index was:
CREATE UNIQUE INDEX demo1_key_ts_id
ON demo1(key, ts DESC, id DESC);
In YugabyteDB, the first unspecified index column is hash-sharded, so this is effectively:
(key HASH, ts DESC, id DESC)
The elegant PostgreSQL query was:
WHERE key = $1
AND (ts, id) < ($2, $3)
ORDER BY ts DESC, id DESC
LIMIT $4
On YugabyteDB 2.8, the complete historical plan showed why Index Cond was not sufficient evidence:
Limit (actual time=26446.024..26748.760 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ROW(ts, id) <
ROW('2022-01-01 00:00:01+00'::timestamp with time zone,
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Rows Removed by Index Recheck: 998000
Planning Time: 1.159 ms
Execution Time: 26751.767 ms
The storage layer had not started at the complete row value. PostgreSQL rechecked and rejected 998,000 entries.
The workaround was to provide a scalar bound for the first range column:
WHERE key = $1
AND ts <= $2
AND (
ts < $2
OR (ts = $2 AND id < $3)
)
ORDER BY ts DESC, id DESC
LIMIT $4
The historical plan became:
Limit (actual time=18.769..305.666 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ts <=
'2022-01-01 00:00:01+00'::timestamp with time zone))
Filter:
((ts <
'2022-01-01 00:00:01+00'::timestamp with time zone)
OR
((ts =
'2022-01-01 00:00:01+00'::timestamp with time zone)
AND
(id <
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Planning Time: 1.039 ms
Execution Time: 309.985 ms
What about now?
YugabyteDB 2026.1 added a useful row boundary for hash indexes, but it is a different key shape. It starts with the hash code and includes a contiguous key prefix:
CREATE TABLE hc_rc_t1 (
h int,
r1 int,
r2 int,
v text,
PRIMARY KEY (h HASH, r1 ASC, r2 ASC)
);
INSERT INTO hc_rc_t1
SELECT i % 5, i, i * 10, 'val' || i
FROM generate_series(1, 50) AS i;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT *
FROM hc_rc_t1
WHERE (yb_hash_code(h), h, r1, r2)
> (yb_hash_code(1), 1, 10, 100)
LIMIT 5;
The released 2026.1.2 regression plan is:
Limit (actual rows=5 loops=1)
-> Index Scan using hc_rc_t1_pkey on hc_rc_t1
(actual rows=5 loops=1)
Index Cond:
(ROW(yb_hash_code(h), h, r1, r2)
> ROW(4624, 1, 10, 100))
Without the leading hash code:
SELECT *
FROM hc_rc_t1
WHERE (h, r1, r2) > (1, 10, 100)
LIMIT 5;
the released test expects:
Limit (actual rows=5 loops=1)
-> Seq Scan on hc_rc_t1
Filter: (ROW(h, r1, r2) > ROW(1, 10, 100))
Rows Removed by Filter: 2
This does not fix the original portable predicate:
key = :key AND (ts, id) < (:ts, :id)
Issue #11794 remains open, and a July 2026 issue still describes those row-comparison bounds as loose bounds requiring recheck. For the original index and ordering, I would still use the decomposed predicate and verify it with:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
In YugabyteDB, check Storage Index Rows Scanned and Rows Removed by Index Recheck, not only Index Cond.
MySQL 8.4 supports the same lexicographic syntax:
CREATE TABLE post (
id bigint NOT NULL PRIMARY KEY,
created_on datetime(6) NOT NULL,
payload varchar(100),
INDEX post_seek_i (created_on DESC, id DESC)
) ENGINE = InnoDB;
EXPLAIN ANALYZE
SELECT id, created_on
FROM post
WHERE (created_on, id)
< ('2026-01-01 12:00:00', 50000)
ORDER BY created_on DESC, id DESC
LIMIT 50;
With the row constructor covering the leftmost index prefix, the desired plan shape is:
Limit: 50 row(s)
-> Index range scan on post using post_seek_i
But MySQL documents an important exception. With:
CREATE INDEX post_tenant_seek_i
ON post (tenant_id, created_on DESC, id DESC);
WHERE tenant_id = ?
AND (created_on, id) < (?, ?)
the row starts at the second index part. MySQL may use only tenant_id. Its documented example reports:
type: ref
key: PRIMARY
key_len: 4
Extra: Using where
Expanding the row comparison:
WHERE tenant_id = ?
AND (
created_on < ?
OR (created_on = ? AND id < ?)
)
allows the documented example to use all three key parts:
type: range
key: PRIMARY
key_len: 12
Extra: Using where
So MySQL supports index range access for row comparison, but do not generalize that to every position in a composite index. Check access_type, used_key_parts, actual rows, and using_filesort.
Reference: MySQL row-constructor optimization.
MongoDB has no equivalent of:
(created_on, id) < (:created_on, :id)
For this index:
db.events.createIndex(
{created_on: -1, _id: -1},
{name: "created_on_-1__id_-1"}
);
use the exact lexicographic expansion:
const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");
const after = {
$or: [
{created_on: {$lt: T}},
{create... (truncated)
Thread Pool in Percona Server and MySQL (Part 1) A new version of Community MySQL Server July release contains two versions 26.7.0 and 9.7.2. It is an important milestone because it finally brings a Thread Pooling feature to the community. The official MySQL Server Thread Pool plugin existed for a long time, but was available … Continued
The post Thread Pool in Percona Server and MySQL (Part 1) appeared first on Percona.
The FETCH FIRST ... ROWS ONLY clause arrived in the SQL standard with SQL:2008, and Oracle Database implemented it in 12cR1, released in June 2013.
Before that, there were two common ways to write a Top-N query, both requiring a subquery:
ROWNUM outside:
SELECT *
FROM (
SELECT ...
FROM ...
ORDER BY ...
)
WHERE ROWNUM <= 42;
ROW_NUMBER(), then filter its result outside:
SELECT *
FROM (
SELECT ...,
ROW_NUMBER() OVER (ORDER BY ...) AS rn
FROM ...
)
WHERE rn <= 42;
The first subquery is necessary because the ordering must happen before the ROWNUM filter. The second is necessary because the analytic function cannot be evaluated in the same query block’s WHERE clause.
With FETCH FIRST, we can express the intention directly:
SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST 42 ROWS ONLY;
However, a simpler SQL statement does not necessarily mean a simpler implementation.
Like many additions to Oracle’s SQL syntax, FETCH FIRST was implemented through a transformation to existing constructs. Oracle initially chose the analytic ROW_NUMBER() solution.
This was the familiar rewrite in 12c, 18c, 19c, 21c, and early 23c releases. In the recent 23ai and 26ai releases tested here, the simple FETCH FIRST n ROWS ONLY case is rewritten using ROWNUM.
“AI” is not responsible for the change. Oracle 18c and 19c belong to the 12cR2 release family, with names reflecting the release year. Similarly, 26ai is still in the 23 release family, and “ai” replaced “c” with the 23.4 release.
Beyond the marketing names, the relevant change is fix control 35915968:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 35915968;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ----------------------------------------------------- ------------------------
35915968 fetch first transformation using rownum 23.1.0
The OPTIMIZER_FEATURE_ENABLE column tells us the optimizer compatibility setting associated with the control.
There is another clue in ORACLE_HOME. In the 26ai installation used for this investigation, rdbms/admin/bundlefcp_DBBP.xml lists this control under the 23.4.0.0.0 bundle:
<bug id="35915968">
<fix_control default_value="1">35915968</fix_control>
</bug>
This is more useful than simply saying “23ai changed it.”
Choosing ROW_NUMBER() rather than ROWNUM already had a consequence in 12cR1: the optimizer did not apply the same first-k-row optimization.
I blogged about this in 2014: ROWNUM vs ROW_NUMBER() and 12c fetch first.
With ROWNUM <= 10, Oracle knew that it needed only the first ten rows and could cost an ordered index access accordingly. With the analytic rewrite, it could cost the access as if many more rows were needed, making a full scan and sort look preferable.
My recommendation was to add FIRST_ROWS(n), not the old FIRST_ROWS hint.
This was improved in 19c:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 22174392;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ---------------------------------------------------------------- ------------------------
22174392 first k row optimization for window function rownum predicate 19.1.0
I blogged about it in 2020: 19c: scalable Top-N queries without further hints to the query planner.
The improvement is visible in the execution plan as WINDOW NOSORT STOPKEY, the stopkey replacing the simple analytic filter WINDOW SORT PUSHED RANK, so that Oracle could cost the analytic Top-N query with the first-k-row objective and choose that access without the additional hint.
When an index supplies the required order, Oracle can stop early. Without an ordered access path, a sort may still be necessary.
The first-k-row is not the only difference between ROWNUM and the analytic function. ROWNUM was also acts as a non-mergeable view barrier, forcing the optimizer to treat the query block as an isolated inline view.
For the simple row limit tested here, Oracle 26ai now goes back to the other implementation:
SQL> EXPLAIN PLAN FOR
SELECT *
FROM dual
ORDER BY dummy
FETCH FIRST 42 ROWS ONLY;
SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 3 | TABLE ACCESS FULL| DUAL |
------------------------------------
Predicate Information:
----------------------
1 - filter(ROWNUM<=42)
The transformation is visible with DBMS_UTILITY.EXPAND_SQL_TEXT:
WITH
FUNCTION expand_my_sql(p_sql IN VARCHAR2) RETURN CLOB IS
v_output CLOB;
BEGIN
DBMS_UTILITY.EXPAND_SQL_TEXT(
input_sql_text => p_sql,
output_sql_text => v_output
);
RETURN v_output;
END;
SELECT expand_my_sql(
'SELECT * FROM DUAL ORDER BY DUMMY FETCH FIRST 42 ROWS ONLY'
) AS expanded_sql
/
Formatted for readability, the output is:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY"
FROM "SYS"."DUAL" "A2"
ORDER BY "A2"."DUMMY"
) "A1"
WHERE ROWNUM <= 42
We can restore the earlier transformation on the same database by disabling the change:
ALTER SESSION SET "_fix_control" = '35915968:0';
The expanded SQL becomes:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY",
"A2"."DUMMY" "rowlimit_$_0",
ROW_NUMBER() OVER (
ORDER BY "A2"."DUMMY"
) "rowlimit_$$_rownumber"
FROM "SYS"."DUAL" "A2"
) "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= 42
ORDER BY "A1"."rowlimit_$_0"
And the plan uses the analytic operation again:
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
|* 2 | WINDOW NOSORT STOPKEY| |
| 3 | TABLE ACCESS FULL | DUAL |
---------------------------------------
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=42)
2 - filter(ROW_NUMBER() OVER (ORDER BY "DUAL"."DUMMY")<=42)
More than a decade after introducing FETCH FIRST, Oracle is using the legacy ROWNUM solution for this case. It benefits from the existing first-k-row optimization and gives the optimizer a well-established row-limit boundary.
This is not a claim that all row-limiting clauses now use ROWNUM. OFFSET, percentages, and WITH TIES have additional semantics. In the same 26ai binary, another control, 35969400, can also select a native ROW LIMIT operation.
But for the case discussed here, going back to ROWNUM also avoids a wrong-results bug.
A Stack Overflow question showed Oracle returning a row for this query:
SELECT 1 AS one_row
FROM dual
WHERE EXISTS (SELECT * FROM view_abcd)
AND NOT EXISTS (SELECT * FROM view_abcd)
;
This is a logical contradiction. EXISTS returns true or false depending on whether its subquery returns any rows, regardless of the values in those rows. If one predicate is true, the other must be false.
The view combines OUTER APPLY, an inner ANSI JOIN, and a correlated FETCH FIRST 1 ROW ONLY. The question reports the problem on 19c and 21c.
On Oracle 23ai Free 23.9.0.25.07, the default result is correct. Disabling 35915968 makes it return the wrong row: db<>fiddle.
I also reproduced it on 26ai Free 23.26.3.0.0 with:
ALTER SESSION SET optimizer_features_enable = '21.1.0';
That was a starting point, not the solution. I wanted a finer setting than changing the whole optimizer compatibility level.
I used Copilot for this investigation. Not just to ask “what is the answer?”, but to run the experiments I would normally run myself.
Here are some of my prompts:
“Please find the bug published by Oracle.”
“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”
“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”
“Maybe you have run without the latest patches.”
“I’m able to reproduce the bug on 26ai with
optimizer_features_enable='21.1.0', but I would like a finer setting.”
The agent ran Mauro Pagano’s Pathfinder with OFE 21.1 as the baseline, testing 2,811 cases. I described this method in the past (I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?).
Two controls avoided the wrong result: 35915968:1 and 35969400:1. The description of the first immediately connected the result to the FETCH FIRST rewrite.
Then I prompted Copilot for the next step:
“Look at the execution plans.”
“Is it a combination of
FETCH FIRSTtransformation, ANSI join, and merged views?”
This is where AI assistance is useful to me. My experience guides the investigation. The agent handles the repetitive experiments, collects the output, and helps compare it. The results still have to explain the behavior. A plausible answer is not enough.
The failing execution plan is reduced to an unconditional FAST DUAL. The correct plan retains the filter, the outer-join branches, and the COUNT STOPKEY operations.
The expanded SQL and execution plans show the difference. The paired 10053 traces add something more interesting: the analytic and ROWNUM rewrites lead to different view-merging decisions.
In the analytic path, Oracle keeps the window-function query block:
SVM: SVM bypassed on view SEL$4(#0): Window functions in this view.
It also retains an additional correlated lateral-view layer:
SVM: SVM bypassed on view SEL$F23444D6(#0): Lateral view with left correlation.
In the ROWNUM path, Oracle merges the simple inner query into the block containing the row limit:
CVM: Merging SPJ view SEL$4 (#0) into SEL$6 (#0)
The resulting scalar subquery is simpler:
SELECT "D"."DUMMY"
FROM "SYS"."DUAL" "D"
WHERE ROWNUM <= 1
AND "D"."DUMMY" = "A"."DUMMY"
It also merges a layer that remained separate in the analytic path:
CVM: Merging SPJ view SEL$F23444D6 (#0) into SEL$3 (#0)
So the explanation is not simply “ROWNUM prevents view merging.” The correct path actually performs more merges at these points. It removes unnecessary wrapper blocks while retaining the scalar subquery’s row-limit boundary.
Both paths still have restrictions around the null-augmented outer-joined lateral view. The analytic path adds another combination of boundaries and correlations, and that combination ends in the wrong executable plan.
This identifies the problematic path. It does not identify the exact line of Oracle code responsible, nor prove that 35915968 was originally created to fix this particular report. What we have demonstrated is that enabling this transformation avoids the wrong result.
I like standard SQL syntax. FETCH FIRST expresses the intention more clearly than a manually nested ROWNUM query. I've seen too many of those queries with the wrong placement of ORDER BY.
But syntax support and native implementation are different things.
Oracle’s ANSI joins are translated into internal structures, including lateral views and legacy outer-join representations. The row-limit clause was translated into an analytic query. Each transformation may be reasonable on its own. Their combinations are where the complexity grows.
There is a long history of ANSI-related wrong-results bugs. Jonathan Lewis has documented examples involving ANSI joins and NATURAL JOIN. I've see it striking again with materialized views and assertions.
Transformations are essential to query optimization. The problem is not that they exist. It is that adding a feature through several layers of rewriting also adds interactions that must preserve the original semantics.
Here, the legacy ROWNUM implementation has two advantages: first-k-row optimization and a row-limit boundary that Oracle already knows how to handle. Finally, the newer syntax stayed but the machinery underneath returned to the simpler implementation.
And AI can help us investigate that machinery. Not by replacing database knowledge with a confident explanation, but by making it easier to run the experiments that turn an explanation into evidence. You don't know to download Pathfinder and run it yourself when an AI agent can reproduce everything on a docker container.
You may wonder how PostgreSQL dealt with the addition of FETCH FIRST to the SQL standard. Unlike Oracle’s initial implementation, PostgreSQL did not rewrite it into an analytic ROW_NUMBER() query. FETCH FIRST was implemented as an alternative syntax for LIMIT/OFFSET which already existed, and EXPLAIN shows a Limit node. The planner also knows the requested row count when costing paths: if an index supplies the required order, it can choose that path and stop once enough rows have been produced. Otherwise, it may still need to scan or sort more data.
At the top level, FETCH FIRST does not add a query block. Inside a subquery, though, the row limit prevents that subquery from being pulled up, since moving the limit could change which rows it returns. So PostgreSQL has a native stop-after-N boundary, conceptually like Oracle’s COUNT STOPKEY—not the same operator, but the same basic idea. The engines converge on that approach. PostgreSQL used it from the start, while Oracle initially implemented FETCH FIRST through ROW_NUMBER() analytic filter and later changed the rewrite for the simple row-limit case.
Here's a detailed look at scalar $group index pushdown, an optimization introduced with DocumentDB 0.117-0 on September 10, 2026. It also highlights an advantage over MongoDB: the PostgreSQL query planner ensures optimal performance without requiring additional hints in the query.
DocumentDB is a fully open-source PostgreSQL extension that brings the MongoDB API to the SQL table. It offers MongoDB users an alternative by integrating with PostgreSQL, transforming MongoDB operators into efficient PostgreSQL access paths. Microsoft leads the development, continuously enhancing the extension based on valuable feedback from enterprise customers on Azure DocumentDB.
To demonstrate the optimization, I load 50,000 documents with 100 distinct a values and 200-byte payloads. I create an index on {a: 1} then calculate {$group: {_id: null, total: {$sum: "$a"}}}. The query has no hint, so the optimizer must decide whether it can answer the accumulator from the a_1 index.
MongoDB, DocumentDB 0.116, and DocumentDB 0.117 process the same documents, indexes, and unhinted pipelines. Each DocumentDB gateway execution is divided into setup, VACUUM (ANALYZE), and query phases. I compare rows, heap fetches, and buffers rather than elapsed time across different containers.
The feature is present in 0.117 but disabled by default. I enable only documentdb.enable_scalar_aggregate_index_pushdown. All other settings stay the same.
I initially perform the unhinted aggregation on MongoDB Atlas 8.0:
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex({a: 1});
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": "2",
"queryPlanner": {
"namespace": "perf117.scalar_group",
"parsedQuery": {},
"indexFilterSet": false,
"queryHash": "7234A6DE",
"planCacheShapeHash": "7234A6DE",
"planCacheKey": "2A6F009E",
"optimizationTimeMillis": 0,
"optimizedPipeline": true,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"queryPlan": {
"stage": "GROUP",
"planNodeId": 3,
"inputStage": {
"stage": "COLLSCAN",
"planNodeId": 1,
"filter": {},
"direction": "forward"
}
},
"slotBasedPlan": {
"slots": "$$RESULT=s8 env: { }",
"stages": "[3] project [s8 = newBsonObj(\"_id\", s6, \"total\", s7)] \n[3] project [s6 = null, s7 = doubleDoubleSumFinalize(s5)] \n[3] group [] [s5 = aggDoubleDoubleSum(s1)] spillSlots[s4] mergingExprs[aggMergeDoubleDoubleSums(s4)] \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"a05943ac-772f-4c76-b128-e6e3bfdd0158\" true false "
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1,
"executionTimeMillis": 12,
"totalKeysExamined": 0,
"totalDocsExamined": 50000,
"executionStages": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"8": "newBsonObj(\"_id\", s6, \"total\", s7) "
},
"inputStage": {
"stage": "project",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"projections": {
"6": "null ",
"7": "doubleDoubleSumFinalize(s5) "
},
"inputStage": {
"stage": "group",
"planNodeId": 3,
"nReturned": 1,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"groupBySlots": [],
"expressions": {
"5": "aggDoubleDoubleSum(s1) ",
"initExprs": {
"5": null
}
},
"mergingExprs": {
"4": "aggMergeDoubleDoubleSums(s4) "
},
"usedDisk": false,
"spills": 0,
"spilledBytes": 0,
"spilledRecords": 0,
"spilledDataStorageSize": 0,
"inputStage": {
"stage": "scan",
"planNodeId": 1,
"nReturned": 50000,
"executionTimeMillisEstimate": 4,
"opens": 1,
"closes": 1,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"numReads": 50000,
"recordSlot": 2,
"recordIdSlot": 3,
"scanFieldNames": [
"a"
],
"scanFieldSlots": [
1
]
}
}
}
}
},
"queryShapeHash": "F1620CAE8E90491891C0FB6F23ECD9556823F83467253A897C540C2A88AC66E5",
"command": {
"aggregate": "scalar_group",
"pipeline": [
{
"$group": {
"_id": null,
"total": {
"$sum": "$a"
}
}
}
],
"cursor": {},
"$db": "perf117"
},
"serverInfo": {
"host": "a004f7434a57",
"port": 27017,
"version": "8.0.28",
"gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted",
"internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
},
"ok": 1
}
}
MongoDB performs a COLLSCAN for this unhinted scalar aggregation, scanning all 50,000 documents without using any index keys, resulting in one group. The aggregation does not spill, but the documents containing payload data are still read.
MongoDB creates index-access plans only when the index helps with filtering, sorting, or a DISTINCT_SCAN rewrite, which isn't the case here. To force an IXSCAN for this query, you need to specify the index explicitly using { hint: { a: 1 } } (this bypasses the query planner so you must ensure that the result remains unaffected, especially if using a partial index):
db.scalar_group.explain(
"executionStats"
).aggregate(
pipeline, { hint: { a: 1 } }
).queryPlanner.winningPlan
;
{
isCached: false,
queryPlan: {
stage: 'GROUP',
planNodeId: 3,
inputStage: {
stage: 'PROJECTION_COVERED',
planNodeId: 2,
transformBy: { a: true, _id: false },
inputStage: {
stage: 'IXSCAN',
planNodeId: 1,
keyPattern: { a: 1 },
indexName: 'a_1',
isMultiKey: false,
multiKeyPaths: { a: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { a: [ '[MinKey, MaxKey]' ] }
}
}
},
slotBasedPlan: {
slots: '$$RESULT=s7 env: { }',
stages: '[3] project [s7 = newBsonObj("_id", s5, "total", s6)] \n' +
'[3] project [s5 = null, s6 = doubleDoubleSumFinalize(s4)] \n' +
'[3] group [] [s4 = aggDoubleDoubleSum(s1)] spillSlots[s3] mergingExprs[aggMergeDoubleDoubleSums(s3)] \n' +
'[1] ixseek KS(0A0104) KS(F0FE04) none s2 none none lowPriority [s1 = 0] @"10eeeb21-169c-4c72-bbb4-4a674cbf37db" @"a_1" true '
}
}
With the hint, MongoDB can use an index-only scan (IXSCAN + PROJECTION_COVERED), reading only 50,000 index entries.
I load and index the same data through the DocumentDB 0.116 gateway (MongoDB-compatible endpoint):
db = db.getSiblingDB("perf117");
db.scalar_group.drop();
db.scalar_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
payload: "x".repeat(200)
})));
db.scalar_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.scalar_group.countDocuments(),
indexes: db.scalar_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
I run VACUUM (ANALYZE) in PostgreSQL to simulate the auto-vacuum job in a reproducible way:
VACUUM (ANALYZE)
;
Then I run the unchanged scalar aggregation:
db = db.getSiblingDB("perf117");
const pipeline = [
{$group: {_id: null, total: {$sum: "$a"}}}
];
print(EJSON.stringify({
result: db.scalar_group.aggregate(pipeline).toArray(),
explain: db.scalar_group.explain("executionStats").aggregate(pipeline)
}, null, 2));
{
"result": [
{
"_id": null,
"total": 2475000
}
],
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'scalar_group', 'pipeline': [{ '$group': { '_id': null, 'total': { '$sum': '$a' } } }], 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 4.189,
"explainCommandExecTimeMillis": 159.291,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "COLLSCAN",
"startupCost": 0,
"totalCost": 2477,
"estimatedTotalKeysExamined": 50000
}
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "COLLSCAN",
"nReturned": 50000,
"executionTimeMillis": 67.959,
"executionStartAtTimeMillis": 0.004,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"numBlocksFromCache": 1852
}
}
}
},
{
"$root": {
"queryPlanner": {
"winningPlan": {
"stage": "GENERIC_AGGREGATE",
"startupCost": 0,
"totalCost": 2602.02,
"aggStrategy": "Sorted",
"estimatedTotalKeysExamined": 1
}
},
"executionStats": {
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"executionStages": {
"stage": "GENERIC_AGGREGATE",
"nReturned": 1,
"executionTimeMillis": 159.235,
"executionStartAtTimeMillis": 159.231,
"totalDocsExamined": 1,
"totalKeysExamined": 1,
"numBlocksFromCache": 1852
}
}
}
}
],
"ok": 1
}
}
DocumentDB 0.116 also performs a collection scan through the gateway. It examines 50,000 documents and reports 1,852 shared-buffer hits.
I run the same setup and pipeline through the native DocumentDB API in the PostgreSQL endpoint, but explicitly disable Seq Scan to show why the Index Scan is more expensive:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf117', 'scalar_group');
SELECT count(documentdb_api.insert_one(
'perf117',
'scalar_group',
format(
'{"_id":%s,"a":%s,"payload":"%s"}',
i, i % 100, repeat('x', 200)
)::documentdb_core.bson,
NULL
))
FROM generate_series(1, 50000) i;
SELECT documentdb_api_internal.create_indexes_non_concurrently(
'perf117',
'{"createIndexes":"scalar_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
true
);
SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf117' AND collection_name = 'scalar_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
'perf117',
'{"aggregate":"scalar_group","pipeline":[{"$group":{"_id":null,"total":{"$sum":"$a"}}}],"cursor":{}}'
);
...
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Output: bson_repath_and_build('_id'::text, 'BSONHEX070000000a0000'::bson, 'total'::text, bsonsumwithexpr(document, 'BSONHEX0e00000002000300000024610000'::bson, 'BSONHEX12000000096e6f7700876814cda001000000'::bson, NUL... (truncated)
Percona ClusterSync for MongoDB (PCSM) synchronizes data from a source MongoDB cluster (including Atlas) to a target MongoDB cluster in two stages. It first creates a consistent starting point and clones the existing collections and indexes. At the same time, it captures source changes through MongoDB change streams. Once the initial copy is complete, PCSM … Continued
The post Tuning Percona ClusterSync for MongoDB Performance appeared first on Percona.
Welcome back to Village News, our curated roundup of MySQL and database news.
If you want to get these updates, just subscribe to the blog.
Enjoy!
Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)
Editor’s Note: This article was originally authored by Inel Pandzic. Migrating data between MongoDB clusters is rarely a five-minute job. A large initial clone followed by days of change-stream replication is normal, and for all that time, Percona ClusterSync for MongoDB (PCSM) is a critical piece of your infrastructure. Until now, it was also a … Continued
The post Percona ClusterSync for MongoDB Goes Highly Available: Active-Standby Failover appeared first on Percona.
This should have been the first post of this series. A few months ago, I demonstrated (video) what I see as one of the key advantages of a document model: a compound index can support filtering, sorting, and pagination across data embedded in a one-to-many relationship.
In a normalized relational model, that relationship typically spans multiple tables. Indexes belong to individual tables, so answering the same query may require to join more rows before sorting and filtering.
That demo used MongoDB. Some MongoDB emulations on SQL databases pretend compatibility but lack performance it they don't implement MongoDB-style indexing or query plans. That's generally because the emulation is built on top of RDBMS indexes which were not designed for non-1NF schemas.
Azure DocumentDB takes a different approach: it runs as a PostgreSQL extension which, thanks to PostgreSQL’s extensibility, can define native indexing for non-relational datatypes. The DocumentDB extension uses Extended RUM indexes.
In this post, I’ll run the same as I did on MongoDB to show that DocumentDB on PostgreSQL provides the same performance with similar execution plan. You can also play with it on db<>fiddle https://dbfiddle.uk/Ft_2L34U:
This starts by creating, though the MongoDB-compatible endpoint, the collection and index with accounts and operations:
//
// Create same data as https://youtu.be/Hq9CFhxSqgw?si=g6CnDuv_PqPHRquc
//
db.accounts.createIndex({
category: 1,
"operations.date": -1,
});
function insert(num) {
const ops = [];
for (let i = 0; i < num; i++) {
const account = Math.floor(Math.random() * 10_000) + 1;
const category = Math.floor(Math.random() * 3);
const operation = {
date: new Date(),
amount: Math.floor(Math.random() * 1_000) + 1,
};
ops.push({
updateOne: {
filter: { _id: account },
update: {
$set: { category: category },
$push: { operations: operation },
},
upsert: true,
},
});
}
db.accounts.bulkWrite(ops);
}
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
insert(1_000); insert(1_000); insert(1_000); insert(1_000); insert(1_000);
As the account operations are embedded as an array for each account, a single index can serve filtering on account's attributes, like "category", and operation's attributes, like "date".
A simple query asks Which Category 1 account had the most recent activity? and this involves a filter on category and operation date:
db.accounts.find(
{ "category": 1 },
{ "operations.amount": 1, "operations.date": 1 }
).sort({ "operations.date": -1 }).limit(1);
This is typical of pagination, filtering a specific number of documents from an ordered result:
[
{
_id: 6115,
operations: [
{ date: ISODate('2026-09-25T22:34:03.218Z'), amount: 320 },
{ date: ISODate('2026-09-25T22:34:05.739Z'), amount: 847 }
]
}
]
The MongoDB-compatible execution plan shows that it didn't require a sort operation as the index scan provides the documents in the expected order:
db.accounts.find(
{ "category": 1 },
{ "operations.amount": 1, "operations.date": 1 }
).sort({ "operations.date": -1 }).limit(1).explain().queryPlanner.winningPlan
{
stage: 'LIMIT',
startupCost: 0,
totalCost: 0.2,
estimatedTotalKeysExamined: 1,
inputStage: {
stage: 'PROJECT',
startupCost: 0,
totalCost: 0.2,
estimatedTotalKeysExamined: 1,
inputStage: {
stage: 'FETCH',
ns: 'test.accounts',
startupCost: 0,
totalCost: 208.79,
estimatedTotalKeysExamined: 1055,
inputStage: {
stage: 'IXSCAN',
ns: 'test.accounts',
indexName: 'category_1_operations.date_-1',
direction: 'Forward',
indexUsage: {
indexKeyString: '{"category": 1,"operations.date": -1}',
isMultiKey: true,
bounds: [
'["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]'
]
},
startupCost: 0,
totalCost: 208.79,
hasOrderBy: true,
indexFilterSet: [ { category: { '$eq': 1 } } ],
estimatedTotalKeysExamined: 1055
}
}
}
}
I can run the same query, as an aggregation pipeline, on the PostgreSQL endpoint though DocumentDB functions:
EXPLAIN (COSTS OFF, ANALYZE ON, BUFFERS ON, VERBOSE ON)
SELECT document FROM documentdb_api_catalog.bson_aggregation_pipeline(
'test',
'{"aggregate": "accounts", "pipeline": [
{"$match": {"category": 1}},
{"$sort": {"operations.date": -1}},
{"$limit": 1},
{"$project": {"operations.amount": 1, "operations.date": 1}}
], "cursor": {}}'::documentdb_core.bson
);
QUERY PLAN
--------------------------------------------------------------------------
Subquery Scan on agg_stage_3 (actual time=0.111..0.112 rows=1.00 loops=1)
Output: documentdb_api_internal.bson_dollar_project(agg_stage_3.document, 'BSONHEX31000000106f7065726174696f6e732e616d6f756e740001000000106f7065726174696f6e732e64617465000100000000'::documentdb_core.bson, 'BSONHEX12000000096e6f7700d660b4daa001000000'::documentdb_core.bson)
Buffers: shared hit=5
-> Limit (actual time=0.101..0.101 rows=1.00 loops=1)
Output: collection.document, (documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson))
Buffers: shared hit=5
-> Custom Scan (DocumentDBApiExplainQueryScan) (actual time=0.100..0.100 rows=1.00 loops=1)
Output: collection.document, documentdb_api_catalog.bson_orderby(collection.document, 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
namespaceName: test.accounts
indexName: category_1_operations.date_-1
indexKey: {"category": 1,"operations.date": -1}
isMultiKey: true
indexBounds: ["category": [1, 1], "operations.date": DESC(MinKey, MaxKey)]
innerScanLoops: 1 loops
scanType: ordered
scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 116)]
_id_: (startup cost=0.282, total cost=287.743, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=6328, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
Buffers: shared hit=5
-> Index Scan using "category_1_operations.date_-1" on documentdb_data.documents_2 collection (actual time=0.055..0.055 rows=1.00 loops=1)
Output: collection.document
Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@=) 'BSONHEX130000001063617465676f7279000100000000'::documentdb_core.bson)
Order By: (collection.document OPERATOR(documentdb_api_catalog.|-<>) 'BSONHEX1a000000106f7065726174696f6e732e6461746500ffffffff00'::documentdb_core.bson)
Index Searches: 0
Buffers: shared hit=5
Planning:
Buffers: shared hit=518
Planning Time: 1.605 ms
Execution Time: 0.202 ms
The Index Scan covered the $match filter with Index Cond, the $sort with Order By, the $limit: 1 with rows=1.00 and the $projection with Output. There no sort or filter above it. The Custom Scan is a purely decorative wrapper that DocumentDB injects around the real index access path so that EXPLAIN can expose MongoDB-style index metadata, like isMultiKey: true and the indexBounds.
I got the remark that the planning time is huge here Planning Time: 1.605 ms. I've ran it on db<>fiddle which use micro-VM for fast ephemeral instances, so don't compare time. But still, Buffers: shared hit=518 as a lot and this is due to the first query in a PostgreSQL session that reads from the catalog. If I had run the query with explain first to check the result (https://dbfiddle.uk/eFaEQ0Gq), the next query would have had much faster planning:
Planning:
Buffers: shared hit=2
Planning Time: 0.126 ms
Execution Time: 0.104 ms
The video demonstrated the main advantage of a document model with multi-key indexes on MongoDB. The same exists open-source on PostgreSQL with the DocumentDB extension. Extended RUM was added in v0.106 (August 29, 2025), ordered indexes/scans were enabled by default in v0.109 (March 09, 2026) - be sure that documentdb_extended_rum is listed in shared_preload_libraries - and later releases improved edge cases of it (e.g. collation support across 0.111–0.113, multikey fixes in 0.116).
DocumentDB 0.116-0, released August 20, 2026, introduced prefix sort pushdown for $group accumulators. It lets the grouping stage consume rows in the existing sort order, avoiding a separate blocking sort or hash aggregate.
The previous article in this series covered a different optimization: a distinct scan that skips duplicate index entries when a group returns one row per distinct key. Prefix sort pushdown does not skip entries. It removes the top-level sort. The query pattern is also related to the first-per-group example in First/Last per Group: PostgreSQL DISTINCT ON and MongoDB DISTINCT_SCAN Performance, which shows MongoDB using DISTINCT_SCAN.
DocumentDB implements the MongoDB API as a fully open-source PostgreSQL extension. It offers an open alternative for MongoDB applications and exposes aggregation execution through both MongoDB-like and PostgreSQL plans, providing insight into sorts, buffers, and temporary I/O. Microsoft is the main contributor, with improvements coming from enterprise experience with Azure DocumentDB.
To demonstrate prefix sort pushdown, I load 50,000 documents across 100 groups, add a 200-byte payload, and create an ordered index on {a: 1}. The pipeline sorts by a, groups by the same key, and returns the first name in each group. Because the group key is a prefix of the sort keys, the accumulator can use the existing index order instead of sorting all 50,000 rows again.
MongoDB, DocumentDB 0.114, and DocumentDB 0.116 receive the same data, ordered index, hint, and pipeline. Both DocumentDB versions get the same VACUUM (ANALYZE), and both native runs use the same planner settings. The documentdb.enableSortPushToAccumulatorWithPrefix setting exists in 0.114 but is disabled by default. It is enabled by default in 0.116. I compare explicit sort nodes, temporary I/O, rows, and buffers rather than total elapsed time. The only timing I cite is when the gateway's group stage starts producing rows (executionStartAtTimeMillis), which shows whether grouping blocks or streams. In the native (SQL function) runs, I set enable_hashagg=off (and enable_seqscan / enable_bitmapscan) to force the sorted plan, but the gateway (MongoDB-compatible endpoint) runs use default settings.
I first run the pipeline on MongoDB Atlas 8.0:
db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
b: (50000 - index - 1) % 1000,
name: `name_${index + 1}`,
payload: "x".repeat(200)
})));
db.sort_group.createIndex({a: 1});
const pipeline = [
{$sort: {a: 1}},
{$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
resultCount: db.sort_group.aggregate(
pipeline,
{hint: "a_1"}
).toArray().length,
explain: db.sort_group.explain("executionStats").aggregate(
pipeline,
{hint: "a_1"}
)
}, null, 2));
It shows a DISTINCT_SCAN:
{
"resultCount": 100,
"explain": {
"explainVersion": "1",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "perf116.sort_group",
"parsedQuery": {},
"indexFilterSet": false,
"queryHash": "DC5C6196",
"planCacheShapeHash": "DC5C6196",
"planCacheKey": "979F6906",
"optimizationTimeMillis": 0,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"prunedSimilarIndexes": false,
"winningPlan": {
"isCached": false,
"stage": "FETCH",
"inputStage": {
"stage": "DISTINCT_SCAN",
"keyPattern": {
"a": 1
},
"indexName": "a_1",
"isMultiKey": false,
"multiKeyPaths": {
"a": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"a": [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 100,
"executionTimeMillis": 2,
"totalKeysExamined": 100,
"totalDocsExamined": 100,
"executionStages": {
"isCached": false,
"stage": "FETCH",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 100,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"docsExamined": 100,
"alreadyHasObj": 0,
"inputStage": {
"stage": "DISTINCT_SCAN",
"nReturned": 100,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 100,
"needTime": 0,
"needYield": 0,
"saveState": 3,
"restoreState": 3,
"isEOF": 1,
"keyPattern": {
"a": 1
},
"indexName": "a_1",
"isMultiKey": false,
"multiKeyPaths": {
"a": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"a": [
"[MinKey, MaxKey]"
]
},
"keysExamined": 100
}
}
}
},
"nReturned": 100,
"executionTimeMillisEstimate": 3
},
{
"$groupByDistinctScan": {
"newRoot": {
"_id": "$a",
"firstVal": "$name"
}
},
"nReturned": 100,
"executionTimeMillisEstimate": 3
}
],
"queryShapeHash": "0822C17532FC5F85649D595163EF78A0BAEF5CDA8811F1689D203E1AD79FD2F7",
"serverInfo": {
"host": "a004f7434a57",
"port": 27017,
"version": "8.0.28",
"gitVersion": "cd6fc9b3b7cf87ff2bbca0af67382ac407fc682a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "trySbeRestricted",
"internalQueryPlannerIgnoreIndexWithCollationForRegex": 1
},
"command": {
"aggregate": "sort_group",
"pipeline": [
{
"$sort": {
"a": 1
}
},
{
"$group": {
"_id": "$a",
"firstVal": {
"$first": "$name"
}
}
}
],
"hint": "a_1",
"cursor": {},
"$db": "perf116"
},
"ok": 1
}
}
MongoDB recognizes the first-per-group pattern. It uses DISTINCT_SCAN on a_1, examines 100 index keys, fetches 100 documents for name, and returns 100 groups. There is no blocking sort.
I load and index the same data through the DocumentDB 0.114 gateway (MongoDB-compatible endpoint):
db = db.getSiblingDB("perf116");
db.sort_group.drop();
db.sort_group.insertMany(Array.from({length: 50000}, (_, index) => ({
_id: index + 1,
a: (index + 1) % 100,
b: (50000 - index - 1) % 1000,
name: `name_${index + 1}`,
payload: "x".repeat(200)
})));
db.sort_group.createIndex(
{a: 1},
{storageEngine: {enableOrderedIndex: true}}
);
print(EJSON.stringify({
insertedDocuments: db.sort_group.countDocuments(),
indexes: db.sort_group.getIndexes().map(index => index.name)
}, null, 2));
{
"insertedDocuments": 50000,
"indexes": [
"_id_",
"a_1"
]
}
Connected to the PostgreSQL endpoint, I make the table statistics and visibility state deterministic:
\pset pager off
SET search_path TO documentdb_api_catalog, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT collection_id
FROM collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;
documentdb_version
--------------------
0.114-0
(1 row)
vacuumed_collection_id
------------------------
2
(1 row)
Then I run the same hinted pipeline on the MongoSH:
db = db.getSiblingDB("perf116");
const pipeline = [
{$sort: {a: 1}},
{$group: {_id: "$a", firstVal: {$first: "$name"}}}
];
print(EJSON.stringify({
resultCount: db.sort_group.aggregate(
pipeline,
{hint: "a_1"}
).toArray().length,
explain: db.sort_group.explain("executionStats").aggregate(
pipeline,
{hint: "a_1"}
)
}, null, 2));
{
"resultCount": 100,
"explain": {
"explainVersion": 2,
"command": "db.runCommand({explain: { 'aggregate': 'sort_group', 'pipeline': [{ '$sort': { 'a': 1 } }, { '$group': { '_id': '$a', 'firstVal': { '$first': '$name' } } }], 'hint': 'a_1', 'cursor': {} }})",
"explainCommandPlanningTimeMillis": 2.649,
"explainCommandExecTimeMillis": 349.424,
"stages": [
{
"$cursor": {
"queryPlanner": {
"winningPlan": {
"stage": "FETCH",
"startupCost": 0,
"totalCost": 13.89,
"estimatedTotalKeysExamined": 5556,
"inputStage": {
"stage": "IXSCAN",
"indexName": "a_1",
"direction": "Forward",
"startupCost": 0,
"totalCost": 13.89,
"hasOrderBy": true,
"indexFilterSet": [
{
"a": {
"$range": {
"orderByScan": 1
}
}
}
],
"estimatedTotalKeysExamined": 5556
}
}
},
"executionStats": {
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"executionStages": {
"stage": "FETCH",
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 50000,
"executionTimeMillis": 125.407,
"executionStartAtTimeMillis": 0.036,
"indexName": "a_1",
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023
}
}
}
}
},
{
"$group": {
"queryPlanner": {
"winningPlan": {
"stage": "GROUP",
"startupCost": 111.12,
"totalCost": 236.13,
"aggStrategy": "Hashed",
"estimatedTotalKeysExamined": 5556,
"inputStage": {
"stage": "PROJECTION_DEFAULT",
"startupCost": 0,
"totalCost": 83.34,
"estimatedTotalKeysExamined": 5556
}
}
},
"executionStats": {
"nReturned": 100,
"executionTimeMillis": 349.126,
"executionStartAtTimeMillis": 348.869,
"totalDocsExamined": 100,
"totalKeysExamined": 100,
"executionStages": {
"stage": "GROUP",
"nReturned": 100,
"executionTimeMillis": 349.126,
"executionStartAtTimeMillis": 348.869,
"totalDocsExamined": 100,
"totalKeysExamined": 100,
"numBlocksFromCache": 50023,
"inputStage": {
"stage": "PROJECTION_DEFAULT",
"nReturned": 50000,
"executionTimeMillis": 255.141,
"executionStartAtTimeMillis": 0.041,
"totalDocsExamined": 50000,
"totalKeysExamined": 50000,
"numBlocksFromCache": 50023
}
}
}
}
}
],
"ok": 1
}
}
With default settings, the gateway plan uses a hash aggregate (aggStrategy: "Hashed"), which cannot return any group until it reads all 50,000 rows. The group stage starts at 348.9 ms. The MongoDB-compatible explain doesn't show buffers or temporary I/O, so I inspect the native plan from the PostgreSQL endpoint. I disable hash aggregation there to see the sort-based plan that the new optimization targets:
\pset pager off
\set ON_ERROR_STOP on
SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog,
documentdb_api_internal, public;
SELECT extversion AS documentdb_version
FROM pg_extension
WHERE extname = 'documentdb';
SELECT documentdb_api.drop_collection('perf116', 'sort_group');
SELECT count(documentdb_api.insert_one(
'perf116',
'sort_group',
format(
'{"_id":%s,"a":%s,"b":%s,"name":"name_%s","payload":"%s"}',
i, i % 100, (50000 - i) % 1000, i, repeat('x', 200)
)::documentdb_core.bson,
Valkey Memory Optimization, Version by Version and Encoding: How Many Bytes Did Each Release Actually Save?
Introduction This post is based on a rigorous benchmark and a structural teardown of the source, aimed at answering three questions: For the same data, how much memory does each version actually save? Where exactly does that saving come from at the data-structure level? How do different data structures and encoding result in significantly different … Continued
The post Valkey Memory Optimization, Version by Version and Encoding: How Many Bytes Did Each Release Actually Save? appeared first on Percona.
Measuring the Performance of Deterministic Exceptions
I have always been interested in Zero-Overhead Deterministic
Exceptions from P0709, as they promised a nicer implementation model
and they are much easier to integrate into JIT-compiled code. Some people
forbid regular C++ exceptions, due to code size and performance
concerns, which are concerns that P0709 explicitly wants to address.
Unfortunately, there was no implementation available. But now, with
LLMs, doing this kind of experiment is much more tractable. I basically
gave the official P0709 specification to an LLM, and it built a first
prototype in one shot. Admittedly my initial enthusiasm cooled down a
bit after I found a lot of problems and corner cases, but after about
one day of work I was able to build a reasonable
prototype, which is something that would easily have taken me weeks
or even months in the past. I tested it with complex code, and it
handled everything fine, but note that it is still not production ready
(e.g., Itanium ABI only).
That prototype now allows us to quantify the differences between
exception implementations. P0709 describes two ways to implement them:
Either pass a hidden pointer to an error state to all functions that are
marked with throws (we call this strategy
pointer here), or use the carry bit to indicate errors and
pass the error itself in registers if the bit is set (we call this
strategy carry). That is a very nifty approach suggested by
Herb Sutter, but it somewhat clashes with some of the function epilogues
(e.g., on Windows), and such a flag is not available on all platforms.
We thus introduced a third variant register here that
simply uses another unused caller-saved register as an error indicator,
which is more portable. The classic C++ exception handling mechanism we
call dynamic.
Microbenchmarks
We first run some microbenchmarks
to see the overall effects. These over-emphasize the differences as the
benchmarks basically do nothing besides function calls (ns per outer
call):
Test
failures
dynamic
pointer
register
carry
int through 8 calls0%
6.02
6.09
6.08
6.16
1%
17.5
7.10
6.31
6.33
10%
101
7.59
6.85
6.88
50%
467
9.48
9.26
9.11
forwarding chain, 8 tail calls
0%
1.54
1.34
1.52
1.59
50%
188
4.74
4.31
4.28
void through 4 calls0%
3.00
3.09
3.05
3.06
1%
9.82
3.33
3.32
3.31
16-byte struct (two registers)
0%
3.01
3.24
3.06
3.06
32-byte struct in memory
0%
5.40
6.06
5.75
5.44
1%
11.3
6.03
5.60
5.43
50%
314
6.26
5.93
5.92
pointer, single call
0%
0.75
0.95
0.75
0.75
50%
188
3.66
3.29
3.08
We notice several things. First, the pointer strategy
often has noticeable overhead due to additional memory instructions, and
is inferior to the other two. register and
carry are nearly identical, which, given the fragility of
the carry approach, means that register seems
to be the preferred choice. Compared to classical C++ exceptions,
performance of register is within 1-2% on the happy path,
where no exception occurs, and it is dramatically faster, up to orders
of magnitude, if exceptions indeed occur.
A more realistic workload
These numbers over-inflate the differences because the functions do
so little work. As a more plausible workload, we use RapidJSON and benchmark
the performance on the nativejson-benchmark,
optionally corrupting some inputs to trigger exceptions. RapidJSON can
be configured to report failures by error codes instead of exceptions;
we call that strategy codes here.
The full results are here;
we show a selection below. What the numbers basically show is 1)
deterministic exceptions with the register strategy have
the same performance and the same code sizes as explicit error codes, 2)
performance differences from traditional C++ exceptions are largely
within the noise even on the happy path, and 3) in the case of errors
they are vastly superior to traditional C++ exceptions.
Happy path: SAX parsing
MB/s, higher is better, best of 5 runs; in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
canada.json
1635.8
1606.6 (-1.8%)
1665.1 (+1.8%)
1606.1 (-1.8%)
1603.3 (-2.0%)
citm_catalog.json
2212.7
2216.0 (+0.1%)
2356.3 (+6.5%)
2206.3 (-0.3%)
2341.5 (+5.8%)
twitter.json
1365.2
1376.5 (+0.8%)
1425.4 (+4.4%)
1377.5 (+0.9%)
1472.0 (+7.8%)
Happy path: retired
instructions per parse
Instructions (perf stat), lower is better; in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
sax canada.json
49555378
49903680 (+0.7%)
50294221 (+1.5%)
50850829 (+2.6%)
50738702 (+2.4%)
sax citm_catalog.json
20552806
20212949 (-1.7%)
20761272 (+1.0%)
20802928 (+1.2%)
20730816 (+0.9%)
sax twitter.json
11758968
11547972 (-1.8%)
11799096 (+0.3%)
11850736 (+0.8%)
11813765 (+0.5%)
Errors:
small documents, a fraction of them corrupted
ns per document, lower is better, best of 5 runs; in parentheses:
change relative to codes.
codesdynamicpointerregistercarry
0%
1726.1
1692.2 (-2.0%)
1729.3 (+0.2%)
1720.5 (-0.3%)
1721.0 (-0.3%)
1%
1707.3
1812.3 (+6.2%)
1703.3 (-0.2%)
1707.7 (+0.0%)
1713.9 (+0.4%)
10%
1600.8
1848.9 (+15.5%)
1597.3 (-0.2%)
1598.3 (-0.2%)
1614.3 (+0.8%)
50%
1256.5
1895.7 (+50.9%)
1263.6 (+0.6%)
1264.2 (+0.6%)
1275.3 (+1.5%)
100%
868.6
1934.7 (+122.7%)
871.8 (+0.4%)
869.3 (+0.1%)
882.1 (+1.6%)
Size of the parser
Bytes of all GenericReader functions,
GenericDocument::ParseStream and parseSAX
(into which parts of the parser are inlined); in parentheses: change
relative to codes.
codesdynamicpointerregistercarry
code
17191
18162 (+5.6%)
17540 (+2.0%)
17204 (+0.1%)
17289 (+0.6%)
unwind info
1292
1500 (+16.1%)
1340 (+3.7%)
1268 (-1.9%)
1248 (-3.4%)
exception tables
0
388
0
0
0
total
18483
20050 (+8.5%)
18880 (+2.1%)
18472 (-0.1%)
18537 (+0.3%)
Conclusion
Given that “exceptions” might not always be that exceptional (e.g.,
when parsing large amounts of user input), deterministic exceptions
indeed seem to be a very good idea, and P0709 indeed should be adopted
by the C++ standard. Not that this is likely to happen, given the lack
of progress on this front, but now we at least have numbers to talk
about.
Signed, Unsigned, Misaligned: Lessons from Assembly to SQL
Remember how you learned counting before school?
One, two, buckle my shoe,
Three, four, knock at the door,
Five, six, pick up sticks,
Seven, eight, lay them straight,
Nine, ten, a big fat hen.
There were no negative numbers, no decimals, no fractions, and certainly no floating point numbers.
Of course, one of the reasons to start with natural integers is that it’s simple and maps to your fingers. But like your fingers, most things in the real world are described by positive numbers. The number of kids in the class, your age, the weight of your bag, and the amount of money you have (kids normally don’t have debt).1
Later in life, you learn about negative numbers and fractions. The difference between two values can be negative, years in history are before Christ, and of course your account balance. So you need lots of different number types to map all the data you gather. But for most things in life, unsigned integers are the right choice.
Whether it’s how much inventory is in stock, the number of rows in a table, the IP port number, the ID of a row in a database, an array index, or loop iterations: pretty much everything in real life and a lot in computing is a positive number. This holds true especially in SQL where we have NULL-value semantics, so we don’t need -1 as a special case for unknown.
This article gives an overview of how we at CedarDB handle type information and make sure that your SQL query is both fast and correct. We cover why signedness matters, from the machine instructions your CPU runs up to the columns you declare, and why a Parquet file full of unsigned integers is what finally made us expose them in SQL.
Being a compiling system, we need several type systems that interoperate to make sure sign information does not get lost on the way from SQL user input through our C++ implementation down to the generated machine code. We walk down through those layers in a follow-up post.
Everything Is a Number
Integer types are the foundation of every programming language. Whether you are a systems programmer, embedded developer, or web developer, you always need them.
They come in different flavors: signed for differences and offsets, unsigned for identifiers and addresses.
But in the end, it all boils down to ones and zeros we interpret differently depending on what we need. uint32_t, int, and float are all 4 bytes in size, as is the emoji 😀 in UTF-8.
For example, the emoji 😀 is stored as the 4 bytes 0xF0 0x9F 0x98 0x80 when we encode it as UTF-8.2 Read as one 32-bit value, that is 0xF09F9880. Feed those exact same bytes to different types and you get:3
Type
Value
uint32_t4036991104
int32_t-257976192
float≈ -3.95e29
char[4] (UTF-8)😀
As you can see, the same bits can mean wildly different things.
Isn’t Number Enough of a Type?
In assembly, the bedrock of programming, we notice there are no types. If you have never looked at assembly before, think of registers as named buckets of bits.
There are just registers and they can store everything. The only differentiation is the size, whether it’s 8 bits, 8 bytes, or a larger vector register (x86 register overview).4
The same storage can be addressed at four different widths.
But is the fact that assembly has no types even right?
How can the computer differentiate between the two comparisons if there are no types?
bool biggerSigned(const int8_t* s, int limit) {
return *s > limit;
}
bool biggerUnsigned(const uint8_t* u, unsigned limit) {
return *u > limit;
}
When looking at the assembly, we see that registers don’t have types but assembly does.
Depending on the signedness, we use different assembly instructions.
In this case, either set if less setl for the signed case, or set if below setb for the unsigned case. On x86 less and greater means signed, while below and above indicate unsigned comparisons.
; *s > limit (signed)
movsx eax, byte ptr [rdi] ; sign-extend s into register
cmp esi, eax
setl al ; set al=1 if less (signed)
; *u > limit (unsigned)
movzx eax, byte ptr [rdi] ; zero-extend u into register
cmp esi, eax
setb al ; set al=1 if below (unsigned)
The result depends on instruction and MSB of the contained value. Nothing in the register says whether `AL` holds 200 or -56.
Notice that the load instruction differs too: movsx sign-extends the signed value, movzx zero-extends the unsigned one.
The same holds, e.g., for division instructions or moves with either zero or sign extension.
So the type is implicitly given by choosing the right instruction.
When reading assembly, we can still infer the data type in most places, but that’s quite tedious.
Encoding the type information in the operations means that whoever or whatever writes the assembly needs to keep track of the types. While this is standard procedure for compilers, it’s quite demanding for programmers, which is one of the reasons why most people don’t program in assembly any more. Tracking types by hand opens endless room for errors. Unless, of course, you have always been curious what sqrt(😀) is.5
High-Level Programming Languages
Now let’s go to the other extreme: how do high-level languages handle number types?
At the bottom of the ladder, JavaScript only has support for doubles and not even integers.6 PHP at least stores integers and floats differently. Neither differentiates between signed and unsigned, a clear sign of how far removed from the hardware these languages are.
Java, R, and SQL are a step up: they distinguish between integers and floats, so you won’t accidentally mix them and get flaky results due to numeric instabilities. But signed and unsigned remain the same type here too.
Java noticed the gap about 10 years ago and added the Integer.divideUnsigned function, and for PostgreSQL you can install an extension for custom unsigned types. Some control, but not the full picture.
Systems Programming
Full control over the datatypes, however, is crucial for systems programming. If you disagree, think of the first time you had to debug code like this:
unsigned count = 10;
while (count >= 0) {
std::cout << count << '\n';
count--;
}
If you don’t immediately see the problem, think of when count >= 0 holds true and why the answer is always true!
Static type checking can do a lot of good here. It’ll warn you at compile time that your code won’t terminate.
The mirror image happens with signed values. A classic trap is a count that can go negative meeting an unsigned loop bound:
int n = count_results(); // -1 on error
for (size_t i = 0; i < n; ++i) {
process(buffer[i]);
}
Signed intuition says that if n is -1, then 0 < -1 is false, so the loop body never runs. But n is compared against size_t i, so it is implicitly converted to SIZE_MAX, which is the largest unsigned value. Now 0 < SIZE_MAX is true, and the loop you expected to skip on error instead runs far past the end of the buffer — reading out of bounds almost immediately, and, absent a crash, looping for about 200 years on a 3 GHz machine.7
Both bugs have the same root cause: the programmer’s mental model uses signed semantics, but the machine applies unsigned arithmetic. This is also precisely why SQL’s NULL is a better tool than -1 for representing missing values.
Nullability vs Unsignedness
The intent of -1 in count_results() in the previous example was to mark an invalid value, since a count cannot be negative. The only reason it’s declared an int was the error case.
Comparing nullability with unsignedness seems odd at first glance. But they have two things in common.
First, strictly speaking, neither is a first class citizen of the SQL standard. Nullability is expressed as a constraint, and unsigned simply is not defined.
And second, you often use them to report invalid results, as shown above.
-1 to Mark Invalid Numbers
Way before C++ had optionals, SQL had nullability. This prevents programmers from the unfortunate habit of using -1 or even worse 99998 or January 1, 17539 for invalid values.
Using negative numbers just for error codes wastes half of the number range, which is by far not the worst thing here.
Imagine your unsigned data neatly stored into an int column with some -1 outliers. You cannot take an average, or sum.
You always need to handle your special values.
SQL nullability gives you a better tool: instead of hijacking a valid number to mean “nothing”, you declare that the value simply does not exist. No magic constants, no corrupted aggregates, no silent bugs.
Left: a tree with no leaves, which is still a tree. Right: no tree. The first is `0`, the second is `NULL`.
The number zero is not the same as nothing, like a tree without leaves is not the same as no tree.
Constraints in SQL
You can express unsigned numbers in plain PostgreSQL as:
CREATE TABLE inventory (
product_id INTEGER,
quantity INTEGER CHECK (quantity >= 0)
);
This gives us few advantages but big disadvantages. On the upside, we now have positive numbers in the column.
But the price we pay is that we waste half of the representable range and have to evaluate the constraint for each compute step.
You add two numbers, check the constraint, insert one, check the constraint. And you cannot even store larger numbers here. So it’s not worth it.
Furthermore it gives you a false sense of safety. It only guarantees that the values stored in the table are positive, not that they stay positive during a query. Subtract two quantities and you are negative again, so nothing downstream can rely on it either.
So we need both: values that may be absent (NULL), and values that are never negative (unsigned). SQL gives us only the first one.
Unsigned Types in CedarDB
The SQL standard does not define unsigned datatypes. To be fair to the standard, it also does not define how to page through a result set, so unsigned numbers are in good company here. The common workaround is constraints, but like NOT NULL, it makes more sense to implement the type properly. This gives it a wider range and avoids the overhead of constraint evaluation on every operation.
At CedarDB we believe you should store data in its most natural representation and avoid unnecessary conversions. So we expose the unsigned types we already used internally directly to the user. We used this as an opportunity to refactor our type system, making it easier to add new types and functions going forward.
The driving motivation for integration was Parquet. Parquet’s integer type carries a signedness flag right next to its bit width, and files in the wild use it for identifiers, offsets, counters, and byte sizes. If you are parsing Parquet data types, we can use the native unsigned type directly, otherwise UInt64 columns would have to be widened to 128-bit numerics just to avoid overflow. That costs memory and speed on every single value, for data that would perfectly fit into 64 bit wide registers.
Unlike in C and C++ which inherited it, unsigned does not lead to silent wrap-arounds. We check for overflow on signed and unsigned arithmetic alike, so subtracting 5 from a quantity of 3 raises an error instead of handing you 4294967294. How we do that without giving up performance is a story of its own, in our posts on overflow handling and vectorized overflow checking.
Unsigned types cast implicitly to any type that can always hold them, so mixing them with signed integers just works. Explicit casts let you convert back to unsigned whenever needed. So if you choose not to use unsigned numbers, you will never know they exist.
Using Unsigned Numbers
Everything sounds nice and easy, and using them is the same. Just declare the column with the type you want:
CREATE TABLE inventory (
product_id uint4,
quantity uint4
);
If you already worked with Parquet files following our examples, chances are high you’re already working with unsigned numbers and didn’t notice.
You can find more examples in the docs.
Making unsigned a first-class citizen throughout the whole code-generating system takes more than just adding it to the catalog. In the next blog post we go from the ground up through our type system. We will look where sign information is tracked and where it deliberately is not, why PostgreSQL’s oid is an unsigned in disguise, and what the System V ABI forgot to specify.
Want to see it in action? Load a Parquet file into CedarDB and the right unsigned types come along for free.
-
Although money is a decimal, it does not have to be. Reporting your net worth in cents is both technically correct and psychologically effective. ↩︎
-
How a character turns into bytes depends on the encoding. The same emoji is 0x00 0x01 0xF6 0x00 in UTF-32BE, and Python’s '😀'.encode('utf-32') even prepends a byte order mark. Some emoji are not a single codepoint at all, but a sequence glued together with zero-width joiners. This article covers the rest. ↩︎
-
Reading four bytes as one number is itself an interpretation. We use the big-endian reading here, the order we wrote the bytes in. On a little-endian machine such as x86, loading the same bytes into a uint32_t gives you 2157486064 instead. ↩︎
-
Yes, there are different registers for integers and floats. You can also store integers in vector registers for SIMD processing, or floats in regular registers for bit tricks. ↩︎
-
It’s NaN. sqrt takes a float, and as a float 😀 is negative. ↩︎
-
JavaScript engines do support integers internally, but only as a storage optimization for typed arrays, not as an exposed type. ↩︎
-
Yes, that’s an em-dash I put there. I liked to use them before they became a symbol of LLM generated content and it’s time, we are taking them back. For now I just put one into the post to not overdo it. ↩︎
-
In METAR, the standard format for aviation weather reports, horizontal visibility is given in meters and caps out at four digits, so 9999 means “10 km or more” rather than “9,999 meters”. See the METAR explanation in the IVAO documentation. ↩︎
-
Microsoft’s Date Data Type documentation for Dynamics NAV. The date range starts at January 1, 1753, which is the lower bound of SQL Server’s datetime, which in turn dates back to Britain switching to the Gregorian calendar in 1752. ↩︎
In many real production databases, we often see some tables receiving high-frequency updates (millions per day) and autovacuum running repeatedly (hundreds of times per day). Additionally, those tables can sometimes become heavily bloated. The end effect is poor performance, poor concurrency, and an unmanageable database. In case we are using methods pg_gather for diagnosis, such … Continued
The post PgQ and PgQue: Workflow Engines you might need in PostgreSQL appeared first on Percona.
Percona Toolkit’s pt-online-schema-change (pt-osc) has long been the preferred solution for performing online schema changes with minimal downtime. Its chunk-based copy algorithm is designed to adapt dynamically to the workload, making it suitable for very large tables in production environments. However, under specific conditions, one of its optimization mechanisms can become counterproductive. During a customer … Continued
The post When pt-online-schema-change “where” Meets Galera: Understanding Chunk Auto-Resize and Flow Control appeared first on Percona.
