DEV Community: Franck Pachot

GDPR data minimization with pg_duckdb: join Azure Blob facts to erasable customer data in Postgres

Franck Pachot — Thu, 15 Oct 2026 07:59:08 +0000

After the archive post, I got a good question: what happens when an old row must change after its year has been written to Parquet? Rewriting a large immutable file for every correction or deletion would defeat much of the purpose of the archive.

My first answer is to archive a fact only once the business considers it immutable. Historical sales shouldn’t change silently after reports are published. Personal data, however, follows a different lifecycle: a customer's name, email, phone number, or address may need to be corrected or erased even if the financial fact must remain unchanged.

That suggests a useful split:

The immutable sales facts go to Parquet. The identifying data remains in PostgreSQL, within the operational system's existing security and governance boundary: database roles and row-level rules, audited changes, constraints, transactional corrections and deletions, retention procedures, and controlled backup and restore processes. pg_duckdb joins both sources only when a query requires them.

The point is not that object storage cannot be secured. It is to avoid creating immutable analytical files from personal data, each of which would require its own access controls, audit trail, retention policy, backup handling, and erasure procedure. Under GDPR's data-minimization principle, keeping direct identifiers out of the archive also reduces the amount of personal data copied into the analytical tier.

This is where pg_duckdb adds more than just convenient file syntax. The PostgreSQL table and the Parquet file become two scan inputs for a single DuckDB relational plan. DuckDB chooses the join order and build side, pushes filters toward both sources, performs the hash join, and runs the aggregation in a single vectorized pipeline.

The query uses a LEFT JOIN with the archived facts on the preserved side, so deleting the matching identity leaves the sale in the result with NULL values in the personal columns.

Lab setup

The sales data is the same deterministic 10-million-row dataset used in the previous posts. There is no random(), so the counts below are reproducible.

SET duckdb.force_execution = false;

DROP TABLE IF EXISTS sales;
CREATE TABLE sales AS
SELECT g AS sale_id,
       (DATE '2021-01-01' + ((g * 7) % 1826))                        AS sale_date,
       1 + (g % 50000)                                               AS cust_id,
       1 + (g % 2000)                                                AS prod_id,
       1 + (g % 5)                                                   AS channel_id,
       (ARRAY['US','CH','FR','DE','UK','JP','BR','IN'])[1 + (g % 8)] AS country,
       (ARRAY['Consumer','SMB','Enterprise'])[1 + (g % 3)]           AS segment,
       1 + (g % 10)                                                  AS qty,
       ((5 + (g % 500))::numeric(10,2))                              AS amount
FROM generate_series(1, 10000000) g;
ANALYZE sales;

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
SELECT duckdb.create_azure_secret('<blob connection string>');

Build the source table on the PostgreSQL executor (force_execution = false). If DuckDB is forced on, it warns about +(DATE, BIGINT) in this generate_series DDL and falls back to PostgreSQL anyway. You don't need to force DuckDB for this example.

Now create the table that owns the erasable identity. The values are synthetic:

DROP TABLE IF EXISTS customer_identity;
CREATE TABLE customer_identity (
  customer_ref int PRIMARY KEY,
  full_name    text NOT NULL,
  email        text NOT NULL,
  phone        text NOT NULL,
  address      text NOT NULL
);

INSERT INTO customer_identity
SELECT g,
       format('Customer %s', lpad(g::text, 5, '0')),
       format('customer%s@example.test', lpad(g::text, 5, '0')),
       format('+41 22 555 %s', lpad(g::text, 4, '0')),
       format('%s Example Street, Test City', g)
FROM generate_series(1, 50000) g;

CREATE UNIQUE INDEX customer_identity_email_idx
  ON customer_identity (email);

ANALYZE customer_identity;

For readability, this lab reuses the sequential cust_id as customer_ref. In a real design, use a stable, opaque token with no business meaning, and keep the only mapping to an identity in the protected operational database.

Write a minimized fact file

When exporting the 2021 sales data, select only the immutable fact columns. Do not copy the name, email, phone, address, or other identifying free text into the file:

postgres=> SELECT duckdb.raw_query($$
             COPY (
               SELECT sale_id,
                      sale_date,
                      cust_id AS customer_ref,
                      prod_id,
                      channel_id,
                      qty,
                      amount
               FROM pgduckdb.public.sales
               WHERE sale_date < DATE '2022-01-01'
             ) TO 'az://lake/archive/sales_2021_minimized.parquet'
               (FORMAT parquet)
           $$)
;
 raw_query
-----------

(1 row)

As in the archive post, raw_query sends the COPY command to the embedded DuckDB engine, and pgduckdb.public.sales is the DuckDB catalog name for the PostgreSQL table.

Check both sides:

postgres=> SELECT count(*) FROM customer_identity
;
 count
-------
 50000
(1 row)

postgres=> SELECT count(*) AS n_sales,
                  count(DISTINCT (s['customer_ref'])::int) AS n_customers
           FROM read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
;
 n_sales | n_customers
---------+-------------
 1998938 | 50000
(1 row)

The file has 1,998,938 immutable sales and only an opaque bridge to the 50,000 identity rows kept in PostgreSQL.

The join before erasure

Customer 42 has 39 sales in the archived year. The query resolves the bridge to the live identity using a LEFT OUTER JOIN:

postgres=> SELECT
             COALESCE(c.full_name, '[erased customer]') AS customer_name,
             COALESCE(c.email, '[removed]') AS email,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
           LEFT OUTER JOIN customer_identity c
             ON c.customer_ref = (s['customer_ref'])::int
           WHERE (s['customer_ref'])::int = 42
           GROUP BY c.full_name, c.email
;
 customer_name  | email                      | n_sales | revenue
----------------+----------------------------+---------+---------
 Customer 00042 | customer00042@example.test | 39      | 1794.00
(1 row)

No SET duckdb.force_execution = true is required. DuckDB automatically executes any statement that references read_parquet, including the scan of the PostgreSQL table and the join.

Delete the identity, not the history

Now process the erasure in the operational database:

postgres=> DELETE FROM customer_identity WHERE customer_ref = 42
;
DELETE 1

Run exactly the same join again:

postgres=> SELECT
             COALESCE(c.full_name, '[erased customer]') AS customer_name,
             COALESCE(c.email, '[removed]') AS email,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
           LEFT OUTER JOIN customer_identity c
             ON c.customer_ref = (s['customer_ref'])::int
           WHERE (s['customer_ref'])::int = 42
           GROUP BY c.full_name, c.email
;
 customer_name     | email     | n_sales | revenue
-------------------+-----------+---------+---------
 [erased customer] | [removed] | 39      | 1794.00
(1 row)

The 39 sales and their 1,794.00 in revenue remain. The name and email are gone. I used COALESCE to show a value for nulls, but NULL is semantically correct: the customer exists, but their personal data is unknown.

After the deletion, all 1,998,938 archived facts are still counted:

postgres=> SELECT
             CASE WHEN c.customer_ref IS NULL THEN 'erased'
                  ELSE 'identified'
             END AS identity_status,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
           LEFT JOIN customer_identity c
             ON c.customer_ref = (s['customer_ref'])::int
           GROUP BY CASE WHEN c.customer_ref IS NULL THEN 'erased'
                         ELSE 'identified'
                    END
           ORDER BY identity_status
;
 identity_status | n_sales | revenue
-----------------+---------+--------------
 erased          | 39      | 1794.00
 identified      | 1998899 | 508736609.00
(2 rows)

It's always important to review the execution plan because SQL code is an abstraction that doesn't show how it's executed or how performant and scalable it is.

The full plan: which side is hashed?

Here is the full EXPLAIN (ANALYZE, COSTS OFF) from Azure Database for PostgreSQL:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT
             CASE WHEN c.customer_ref IS NULL THEN 'erased'
                  ELSE 'identified'
             END AS identity_status,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
           LEFT OUTER JOIN customer_identity c
             ON c.customer_ref = (s['customer_ref'])::int
           GROUP BY CASE WHEN c.customer_ref IS NULL THEN 'erased'
                         ELSE 'identified'
                    END
           ORDER BY identity_status
;
                   QUERY PLAN
