原 PG中OLAP类插件之pg_duckdb介绍，性能提升不止1000倍
Tags： 原创PGPostgreSQL插件OLAPPG插件pg_duckdb
👉 本文共约3852个字，系统预计阅读时间或需15分钟。
pg_duckdb简介
https://github.com/duckdb/pg_duckdb
https://docs.pgduckdb.com/quickstart
https://hub.docker.com/r/pgduckdb/pgduckdb
pg_duckdb 是一个扩展模块，旨在将 PostgreSQL 和 DuckDB 结合使用，提供了一种在 PostgreSQL 中访问 DuckDB 功能的途径。DuckDB 是一种面向分析工作负载优化的嵌入式列存储数据库，其特点是高性能、开箱即用且支持多种分析功能。
pg_duckdb 是一种强大的扩展，特别适合数据分析、外部数据集成以及需要高性能查询的场景。通过它，用户可以在 PostgreSQL 的生态中轻松引入 DuckDB 的能力，进一步扩展数据库的应用范围。
核心功能
- 嵌入式集成：pg_duckdb 在 PostgreSQL 中嵌入了 DuckDB 的功能，使用户可以利用 DuckDB 的列存分析引擎处理复杂的分析查询。
- 无缝访问：通过 pg_duckdb，用户可以在 PostgreSQL 中直接查询 DuckDB 表，而无需离开 PostgreSQL 环境。
- 高性能分析：DuckDB 优化了分析场景中的列式存储和向量化计算，pg_duckdb 可以充分利用这些特性。
- 多格式支持：DuckDB 支持查询多种外部数据格式（如 Parquet、CSV 等），通过 pg_duckdb，用户可以直接从这些格式中读取数据。
优势
- 提升查询性能：在分析场景中，利用 DuckDB 的列存优势，可以显著提升查询性能。
- 节省存储资源：DuckDB 的列存设计在处理高基数列时具有更高的压缩效率。
- 数据格式兼容性：支持直接加载和查询 Parquet、Arrow 等流行的数据格式，无需额外的转换。
使用场景
- 数据分析和聚合： pg_duckdb 适用于处理大量的分析查询，特别是涉及复杂聚合和多表联接的场景。
- 外部文件查询： 通过 pg_duckdb 可以直接查询 CSV、Parquet 等文件，而不需要将文件数据加载到 PostgreSQL 中。
- 加速 BI 工具查询： 对于与 BI 工具集成的 PostgreSQL 数据库，pg_duckdb 可以显著提高查询响应速度。
示例查询
1 2 3 4 5 6 7 8 9 10 | -- Remote Parquet scans: SELECT count(*) FROM read_parquet('https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet') AS (o_orderkey int); SELECT avg(c_acctbal) FROM read_parquet('https://shell.duckdb.org/data/tpch/0_01/parquet/customer.parquet') AS (c_acctbal float); SELECT count(*)::int as aws_service_cnt FROM read_parquet('https://raw.githubusercontent.com/tobilg/aws-iam-data/main/data/parquet/aws_services.parquet') AS (service_id int); SELECT code, city, state FROM read_parquet('https://raw.githubusercontent.com/tobilg/aws-edge-locations/main/data/aws-edge-locations.parquet') AS (code text, city text, state text); SELECT cloud_provider, sum(ip_address_cnt)::int as cnt FROM read_parquet('https://raw.githubusercontent.com/tobilg/public-cloud-provider-ip-ranges/main/data/providers/all.parquet') AS (cloud_provider text, ip_address_cnt int) GROUP BY cloud_provider; -- Remote CSV scan SELECT * FROM read_csv('https://raw.githubusercontent.com/tobilg/public-cloud-provider-ip-ranges/main/data/providers/all.csv') AS (cloud_provider text, cidr_block text, ip_address text) limit 30; |
pg_duckdb安装
docker安装使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | docker pull pgduckdb/pgduckdb:15-main docker pull pgduckdb/pgduckdb:16-main docker pull pgduckdb/pgduckdb:17-main docker run -d --name pgduckdb17 -h pgduckdb15 -p 5632:5432 -e POSTGRES_PASSWORD=lhr pgduckdb/pgduckdb:15-main docker run -d --name pgduckdb17 -h pgduckdb16 -p 5633:5432 -e POSTGRES_PASSWORD=lhr pgduckdb/pgduckdb:16-main docker run -d --name pgduckdb17 -h pgduckdb17 -p 5634:5432 -e POSTGRES_PASSWORD=lhr pgduckdb/pgduckdb:17-main psql postgres://postgres:lhr@127.0.0.1:5634/postgres # Or if using docker compose git clone https://github.com/duckdb/pg_duckdb && cd pg_duckdb && docker compose up -d docker compose exec db psql |
编译安装
To build pg_duckdb, you need:
- Postgres 15-17
- Ubuntu 22.04-24.04 or MacOS
- Standard set of build tools for building Postgres extensions
- Build tools that are required to build DuckDB ：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | sudo yum install -y git g++ cmake ninja-build openssl-devel -- CMake 3.5...3.29 or higher is required git clone https://github.com/duckdb/pg_duckdb cd pg_duckdb make install -j 16 -- 主要文件 /pg17/pg17/lib/postgresql/pg_duckdb.so /pg17/pg17/lib/postgresql/libduckdb.so /pg17/pg17/share/postgresql/extension/pg_duckdb--0.1.0--0.2.0.sql /pg17/pg17/share/postgresql/extension/pg_duckdb--0.1.0.sql /pg17/pg17/share/postgresql/extension/pg_duckdb.control alter system set shared_preload_libraries = 'pg_duckdb'; CREATE EXTENSION pg_duckdb; SET duckdb.force_execution = true; -- Ubuntu 安装 curl -sfL https://install.pgx.sh | sh - pgxman install pg_duckdb shared_preload_libraries = 'pg_duckdb' CREATE EXTENSION pg_duckdb; SET duckdb.force_execution = true; |
pg_duckdb涉及到的参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | postgres=# \dconfig duckdb* List of configuration parameters Parameter | Value --------------------------------------+----------------- duckdb.allow_unsigned_extensions | off duckdb.disabled_filesystems | LocalFileSystem duckdb.enable_external_access | on duckdb.force_execution | off duckdb.max_memory | 4GB duckdb.max_threads_per_postgres_scan | 1 duckdb.memory_limit | 4GB duckdb.motherduck_enabled | auto duckdb.motherduck_postgres_database | postgres duckdb.motherduck_token | duckdb.postgres_role | duckdb.threads | -1 duckdb.worker_threads | -1 (13 rows) postgres=# \dconfig+ duckdb* List of configuration parameters Parameter | Value | Type | Context | Access privileges --------------------------------------+-----------------+---------+------------+------------------- duckdb.allow_unsigned_extensions | off | bool | superuser | duckdb.disabled_filesystems | LocalFileSystem | string | superuser | duckdb.enable_external_access | on | bool | superuser | duckdb.force_execution | off | bool | user | duckdb.max_memory | 4GB | string | superuser | duckdb.max_threads_per_postgres_scan | 1 | integer | user | duckdb.memory_limit | 4GB | string | superuser | duckdb.motherduck_enabled | auto | enum | postmaster | duckdb.motherduck_postgres_database | postgres | string | user | duckdb.motherduck_token | | string | postmaster | duckdb.postgres_role | | string | postmaster | duckdb.threads | -1 | integer | superuser | duckdb.worker_threads | -1 | integer | superuser | (13 rows) |
每个参数及其含义：
| 参数名 | 当前值 | 含义 |
|---|---|---|
duckdb.allow_unsigned_extensions | off | 是否允许加载未签名的 DuckDB 扩展，默认关闭以增强安全性。 |
duckdb.disabled_filesystems | LocalFileSystem | 禁用的文件系统类型，本地文件系统被禁用可能用于限制文件访问。 |
duckdb.enable_external_access | on | 是否允许访问外部资源（文件系统、网络），启用时可能存在安全风险。 |
duckdb.force_execution | off | 是否强制执行查询计划，关闭时允许优化器调整执行计划以提高性能。 |
duckdb.max_memory | 4GB | DuckDB 可用的最大内存量，默认限制为 4GB。 |
duckdb.max_threads_per_postgres_scan | 1 | 每个 PostgreSQL 扫描任务的最大线程数，默认单线程执行。 |
duckdb.memory_limit | 4GB | 内存使用的硬限制，与 duckdb.max_memory 类似。 |
duckdb.motherduck_enabled | auto | 是否启用与 MotherDuck 集成，auto 表示根据需要动态启用。 |
duckdb.motherduck_postgres_database | postgres | 使用 MotherDuck 集成时，连接的 PostgreSQL 数据库名称。 |
duckdb.motherduck_token | 空 | 用于 MotherDuck 集成的身份验证令牌，为空时无法访问 MotherDuck。 |
duckdb.postgres_role | 空 | 集成 PostgreSQL 时使用的角色，为空表示未配置特定角色。 |
duckdb.threads | -1 | 查询执行时的最大线程数，-1 表示自动检测并使用所有可用线程。 |
duckdb.worker_threads | -1 | 用于并行任务的工作线程数量，-1 表示自动分配线程数量。 |
如果需要对某些参数进行调整或优化，建议根据具体使用场景评估参数设置。
大表测试性能
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 1. 安装duckdb、生成数据、导出数据 wget https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip unzip duckdb_cli-linux-amd64.zip ./duckdb install tpcds; load tpcds; -- 生成规模因子为1的测试数据，即1GB左右数据 call dsdgen(sf=1); -- 导出数据为csv格式，并且用“|”分隔 export database 'public' (format csv, delimiter '|'); .quit 2. 导入数据到PG数据库中 sed -i 's/COPY/\\copy/' public/load.sql createdb testduckdb psql -d testduckdb -f public/schema.sql psql -d testduckdb -f public/load.sql testduckdb=# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------------+-------+----------+-------------+---------------+------------+------------- public | call_center | table | postgres | permanent | heap | 16 kB | public | catalog_page | table | postgres | permanent | heap | 1976 kB | public | catalog_returns | table | postgres | permanent | heap | 23 MB | public | catalog_sales | table | postgres | permanent | heap | 293 MB | public | customer | table | postgres | permanent | heap | 15 MB | public | customer_address | table | postgres | permanent | heap | 7400 kB | public | customer_demographics | table | postgres | permanent | heap | 139 MB | public | date_dim | table | postgres | permanent | heap | 11 MB | public | household_demographics | table | postgres | permanent | heap | 440 kB | public | income_band | table | postgres | permanent | heap | 8192 bytes | public | inventory | table | postgres | permanent | heap | 496 MB | public | item | table | postgres | permanent | heap | 5800 kB | public | promotion | table | postgres | permanent | heap | 80 kB | public | reason | table | postgres | permanent | heap | 16 kB | public | ship_mode | table | postgres | permanent | heap | 16 kB | public | store | table | postgres | permanent | heap | 16 kB | public | store_returns | table | postgres | permanent | heap | 38 MB | public | store_sales | table | postgres | permanent | heap | 405 MB | public | time_dim | table | postgres | permanent | heap | 8424 kB | public | warehouse | table | postgres | permanent | heap | 16 kB | public | web_page | table | postgres | permanent | heap | 16 kB | public | web_returns | table | postgres | permanent | heap | 11 MB | public | web_sales | table | postgres | permanent | heap | 147 MB | public | web_site | table | postgres | permanent | heap | 48 kB | (24 rows) |
做查询操作，测试性能：
不使用pg_duckdb插件的情况下：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
