DEV Community: Franck Pachot

PostgreSQL document compression: TOAST thresholds, storage policies, and page size

Franck Pachot — Mon, 02 Nov 2026 21:14:00 +0000

A common way to store documents in PostgreSQL is a table with an ID and a JSONB column. The open-source DocumentDB extension instead stores BSON, the binary document format used by MongoDB, to provide more data types and MongoDB compatibility.

Both approaches commonly combine two patterns that do not perfectly fit what SQL databases were designed for:

Rows with a single variable-length column, whose values can range from a few dozen bytes to many megabytes.
Repeated data or metadata, such as similar field names and arrays of objects with the same schema, like time-series buckets.

MongoDB's storage engine, WiredTiger, packs many documents into B-tree leaf pages. The default maximum page sizes — 32 KB for leaf pages and 4 KB for internal pages — are limits on the uncompressed page image fed to the block compressor, not on individual documents: MongoDB configures leaf_value_max=64MB, so a document larger than 32 KB simply produces a larger leaf page rather than overflow storage. Snappy compresses each page image for ordinary collections (time-series collections use Zstd, which ignores the global compressor setting), and the compressed result is written in multiples of the 4 KB allocation_size. Because compression runs over the whole page image, several small documents — including their repeated field names — contribute redundant byte sequences to the same compression input.

A JSON or BSON document can contain substantial byte-sequence redundancy: repeated field names, common name prefixes, and repeated values or structures. LZ77-family compressors such as Snappy and LZ4 can replace matching sequences with backreferences to earlier bytes. They don't need to understand JSON, and matches aren't restricted to the beginning of a document.

Binary does not mean compressed: BSON includes field names, and repeated embedded objects repeat those names. JSONB can contain similar redundancy. PostgreSQL can compress those sequences in JSON, JSONB, or a BSON extension type. However, the decision to attempt compression is not driven by document redundancy. It belongs to TOAST's generic heap-row storage policy: reduce a wide row, trying column compression before storing permitted attributes externally in chunks.

This distinction matters for small documents. It also makes increasing the page size potentially counterproductive: the same document can stop being compressed on a larger-page build.

I will reproduce both effects, then compare the SQL controls with the hardcoded values. The examples use PostgreSQL 19 and core JSONB, so they can run without an extension or a custom patch. The distinction between whole-row activation and individual-column compression also applies to the TOAST-backed BSON values stored by DocumentDB.

What is compressed, and what is measured?

TOAST measures the whole tuple (row) to decide whether to start. However, compression acts on individual eligible column values, not on the entire row or all the documents in a page.

PostgreSQL's ordinary table storage uses fixed-size pages, 8 KB by default. A row cannot span those pages. Large column values can instead be moved to an associated TOAST table, and the main row then contains a pointer to access the chunks in the TOAST table by index scan.

JSONB still contains compressible byte sequences. It is a binary representation, not an already-compressed JSON string. Within one object, it normalizes key order and duplicate keys, but an array of similarly structured objects can repeat the same field names. No shared key dictionary exists across those objects or across independently stored JSONB rows.

The distinction between repeated names within a document and repeated names across documents is important. PostgreSQL compresses one column value at a time. PostgreSQL calls each value a datum in its source code. That per-datum compression can use the former repetition, not the latter.

MongoDB's WiredTiger storage engine uses Snappy block compression on disk by default for ordinary collections. Small documents can share a page, so one block-compression input can include repeated names and structures from several documents. This is not a separate compression call per small document. Other codecs can be configured, and time-series collections have different defaults. This is a difference in compression scope, not a capability reserved to Snappy.

This post measures stored datum representations, not table allocation or query performance. I use:

pg_column_size(doc::text::jsonb) to reconstruct an uncompressed logical JSONB datum for these ordinary JSON inputs
pg_column_compression(doc) to report the actual accepted method
pg_column_size(doc) for its stored representation
pg_column_toast_chunk_id(doc) IS NOT NULL to identify external values

The last function is available in PostgreSQL 19. For an external value, pg_column_size is not the inline pointer size. The on-disk pointer in the main row occupies 18 bytes. The external size excludes the outer four-byte varlena length word, which explains some four-byte differences below without attributing them to the codec.

Reproduce the environment and output

All SQL needed for the experiments is included below. Run the statements in order in a fresh test database. For each page-size comparison, start a separate PostgreSQL instance.

I use aligned ASCII output, with NULL displayed as (null). To reproduce that format, start an existing server's session with these options:

psql -X -P pager=off -P 'null=(null)' -P format=aligned \
  -P linestyle=ascii -P border=1 -U postgres -d postgres

These are the initial settings on the source-built 8 KB server:

postgres=# SHOW server_version;

 server_version 
----------------
 19rc1
(1 row)

postgres=# SHOW block_size;

 block_size 
------------
 8192
(1 row)

postgres=# SHOW default_toast_compression;

 default_toast_compression 
---------------------------
 lz4
(1 row)

The selected codec is explicit in every test. PostgreSQL 14-18's upstream default is pglz, but an LZ4-enabled PostgreSQL 19 RC1 build defaults to LZ4. SET COMPRESSION has been available since PostgreSQL 14.

LZ4 is a sensible starting choice when compression/decompression CPU matters, but the workload still determines the trade-off. It does not always produce the smallest result. DocumentDB's current setup/tuning configuration selects default_toast_compression = 'lz4' when supported. This is a launcher-chosen PostgreSQL setting, not a property of BSON, nor a guarantee that every installation is configured that way. Check the live server and column settings rather than assuming them from a product name.

A compressible document below the activation threshold

I construct a JSONB array containing eight objects with repeated temperature and humidity names. Both rows receive the same document. The only difference is an unrelated column stored PLAIN, so it cannot be compressed or moved externally.

postgres=# CREATE TABLE small_documents(
    id integer, doc jsonb, other text
);

CREATE TABLE

postgres=# ALTER TABLE small_documents
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE small_documents
            ALTER COLUMN other SET STORAGE PLAIN
;

ALTER TABLE

postgres=# INSERT INTO small_documents
    SELECT id,
           jsonb_build_object('samples', (
             SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
             FROM generate_series(1,8)
           )),
           repeat('q', CASE id WHEN 1 THEN 0 ELSE 1600 END)
    FROM generate_series(1,2) AS g(id)
;

