Databases Azure

pg_duckdb DuckDB Analytics for PostgreSQL 17 on Ubuntu 24.04 on Azure User Guide

| Product: pg_duckdb DuckDB Analytics for PostgreSQL 17 on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of pg_duckdb with PostgreSQL 17 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. pg_duckdb is the official PostgreSQL extension for DuckDB. It embeds DuckDB's vectorised, columnar execution engine directly inside the PostgreSQL backend process, so an analytical query is executed by an analytics engine instead of PostgreSQL's row at a time executor, without moving the data anywhere and without changing your SQL.

Two things follow from that, and they are the whole point of this image:

  • Analytics on the data you already have. Set duckdb.force_execution = true and your existing GROUP BY, window function and large join queries against ordinary PostgreSQL heap tables are planned into a DuckDB scan. There is no export step, no second system to keep in sync, and no ETL.

  • Data lake files as tables. read_parquet(), read_csv() and friends make Parquet, CSV, JSON, Iceberg and Delta Lake files queryable as if they were tables, and you can join them against your PostgreSQL tables in one statement. Files can be on local disk or in S3, GCS, Azure Blob or R2.

Everything runs on a single VM. There is no separate analytics cluster, no container runtime and no external service. PostgreSQL listens on port 5432; that one port is the whole product.

pg_duckdb has no web interface. It is a PostgreSQL extension, so you reach it with psql or any PostgreSQL client driver, exactly as you reach any other PostgreSQL database. Everything in this guide is done at the SQL prompt, and any BI tool, notebook or application that already speaks PostgreSQL can use it unchanged.

Sample data ships with the image. A 50,000 row analytics.orders table is created in the analyticsdb database, and the same 50,000 rows are written out as a Parquet file and a CSV file on local disk. That means the data lake capability is demonstrable on a brand new VM with no cloud account, no credentials and no configuration, before you connect any object storage of your own.

Security by design. On first boot each VM generates a unique password for the postgres superuser, a unique password for the analyst application role, and a unique self signed TLS certificate, then writes them to the root only file /root/pg-duckdb-credentials.txt. Nothing is baked into the image. Until those secrets exist the database listens only on loopback, so an instance can never serve a routable port before it has its own credentials. Remote connections are then accepted only over TLS with scram-sha-256.

What is included:

  • PostgreSQL 17 from the official PostgreSQL PGDG repository, running under systemd as postgresql.service

  • pg_duckdb 1.1.1 compiled from the pinned upstream release together with its vendored DuckDB engine, loaded through shared_preload_libraries. The SQL extension object reports extversion 1.1.0, which is upstream's extension script version and is expected on the v1.1.1 release.

  • A database analyticsdb with an analytics schema, a 50,000 row orders table, and matching Parquet and CSV sample files under /var/lib/cloudimg/pg_duckdb-samples/

  • The duckdb_users role, which controls who may drive DuckDB execution, with the analyst application role already a member

  • Per VM passwords and a per VM TLS certificate, all generated on first boot and written to a root only credentials file

  • pg-duckdb-selfcheck, which verifies the whole appliance end to end including that queries really are executed by DuckDB

  • Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

  • Active Azure subscription, an SSH public key, and a VNet and subnet in the target region

  • Subscription to this listing on Azure Marketplace

  • A Network Security Group allowing TCP 22 for administration. Open TCP 5432 as well only if you need to reach the database from outside the VM. In production, restrict it to your client subnet.

Recommended virtual machine size: Standard_B2s with 2 vCPU and 4 GB RAM for development and evaluation. DuckDB is memory hungry by design, so for real analytical workloads choose a memory optimised size such as Standard_E2s_v5 or above and raise duckdb.max_memory to match, as described under Tuning below.

Deploy the virtual machine

Deploy from the Azure Portal by selecting the image from Azure Marketplace, choosing your VM size, and supplying your SSH public key for the azureuser account. Or deploy from the Azure CLI:

These commands run on your own workstation, not on the VM:

az vm create \
  --resource-group my-resource-group \
  --name my-pgduckdb-vm \
  --image <this-marketplace-image> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

az vm open-port --resource-group my-resource-group --name my-pgduckdb-vm --port 5432

Connect over SSH once the VM is running:

ssh azureuser@<vm-ip>

Retrieve your per VM credentials

First boot generates every secret for this VM and writes them to a root only file. Read it first, because the passwords are shown in plain text only here.

sudo cat /root/pg-duckdb-credentials.txt

The file records the connection URL, the database name, the postgres superuser password, the analyst application role password, and the paths of the bundled sample files.

