pg_lake ILM Analytics — pg_duckdb vs pg_clickhouse on Iceberg (Polaris)

---

Postgres is the OLTP system; old rows are tiered by pg_lake into Iceberg tables registered in an Apache Polaris REST catalog. This lab asks which read path should serve analytics on that cold data, measured on one frozen Iceberg snapshot that all paths share:

Path	How Postgres reads the lake	Runs where
A pg_lake native (baseline)	pg_lake's own Iceberg tables → pgduck_server (DuckDB)	the main Postgres node
B pg_duckdb 1.1.1 (+ B′ main build, reference)	DuckDB iceberg extension, REST ATTACH to Polaris	a separate Postgres read node
C pg_clickhouse → ClickHouse 26.9	DataLakeCatalog (REST) → Polaris → the same files	ClickHouse node; Postgres only plans and fetches rows

Nothing is loaded into ClickHouse tables: path C reads pg_lake's Iceberg files in place. The full test design, including every place where the lab differs from the v0.1 draft, is in docs/DESIGN.md. Results are in RESULTS.md, and a blog-style write-up is in blog/pg-lake-duckdb-vs-clickhouse.en.md.

⚠️ Two interoperability bugs this lab found

1. pg_lake 3.5.3 writes Iceberg manifests without the spec-required metadata (schema, partition-spec, format-version, content, …). Spec-strict readers reject them: ClickHouse fails with No partition-spec in iceberg manifest file. This is a writer bug, not a pg_clickhouse one — reported upstream as Snowflake-Labs/pg_lake#659. The lab builds pg_lake with the fix from that issue (images/pg-main/patches/), so path C numbers are "pg_lake + patch". Without it, path C cannot read the tables at all.
2. pg_duckdb 1.1.1 (DuckDB 1.4.3 iceberg extension) returns wrong results on range filters over month()-partitioned tables: it prunes files it must read (l_shipdate >= '1994-01-01' drops January–April 1994). Standalone DuckDB 1.4.5 / 1.5.5 and the pg_duckdb main build (1.2.0, DuckDB 1.5.4) are correct, which is why B′ is measured as a reference. Cells where B's result hash differs from A's are reported as wrong and never timed. This is the known month-transform bug duckdb/duckdb-iceberg#699 (days / 30 instead of calendar months), fixed upstream but not yet in a pg_duckdb release — raised as duckdb/pg_duckdb#1083.

The first SF10 run, on pg_clickhouse 0.3.2, crashed a backend (SIGSEGV) on C6. That bug, two foreign scans sharing one binary-driver connection, is already fixed in v0.10.0, which the lab now builds; see RESULTS.md §4.

🧱 Architecture

 datagen (DuckDB tpch) ─► MinIO s3://warehouse/staging ─┐
                                                         ▼
 ┌──────── pg-main: PostgreSQL 18.6 ────────────────────────────────┐  Iceberg commit (REST)
 │ hot.*  heap: last 6 months + dimensions                          │ ─────────────► Polaris 1.7.0 ◄─ polaris-db
 │ tpch.* Iceberg (catalog='rest') ── pg_lake ─► pgduck_server  (A) │                  ▲    ▲
 │ ch.*   foreign tables ─ pg_clickhouse ────────────────────┐  (C) │                  │    │
 └───────────────────────────────────────────────────────────┼──────┘                  │    │
                           ClickHouse 26.9  DataLakeCatalog ◄─┘ ────────────────────────┘    │
 pg-duck      (PG 18 + pg_duckdb 1.1.1, DuckDB 1.4.3)  ATTACH TYPE iceberg  (B) ────────────┤
 pg-duck-main (PG 18 + pg_duckdb 1.2.0, DuckDB 1.5.4)  ATTACH TYPE iceberg  (B′) ───────────┘
 runner: phases, Docker API (cgroup CPU, memory), MinIO v3 metrics

• Same files, same SQL. One Postgres-dialect file per query; each path exposes lineitem, orders (cold tier), lineitem_all, orders_all (hot UNION ALL cold) and six dimensions in its own schema, and the runner only switches search_path.
• Credentials. pg_lake commits as the Polaris principal ilm_writer; B/B′/C read as ilm_reader, which has read-only privileges. MinIO has no STS, so every engine uses static S3 keys (vended_credentials = false on the ClickHouse side).

📁 Layout

