pg_duckdb
Versions
| Run as | Image | Server | DuckDB | Digest |
|---|---|---|---|---|
| pg_duckdb | pgduckdb/pgduckdb:18-v1.1.1 | PostgreSQL 18 + pg_duckdb v1.1.1 | bundled; see versions in records | sha256:44c88eb9207971af1d7f9804a37429b1bb36f413cd8d7118c81e1288ddde85d1 |
Observed in this run
Generated the same way for every system from its result files: record counts per measure and status, and the reasons given for anything that is not ok.
pg_duckdb
- M1: ok 22
- M2: n/a 1, ok 1
- M3: ok 2
- M4: error 3, ok 2
- M5: n/a 7, ok 113
- M7: ok 24
- M8: ok 3
- M2 load_convert (n/a): queries normal Postgres tables directly; no conversion step
- M4 streams=16 (error): queries failed during the run (59 error, 79 timeout, 214 ok of 352 queries); no throughput is reported; first errors: [['q21', 'timeout', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Executor Error: Query cancelled'], ['q07', 'timeout', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Executor Error: Query cancelled'], ['q12', 'timeout', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Executor Error: Query cancelled']]
- M4 streams=32 (error): queries failed during the run (455 error, 162 timeout, 87 ok of 704 queries); no throughput is reported; first errors: [['q21', 'timeout', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Executor Error: Query cancelled'], ['q02', 'error', 'OperationalError: consuming input failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.'], ['q07', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_S96K-0.tmp": attempted to read 98304 bytes from location 102531072']]
- M4 streams=8 (error): queries failed during the run (15 error, 10 timeout, 151 ok of 176 queries); no throughput is reported; first errors: [['q21', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_DEFAULT-0.tmp": attempted to read 262144 bytes from location 192937984'], ['q12', 'timeout', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Executor Error: Query cancelled'], ['q18', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) Conversion Error: Casting value "-265845505630417986306734531451771944.96" to type DECIMAL(38,3) failed: value is out of range! when casting from source column sum(l_quantity)\n\nLINE 1: ....lineitem lineitem_1 GROUP BY lineitem_1.l_orderkey HAVING ']]
- M5 dml_insert_select_cross_engine (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 dml_insert_select_from_rowstore (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 join_mixed_exists (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 join_mixed_group (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 join_mixed_inner (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 join_mixed_left (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
- M5 join_mixed_tpch_dimension (n/a): pg_duckdb queries heap tables; there is no separate engine table to mix with
pg_duckdb (duckdb.query)
- M1: ok 26
- M4: error 3, failed 1, ok 1
- M4 streams=16 (error): queries failed during the run (101 error, 1 timeout, 250 ok of 352 queries); no throughput is reported; first errors: [['q21', 'timeout', 'watchdog: OperationalError: consuming input failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.'], ['q02', 'error', 'OperationalError: connection failed: connection to server at "172.17.0.3", port 5432 failed: FATAL: the database system is in recovery mode'], ['q16', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_S64K-0.tmp": attempted to read 65536 bytes from location 93650944']]
- M4 streams=32 (error): queries failed during the run (452 error, 8 timeout, 244 ok of 704 queries); no throughput is reported; first errors: [['q21', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_DEFAULT-0.tmp": attempted to read 262144 bytes from location 66846720'], ['q07', 'error', 'OperationalError: consuming input failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.'], ['q22', 'error', 'OperationalError: connection failed: connection to server at "172.17.0.3", port 5432 failed: FATAL: the database system is in recovery mode']]
- M4 streams=4 (failed): TPC-H Q9 over Parquet hangs with 4 concurrent streams at SF100: the backends ignore statement_timeout, pg_cancel_backend and pg_terminate_backend; only kill -9 stops them (reproduced twice: 58 h on 2026-11-06..09, then 3 streams stuck >20 min on 2026-11-09)
- M4 streams=8 (error): queries failed during the run (18 error, 3 timeout, 155 ok of 176 queries); no throughput is reported; first errors: [['q21', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_DEFAULT-0.tmp": attempted to read 262144 bytes from location 1197211648'], ['q18', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_S96K-0.tmp": attempted to read 98304 bytes from location 28311552'], ['q13', 'error', 'InternalError_: (PGDuckDB/Duckdb_ExecCustomScan_Cpp) IO Error: Could not read enough bytes from file "/var/lib/postgresql/duckdb_tmp/duckdb_temp_storage_S160K-0.tmp": attempted to read 163840 bytes from location 369459200']]
Settings that differ from the defaults
| Setting | Value | Reason | File |
|---|---|---|---|
listen_addresses | '*' | The runner container connects over the docker bridge. | systems/pgduckdb/postgresql.conf |
shared_preload_libraries | 'pg_duckdb' | The extension must be preloaded (the image adds this to the default file, which is replaced here). | systems/pgduckdb/postgresql.conf |
shared_buffers | 16GB | 25% of container memory, as in the Postgres baseline: DuckDB reads heap pages through it. | systems/pgduckdb/postgresql.conf |
effective_cache_size | 48GB | Planner's estimate of buffer plus page cache: 75% of container memory. | systems/pgduckdb/postgresql.conf |
max_parallel_workers_per_gather | 16 | Allow one query to use every core given to the database. | systems/pgduckdb/postgresql.conf |
max_parallel_workers | 16 | Cap on parallel workers across all sessions: one per core. | systems/pgduckdb/postgresql.conf |
max_worker_processes | 16 | Must be at least max_parallel_workers for those workers to start. | systems/pgduckdb/postgresql.conf |
work_mem | 256MB | Analytical sorts and hashes stay in memory instead of spilling at the 4MB default. | systems/pgduckdb/postgresql.conf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/pgduckdb/postgresql.conf |
max_wal_size | 16GB | WAL of mem_gb/4 keeps checkpoints from throttling bulk load at the 1GB default. | systems/pgduckdb/postgresql.conf |
checkpoint_timeout | 30min | Timed checkpoints every 30 minutes, so load and query runs are not interrupted by them. | systems/pgduckdb/postgresql.conf |
duckdb.max_memory | 32GB | DuckDB memory limit: half of container memory, leaving the rest for shared_buffers and page cache. | systems/pgduckdb/postgresql.conf |
duckdb.threads | 16 | DuckDB worker threads: one per core in the DB cpuset (default -1 lets DuckDB pick). | systems/pgduckdb/postgresql.conf |
duckdb.temporary_directory | '/var/lib/postgresql/duckdb_tmp' | DuckDB spill directory, explicit and on the data volume (the default is also under PGDATA). | systems/pgduckdb/postgresql.conf |
duckdb.threads_for_postgres_scan | 16 | Threads of one DuckDB heap scan: one per core (default 2), the analogue of max_parallel_workers_per_gather. | systems/pgduckdb/postgresql.conf |
duckdb.max_workers_per_postgres_scan | 16 | Postgres workers one DuckDB heap scan may use: one per core (default 2). | systems/pgduckdb/postgresql.conf |
README
pg_duckdb 1.1.1 (PostgreSQL 18)
Image pgduckdb/pgduckdb:18-v1.1.1 (digest recorded in every result as versions.image_digest). It ships PostgreSQL 18.1 and bundles DuckDB 1.4.3, which is older than the DuckDB used by the native duckdb-<version> system, so small plan and timing differences against it are expected. versions() reports the server version, the extension version (pg_duckdb 1.1.0, the SQL extension version of release 1.1.1), the DuckDB version (duckdb.query('SELECT version()')) and the image digest.
pg_duckdb lets DuckDB run a query over ordinary Postgres heap tables: the heap is read page by page through the Postgres buffer manager. There is no second copy of the data.
Configuration
Started with -c config_file=/etc/postgresql/postgresql.conf, the file rendered from postgresql.conf and mounted read-only. It sets shared_preload_libraries = 'pg_duckdb' (the image appends that to the default file, which is replaced here). Authentication is trust on the private docker network and shm_size is 8 GB. Every setting has a one-line reason comment in the file.
Rendered from the container limits (duckdb_pg_settings(limits, "pgduckdb") in dbench/systems/baseline_conf.py); at 64 GB and 16 cores:
| Setting | Value | Reason |
|---|---|---|
shared_buffers | 0.25 x container GB = 16GB | DuckDB reads Postgres heap pages through it, so it keeps the Postgres baseline value. |
duckdb.max_memory | 0.5 x container GB = 32GB | The rest of the container goes to shared_buffers and the page cache. |
duckdb.threads | cores in the DB cpuset = 16 | One DuckDB thread per core (default -1). |
duckdb.temporary_directory | /var/lib/postgresql/duckdb_tmp | Spill directory made explicit, on the data volume. |
duckdb.threads_for_postgres_scan, duckdb.max_workers_per_postgres_scan | cores = 16 | Heap-scan parallelism sized to the container (default 2), the analogue of the baseline's max_parallel_workers_per_gather = cores. |
effective_cache_size, max_parallel_workers_per_gather, max_parallel_workers, max_worker_processes, work_mem, max_connections, max_wal_size, checkpoint_timeout | as in the Postgres baseline | Same formulas as systems/postgres/. |
Per session, every benchmark query runs after SET duckdb.force_execution = true, so DuckDB takes the query even where the planner would not choose it.
DuckDB's memory limit was checked from DuckDB itself (duckdb.query('SELECT current_setting(''memory_limit'')')): duckdb.max_memory = 4GB reads 4.0 GiB, so the MB/GB unit is binary and no conversion is needed; duckdb.threads reads back as set.
Sessions and concurrency
Each Postgres backend holds its own DuckDB instance (and its cache). execute() and pushdown() therefore run on one persistent session connection per calling thread, set up once (SET duckdb.force_execution), so warm runs keep the backend's DuckDB state. The session is dropped and reopened after an error, a timeout, a restart or a crash. duckdb.max_memory and duckdb.threads apply per backend: with N concurrent streams the memory in use could reach N times the limit, so the throughput run (M4) divides it: before opening N streams (N > 1) the adapter runs SET duckdb.max_memory = '<0.5 * mem_gb / N>MB' on every session (the GUC is superuser-settable per session, checked on the real server, and DuckDB picks it up when the backend's instance starts). The value in force is recorded as memory_per_backend in the M4 record. Threads are not divided. With one stream the postgresql.conf value applies.
Schema and loading
bench_schema_kind = "rowstore": the same tables as the Postgres baseline (primary keys plus a plain index on every foreign-key column), because the point of pg_duckdb is querying normal Postgres tables. No schema deviations.load_bulkis the Postgres baseline path:COPYper CSV part, row-count check, thenVACUUM (FREEZE, ANALYZE)per table (force_executionis off while loading).load_convert(M11) is n/a: "queries normal Postgres tables directly; no conversion step".supports("M11")is(False, "no separate DuckDB copy to keep fresh").
Pushdown detection (M12)
Marker: the plan text of a plain, plan-only EXPLAIN <query> contains Custom Scan (DuckDBScan) (on the real server: Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0) followed by DuckDB Execution Plan:). Any other plan is fallback. The EXPLAIN runs on the query's own connection after SET duckdb.force_execution and before the timer and the timed region start, so it is never inside the measured time and never executes the query.
Timeouts
statement_timeout is set per session and fires for a pushed query (verified by a docker test: a 3-way cross join of 3M rows times out at 5 s). DuckDB's executor reports the cancel as InternalError_: ... Query cancelled (SQLSTATE XX000, not 57014), so the adapter records it as timeout when the message says cancelled and the deadline has passed. No query lingered after the cancel, so no restart is needed. The client watchdog (pg_cancel_backend, then pg_terminate_backend after 10 s) from the Postgres family stays in place as a backstop.
Storage and other notes
storage_bytes() is du -sb $PGDATA after a CHECKPOINT (WAL counted, as for the baseline). The 22 DuckDB query texts run unchanged. Failed queries, mismatches and fallbacks are results; no query is rewritten.
Track B variant (pg_duckdb-trackb)
Track B runs the canonical DuckDB text over Parquet (dbench/queries/trackb.py) through the raw entry point: SELECT * FROM duckdb.query($dbq$<sql>$dbq$). Same image and postgresql.conf as above; no tables are loaded and nothing about Track A changes. The DuckDB behind it is the bundled 1.4.3, not the 1.5.5 of the other Track B systems. See systems/villagesql/README.md for why Track B exists.
The hybrid joins (join_innodb*, dbench/queries/hybrid.py) use pg_duckdb's own documented pattern instead of duckdb.query(): one PostgreSQL statement in which read_parquet() (columns read as r['col'] with explicit casts) is joined to heap tables. region, nation, supplier and customer are loaded as heap tables with the row-store schema before the queries run; lineitem and orders stay Parquet-only. DuckDB plans the whole statement and reads the heap tables itself (PGDUCKDB_POSTGRES_SCAN in EXPLAIN), unlike VillageSQL, where only the DuckDB part's result crosses into MySQL. The item names keep the join_innodb prefix shared with VillageSQL.