The per VM credentials file and the pg-duckdb-selfcheck command reporting a healthy appliance

Verify the whole appliance end to end at any time with the bundled self check. It proves that pg_duckdb is preloaded, that your per VM password works over TLS while blank and well known passwords are refused, that a query really is executed by DuckDB, and that DuckDB can read the local Parquet and CSV files:

sudo pg-duckdb-selfcheck

Confirm the extension is live

PostgreSQL starts automatically on boot. Check the service, confirm that pg_duckdb is preloaded, and confirm the extension is created in the analytics database. The shared_preload_libraries line is the one that matters: without it the extension's planner hook is never installed and no query would ever reach DuckDB.

systemctl is-active postgresql
sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
sudo -u postgres psql -d analyticsdb -tAc "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_duckdb';"
sudo -u postgres psql -d analyticsdb -tAc "SELECT r['v'] FROM duckdb.query(\$\$SELECT version() AS v\$\$) r;"

Expected output

active
pg_duckdb
pg_duckdb|1.1.0
v1.4.3

PostgreSQL active under systemd with pg_duckdb preloaded and the embedded DuckDB engine reporting its version

Run your first analytical query through DuckDB

On the VM itself the unix socket needs no password, so sudo -u postgres psql -d analyticsdb drops you straight into an interactive session against the sample database. The examples below pass their SQL with -c so you can paste them as they are.

The analytics.orders table holds 50,000 rows of sample order data. Run an ordinary aggregate over it with duckdb.force_execution turned on and DuckDB executes it:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) AS orders, round(sum(amount), 2) AS revenue FROM analytics.orders GROUP BY region ORDER BY region;"

Expected output

SET
 region | orders |  revenue  
--------+--------+-----------
 amer   |  12500 | 892173.48
 apac   |  12500 | 892144.77
 emea   |  12500 | 892226.91
 latam  |  12500 | 892116.06
(4 rows)

Note that the SQL is completely ordinary. Nothing about it is DuckDB specific, and the same statement runs unchanged against a stock PostgreSQL.

Prove the query really ran on DuckDB

This is the check worth internalising, because it is the only one that distinguishes this image from a plain PostgreSQL. Ask for the plan. When DuckDB executes a query, the plan contains a Custom Scan (DuckDBScan) node:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"

Expected output

SET
          QUERY PLAN           
-------------------------------
 Custom Scan (DuckDBScan)
   DuckDB Execution Plan: 

 ┌───────────────────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │        ~31,606 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           region          │
 │           amount          │
 │                           │
 │        ~50,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │       Table: orders       │
 │                           │
 │        Projections:       │
 │           region          │
 │           amount          │
 │                           │
 │        ~50,000 rows       │
 └───────────────────────────┘


(35 rows)

Now run the identical query with DuckDB execution turned off, and the plan reverts to PostgreSQL's own executor:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = false;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"

Expected output

SET
        QUERY PLAN        
--------------------------
 HashAggregate
   Group Key: region
   ->  Seq Scan on orders
(3 rows)

The presence of DuckDBScan in the first plan and its absence in the second is the proof. Both plans return the same answers, which you can confirm for yourself:

sudo -u postgres psql -d analyticsdb -tAc "SET duckdb.force_execution = true; SELECT round(sum(amount), 2) FROM analytics.orders;"
sudo -u postgres psql -d analyticsdb -tAc "SET duckdb.force_execution = false; SELECT round(sum(amount), 2) FROM analytics.orders;"

Expected output

SET
3568661.22
SET
3568661.22

An EXPLAIN plan showing the Custom Scan DuckDBScan node with force execution on, and the ordinary PostgreSQL plan with it off

When you do not need force_execution. As soon as a query uses a DuckDB only feature, such as read_parquet(), DuckDB execution is selected automatically. duckdb.force_execution is only needed to push a query that touches nothing but PostgreSQL tables onto DuckDB.

Query Parquet and CSV files as tables

The image ships a Parquet file and a CSV file holding the same 50,000 rows, so you can exercise the data lake path immediately with no object storage and no credentials:

ls -lh /var/lib/cloudimg/pg_duckdb-samples/
sudo -u postgres psql -d analyticsdb -c "SELECT count(*) AS parquet_rows FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet');" -c "SELECT count(*) AS csv_rows FROM read_csv('/var/lib/cloudimg/pg_duckdb-samples/orders.csv');"

Expected output

total 2.1M
-rw-r--r-- 1 postgres postgres 1.9M Aug  7 02:48 orders.csv
-rw-r--r-- 1 postgres postgres 230K Aug  7 02:48 orders.parquet
 parquet_rows 