-------------------------------------------------
Custom Scan (DuckDBScan) (actual time=0.001..0.003 rows=0.00 loops=1)
  DuckDB Execution Plan:

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE  SELECT CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END AS identity_status, count(*) AS n_sales, sum((s.amount)::numeric(10,2)) AS revenue FROM (system.main.read_parquet('az://lake/archive/sales_2021_minimized.parquet'::text) s LEFT JOIN pgduckdb.public.customer_identity c ON ((c.customer_ref = (s.customer_ref)::integer))) GROUP BY CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END ORDER BY CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.515s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_decompress_strin│
│           g(#0)           │
│             #1            │
│             #2            │
│                           │
│           2 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          ORDER_BY         │
│    ────────────────────   │
│       CASE  WHEN ((c      │
│  .customer_ref IS NULL))  │
│   THEN (CAST('erased' AS  │
│    VARCHAR)) ELSE CAST(   │
│  'identified' AS VARCHAR) │
│           END ASC         │
│                           │
│           2 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_compress_string_│
│        uhugeint(#0)       │
│             #1            │
│             #2            │
│                           │
│           2 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_decompress_strin│
│           g(#0)           │
│             #1            │
│             #2            │
│                           │
│           2 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│        count_star()       │
│          sum(#1)          │
│                           │
│           2 rows          │
│          (0.02s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_compress_string_│
│   uhugeint(CASE  WHEN (   │
│  (customer_ref IS NULL))  │
│    THEN ('erased') ELSE   │
│     'identified' END)     │
│           amount          │
│                           │
│       1,998,938 rows      │
│          (0.01s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: LEFT      │
│                           │
│        Conditions:        ├──────────────┐
│customer_ref = customer_ref│              │
│                           │              │
│       1,998,938 rows      │              │
│          (0.08s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Function:         ││           Table:          │
│        READ_PARQUET       ││     customer_identity     │
│                           ││                           │
│        Projections:       ││        Projections:       │
│        customer_ref       ││        customer_ref       │
│           amount          ││                           │
│                           ││                           │
│    Total Files Read: 1    ││                           │
│                           ││                           │
│       1,998,938 rows      ││        49,999 rows        │
│          (0.11s)          ││          (0.01s)          │
└───────────────────────────┘└───────────────────────────┘


Planning:
  Buffers: shared hit=1
Planning Time: 223.697 ms
Execution Time: 223.914 ms
(126 rows)

One top-level engine over heterogeneous sources

The outer PostgreSQL plan is a single Custom Scan (DuckDBScan). Inside it, the DuckDB plan includes both leaves—the Blob READ_PARQUET scan and the PostgreSQL customer_identity scan—and the HASH_JOIN and HASH_GROUP_BY above them. DuckDB, not PostgreSQL, owns the cross-source join and aggregation.

That is different from the read-only foreign-data-wrapper solution. With a conventional FDW, PostgreSQL remains the top-level relational engine: the foreign side returns rows or a pushed-down fragment, and PostgreSQL typically joins the result with local tables. Here, DuckDB sees both sides together and can optimize the pipeline across the storage boundary, including the runtime join filter. Everything between the two scans and the result is one vectorized pipeline: the Parquet scan streams ~2M rows, probes the hash table, and HASH_GROUP_BY reduces them to 2 rows without materializing the join output.

DuckDB, a single engine, executes the query with all its analytics optimizations, while PostgreSQL continues to read its own storage. The PGDUCKDB_POSTGRES_SCAN leaf delegates to SQL, where the PostgreSQL optimizer chooses the heap or index access path. PostgreSQL handles only this scan leaf. DuckDB controls the join order, hash table, filtering between inputs, grouping, and final result.

Read a DuckDB plan bottom-up. A physical HASH_JOIN builds its in-memory hash table from the right child, then streams the left child through it:

The right child is customer_identity: 49,999 PostgreSQL rows, with only customer_ref projected. This is the side that is hashed.
The left child is READ_PARQUET: 1,998,938 Blob rows, with only customer_ref and amount projected. This is the probe side.
The join emits all 1,998,938 facts, then HASH_GROUP_BY reduces them to two rows.

Hashing 49,999 integers once is the natural shape. A nested loop would prevent DuckDB from performing a B-tree lookup in Parquet. It would replace one small hash build with repeated file probes. Even when the customer filter below leaves one row, DuckDB keeps a hash join because hashing one key is negligible.

What statistics does DuckDB see?

An EXPLAIN without ANALYZE (the default, or explicitly EXPLAIN (ANALYZE OFF, COSTS ON)) displays the estimated cardinalities (DuckDB prefixes them with ~):


 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_strin│
 │           g(#0)           │
 │             #1            │
 │             #2            │
 │                           │
 │          ~0 rows          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │          ORDER_BY         │
 │    ────────────────────   │
 │       CASE  WHEN ((c      │
 │  .customer_ref IS NULL))  │
 │   THEN (CAST('erased' AS  │
 │    VARCHAR)) ELSE CAST(   │
 │  'identified' AS VARCHAR) │
 │           END ASC         │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_string_│
 │        uhugeint(#0)       │
 │             #1            │
 │             #2            │
 │                           │
 │       ~999,469 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_strin│
 │           g(#0)           │
 │             #1            │
 │             #2            │
 │                           │
 │       ~999,469 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │       ~999,469 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_string_│
 │   uhugeint(CASE  WHEN (   │
 │  (customer_ref IS NULL))  │
 │    THEN ('erased') ELSE   │
 │     'identified' END)     │
 │           amount          │
 │                           │
 │      ~1,998,938 rows      │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │    ────────────────────   │
 │      Join Type: LEFT      │
 │                           │
 │        Conditions:        ├──────────────┐
 │customer_ref = customer_ref│              │
 │                           │              │
 │      ~1,998,938 rows      │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │       READ_PARQUET        ││  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   ││    ────────────────────   │
 │         Function:         ││           Table:          │
 │        READ_PARQUET       ││     customer_identity     │
 │                           ││                           │
 │        Projections:       ││        Projections:       │
 │        customer_ref       ││        customer_ref       │
 │           amount          ││                           │
 │                           ││                           │
 │      ~1,998,938 rows      ││        ~50,000 rows       │
 └───────────────────────────┘└───────────────────────────┘

Those estimates come from different places:

Parquet carries its row counts and per-row-group min/max statistics in the file metadata.
For a PostgreSQL relation, pg_duckdb 1.1.0 calls PostgreSQL's estimate_rel_size and passes that cardinality to DuckDB.
It does not expose PostgreSQL's per-column histograms, most-common values, or distinct-value estimates to DuckDB (PostgresTable::GetStatistics is not implemented in this release).

This explains the mixed quality of the estimates. DuckDB knows the inputs contain about 2 million and 50,000 rows, enough to place the small relation on the hash-build side. It does not predict that the final CASE expression has exactly two groups: the plain plan estimates ~999,469 groups, whereas the analyzed plan produces 2.

ANALYZE customer_identity still matters. PostgreSQL statistics maintain the relation-size estimate supplied to DuckDB, and PostgreSQL's own planner uses its full local statistics when pg_duckdb delegates a filtered scan back to it.

What if the filter starts from the customer?

The setup already created customer_identity_email_idx. Now ask for the archived sales of one email address. This deliberately uses an INNER JOIN: the question asks for one known identity, so unmatched or erased identities are irrelevant:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT
             c.full_name,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM customer_identity c
           JOIN read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
             ON (s['customer_ref'])::int = c.customer_ref
           WHERE c.email = 'customer00043@example.test'
           GROUP BY c.full_name
;

                                                                                                                                                                             QUERY PLAN                                                                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.002..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT c.full_name, count(*) AS n_sales, sum((s.amount)::numeric(10,2)) AS revenue FROM (pgduckdb.public.customer_identity c JOIN system.main.read_parquet('az://lake/archive/sales_2021_minimized.parquet'::text) s ON (((s.customer_ref)::integer = c.customer_ref))) WHERE (c.email = 'customer00043@example.test'::text) GROUP BY c.full_name
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.437s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │         full_name         │
 │           amount          │
 │                           │
 │          39 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │    ────────────────────   │
 │      Join Type: INNER     │
 │                           │
 │        Conditions:        ├──────────────┐
 │customer_ref = customer_ref│              │
 │                           │              │
 │          39 rows          │              │
 │          (0.00s)          │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         TABLE_SCAN        ││         TABLE_SCAN        │
 │    ────────────────────   ││    ────────────────────   │
 │         Function:         ││           Table:          │
 │        READ_PARQUET       ││     customer_identity     │
 │                           ││                           │
 │        Projections:       ││        Projections:       │
 │        customer_ref       ││        customer_ref       │
 │           amount          ││         full_name         │
 │                           ││                           │
 │    Total Files Read: 1    ││          Filters:         │
 │                           ││    email='customer00043   │
 │                           ││       @example.test'      │
 │                           ││                           │
 │          39 rows          ││           1 row           │
 │          (0.12s)          ││          (0.00s)          │
 └───────────────────────────┘└───────────────────────────┘


 Planning:
   Buffers: shared hit=1
 Planning Time: 566.542 ms
 Execution Time: 213.829 ms
(78 rows)

The bottom of EXPLAIN (ANALYZE, COSTS OFF) is:

┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│customer_ref = customer_ref│              │
│                           │              │
│          39 rows          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Function:         ││           Table:          │
│        READ_PARQUET       ││     customer_identity     │
│                           ││                           │
│        Projections:       ││        Projections:       │
│        customer_ref       ││        customer_ref       │
│           amount          ││         full_name         │
│                           ││                           │
│    Total Files Read: 1    ││          Filters:         │
│                           ││    email='customer00043   │
│                           ││       @example.test'      │
│                           ││                           │
│          39 rows          ││           1 row           │
└───────────────────────────┘└───────────────────────────┘

It starts with the selective customer side. The right child produces one row and becomes the hash-build side. DuckDB then applies the resulting join-key range while scanning Parquet: the READ_PARQUET leaf emits 39 rows, not 1,998,938. The runtime join filter is not printed as a static Filters: line in this plan, but the scan's actual row count makes it visible. When the predicate is written directly on the file side, the plan does print Filters: customer_ref=43.

Many databases push join filters only through a nested loop. In PostgreSQL, for example, each outer row re-parameterizes an inner index scan. YugabyteDB batches these scans to amortize the probes. Oracle and SQL Server instead attach a Bloom or bitmap filter to the hash join, allowing the probe scan to discard non-matching keys. DuckDB takes this latter approach, optimized for analytics: while building a hash table from the one-row customer_identity side, it derives a dynamic filter on the join key and injects it as a table filter into the probe-side READ_PARQUET scan.

The PostgreSQL table is not a blind full scan either. duckdb.log_pg_explain shows the inner SQL that pg_duckdb delegated and the access path PostgreSQL chose:

postgres=> SET duckdb.log_pg_explain = true;
SET

postgres=> SELECT
             c.full_name,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM customer_identity c
           JOIN read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
             ON (s['customer_ref'])::int = c.customer_ref
           WHERE c.email = 'customer00043@example.test'
           GROUP BY c.full_name
;

NOTICE:  (PGDuckDB/PostgresTableReader)

QUERY: SELECT customer_ref, full_name FROM public.customer_identity WHERE email='customer00043@example.test'

RUNNING: IN PROCESS THREAD.

EXECUTING:

Parallel Index Scan using customer_identity_email_idx on customer_identity
  Index Cond: (email = 'customer00043@example.test'::text)

 full_name      | n_sales | revenue
----------------+---------+---------
 Customer 00043 | 39      | 1833.00
(1 row)

DuckDB does not manage PostgreSQL indexes directly. It pushes columns and predicates into an inner PostgreSQL query, allowing the PostgreSQL optimizer to choose a sequential, bitmap, index, or index-only scan. Here:

postgres=> EXPLAIN (COSTS OFF)
       SELECT customer_ref, full_name
       FROM public.customer_identity
       WHERE email='customer00043@example.test'
;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using customer_identity_email_idx on customer_identity
   Index Cond: (email = 'customer00043@example.test'::text)
(2 rows)

For the unfiltered full join above, which requires only customer_ref, the delegated plan was a Parallel Index Only Scan using customer_identity_pkey on customer_identity:

postgres=> EXPLAIN  (COSTS OFF)
           SELECT customer_ref FROM public.customer_identity
;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather
   Workers Planned: 1
   ->  Parallel Index Only Scan using customer_identity_pkey on customer_identity
(3 rows)

The flow works in the other direction too. With WHERE (s['customer_ref'])::int = 43 on the Parquet side, the delegated SQL became SELECT customer_ref, full_name FROM public.customer_identity WHERE customer_ref=43, and PostgreSQL chose Parallel Index Scan using customer_identity_pkey.

Using DuckDB over PostgreSQL via pg_duckdb, rather than PostgreSQL over DuckDB via a foreign data wrapper, is a major advantage here. DuckDB was designed for analytics, while PostgreSQL was primarily designed for OLTP. Regardless of whether storage is row- or columnar-based, the join algorithms are designed accordingly.

Pushdown is not the same as row-group pruning

The filter reaches READ_PARQUET, but whether that avoids I/O depends on how the file is laid out. Inspect the customer_ref statistics stored in three of this file's 17 row groups:

postgres=> SELECT *
           FROM duckdb.query($duckdb$
             SELECT row_group_id,
                    num_values AS n,
                    stats_min AS min_customer_ref,
                    stats_max AS max_customer_ref
             FROM parquet_metadata(
               'az://lake/archive/sales_2021_minimized.parquet'
             )
             WHERE path_in_schema = 'customer_ref'
               AND row_group_id IN (0, 1, 16)
             ORDER BY row_group_id
           $duckdb$)
;
 row_group_id | n      | min_customer_ref | max_customer_ref
--------------+--------+------------------+------------------
 0            | 122880 | 1                | 50000
 1            | 122880 | 1                | 50000
 16           | 32858  | 1                | 50000
(3 rows)

The generator I used cycles through customer IDs, so every row group can include customer 43. DuckDB filters correctly, but min/max statistics cannot eliminate any row group. This is why a highly selective result does not automatically mean a cheap object-storage read.

If customer-based lookups are important, sort by customer_ref when writing each time partition. I tested an alternate export with ORDER BY cust_id: its first row group covered customers 1–3,072, the next 3,072–6,148, and the last 49,179–50,000. A lookup for customer 43 can then reject 16 of 17 row groups based on metadata. For frequent point lookups with low-latency requirements, materializing the facts into an indexed PostgreSQL table may still be a better physical design than querying Parquet.

The 39-row output of READ_PARQUET in the earlier plan shows the filter still ran at the row level — it saved decompression and hash probes, just not reads. With Parquet, there are two levels of pruning, and only metadata-level pruning avoids touching a row group.

What this pattern does and does not guarantee

Keeping identity out of immutable files does more than avoid rewrites. It keeps the authoritative personal record in the operational database, where a single governed update or deletion can take effect across every analytical query, rather than requiring the same action across many Parquet objects and downstream copies.

But the boundary must be designed carefully:

Data minimization matters. Do not put names, emails, phone numbers, addresses, or identifying free text in the fact file just because Parquet makes it convenient.
customer_ref is pseudonymous while another system can map it back to a person. The sequential number in this lab is only for reproducibility; production should use a non-meaningful, opaque token and control every mapping.
PostgreSQL becomes the authoritative location for identity lifecycle rules: who may read personal data, who may correct or erase it, what must be audited, and how retention is enforced.
The query is not a permission boundary. Users who can query the base tables and files can still see the bridge key. In production, expose a reviewed view or function and restrict direct access to both sources.
Deleting the live row does not retroactively remove it from historical backups. Erasure procedures must also define how replicas, exports, caches, logs, downstream results, backups, and restored environments are handled under the applicable retention policy.
Some records may have legal retention requirements. Whether data must be erased, retained, quarantined, or anonymized is a policy and legal decision, not something pg_duckdb decides.

This architecture gives governance a clear boundary: personal identity remains under PostgreSQL's operational controls, while the analytical tier receives only minimized, pseudonymous facts. It supports a GDPR program; it does not by itself prove compliance.

Takeaways

Keep immutable, minimized transaction facts in Parquet and personal identity in the governed operational database.
Join them on an opaque reference only when a query needs both.
The central pg_duckdb value is a DuckDB relational plan that spans heterogeneous Blob and PostgreSQL inputs, with an analytic-optimized engine, rather than coordinating separate local and foreign execution fragments.
The full join hashes the smaller PostgreSQL input and streams the Parquet facts through it.
pg_duckdb supplies base-table cardinality to DuckDB, while PostgreSQL still chooses the access path for delegated scans and can use its indexes.
Customer-side filters can reduce both inputs, but Parquet row-group pruning depends on physical ordering and min/max statistics.
For repeated selective lookups, sort within each time partition or materialize into an indexed table rather than assuming object storage behaves like an OLTP index.

One file is enough to demonstrate the join. A real archive grows into a tree of yearly or monthly files. In the next post, I will lay out that tree using Hive-style partitions and show DuckDB pruning out every file the query does not need.

Automate Postgres-to-Blob with durable workflow (pg_durable + pg_duckdb) in Azure Database for PostgreSQL

Franck Pachot — Tue, 13 Oct 2026 09:07:59 +0000

In the archive post I moved cold rows out of a Postgres table into Parquet on Azure Blob and kept querying them. The steps were manual: export to Blob, verify the file, delete the rows, record what happened. If the process dies after the export but before the delete, you're stuck — re-run and you re-export (wasteful), skip ahead and you might delete rows that were never safely written (dangerous). That partial-failure gap is exactly what a durable workflow closes.

Azure Database for PostgreSQL ships another open-source Microsoft extension that fits here: pg_durable. It runs checkpointed, crash-safe workflows inside Postgres — define a graph of SQL steps, and the engine persists progress between them. If the database restarts or a step fails, execution resumes from the last completed step instead of starting over. No cron-plus-status-table-plus-worker scaffolding. This post turns the archive into one durable, schedulable workflow, with pg_duckdb doing the write to Blob.

Enabling pg_durable

Like pg_duckdb, pg_durable loads through shared_preload_libraries, so it needs allow-listing and a restart:

Add pg_durable to azure.extensions (the allow-list).
Add pg_durable to shared_preload_libraries (append to the current value, next to pg_duckdb) and restart the server.

Then create both extensions in the database:

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE EXTENSION IF NOT EXISTS pg_durable;
SELECT duckdb.create_azure_secret('<blob connection string>');

pg_durable runs its steps in a background worker. Right after a restart that worker takes a moment to come up. The first df.start() can return pg_durable background worker not yet initialized — try again in a moment. Wait a few seconds and retry. A normal (non-superuser) login also needs a one-time grant:

SELECT df.grant_usage(current_user);

A quick smoke test confirms the engine runs:

postgres=> SELECT df.start('SELECT 1 AS ok')
;

 start
----------
 50a27f7d

(1 row)

postgres=> SELECT df.status('50a27f7d')
;

  status
-----------
 completed

(1 row)

postgres=> SELECT df.result('50a27f7d')
;

             result
--------------------------------
 {"rows": [{"ok": 1}], "row_count": 1}

(1 row)

The workflow status, errors, and results are persisted for each step and you can query them with pg_durable functions and tables.

The data to archive

A small orders table spanning three years — the 2023 rows are the cold slice we'll move out:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders AS
SELECT g AS order_id,
       (DATE '2023-01-01' + ((g * 7) % 1095))::date AS order_date,
       ((g % 500) + 1)::numeric(10,2)              AS amount
FROM generate_series(1, 1000) g;

CREATE TABLE IF NOT EXISTS archive_log (
  id            serial,
  archived_year int,
  n_rows        bigint,
  blob          text,
  at            timestamptz DEFAULT now()
);

Here is the current data in this table:

postgres=> -- total number of rows
postgres=> SELECT count(*) FROM orders;
 count
-------
  1000

postgres=> -- the cold slice
postgres=> SELECT count(*) FROM orders 
           WHERE order_date < DATE '2024-01-01'
;
 count
-------
   364

I have a reproducible test case where I want to archive orders from before 2024. I'll create a one-time workflow for that and let it run.

The durable workflow

The whole archive is one df.start():

steps are chained with ~>
step's result is captured into a variable with |=> 'name'
next step can reuse the result as $name
if-then-else is declared with (), ?>, !>

SELECT df.start(
  -- 1. count the cold slice, capture it as $n
     $Step1$
        SELECT count(*) AS n FROM orders WHERE order_date < DATE '2024-01-01'
     $Step1$ |=> 'n'
  -- 2. write those rows to Blob as Parquet, through the DuckDB engine
  ~> $Step2$ 
        SELECT duckdb.raw_query(
        $DuckDB$
           COPY (
             SELECT order_id, order_date, amount
             FROM pgduckdb.public.orders
             WHERE order_date < DATE '2024-01-01'
           ) TO 'az://lake/archive/orders_2023.parquet' (FORMAT parquet)
        $DuckDB$
        )
     $Step2$
  -- 3. verify the file reads back, capture the row count as $v
  ~> $Step3$
        SELECT count(*) AS v
        FROM read_parquet('az://lake/archive/orders_2023.parquet')
     $Step3$ |=> 'v'
  -- 4. delete only if the archive count matches
  ~> ( $Check$ SELECT $v = $n AS archive_complete $Check$
       ?> $Del$  DELETE FROM orders WHERE order_date < DATE '2024-01-01' $Del$
       !> $Fail$ DO $do$ BEGIN
                   RAISE EXCEPTION 'archive incomplete: expected % rows, file has %', $n, $v;
                 END $do$;
                $Fail$
     )
  -- 5. record what happened
  ~> $Step5$
        INSERT INTO archive_log(archived_year, n_rows, blob)
        VALUES (2023, $n, 'az://lake/archive/orders_2023.parquet')
     $Step5$,
  'durable-archive'   -- a label to find the run by
);

Two things about the write step (step 2), both carried over from the archive post:

The blob write goes through duckdb.raw_query(), not plain COPY TO 'az://…' so that DuckDB authorizes the write according to its own secret and allow-list — the object-storage controls that PostgreSQL's server-file gate knows nothing about and would otherwise reject.
Inside raw_query you're in DuckDB's catalog, so the table is pgduckdb.public.orders, not a bare orders.

The condition is a safety check to run the delete only when the count matches, or fail otherwise.

And a word on the quoting: the steps are SQL statements declared as text arguments for df.start() and themselves include nested text. This would require multiple levels of double quotes in standard SQL. It is solved easily with PostgreSQL dollar quoting ($$ with an optional label inside).

Watching it run

df.start() returns immediately with an instance id. The worker executes the steps. You can check progress directly with the id or getting the last id with your label:

postgres=> SELECT instance_id, label, status, execution_count
           FROM df.list_instances()
           WHERE instance_id = (SELECT id FROM df.instances
                                WHERE label = 'durable-archive'
                                ORDER BY created_at DESC LIMIT 1)
;
 instance_id |      label      |  status   | execution_count
-------------+-----------------+-----------+-----------------
 b9b24177    | durable-archive | completed |               1

(1 row)

The steps are visible with df.explain():

postgres=> SELECT df.explain('b9b24177')
;
                                explain
-----------------------------------------------------------------------
 Instance: b9b24177 (durable-archive)                                 +
 Status:   ✓ Completed                                                +
 Output:   {"rows":[],"row_count":0}                                  +
                                                                      +
 SQL:                                                                 +
         SELECT count(*) AS n FROM orders WHER... |=> 'n' ✓           +
 → SQL:                                                               +
         SELECT duckdb.raw_query(                                     +
         $Du... ✓                                                     +
 → SQL:                                                               +
         SELECT count(*) AS v                                         +
         FROM re... ✓                                                 +
 → IF ✓                                                               +
     ? condition:                                                     +
       └─SQL:  SELECT $v = $n AS archive_complete  ✓                  +
     ✓ then:                                                          +
       └─SQL:   DELETE FROM orders WHERE order_date < DATE '2... ✓    +
     ✗ else:                                                          +
       └─SQL:  DO $do$ BEGIN                                          +
                    RAISE EXCEPT... ⊘                                 +
 → SQL:                                                               +
         INSERT INTO archive_log(archived_year... ✓
(1 row)

Each step is a node you can inspect — this is the checkpoint record:

postgres=> SELECT node_type, status, query
           FROM df.nodes
           WHERE instance_id = (SELECT id FROM df.instances
                                WHERE label = 'durable-archive'
                                ORDER BY created_at DESC LIMIT 1)
           ORDER BY updated_at
;
 node_type |  status   |                                             query
-----------+-----------+------------------------------------------------------------------------------------------------
 SQL       | pending   |  DO $do$ BEGIN                                                                                +
           |           |                    RAISE EXCEPTION 'archive incomplete: expected % rows, file has %', $n, $v; +
           |           |                  END $do$;                                                                    +
           |           |
 SQL       | completed |                                                                                               +
           |           |         SELECT count(*) AS n FROM orders WHERE order_date < DATE '2024-01-01'                 +
           |           |
 SQL       | completed |                                                                                               +
           |           |         SELECT duckdb.raw_query(                                                              +
           |           |         $DuckDB$                                                                              +
           |           |            COPY (                                                                             +
           |           |              SELECT order_id, order_date, amount                                              +
           |           |              FROM pgduckdb.public.orders                                                      +
           |           |              WHERE order_date < DATE '2024-01-01'                                             +
           |           |            ) TO 'az://lake/archive/orders_2023.parquet' (FORMAT parquet)                      +
           |           |         $DuckDB$                                                                              +
           |           |         )                                                                                     +
           |           |
 THEN      | completed |
 SQL       | completed |                                                                                               +
           |           |         SELECT count(*) AS v                                                                  +
           |           |         FROM read_parquet('az://lake/archive/orders_2023.parquet')                            +
           |           |
 THEN      | completed |
 SQL       | completed |  SELECT $v = $n AS archive_complete
 SQL       | completed |   DELETE FROM orders WHERE order_date < DATE '2024-01-01'
 IF        | completed | {"condition_node":"3149a512"}
 THEN      | completed |
 SQL       | completed |                                                                                               +
           |           |         INSERT INTO archive_log(archived_year, n_rows, blob)                                  +
           |           |         VALUES (2023, $n, 'az://lake/archive/orders_2023.parquet')                            +
           |           |
 THEN      | completed |
(12 rows)

The gate adds nodes: the SELECT $v = $n AS archive_complete check, its IF node, and both branches — the DELETE ran and is completed, while the DO ... RAISE EXCEPTION branch stayed pending because it was never taken.

It worked, end to end

As expected by the completed workflow, the archived file is in Blob that I can query though pg_duckdb functions:

postgres=> SELECT count(*) FROM read_parquet('az://lake/archive/orders_2023.parquet')
;
 count
-------
   364

(1 row)

The cold rows are gone from Postgres:

postgres=> SELECT count(*), min(order_date) FROM orders
;
 count |    min
-------+------------
   636 | 2024-01-01
(1 row)

The audit row is written in my custom table:

postgres=> SELECT archived_year, n_rows, blob FROM archive_log ORDER BY at
;
 archived_year | n_rows |                 blob
---------------+--------+---------------------------------------
          2023 |    364 | az://lake/archive/orders_2023.parquet

(1 row)

As we have seen in the previous post, history is still one query away, live rows unioned with the archived file, because pg_duckdb can run queries on heterogenous sources:

postgres=> SELECT yr, count(*) AS n
           FROM (
             SELECT extract(year FROM order_date)::int AS yr FROM orders
             UNION ALL
             SELECT extract(year FROM (r['order_date'])::date)::int
             FROM read_parquet('az://lake/archive/orders_2023.parquet') r
           ) u
           GROUP BY yr ORDER BY yr
;
  yr  |  n
------+-----
 2023 | 364
 2024 | 324
 2025 | 312

(3 rows)

2023 comes from the archived Blob file and 2024 and 2025 from the live Postgres table.

Why durable, not just a cron job

Steps 2→3→4 are export, verify, then delete — and because pg_durable checkpoints after each step, a crash between them is safe:

Die after the export but before the delete, and the run resumes at the verify and delete steps on restart. The export is not repeated. The delete is not skipped.
A failed step stops the workflow with its state intact in df.nodes, so you can see exactly where it stopped and why — no guessing, no half-done cleanup.

With a plain pg_cron + PL/pgSQL procedure you'd be rebuilding this yourself: a status table, retry counters, and a way to tell "exported but not yet deleted" apart from "nothing done yet." pg_durable keeps that bookkeeping inside Postgres.

Make it nightly

Wrap the same workflow in an eternal loop (@>) that waits on a cron schedule, and the archive runs on its own, forever, surviving restarts. Point the date window at a rolling boundary instead of a fixed year:

SELECT df.start(  
  @> (  
    $Step0$  
       SELECT (date_trunc('year', now()) - interval '1 year')::date AS cutoff  
    $Step0$ |=> 'cutoff'  
    ~> $Step1$  
         SELECT count(*) AS n FROM orders WHERE order_date < $cutoff  
       $Step1$ |=> 'n'   
    -- copy / verify 
    ~> $View$  
         CREATE OR REPLACE VIEW orders_all AS  
           SELECT order_id, order_date, amount FROM orders  
           WHERE order_date >= $cutoff          -- exclude rows about to be deleted  
           UNION ALL  
           SELECT order_id, order_date, amount  
           FROM read_parquet('az://lake/archive/orders_*.parquet')  
   $View$  
    -- the same ( check ?> delete !> fail ) gate, then log — all referencing $cutoff
    ~> df.wait_for_schedule('0 2 * * *')  
  ),  
  'nightly-archive'  
);

I added a step to (re)build a view that reads from the archives and adds the operational data. I'll explain how to query a partitioned data lake in a future post.

Takeaways

pg_durable is another open-source extension on Azure Database for PostgreSQL, enabled like pg_duckdb (azure.extensions + shared_preload_libraries + restart), usable in your database after CREATE EXTENSION pg_durable, and by your user after df.grant_usage(current_user).
The archive becomes one df.start() graph: count → write to Blob (duckdb.raw_query) → verify → delete → log, checkpointed between steps.
Durability closes the export-then-delete gap: a crash resumes at the next uncompleted step instead of re-exporting or deleting unsafely.
@> + df.wait_for_schedule('0 2 * * *') turns it into a nightly job that survives restarts — no external scheduler, no status table.
Everything is plain SQL with additional operators to define the workflow.
Each step is a transaction, so intermediate results are durable — the workflow continues automatically after a failover.

You can visualize the workflow from VS Code with the PostgreSQL extension by Microsoft

Archive cold Postgres data to Azure Blob and keep querying it with pg_duckdb

Franck Pachot — Sun, 11 Oct 2026 17:16:16 +0000

A Postgres table that only grows is a familiar problem: queries touch recent data, but old rows still consume storage and slow maintenance. With pg_duckdb, you can move cold data to Parquet in Azure Blob Storage and still query it — on its own and joined back to the live table — from the same connection. Here's the full cycle for a sales table: 10M rows over five years, with 2021 archived out.

Setup (same table as the previous post)

I'll use the same data as in the previous post DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL:

SET duckdb.force_execution = false;

DROP TABLE IF EXISTS sales;
CREATE TABLE sales AS
SELECT g AS sale_id,
       (DATE '2021-01-01' + ((g * 7) % 1826))                       AS sale_date,
       1 + (g % 50000)  AS cust_id, 1 + (g % 2000) AS prod_id, 1 + (g % 5) AS channel_id,
       (ARRAY['US','CH','FR','DE','UK','JP','BR','IN'])[1 + (g % 8)] AS country,
       (ARRAY['Consumer','SMB','Enterprise'])[1 + (g % 3)]          AS segment,
       1 + (g % 10) AS qty, ((5 + (g % 500))::numeric(10,2))        AS amount
FROM generate_series(1, 10000000) g;
ANALYZE sales;

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
SELECT duckdb.create_azure_secret('<blob connection string>');

I set force_execution = false, the default, in case you activated it when reproducing the previous post. The table needs to be built on the PostgreSQL executor. If DuckDB is enforced, it issues a warning: Binder Error: +(DATE, BIGINT) on this generate_series DDL and defaults back to Postgres.

Writing to Blob from inside Azure Database for PostgreSQL

One point up front, because it shapes the workflow. pg_duckdb can write Parquet to Azure Blob from inside the database — but on Azure Database for PostgreSQL the plain COPY … TO 'az://…' syntax is refused:

COPY (SELECT …) TO 'az://lake/archive/sales_2021.parquet' (FORMAT parquet);

--  ERROR: relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

On self-managed PostgreSQL, the pg_duckdb extension intercepts COPY ... TO 'az://...' and runs it in DuckDB for you. However, Azure ensures that extensions cannot bypass PostgreSQL security checks. It considers any path without a leading / as "relative", causing the az:// URL to be rejected. This occurs before the server requires superuser privileges or pg_write_server_files permissions for server-side file operations. Azure allows PostgreSQL extensions but limits their ability to bypass the managed environment's security controls.

The way through is duckdb.raw_query, which runs the COPY inside the embedded DuckDB engine. This isn't a way around the platform's guard — it's a write to a different resource, governed by a different control: the Blob write is governed instead by DuckDB's own secret and allow-list (duckdb.allowed_directories, here az://), so the write lands through the layer that actually owns that resource. That's the in-database export used below. If you'd rather not write from the database at all, a client-side export works too — see the alternative at the end of Step 2.

Step 1 — Identify the cold slice

I'll consider sales prior to 2022 as old data to be archived:

postgres=> SELECT count(*) 
           FROM sales
;
  count
----------
 10000000

postgres=> SELECT count(*) 
           FROM sales 
           WHERE sale_date < DATE '2022-01-01'
;
  count
---------
 1998938

We will retain just under 2 million rows from 2021 in the operational database and archive the older data.

Step 2 — Export to Parquet in Blob (in-database)

Hand the COPY to the DuckDB engine with duckdb.raw_query(), writing straight to Blob. Keep types faithful — the source amount is already numeric(10,2), so DuckDB writes it as DECIMAL(10,2) and reads it back natively:

postgres=> SELECT duckdb.raw_query($$
             COPY (
               SELECT sale_id, sale_date, cust_id, prod_id, channel_id,
                      country, segment, qty, amount
               FROM pgduckdb.public.sales
               WHERE sale_date < DATE '2022-01-01'
             ) TO 'az://lake/archive/sales_2021.parquet' (FORMAT parquet)
           $$)
;
 raw_query
-----------

(1 row)

Four things to know:

Qualify the table as pgduckdb.public.sales, not sales because raw_query runs inside the DuckDB engine, so it sees DuckDB's catalog, not PostgreSQL's search path. pg_duckdb mounts your Postgres tables under the pgduckdb.public.* catalog. A bare sales fails with Catalog Error: Table with name sales does not exist.
duckdb.query() won't carry this — it only accepts a single SELECT, so the COPY must go through raw_query().
The write target must be under the az:// allow-list (a local path fails with LocalFileSystem has been disabled by configuration).
If the target blob already exists and was created by something other than DuckDB (an az storage blob upload, say), the write can fail with IO Error: AzureStorageFileSystem … 'InvalidBlobType'. DuckDB happily overwrites files it wrote itself, but not a blob another tool created with an incompatible type. Write to a fresh path, or delete the stale blob first.

The result is a sales_2021.parquet of about 17 MB for these ~2M rows, already in Blob — no upload step.

Alternative — client-side export

If you can't or don't want to write from the database, pull the rows out and write the file client-side, then upload it.

The DuckDB CLI can read Postgres directly:

-- in the duckdb CLI
ATTACH 'host=… dbname=… user=… password=… sslmode=require' AS pg (TYPE postgres);
COPY (
  SELECT sale_id, sale_date, cust_id, prod_id, channel_id,
         country, segment, qty, amount
  FROM pg.sales WHERE sale_date < DATE '2022-01-01'
) TO 'sales_2021.parquet' (FORMAT parquet);

The resulting file can be uploaded:

az storage blob upload \
  --account-name <account> --account-key <key> \
  -c lake -f sales_2021.parquet -n archive/sales_2021.parquet --overwrite

This method is a possible alternative, but using PostgreSQL with a DuckDB COPY command offers the best security because it keeps the data within the cloud services protected by their native authentication methods.

Step 3 — Read it back through pg_duckdb

You can read the files from PostgreSQL to verify their content:

postgres=> SELECT count(*) 
           FROM read_parquet('az://lake/archive/sales_2021.parquet')
;
  count
---------
 1998938

(1 row)

The 2021 data is queryable straight from Blob. Note that the count(*) didn't read all rows, as the row count is available in the Parquet metadata footer and DuckDB gets the count from it.

Step 4 — Delete the cold rows from Postgres

Now that it's safely in Blob and verified, reclaim the space for new inserts:

postgres=> DELETE FROM sales 
           WHERE sale_date < DATE '2022-01-01'
;

DELETE 1998938

postgres=> SELECT count(*) FROM sales
;
  count
---------
 8001062

(1 row)

If lifecycle management wasn't in place from the start and you've deleted a large amount of data, you may need to reorg the table to actually shrink it on disk (pg_repack and pg_squeeze are available on Azure Database for PostgreSQL). Plain DELETE only marks rows as dead — it doesn't free disk space. A subsequent VACUUM reclaims that space, but it doesn't return it to the operating system: the freed heap space becomes available for any new row inserted into that table (tracked via the Free Space Map). In B-Tree indexes, reclaimed space is only reusable by new index entries whose key value falls into the same page/block — because B-Tree pages are key-ordered, not by arbitrary new values.

Step 5 — Keep querying history, concatenated with live data

The 2021 rows are gone from Postgres, but they are accessible from Parquet, and one query can span both. Here is an aggregate of the revenue per year, with hot rows from Postgres and old ones from Blob:

postgres=> SELECT yr, count(*) AS n, sum(amount) AS revenue
           FROM (
             -- Operational data in PostgreSQL
             SELECT extract(year FROM sale_date)::int AS yr, amount
             FROM sales
             WHERE sale_date >= DATE '2022-01-01'
             -- Heterogeneous Federation 
             UNION ALL
             -- Archived data in Blob
             SELECT extract(year FROM (r['sale_date'])::date)::int,
                    (r['amount'])::numeric(10,2)
             FROM read_parquet('az://lake/archive/sales_2021.parquet') r   
           ) u
           GROUP BY yr ORDER BY yr
;

  yr  |    n    |   revenue
------+---------+--------------
 2021 | 1998938 | 508738403.00
 2022 | 1998897 | 508731504.00
 2023 | 1998897 | 508723449.00
 2024 | 2004372 | 510107354.00
 2025 | 1998896 | 508699290.00
(5 rows)

The execution plan shows one DuckDBScan with a UNION over the Postgres scan and the Blob read:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT yr, count(*) AS n, sum(amount) AS revenue
           FROM (
             SELECT extract(year FROM sale_date)::int AS yr, amount
             FROM sales
             WHERE sale_date >= DATE '2022-01-01'
             UNION ALL
             SELECT extract(year FROM (r['sale_date'])::date)::int,
                    (r['amount'])::numeric(10,2)
             FROM read_parquet('az://lake/archive/sales_2021.parquet') r
           ) u
           GROUP BY yr ORDER BY yr
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.002..0.003 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 6.45s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │          ORDER_BY         │
 │    ────────────────────   │
 │          u.yr ASC         │
 │                           │
 │           5 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │           5 rows          │
 │          (0.21s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │             yr            │
 │           amount          │
 │                           │
 │      10,000,000 rows      │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │           UNION           │
 │    ────────────────────   │
 │           0 rows          ├──────────────┐
 │          (0.00s)          │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         PROJECTION        ││         PROJECTION        │
 │    ────────────────────   ││    ────────────────────   │
 │             yr            ││          extract          │
 │           amount          ││             r             │
 │                           ││                           │
 │       8,001,062 rows      ││       1,998,938 rows      │
 │          (0.03s)          ││          (0.01s)          │
 └─────────────┬─────────────┘└─────────────┬─────────────┘
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         TABLE_SCAN        ││         TABLE_SCAN        │
 │    ────────────────────   ││    ────────────────────   │
 │        Table: sales       ││         Function:         │
 │                           ││        READ_PARQUET       │
 │        Projections:       ││                           │
 │         sale_date         ││        Projections:       │
 │           amount          ││         sale_date         │
 │          Filters:         ││           amount          │
 │  sale_date>='2022-01-01': ││    Total Files Read: 1    │
 │           :DATE           ││                           │
 │                           ││                           │
 │       8,001,062 rows      ││       1,998,938 rows      │
 │          (4.68s)          ││          (6.60s)          │
 └───────────────────────────┘└───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 446.973 ms
 Execution Time: 224.008 ms

The left leaf reads the live table (with the sale_date >= 2022 filter pushed into the scan). The right leaf reads the archive from Blob. DuckDB unions and aggregates them. Note you don't need SET duckdb.force_execution = true — a query that references read_parquet goes to DuckDB automatically.

Making it a routine

If you partition the table by time (pg_partman is available on Azure Database for PostgreSQL), archiving a month becomes: export that partition, upload, DROP the partition. It is far cheaper than a big DELETE + VACUUM.

Lay the archive out by period (archive/year=2021/…) so you can read one period or glob a range — I will cover that in a future post.

Keep the export faithful — the source amount is numeric(10,2), so DuckDB writes and reads it as DECIMAL(10,2) natively (a bare numeric falls back to the Postgres executor).

I will show a fully automated information lifecycle management with pg_durable in a future blog post.

Takeaways

pg_duckdb on Azure Database for PostgreSQL writes Parquet to Blob via duckdb.raw_query($$ COPY (…) TO 'az://…' $$)
read_parquet('az://…') makes archived data queryable again. UNION/join it with the live table in one statement.
You reclaim Postgres storage without losing query access to the history.

In the next post, we will see how to join a live Postgres table with reference data in Blob.

DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

Franck Pachot — Fri, 09 Oct 2026 19:07:22 +0000

In the previous post, we discussed how pg_duckdb on Azure offers more than just reading Parquet files. It integrates DuckDB's columnar, vectorized engine directly into PostgreSQL. A practical first step requires no data lake: simply point DuckDB at your existing tables and run analytical queries more quickly — at times. This post guides you through enabling this on Azure Database for PostgreSQL (Flexible Server), highlights its advantages, and notes when it might not be as effective. It's a choice driven by your knowledge of the types of analytics you run, not a query planner decision.

Enable pg_duckdb on Azure Database for PostgreSQL

pg_duckdb loads through shared_preload_libraries, so it needs two server parameters:

Add pg_duckdb to azure.extensions (the allow-list).
Add pg_duckdb to shared_preload_libraries (append to the current value) and restart the server.

Then, as your normal login:

CREATE EXTENSION pg_duckdb
;

This is the open-source DuckDB extension for PostgreSQL and you have access to all functions.

A table to analyze

I created a table with ten million sales rows spanning over five years. Note that amount is numeric(10,2) with precision, and that detail matters. I come back to it at the end:

postgres=> DROP TABLE IF EXISTS sales
;
NOTICE:  table "sales" does not exist, skipping
DROP TABLE
postgres=> SHOW duckdb.force_execution
;
 duckdb.force_execution
------------------------
 off
(1 row)
postgres=> CREATE TABLE sales AS
           SELECT g AS sale_id,
                  (DATE '2021-01-01' + ((g * 7) % 1826))                        AS sale_date,
                  1 + (g % 50000)                                               AS cust_id,
                  1 + (g % 2000)                                                AS prod_id,
                  1 + (g % 5)                                                  A S channel_id,
                  (ARRAY['US','CH','FR','DE','UK','JP','BR','IN'])[1 + (g % 8)] AS country,
                  (ARRAY['Consumer','SMB','Enterprise'])[1 + (g % 3)]           AS segment,
                  1 + (g % 10)                                                  AS qty,
                  ((5 + (g % 500))::numeric(10,2))                              AS amount
           FROM generate_series(1, 10000000) g
;

SELECT 10000000

postgres=> VACUUM ANALYZE sales
;

VACUUM

Turning DuckDB on

For a query that touches only regular PostgreSQL tables, pg_duckdb does nothing until you ask. The switch is a session setting:

postgres=> SET duckdb.force_execution = true;
SET

With it on, pg_duckdb hands the query to DuckDB when it can, and you see a single Custom Scan (DuckDBScan) node in EXPLAIN wrapping DuckDB's own plan.

Where it wins: a CUBE over 10M rows

A CUBE across three dimensions computes every combination of channel × country × segment — 2³ grouping sets in one pass:

postgres => SELECT channel_id, country, segment
             , count(*) AS n, sum(amount) AS rev
            FROM sales
            GROUP BY CUBE (channel_id, country, segment)
;

Native PostgreSQL (SET duckdb.force_execution = false) takes about 22 seconds:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT channel_id, country, segment
            , count(*) AS n, sum(amount) AS rev
           FROM sales
           GROUP BY CUBE (channel_id, country, segment)
;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 MixedAggregate (actual time=22501.584..22501.662 rows=216.00 loops=1)
   Hash Key: channel_id, country, segment
   Hash Key: channel_id, country
   Hash Key: channel_id
   Hash Key: country, segment
   Hash Key: country
   Hash Key: segment, channel_id
   Hash Key: segment
   Group Key: ()
   Batches: 1  Memory Usage: 264kB
   Buffers: shared hit=2824 read=87288
   ->  Seq Scan on sales (actual time=0.782..1175.074 rows=10000000.00 loops=1)
         Buffers: shared hit=2824 read=87288
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.247 ms
 Execution Time: 22501.821 ms
(17 rows)

DuckDB (SET duckdb.force_execution = true) is two times faster (look at Total Time, not Execution Time which doesn't include the time in DuckDB):

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT channel_id, country, segment
            , count(*) AS n, sum(amount) AS rev
           FROM sales
           GROUP BY CUBE (channel_id, country, segment)
;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.001 rows=0.00 loops=1)
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT channel_id, country, segment, count(*) AS n, sum(amount) AS rev FROM pgduckdb.public.sales GROUP BY CUBE(channel_id, country, segment)
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 12.46s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │          Groups:          │
 │             #0            │
 │             #1            │
 │             #2            │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#3)          │
 │                           │
 │          216 rows         │
 │          (3.95s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │         channel_id        │
 │          country          │
 │          segment          │
 │           amount          │
 │                           │
 │      10,000,000 rows      │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │                           │
 │      10,000,000 rows      │
 │          (20.87s)         │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.356 ms
 Execution Time: 1.257 ms
(63 rows)

The TABLE_SCAN's 20.87s is aggregated CPU/thread time, not wall-clock time, while Total Time: 12.46s is elapsed wall time. pg_duckdb scans the Postgres heap with parallelism at two layers — Postgres parallel workers feed tuples, and multiple DuckDB threads consume and convert them — so the per-operator timing adds up across threads and can exceed the total elapsed time. Additionally, execution is pipelined: the HASH_GROUP_BY consumes scan output concurrently rather than after it finishes.

Postgres's MixedAggregate processes all 10M rows one-at-a-time through eight grouping sets on a single backend. DuckDB does a single vectorized HASH_GROUP_BY over columnar chunks fed by a parallel scan.

Is the win just the parallel scan, though? It's a fair question — the native CUBE runs single-threaded (grouping sets disable parallel aggregation in PostgreSQL, so there's no Gather in that plan). I checked by forcing the native scan parallel (max_parallel_workers_per_gather = 4): a Gather + Parallel Seq Scan appears, the scan finishes in ~1.9s instead of ~1.3s — and the query still takes 22.8s, no improvement. The reason is that the scan was never the bottleneck: a bare count(*) over this table is ~1.5s whether serial or parallel, so the scan is barely a second of the 22s. The other ~21s is the MixedAggregate maintaining eight grouping sets row by row. That is exactly what DuckDB replaces with one vectorized pass. So the win here is the vectorized aggregation, not parallelism on the scan — parallelizing the scan changes nothing because there was almost nothing to parallelize.

Where it doesn't win: a plain aggregate

Not every query benefits from offloading to DuckDB. A full-table sum is an all-scan with no additional work on top:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*), sum(amount), avg(amount)
           FROM sales
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=11499.774..11507.049 rows=1.00 loops=1)
   Buffers: shared hit=4904 read=85208
   ->  Gather (actual time=11499.441..11507.032 rows=2.00 loops=1)
         Workers Planned: 2
         Workers Launched: 1
         Buffers: shared hit=4904 read=85208
         ->  Partial Aggregate (actual time=11488.395..11488.397 rows=1.00 loops=2)
               Buffers: shared hit=4904 read=85208
               ->  Parallel Seq Scan on sales (actual time=0.764..10322.313 rows=5000000.00 loops=2)
                     Buffers: shared hit=4904 read=85208
 Planning Time: 0.086 ms
 Execution Time: 11507.146 ms
(12 rows)

Native PostgreSQL does this in 11 seconds with a parallel scan. DuckDB doesn't improve that:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*), sum(amount), avg(amount)
           FROM sales
;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT count(*) AS count, sum(amount) AS sum, avg(amount) AS avg FROM pgduckdb.public.sales
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 11.81s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#0)          │
 │          avg(#1)          │
 │                           │
 │           1 row           │
 │          (0.08s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           amount          │
 │           amount          │
 │                           │
 │      10,000,000 rows      │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │    Projections: amount    │
 │                           │
 │      10,000,000 rows      │
 │          (23.45s)         │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.093 ms
 Execution Time: 1.018 ms
(58 rows)

The query is the scan, so DuckDB only adds the cost of pulling rows across the boundary and vectorizing them — including decoding that numeric(10,2) column — while PostgreSQL aggregates its own heap in place with parallel workers. When there's nothing above the scan to vectorize, there's nothing to win.

So the rule of thumb isn't "turn DuckDB on for analytics". It's "use it where there's work above the scan". Cubes, rollups, grouping sets, and window functions are good candidates. Full-table sums, plain scans, and point lookups probably aren't, since PostgreSQL already handles those well in place.

Use `pg_hint_plan` to force at the query scope

Note that duckdb.force_execution is a session setting, not a per-query hint — once enabled, pg_duckdb routes everything it can through DuckDB. In practice, that means running analytical workloads on a dedicated connection or role with it enabled, rather than toggling it statement by statement. You can also use SET LOCAL to limit the scope to a transaction, but that requires multiple calls.

Alternatively, use pg_hint_plan, which is also available on Azure Database for PostgreSQL:

postgres=> \dconfig pg_hint_plan.enable_hint

 List of configuration parameters
        Parameter         | Value
--------------------------+-------
 pg_hint_plan.enable_hint | on

postgres=> \dconfig duckdb.force_execution

List of configuration parameters
       Parameter        | Value
------------------------+-------
 duckdb.force_execution | off

postgres=> -- the default: do not involve DuckDB for local tables

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT cust_id FROM sales GROUP BY cust_id HAVING COUNT(DISTINCT prod_id) > 1
;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 GroupAggregate (actual time=7863.960..7863.963 rows=0.00 loops=1)
   Group Key: cust_id
   Filter: (count(DISTINCT prod_id) > 1)
   Rows Removed by Filter: 50000
   Buffers: shared hit=4680 read=85411, temp read=44038 written=44159
   ->  Sort (actual time=5251.976..6707.319 rows=10000000.00 loops=1)
         Sort Key: cust_id, prod_id
         Sort Method: external merge  Disk: 176152kB
         Buffers: shared hit=4680 read=85411, temp read=44038 written=44159
         ->  Seq Scan on sales (actual time=0.291..1226.341 rows=10000000.00 loops=1)
               Buffers: shared hit=4680 read=85411
 Planning Time: 0.089 ms
 Execution Time: 9346.363 ms
(13 rows)

postgres=> -- add a hint to force for this query only

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           /*+ SET( duckdb.force_execution on ) */
           SELECT cust_id FROM sales GROUP BY cust_id HAVING COUNT(DISTINCT prod_id) > 1
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT cust_id FROM pgduckdb.public.sales GROUP BY cust_id HAVING (count(DISTINCT prod_id) > 1)
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 2.08s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │             #0            │
 │                           │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │           FILTER          │
 │    ────────────────────   │
 │ (count(DISTINCT prod_id) >│
 │             1)            │
 │                           │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │     count(DISTINCT #1)    │
 │                           │
 │        50,000 rows        │
 │          (0.25s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │          cust_id          │
 │          prod_id          │
 │                           │
 │      10,000,000 rows      │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │                           │
 │        Projections:       │
 │          cust_id          │
 │          prod_id          │
 │                           │
 │      10,000,000 rows      │
 │          (1.82s)          │
 └───────────────────────────┘


 Planning:
   Buffers: shared hit=1
 Planning Time: 0.966 ms
 Execution Time: 1.973 ms
(78 rows)

This example demonstrates DuckDB finishing the task in 2 seconds, whereas PostgreSQL requires 9 seconds to list customers who ordered more than one product. There is no risk in leaving the parameter as it was set within the query scope with a hint.

The precision detail

I declared amount as numeric(10,2) on purpose. A bare numeric (no precision) is something DuckDB can't represent, so pg_duckdb falls back to PostgreSQL — force_execution = true and all, your query just runs on the normal executor. The only tell is a WARNING at plan time (the prepared query fails inside CreatePlan, Postgres emits a warning, then falls back), which is easy to miss, plus the absence of Custom Scan (DuckDBScan) in EXPLAIN. If the top node isn't a DuckDBScan, DuckDB declined.

If you want DuckDB to run it anyway, SET duckdb.convert_unsupported_numeric_to_double = true casts the unsupported numeric to DOUBLE instead of falling back — fine for aggregates where a little floating-point imprecision is acceptable, not when you need exact decimal arithmetic.

Takeaways

Enable PG_DUCKDB on Azure Database for PostgreSQL (Flexible Server) with azure.extensions + shared_preload_libraries + restart, then CREATE EXTENSION pg_duckdb. Add PG_HINT_PLAN if you want to force per query.
SET duckdb.force_execution = true runs analytics over your existing tables — no Parquet, no data movement, but a faster engine.
It wins on heavy grouping, not on plain scans. Read the plan: DuckDBScan means it ran in DuckDB.
Declare numeric(p,s) precision on columns you aggregate, or DuckDB falls back (or set convert_unsupported_numeric_to_double).
Control the scope of duckdb.force_execution at session, transaction, or query level.

In a future post, I'll demonstrate how pg_duckdb can query Parquet files stored in Azure Blob Storage to archive cold data from Postgres and enable heterogeneous queries. We might even use pg_durable to automate lifecycle management. While it can read Parquet files, the true strength of pg_duckdb is executing analytics across diverse data sources.

Full pg_duckdb on Azure Database for PostgreSQL

Franck Pachot — Thu, 08 Oct 2026 11:26:47 +0000

In my previous post, Amazon Aurora's analytics is gated DuckDB: comparison with pg_duckdb, I examined Aurora Analytics in detail alongside pg_duckdb. I noted that the pg_duckdb extension, installed on a PostgreSQL container, offers more features. For this article, I configured it on Azure Database for PostgreSQL (Flexible Server), a managed service running the community PostgreSQL with popular open-source extensions.

Enabling pg_duckdb on Azure Database for PostgreSQL

I've run this demonstration on Azure Database for PostgreSQL flexible server newly provisioned in Canada Central region, with 2 vCores, 4 GiB RAM, 32 GiB storage, and PostgreSQL 18.6:

The pg_duckdb extension is a shared_preload_libraries extension, so two settings are needed. First, allow-list it, then add it to the preload libraries and restart.

The cover image of this article shows the manual way in the portal's Server parameters, with two clicks on the checkboxes, or you can do the same via the CLI:

# allow-list the extension
az postgres flexible-server parameter set \
  --resource-group <rg> --server-name <server> \
  --name azure.extensions --value pg_duckdb

# load the library (append to whatever is already there), then restart
az postgres flexible-server parameter set \
  --resource-group <rg> --server-name <server> \
  --name shared_preload_libraries --value "<existing>,pg_duckdb"

az postgres flexible-server restart \
  --resource-group <rg> --name <server>

If you only do the first step, CREATE EXTENSION fails with a clear hint:

ERROR: pg_duckdb needs to be loaded via shared_preload_libraries
HINT:  Add pg_duckdb to shared_preload_libraries.

After the restart, with a normal login, you CREATE EXTENSION in the database where you want to run DuckDB functions:

postgres=> CREATE EXTENSION pg_duckdb
;
CREATE EXTENSION
postgres=> \dx *duckdb*
                         List of installed extensions
   Name    | Version | Default version | Schema |         Description
-----------+---------+-----------------+--------+-----------------------------
 pg_duckdb | 1.1.0   | 1.1.0           | public | DuckDB Embedded in Postgres
(1 row)

postgres=> SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_duckdb';
  extname  | extversion
-----------+------------
 pg_duckdb | 1.1.0

postgres=> SELECT * FROM duckdb.query('SELECT version();');

 "version"()
-------------
 v1.4.3

That's how you load an extension in PostgreSQL — since this is PostgreSQL itself, not a re-implementation. You load pg_duckdb using the same shared_preload_libraries and CREATE EXTENSION commands you'd use anywhere else, and everything afterward works exactly like the standard community server.

Reading Parquet from Azure Blob

The pg_duckdb extension reads object storage through a DuckDB secret. For Blob, a connection string is the simplest form:

SELECT duckdb.create_azure_secret(
  'DefaultEndpointsProtocol=https;AccountName=…;AccountKey=…;EndpointSuffix=core.windows.net'
)
;
 create_azure_secret
---------------------
 azure_secret_42
(1 row)

I uploaded the same title.parquet used in the previous post (500,000 rows: id, title, kind_id, production_year) to a container named lake:

Then a Blob path is just a table source for DuckDB:

postgres=> SELECT count(*) 
           FROM read_parquet('az://lake/job/title.parquet')
;
 count
--------
 500000
(1 row)

postgres=> SELECT r['id'], r['title'], r['production_year']
           FROM read_parquet('az://lake/job/title.parquet') r
           LIMIT 5
;
 id |  title  | production_year
----+---------+-----------------
  1 | Title 1 |            1921
  2 | Title 2 |            1922
  3 | Title 3 |            1923
  4 | Title 4 |            1924
  5 | Title 5 |            1925
(5 rows)

The execution plan is exposed by pg_duckdb:

postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT r['id'], r['title'], r['production_year']
           FROM read_parquet('az://lake/job/title.parquet') r
           LIMIT 5
;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   Output: id, title, production_year
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT r.id, r.title, r.production_year FROM system.main.read_parquet('az://lake/job/title.parquet'::text) r LIMIT 5
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.374s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      STREAMING_LIMIT      │
 │    ────────────────────   │
 │           5 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │        Projections:       │
 │             id            │
 │           title           │
 │      production_year      │
 │                           │
 │         4,096 rows        │
 │          (0.00s)          │
 └───────────────────────────┘
 Query Identifier: 7627283629006825662
 Planning:
   Buffers: shared hit=1
 Planning Time: 254.221 ms
 Execution Time: 249.641 ms
(51 rows)

This is the DuckDB representation of EXPLAIN ANALYZE.

The same query, the same plan

Here is the query from the last post, run against the Blob file through pg_duckdb on Azure Database for PostgreSQL (Flexible Server):

postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT r['production_year'] AS production_year, count(*) AS count
           FROM read_parquet('az://lake/job/title.parquet') r
           WHERE r['kind_id'] = 1
           GROUP BY r['production_year']
           ORDER BY count DESC
;
                                                                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.002..0.003 rows=0.00 loops=1)
   Output: production_year, count
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT r.production_year AS production_year, count(*) AS count FROM system.main.read_parquet('az://lake/job/title.parquet'::text) r WHERE (r.kind_id = 1) GROUP BY r.production_year ORDER BY (count(*)) DESC
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 1.14s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_integ│
 │   ral_integer(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │          ORDER_BY         │
 │    ────────────────────   │
 │     count_star() DESC     │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_integra│
 │    l_utinyint(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_integ│
 │   ral_integer(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │   PERFECT_HASH_GROUP_BY   │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │      production_year      │
 │                           │
 │        100,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_integra│
 │    l_utinyint(#0, 1920)   │
 │                           │
 │        100,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │        Projections:       │
 │      production_year      │
 │                           │
 │     Filters: kind_id=1    │
 │    Total Files Read: 1    │
 │                           │
 │        100,000 rows       │
 │          (1.28s)          │
 └───────────────────────────┘
 Query Identifier: 4315756270624112827
 Planning:
   Buffers: shared hit=1
 Planning Time: 678.580 ms
 Execution Time: 252.476 ms
(112 rows)

That's how the DuckDB extension displays its execution plan within the PostgreSQL query plan: pg_duckdb draws its box tree. DuckDB is a different engine, gathering execution statistics differently and pg_duckdb displays it as-is so you can understand them better. For example, each box shows that operator's thread-summed CPU time, per-node and not cumulative — unlike PostgreSQL's EXPLAIN ANALYZE, where a parent node's time includes its children.

With a different display, this is the identical plan operations produced though aurora_analytics for the same file in the previous post — down to __internal_compress_integral_utinyint(#0, 1920), DuckDB's frame-of-reference integer compression with the base constant 1920 (the minimum production_year in the data).

Custom Scan (DuckDBScan)
  DuckDB Execution Plan:
  PROJECTION   __internal_decompress_integral_integer(#0, 1920), #1
  ORDER_BY     count_star() DESC
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920), #1
  PERFECT_HASH_GROUP_BY   Groups: #0   Aggregates: count_star()
  PROJECTION   production_year
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920)
  READ_PARQUET
     Function: READ_PARQUET
     Projections: production_year
     Filters: kind_id=1
     ~100,000 rows

It fully matches the open-source pg_duckdb running in a local Docker container. Three independent deployments — a laptop, Aurora, and Azure Database for PostgreSQL — emit the same DuckDB plan for the same query. It is the same engine in all three. But there's more in the pg_duckdb extension.

Where the extension approach actually differs

Same engine, so the interesting part is the packaging. Here Azure's choice — ship the extension itself — leads to concrete, observable differences from Aurora's closed FDW.

1. The engine is directly usable

On Aurora, DuckDB is sealed behind the FDW: read_parquet() as a function, list/struct literals, QUALIFY, duckdb.query() — all are rejected, because only a foreign-table scan is handed to DuckDB. On Azure Database for PostgreSQL, the whole extension surface is present. \dx+ pg_duckdb lists hundreds of objects (235 functions, 23 types, an FDW, casts and operators), and you can call DuckDB directly:

postgres=> SELECT * 
           FROM duckdb.query(
            'SELECT 1 AS a, [1,2,3] AS lst
');
 a |   lst
---+---------
 1 | {1,2,3}
(1 row)

That is a DuckDB list literal evaluated by DuckDB, returned to PostgreSQL as an integer[] array datatype. On Aurora, it's a parser error. If you want DuckDB's SQL dialect — not just its scan — the extension gives it to you.

2. It accelerates your existing PostgreSQL tables

This is the sharpest functional difference. Aurora's aurora_analytics only engages DuckDB for foreign (S3) tables. A query on a regular Aurora table runs on the normal PostgreSQL executor. With pg_duckdb you can push a query on an ordinary heap table through DuckDB:

postgres=> CREATE TABLE local_sales AS
           SELECT g AS id, (g % 1000) AS cust, (random()*100)::int AS amt
           FROM generate_series(1, 200000) g
;
SELECT 200000
postgres=> SET duckdb.force_execution = true
;
postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT cust, count(*), sum(amt)
           FROM local_sales GROUP BY cust
           ORDER BY 2 DESC LIMIT 5
;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.001 rows=0.00 loops=1)
   Output: cust, count, sum
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT cust, count(*) AS count, sum(amt) AS sum FROM pgduckdb.public.local_sales GROUP BY cust ORDER BY (count(*)) DESC LIMIT 5
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.126s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │           TOP_N           │
 │    ────────────────────   │
 │           Top: 5          │
 │                           │
 │         Order By:         │
 │     count_star() DESC     │
 │                           │
 │           5 rows          │
 │          (0.01s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │         1,000 rows        │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │            cust           │
 │            amt            │
 │                           │
 │        200,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │     Table: local_sales    │
 │                           │
 │        Projections:       │
 │            cust           │
 │            amt            │
 │                           │
 │        200,000 rows       │
 │          (0.18s)          │
 └───────────────────────────┘
 Query Identifier: 6568044750341216650
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.394 ms
 Execution Time: 1.360 ms
(75 rows)

This runs DuckDB scans and aggregation on PostgreSQL tables:

Custom Scan (DuckDBScan)
  TOP_N  Top: 5  Order By: count_star() DESC
  HASH_GROUP_BY  Aggregates: count_star(), sum(#1)
  PROJECTION  cust, amt
  PGDUCKDB_POSTGRES_SCAN  Table: local_sales

DuckDB is now the executor, reading the PostgreSQL heap through PGDUCKDB_POSTGRES_SCAN. Whether that is faster than the native plan depends entirely on the query — it is a win for scans, joins and big aggregations, and a loss for point lookups — but the point here is that the option exists at all, which it does not on Aurora's proprietary extension.

3. It is the same extension you run anywhere

pg_duckdb on Azure Database for PostgreSQL is the same open-source extension (version 1.1.0 here) you can run in Docker on a laptop or on a self-managed server. The plans I showed match my local container exactly. There is no Azure-specific dialect to learn: it is the community extension, managed. That's the freedom PostgreSQL users expect: they run on a managed service because the operations, reliability, and performance provide the best experience, not because they are locked into something different from the open source postgres with extensions.

When pg_duckdb on Azure is different than self-managed?

Being honest, any managed service that packages an extension might also need to impose constraints. The hardening is real, and one capability—write-back to the lake—requires a detour you should be aware of.

Hardening from superuser configuration

Azure Database for PostgreSQL locks pg_duckdb access to local directories. These are all superuser-context settings, and a normal login can't touch them:

postgres=> \dconfig duckdb.*
                             List of configuration parameters
                   Parameter                    |                  Value
------------------------------------------------+-----------
...
 duckdb.allowed_directories                     | az://
...
 duckdb.enable_external_access                  | off
...
(27 rows)

The effect: only az:// is reachable. Local files are blocked outright:

postgres=> SELECT count(*) 
           FROM read_parquet('/tmp/my.parquet')
;
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: Permission Error: File system LocalFileSystem has been disabled by configuration

This also blocks s3:// and arbitrary https:// URLs. That is a deliberate, security-first posture (DuckDB can otherwise fetch arbitrary URLs), and it is the right default for a managed service, but it does mean the extension is not exactly the "wide open DuckDB" you get self-managed.

These are the extension's own settings, not an Azure bolt-on. duckdb.allowed_directories and duckdb.enable_external_access are upstream pg_duckdb GUCs (added March 2026) — allowed_directories allowlists prefixes that stay reachable even when enable_external_access is off. So Azure is configuring the community extension's built-in allow-list, not patching restrictions on top of it. enable_external_access is also applied at initialization and can't be flipped at runtime afterward — DuckDB rejects changes to allowed_directories once external access is disabled — which is why it reads as a fixed, image-level setting.

Write-back works, but not with the `COPY TO` syntax

In the self-managed world, you can write results back to object storage directly with a PostgreSQL command: COPY (…) TO 's3://…' or az://. As a normal azure_pg_admin login, though, that statement is refused:

postgres=> COPY (
            SELECT cust, count(*) 
            FROM local_sales GROUP BY cust
) TO 'az://lake/exports/agg.parquet'
;
ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

In PostgreSQL, a server-side COPY … TO <file> requires superuser privileges or the pg_write_server_files role, neither of which Azure Database for PostgreSQL grants to a normal login. It also rejects non-absolute paths before the privilege check, so the error you see depends on the path shape:

-- any path that doesn't start with "/" is classed as relative, rejected first:

postgres=> COPY (SELECT 1 a) TO 'az://lake/my.parquet';

ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

postgres=> COPY (SELECT 1 a) TO 'relative/my.csv';

ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

-- a genuine absolute path gets past that and hits the privilege check:

postgres=> COPY (SELECT 1 a) TO '/tmp/my.csv';

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

postgres=>

The error message names Azure for a reason: the managed service adds its own security gate ahead of PostgreSQL's normal COPY checks, and that gate, like PostreSQL, rejects any path without a leading / as "relative" — az:// included. So an az:// URL is stopped at that first gate before it ever reaches the privilege check, while a genuine absolute path like /tmp/my.csv clears the gate and then hits PostgreSQL's own superuser requirement. Two separate checks, and a normal login is stopped by one or the other.

But write-back is not actually unavailable on Azure Database for PostgreSQL — because the extension exposes the DuckDB engine directly, with its own security that allows access to az://. You can hand the COPY straight to DuckDB with duckdb.raw_query:

postgres=> SELECT duckdb.raw_query($$
             COPY (
               SELECT cust, count(*) AS n
                FROM pgduckdb.public.local_sales GROUP BY cust
             ) TO 'az://lake/exports/agg.parquet' (FORMAT parquet)
           $$);

postgres=> SELECT count(*) FROM      
           read_parquet('az://lake/exports/agg.parquet')
;
 count
-------
  1000
(1 row)

This doesn't bypass any security — it writes through DuckDB's own storage controls instead of PostgreSQL's server-file gate, which is unaware of additional extension's capabilities and object storage URLs. The two protect different things: PostgreSQL's gate restricts writes to local filesystem, fir superuser, while DuckDB governs object-storage access through its secret and allow-list (duckdb.allowed_directories, which here permits only az://). An object-storage write must go through the layer that actually owns that resource.

The file is really written to Blob and reads straight back. Three caveats:

Qualify Postgres tables as pgduckdb.public.<table>, because raw_query runs inside the DuckDB engine, which sees DuckDB's catalog, not PostgreSQL's search path. A bare FROM local_sales fails with Catalog Error: Table with name local_sales does not exist. pg_duckdb mounts your Postgres tables under pgduckdb.public.*, so that's how you name them here.
duckdb.query() won't do it — it only accepts a single SELECT, so the COPY must go through raw_query.
The storage allow-list still applies: you can only write under az:// (a local /tmp target fails with LocalFileSystem has been disabled by configuration).

So write-back is there and under control. It just isn't the PostgreSQL COPY TO, and you reach your tables through the DuckDB catalog name.

This is where the two cloud systems really diverge. Aurora's foreign tables are strictly read-only, and I verified every write attempt — not just one. Both INSERT … VALUES, bulk INSERT … SELECT, UPDATE, and DELETE commands all fail with the same message: "Aurora Analytics foreign tables are read-only — update the source files in S3 … directly". Pointing a foreign table to a new S3 path and trying to insert also fails, suggesting the wrapper is the issue, not a locked file. Additionally, COPY (…) TO 's3://…' results in "COPY to a file is not supported", and the extension does not provide any write, export, or unload functions. The FDW has no write capabilities—the DuckDB engine is sealed behind it.

Conversely, on Azure, pg_duckdb exposes the DuckDB engine itself. Queries can read from and write to the data lake. The key difference is that the engine is accessible rather than sealed behind a restricted interface. pg_duckdb embeds a DuckDB execution engine alongside PostgreSQL, allowing DuckDB operations on both PostgreSQL tables and external data sources.

Summary

	`pg_duckdb` on Microsoft Azure for PostgreSQL	`aurora_analytics` on Amazon Aurora (AWS)
What it is	the open-source `pg_duckdb` extension	a closed extension embedding DuckDB
Engine	✅ DuckDB (identical plan)	✅ DuckDB (identical plan)
How you reach the lake	`read_parquet('az://…')`, `r['col']`	`CREATE FOREIGN TABLE … OPTIONS(format 'parquet')`
Direct DuckDB SQL (`duckdb.query`, list/struct)	✅ Yes	❌ No — sealed behind the FDW
Accelerate your own PostgreSQL tables	✅ Yes (`force_execution`)	❌ No — foreign tables only
Read object storage	✅ Yes (`az://`, secret)	✅ Yes (`s3://`, IAM role)
Write back to the lake	✅ Yes, via `duckdb.raw_query` to use the extension's security model	❌ No — foreign tables read-only
External access	locked to `az://`, superuser-only GUCs	IAM-scoped to S3/Glue
Portability of what you learn	same extension as laptop/self-managed	Aurora-specific

Both clouds embed the same DuckDB engine in PostgreSQL. Aurora wraps it as a managed, catalog-oriented, read-only foreign-data wrapper. Azure ships the community extension, so you can use DuckDB directly, accelerate your existing tables, and even write back to the lake. Azure limits only the granted permissions, without changing the open-source software's behavior. Where the managed guardrails bite, they bite on both (external access is locked down on each), but the extension approach keeps the engine in your hands rather than sealed behind an FDW.

In the next posts of this series, we will go through some examples of using pg_duckdb in Azure Database for PostgreSQL.

Amazon Aurora's analytics is gated DuckDB: comparison with open-source pg_duckdb

Franck Pachot — Mon, 05 Oct 2026 21:09:00 +0000

Microsoft announced the public preview of the pg_duckdb extension for Azure Database for PostgreSQL Flexible Server at Ignite on November 18, 2025. In August 2026, Amazon announced an agreement to acquire DuckLabs, the company behind DuckDB, but the DuckDB open-source project itself remains independent. On September 30, AWS announced that Amazon Aurora PostgreSQL can use embedded DuckDB to query Apache Iceberg and Parquet data in data lakes alongside operational data. Let's compare.

Amazon Aurora PostgreSQL can now query Parquet and Iceberg in S3 through the aurora_analytics extension, and AWS says DuckDB is embedded. I wanted to prove that rather than take it on faith. The method is simple: get an execution plan out of Aurora, then get one out of a known-DuckDB engine (the open-source pg_duckdb) for the same query over the same file, and compare. Here is every step.

Provision Aurora with aurora_analytics

aurora_analytics needs two things: an Aurora PostgreSQL cluster on a supported version (17.11+ or 18.6+), and an IAM role attached to the cluster with the AuroraAnalytics feature so Aurora can read your S3 bucket. I used a CloudFormation stack The essential pieces are:

an Aurora PostgreSQL Serverless v2 cluster, EngineVersion=17.11;
a cluster parameter group with aurora_analytics.enabled = 1;
an IAM role trusting rds.amazonaws.com, with S3 read on the data bucket (and Glue read if you want Iceberg), attached to the cluster via AssociatedRoles with FeatureName: AuroraAnalytics;
an S3 bucket for the Parquet data, plus an S3 gateway VPC endpoint.

I deployed it with CloudFormation:

aws cloudformation deploy --stack-name aurora-analytics ...

aws cloudformation describe-stacks --stack-name aurora-analytics --query 'Stacks[0].Outputs' --output table

I uploaded the Parquet file and connected to Aurora:


aws s3 cp title.parquet s3://my-bucket/job/title.parquet

psql "host=<cluster-endpoint> port=5432 dbname=analytics user=dbadmin sslmode=require"

Query Parquet in Aurora and capture the plan

I enabled the extension and defined a foreign table over the file. The empty column list makes Aurora infer the schema from the Parquet footer:

CREATE EXTENSION aurora_analytics;

CREATE FOREIGN TABLE title_ft ()
  SERVER aurora_analytics_server
  OPTIONS (location 's3://my-bucket/job/title.parquet', format 'parquet');

SELECT count(*) FROM title_ft;

Here is the query I compared, and its plan:

EXPLAIN (ANALYZE, VERBOSE)
 SELECT production_year, count(*)
 FROM title_ft
 WHERE kind_id = 1
 GROUP BY production_year
 ORDER BY 2 DESC
;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (actual time=65.848..65.919 rows=21 loops=1)
  Output: aurora_analytics.production_year, aurora_analytics.count
  Pushdown SQL: SELECT production_year, count(*) AS count FROM (SELECT kind_id::INTEGER AS kind_id, production_year::INTEGER AS production_year FROM system.main.read_parquet($aurora_analytics_parameter_1) AS title_ft) title_ft WHERE (kind_id = 1::INTEGER) GROUP BY production_year ORDER BY (count(*)) DESC
  Total CPU Time: 0.0 ms
  Effective Parallelism: 0.7
  Total Thread Wait Time: 0.0 ms
  ->  PROJECTION (actual time=51.637..51.637 rows=21 loops=1)
        Projections: __internal_decompress_integral_integer(#0, 1920), #1
        ->  ORDER_BY (actual time=51.614..51.614 rows=21 loops=1)
              Order By: count_star() DESC
              ->  PROJECTION (actual time=46.880..46.880 rows=21 loops=1)
                    Projections: __internal_compress_integral_utinyint(#0, 1920), #1
                    ->  PROJECTION (actual time=46.873..46.873 rows=21 loops=1)
                          Projections: __internal_decompress_integral_integer(#0, 1920), #1
                          ->  PERFECT_HASH_GROUP_BY (actual time=46.844..46.844 rows=21 loops=1)
                                Groups: #0
                                Aggregates: count_star()
                                ->  PROJECTION (actual time=16.582..43.982 rows=100000 loops=1)
                                      Projections: production_year
                                      ->  PROJECTION (actual time=16.571..43.982 rows=100000 loops=1)
                                            Projections: __internal_compress_integral_utinyint(#0, 1920)
                                            ->  READ_PARQUET (actual time=14.609..43.981 rows=100000 loops=1)
                                                  Table: title_ft
                                                  Filters: kind_id=1
                                                  Projections: production_year
                                                  Total Files Read: 1
                                                  Rows Removed by Filter: 400000
Query Identifier: 5750582199016602255
Planning Time: 369.237 ms
Execution Time: 81.265 ms
Analytics Cache Hit Bytes: 93kB
Analytics Remote Read Bytes: 0kB
S3 HEAD Request Count: 0
S3 GET Request Count: 0

This is already suggestive: READ_PARQUET, PERFECT_HASH_GROUP_BY, count_star(), and a Pushdown SQL line that reads FROM system.main.read_parquet(...). Those are DuckDB's names. But let's prove it.

The cover image of this post shows the same with the Microsoft PostgreSQL extension for VSCode on Kiro.

The same query in pg_duckdb on Docker

pg_duckdb is the open-source DuckDB-in-PostgreSQL extension. I run it locally:

docker run -d --name pgduckdb -e POSTGRES_PASSWORD=duckdb -e POSTGRES_DB=lab \
  -p 55432:5432 pgduckdb/pgduckdb:17-v1.1.1

# put the SAME file where the container can read it
docker cp title.parquet pgduckdb:/tmp/title.parquet

docker exec -it pgduckdb psql -U postgres -d lab

I execute the same query with the pg_duckdb functions:

CREATE EXTENSION pg_duckdb;
SET duckdb.force_execution = true;

EXPLAIN
 SELECT r['production_year'] AS production_year, count(*) AS count
 FROM read_parquet('/tmp/title.parquet') r
 WHERE r['kind_id'] = 1
 GROUP BY r['production_year']
 ORDER BY count DESC
;

pg_duckdb draws the plan as a box tree:

One rendering difference worth noting: pg_duckdb prints DuckDB's native plan verbatim, so per-node times are DuckDB's thread-summed CPU (a child can read higher than the total wall time). That's different from Aurora which reformats the DuckDB plan into PostgreSQL's convention, where each node's time is cumulative wall-clock and the top node matches Execution Time.

Here are the operators:

Custom Scan (DuckDBScan)
  DuckDB Execution Plan:

  PROJECTION   __internal_decompress_integral_integer(#0, 1920), #1
  ORDER_BY     count_star() DESC
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920), #1
  PERFECT_HASH_GROUP_BY   Groups: #0   Aggregates: count_star()
  PROJECTION   production_year
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920)
  READ_PARQUET
     Function: READ_PARQUET
     Projections: production_year
     Filters: kind_id=1
     ~100,000 rows

We recognize the same functions, presented differently.

Comparison

I put them next to each other:

	Aurora `aurora_analytics`	pg_duckdb (Docker)
Wrapper node	`Custom Scan` (provider `aurora_analytics`)	`Custom Scan (DuckDBScan)`
Scan	`READ_PARQUET` Filters: kind_id=1, Projections: production_year	`READ_PARQUET` Filters: kind_id=1, Projections: production_year
Group by	`PERFECT_HASH_GROUP_BY` / `count_star()`	`PERFECT_HASH_GROUP_BY` / `count_star()`
Sort	`ORDER_BY count_star() DESC`	`ORDER_BY count_star() DESC`
Compression op	`__internal_compress_integral_utinyint(#0, 1920)`	`__internal_compress_integral_utinyint(#0, 1920)`
Decompression op	`__internal_decompress_integral_integer(#0, 1920)`	`__internal_decompress_integral_integer(#0, 1920)`

They are the same plan. The detail that removes all doubt is the compression operator: both engines, independently, encoded production_year with __internal_compress_integral_utinyint(#0, 1920). That is DuckDB's frame-of-reference integer compression, and 1920 is the minimum production_year in the file — the base value DuckDB subtracts so the column fits in an unsigned byte. A separate implementation would not reproduce DuckDB's internal operator names and independently pick the same base constant from the
data. Aurora’s plan exposes DuckDB operators, indicating that aurora_analytics uses DuckDB or DuckDB-derived components under its Foreign Data Wrapper.

How differently they package it

It's the same engine, but opposite exposure. We can look at what each extension registers.

With pg_duckdb the whole DuckDB surface is SQL-visible:

postgres=# \dx+ pg_duckdb
                                                                                                                                                      Objects in extension "pg_duckdb"
                                                                                                                                                             Object description
--------------------------------------------------------------------
 access method duckdb
 cast from duckdb.unresolved_type to bigint
 cast from duckdb.unresolved_type to bigint[]
 ...

The full output shows 366 objects, including 235 function entries (overloads counted separately), 1 foreign-data wrapper (duckdb), and 23 type entries (including arrays). The remaining 107 objects include casts, operators, triggers, tables, and other objects.

With aurora_analytics the engine is sealed behind an FDW:

postgres=# \dx+ aurora_analytics
                                                                                                                                                      Objects in extension "aurora_analytics"
                                                                                                                                                             Object description
--------------------------------------------------------------------
foreign-data wrapper aurora_analytics_fdw
function aurora_analytics_fdw_handler()
function aurora_analytics_fdw_validator(text[],oid)
server aurora_analytics_server

In Aurora the DuckDB SQL dialect is not reachable — read_parquet() as a function, list/struct literals, QUALIFY, approx_count_distinct, ::HUGEINT are all rejected by PostgreSQL’s SQL layer, because only the foreign-table scan is handed to DuckDB. With pg_duckdb you can call the engine directly (duckdb.query('…')).

So, it's the same DuckDB underneath, but without exposing its functions. in summary:

	pg_duckdb	aurora_analytics
Where it runs	Any PostgreSQL (open source)	Aurora PostgreSQL only (17.11+/18.6+)
Engine	DuckDB	DuckDB, embedded behind a foreign-data wrapper
Lake interface	`read_parquet('…') r` with `r['col']`	`CREATE FOREIGN TABLE … OPTIONS(format 'parquet')`, schema inferred
Direct DuckDB SQL	Yes — `duckdb.query()`, DuckDB dialect, extensions	No — engine sealed; only foreign-table scans
Accelerates your own PostgreSQL tables	Yes — `SET duckdb.force_execution = true`	No — foreign (lake) tables only
Write back to the lake	Yes — `COPY (…) TO 's3://…'` / `az://`	No — read-only
Join live tables with the lake	Yes	Yes
Iceberg / Delta	`iceberg_scan` / `delta_scan` (DuckDB extensions)	Iceberg via Glue catalog; no Delta
Credentials	DuckDB secret (connection string / keys)	IAM role attached to the cluster
Memory / spill knob	`duckdb.max_memory`	`aurora_analytics.query_mem`
Observability	`EXPLAIN`, `duckdb.log_pg_explain`	`EXPLAIN`, `aurora_analytics_stat_statements()`

AWS's acquisition of DuckDB should not affect the open-source DuckDB and pg_duckdb. Here are some quotes from the Founder & CEO of MotherDuck (from his blog post):

Some day, Amazon will likely release their own service based on DuckDB. After all, they’re not acquiring Duck Labs just because they love open source.

That's what they did with aurora_analytics, even though it's a proprietary foreign data wrapper in Amazon Aurora rather than a separate service.

The core open-source engine remains fully open. Tigani notes:

Duck Labs is being kept as a wholly-owned subsidiary, with its organization intact. The Duck Labs team will still be working in open source. The DuckDB Foundation has iron clad control over the DuckDB IP, and DuckDB license is going to stay permissively open.

Because pg_duckdb is governed under the DuckDB Foundation's intellectual property, it is similarly protected and will remain community-driven and open-source.

How to Do Pagination Right: Row Comparison, Datatypes, and Index Range Scans

Franck Pachot — Mon, 05 Oct 2026 13:46:52 +0000

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:

Use a row comparison when the database can turn it into a composite index boundary.
Pass values with exactly the right data types.

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.

Reproducing it on PostgreSQL

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.

The best predicate: one correctly typed row comparison

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.

The exact OR expression is correct, but not as good on PostgreSQL

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.

The datatype can silently remove part of the seek

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.

A test that exposes wasted index work

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.

Which databases support an index for row comparison?

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

YugabyteDB: improved, but the old case still needs the workaround

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: native row comparison, with a prefix caveat

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: no tuple syntax, but two index seeks

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}},
    {created_on: T, _id: {$lt: I}}
  ]
};

db.events.find(after)
  .sort({created_on: -1, _id: -1})
  .limit(50);

Here is a complete small reproduction:

db.events.drop();

db.events.insertMany([
  {_id:ObjectId("000000000000000000000006"), created_on:ISODate("2026-01-01T12:02:00Z"), n:"A"},
  {_id:ObjectId("000000000000000000000005"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"B"},
  {_id:ObjectId("000000000000000000000004"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"C"},
  {_id:ObjectId("000000000000000000000003"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"D"},
  {_id:ObjectId("000000000000000000000002"), created_on:ISODate("2026-01-01T12:00:00Z"), n:"E"},
  {_id:ObjectId("000000000000000000000001"), created_on:ISODate("2026-01-01T11:59:00Z"), n:"F"}
]);

db.events.createIndex(
  {created_on:-1, _id:-1},
  {name:"created_on_-1__id_-1"}
);

const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");

const q = {
  $or: [
    {created_on: {$lt:T}},
    {created_on:T, _id:{$lt:I}}
  ]
};

db.events.find(q, {n:1, created_on:1})
  .sort({created_on:-1, _id:-1})
  .limit(3)
  .hint("created_on_-1__id_-1")
  .explain("executionStats");

MongoDB can plan this as:

LIMIT
└─ FETCH
   └─ SORT_MERGE
      ├─ IXSCAN created_on_-1__id_-1
      └─ IXSCAN created_on_-1__id_-1

SORT_MERGE merges two already ordered index streams. It is not a blocking in-memory SORT. MongoDB 9.0 regression tests require this shape for sortable $or queries.

Explain output varies between the classic and slot-based engines. Inspect the complete result for:

{
  executionStats: {
    nReturned: 3,
    totalKeysExamined: 3,
    totalDocsExamined: 3,
    executionStages: {
      stage: "LIMIT",
      inputStage: {
        stage: "FETCH",
        inputStage: {
          stage: "SORT_MERGE",
          inputStages: [
            {
              stage: "IXSCAN",
              indexName: "created_on_-1__id_-1"
            },
            {
              stage: "IXSCAN",
              indexName: "created_on_-1__id_-1"
            }
          ]
        }
      }
    }
  }
}

This is the plan structure and the decisive counters, not a captured complete JSON plan from a specific server. Run the reproduction on the target MongoDB version because the optimizer can also choose one broader index scan with a residual $or.

An array expression is not a replacement:

{$expr: {$lt: [["$created_on", "$_id"], [T, I]]}}

It compares a computed array and does not provide compound index bounds. Materializing an array field is also different because it becomes a multikey index.

Oracle: no ordered row comparison

Oracle didn't implement the full SQL standard. it supports expression lists for equality and IN, but not:

(created_on, id) < (:created_on, :id)

Use:

CREATE TABLE post (
    id         number NOT NULL,
    created_on timestamp NOT NULL,
    payload    varchar2(100),
    CONSTRAINT post_pk PRIMARY KEY (id)
);

CREATE INDEX post_seek_i ON post (created_on, id);

SELECT id, created_on, payload
FROM post
WHERE created_on <= :last_created_on
  AND (
       created_on < :last_created_on
       OR (
           created_on = :last_created_on
           AND id < :last_id
       )
  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

The redundant created_on <= :last_created_on exposes a simple leading range to the optimizer. The desired shape is:

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN DESCENDING POST_SEEK_I

Predicate Information:
  access("CREATED_ON" <= :LAST_CREATED_ON)
  filter("CREATED_ON" < :LAST_CREATED_ON
         OR ("CREATED_ON" = :LAST_CREATED_ON
             AND "ID" < :LAST_ID))

That is a leading-column range followed by the exact filter, not one native two-column seek.

If the filter reads too many entries, use two disjoint bounded branches:

WITH same_time AS (
  SELECT id, created_on, payload
  FROM post
  WHERE created_on = :last_created_on
    AND id < :last_id
  ORDER BY created_on DESC, id DESC
  FETCH FIRST 50 ROWS ONLY
),
older AS (
  SELECT id, created_on, payload
  FROM post
  WHERE created_on < :last_created_on
  ORDER BY created_on DESC, id DESC
  FETCH FIRST 50 ROWS ONLY
)
SELECT *
FROM (
  SELECT * FROM same_time
  UNION ALL
  SELECT * FROM older
)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

Each branch can have its own index range. Fetching at most one page from each disjoint branch is sufficient to produce one global page.

Capture actual operations and predicates with:

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  NULL, NULL, 'ALLSTATS LAST +PREDICATE +OUTLINE'
));

The displayed Oracle plan above is the target shape, not a plan captured for this draft.

SQL Server: no tuple syntax, but multiple ranges in one seek

T-SQL does not support an ordered row constructor. Use the exact disjunction:

CREATE INDEX IX_post_seek
ON dbo.Post (created_on DESC, id DESC)
INCLUDE (payload);

SELECT TOP (@page_size)
       id, created_on, payload
FROM dbo.Post
WHERE created_on < @last_created_on
   OR (
       created_on = @last_created_on
       AND id < @last_id
   )
ORDER BY created_on DESC, id DESC;

SQL Server can compile the two disjoint ranges into one ordered Index Seek:

Top
  Index Seek
    Object: IX_post_seek
    Ordered: True
    Scan Direction: FORWARD
    Seek Predicates:
      1. created_on < @last_created_on
      2. created_on = @last_created_on
         AND id < @last_id

This is not native tuple syntax, but it has the required physical behavior. In the XML or graphical plan, confirm that both branches are under Seek Predicates, not only under a residual Predicate.

For more columns, continue the equality prefixes:

WHERE (a = @a AND b = @b AND c = @c AND d > @d)
   OR (a = @a AND b = @b AND c > @c)
   OR (a = @a AND b > @b)
   OR  a > @a
ORDER BY a, b, c, d;

The plan shape above is documented from reproducible SQL Server investigations, but it was not captured on a SQL Server instance for this draft.

Direction, NULLs, and concurrent changes

For a homogeneous order, the comparator reverses with the direction:

ORDER BY a ASC,  b ASC  -> (a,b) > (:a,:b)
ORDER BY a DESC, b DESC -> (a,b) < (:a,:b)

For mixed directions, one tuple operator does not express the order:

-- ORDER BY created_on DESC, id ASC
WHERE created_on < :created_on
   OR (
       created_on = :created_on
       AND id > :id
   )

The index must use the same mixed directions when the database supports them:

(created_on DESC, id ASC)

Pagination columns should preferably be NOT NULL. A comparison with NULL is not true, and databases may order nulls differently. If nulls are part of the application order, encode that order explicitly and index the same expression.

Finally, keyset pagination does not freeze the dataset. Separate requests under READ COMMITTED can observe inserts, deletes, or updates to ordering columns between pages. Decide whether this is acceptable for a live feed or whether all pages must use one consistent snapshot.

The practical rule

For PostgreSQL, start with:

WHERE (created_on, id)
    < (:last_created_on::timestamp, :last_id::bigint)
ORDER BY created_on DESC, id DESC
LIMIT :page_size

Then read the plan:

the complete row value should be in Index Cond
there should be no residual cursor Filter
the index should provide the order
the number of entries read should be close to the pagination size

For other databases, preserve the same lexicographic semantics but use the form their optimizer can seek:

PostgreSQL: correctly typed row comparison
YugabyteDB: verify storage pushdown, the original hash-plus-range case still needs the scalar range workaround
MySQL: row comparison when it covers the leftmost index prefix, otherwise expand and inspect all used key parts
MongoDB: two $or branches and SORT_MERGE
Oracle: leading range plus filter, or two bounded branches
SQL Server: expanded predicate compiled into multiple seek ranges.

The SQL text is only the request. The execution plan tells you where pagination really starts.

Does Hibernate generate the best for each database?

Hibernate ORM does not use the dialect capability flags to choose the predicate shape for keyset pagination. For ORDER BY created_on DESC, id DESC it generates created_on < ? OR (created_on = ? AND id < ?), i.e. exactly the "exact OR expression" shown above, on every database, including PostgreSQL where the row comparison (created_on, id) < (?, ?) would yield an Index Cond composite boundary.

Hibernate does contain dialect-aware machinery for row comparisons, true by default and overridden to false by Oracle, SQL Server, DB2, Sybase, HSQL and others, but it only applies to tuple/subquery comparisons written explicitly in HQL, not to keyset pagination.

When a dialect must emulate a tuple comparison, the generation depends on an indexOptimized flag:

the "Normal" form produces the top-level OR (a > 1 or a = 1 and b > 2), which defeats index seeks as we have seen above
the "Optimized" form, selected by the dialects that lack row-value syntax (Oracle, SQL Server, DB2, Sybase, etc.), emits a >= 1 and not (a = 1 and b <= 2) — a top-level AND with a broadened leading bound that the optimizer can turn into an index range and then recheck, the same "leading range plus filter" that we have seen above.

However, the key set pagination doesn't use this and then doenst have this optimization

So on PostgreSQL and MySQL 8.0.30+, Hibernate's output is correct but uses the weaker filterable form instead of a composite index boundary. On SQL Server the expansion can still be compiled into multiple seek ranges, and on Oracle it may produce the scan-plus-filter plan unless you write the bound predicate yourself.

In short, Hibernate guarantees correct results everywhere, but guarantees the optimal plan nowhere — the same 'verify with EXPLAIN' rule applies to ORM-generated SQL as to hand-written SQL.

References

The FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai, and a LIMIT equivalent for PostgreSQL

Franck Pachot — Sun, 04 Oct 2026 22:36:44 +0000

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:

Order the rows first, then apply ROWNUM outside:

SELECT *
FROM (
  SELECT ...
  FROM ...
  ORDER BY ...
)
WHERE ROWNUM <= 42;

Calculate an analytic 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.

First, a transformation to ROW_NUMBER()

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. However, 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 and is just a marketing suffix following the tech hype cycles (_i_nternet, _g_rid, _c_loud, and now ai). It's not a major version change either: Oracle 18c and 19c belong to the 12cR2 release family, with names changed to the release year. Similarly, 26ai is still in the 23 major release family, and “ai” replaced “c” with the 23.4 minor version.

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.” The change is transparent to the API, as applications still use the same syntax, but the implementation is important to understand when it comes to troubleshoot results or performance. Fortunately, Oracle Database provides the right tools to the developer, especially dbms_xplanand dbms_utility.

The first problem was costing

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.

dbi BlogROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog

top-n queries with rownum and row_number() and new Oracle 12c syntax

dbi-services.com

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.

I've also written about a remaining performance issue on Exadata, fixed in 12.2 Oracle Exadata – poor optimization for FIRST_ROWS

dbi BlogOracle Exadata - poor optimization for FIRST_ROWS - dbi Blog

TABLE ACCESS STORAGE FULL FIRST ROWS: 'smart scan optimization for fast response' is not always a good optimization.

dbi-services.com

This was further 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.

dbi Blog19c: scalable Top-N queries without further hints to the query planner - dbi Blog

dbi-services.com

The improvement was 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. This WINDOW NOSORT STOPKEY stayed until 26ai where it became a simple COUNT STOPKEY like with the ROWNUM approach.

Now, back to ROWNUM

The first-k-row with ROWNUM 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. This case comes from a Stack Overflow user encountering an issue that is fixed when using ROWNUM.

EXISTS and NOT EXISTS returning a row

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. NULL doesn't matter here as this operation concerns rows and not columns, similarly to COUNT(*).

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. Rather than setting a lab with Oracle Databases and install and run the troubleshooting tools, which can take hours, I used AI. The main reason I'm sharing this is to show that with AI such troubleshooting becomes much easer.

Copilot, guided by experience

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.”

I didn't ask if it is a bug because I know SQL enough to know that no row can EXISTS and NOT EXISTS at the same time.

“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”

I'm not familiar enough with OUTER APPLY but my experience with ANSI joins on Oracle guided this question.

“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”

I didn't mention what is Pathfinder and where to find it. There are lots of online content about troubleshooting Oracle Database and the model knows about it.

“Maybe you have run without the latest patches.”

That came from my knowledge that running Oracle XE or Free edition is different from a production setup with the latest path. The AI answers where inconsistent, sometimes acting as if the lab was running with the latest patches, sometimes thinking the ALTER SESSION enabled it when ':0 actually disables it.

“I’m able to reproduce the bug on 26ai with optimizer_features_enable='21.1.0', but I would like a finer setting.”

It's good to remind the final goal. The AI though that I wanted a solution to get the right result whereas my goal was to understand exactly the scope of the problem and the solution.

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 FIRST transformation, 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.

What the trace adds

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 prove that 35915968 was originally created to fix this particular query shape. What we have demonstrated is that enabling this transformation avoids the wrong result.

New syntax, old machinery

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, or even missing 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.

What about PostgreSQL?

You may wonder how PostgreSQL dealt with the addition of FETCH FIRST to the SQL standard in 2008. 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 conclusion to this investigation is that 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.

In any case, I recommend always looking at the execution plan. Row limiting with FETCH FIRST, LIMIT, TOP, or ROWNUM sits at the boundary between relational operations and consuming their results: relations are unordered, and “the first N rows” has no meaning without an ordering. A client doesn’t need this syntax just to consume N rows: it can fetch from an ordered SQL cursor and stop when it has enough. Putting the limit in the query makes that requirement explicit to the database. I guess the choice of the analytic rewrite was made to be closer to the relational concepts and existing SQL operations, but a dedicated stop operation is finally more practical. Still, stopping after N rows does not necessarily mean reading only N rows—sorting may require reading them all. That is why the execution plan matters.

DocumentDB 0.117: scalar $group index pushdown

Franck Pachot — Thu, 01 Oct 2026 21:02:22 +0000

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.

Controlled method

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.

MongoDB 8.0 reference

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.

DocumentDB 0.116 (before this optimization)

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, NULL::text)), 'BSONHEX070000000a0000'::bson
   Buffers: shared hit=2101
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: document
         namespaceName: perf117.scalar_group
         indexName: _id_
         _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=2101
         ->  Index Scan using _id_ on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.shard_key_value = '4'::bigint)
               Buffers: shared hit=2101
 Planning:
   Buffers: shared hit=254
(15 rows)

Without scalar pushdown, DocumentDB 0.116 scans the _id_ index and visits the table for all 50,000 rows. The plan reports 2,101 shared-buffer hits. The a_1 index cannot yet supply the accumulator directly.

In this case, the Index Scan was not selected by default because it was estimated to be more costly—I had to disable the Seq Scan to reveal it.

DocumentDB 0.117 (with the optimization)

Since this feature is off by default in 0.117, where it was introduced, I enable it on the server running the MongoDB gateway and restart the local container to ensure new gateway sessions adopt the setting.

ALTER SYSTEM SET documentdb.enable_scalar_aggregate_index_pushdown TO 'on';
SELECT pg_reload_conf();

The setting used for the captured run is:

 documentdb.enable_scalar_aggregate_index_pushdown 
---------------------------------------------------
 on
(1 row)

The source code indicates that, like many features, it will be enabled in a later version: /* Added in v117, Pending stabilization, enable in v119 */. Since then, v0.118 has been renamed to v1.0 and v0.119 to v1.1 as DocumentDB prepares for a 1.0 release.

I repeat the same setup from the MongoDB 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"
  ]
}

The same maintenance step makes the heap all-visible:

VACUUM (ANALYZE);

The query is unchanged and still has no hint:

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": 1.54,
    "explainCommandExecTimeMillis": 414.061,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "IXSCAN",
              "ns": "perf117.scalar_group",
              "indexName": "a_1",
              "direction": "Forward",
              "isIndexOnlyScan": true,
              "indexUsage": {
                "indexKeyString": "{\"a\": 1}",
                "isMultiKey": false,
                "bounds": [
                  "[\"a\": (MinKey, MaxKey)]"
                ]
              },
              "startupCost": 0,
              "totalCost": 4.01,
              "indexFilterSet": [
                {
                  "a": {
                    "$range": {
                      "fullScan": true
                    }
                  }
                }
              ],
              "estimatedTotalKeysExamined": 16667
            },
            "indexCosts": [
              {
                "namespace": "perf117.scalar_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "indexKeyString": "{\"_id\": 1}",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 98.976,
            "executionStartAtTimeMillis": 0.084,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "IXSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 98.976,
              "executionStartAtTimeMillis": 0.084,
              "indexName": "a_1",
              "totalDocsAnalyzed": 0,
              "indexUsage": {
                "scanLoops": 100,
                "scanType": "ordered",
                "scanKeys": [
                  "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                ]
              },
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$root": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GENERIC_AGGREGATE",
              "startupCost": 0,
              "totalCost": 45.7,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 1
            }
          },
          "executionStats": {
            "nReturned": 1,
            "executionTimeMillis": 413.955,
            "executionStartAtTimeMillis": 413.95,
            "totalDocsExamined": 1,
            "totalKeysExamined": 1,
            "executionStages": {
              "stage": "GENERIC_AGGREGATE",
              "nReturned": 1,
              "executionTimeMillis": 413.955,
              "executionStartAtTimeMillis": 413.95,
              "totalDocsExamined": 1,
              "totalKeysExamined": 1,
              "numBlocksFromCache": 24
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The 0.117 gateway now performs an IXSCAN on a_1 with isIndexOnlyScan: true. It scans 50,000 index entries, reports totalDocsAnalyzed: 0, and makes 24 shared-buffer hits. The 200-byte payload remains unread.

The native query differs from the 0.116 script only in enabling the released feature:

\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 documentdb.enable_scalar_aggregate_index_pushdown TO on;

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, 'BSONHEX12000000096e6f77006d7a14cda001000000'::bson, NULL::text)), 'BSONHEX070000000a0000'::bson
   Buffers: shared hit=24
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: document
         namespaceName: perf117.scalar_group
         indexName: a_1
         indexKey: {"a": 1}
         isMultiKey: false
         indexBounds: ["a": (MinKey, MaxKey)]
         innerScanLoops: 100 loops
         scanType: ordered
         scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
         _id_: (indexKey='{"_id": 1}', startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=24
         ->  Index Only Scan using a_1 on documentdb_data.documents_3 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.document @<> 'BSONHEX18000000036100100000000866756c6c5363616e00010000'::bson)
               Heap Fetches: 0
               Buffers: shared hit=24
 Planning:
   Buffers: shared hit=328
(22 rows)

PostgreSQL shows the same change as Index Only Scan using a_1, with Heap Fetches: 0 and 24 shared-buffer hits. This is the access path underlying the MongoDB-compatible IXSCAN stage.

DocumentDB and PostgreSQL two-step optimization

Before this change, scalar $group index-path generation considered the group key but not the fields referenced by its accumulators. With a constant key such as _id: null, it could overlook a covering index on an accumulator field. The optimization collects simple "$field" accumulator paths and adds a fullScan qual for each. In this example, the native plan shows document @<> '{"a":{"fullScan":true}}', while the gateway explain shows the marker in indexFilterSet. The marker expands to inclusive MinKey–MaxKey bounds, so it matches all documents rather than filtering the result.

This exposes an access path. It does not force the planner to choose it.

PostgreSQL chooses the path: it costs the available alternatives and, in this run, selects the a_1 Index Only Scan. The reported access-path cost is 4.01, versus 1,379.415 for the _id_ index alternative. Those are planner estimates, not execution times. VACUUM (ANALYZE) supplies statistics and marks heap pages all-visible, helping the scan avoid heap fetches, but the injected condition does not guarantee that PostgreSQL will choose it.

In comparison, MongoDB’s hinted plan also scans a_1 from MinKey to MaxKey—shown explicitly as indexBounds: {a: ['[MinKey, MaxKey]']} but obtaining that covered path required the user’s {hint: {a: 1}} option. That hint directs MongoDB to use the index, whereas DocumentDB’s fullScan marker leaves the final access-path choice to PostgreSQL’s cost-based planner.

Conclusion

This is a measurable optimization improvement, not just a new plan-node name. DocumentDB 0.117 replaces full-document access with a covered scan of the accumulator field.

Here is a summary of the experiment:

Engine and API	Access path	Index entries	Heap/document reads	Shared-buffer hits
MongoDB 8.0	`COLLSCAN`	0	50,000 documents	not reported
MongoDB 8.0 with `hint()`	`IXSCAN`	50,000	0	not reported
DocumentDB 0.116 gateway (MongoDB API)	`COLLSCAN`	not applicable	50,000 documents	1,852
DocumentDB 0.116 native (SQL function) with Seq Scan disabled	`_id_` `Index Scan`	50,000	50,000 heap rows	2,101
DocumentDB 0.117 gateway (MongoDB API), feature on	index-only `IXSCAN` on `a_1`	50,000	0 (`totalDocsAnalyzed`)	24
DocumentDB 0.117 native (SQL function), feature on	`Index Only Scan` on `a_1`	50,000	0 (`Heap Fetches`)	24

Without needing the developer to manually suggest index usage, DocumentDB 0.117 naturally chooses a more efficient access path compared to un-hinted MongoDB 8.0 in the captured plans. MongoDB reads 50,000 documents, while DocumentDB accesses only the covered index. This extension uniquely combines the best of both worlds: MongoDB's emulation with BSON-based access paths and extended RUM indexes, plus PostgreSQL's smart query planner and cost-based optimizer. Note: It relies on accurate planner statistics for optimization and a fresh visibility map for execution. I've run VACUUM ANALYZE manually here to create a consistent test case without delay, but statistics gathering also runs smoothly in the background through PostgreSQL’s auto-vacuum.

DocumentDB 0.109: $match $sort $limit $project covered by index scan

Franck Pachot — Tue, 29 Sep 2026 22:05:28 +0000

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: $sort $group prefix pushdown

Franck Pachot — Tue, 29 Sep 2026 21:11:04 +0000

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.

Controlled method

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.

MongoDB 8.0 reference

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.

DocumentDB 0.114 (before this optimization)

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,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf116',
  '{"createIndexes":"sort_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByCompoundIdIndexPushdown TO off;

\echo === DocumentDB 0.114 ===
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}'
);

Here is the output:

 documentdb_version 
--------------------
 0.114-0
(1 row)

 drop_collection 
-----------------
 f
(1 row)

NOTICE:  creating collection
 count 
-------
 50000
(1 row)

                                                                                                                   create_indexes_non_concurrently                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BSONHEX7e00000003726177006c0000000364656661756c7453686172640059000000106e756d496e64657865734265666f72650001000000106e756d496e6465786573416674657200020000000863726561746564436f6c6c656374696f6e4175746f6d61746963616c6c790000106f6b00010000000000106f6b000100000000
(1 row)

=== DocumentDB 0.114 ===
                                                                                                                                                                                                                                                                                        QUERY PLAN                                                                                                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=100 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), 'firstVal'::text, bson_expression_get(bsonfirst(agg_stage_0.document, '{BSONHEX0c0000001061000100000000}'::bson[]), 'BSONHEX11000000020006000000246e616d650000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
   Group Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
   Buffers: shared hit=50023, temp read=1768 written=1771
   ->  Sort (actual rows=50000 loops=1)
         Output: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson)), agg_stage_0.document
         Sort Key: (bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson))
         Sort Method: external merge  Disk: 14144kB
         Buffers: shared hit=50023, temp read=1768 written=1771
         ->  Subquery Scan on agg_stage_0 (actual rows=50000 loops=1)
               Output: bson_expression_get(agg_stage_0.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700210816cda001000000'::bson), agg_stage_0.document
               Buffers: shared hit=50023
               ->  Index Scan using a_1 on documentdb_data.documents_2 collection (actual rows=50000 loops=1)
                     Output: collection.document, bson_orderby(collection.document, 'BSONHEX0c0000001061000100000000'::bson)
                     Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                     Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                     Buffers: shared hit=50023
 Planning:
   Buffers: shared hit=548
(19 rows)

With hash aggregation disabled, DocumentDB 0.114 reads the ordered a_1 index but still sorts all 50,000 rows before GroupAggregate. The sort spills to disk as a 14,144 kB external merge and reports 1,768 temporary blocks read and 1,771 written.

DocumentDB 0.116 (after this optimization)

I repeat the same:

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"
  ]
}

The same maintenance step is applied:

\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.116-0
(1 row)

 vacuumed_collection_id 
------------------------
 7
(1 row)

The query and hint are unchanged:

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": 1.094,
    "explainCommandExecTimeMillis": 280.012,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "FETCH",
              "ns": "perf116.sort_group",
              "startupCost": 0,
              "totalCost": 0,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "ns": "perf116.sort_group",
                "indexName": "a_1",
                "direction": "Forward",
                "indexUsage": {
                  "indexKeyString": "{\"a\": 1}",
                  "isMultiKey": false,
                  "bounds": [
                    "[\"a\": (MinKey, MaxKey)]"
                  ]
                },
                "startupCost": 0,
                "totalCost": 0,
                "hasOrderBy": true,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            },
            "indexCosts": [
              {
                "namespace": "perf116.sort_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 50000,
            "executionTimeMillis": 92.674,
            "executionStartAtTimeMillis": 0.11,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "FETCH",
              "nReturned": 50000,
              "executionTimeMillis": 92.674,
              "executionStartAtTimeMillis": 0.11,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 50044,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 50000,
                "executionTimeMillis": 92.674,
                "executionStartAtTimeMillis": 0.11,
                "indexName": "a_1",
                "indexUsage": {
                  "scanLoops": 100,
                  "scanType": "ordered",
                  "scanKeys": [
                    "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                  ]
                },
                "totalKeysExamined": 50000,
                "numBlocksFromCache": 50044
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 152.79,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 279.817,
            "executionStartAtTimeMillis": 3.311,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 279.817,
              "executionStartAtTimeMillis": 3.311,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 50044
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The group stage now reports aggStrategy: "Sorted" instead of 0.114's "Hashed": with the $sort pushed down, $group consumes the ordered index scan directly.

The gateway still reports 50,000 index entries and documents. DocumentDB's distinct scan for $first-only groups (documentdb.enableDistinctScanForGroupFirst, a sibling of the enableGroupByDistinctScan setting from the previous article) is disabled by default in 0.116, so all 50,000 keys are examined. I left it disabled to isolate the sort elimination, which is visible in the PostgreSQL execution plan (you can try all variations in this db<>fiddle):

\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,
  NULL
))
FROM generate_series(1, 50000) i;

SELECT documentdb_api_internal.create_indexes_non_concurrently(
  'perf116',
  '{"createIndexes":"sort_group","indexes":[{"key":{"a":1},"storageEngine":{"enableOrderedIndex":true},"name":"a_1"}]}',
  true
);

SELECT collection_id
FROM documentdb_api_catalog.collections
WHERE database_name = 'perf116' AND collection_name = 'sort_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;

SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByCompoundIdIndexPushdown TO off;

SET documentdb.enableSortPushToAccumulatorWithPrefix TO on;
\echo === DocumentDB 0.116 feature ON ===
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS)
SELECT document
FROM bson_aggregation_pipeline(
  'perf116',
  '{"aggregate":"sort_group","pipeline":[{"$sort":{"a":1}},{"$group":{"_id":"$a","firstVal":{"$first":"$name"}}}],"cursor":{},"hint":"a_1"}'
);

Here is the output:

 documentdb_version 
--------------------
 0.116-0
(1 row)

 drop_collection 
-----------------
 t
(1 row)

NOTICE:  creating collection
 count 
-------
 50000
(1 row)

                                                                                                                   create_indexes_non_concurrently                                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BSONHEX7e00000003726177006c0000000364656661756c7453686172640059000000106e756d496e64657865734265666f72650001000000106e756d496e6465786573416674657200020000000863726561746564436f6c6c656374696f6e4175746f6d61746963616c6c790000106f6b00010000000000106f6b000100000000
(1 row)

=== DocumentDB 0.116 feature ON ===
                                                                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=100 loops=1)
   Output: bson_repath_and_build('_id'::text, (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson)), 'firstVal'::text, bsonfirstwithexpr(document, 'BSONHEX11000000020006000000246e616d650000'::bson, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson, NULL::text)), (bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson))
   Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson)
   Buffers: shared hit=50023
   ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=50000 loops=1)
         Output: bson_expression_get(document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f7700d2f01acda001000000'::bson), document
         namespaceName: perf116.sort_group
         indexName: a_1
         indexKey: {"a": 1}
         isMultiKey: false
         indexBounds: ["a": (MinKey, MaxKey)]
         innerScanLoops: 100 loops
         scanType: ordered
         scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
         _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
         Buffers: shared hit=50023
         ->  Index Scan using a_1 on documentdb_data.documents_8 collection (actual rows=50000 loops=1)
               Output: document
               Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
               Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
               Buffers: shared hit=50023
 Planning:
   Buffers: shared hit=387
(23 rows)

In 0.116, GroupAggregate consumes the ordered index scan directly. The Sort node, 14 MB spill, and all temporary reads and writes are gone. The scan work is unchanged, which isolates the improvement to sort elimination.

Conclusion

DocumentDB 0.116 turns a blocking $group into a streaming one: groups come directly from the ordered index scan, with no hash table or sort in between.

Here is a summary of the experiment:

Engine and API	Index entries	Documents fetched	Blocking step	Temporary I/O
MongoDB 8.0	100	100	none, `DISTINCT_SCAN`	not reported
DocumentDB 0.114 gateway (MongoDB API)	50,000	50,000	hash aggregate (first group at ~349 ms)	not exposed
DocumentDB 0.114 native (SQL function)	50,000	50,000	external merge sort, 14,144 kB (hash aggregation off)	1,768 blocks read, 1,771 written
DocumentDB 0.116 gateway (MongoDB API)	50,000	50,000	no, sorted streaming (first group at ~3 ms)	not exposed
DocumentDB 0.116 native (SQL function)	50,000	50,000	none	none
DocumentDB 0.116 native (SQL function), `enableDistinctScanForGroupFirst` on (default in 0.117)	100	100	none	none

By default, 0.114 uses hash aggregation and blocks until it reads all 50,000 rows (first group around 349 ms). When hash aggregation is turned off, it sorts and spills 14 MB. Version 0.116 streams sorted groups directly from the index (first group in about 3 ms), avoiding sorting and temporary I/O. With default 0.116 settings, DocumentDB does not use the distinct-scan access path for this pipeline, so it still reads all 50,000 entries, but grouping is no longer blocking. Enabling enableDistinctScanForGroupFirst (the default in 0.117) brings it to MongoDB's profile: 100 keys and 100 documents.

Note that the official description of this optimization is "pushing suffix sort keys into the accumulator in $sortGroup", but $sortGroup is not a user-facing MongoDB aggregation operator. It's an internal stage name in DocumentDB's codebase: when a pipeline has a $sort immediately followed by a $group, the two are fused into a single internal stage.

DocumentDB 0.116: $group distinct scan

Franck Pachot — Thu, 24 Sep 2026 17:52:53 +0000

I previously covered MongoDB’s DISTINCT_SCAN for first/last-per-group queries. DocumentDB 0.116 (August 20, 2026) extends the same loose-index-scan principle to $group queries that return one row per distinct grouping key, combining it with the index-only access path described in the previous post of this series.

DocumentDB implements the MongoDB API as a fully open source PostgreSQL extension. This makes it possible to run MongoDB applications on the most popular open source relational database without reducing the MongoDB language to simple document filtering. In my opinion, DocumentDB is the only MongoDB emulation that translates MongoDB operators into native SQL access paths. Microsoft is the main contributor, improving the extension from en enterprise customer feedback on Azure DocumentDB.

To demonstrate this optimization, I create 50,000 documents with only 100 distinct values in a, and an ordered a_1 index. The aggregation groups by a without an accumulator:

[
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
]

The result contains 100 groups. A normal index scan reads all 50,000 index entries and lets the aggregate remove duplicates. A distinct scan can jump from one value to the next and read only 100 index entries.

I use MongoDB 8.0.28 as the reference, DocumentDB 0.114-0 as the previous version, and DocumentDB 0.116-0 with enableGroupByDistinctScan explicitly enabled. VACUUM (ANALYZE) is run on both DocumentDB versions so the visibility state is identical.

MongoDB 8.0 reference

This script creates the collection, builds the index, returns the number of groups, and runs the complete explain("executionStats"):

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

const batch = [];
for (let i = 0; i < 50000; i++) {
  batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
}
db.distinct_group.insertMany(batch);
db.distinct_group.createIndex({a: 1}, {name: "a_1"});

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": "1",
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "namespace": "distinct116.distinct_group",
            "parsedQuery": {},
            "indexFilterSet": false,
            "queryHash": "C46B0559",
            "planCacheShapeHash": "C46B0559",
            "planCacheKey": "C66CA7DD",
            "optimizationTimeMillis": 0,
            "maxIndexedOrSolutionsReached": false,
            "maxIndexedAndSolutionsReached": false,
            "maxScansToExplodeReached": false,
            "prunedSimilarIndexes": false,
            "winningPlan": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "transformBy": {
                "a": 1,
                "_id": 0
              },
              "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": 0,
            "executionStages": {
              "isCached": false,
              "stage": "PROJECTION_COVERED",
              "nReturned": 100,
              "executionTimeMillisEstimate": 0,
              "works": 101,
              "advanced": 100,
              "needTime": 0,
              "needYield": 0,
              "saveState": 3,
              "restoreState": 3,
              "isEOF": 1,
              "transformBy": {
                "a": 1,
                "_id": 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": 1
      },
      {
        "$groupByDistinctScan": {
          "newRoot": {
            "_id": "$a"
          }
        },
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      },
      {
        "$sort": {
          "sortKey": {
            "_id": 1
          }
        },
        "totalDataSizeSortedBytesEstimate": 22100,
        "usedDisk": false,
        "spills": 0,
        "spilledDataStorageSize": 0,
        "nReturned": 100,
        "executionTimeMillisEstimate": 1
      }
    ],

MongoDB recognizes that the pipeline needs only one result per distinct indexed value. The winning plan is a covered DISTINCT_SCAN. It examines 100 keys, reads no documents, and returns 100 values to $groupByDistinctScan. This is the ideal access path for this query.

DocumentDB 0.114-0 (before this optimization)

I load exactly the same 50,000 documents in DocumentDB 0.114-0 on PostgreSQL through the MongoDB-compatible gateway:

db = db.getSiblingDB("distinct116");
db.distinct_group.drop();

for (let start = 0; start < 50000; start += 1000) {
  const batch = [];
  for (let i = start; i < start + 1000; i++) {
    batch.push({_id: i, a: i % 100, payload: "x".repeat(200)});
  }
  db.distinct_group.insertMany(batch);
}

db.distinct_group.createIndex({a: 1}, {name: "a_1"});

Before running the plans, I vacuum the physical PostgreSQL table, from psql:

\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 = 'distinct116' AND collection_name = 'distinct_group'
\gset
VACUUM (ANALYZE) documentdb_data.documents_:collection_id;
SELECT :'collection_id' AS vacuumed_collection_id;

In real life, this runs automatically by the background auto-vacuum of PostgreSQL but I don't want to wait and prefer a deterministic test.

The 0.114-0 output identifies the extension and the collection that was vacuumed:

Pager usage is off.
SET
 documentdb_version 
--------------------
 0.114-0
(1 row)

VACUUM
 vacuumed_collection_id 
------------------------
 4
(1 row)

I then run the same aggregation through the MongoDB API:

db = db.getSiblingDB("distinct116");

const pipeline = [
  {$group: {_id: "$a"}},
  {$sort: {_id: 1}}
];

print(EJSON.stringify({
  resultCount: db.distinct_group.aggregate(
    pipeline,
    {hint: "a_1"}
  ).toArray().length,
  explain: db.distinct_group.explain("executionStats").aggregate(
    pipeline,
    {hint: "a_1"}
  )
}, null, 2));

Here is the full execution plan:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 3.197,
    "explainCommandExecTimeMillis": 179.281,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "IXSCAN",
              "indexName": "a_1",
              "direction": "Forward",
              "isIndexOnlyScan": true,
              "startupCost": 0,
              "totalCost": 13.89,
              "indexFilterSet": [
                {
                  "a": {
                    "$range": {
                      "orderByScan": 1
                    }
                  }
                }
              ],
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {

            "nReturned": 50000,
            "executionTimeMillis": 109.417,
            "executionStartAtTimeMillis": 0.09,
            "totalDocsExamined": 50000,
            "totalKeysExamined": 50000,
            "executionStages": {
              "stage": "IXSCAN",
              "nReturned": 50000,
              "executionTimeMillis": 109.417,
              "executionStartAtTimeMillis": 0.09,
              "indexName": "a_1",
              "totalDocsAnalyzed": 0,
              "totalKeysExamined": 50000,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 176.999,
            "executionStartAtTimeMillis": 1.335,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 176.999,
              "executionStartAtTimeMillis": 1.335,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 179.079,
            "executionStartAtTimeMillis": 178.996,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 179.079,
              "executionStartAtTimeMillis": 178.996,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 32,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 178.606,
                "executionStartAtTimeMillis": 1.343,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The gateway reports an index-only IXSCAN, but its cursor returns all 50,000 index entries. totalDocsAnalyzed: 0 confirms that the table is not visited, while totalKeysExamined: 50000 shows the remaining work. The GROUP stage reduces these entries to 100 groups.

The native PostgreSQL call uses the same database, collection, pipeline, hint, planner settings, and post-vacuum state:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

The PostgreSQL execution plan is:

                                                                                                                                                                     QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson)
               Buffers: shared hit=24
               ->  Index Only Scan using a_1 on documentdb_data.documents_4 collection (actual rows=50000 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f77002cec22cda001000000'::bson), collection.document
                     Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                     Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                     Heap Fetches: 0
                     Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(20 rows)

The PostgreSQL plan makes the old behavior explicit. Index Only Scan using a_1 reads 50,000 rows before GroupAggregate returns 100. Heap Fetches: 0 is good, but the executor still visits every duplicate index entry.

DocumentDB 0.116-0 (after this optimization)

The new distinct-scan path is controlled by documentdb.enableGroupByDistinctScan. It is disabled by default in this image, so I enable it explicitly. ALTER SYSTEM is used from psql because the MongoDB gateway opens independent PostgreSQL sessions:

\pset pager off
ALTER SYSTEM SET documentdb.enableGroupByDistinctScan TO 'on';
SELECT pg_reload_conf();
SHOW documentdb.enableGroupByDistinctScan;

After restarting the local DocumentDB container, I run the same gateway setup script, I vacuum the new physical collection with the same script, and run the query unchanged. Here is the complete MongoDB-compatible gateway output from DocumentDB 0.116-0:

{
  "resultCount": 100,
  "explain": {
    "explainVersion": 2,
    "command": "db.runCommand({explain: { 'aggregate': 'distinct_group', 'pipeline': [{ '$group': { '_id': '$a' } }, { '$sort': { '_id': 1 } }], 'hint': 'a_1', 'cursor': {} }})",
    "explainCommandPlanningTimeMillis": 1.482,
    "explainCommandExecTimeMillis": 1.631,
    "stages": [
      {
        "$cursor": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "DISTINCT_SCAN",
              "ns": "distinct116.distinct_group",
              "startupCost": 0,
              "totalCost": 0,
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "IXSCAN",
                "indexName": "a_1",
                "direction": "Forward",
                "isIndexOnlyScan": true,
                "indexUsage": {
                  "indexKeyString": "{\"a\": 1}",
                  "isMultiKey": false,
                  "bounds": [
                    "[\"a\": (MinKey, MaxKey)]"
                  ]
                },
                "startupCost": 0,
                "totalCost": 0,
                "indexFilterSet": [
                  {
                    "a": {
                      "$range": {
                        "orderByScan": 1
                      }
                    }
                  }
                ],
                "estimatedTotalKeysExamined": 5556
              }
            },
            "indexCosts": [
              {
                "namespace": "distinct116.distinct_group",
                "costs": [
                  {
                    "indexName": "_id_",
                    "startupCost": 0.415,
                    "totalCost": 1379.415,
                    "selectivity": 1,
                    "correlation": 0.75,
                    "estimatedPercentIndexPagesLoaded": 100,
                    "estimatedTotalIndexEntries": 50000,
                    "boundarySelectivity": 1
                  }
                ]
              }
            ]
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 0.696,
            "executionStartAtTimeMillis": 0.042,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "DISTINCT_SCAN",
              "nReturned": 100,
              "executionTimeMillis": 0.696,
              "executionStartAtTimeMillis": 0.042,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24,
              "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 100,
                "executionTimeMillis": 0.531,
                "executionStartAtTimeMillis": 0.038,
                "indexName": "a_1",
                "totalDocsAnalyzed": 0,
                "indexUsage": {
                  "scanLoops": 100,
                  "scanType": "ordered",
                  "scanKeys": [
                    "key 1: [(isInequality: true, estimatedEntryCount: 50000)]"
                  ]
                },
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      },
      {
        "$group": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "GROUP",
              "startupCost": 0,
              "totalCost": 125.01,
              "aggStrategy": "Sorted",
              "estimatedTotalKeysExamined": 5556
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.085,
            "executionStartAtTimeMillis": 0.07,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "GROUP",
              "nReturned": 100,
              "executionTimeMillis": 1.085,
              "executionStartAtTimeMillis": 0.07,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "numBlocksFromCache": 24
            }
          }
        }
      },
      {
        "$sort": {
          "queryPlanner": {
            "winningPlan": {
              "stage": "SORT",
              "startupCost": 540.04,
              "totalCost": 553.93,
              "sortKeysCount": 1,
              "sortKey": [
                {
                  "_id": 1
                }
              ],
              "estimatedTotalKeysExamined": 5556,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "startupCost": 0,
                "totalCost": 194.46,
                "estimatedTotalKeysExamined": 5556
              }
            }
          },
          "executionStats": {
            "nReturned": 100,
            "executionTimeMillis": 1.475,
            "executionStartAtTimeMillis": 1.39,
            "totalDocsExamined": 100,
            "totalKeysExamined": 100,
            "executionStages": {
              "stage": "SORT",
              "nReturned": 100,
              "executionTimeMillis": 1.475,
              "executionStartAtTimeMillis": 1.39,
              "totalDocsExamined": 100,
              "totalKeysExamined": 100,
              "sortMethod": "quicksort",
              "totalDataSizeSortedBytesEstimate": 29,
              "numBlocksFromCache": 29,
              "inputStage": {
                "stage": "PROJECTION_DEFAULT",
                "nReturned": 100,
                "executionTimeMillis": 1.273,
                "executionStartAtTimeMillis": 0.074,
                "totalDocsExamined": 100,
                "totalKeysExamined": 100,
                "numBlocksFromCache": 24
              }
            }
          }
        }
      }
    ],
    "ok": 1
  }
}

The winning cursor stage is now DISTINCT_SCAN over the index-only IXSCAN. It returns 100 entries instead of 50,000. The gateway reports totalKeysExamined: 100, totalDocsAnalyzed: 0, and 100 scan loops: one ordered index probe per distinct value.

The native PostgreSQL script verifies the physical operator:

\set ON_ERROR_STOP on
\pset pager off

SET search_path TO documentdb_api, documentdb_core, documentdb_api_catalog, documentdb_api_internal, public;
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
SET enable_hashagg TO off;
SET documentdb.enableGroupByDistinctScan TO on;

SHOW documentdb.enableGroupByDistinctScan;

SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS ON, SUMMARY OFF, TIMING OFF, VERBOSE ON)
SELECT document FROM bson_aggregation_pipeline(
  'distinct116',
  '{"aggregate":"distinct_group","pipeline":[{"$group":{"_id":"$a"}},{"$sort":{"_id":1}}],"cursor":{},"hint":"a_1"}'
);

Here is the PostgreSQL execution plan:

QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual rows=100 loops=1)
   Output: agg_stage_1.document, (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson))
   Sort Key: (bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)) NULLS FIRST
   Sort Method: quicksort  Memory: 29kB
   Buffers: shared hit=24
   ->  Subquery Scan on agg_stage_1 (actual rows=100 loops=1)
         Output: agg_stage_1.document, bson_orderby(agg_stage_1.document, 'BSONHEX0e000000105f6964000100000000'::bson)
         Buffers: shared hit=24
         ->  GroupAggregate (actual rows=100 loops=1)
               Output: bson_repath_and_build('_id'::text, (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))), (bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson))
               Group Key: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson)
               Buffers: shared hit=24
               ->  Custom Scan (DocumentDBApiExplainQueryScan) (actual rows=100 loops=1)
                     Output: bson_expression_get(collection.document, 'BSONHEX0e00000002000300000024610000'::bson, true, 'BSONHEX12000000096e6f770033f022cda001000000'::bson), collection.document
                     namespaceName: distinct116.distinct_group
                     indexName: a_1
                     indexKey: {"a": 1}
                     isMultiKey: false
                     indexBounds: ["a": (MinKey, MaxKey)]
                     innerScanLoops: 100 loops
                     scanType: ordered
                     scanKeyDetails: key 1: [(isInequality: true, estimatedEntryCount: 50000)]
                     _id_: (startup cost=0.415, total cost=1379.415, selectivity=1, correlation=0.750, estimated index pages loaded=100.00%, estimated total index entries=50000, boundary selectivity=1, num boundaries=0, estimated data pages loaded=0.00%)
                     Buffers: shared hit=24
                     ->  Custom Scan (DocumentDBApiDistinctQueryScan) (actual rows=100 loops=1)
                           Output: collection.document
                           Buffers: shared hit=24
                           ->  Index Only Scan using a_1 on documentdb_data.documents_10 collection (actual rows=100 loops=1)
                                 Output: collection.document
                                 Index Cond: (collection.document @<> 'BSONHEX1e00000003610016000000106f7264657242795363616e00010000000000'::bson)
                                 Order By: (collection.document |-<> 'BSONHEX0c0000001061000100000000'::bson)
                                 Heap Fetches: 0
                                 Buffers: shared hit=24
 Planning:
   Buffers: shared hit=18
