Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 40
DuckDB, foreign data, Parquet, and pushdown
Design and verify analytical queries that cross PostgreSQL, embedded DuckDB, foreign servers, and object-storage files without hiding data movement.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
A query spanning engines can ship predicates and aggregates to the data, pull rows into the coordinator, or combine both. The SQL text alone does not reveal which boundary was crossed.
02
An extension such as pg_duckdb can run analytical operators through DuckDB inside a PostgreSQL deployment while preserving a PostgreSQL entry point. That adds capability and also a versioned executable dependency.
03
Parquet and Iceberg carry useful metadata, but access still depends on URI handling, credentials, network paths, type mapping, and catalog behavior. Managed services expose only an approved subset of those controls.
04
Filtering, projection, aggregation, ordering, and limiting near the source can avoid transferring irrelevant rows. Support is shape- and version-specific, so a remote-looking scan is not proof that useful work moved.
05
A local statement can read several systems without giving them one MVCC snapshot, commit protocol, or failure boundary. Reproducible analytics must state the consistency point of every source.
06
Embedded DuckDB, a foreign data wrapper, a native managed-service extension, and extract-then-query pipelines optimize different constraints. Compare setup, pushdown, transfer, cache state, concurrency, and governance rather than one warm runtime.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Use each engine's plan output and retain the remote SQL or file scan details. A local Foreign Scan or Custom Scan node is only the start of the evidence.
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT key, count(*)
FROM federated_source
WHERE event_time >= $1
GROUP BY key;Compare equivalent queries with a pushdown-compatible predicate and a deliberately unsupported expression. Record source rows, transferred rows, bytes, requests, and elapsed time.
pushdown_ratio = rows_returned_by_source / rows_examined_at_source
transfer_ratio = rows_received_locally / rows_examined_at_source
# Keep plans and remote statements for both query shapes.Managed services may require allow-listing and a restart before CREATE EXTENSION. Capture the installed version and preload state rather than assuming another server has the same capability.
SELECT extname, extversion FROM pg_extension
WHERE extname IN ('pg_duckdb', 'postgres_fdw', 'oracle_fdw');
SHOW shared_preload_libraries;
SELECT name, setting, pending_restart FROM pg_settings
WHERE name = 'shared_preload_libraries';Object paths can be overwritten while a query remains syntactically identical. Record immutable object versions, file hashes, schemas, row counts, and the credentials scope used by the test.
input = {
'uri': 'object://bucket/path/data.parquet',
'version': '<immutable-version>',
'sha256': '<digest>',
'rows': '<count>',
'schema': '<captured-schema>'
}08
Optional deep dives with the complete experiments and product-version context behind this guide.