--------------
        50000
(1 row)

 csv_rows 
----------
    50000
(1 row)

Select columns out of a file with the r['column'] syntax, giving the function call a short alias:

sudo -u postgres psql -d analyticsdb -c "SELECT r['region'] AS region, count(*) AS orders, round(sum(r['amount']::numeric), 2) AS revenue FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') r GROUP BY r['region'] ORDER BY 1;"

Expected output

 region | orders |  revenue  
--------+--------+-----------
 amer   |  12500 | 892173.48
 apac   |  12500 | 892144.77
 emea   |  12500 | 892226.91
 latam  |  12500 | 892116.06
(4 rows)

Join a file against a database table

This is the mixed transactional and analytical shape the extension exists for: a live PostgreSQL table on one side, a data lake file on the other, joined in a single statement.

sudo -u postgres psql -d analyticsdb -c "SELECT p.region, p.db_orders, f.file_orders FROM (SELECT region, count(*) AS db_orders FROM analytics.orders GROUP BY region) p JOIN (SELECT r['region'] AS region, count(*) AS file_orders FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') r GROUP BY r['region']) f ON f.region = p.region ORDER BY 1;"

Expected output

 region | db_orders | file_orders 
--------+-----------+-------------
 amer   |     12500 |       12500
 apac   |     12500 |       12500
 emea   |     12500 |       12500
 latam  |     12500 |       12500
(4 rows)

DuckDB reading the bundled Parquet and CSV sample files and joining a file against a PostgreSQL table

Write query results back out to Parquet

The embedded engine writes files as well as reading them, which is how the bundled sample Parquet file was produced at build time:

sudo -u postgres psql -d analyticsdb -c "SELECT duckdb.raw_query(\$\$COPY (SELECT region, count(*) AS orders FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') GROUP BY region) TO '/tmp/region_summary.parquet' (FORMAT parquet)\$\$);" -c "SELECT * FROM read_parquet('/tmp/region_summary.parquet');"

Expected output

 raw_query 
-----------

(1 row)

NOTICE:  result: Count  
BIGINT  
[ Rows: 1]
4


 region | orders 
--------+--------
 emea   |  12500
 amer   |  12500
 apac   |  12500
 latam  |  12500
(4 rows)

Connect from your own applications

Any PostgreSQL client works, because this is a PostgreSQL server. From a remote host, connect over TLS with the per VM password from the credentials file. Replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file:

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=analyticsdb user=postgres sslmode=require" -c "SELECT now();"

For applications, prefer the analyst role rather than the superuser. It owns the analytics schema and is a member of duckdb_users, so it can drive DuckDB execution:

PGPASSWORD=<ANALYST_PASSWORD> psql "host=<vm-ip> port=5432 dbname=analyticsdb user=analyst sslmode=require" -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) FROM analytics.orders GROUP BY region ORDER BY 1;"

Remote connections are accepted only over TLS. A non TLS connection attempt is rejected by pg_hba.conf before any password is checked.

Connect object storage

Everything above needs no cloud account. When you are ready to point the engine at your own data lake, create a DuckDB secret from SQL and then read remote paths exactly as you read the local ones. Run this as postgres or another member of duckdb_users:

SELECT duckdb.create_simple_secret(
    type := 'S3', key_id := 'your_key', secret := 'your_secret', region := 'eu-west-2'
);

SELECT count(*) FROM read_parquet('s3://your-bucket/your-data.parquet');

Azure Blob Storage, Google Cloud Storage and Cloudflare R2 are supported the same way. Iceberg and Delta Lake are available by installing the matching DuckDB extension first, for example SELECT duckdb.install_extension('iceberg');.

Secrets created this way are visible to members of duckdb_users, so grant that role deliberately.

Tuning

The shipped configuration lives in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf.

Setting Shipped value Notes
shared_preload_libraries 'pg_duckdb' Required. Removing it disables the extension entirely.
duckdb.max_memory 1024 Megabytes of memory DuckDB may use per connection. DuckDB's own default is 80 percent of system RAM per connection, which would exhaust a small VM under any concurrency, so this image caps it. Raise it on a larger VM.
duckdb.postgres_role 'duckdb_users' Only members of this role, plus superusers, may drive DuckDB execution or manage secrets.
duckdb.force_execution false Session level. Set it per session or per role rather than globally unless every query on the instance is analytical.
duckdb.threads upstream default DuckDB threads per connection; the default is the CPU core count.

To make analytical execution the default for a specific role rather than typing SET every session:

sudo -u postgres psql -d analyticsdb -c "ALTER ROLE analyst SET duckdb.force_execution = true;"