(35 rows)

DocumentDBApiDistinctQueryScan wraps the index-only scan. The inner Index Only Scan now reports actual rows=100 instead of 50,000, with Heap Fetches: 0. This is not only a different plan name: 49,900 duplicate index entries no longer pass through the aggregate.

Conclusion

Here is a summary of the result on this 50,000-document example:

Engine	Access path	Index entries read	Heap/document reads	Groups
MongoDB 8.0.28	covered `DISTINCT_SCAN`	100	0	100
DocumentDB 0.114-0 gateway (MongoDB API)	index-only `IXSCAN`	50,000	0 analyzed	100
DocumentDB 0.114-0 native (SQL function)	`Index Only Scan`	50,000	0 heap fetches	100
DocumentDB 0.116-0 gateway (MongoDB API), feature on	`DISTINCT_SCAN` over index-only `IXSCAN`	100	0 analyzed	100
DocumentDB 0.116-0 native (SQL function), feature on	`DocumentDBApiDistinctQueryScan` over `Index Only Scan`	100	0 heap fetches	100

On this query, DocumentDB 0.116-0 reaches the same physical efficiency as MongoDB: both read one covered index entry per group and no documents. Compared with DocumentDB 0.114-0, the new access path reduces index entries flowing into the aggregate from 50,000 to 100, a 500-fold reduction. This is the kind of MongoDB compatibility that matters for performance: the same query is translated into the same efficient access strategy.

