The full pg_duckdb extension on Azure Database for PostgreSQL
In my previous post, Amazon Aurora's analytics being DuckDB — a side-by-side 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)
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.
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 process is genuine, and one capability — write-back to the lake — requires a detour you should be aware of.
The hardening is real, and you can't change it
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/x.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
This is not an az://-specific block — 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. Azure 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:
COPY (SELECT 1 a) TO 'az://lake/x.parquet'; -- relative path not allowed …
COPY (SELECT 1 a) TO 'relative/x.csv'; -- relative path not allowed …
-- a genuine absolute path gets past that and hits the privilege check:
COPY (SELECT 1 a) TO '/tmp/x.csv';
-- ERROR: must be superuser to COPY to or from a file
So an az:// URL (no leading /) trips Azure's "relative path" check before it ever reaches the privilege check, but both roads are closed to a non-superuser. (Community PostgreSQL actually checks privileges first —... (truncated)