Apply configuration file changes with sudo systemctl restart postgresql.

Security posture

  • No credential is baked into the image. The postgres superuser and the analyst role both ship with no password at all, and the build gate asserts that no role anywhere in the cluster carries one. Both passwords are generated on first boot, so no two VMs share a secret.

  • The database is loopback only until first boot completes. The shipped configuration pins listen_addresses to localhost with TLS off. First boot mints the per VM TLS certificate and both passwords, and only then opens the listener to the network. There is no window in which a routable port is served without this instance's own credentials behind it.

  • Remote access requires TLS. pg_hba.conf permits remote clients only through hostssl with scram-sha-256. A non TLS remote connection is refused before any password is checked, and there is no trust rule anywhere in the file.

  • DuckDB execution is role gated. duckdb.postgres_role restricts DuckDB execution and secret management to members of duckdb_users. The analyst role is deliberately not granted pg_read_server_files or pg_write_server_files, so DuckDB's local file system access stays disabled for it and reading files off the server's disk remains an explicit superuser action.

  • Restrict the Network Security Group. Leave port 5432 closed unless you need remote database access, and when you do open it, allow only the client subnets that need it.

  • Rotate the passwords when you need to. Run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file. The same applies to analyst.

Operations

Task Command
Service status systemctl status postgresql
Restart the database sudo systemctl restart postgresql
Database logs sudo tail -n 100 /var/log/postgresql/postgresql-17-main.log
Confirm the extension is preloaded sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
Embedded engine version sudo -u postgres psql -d analyticsdb -tAc "SELECT r['v'] FROM duckdb.query(\$\$SELECT version() AS v\$\$) r;"
Verify the appliance sudo pg-duckdb-selfcheck
Read per VM credentials sudo cat /root/pg-duckdb-credentials.txt
Local SQL prompt sudo -u postgres psql -d analyticsdb

Server components

Component Version Purpose
pg_duckdb 1.1.1 PostgreSQL extension embedding the DuckDB engine
DuckDB vendored with pg_duckdb 1.1.1 Columnar vectorised execution engine, with json, icu and httpfs built in
PostgreSQL 17 Relational database and storage engine, on port 5432
Ubuntu Server 24.04 LTS Base operating system

Key paths

Path Contents
/etc/postgresql/17/main/conf.d/10-pg_duckdb.conf pg_duckdb configuration including shared_preload_libraries
/etc/postgresql/17/main/pg_hba.conf Client authentication rules
/var/lib/postgresql/17/main/ Database data directory
/var/lib/cloudimg/pg_duckdb-samples/ Bundled Parquet and CSV sample files
/root/pg-duckdb-credentials.txt All per VM secrets, readable only by root
/usr/local/sbin/pg-duckdb-selfcheck End to end appliance verification

Troubleshooting

CREATE EXTENSION pg_duckdb fails. The extension must be preloaded at postmaster start. Check sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;' returns pg_duckdb. If it does not, confirm /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf is present and restart with sudo systemctl restart postgresql.

EXPLAIN shows no DuckDBScan node. Either duckdb.force_execution is not set in that session, or the connected role is not a member of duckdb_users. Check with SELECT pg_has_role(current_user, 'duckdb_users', 'member'); and grant it if needed.

A read_parquet() call on a local path returns a permission error. DuckDB's local file system is disabled for roles that are not superusers and not members of both pg_read_server_files and pg_write_server_files. Read local files as postgres, or grant those roles deliberately if an application genuinely needs server side file access.

Queries fail with an out of memory error. DuckDB's memory ceiling is per connection. Lower duckdb.max_memory in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf, or move to a memory optimised VM size and raise it.

The database is unreachable from outside the VM. Confirm the Network Security Group allows TCP 5432 from your client address, that sudo -u postgres psql -tAc 'SHOW listen_addresses;' returns *, and that your client is connecting with sslmode=require. If listen_addresses still reports localhost, first boot has not completed; inspect sudo journalctl -u pg-duckdb-firstboot -n 100 --no-pager and sudo cat /var/log/cloudimg-firstboot.log.

Advanced DuckDB types are rejected. STRUCT, MAP and UNION need a DuckDB execution context. Wrap those expressions in duckdb.query($$ ... $$) as described in the upstream types documentation.

Support

cloudimg provides 24/7 support for this image. Contact support@cloudimg.co.uk with your Azure subscription ID and the VM name. For pg_duckdb itself, the upstream documentation at github.com/duckdb/pg_duckdb covers the function reference, settings and data lake integration, and the DuckDB documentation covers its SQL dialect.