pg-analytics/
├── 00-setup.sh … 07-report.sh, 99-cleanup.sh   numbered entry points
├── run-all.sh                  the light run behind RESULTS.md (SF10, ~20 min), unattended
├── compose.yaml, .env.example  services and resource caps
├── images/pg-main/             PG 18 + pg_lake 3.5.3 (+ patch) + pg_clickhouse 0.10.0, release build
├── images/runner/              Python driver image (psql 18, docker SDK, duckdb)
├── init/polaris/bootstrap.py   catalog, principals, grants
├── init/pg-main/*.sql          extensions + REST GUCs, heap load, Iceberg tables, FDW, views
├── init/clickhouse/            DataLakeCatalog + bench views, filesystem cache
├── datagen/gen.py              DuckDB tpch → Parquet in MinIO (sliced, resumable)
├── ilm/tiering.sql             idempotent monthly heap → Iceberg move
├── queries/tpch, queries/custom  10 TPC-H + 7 custom queries (L1–L5)
├── runner/                     setup, verify (Phase 0), bench, faults, concurrency, direct, ilm_ops
├── analysis/report.py          SUMMARY.md tables + charts
├── docs/DESIGN.md              test design (bilingual)
├── RESULTS.md                  findings (bilingual)
├── blog/                       the write-up as a blog post, .en.md and .ko.md
└── results/                    raw CSVs, plans, charts per scale factor

🚀 Quick start

Needs Docker with ≥ 8 GB for the VM, ~40 GB free disk, and time: the first 00-setup.sh compiles PostgreSQL, pg_lake (with DuckDB) and pg_clickhouse (30–60 min).

The light run that produced RESULTS.md: SF10, with the cold months written straight into Iceberg (setup.py --bulk, no monthly tiering job). It runs 9 queries covering L1–L5 with dimensions in the lake (D1), with 1 warm-up + 5 timed runs on A/B′/C. B runs only Q6 and C1, to show its pruning bug. It takes about 20 min after the build:

cd local/pg-analytics
./00-setup.sh          # build, start, bootstrap Polaris, stage TPC-H for SF_LIST
./run-all.sh 10        # bulk load, Phase 0 gate, bench A/B'/C (+ B on Q6, C1)
./07-report.sh         # results/SUMMARY.md + results/charts/

The other numbered scripts run the full design step by step: 01-load.sh (monthly tiering), 03-bench.sh (all 17 queries, cold runs, D2) and the rest. They are kept, but were not run end to end for RESULTS.md.

Script	Phase	What it does
01-load.sh <sf>	1 + 0	heap load, monthly ilm.tier_month() into Iceberg, VACUUM, readers, integration gate
02-faults.sh <sf>	1	SIGKILL pg-main / stop Polaris during tiering commits, classify, re-run
03-bench.sh <sf>	2	cold + 5 warm per query × path × dimension placement, EXPLAIN, result hashes
04-concurrency.sh <sf>	3	1/4/8/16 closed-loop clients on Q6 and Q3
05-direct.sh <sf>	§5.4	Q6/Q3 straight on pgduck_server and ClickHouse (cost of going through Postgres)
06-ilm-ops.sh <sf>	4	erasure deletes, freshness lag, snapshot expiry (modifies data: run last)
99-cleanup.sh		stop everything, delete volumes

Interactive shells: psql -h localhost -p 15432 -U postgres ilm (pg-main), -p 15433 (pg-duck), -p 15434 (pg-duck-main), docker exec -it pga-clickhouse clickhouse-client.

🔍 Things worth knowing

• pg_lake + REST catalog: database name = Polaris catalog (warehouse), schema = namespace. catalog = 'rest' tables keep no metadata_location locally (iceberg_tables is empty for them); ask Polaris. File inventory is in lake_table.files.
• Polaris must allow client-chosen locations (ALLOW_UNSTRUCTURED_TABLE_LOCATION, ALLOW_EXTERNAL_TABLE_LOCATION, ALLOW_NAMESPACE_CUSTOM_LOCATION), or pg_lake's and DuckDB's creates fail.
• pg_duckdb's ATTACH is per session. A PG18 login event trigger re-attaches Polaris for every connection. It must not use an EXCEPTION block, because DuckDB rejects the subtransaction it opens.
• ClickHouse config.d is replaced by the mount here, so listen_host is restated in init/clickhouse/config.d/lake.xml.
• MinIO's v2 cluster metrics lag ~10 s. The runner uses the v3 API metrics, or reads land on the next query's row.
• MinIO images: minio/minio no longer publishes to Docker Hub; the lab pins bitnamilegacy/minio.
• max_worker_processes = 16. PG18 io workers, parallel workers and pg_lake's per-statement workers share the default pool of 8. SF10 tiering failed with out of background worker slots until it was raised.
• Bulk writes go one year per commit. A single 54 M-row partitioned INSERT into lineitem_cold got pg-main OOM-killed at the 4 GB cap.
• dbgen slices every table, nation and region included. With children > 1 each slice holds only part of them, so datagen/gen.py writes those two from their own run.

📝 License

MIT. The pg_lake patch under images/pg-main/patches/ is a change to pg_lake (Apache-2.0) and is offered upstream under that licence.

👤 Author

Ken (ClickHouse Solution Architect) Created: 2026-09-25

---

cd local/pg-analytics
./00-setup.sh          # 빌드, 기동, Polaris bootstrap, SF_LIST의 TPC-H 생성
./run-all.sh 10        # bulk 적재, Phase 0 게이트, A/B'/C 벤치(+ B는 Q6, C1)
./07-report.sh         # results/SUMMARY.md + results/charts/