在数据管理领域，PostgreSQL（pg）作为一款出色的在线事务处理（OLTP）数据库，凭借其强大的事务处理能力和可靠性，赢得了广泛的认可。然而，当面对大规模数据分析时，pg 有时显得局促，响应速度也未能满足高效分析的需求。

近一段时间来，DuckDB 作为一款高效的列式数据库引擎，正在数据分析领域引起越来越多的关注。其轻量级的设计和强大的查询性能，使其在处理大规模数据集时展现出优异的表现，DuckDB 可以为数据湖(Data Lake)或湖屋(Lakehouse)架构带来了快速、便捷的分析能力，也是推动数据分析向更高效、灵活的方向发展的重要组件。

在这种情况下，pg_duckdb 作为一项创新的扩展，为 PostgreSQL 注入了新的活力，仿佛为这头大象插上了翅膀。通过将 DuckDB 的分析引擎与 PostgreSQL 深度集成，pg_duckdb 提供了一种便捷、简单且高效的数据分析架构，使用户能够快速执行复杂的分析查询，充分挖掘数据的潜力。

pg_duckdb 是一个 PostgreSQL 扩展，将 DuckDB 的分析引擎直接集成到 PostgreSQL 中，允许在传统事务工作负载上支持快速进行数据分析查询。接下来的内容我们快速体验一下,感受高效的数据分析能力.

镜像安装带有pg_duckdb的PG

最简单的入门方法是使用提供的 Docker 镜像，该镜像包含了最新版本的 pg_duckdb 扩展预安装的 PostgreSQL。详细参见:https://github.com/duckdb/pg_duckdb ; 为了便于测试,推了镜像到阿里云的ACR上,下面命令创建测试实例容器:

docker run -d --name pg_duckdb -p 54322:5432 -e POSTGRES_HOST_AUTH_METHOD=trust registry.cn-hangzhou.aliyuncs.com/smartnotebook/pg_duckdb:17-v0.1.0

在psql 输入:SELECT * FROM pg_extension; 可看到pg 的扩展项包括：gp_duckdb .

PostgreSQL 是一款事务性数据库，而非分析性数据库。它非常适合查找、小规模更新以及在仔细设置索引和连接关系后运行查询。然而，当需要在整个数据集上运行临时分析查询时，它并不是最佳选择。

尽管 PostgreSQL 并不是专门为分析设计的，但它常常被用于分析，因为数据随时可用，便于启动。然而，随着数据量的增加，以及对更复杂的聚合和分组分析查询的需求，用户常常会遇到限制。这时，像 DuckDB 这样的分析数据库引擎便能派上用场。

通过 pg_duckdb，你可以在 PostgreSQL 中使用 DuckDB 执行引擎处理已存储的数据，对于某些查询，这可能会显著提升性能。以下是一个查询示例，显示了显著的性能提升；

在带有pg_duckdb的PG上测试TPC-DS 用例1

让我们尝试 TPC-DS 基准测试套件中的第一个查询(用例1)，该查询包含在 TPC-DS DuckDB 扩展中。详细参见：《使用SNB 进行DuckDB的TPC-DS 测试:性能强悍》

使用该扩展，在duckdb 使用规模因子 1（即总数据为 1GB左右）生成 TPC-DS 数据集,然后导出再加载到没有索引的 PostgreSQL 中。下面代码是smarnotebook 执行dfSQL（duckdb 引擎）或在duckdb 内执行SQL生成tpcds的测试数据集。

INSTALL tpcds;
LOAD tpcds;
CALL dsdgen(sf = 1);
EXPORT DATABASE 'public' (FORMAT CSV, DELIMITER '|');
-- PRAGMA tpcds(1);

导出后的数据文件和SQL 脚本如下:

将数据迁移到带有pg_duckdb插件的PG 容器中，执行下面的指令，就可以tpcds 数据集加载到pg数据库中。

export schema_name=public
sed 's/COPY/\\copy/' "$schema_name/load.sql" >"$schema_name/load-psql.sql"
psql -v ON_ERROR_STOP=1 "options=--search-path=$schema_name" -c "CREATE SCHEMA IF NOT EXISTS $schema_name" -f "$schema_name/schema.sql" -f "$schema_name/load-psql.sql" -c "ANALYZE;"

