DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

[kasenur]

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.

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

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

(DATE '2021-01-01' + ((g * 7) % 1826))

(ARRAY['US','CH','FR','DE','UK','JP','BR','IN'])[1 + (g % 8)] AS country,

(ARRAY['Consumer','SMB','Enterprise'])[1 + (g % 3)]

((5 + (g % 500))::numeric(10,2))

FROM generate_series(1, 10000000) g
;