pg_duckdb

Overview

conflict with duckdb_fdw

Version

Build

You can build the RPM / DEB packages for pg_duckdb using pig build:

pig build pkg pg_duckdb         # build RPM / DEB packages

Install

You can install pg_duckdb directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_duckdb;          # Install for current active PG version

pig ext install -y pg_duckdb -v 18  # PG 18
pig ext install -y pg_duckdb -v 17  # PG 17
pig ext install -y pg_duckdb -v 16  # PG 16
pig ext install -y pg_duckdb -v 15  # PG 15
pig ext install -y pg_duckdb -v 14  # PG 14

dnf install -y pg_duckdb_18       # PG 18
dnf install -y pg_duckdb_17       # PG 17
dnf install -y pg_duckdb_16       # PG 16
dnf install -y pg_duckdb_15       # PG 15
dnf install -y pg_duckdb_14       # PG 14

apt install -y postgresql-18-pg-duckdb   # PG 18
apt install -y postgresql-17-pg-duckdb   # PG 17
apt install -y postgresql-16-pg-duckdb   # PG 16
apt install -y postgresql-15-pg-duckdb   # PG 15
apt install -y postgresql-14-pg-duckdb   # PG 14

Preload:

shared_preload_libraries = 'pg_duckdb';

Create Extension:

CREATE EXTENSION pg_duckdb;

Usage

pg_duckdb docs

Quick Setup

Install pg_duckdb with pig:

pig repo set
pig install pg_duckdb

Edit postgresql.conf, then restart to take effect

shared_preload_libraries = 'pg_duckdb'
duckdb.allow_community_extensions = true

Accelerate Query

You can use DuckDB to query existing PostgreSQL table without modifying them:

-- pgbench -is 1000  # init some test workloads with pgbench
CREATE EXTENSION pg_duckdb;

-- default behavior, common postgres engine
SET duckdb.force_execution = false;
EXPLAIN ANALYZE SELECT count(*) FROM pgbench_accounts;

-- now the query goes to pg_duckdb
SET duckdb.force_execution = true;
EXPLAIN ANALYZE SELECT count(*) FROM pgbench_accounts;

The result would be 8s -> 4s on 4c VM on local laptop) :

postgres@el9:5432/postgres=# SET duckdb.force_execution = true;
EXPLAIN ANALYZE SELECT count(*) FROM pgbench_accounts;
SET
Time: 0.206 ms
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT count(*) AS count FROM pgduckdb.public.pgbench_accounts
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 3.89s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │           Table:          │
 │      pgbench_accounts     │
 │                           │
 │      100,000,000 rows     │
 │          (3.88s)          │
 └───────────────────────────┘

Data Lake

Let’s play with a local minio instance:

SELECT duckdb.create_simple_secret(
    type := 'S3', key_id := 's3user_data', secret := 'S3User.Data',
    endpoint := 'https://sss.pigsty:9000', url_style := 'path' 
);