INSERT 0 2

postgres=# SELECT id,
           pg_column_size(doc::text::jsonb) AS logical_bytes,
           octet_length(other) AS other_bytes,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM small_documents ORDER BY id
;

 id | logical_bytes | other_bytes | compression | stored_bytes | external 
----+---------------+-------------+-------------+--------------+----------
  1 |           508 |           0 | (null)      |          508 | f
  2 |           508 |        1600 | lz4         |          109 | f
(2 rows)

The first 508-byte JSONB datum remains uncompressed. The second compresses to 109 bytes, even though its uncompressed document bytes are identical. The additional 1,600-byte column made the tuple large enough to enter TOAST processing.

This proves both points:

Repeated field names and values in JSONB are compressible.
PostgreSQL does not independently ask whether that small JSONB datum would benefit from compression. The enclosing tuple controls activation.

I used an unrelated column for academic purposes. Of course, I'm not suggesting that adding more data to trigger compression is a good idea.

The hardcoded gate is not the per-table target

In the PostgreSQL source, the ordinary values are derived as follows:

#define TOAST_TUPLES_PER_PAGE 4
#define TOAST_TUPLE_THRESHOLD MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD

MaximumBytesPerTuple(4) accounts for page headers, line pointers, and alignment. It is not exactly block_size / 4. Here are the values for the default 8 KB page size and for PostgreSQL builds with 16 KB or 32 KB pages:

PostgreSQL page size	Fixed tuple activation threshold	Default tuple target	Maximum native TOAST chunk payload
8,192 bytes	2,032 bytes	2,032 bytes	1,996 bytes
16,384 bytes	4,080 bytes	4,080 bytes	4,044 bytes
32,768 bytes	8,176 bytes	8,176 bytes	8,140 bytes

These are the values for the tested eight-byte-aligned builds. The chunk capacities were also observed from actual external data, not inferred from page size alone.

Both heap_prepare_insert and heap_update check the fixed threshold before invoking the toaster for fresh inline tuples. Inside the toaster, toast_tuple_target provides the reduction target, after subtracting tuple header overhead.

It’s not simply "compress when the row cannot fit on a page.” A tuple just over 2 KB can still fit on an 8 KB page. By default, wide rows are reduced earlier, with the goal of keeping their size consistent with roughly four tuples per page. This is a row-density and oversized-attribute policy, rather than a separately tunable document-compression policy.

These are fresh values with one eligible document column. If a row has several eligible columns, the toaster works in phases rather than compressing the entire row as one object.

Change the target without changing activation

The following input view constructs fresh JSONB values from repetitive text. It deliberately samples several payload sizes. It is not intended to represent a natural document distribution.

A view is used rather than copying stored compressed values from another table. Reusing an existing compressed datum would confound a test of whether the destination attempts compression.

postgres=# CREATE TEMP VIEW input_documents AS
    SELECT n, jsonb_build_object('v', repeat('x', 1024 * (1 << n))) AS doc
    FROM (VALUES (0),(1),(2),(6),(9),(10),(11)) AS sizes(n)
;

CREATE VIEW

postgres=# CREATE TABLE extended_default(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# CREATE TABLE extended_200(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=200)
;

CREATE TABLE

postgres=# CREATE TABLE extended_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# ALTER TABLE extended_default
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE extended_200
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE extended_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO extended_default
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO extended_200
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO extended_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# SELECT policy, 1 << n AS payload_kib,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position, 'default' AS policy, * FROM extended_default
      UNION ALL SELECT 2,'target_200',* FROM extended_200
      UNION ALL SELECT 3,'target_4096',* FROM extended_4k
    ) AS cases
    ORDER BY position,n
;

   policy    | payload_kib | compression | stored_bytes | external 
-------------+-------------+-------------+--------------+----------
 default     |           1 | (null)      |         1041 | f
 default     |           2 | lz4         |           39 | f
 default     |           4 | lz4         |           47 | f
 default     |          64 | lz4         |          288 | f
 default     |         512 | lz4         |         2083 | t
 default     |        1024 | lz4         |         4139 | t
 default     |        2048 | lz4         |         8252 | t
 target_200  |           1 | (null)      |         1041 | f
 target_200  |           2 | lz4         |           39 | f
 target_200  |           4 | lz4         |           47 | f
 target_200  |          64 | lz4         |          284 | t
 target_200  |         512 | lz4         |         2083 | t
 target_200  |        1024 | lz4         |         4139 | t
 target_200  |        2048 | lz4         |         8252 | t
 target_4096 |           1 | (null)      |         1041 | f
 target_4096 |           2 | (null)      |         2065 | f
 target_4096 |           4 | lz4         |           47 | f
 target_4096 |          64 | lz4         |          288 | f
 target_4096 |         512 | lz4         |         2087 | f
 target_4096 |        1024 | lz4         |         4139 | t
 target_4096 |        2048 | lz4         |         8252 | t
(21 rows)

The default JSONB storage strategy is EXTENDED, which permits both compression and external storage.

Three observations distinguish the decisions:

Even with a 200-byte target, the 1 KB-payload document still remains uncompressed and inline at 1,041 bytes. Its tuple did not enter TOAST because it is smaller than the hardcoded MaximumBytesPerTuple(4).
Once TOAST is active, lowering the target externalizes values sooner: the 64 KB payload is inline with the default target but external with the 200-byte target.
With a 4 KB target, the 2 KB-payload document remains uncompressed at 2,065 bytes. Its tuple enters TOAST, but already meets the larger target. The same document compressed to 39 bytes in the other two tables.

Raising the target is therefore not an independent "retain more compressed documents inline" control. It can also prevent a compression attempt.

The default and 200-byte cases report 288 and 284 bytes respectively for the same compressed 64 KB-payload document. This is the inline/external varlena accounting difference, not a better compression result.

Storage strategies: what SQL can control

There are four column storage strategies:

Strategy	Compression	External storage
`EXTENDED`	Permitted, attempted in the first phase when reduction is needed	Permitted
`EXTERNAL`	Not permitted	Permitted
`MAIN`	Permitted, attempted in a later phase when needed	Last resort
`PLAIN`	Not permitted	Not permitted

