pg_duckdb の性能測定をやってみた (前編)

この記事は [PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql) 11日目の記事です。

# はじめに

もう年末ですね。1年は早いですね。

日本のテレコム会社で PostgreSQL のコミュニティ開発やサポートを担当している 山田 です。PostgreSQL の本体開発では [コントリビュータ](https://www.postgresql.org/community/contributors/) として、拡張機能 [pg_hint_plan](https://github.com/ossc-db/pg_hint_plan) においてはコミッタの一人として貢献しています。

今年を振り返ると、私のハイライトの1つは [PGConf.Dev 2024](https://2024.pgconf.dev/) での講演でした。
PGConf.Dev は PostgreSQL開発者向けカンファレンスであった PGCon の後継となるカンファレンスです (2024年が第一回目)。

海外での講演は毎回苦労していますが、コミュニティに少しでも良いものを残せるように今回は機能改善すべき点について提案してきました。 講演後にはコミッタ含め聴講者の数人から great talk! と言ってもらえたので、一部の方には響いたのだろうと思っています。
特に、PostgreSQLを触り始めた頃に頻繁に見ていた PostgreSQLに関する [blog](https://www.depesz.com/) や [Explain の可視化](https://explain.depesz.com/)で有名な **depsez** さんに興味を持ってもらい質問してもらえたことは嬉しかったです。

ここからは本題に入っていきます

私のアドベントカレンダーでは毎年以下のようなネタ的な記事[^1][^2][^3]を書いてきましたが、今年は良いアイデアが思いつきませんでした。

[^1]: [スマホとタブレットでロジカルレプリケーションを試してみた](https://qiita.com/yamatattsu/items/c7924834116ed5b12f66)
[^2]: [PostgreSQL でムービーを再生してみた](https://qiita.com/yamatattsu/items/06c797a032b633a9fafa)
[^3]: [PostgreSQLでファイヤエフェクト！](https://qiita.com/yamatattsu/items/9c2ed8078dea4c3c9c84)

そこで、最近興味がある [pg_duckdb](https://github.com/duckdb/pg_duckdb) について書いてみることにしました。 [duckdb](https://github.com/duckdb/duckdb) 自体は台湾で知り合った duckdb 開発者から話を聞いた際に「そんなに速いの？」と疑い、ベンチマークテストしようと考えておりました。 そんな中、丁度良いタイミングで [pg_duckdb](https://github.com/duckdb/pg_duckdb) という PostgreSQL の拡張機能が公開されたため、性能測定を行ってみることにしました。
なお、duckdb と pg_duckdb については初めて触っています。もし誤りやアドバイスがあれば教えていただけると助かります。

# pg_duckdb とは

PostgreSQL の拡張機能で duckdb のクエリ処理のエンジンを利用可能とし、OLAP用のクエリを高速に処理できる (らしい)。
詳しくは下記リポジトリや @yohei1126 さんの[アドベントカレンダーの記事](https://zenn.dev/yohei/articles/2024-11-09-pg-duckdb)を参照 (Thanks!)。

- リポジトリ
	- https://github.com/duckdb/pg_duckdb
- バージョン
	- v0.1.0

# pg_duckdb を試してみる

:::note
前提
- PostgreSQLはインストール済
- 拡張機能のビルドが出来る環境であること
:::

## 環境

- Rocky Linux 9.4
- PostgreSQL 16.6
- duckdb 1.1.2
- pg_duckdb 0.1.0

## duckdb のインストール

duckdb の tpc-ds 拡張機能 が持つスキーマやクエリを測定時に利用するために duckdb をインストールしておく。ビルドが完了したら、duckdb のバイナリにパスを通しておく。


```
-- リポジトリのクローンとビルド
yum install -y git g++ cmake ninja-build openssl-devel
git clone https://github.com/duckdb/duckdb
git checkout v1.1.3
GEN=ninja make

-- バイナリが出来たか確認
ls -l build/release/duckdb

-- 環境変数 PATH に追加する
省略
```


バージョンを確認してみると、
```
duckdb --version
v1.1.3 19864453f7
```
ちなみに、この198～という番号はコミット番号でした

参考:
[https://duckdb.org/docs/dev/building/build_instructions.html](https://duckdb.org/docs/dev/building/build_instructions.html)

## pg_duckdb のインストール

次の手順でビルドしインストール

```
-- リポジトリを clone し、make install
yum install -y git g++ cmake ninja-build openssl-devel
git clone https://github.com/duckdb/pg_duckdb.git
git checkout v0.1.0
cd pg_duckdb
make install

-- postgresql.confの編集し、shared_preload_libraries に pg_duckdbを追加
vi postgresql.conf
====
shared_preload_libraries = 'pg_duckdb'
====

-- PostgreSQLを起動し、拡張機能を使用可能にする
pg_ctl start
psql -c 'CREATE EXTENSION pg_duckdb;'
```

psql上でメタコマンド \dx を用いてインストール済み拡張機能を確認
```
-- 拡張機能の確認
\dx
                  List of installed extensions
   Name    | Version |   Schema   |         Description
-----------+---------+------------+------------------------------
 pg_duckdb | 0.1.0   | public     | DuckDB Embedded in Postgres
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
```

参考:
[https://github.com/duckdb/pg_duckdb?tab=readme-ov-file#compile-from-source](https://github.com/duckdb/pg_duckdb?tab=readme-ov-file#compile-from-source)

## tpc-ds のデータのロード

pg_duckdb の script ディレクトリに [load-tpcds.sh](https://github.com/duckdb/pg_duckdb/blob/main/scripts/load-tpcds.sh) というスクリプトが提供されているので利用する。 このスクリプトでは duckdb の tpc-ds 拡張機能を利用し、CSV ファイルを作成した上で PostgreSQL に COPY コマンドでロードしている。

:::note
スクリプトは環境変数 PGUSER, PGPASSWORD, PGHOST, PGPORT, PGDATABASE を利用するため、あらかじめ設定が必要
:::

load-tpcds.sh の引数はデータ量を表す Scale Factor。 ここでは 1 を指定して実行
```
cd pg_duckdb/scripts
./load-tpcds.sh　1
```
次のような画面出力が行われ、ANALYZEまで表示されればOK
```
+ duckdb -c 'CALL dsdgen(sf=1); EXPORT DATABASE '\''tpcds1'\'' (FORMAT CSV, DELIMITER '\''|'\'');'
...
+ sed 's/COPY/\\copy/' tpcds1/load.sql
+ psql -v ON_ERROR_STOP=1 options=--search-path=tpcds1 -c 'CREATE SCHEMA IF NOT EXISTS tpcds1' -f tpcds1/schema.sql -f tpcds1/load-psql.sql -c 'ANALYZE;'
CREATE SCHEMA
CREATE TABLE
...
COPY 719384
COPY 30
ANALYZE
```

作成されたテーブルの一覧は以下。先のスクリプトの中で tpcds1 というスキーマの中にテーブルが作成されているため、search_path を変更した上で psql のメタコマンド \d+ で確認
```sql
set search_path to tpcds1;
\d+

                                              List of relations
 Schema |          Name          | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+------------------------+-------+----------+-------------+---------------+------------+-------------
 tpcds1 | call_center            | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | catalog_page           | table | postgres | permanent   | heap          | 1976 kB    |
 tpcds1 | catalog_returns        | table | postgres | permanent   | heap          | 23 MB      |
 tpcds1 | catalog_sales          | table | postgres | permanent   | heap          | 293 MB     |
 tpcds1 | customer               | table | postgres | permanent   | heap          | 15 MB      |
 tpcds1 | customer_address       | table | postgres | permanent   | heap          | 7400 kB    |
 tpcds1 | customer_demographics  | table | postgres | permanent   | heap          | 139 MB     |
 tpcds1 | date_dim               | table | postgres | permanent   | heap          | 11 MB      |
 tpcds1 | household_demographics | table | postgres | permanent   | heap          | 440 kB     |
 tpcds1 | income_band            | table | postgres | permanent   | heap          | 8192 bytes |
 tpcds1 | inventory              | table | postgres | permanent   | heap          | 496 MB     |
 tpcds1 | item                   | table | postgres | permanent   | heap          | 5800 kB    |
 tpcds1 | promotion              | table | postgres | permanent   | heap          | 80 kB      |
 tpcds1 | reason                 | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | ship_mode              | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | store                  | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | store_returns          | table | postgres | permanent   | heap          | 38 MB      |
 tpcds1 | store_sales            | table | postgres | permanent   | heap          | 405 MB     |
 tpcds1 | time_dim               | table | postgres | permanent   | heap          | 8424 kB    |
 tpcds1 | warehouse              | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | web_page               | table | postgres | permanent   | heap          | 16 kB      |
 tpcds1 | web_returns            | table | postgres | permanent   | heap          | 11 MB      |
 tpcds1 | web_sales              | table | postgres | permanent   | heap          | 147 MB     |
 tpcds1 | web_site               | table | postgres | permanent   | heap          | 48 kB      |
(24 rows)
```

参考:
[https://github.com/duckdb/pg_duckdb/blob/main/scripts/load-tpcds.sh](https://github.com/duckdb/pg_duckdb/blob/main/scripts/load-tpcds.sh)

## tpc-ds のクエリを用いて実行時間を比較してみる

以下の条件、パターンで実行時間を測定

**条件**
- パラメータ
    - PostgreSQL: 以下のみ変更
            - shared_preload_libraries = 'pg_duckdb'
            - log_destination = csvlog
            - logging_collector = on
            - shared_buffers = 2GB
            - work_mem = 16MB
            - max_parallel_workers_per_gather=default|0[^5]
    - pg_duckdb: 以下のみ変更
            - duckdb.force_execution[^5]
- スキーマ: tpc-ds　(duckdb のものを利用)
- データ量 (Scale Factor): 1
- クエリ: 01 ～ 99.sql を Explain Analyze　を付与して実行[^6]
- パーティショニング: 無し
- 制約やインデックス等: 無し[^7]
- キャッシュの状態: OSのページキャッシュをクリア済 ＆ pg_prewarm 不使用

**測定パターン**
- pg_duckdb使用
- パラレルクエリあり
- パラレルクエリなし

[^5]: 測定パターンに合わせてセッションレベルで変更
[^6]: duckdb の tpcds の　DDL　には制約やインデックスが含まれないため
[^7]: [https://github.com/duckdb/duckdb/tree/main/extension/tpcds/dsdgen/queries]([https://github.com/duckdb/duckdb/tree/main/extension/tpcds/dsdgen/queries) から取得

## え？ 記事執筆中に pg_duckdb の 新バージョンがリリース！？

この記事は 12/10 に準備中なのですが、先ほど pg_duckdb の新バージョン **[v0.2.0](https://github.com/duckdb/pg_duckdb/releases/tag/v0.2.0)** が出ました！！

![pg_duckdbのリリースページの画面キャプチャ](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/380469/77d91ba6-2a4e-9ae8-d10b-ea324b8ecebe.png)

リリースページを見ると、どうやらパフォーマンスの改善も行われたようです。
記事を公開する前から測定結果が陳腐化してしまったため、ここまでを前編として仕切り直してみます。
後編については新バージョンを使用して再測定した上で公開することとします[^8]。

**後編に続く！** (12/22公開予定)

[^8]: [PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql) のシリーズ2 に空きがあって良かった