DEV Community: Franck Pachot

GDPR data minimization with pg_duckdb: join Azure Blob facts to erasable customer data in Postgres

Lab setup

Write a minimized fact file

The join before erasure

Delete the identity, not the history

The full plan: which side is hashed?

One top-level engine over heterogeneous sources

What statistics does DuckDB see?

What if the filter starts from the customer?

Pushdown is not the same as row-group pruning

What this pattern does and does not guarantee

Takeaways

Automate Postgres-to-Blob with durable workflow (pg_durable + pg_duckdb) in Azure Database for PostgreSQL

Enabling pg_durable

The data to archive

The durable workflow

Watching it run

It worked, end to end

Why durable, not just a cron job

Make it nightly

Takeaways

Archive cold Postgres data to Azure Blob and keep querying it with pg_duckdb

Setup (same table as the previous post)

Writing to Blob from inside Azure Database for PostgreSQL

Step 1 — Identify the cold slice

Step 2 — Export to Parquet in Blob (in-database)

Alternative — client-side export

Step 3 — Read it back through pg_duckdb

Step 4 — Delete the cold rows from Postgres

Step 5 — Keep querying history, concatenated with live data

Making it a routine

Takeaways

DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

Enable pg_duckdb on Azure Database for PostgreSQL

A table to analyze