MAIN does not guarantee inline storage. MAIN attributes are also skipped during the first compression pass: they get their own compression round only after EXTENDED/EXTERNAL attributes have been compressed and externalized. They are likewise skipped by the out-of-line loops until the last-resort pass, which uses TOAST_TUPLE_TARGET_MAIN (MaximumBytesPerTuple(1) ≈ 8,160 bytes on an 8 KB build). EXTERNAL does not mean "externalize every document,” and an explicit LZ4 method cannot override its prohibition on compression.

Here EXTERNAL and MAIN use the same 4 KB target, making the comparison independent of an accidental target difference:

postgres=# CREATE TABLE external_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# CREATE TABLE main_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# CREATE TABLE plain_case(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# ALTER TABLE external_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE main_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE plain_case
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE external_4k
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# ALTER TABLE main_4k
            ALTER COLUMN doc SET STORAGE MAIN
;

ALTER TABLE

postgres=# ALTER TABLE plain_case
            ALTER COLUMN doc SET STORAGE PLAIN
;

ALTER TABLE

postgres=# INSERT INTO external_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO main_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO plain_case
    SELECT * FROM input_documents WHERE n<=2 ORDER BY n
;

INSERT 0 3

postgres=# SELECT policy, 1 << n AS payload_kib,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position,'EXTERNAL' AS policy,* FROM external_4k
      UNION ALL SELECT 2,'MAIN',* FROM main_4k
      UNION ALL SELECT 3,'PLAIN',* FROM plain_case
    ) AS cases
    ORDER BY position,n
;

  policy  | payload_kib | compression | stored_bytes | external 
----------+-------------+-------------+--------------+----------
 EXTERNAL |           1 | (null)      |         1041 | f
 EXTERNAL |           2 | (null)      |         2065 | f
 EXTERNAL |           4 | (null)      |         4109 | t
 EXTERNAL |          64 | (null)      |        65549 | t
 EXTERNAL |         512 | (null)      |       524301 | t
 EXTERNAL |        1024 | (null)      |      1048589 | t
 EXTERNAL |        2048 | (null)      |      2097165 | t
 MAIN     |           1 | (null)      |         1041 | f
 MAIN     |           2 | (null)      |         2065 | f
 MAIN     |           4 | lz4         |           47 | f
 MAIN     |          64 | lz4         |          288 | f
 MAIN     |         512 | lz4         |         2087 | f
 MAIN     |        1024 | lz4         |         4143 | f
 MAIN     |        2048 | lz4         |         8252 | t
 PLAIN    |           1 | (null)      |         1041 | f
 PLAIN    |           2 | (null)      |         2065 | f
 PLAIN    |           4 | (null)      |         4113 | f
(17 rows)

For EXTERNAL, the 2 KB payload is still inline, but the 4 KB payload is external without compression.

For MAIN, the 1 MB payload compresses to 4,143 inline bytes. It can stay inline because MAIN postpones externalization to its last-resort target: the maximum tuple size that fits on one page. The larger 2 MB-payload document still becomes external after compression.

For PLAIN, no value compresses or becomes external. A row that cannot fit on the heap page fails. Run this separate statement expecting an error:

postgres=# INSERT INTO plain_case
    VALUES (3,jsonb_build_object('v',repeat('x',8192)))
;

ERROR:  54000: row is too big: size 8240, maximum size 8160
LOCATION:  RelationGetBufferForTuple, hio.c:531

The 8,160-byte maximum in this error is the heap-page tuple limit, not the 2,032-byte TOAST activation threshold. They answer different questions.

This is the complete SQL control surface relevant to these cases:

Control	Scope	Changes the fixed activation threshold?
`ALTER COLUMN ... SET STORAGE ...`	Column's permitted/preferred representation	No
`ALTER COLUMN ... SET COMPRESSION pglz/lz4`	Codec when compression is attempted	No
`default_toast_compression`	Default codec when the column has no override	No
`ALTER TABLE ... SET (toast_tuple_target=...)`	Whole-tuple reduction target after entry	No
`TOAST_TUPLE_THRESHOLD`, `TOAST_TUPLES_PER_PAGE`	Compiled activation policy	Source change
`TOAST_MAX_CHUNK_SIZE`, `EXTERN_TUPLES_PER_PAGE`	Compiled external chunk capacity	Separate from activation
`BLCKSZ`	Compiled page size	Changes several defaults together

Changing page and chunk sizes requires a freshly initialized cluster. They are not runtime GUCs. Changing only a main-tuple threshold is not inherently an on-disk page-format change, but a correct implementation still has to consider all heap entry paths and TOAST-table creation.

The compression algorithm still matters after activation

Compression is an attempt, not a storage guarantee. Pglz's default strategy has a 32-byte minimum input, a 25% minimum reduction policy, and a 1,024-byte encoded-output first-success limit: it aborts if it has emitted that much without finding a match.

Those are pglz strategy rules, not user-configurable document thresholds. LZ4 does not impose pglz's first-success or minimum-rate rules. Both must still pass TOAST's header/alignment savings check.

Changing the column codec does not rewrite existing values:

postgres=# CREATE TABLE codec_example(
    id integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# ALTER TABLE codec_example
            ALTER COLUMN doc SET COMPRESSION pglz
;

ALTER TABLE

postgres=# INSERT INTO codec_example
    VALUES
      (1,jsonb_build_object('v',repeat('x',4096)))
;

INSERT 0 1

postgres=# ALTER TABLE codec_example
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO codec_example
    VALUES
      (2,jsonb_build_object('v',repeat('x',4096)))
;

INSERT 0 1

postgres=# SELECT id, pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes
    FROM codec_example ORDER BY id
;

 id | compression | stored_bytes 
----+-------------+--------------
  1 | pglz        |           71
  2 | lz4         |           47
(2 rows)

The old value remains pglz. The freshly constructed new value uses LZ4. An update that leaves the document unchanged, or a copy of an already compressed datum, need not recompress it. Therefore, changing the setting is not proof that existing data now uses the new codec.

Larger pages: fewer documents become compression candidates

Larger pages can reduce the number of external chunks. That does not mean they preserve compression behavior.

I rebuilt PostgreSQL 19 RC1 with --with-blocksize=8, 16, and 32, keeping the other configure options the same, then initialized fresh clusters.

Do not reuse a data directory across page sizes. A different page size also requires rebuilding linked extensions against that PostgreSQL installation. This comparison deliberately uses core JSONB rather than mixing extension versions or a stock image with a different source-built baseline.

