pg_duckdb
pg_duckdb copied to clipboard
duckdb
Feature Request: Support configuring chunk size when reading Parquet files from S3 via read_parquet
Description
Description:
When using pg_duckdb to read Parquet files stored on S3, such as:
explain analyse
select
*
from
(
select
r ['scansn'] :: character varying(11) as mcbsno,
r ['subpanelno'] as blockno,
r ['pn'] as partno,
r ['componentid'],
r ['supplierpn'] as mfg_pn,
r ['lotcode'] as lotno,
r ['datecode'],
r ['location'] :: text as reference,
r ['moduleno'],
r ['slotno'],
r ['feederid'],
r ['headid'],
r ['holderno'],
r ['nozzleid'],
r ['machinetype'],
r ['cdt'],
r ['qty']
from
read_parquet(
array ['s3://table-data/smt-componentTrace-days/2025/05/02/2025-05-02.5334d6025622.parquet','s3://table-data/smt-componentTrace-days/2025/05/02/2025-05-02.d6cca87eb27d.parquet','s3://table-data/smt-componentTrace-days/2025/05/03/2025-05-03.7da713e1d8ae.parquet','s3://table-data/smt-componentTrace-days/2025/05/03/2025-05-03.9813e438417b.parquet','s3://table-data/smt-componentTrace-days/2025/05/05/2025-05-05.094fc8111e01.parquet']
) r
) r
where
r.mcbsno in (
'U155GT5541',
'V354RT1027',
'V354RT1052',
'9J54RT0176',
'V354RT0929',
'V354RT0719',
'V354RT1098',
'V354RT1097',
'9J54RT0146',
'BL54RT0202',
'HF54RTA3HE',
'V354RT1099',
'HF54RTA3TW',
'V354RT0760',
'V354RT0932',
'9J54RT0146',
'9J54RT0151',
'HF54RTA39B',
'9J54RT0210',
'V354RT1086',
'9J54RT0151',
'HF54RTA3N0',
'V354RT0932',
'V354RT0788'
)
and string_to_array(r.reference, ',') && array ['BAT4','C961','COMPONENT1','GOIDFINGER','HEATSINK','J371','J372','J373','J374','J375','J376','J377','LS2','MB2_SL1M','PC500','PU128','PU97','R2234','R718','SCERW','SCREW','STUD','SW_PWR']
/*
"Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0) (actual time=1186.999..1187.075 rows=1 loops=1)"
" DuckDB Execution Plan: "
"┌─────────────────────────────────────┐"
"│┌───────────────────────────────────┐│"
"││ Query Profiling Information ││"
"│└───────────────────────────────────┘│"
"└─────────────────────────────────────┘"
"EXPLAIN ANALYZE SELECT mcbsno, blockno, partno, r, mfg_pn, lotno, r_1 AS r, reference, r_2 AS r, r_3 AS r, r_4 AS r, r_5 AS r, r_6 AS r, r_7 AS r, r_8 AS r, r_9 AS r, r_10 AS r FROM (SELECT (r_1.scansn)::character varying(11) AS mcbsno, r_1.subpanelno AS blockno, r_1.pn AS partno, r_1.componentid AS r, r_1.supplierpn AS mfg_pn, r_1.lotcode AS lotno, r_1.datecode AS r, (r_1.location)::text AS reference, r_1.moduleno AS r, r_1.slotno AS r, r_1.feederid AS r, r_1.headid AS r, r_1.holderno AS r, r_1.nozzleid AS r, r_1.machinetype AS r, r_1.cdt AS r, r_1.qty AS r FROM system.main.read_parquet(VARIADIC ARRAY['s3://table-data/smt-componentTrace-days/2025/05/02/2025-05-02.5334d6025622.parquet'::text, 's3://table-data/smt-componentTrace-days/2025/05/02/2025-05-02.d6cca87eb27d.parquet'::text, 's3://table-data/smt-componentTrace-days/2025/05/03/2025-05-03.7da713e1d8ae.parquet'::text, 's3://table-data/smt-componentTrace-days/2025/05/03/2025-05-03.9813e438417b.parquet'::text, 's3://table-data/smt-componentTrace-days/2025/05/05/2025-05-05.094fc8111e01.parquet'::text]) r_1) r(mcbsno, blockno, partno, r, mfg_pn, lotno, r_1, reference, r_2, r_3, r_4, r_5, r_6, r_7, r_8, r_9, r_10) WHERE (((mcbsno)::text = ANY ((ARRAY['U155GT5541'::character varying, 'V354RT1027'::character varying, 'V354RT1052'::character varying, '9J54RT0176'::character varying, 'V354RT0929'::character varying, 'V354RT0719'::character varying, 'V354RT1098'::character varying, 'V354RT1097'::character varying, '9J54RT0146'::character varying, 'BL54RT0202'::character varying, 'HF54RTA3HE'::character varying, 'V354RT1099'::character varying, 'HF54RTA3TW'::character varying, 'V354RT0760'::character varying, 'V354RT0932'::character varying, '9J54RT0146'::character varying, '9J54RT0151'::character varying, 'HF54RTA39B'::character varying, '9J54RT0210'::character varying, 'V354RT1086'::character varying, '9J54RT0151'::character varying, 'HF54RTA3N0'::character varying, 'V354RT0932'::character varying, 'V354RT0788'::character varying])::text[])) AND (string_to_array(reference, ','::text) && ARRAY['BAT4'::text, 'C961'::text, 'COMPONENT1'::text, 'GOIDFINGER'::text, 'HEATSINK'::text, 'J371'::text, 'J372'::text, 'J373'::text, 'J374'::text, 'J375'::text, 'J376'::text, 'J377'::text, 'LS2'::text, 'MB2_SL1M'::text, 'PC500'::text, 'PU128'::text, 'PU97'::text, 'R2234'::text, 'R718'::text, 'SCERW'::text, 'SCREW'::text, 'STUD'::text, 'SW_PWR'::text]))"
"┌─────────────────────────────────────┐"
"│┌───────────────────────────────────┐│"
"││ HTTPFS HTTP Stats ││"
"││ ││"
"││ in: 2.3 MiB ││"
"││ out: 0 bytes ││"
"││ #HEAD: 5 ││"
"││ #GET: 316 ││"
"││ #PUT: 0 ││"
"││ #POST: 0 ││"
"│└───────────────────────────────────┘│"
"└─────────────────────────────────────┘"
"┌────────────────────────────────────────────────┐"
"│┌──────────────────────────────────────────────┐│"
"││ Total Time: 1.14s ││"
"│└──────────────────────────────────────────────┘│"
"└────────────────────────────────────────────────┘"
"┌───────────────────────────┐"
"│ QUERY │"
"└─────────────┬─────────────┘"
"┌─────────────┴─────────────┐"
"│ EXPLAIN_ANALYZE │"
"│ ──────────────────── │"
"│ 0 Rows │"
"│ (0.00s) │"
"└─────────────┬─────────────┘"
"┌─────────────┴─────────────┐"
"│ HASH_JOIN │"
"│ ──────────────────── │"
"│ Join Type: SEMI │"
"│ │"
"│ Conditions: ├──────────────┐"
"│ mcbsno = #0 │ │"
"│ │ │"
"│ 52 Rows │ │"
"│ (0.00s) │ │"
"└─────────────┬─────────────┘ │"
"┌─────────────┴─────────────┐┌─────────────┴─────────────┐"
"│ FILTER ││ PROJECTION │"
"│ ──────────────────── ││ ──────────────────── │"
"│ (string_to_array(reference││ UNNEST(list_value(CAST( │"
"│ , ',') && ['BAT4', 'C961',││ 'U155GT5541' AS VARCHAR), │"
"│ 'COMPONENT1', 'GOIDFINGER││ CAST('V354RT1027' AS │"
"│ ', 'HEATSINK', 'J371', ││ VARCHAR), CAST( │"
"│ 'J372', 'J373', 'J374', ││ 'V354RT1052' AS VARCHAR), │"
"│ 'J375', 'J376', 'J377', ││ CAST('9J54RT0176' AS │"
"│ 'LS2', 'MB2_SL1M', 'PC500'││ VARCHAR), CAST( │"
"│ , 'PU128', 'PU97', 'R2234'││ 'V354RT0929' AS VARCHAR), │"
"│ , 'R718', 'SCERW', 'SCREW'││ CAST('V354RT0719' AS │"
"│ , 'STUD', 'SW_PWR']) ││ VARCHAR), CAST( │"
"│ ││ 'V354RT1098' AS VARCHAR), │"
"│ ││ CAST('V354RT1097' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ '9J54RT0146' AS VARCHAR), │"
"│ ││ CAST('BL54RT0202' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ 'HF54RTA3HE' AS VARCHAR), │"
"│ ││ CAST('V354RT1099' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ 'HF54RTA3TW' AS VARCHAR), │"
"│ ││ CAST('V354RT0760' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ 'V354RT0932' AS VARCHAR), │"
"│ ││ CAST('9J54RT0146' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ '9J54RT0151' AS VARCHAR), │"
"│ ││ CAST('HF54RTA39B' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ '9J54RT0210' AS VARCHAR), │"
"│ ││ CAST('V354RT1086' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ '9J54RT0151' AS VARCHAR), │"
"│ ││ CAST('HF54RTA3N0' AS │"
"│ ││ VARCHAR), CAST( │"
"│ ││ 'V354RT0932' AS VARCHAR), │"
"│ ││ CAST('V354RT0788' AS │"
"│ ││ VARCHAR))) │"
"│ ││ │"
"│ 4499 Rows ││ 24 Rows │"
"│ (1.55s) ││ (0.00s) │"
"└─────────────┬─────────────┘└─────────────┬─────────────┘"
"┌─────────────┴─────────────┐┌─────────────┴─────────────┐"
"│ TABLE_SCAN ││ UNNEST │"
"│ ──────────────────── ││ ──────────────────── │"
"│ Function: ││ │"
"│ READ_PARQUET ││ │"
"│ ││ │"
"│ Projections: ││ │"
"│ ScanSN ││ │"
"│ SubPanelNo ││ │"
"│ PN ││ │"
"│ ComponentId ││ │"
"│ SupplierPN ││ │"
"│ LotCode ││ │"
"│ DateCode ││ │"
"│ Location ││ │"
"│ ModuleNo ││ │"
"│ SlotNo ││ │"
"│ FeederId ││ │"
"│ HeadId ││ │"
"│ HolderNo ││ │"
"│ NozzleId ││ │"
"│ MachineType ││ │"
"│ Cdt ││ │"
"│ Qty ││ │"
"│ ││ │"
"│ 1292070 Rows ││ 24 Rows │"
"│ (5.41s) ││ (0.00s) │"
"└───────────────────────────┘└─────────────┬─────────────┘"
" ┌─────────────┴─────────────┐"
" │ DUMMY_SCAN │"
" │ ──────────────────── │"
" │ 1 Rows │"
" │ (0.00s) │"
" └───────────────────────────┘"
"Planning Time: 89.990 ms"
"Execution Time: 1268.680 ms"
*/
we observed that reading just 5 Parquet files can result in over 300 HTTP requests being sent to S3. This is because the current default request granularity is very fine (around 1KB per request), which leads to excessive I/O overhead and latency, especially when working with cloud object storage.
We’d like to request the ability to control the read request size, so that instead of reading in 1KB chunks, the engine could fetch data in larger blocks—for example, 100KB or 1MB per request.
This feature would help reduce the number of round-trip requests to S3 and significantly improve performance and efficiency in real-world usage.
Expected Behavior:
We would like to see support for:
- A configurable request size parameter (e.g., read_parquet(..., s3_request_size='1MB'))
- Or automatic batching that increases the read block size to reduce request count when reading remote Parquet files
This might be expected. The layout of the parquet file needs multiple random IOs when reading. Like, checking the magic number, file footer, row group meta, and pages in row groups. In your particular case, reading 17 columns from 5 files would need at least 5*2 (footer reads) + 17 (column page reads) * N * 5 = 95N, where N is the number of row groups in the files.
I guess what you want is file caching. Each time you send an IO request to S3, it doesn't just read the requested data range, but also prefetches data nearby. For instance, a single request might read 4MB of data instead of just a few KBs. Future reads will be much faster because they'll be reading from the local cache instead of S3.
If accessing just 5 parquet files results in such a large number of requests, then with hundreds of files, any network instability would cause the latency spikes.
I guess what you want is file caching.
My goal is simply to read the data, so it's not really about things like data caching. So for scenarios like this, how can it be optimized? Right now, the only thing I can think of is reducing the number of columns being accessed. @YuweiXiao
Is there a way to merge column read requests and push down filter conditions?
Filters and projections are pushed down. In general this isn't much related to pg_duckdb afaict, i.e. the same requests apply to general duckdb.
You're right -- DuckDB also issues many requests, but if the data is read repeatedly, DuckDB has a built-in caching mechanism by default, whereas pg_duckdb does not.
CREATE SECRET itc (
TYPE S3,
KEY_ID '...',
SECRET '...',
ENDPOINT '...',
USE_SSL true,
URL_STYLE 'path'
);
-- explain analyse
SELECT * FROM read_parquet('s3://table-data/smt-componentTrace-days/2025/07/20/2025-07-20.0cef0d14e71a.parquet', filename = false)
WHERE scansn in ('U157GTBVW1');
/*
100% ▕████████████████████████████████████████████████████████████▏
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyse SELECT * FROM read_parquet('s3://table-data/smt-componentTrace-days/2025/07/20/2025-07-20.0cef0d14e71a.parquet', filename = false) where scansn in ('U157GTBVW1');
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ HTTPFS HTTP Stats ││
││ ││
││ in: 158.2 KiB ││
││ out: 0 bytes ││
││ #HEAD: 1 ││
││ #GET: 54 ││
││ #PUT: 0 ││
││ #POST: 0 ││
││ #DELETE: 0 ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 6.31s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ WorkstationName │
│ WorkstationType │
│ MachineType │
│ TraceId │
│ SequenceNo │
│ Qty │
│ ComponentId │
│ PN │
│ DateCode │
│ LotCode │
│ ScanSN │
│ Revision │
│ SupplierPN │
│ SubPanelNo │
│ ModuleNo │
│ SlotNo │
│ SubSlotNo │
│ FeederId │
│ HeadId │
│ HolderNo │
│ NozzleId │
│ Location │
│ Cdt │
│ Model │
│ TimeStamp │
│ filename │
│ │
│ 614 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ TABLE_SCAN │
│ ──────────────────── │
│ Function: │
│ READ_PARQUET │
│ │
│ Projections: │
│ ScanSN │
│ WorkstationName │
│ WorkstationType │
│ MachineType │
│ TraceId │
│ SequenceNo │
│ Qty │
│ ComponentId │
│ PN │
│ DateCode │
│ LotCode │
│ Revision │
│ SupplierPN │
│ SubPanelNo │
│ ModuleNo │
│ SlotNo │
│ SubSlotNo │
│ FeederId │
│ HeadId │
│ HolderNo │
│ NozzleId │
│ Location │
│ Cdt │
│ Model │
│ TimeStamp │
│ filename │
│ │
│ Filters: │
│ ScanSN='U157GTBVW1' │
│ │
│ Total Files Read: 1 │
│ │
│ 614 Rows │
│ (10.60s) │
└───────────────────────────┘
D explain analyse
SELECT scansn FROM read_parquet('s3://table-data/smt-componentTrace-days/2025/07/20/2025-07-20.0cef0d14e71a.parquet', filename = false)
where scansn in ('U157GTBVW1');
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyse SELECT scansn FROM read_parquet('s3://table-data/smt-componentTrace-days/2025/07/20/2025-07-20.0cef0d14e71a.parquet', filename = false) where scansn in ('U157GTBVW1');
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ HTTPFS HTTP Stats ││
││ ││
││ in: 0 bytes ││
││ out: 0 bytes ││
││ #HEAD: 1 ││
││ #GET: 0 ││
││ #PUT: 0 ││
││ #POST: 0 ││
││ #DELETE: 0 ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 0.611s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ TABLE_SCAN │
│ ──────────────────── │
│ Function: │
│ READ_PARQUET │
│ │
│ Projections: ScanSN │
│ │
│ Filters: │
│ ScanSN='U157GTBVW1' │
│ │
│ Total Files Read: 1 │
│ │
│ 614 Rows │
│ (0.00s) │
└───────────────────────────┘
*/