Turning DuckDB on

Where it wins: a CUBE over 10M rows

Where it doesn't win: a plain aggregate

Use pg_hint_plan to force at the query scope

The precision detail

Takeaways

Full pg_duckdb on Azure Database for PostgreSQL

Enabling pg_duckdb on Azure Database for PostgreSQL

Reading Parquet from Azure Blob

The same query, the same plan

Where the extension approach actually differs

1. The engine is directly usable

2. It accelerates your existing PostgreSQL tables

3. It is the same extension you run anywhere

When pg_duckdb on Azure is different than self-managed?

Hardening from superuser configuration

Write-back works, but not with the COPY TO syntax

Summary

Amazon Aurora's analytics is gated DuckDB: comparison with open-source pg_duckdb

Provision Aurora with aurora_analytics

Query Parquet in Aurora and capture the plan

The same query in pg_duckdb on Docker

Comparison

How differently they package it

How to Do Pagination Right: Row Comparison, Datatypes, and Index Range Scans

Reproducing it on PostgreSQL

The best predicate: one correctly typed row comparison

The exact OR expression is correct, but not as good on PostgreSQL

The datatype can silently remove part of the seek

A test that exposes wasted index work

Which databases support an index for row comparison?

YugabyteDB: improved, but the old case still needs the workaround

MySQL: native row comparison, with a prefix caveat

MongoDB: no tuple syntax, but two index seeks

Oracle: no ordered row comparison

SQL Server: no tuple syntax, but multiple ranges in one seek

Direction, NULLs, and concurrent changes

The practical rule

Does Hibernate generate the best for each database?

References

The FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai, and a LIMIT equivalent for PostgreSQL

First, a transformation to ROW_NUMBER()

The first problem was costing

dbi BlogROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog

Use `pg_hint_plan` to force at the query scope

Write-back works, but not with the `COPY TO` syntax