8 KB: all but the smallest repeated-field document compress

The samples array now contains 8, 64, 128, or 256 similarly structured objects. All tests explicitly select LZ4. One table retains the build's default target. The other sets it to 2040.

postgres=# CREATE TEMP VIEW page_inputs AS
    SELECT n, jsonb_build_object('samples', (
             SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
             FROM generate_series(1,n)
           )) AS doc
    FROM (VALUES (8),(64),(128),(256)) AS counts(n)
;

CREATE VIEW

postgres=# CREATE TABLE page_default(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# CREATE TABLE page_target(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=2040)
;

CREATE TABLE

postgres=# ALTER TABLE page_default
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE page_target
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO page_default
    SELECT * FROM page_inputs ORDER BY n
;

INSERT 0 4

postgres=# INSERT INTO page_target
    SELECT * FROM page_inputs ORDER BY n
;

INSERT 0 4

postgres=# CREATE VIEW page_observations AS
    SELECT policy, n AS objects,
           pg_column_size(doc::text::jsonb) AS logical_bytes,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position,'default' AS policy,* FROM page_default
      UNION ALL SELECT 2,'target_2040',* FROM page_target
    ) AS cases ORDER BY position,n
;

CREATE VIEW

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | lz4         |          126 | f
 default     |     128 |          7708 | lz4         |          150 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | lz4         |          126 | f
 target_2040 |     128 |          7708 | lz4         |          150 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

16 KB: the 3,868-byte JSONB document no longer compresses

The complete setup is unchanged. This is the same final query on the 16 KB build:

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | lz4         |          150 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | lz4         |          150 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The tuple containing 3,868 JSONB bytes remains below the 4,080-byte activation gate. Lowering its target to 2040 does not make the caller invoke TOAST.

32 KB: the 7,708-byte document also stops compressing

The same query on the 32 KB build:

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | (null)      |         7708 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | (null)      |         7708 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The 7,708-byte JSONB value compressed to 150 bytes on the 8- and 16-KB builds. On the 32 KB build, the tuple stays below the 8,176-byte gate, and the datum remains at 7,708 bytes. A 2040-byte target does not restore compression.

The 15,388-byte positive control compresses on all three builds. Its tuple exceeds even the largest activation threshold.

UPDATE has the same outer gate

The following statements reconstruct each JSONB value on UPDATE, rather than reassigning an existing compressed value. On the 32 KB build:

postgres=# UPDATE page_default d
    SET doc=jsonb_build_object('samples', (
        SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
        FROM generate_series(1,d.n)
    ))
;

UPDATE 4

postgres=# UPDATE page_target d
    SET doc=jsonb_build_object('samples', (
        SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
        FROM generate_series(1,d.n)
    ))
;

UPDATE 4

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | (null)      |         7708 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | (null)      |         7708 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The INSERT and UPDATE results matched on all three servers. This is why looking only at the inner compression loop, without the INSERT/UPDATE callers, can give the wrong interpretation of toast_tuple_target.

Chunk capacity does grow

This 8 KB-page case inserts a 65,536-character text value with STORAGE EXTERNAL and reads its actual TOAST chunks. \gset assigns the TOAST relation name to a psql variable, avoiding a hardcoded relation OID:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            1996
(1 row)

Run the same statements in the 16 KB instance:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            4044
(1 row)

And in the 32 KB instance:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            8140
(1 row)

The maximum payloads are 1,996, 4,044, and 8,140 bytes.

That is an independent benefit larger pages can provide for sufficiently large values. It does not compensate automatically for the loss of compression on smaller documents. Neither fewer chunks nor larger pages alone establishes lower allocation or better read performance.

Why this behavior remains

The documented TOAST design handles values that cannot fit directly in heap tuples, and keeps main rows smaller so queries not needing large attributes can avoid fetching them. It is a general relational storage mechanism, predating JSONB, not a policy specifically optimized for repeated document schemas.

I searched the pgsql-hackers discussions with the pghacker MCP. This separation was already being discussed explicitly in 2019:

February 6, 2019: Pavan Deolasee proposed a separate table-level option to control compression, named compress_tuple_target, so smaller values could be compressed inline without requiring out-of-line storage.
March 5, 2019: Andrew Dunstan supported the idea, while calling for more review.
April 3, 2019: Michael Paquier identified the fixed-threshold caller checks independently of the per-table toast_tuple_target.

The subsequent discussion identified two broader problems: a bounded-width relation may have no TOAST table, so lowering its trigger could require creating one under a stronger lock. Rejecting previously accepted low targets could break dump restores. These are implementation and compatibility constraints, not evidence that the current fraction is optimal for small JSONB documents.

If you want to build and run the three page sizes

The SQL above is sufficient for an existing PostgreSQL 19 server. The following commands run in Bash on a machine with Docker. The Dockerfile is supplied inline. No repository checkout or external code file is required.

set -euo pipefail

for kb in 8 16 32; do
  docker build --build-arg BLOCK_SIZE="$kb" \
    -t "toast-pg19rc1:${kb}k" - <<'DOCKERFILE'
FROM postgres:19rc1-alpine
RUN apk add --no-cache build-base curl bison flex perl linux-headers \
    icu-dev lz4-dev openssl-dev readline-dev zlib-dev
ARG BLOCK_SIZE=8
RUN set -eu; \
    case "$BLOCK_SIZE" in 8|16|32) ;; *) exit 1 ;; esac; \
    mkdir /tmp/page-build; \
    curl --fail --location \
      "https://ftp.postgresql.org/pub/source/v${PG_VERSION}/postgresql-${PG_VERSION}.tar.bz2" \
      -o /tmp/page-build/postgres.tar.bz2; \
    tar -xjf /tmp/page-build/postgres.tar.bz2 -C /tmp/page-build; \
    cd "/tmp/page-build/postgresql-${PG_VERSION}"; \
    ./configure --prefix=/usr/local --with-blocksize="$BLOCK_SIZE" \
      --with-lz4 --with-icu --with-openssl; \
    make -s -j2; \
    make -s install; \
    cd /; \
    rm -rf /tmp/page-build
DOCKERFILE
done

Choose one page size, start a fresh disposable instance, and open psql:

kb=8                      # repeat with 16 and 32
pg="toast-research-${kb}k-$$"
docker run --detach --name "$pg" --network none \
  -e POSTGRES_HOST_AUTH_METHOD=trust "toast-pg19rc1:${kb}k"

ready=no
for attempt in $(seq 1 60); do
  if docker exec "$pg" pg_isready -U postgres; then
    ready=yes
    break
  fi
  sleep 1
done
if [ "$ready" != yes ]; then
  docker logs "$pg"
  docker rm --force --volumes "$pg"
  exit 1
fi

docker exec -it "$pg" psql -X -v ON_ERROR_STOP=1 \
  -P pager=off -P 'null=(null)' -P format=aligned \
  -P linestyle=ascii -P border=1 -U postgres -d postgres

Use the 8 KB instance for the storage-strategy cases. For the page comparison, run the same page_inputs, page_default, page_target, and observation statements on each instance. Repeat the chunk case on all three.

The deliberately failing PLAIN insert is a separate statement: do not include it in a successful setup batch with ON_ERROR_STOP.

After leaving psql with \q, remove only the container created above:

docker rm --force --volumes "$pg"

Conclusion: separate compression eligibility from row placement

The experiment does not show that PostgreSQL cannot compress document redundancy. It shows that the eligibility policy is tied to heap tuple size and page size, rather than to a document-column compression policy.

Under the ordinary defaults, increasing page size from 8 to 16 to 32 KB raises activation from about 2 to 4 to 8 KB. Some highly compressible documents therefore stop reaching the compressor. A lower toast_tuple_target cannot recover those missed attempts. A higher target can suppress attempts further.

A useful direction is to make the hardcoded activation policy configurable, separately from the reduction/inline target. For example, a 32 KB-page server could retain a lower activation threshold without adopting a smaller page. For document workloads, a per-column compression-eligibility threshold may be more directly useful than a whole-row threshold.

This isn't as simple as making the hardcoded value a parameter. A safe implementation must cover INSERT, UPDATE, rewrites, TOAST-table creation, locking, and existing representations. The appropriate default would still need insertion CPU and read/update benchmarks on representative document sizes. There is no basis here for declaring that every 500-byte document should be compressed.

The narrower conclusion is reproducible: the quarter-page activation gate can prevent useful compression, and larger pages may widen the gap. Currently, the DocumentDB extension relies on the TOAST mechanism, trading MongoDB’s compression behavior for compatibility with PostgreSQL storage. I've seen many MongoDB workloads store small documents (and ran a poll on LinkedIn), much like a relational model—even though document models are meant to store larger aggregates to favor single-document data locality and single-IO transactions.

Load CSV and JSON from Azure Blob into Postgres with pg_duckdb

Franck Pachot — Fri, 23 Oct 2026 13:02:31 +0000

Data arrives as files. A vendor drops a CSV in a container, an upstream system exports newline-delimited JSON, a daily extract lands in Blob. The usual way into Postgres is a client-side load tool and a hand-written COPY with a column list. With pg_duckdb you can skip most of that: point read_csv() / read_json() at the file in Blob, let DuckDB infer the schema, and CREATE TABLE AS straight into a normal PostgreSQL table — or just query the file in place and never import it.

Setup

Only the Blob secret is needed:

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

The files in Blob

A vendor list as CSV and an event stream as newline-delimited JSON:

az://lake/incoming/vendors.csv
   vendor_id,name,country,active,rating
   1,Acme Corp,US,true,4.5
   2,Globex,CH,false,3.9
   3,Initech,FR,true,4.1
   4,Umbrella,DE,true,2.8
   5,Soylent,JP,false,4.9

az://lake/incoming/events.json
   {"event_id":101,"ts":"2026-01-05T10:00:00","user":"alice","action":"login","value":1}
   {"event_id":102,"ts":"2026-01-05T10:02:13","user":"bob","action":"purchase","value":42}
   {"event_id":103,"ts":"2026-01-05T10:05:41","user":"alice","action":"logout","value":1}

The image cover of this blog post shows those files in Microsoft Azure Storage Explorer. I can read them from Azure Database for PostgreSQL with the pg_duckdb extension enabled.

Read a CSV with inferred types

read_csv() parses and infers types from the file — no column list, no cast boilerplate to look at the data:

postgres=> SELECT r['vendor_id'], r['name'], r['country'], r['active'], r['rating']
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
 vendor_id |   name    | country | active | rating
-----------+-----------+---------+--------+--------
 1         | Acme Corp | US      | t      | 4.5
 2         | Globex    | CH      | f      | 3.9
 3         | Initech   | FR      | t      | 4.1
 4         | Umbrella  | DE      | t      | 2.8
 5         | Soylent   | JP      | f      | 4.9
(5 rows)

Note active came back as a boolean and rating as a number — DuckDB inferred them from the file; you didn't declare anything.

Materialize into a real PostgreSQL table

To bring it in, wrap it in CREATE TABLE AS and cast each column to the Postgres type you want. (Casting on the way in is good practice — it pins the schema rather than trusting inference, and it gives money/number columns an explicit precision.)

postgres=> CREATE TABLE vendors AS
           SELECT r['vendor_id']::int       AS vendor_id,
                  r['name']::text           AS name,
                  r['country']::text        AS country,
                  r['active']::boolean      AS active,
                  r['rating']::numeric(3,1) AS rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
SELECT 5

"vendors" is now an ordinary PostgreSQL table — index it, join it, update it, EXPLAIN it like any other. Two plans make the point.

With duckdb.force_execution = true, DuckDB will still scan the table (because it can), through a TABLE_SCAN on Table: vendors — which already proves "vendors" is a real Postgres relation, not a file handle:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.021 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││             Total Time: 0.0012s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │       Table: vendors      │
 │      Filters: active      │
 │                           │
 │           3 rows          │
 │          (0.00s)          │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.196 ms
 Execution Time: 1.034 ms

