pg_duckdb
DuckDB Embedded in Postgres
Module:
Categories:
Overview
PIGSTY 3rd Party Extension: pg_duckdb : DuckDB Embedded in Postgres
Information
- Extension ID: 2430
- Extension Name:
pg_duckdb - Package Name:
pg_duckdb - Category:
OLAP - License: MIT
- Website: https://github.com/duckdb/pg_duckdb
- Language: C++
- Extra Tags:
duckdb - Comment:
Metadata
- Latest Version: 0.3.1
- Postgres Support:
17,16,15,14 - Need Load: Explicit Loading Required
- Need DDL: Need
CREATE EXTENSIONDDL - Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas: N/A
- Requires: N/A
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
pg_duckdb_$v* - RPM Ver :
0.3.1 - RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-pg-duckdb - DEB Ver :
0.3.1 - DEB Deps: N/A
Availability
Installation
Install pg_duckdb via the pig CLI tool:
pig ext install pg_duckdb
Install pg_duckdb via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_duckdb"]}' # -l <cls>
Install pg_duckdb RPM from YUM repo directly:
dnf install pg_duckdb_17*;
dnf install pg_duckdb_16*;
dnf install pg_duckdb_15*;
dnf install pg_duckdb_14*;
Install pg_duckdb DEB from APT repo directly:
apt install postgresql-17-pg-duckdb;
apt install postgresql-16-pg-duckdb;
apt install postgresql-15-pg-duckdb;
apt install postgresql-14-pg-duckdb;
Extension pg_duckdb has to be loaded via shared_preload_libraries
shared_preload_libraries = 'pg_duckdb'; # add to pg cluster config
Create pg_duckdb extension on PostgreSQL cluster:
CREATE EXTENSION pg_duckdb;
Usage
Add pg_duckdb to shared_preload_libraries via patronictl
pg edit-config --force -p shared_preload_libraries='pg_duckdb, pg_stat_statements, auto_explain'
pg restart --force pg-meta
Create Extension
CREATE EXTENSION pg_duckdb;
Generate some data
pgbench -is100
\timing on
SELECT count(*) FROM pgbench_accounts;
-- 3268.023ms
# use the duckdb execution engine
SET duckdb.force_execution = true;
postgres@el8:5432/postgres=# explain SELECT count(*) FROM pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------------------
Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0)
DuckDB Execution Plan:
┌───────────────────────────┐
│ UNGROUPED_AGGREGATE │
│ ──────────────────── │
│ Aggregates: │
│ count_star() │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ POSTGRES_SEQ_SCAN │
│ ──────────────────── │
│ Function: │
│ POSTGRES_SEQ_SCAN │
│ │
│ ~10000000 Rows │
└───────────────────────────┘
JIT:
Functions: 1
Options: Inlining false, Optimization false, Expressions true, Deforming true
(22 rows)
postgres@el8:5432/postgres=# SELECT count(*) FROM pgbench_accounts;
count
----------
10000000
(1 row)
Time: 696.801 ms
According some user feedbacks, the duckdb engine can achieve 100x - 1000x speed up on certain queries.
Check more details @ https://github.com/duckdb/pg_duckdb
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.