Sub-issue of #160. Remaining bottlenecks identified by profiling but not yet addressed.
Problem
After the optimizations in #162, pg_ducklake before-flush queries are still ~7x slower than standalone DuckDB+DuckLake (7.8s vs 1.1s at 300k rows). Profiling shows two remaining bottlenecks:
1. Per-query re-read of inlined data (43x redundant reads)
Each of the 43 ClickBench queries re-initializes the DuckDB pipeline, which creates a new DuckLakeInlinedDataReader and calls ReadInlinedData. This re-reads the entire inlined data table from PG for every query, even though the data is immutable within a DuckLake snapshot.
Standalone DuckDB+DuckLake has the same per-query re-read pattern but is faster because it reads via libpq binary protocol instead of through the PG executor.
Potential fix: Cache ReadInlinedData result per (table, snapshot_id) in PgDuckLakeMetadataManager. The cache lives per-transaction and is invalidated on snapshot change. This would eliminate 42 of 43 redundant reads.
2. DuckDB MultiFile mutex contention (49% of active samples)
MultiFileFunction::WaitForFile is the dominant cost in the profile. The DuckDB main thread blocks on this mutex while a worker thread does the ReadInlinedData call. This is a DuckDB-internal mutex in the multi-file reader pipeline initialization, not the PG GlobalProcessLock.
This contention exists in both pg_ducklake and standalone DuckDB, but impacts pg_ducklake more because each ReadInlinedData call is more expensive (PG executor vs libpq).
Potential fix: Pre-read inlined data on the main thread before DuckDB pipeline dispatch, or cache as above to make subsequent reads instant.
Profile data (10k rows, 5x43 queries, macOS sample)
| Samples |
% active |
Function |
| 1328 |
49% |
MultiFileFunction::WaitForFile (DuckDB mutex) |
| 262 |
10% |
PgDuckLakeMetadataManager::ReadInlinedData (via PostgresTableReader) |
| 167 |
6% |
PostgresScanFunction -> GetNextMinimalWorkerTuple |
Benchmark gap at 1M rows
| Scenario |
Before flush |
| pg_ducklake |
46.3s |
| DuckDB + DuckLake |
4.3s |
The gap grows linearly with data size since each re-read scans more rows.
Sub-issue of #160. Remaining bottlenecks identified by profiling but not yet addressed.
Problem
After the optimizations in #162, pg_ducklake before-flush queries are still ~7x slower than standalone DuckDB+DuckLake (7.8s vs 1.1s at 300k rows). Profiling shows two remaining bottlenecks:
1. Per-query re-read of inlined data (43x redundant reads)
Each of the 43 ClickBench queries re-initializes the DuckDB pipeline, which creates a new
DuckLakeInlinedDataReaderand callsReadInlinedData. This re-reads the entire inlined data table from PG for every query, even though the data is immutable within a DuckLake snapshot.Standalone DuckDB+DuckLake has the same per-query re-read pattern but is faster because it reads via libpq binary protocol instead of through the PG executor.
Potential fix: Cache
ReadInlinedDataresult per(table, snapshot_id)inPgDuckLakeMetadataManager. The cache lives per-transaction and is invalidated on snapshot change. This would eliminate 42 of 43 redundant reads.2. DuckDB MultiFile mutex contention (49% of active samples)
MultiFileFunction::WaitForFileis the dominant cost in the profile. The DuckDB main thread blocks on this mutex while a worker thread does theReadInlinedDatacall. This is a DuckDB-internal mutex in the multi-file reader pipeline initialization, not the PGGlobalProcessLock.This contention exists in both pg_ducklake and standalone DuckDB, but impacts pg_ducklake more because each
ReadInlinedDatacall is more expensive (PG executor vs libpq).Potential fix: Pre-read inlined data on the main thread before DuckDB pipeline dispatch, or cache as above to make subsequent reads instant.
Profile data (10k rows, 5x43 queries, macOS
sample)MultiFileFunction::WaitForFile(DuckDB mutex)PgDuckLakeMetadataManager::ReadInlinedData(via PostgresTableReader)PostgresScanFunction->GetNextMinimalWorkerTupleBenchmark gap at 1M rows
The gap grows linearly with data size since each re-read scans more rows.