The TABLE_SCAN with Table: vendors and 3 rows (the active vendors) proves "vendors" is a real Postgres relation DuckDB reads, not a file handle. Turn forcing back off (the default) and the data is served entirely by the PostgreSQL executor — no DuckDB in the picture at all:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate (actual time=0.024..0.025 rows=1.00 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on vendors (actual time=0.018..0.019 rows=3.00 loops=1)
         Filter: active
         Rows Removed by Filter: 2
         Buffers: shared hit=1
 Planning Time: 0.071 ms
 Execution Time: 0.062 ms

That Seq Scan on vendors is the proof the load worked: the rows are physically in Postgres, queryable by the native engine like any table you created by hand.

Same thing for JSON

read_json() handles newline-delimited JSON (and arrays), inferring fields:

postgres=> SELECT r['event_id'], r['user'], r['action'], r['value']
           FROM read_json('az://lake/incoming/events.json') r
;
 event_id | user  |  action  | value
----------+-------+----------+-------
 101      | alice | login    | 1
 102      | bob   | purchase | 42
 103      | alice | logout   | 1
(3 rows)

And into a table, parsing the timestamp as you go:

postgres=> CREATE TABLE events_log AS
           SELECT r['event_id']::int   AS event_id,
                  (r['ts'])::timestamp AS ts,
                  r['user']::text      AS usr,
                  r['action']::text    AS action,
                  r['value']::int      AS value
           FROM read_json('az://lake/incoming/events.json') r
;
SELECT 3

postgres=> SELECT * FROM events_log ORDER BY event_id
;
 event_id |         ts          |  usr  |  action  | value
----------+---------------------+-------+----------+-------
 101      | 2026-01-05 10:00:00 | alice | login    | 1
 102      | 2026-01-05 10:02:13 | bob   | purchase | 42
 103      | 2026-01-05 10:05:41 | alice | logout   | 1
(3 rows)

For a recurring load, use INSERT INTO events_log SELECT … FROM read_json(…) instead of CREATE TABLE AS, and point the path at the new file (or a glob over a folder of drops — see the partitioned-lakes post).

Or don't import at all

Sometimes you don't need the data in Postgres — you just need an answer. You can aggregate the Blob file directly:

postgres=> SELECT r['country'] AS country,
                  count(*)     AS n,
                  avg((r['rating'])::numeric(3,1)) AS avg_rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
           GROUP BY r['country']
           ORDER BY 1
;
 country | n | avg_rating
---------+---+------------
 CH      | 1 | 3.9
 DE      | 1 | 2.8
 FR      | 1 | 4.1
 JP      | 1 | 4.9
 US      | 1 | 4.5
(5 rows)

That's the nice property of this approach: the same read_csv() / read_json() is either a one-off query or the source of an import, depending on whether you wrap it in CREATE TABLE AS. Query in place for exploration and ad-hoc checks. Materialize when you need indexes, constraints, or OLTP access.

The reverse direction: writing a result back out

Loading isn't one-way. You can also write a Postgres query result back to Blob as Parquet — handy for publishing an aggregate or an extract. On Azure Database for PostgreSQL the plain COPY … TO 'az://…' syntax is blocked for a normal login (it needs superuser), so you drive the write through the DuckDB engine with duckdb.raw_query, which authorizes it with the secret and the az:// allow-list — the object-storage controls PostgreSQL's server-file gate has no say over:

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

(1 row)

Note the pgduckdb.public. prefix on the table: inside raw_query you're in DuckDB's catalog, not PostgreSQL's search path, so a bare vendors won't resolve. The archive post covers this write path in full.

A few practical notes

Cast on import. Inference is convenient for a look, but pin the types in your CREATE TABLE AS so the table schema is deterministic — and give decimals an explicit precision (numeric(p,s)), which also keeps later DuckDB queries on the table from falling back (see the numeric gotcha side-note).
CSV options mirror DuckDB's, passed with PostgreSQL named-arg syntax: read_csv('az://…/f.csv', delim := ';', header := true) and so on.
Globs work here too. read_csv('az://lake/incoming/*.csv') loads a whole drop folder in one statement.
Big loads stream. DuckDB reads the file in chunks, so you're not limited to what fits in memory for the parse.

Takeaways

read_csv / read_json over az:// read files straight from Blob with type inference — no pre-defined table, no external load tool.
CREATE TABLE AS SELECT … FROM read_csv(…) materializes into a normal Postgres table (cast the columns to pin the schema); the native Seq Scan plan proves the rows really landed in Postgres. INSERT … SELECT appends for recurring loads.
Or skip the import and aggregate the file in place — same function, your choice.
Globs and DuckDB's CSV options (delim :=, header :=, …) are available via PostgreSQL named-argument syntax.

The archive post wrote cold data out to Blob by hand, step by step. In the last post of the series I turn that export-verify-delete cycle into a single durable, scheduled workflow with pg_durable — so a crash mid-way resumes instead of re-exporting.

pg_duckdb quietly ignored force_execution on my numeric column

Franck Pachot — Fri, 23 Oct 2026 12:35:08 +0000

In this series, I encountered a pg_duckdb gotcha with unconstrained numeric column. With duckdb.force_execution = true, I expected DuckDB to run my aggregate. But it ran on the PostgreSQL engine instead, with a warning. Here's how to see it and fix it.

Reproduce

I created a table with four columns of the same number, differing only in declared type:

DROP TABLE IF EXISTS nf;
CREATE TABLE nf (
 id bigint
 , n_bare  numeric
 , n_10_2  numeric(10,2)
 , d       double precision
);
INSERT INTO nf SELECT g, g+0.5, g+0.5, g+0.5 
 FROM generate_series(1,100000) g
;
ANALYZE nf
;

Here are their Postgres type modifiers (-1 means "no declared precision"):

postgres=> SELECT attname, atttypmod FROM pg_attribute
           WHERE attrelid='nf'::regclass AND attnum>0 AND NOT attisdropped ORDER BY attnum
;
 attname | atttypmod
---------+-----------
 id      |        -1
 n_bare  |        -1
 n_10_2  |    655366
 d       |        -1
(4 rows)

n_bare and d show -1 (no declared precision)
n_10_2 shows 655366, the encoded form of numeric(10,2)

The symptom

I forced DuckDB on, then sum the bare numeric:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: Binder Error: No function matches the given name and argument types 'sum(UnsupportedPostgresType(DuckDB requires the precision of a NUMERIC to be set))'
                      QUERY PLAN
-----------------------------------------------------
 Aggregate  (cost=2074.00..2074.01 rows=1 width=32)
   ->  Seq Scan on nf  (cost=0.00..1824.00 rows=100000 width=8)
(2 rows)

That's a plain PostgreSQL plan. I've set force_execution = true, yet the top node is a plain Aggregate, not a Custom Scan (DuckDBScan). DuckDB declined and PostgreSQL ran it. A WARNING at plan time is emitted when pg_duckdb tries to build the DuckDB plan and bails on the unconstrained numeric. But it's a WARNING, not an error, easy to miss from an application, and the query still returns the right answer on the native executor. If you were only watching the response time you'd think pg_duckdb didn't help, when it never ran the query in DuckDB at all.

I've run the same sum over the numeric(10,2) column:

postgres=> EXPLAIN SELECT sum(n_10_2) FROM nf
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │    Aggregates: sum(#0)    │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           n_10_2          │
 │                           │
 │       ~100,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │         Table: nf         │
 │    Projections: n_10_2    │
 │                           │
 │       ~100,000 rows       │
 └───────────────────────────┘
(25 rows)

DuckDB runs it. The only difference between the two queries is that one column has a declared precision and the other doesn't.

Why

DuckDB's DECIMAL is fixed precision, up to 38 digits. A Postgres numeric with a declared (p,s) maps straight onto it. A bare numeric has no bound — there's nothing for DuckDB to map it to — so pg_duckdb leaves the whole query to PostgreSQL. (A numeric(38,10) works fine, only the unconstrained one falls back.) The fallback itself is a feature: forcing DuckDB can never break a query, it just might not accelerate it. The problem is only that it's silent.

The fix

One session setting makes the bare numeric run in DuckDB, read as double:

postgres=> SET duckdb.convert_unsupported_numeric_to_double = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │    Aggregates: sum(#0)    │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           n_bare          │
 │                           │
 │       ~100,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │         Table: nf         │
 │    Projections: n_bare    │
 │                           │
 │       ~100,000 rows       │
 └───────────────────────────┘
(25 rows)

No more warning, and the top node is now Custom Scan (DuckDBScan) — DuckDB runs it, reading the bare numeric as double.

The caveat is in the name: double is floating point, so you can lose the exact decimal precision numeric guarantees — fine for analytical sums and averages, think twice for exact financial rounding. The cleaner fix is to declare the precision in the schema (numeric(12,2)), so DuckDB runs it exactly, no flag.

Takeaway

With force_execution = true, always EXPLAIN and check the top node is Custom Scan (DuckDBScan). If it's a plain PostgreSQL node, DuckDB declined — and a bare numeric (typmod -1) is the usual reason. Declare the precision, or set duckdb.convert_unsupported_numeric_to_double to true if your can accept that decimal fractions that cannot be stored with exact base-10 precision are rounded.

Even with duckdb.force_execution = true, you should always run EXPLAIN to verify that the query utilizes the Custom Scan (DuckDBScan) node. If the planner falls back to native PostgreSQL nodes, it indicates that DuckDB declined the query—most commonly due to an unconstrained PostgreSQL NUMERIC column (with a type modifier of -1). To resolve this, explicitly declare the column's precision and scale, or set duckdb.convert_unsupported_numeric_to_double = true if your workload can tolerate the inherent precision loss and rounding behavior of binary floating-point (DOUBLE) conversions.

Query a partitioned data lake in Azure Blob with pg_duckdb

Franck Pachot — Sat, 17 Oct 2026 21:57:05 +0000

A data lake is rarely one file. Once you archive or publish data by period, you get a tree of files — and the trick is querying the whole thing as one table while only reading the parts a query needs. pg_duckdb does both: globs read many files as one, and Hive-style partitioning turns the folder names into columns and lets DuckDB skip files it doesn't need. With read_parquet(), the query itself chooses the file set and reader options, rather than requiring a foreign table for each file. Here are some examples from Azure Database for PostgreSQL.

Setup

Only the Blob secret is needed for this post — everything else reads from the lake:

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

A partitioned layout

The convention: a folder per partition, named column=value, each holding one or more Parquet files. Here, four yearly files of sales data:

az://lake/sales_part/
   year=2022/data.parquet
   year=2023/data.parquet
   year=2024/data.parquet
   year=2025/data.parquet

You produce these one file per year. The only new thing here is the folder naming (year=YYYY/). The write from PostgreSQL is covered in the archive post. With the sales table from that post, one year's export looks like this:

SELECT duckdb.raw_query($$
  COPY (
    SELECT * FROM pgduckdb.public.sales
    WHERE sale_date >= DATE '2022-01-01'
      AND sale_date < DATE '2023-01-01'
  ) TO 'az://lake/sales_part/year=2022/data.parquet'
  (FORMAT parquet)
$$);

Note the SELECT before duckdb.raw_query and the pgduckdb.public. catalog prefix: inside raw_query you're in DuckDB's catalog, not PostgreSQL's. Keep this wrapper on the tested Flexible Server: plain PostgreSQL COPY ... TO 'az://...' is rejected by the service's file-COPY guard, even though self-managed pg_duckdb can intercept it. The export also requires permission to execute duckdb.raw_query(). A client-side export and upload is the alternative described in the archive post.

The column=value convention lets the reader interpret the folder names as partition columns, and a wildcard selects the files across those folders.

Read them all with one glob

A * in the path reads every matching file as a single table:

postgres=> SELECT count(*) FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet'
)
;
  count
---------
 8001032
(1 row)

A specific path reads one partition:

postgres=> SELECT count(*) FROM read_parquet(
 'az://lake/sales_part/year=2024/data.parquet'
)
;
  count
---------
 2003093
(1 row)

And an explicit list reads an exact set — note the ARRAY[...] syntax (a bare [...] is DuckDB dialect that the PostgreSQL parser rejects):

postgres=> SELECT count(*) FROM read_parquet(ARRAY[
 'az://lake/sales_part/year=2024/data.parquet',
 'az://lake/sales_part/year=2025/data.parquet'
           ])
;
  count
---------
 4003149
(1 row)

Want to know which files a glob actually touched? Add the filename column:

postgres=> SELECT DISTINCT r['filename']
           FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet',
 filename := true
) r
           ORDER BY 1
;
                  filename
---------------------------------------------
 az://lake/sales_part/year=2022/data.parquet
 az://lake/sales_part/year=2023/data.parquet
 az://lake/sales_part/year=2024/data.parquet
 az://lake/sales_part/year=2025/data.parquet
(4 rows)

(pg_duckdb passes DuckDB's optional arguments with PostgreSQL's named-argument syntax, name := value — so filename := true, hive_partitioning := true.)

Turn the folder name into a column

The partition value lives only in the path, not inside the files. Turn on hive_partitioning and year becomes a real column you can group and filter on:

postgres=> SELECT r['year'] AS year, count(*) AS n
           FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet',
  hive_partitioning := true
) r
           GROUP BY r['year']
           ORDER BY 1
