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 — on vanilla Postgres a non-superuser gets permission denied to COPY to a file for any path, so the two-step error dance here is Azure-specific behavior).

But write-back is not actually unavailable on Azure Database for PostgreSQL — because the extension exposes the DuckDB engine directly, you can hand the COPY straight to DuckDB with duckdb.raw_query, which never goes through PostgreSQL's COPY privilege gate at all:

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)

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. It just isn't the pretty COPY TO one-liner, 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 (plain COPY TO needs superuser, which Flex doesn't grant)	❌ 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 put the same DuckDB engine inside PostgreSQL. Aurora wraps it as a managed, catalog-oriented, read-only foreign-data wrapper. Azure ships the community extension, so DuckDB is directly usable, can accelerate your existing tables, and can even write back to the lake. 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. It's the same engine, and the difference is how much of it the service hands you.

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