加载收据后,执行用例1 的SQL, 用例SQL参见:https://github.com/duckdb/duckdb/tree/main/extension/tpcds/dsdgen/queries ; 下面在smartnotebook 内创建数据源连接,并分别以pg 和pg_duckdb 内核执行（设置参数SET duckdb.force_execution = True | False）:

SET duckdb.force_execution = True;
WITH customer_total_return AS
  (SELECT sr_customer_sk AS ctr_customer_sk,
          sr_store_sk AS ctr_store_sk,
          sum(sr_return_amt) AS ctr_total_return
   FROM store_returns,
        date_dim
   WHERE sr_returned_date_sk = d_date_sk
     AND d_year = 2000
   GROUP BY sr_customer_sk,
            sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1,
     store,
     customer
WHERE ctr1.ctr_total_return >
    (SELECT avg(ctr_total_return)*1.2
     FROM customer_total_return ctr2
     WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'TN'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 5;

SET duckdb.force_execution = False 的情况下(使用PG 自有的方式)执行结果如下,执行时间为250秒。

SET duckdb.force_execution = True 的情况下(使用gp_duckdb的duckdb 内核)执行结果如下,执行时间为0.3秒。

性能足足提升1000倍。

在数据湖(Data Lake)或湖屋(Lakehouse)中使用 pg_duckdb

DuckDB 原生支持在外部对象存储上读取和写入文件，因此非常适合用于查询数据湖中的数据。DuckDB 还可以从 Iceberg 和 Delta 中读取数据，让你能够利用湖屋架构。以下测试代码片段使用了来自公共桶的数据集。

分析 Parquet 文件

以下查询使用 pg_duckdb 查询存储在 S3 中的 Parquet 文件，以找出 2020-2022 年美国最受欢迎的电视节目。

SELECT Title, max("Days In Top 10")::int as MaxDaysInTop10
FROM read_parquet('s3://duckdb-md-dataset-121/netflix_daily_top_10.parquet') 
    AS ("Days In Top 10" varchar, Title varchar, Type varchar) 
WHERE Type = 'TV Show'
GROUP BY Title
ORDER BY MaxDaysInTop10 DESC
LIMIT 5;

分析 Iceberg 表

为了查询 Iceberg 中的数据，你首先需要安装 DuckDB Iceberg 扩展。在 pg_duckdb 中，安装 duckdb 扩展可以使用 duckdb.install_extension(<扩展名称>) 函数。

-- Install the iceberg extension
SELECT duckdb.install_extension('iceberg');
-- Total quantity of items ordered for each `l_shipmode`
SELECT l_shipmode, SUM(l_quantity) AS total_quantity 
FROM iceberg_scan('s3://us-prd-motherduck-open-datasets/iceberg/lineitem_iceberg', allow_moved_paths := true)
    AS l(l_shipmode varchar, l_quantity int)
GROUP BY l_shipmode 
ORDER BY total_quantity DESC;

写回数据湖(Data Lake)或湖屋(Lakehouse)

在 pg_duckdb 中，对数据湖的访问不仅限于只读操作，你还可以使用 COPY 命令进行写回。请注意，你可以混合使用原生 PostgreSQL 数据，因此可以利用此功能将数据从 PostgreSQL 表导出到外部数据湖存储。

COPY (
  SELECT Title, max("Days In Top 10")::int AS MaxDaysInTop10
  FROM read_parquet('s3://us-prd-motherduck-open-datasets/netflix/netflix_daily_top_10.parquet') 
    AS ("Days In Top 10" varchar, Title varchar, Type varchar) 
  WHERE Type = 'TV Show'
  GROUP BY Title
  ORDER BY MaxDaysInTop10 DESC
  LIMIT 5
) TO '/home/results.parquet';

pg_duckdb 虽然处于测试阶段，未来的发展备受期待。DuckDB 的成功源于其简单性，这一优势将直接带给 PostgreSQL 用户，使其在现有数据库中发挥分析的作用和价值。