;
 year |    n
------+---------
 2022 | 1998570
 2023 | 1999313
 2024 | 2003093
 2025 | 2000056
(4 rows)

Because the year is encoded in the layout, you can compute per-year totals even without the files carrying a date column at all.

Partition values are not necessarily text. On this server (PostgreSQL 18.6, pg_duckdb 1.1.0), SELECT pg_typeof(r['year']) over this reader returns bigint: DuckDB infers the type from the folder values. Both r['year'] = 2024 and the quoted literal below work and prune to one file.

When file schemas evolve

If a later year's file adds a column, use union_by_name := true to align columns by name rather than position:

SELECT count(*)
FROM read_parquet(
  'az://lake/sales_part/year=*/data.parquet',
  hive_partitioning := true,
  union_by_name := true
);

Columns absent from a file become NULL for that file's rows. This handles added or missing columns, not arbitrary incompatible type changes.

The payoff: partition pruning, visible in the plan

This is why you partition. Filter on the partition column and DuckDB reads only the matching files. EXPLAIN shows it — and here is the full verbatim plan:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*)
           FROM read_parquet('az://lake/sales_part/year=*/data.parquet',
                             hive_partitioning := true) r
           WHERE r['year'] = '2024'
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.78s               ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │       File Filters:       │
 │       (year = 2024)       │
 │                           │
 │    Scanning Files: 1/4    │
 │    Total Files Read: 1    │
 │                           │
 │       2,003,093 rows      │
 │          (0.11s)          │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 329.131 ms
 Execution Time: 330.574 ms

