DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

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:

1. Add pg_duckdb to azure.extensions (the allow-list).
2. 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.