跳转到主要内容

pg_duckdb

DuckDB Embedded in Postgres

pg_duckdb : DuckDB Embedded in Postgres

Overview

ID Extension Package Version Category License Language
2430 pg_duckdb pg_duckdb 1.1.1 OLAP MIT C++
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd-- No Yes Yes Yes no no
Relationships
Schemas public
Need By pg_mooncake
See Also pg_mooncake pg_ducklake duckdb_fdw pg_parquet pg_lake pg_clickhouse aws_s3 file_fdw pg_bulkload columnar
conflict with duckdb_fdw

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 1.1.1 18 17 16 15 14 pg_duckdb -
RPM PIGSTY 1.1.1 18 17 16 15 14 pg_duckdb_$v -
DEB PIGSTY 1.1.1 18 17 16 15 14 postgresql-$v-pg-duckdb -
Linux / PG PG18 PG17 PG16 PG15 PG14
el8.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
el8.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
el9.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
el9.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
el10.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
el10.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
d12.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
d12.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
d13.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
d13.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u22.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u22.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u24.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u24.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u26.x86_64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
u26.aarch64 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1 PIGSTY 1.1.1
Package Version OS ORG SIZE File URL
pg_duckdb_18 1.1.1 el8.x86_64 pigsty 15.4 MiB pg_duckdb_18-1.1.1-1PIGSTY.el8.x86_64.rpm
pg_duckdb_18 1.1.1 el8.aarch64 pigsty 13.3 MiB pg_duckdb_18-1.1.1-1PIGSTY.el8.aarch64.rpm
pg_duckdb_18 1.1.1 el9.x86_64 pigsty 15.8 MiB pg_duckdb_18-1.1.1-1PIGSTY.el9.x86_64.rpm
pg_duckdb_18 1.1.1 el9.aarch64 pigsty 14.2 MiB pg_duckdb_18-1.1.1-1PIGSTY.el9.aarch64.rpm
pg_duckdb_18 1.1.1 el10.x86_64 pigsty 15.1 MiB pg_duckdb_18-1.1.1-1PIGSTY.el10.x86_64.rpm
pg_duckdb_18 1.1.1 el10.aarch64 pigsty 13.4 MiB pg_duckdb_18-1.1.1-1PIGSTY.el10.aarch64.rpm
postgresql-18-pg-duckdb 1.1.1 d12.x86_64 pigsty 13.9 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~bookworm_amd64.deb
postgresql-18-pg-duckdb 1.1.1 d12.aarch64 pigsty 12.0 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~bookworm_arm64.deb
postgresql-18-pg-duckdb 1.1.1 d13.x86_64 pigsty 14.3 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~trixie_amd64.deb
postgresql-18-pg-duckdb 1.1.1 d13.aarch64 pigsty 12.2 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~trixie_arm64.deb
postgresql-18-pg-duckdb 1.1.1 u22.x86_64 pigsty 15.6 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~jammy_amd64.deb
postgresql-18-pg-duckdb 1.1.1 u22.aarch64 pigsty 14.2 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~jammy_arm64.deb
postgresql-18-pg-duckdb 1.1.1 u24.x86_64 pigsty 15.8 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~noble_amd64.deb
postgresql-18-pg-duckdb 1.1.1 u24.aarch64 pigsty 14.5 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~noble_arm64.deb
postgresql-18-pg-duckdb 1.1.1 u26.x86_64 pigsty 16.3 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~resolute_amd64.deb
postgresql-18-pg-duckdb 1.1.1 u26.aarch64 pigsty 15.2 MiB postgresql-18-pg-duckdb_1.1.1-1PIGSTY~resolute_arm64.deb
Package Version OS ORG SIZE File URL
pg_duckdb_17 1.1.1 el8.x86_64 pigsty 15.4 MiB pg_duckdb_17-1.1.1-1PIGSTY.el8.x86_64.rpm
pg_duckdb_17 1.1.1 el8.aarch64 pigsty 13.3 MiB pg_duckdb_17-1.1.1-1PIGSTY.el8.aarch64.rpm
pg_duckdb_17 1.1.1 el9.x86_64 pigsty 15.8 MiB pg_duckdb_17-1.1.1-1PIGSTY.el9.x86_64.rpm
pg_duckdb_17 1.1.1 el9.aarch64 pigsty 14.2 MiB pg_duckdb_17-1.1.1-1PIGSTY.el9.aarch64.rpm
pg_duckdb_17 1.1.1 el10.x86_64 pigsty 15.1 MiB pg_duckdb_17-1.1.1-1PIGSTY.el10.x86_64.rpm
pg_duckdb_17 1.1.1 el10.aarch64 pigsty 13.4 MiB pg_duckdb_17-1.1.1-1PIGSTY.el10.aarch64.rpm
postgresql-17-pg-duckdb 1.1.1 d12.x86_64 pigsty 13.9 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~bookworm_amd64.deb
postgresql-17-pg-duckdb 1.1.1 d12.aarch64 pigsty 12.0 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~bookworm_arm64.deb
postgresql-17-pg-duckdb 1.1.1 d13.x86_64 pigsty 14.3 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~trixie_amd64.deb
postgresql-17-pg-duckdb 1.1.1 d13.aarch64 pigsty 12.2 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~trixie_arm64.deb
postgresql-17-pg-duckdb 1.1.1 u22.x86_64 pigsty 16.0 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~jammy_amd64.deb
postgresql-17-pg-duckdb 1.1.1 u22.aarch64 pigsty 14.6 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~jammy_arm64.deb
postgresql-17-pg-duckdb 1.1.1 u24.x86_64 pigsty 15.9 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~noble_amd64.deb
postgresql-17-pg-duckdb 1.1.1 u24.aarch64 pigsty 14.5 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~noble_arm64.deb
postgresql-17-pg-duckdb 1.1.1 u26.x86_64 pigsty 16.3 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~resolute_amd64.deb
postgresql-17-pg-duckdb 1.1.1 u26.aarch64 pigsty 15.2 MiB postgresql-17-pg-duckdb_1.1.1-1PIGSTY~resolute_arm64.deb
Package Version OS ORG SIZE File URL
pg_duckdb_16 1.1.1 el8.x86_64 pigsty 15.4 MiB pg_duckdb_16-1.1.1-1PIGSTY.el8.x86_64.rpm
pg_duckdb_16 1.1.1 el8.aarch64 pigsty 13.3 MiB pg_duckdb_16-1.1.1-1PIGSTY.el8.aarch64.rpm
pg_duckdb_16 1.1.1 el9.x86_64 pigsty 15.8 MiB pg_duckdb_16-1.1.1-1PIGSTY.el9.x86_64.rpm
pg_duckdb_16 1.1.1 el9.aarch64 pigsty 14.2 MiB pg_duckdb_16-1.1.1-1PIGSTY.el9.aarch64.rpm
pg_duckdb_16 1.1.1 el10.x86_64 pigsty 15.1 MiB pg_duckdb_16-1.1.1-1PIGSTY.el10.x86_64.rpm
pg_duckdb_16 1.1.1 el10.aarch64 pigsty 13.4 MiB pg_duckdb_16-1.1.1-1PIGSTY.el10.aarch64.rpm
postgresql-16-pg-duckdb 1.1.1 d12.x86_64 pigsty 13.9 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~bookworm_amd64.deb
postgresql-16-pg-duckdb 1.1.1 d12.aarch64 pigsty 12.0 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~bookworm_arm64.deb
postgresql-16-pg-duckdb 1.1.1 d13.x86_64 pigsty 14.3 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~trixie_amd64.deb
postgresql-16-pg-duckdb 1.1.1 d13.aarch64 pigsty 12.2 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~trixie_arm64.deb
postgresql-16-pg-duckdb 1.1.1 u22.x86_64 pigsty 16.0 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~jammy_amd64.deb
postgresql-16-pg-duckdb 1.1.1 u22.aarch64 pigsty 14.6 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~jammy_arm64.deb
postgresql-16-pg-duckdb 1.1.1 u24.x86_64 pigsty 15.8 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~noble_amd64.deb
postgresql-16-pg-duckdb 1.1.1 u24.aarch64 pigsty 14.5 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~noble_arm64.deb
postgresql-16-pg-duckdb 1.1.1 u26.x86_64 pigsty 16.3 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~resolute_amd64.deb
postgresql-16-pg-duckdb 1.1.1 u26.aarch64 pigsty 15.1 MiB postgresql-16-pg-duckdb_1.1.1-1PIGSTY~resolute_arm64.deb
Package Version OS ORG SIZE File URL
pg_duckdb_15 1.1.1 el8.x86_64 pigsty 15.4 MiB pg_duckdb_15-1.1.1-1PIGSTY.el8.x86_64.rpm
pg_duckdb_15 1.1.1 el8.aarch64 pigsty 13.3 MiB pg_duckdb_15-1.1.1-1PIGSTY.el8.aarch64.rpm
pg_duckdb_15 1.1.1 el9.x86_64 pigsty 15.8 MiB pg_duckdb_15-1.1.1-1PIGSTY.el9.x86_64.rpm
pg_duckdb_15 1.1.1 el9.aarch64 pigsty 14.2 MiB pg_duckdb_15-1.1.1-1PIGSTY.el9.aarch64.rpm
pg_duckdb_15 1.1.1 el10.x86_64 pigsty 15.1 MiB pg_duckdb_15-1.1.1-1PIGSTY.el10.x86_64.rpm
pg_duckdb_15 1.1.1 el10.aarch64 pigsty 13.5 MiB pg_duckdb_15-1.1.1-1PIGSTY.el10.aarch64.rpm
postgresql-15-pg-duckdb 1.1.1 d12.x86_64 pigsty 13.9 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~bookworm_amd64.deb
postgresql-15-pg-duckdb 1.1.1 d12.aarch64 pigsty 12.0 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~bookworm_arm64.deb
postgresql-15-pg-duckdb 1.1.1 d13.x86_64 pigsty 14.3 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~trixie_amd64.deb
postgresql-15-pg-duckdb 1.1.1 d13.aarch64 pigsty 12.2 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~trixie_arm64.deb
postgresql-15-pg-duckdb 1.1.1 u22.x86_64 pigsty 16.0 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~jammy_amd64.deb
postgresql-15-pg-duckdb 1.1.1 u22.aarch64 pigsty 14.6 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~jammy_arm64.deb
postgresql-15-pg-duckdb 1.1.1 u24.x86_64 pigsty 15.8 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~noble_amd64.deb
postgresql-15-pg-duckdb 1.1.1 u24.aarch64 pigsty 14.5 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~noble_arm64.deb
postgresql-15-pg-duckdb 1.1.1 u26.x86_64 pigsty 16.3 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~resolute_amd64.deb
postgresql-15-pg-duckdb 1.1.1 u26.aarch64 pigsty 15.2 MiB postgresql-15-pg-duckdb_1.1.1-1PIGSTY~resolute_arm64.deb
Package Version OS ORG SIZE File URL
pg_duckdb_14 1.1.1 el8.x86_64 pigsty 15.4 MiB pg_duckdb_14-1.1.1-1PIGSTY.el8.x86_64.rpm
pg_duckdb_14 1.1.1 el8.aarch64 pigsty 13.3 MiB pg_duckdb_14-1.1.1-1PIGSTY.el8.aarch64.rpm
pg_duckdb_14 1.1.1 el9.x86_64 pigsty 15.8 MiB pg_duckdb_14-1.1.1-1PIGSTY.el9.x86_64.rpm
pg_duckdb_14 1.1.1 el9.aarch64 pigsty 14.2 MiB pg_duckdb_14-1.1.1-1PIGSTY.el9.aarch64.rpm
pg_duckdb_14 1.1.1 el10.x86_64 pigsty 15.1 MiB pg_duckdb_14-1.1.1-1PIGSTY.el10.x86_64.rpm
pg_duckdb_14 1.1.1 el10.aarch64 pigsty 13.5 MiB pg_duckdb_14-1.1.1-1PIGSTY.el10.aarch64.rpm
postgresql-14-pg-duckdb 1.1.1 d12.x86_64 pigsty 13.9 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~bookworm_amd64.deb
postgresql-14-pg-duckdb 1.1.1 d12.aarch64 pigsty 12.0 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~bookworm_arm64.deb
postgresql-14-pg-duckdb 1.1.1 d13.x86_64 pigsty 14.3 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~trixie_amd64.deb
postgresql-14-pg-duckdb 1.1.1 d13.aarch64 pigsty 12.2 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~trixie_arm64.deb
postgresql-14-pg-duckdb 1.1.1 u22.x86_64 pigsty 16.0 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~jammy_amd64.deb
postgresql-14-pg-duckdb 1.1.1 u22.aarch64 pigsty 14.6 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~jammy_arm64.deb
postgresql-14-pg-duckdb 1.1.1 u24.x86_64 pigsty 15.9 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~noble_amd64.deb
postgresql-14-pg-duckdb 1.1.1 u24.aarch64 pigsty 14.5 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~noble_arm64.deb
postgresql-14-pg-duckdb 1.1.1 u26.x86_64 pigsty 16.3 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~resolute_amd64.deb
postgresql-14-pg-duckdb 1.1.1 u26.aarch64 pigsty 15.2 MiB postgresql-14-pg-duckdb_1.1.1-1PIGSTY~resolute_arm64.deb

Source 

pig build pkg pg_duckdb;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_duckdb;		# install via package name, for the active PG version

pig install pg_duckdb -v 18;   # install for PG 18
pig install pg_duckdb -v 17;   # install for PG 17
pig install pg_duckdb -v 16;   # install for PG 16
pig install pg_duckdb -v 15;   # install for PG 15
pig install pg_duckdb -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_duckdb';

Create this extension with:

CREATE EXTENSION pg_duckdb;

Usage

pg_duckdb docs

Topic Description
Functions Complete reference for all available functions
Syntax Guide & Gotchas Quick reference for common SQL patterns and things to know
Types Supported data types and type mappings
Extensions DuckDB extension installation and usage
Settings Configuration options and parameters
Transactions Transaction behavior and limitations

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' 
);