The lines that matter are Scanning Files: 1/4 and Total Files Read: 1. The glob matched four files, but the year = 2024 filter let DuckDB skip scanning data from three of them — it decided that from the folder names alone (File Filters: (year = 2024)). This does not mean zero listing or metadata requests: resolving the glob and binding the reader can still require them. The 2,003,093 rows at the scan is the one file's actual row count; a count(*) can use Parquet metadata rather than decode every row. With hundreds of monthly partitions, a filter selecting one month can restrict the data scan to that month's files, whether there is one file or several. That's why this layout pays off, and pg_duckdb gives it to you over Azure Blob with the filter sitting in ordinary SQL.

Does `abfss://` work on Azure Database for PostgreSQL?

DuckDB's Azure extension supports both Blob (az://) and ADLS Gen2 (abfss://) paths, but the managed server's allow-list comes first. I retested on this Flexible Server with PostgreSQL 18.6 and pg_duckdb 1.1.0:

postgres=> SHOW duckdb.enable_external_access;
 duckdb.enable_external_access
-------------------------------
 off
(1 row)

postgres=> SHOW duckdb.allowed_directories;
 duckdb.allowed_directories
----------------------------
 az://
(1 row)

postgres=> SELECT count(*)
FROM read_parquet('abfss://lake/sales_part/year=2024/data.parquet');
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: Permission Error: Cannot access file "abfss://lake/sales_part/year=2024/data.parquet" - file system operations are disabled by configuration

LINE 1: SELECT count(*) AS count FROM system.main.read_parquet('abfss://lake/sales_part/year=2024...
                                      ^
postgres=>

Both a single-file read and a glob fail, including with the fully qualified abfss://<account>.dfs.core.windows.net/lake/... form. The same reads through az:// succeed. These settings require superuser privileges to change, which this login does not have.

So the two schemes are not interchangeable on this server. The rejection happens in DuckDB's filesystem permission check, before we can test the ADLS endpoint or compare listing performance. Use az:// here; there is no measured basis in this test for recommending abfss:// as faster.

Takeaways

A glob (year=*/data.parquet) reads a whole partitioned lake as one table. A path reads one partition. ARRAY[...] reads an explicit set.
hive_partitioning := true turns column=value folder names into queryable columns — group and filter on them like any column.
Filtering the partition column prunes files before reading: the plan shows Scanning Files: 1/4. This is the main reason partitioning pays off.
Pass DuckDB's reader options with PostgreSQL named-arg syntax (:=); use ARRAY[...] not [...] for file lists.
Use union_by_name := true when files add or omit columns across partitions.
On the tested Flexible Server, az:// is allowed but abfss:// is blocked by configuration, for both single files and globs.

Everything so far has read Parquet. Blob also collects the messier arrivals — vendor CSVs, newline-delimited JSON event streams. Next I'll point read_csv and read_json at those and load them into Postgres with a single statement.

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.

DEV Community: Franck Pachot

PostgreSQL document compression: TOAST thresholds, storage policies, and page size

What is compressed, and what is measured?

Reproduce the environment and output

A compressible document below the activation threshold

The hardcoded gate is not the per-table target

Change the target without changing activation

Storage strategies: what SQL can control

The compression algorithm still matters after activation

Larger pages: fewer documents become compression candidates

8 KB: all but the smallest repeated-field document compress

16 KB: the 3,868-byte JSONB document no longer compresses

32 KB: the 7,708-byte document also stops compressing

UPDATE has the same outer gate

Chunk capacity does grow

Why this behavior remains

If you want to build and run the three page sizes

Conclusion: separate compression eligibility from row placement

Load CSV and JSON from Azure Blob into Postgres with pg_duckdb

Setup

The files in Blob

Read a CSV with inferred types

Materialize into a real PostgreSQL table

Same thing for JSON

Or don't import at all

The reverse direction: writing a result back out

A few practical notes

Takeaways

pg_duckdb quietly ignored force_execution on my numeric column

Reproduce

The symptom

Why

The fix

Takeaway

Query a partitioned data lake in Azure Blob with pg_duckdb

Setup

A partitioned layout

Read them all with one glob

Turn the folder name into a column

When file schemas evolve

The payoff: partition pruning, visible in the plan

Does abfss:// work on Azure Database for PostgreSQL?

Takeaways

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

Does `abfss://` work on Azure Database for PostgreSQL?

Use `pg_hint_plan` to force at the query scope

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