After the archive post, I got a good question: what happens when an old row must change after its year has been written to Parquet? Rewriting a large immutable file for every correction or deletion would defeat much of the purpose of the archive.
My first answer is to archive a fact only once the business considers it immutable. Historical sales shouldn’t change silently after reports are published. Personal data, however, follows a different lifecycle: a customer's name, email, phone number, or address may need to be corrected or erased even if the financial fact must remain unchanged.
That suggests a useful split:
The immutable sales facts go to Parquet. The identifying data remains in PostgreSQL, within the operational system's existing security and governance boundary: database roles and row-level rules, audited changes, constraints, transactional corrections and deletions, retention procedures, and controlled backup and restore processes. pg_duckdb joins both sources only when a query requires them.
The point is not that object storage cannot be secured. It is to avoid creating immutable analytical files from personal data, each of which would require its own access controls, audit trail, retention policy, backup handling, and erasure procedure. Under GDPR's data-minimization principle, keeping direct identifiers out of the archive also reduces the amount of personal data copied into the analytical tier.
This is where pg_duckdb adds more than just convenient file syntax. The PostgreSQL table and the Parquet file become two scan inputs for a single DuckDB relational plan. DuckDB chooses the join order and build side, pushes filters toward both sources, performs the hash join, and runs the aggregation in a single vectorized pipeline.
The query uses a LEFT JOIN with the archived facts on the preserved side, so deleting the matching identity leaves the sale in the result with NULL values in the personal columns.
Lab setup
The sales data is the same deterministic 10-million-row dataset used in the previous posts. There is no random(), so the counts below are reproducible.
SET duckdb.force_execution = false;
DROP TABLE IF EXISTS sales;
CREATE TABLE sales AS
SELECT g AS sale_id,
(DATE '2021-01-01' + ((g * 7) % 1826)) AS sale_date,
1 + (g % 50000) AS cust_id,
1 + (g % 2000) AS prod_id,
1 + (g % 5) AS channel_id,
(ARRAY['US','CH','FR','DE','UK','JP','BR','IN'])[1 + (g % 8)] AS country,
(ARRAY['Consumer','SMB','Enterprise'])[1 + (g % 3)] AS segment,
1 + (g % 10) AS qty,
((5 + (g % 500))::numeric(10,2)) AS amount
FROM generate_series(1, 10000000) g;
ANALYZE sales;
CREATE EXTENSION IF NOT EXISTS pg_duckdb;
SELECT duckdb.create_azure_secret('<blob connection string>');
Build the source table on the PostgreSQL executor (force_execution = false). If DuckDB is forced on, it warns about +(DATE, BIGINT) in this generate_series DDL and falls back to PostgreSQL anyway. You don't need to force DuckDB for this example.
Now create the table that owns the erasable identity. The values are synthetic:
DROP TABLE IF EXISTS customer_identity;
CREATE TABLE customer_identity (
customer_ref int PRIMARY KEY,
full_name text NOT NULL,
email text NOT NULL,
phone text NOT NULL,
address text NOT NULL
);
INSERT INTO customer_identity
SELECT g,
format('Customer %s', lpad(g::text, 5, '0')),
format('customer%s@example.test', lpad(g::text, 5, '0')),
format('+41 22 555 %s', lpad(g::text, 4, '0')),
format('%s Example Street, Test City', g)
FROM generate_series(1, 50000) g;
CREATE UNIQUE INDEX customer_identity_email_idx
ON customer_identity (email);
ANALYZE customer_identity;
For readability, this lab reuses the sequential cust_id as customer_ref. In a real design, use a stable, opaque token with no business meaning, and keep the only mapping to an identity in the protected operational database.
Write a minimized fact file
When exporting the 2021 sales data, select only the immutable fact columns. Do not copy the name, email, phone, address, or other identifying free text into the file:
postgres=> SELECT duckdb.raw_query($$
COPY (
SELECT sale_id,
sale_date,
cust_id AS customer_ref,
prod_id,
channel_id,
qty,
amount
FROM pgduckdb.public.sales
WHERE sale_date < DATE '2022-01-01'
) TO 'az://lake/archive/sales_2021_minimized.parquet'
(FORMAT parquet)
$$)
;
raw_query
-----------
(1 row)
As in the archive post, raw_query sends the COPY command to the embedded DuckDB engine, and pgduckdb.public.sales is the DuckDB catalog name for the PostgreSQL table.
Check both sides:
postgres=> SELECT count(*) FROM customer_identity
;
count
-------
50000
(1 row)
postgres=> SELECT count(*) AS n_sales,
count(DISTINCT (s['customer_ref'])::int) AS n_customers
FROM read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
;
n_sales | n_customers
---------+-------------
1998938 | 50000
(1 row)
The file has 1,998,938 immutable sales and only an opaque bridge to the 50,000 identity rows kept in PostgreSQL.
The join before erasure
Customer 42 has 39 sales in the archived year. The query resolves the bridge to the live identity using a LEFT OUTER JOIN:
postgres=> SELECT
COALESCE(c.full_name, '[erased customer]') AS customer_name,
COALESCE(c.email, '[removed]') AS email,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
LEFT OUTER JOIN customer_identity c
ON c.customer_ref = (s['customer_ref'])::int
WHERE (s['customer_ref'])::int = 42
GROUP BY c.full_name, c.email
;
customer_name | email | n_sales | revenue
----------------+----------------------------+---------+---------
Customer 00042 | customer00042@example.test | 39 | 1794.00
(1 row)
No SET duckdb.force_execution = true is required. DuckDB automatically executes any statement that references read_parquet, including the scan of the PostgreSQL table and the join.
Delete the identity, not the history
Now process the erasure in the operational database:
postgres=> DELETE FROM customer_identity WHERE customer_ref = 42
;
DELETE 1
Run exactly the same join again:
postgres=> SELECT
COALESCE(c.full_name, '[erased customer]') AS customer_name,
COALESCE(c.email, '[removed]') AS email,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
LEFT OUTER JOIN customer_identity c
ON c.customer_ref = (s['customer_ref'])::int
WHERE (s['customer_ref'])::int = 42
GROUP BY c.full_name, c.email
;
customer_name | email | n_sales | revenue
-------------------+-----------+---------+---------
[erased customer] | [removed] | 39 | 1794.00
(1 row)
The 39 sales and their 1,794.00 in revenue remain. The name and email are gone. I used COALESCE to show a value for nulls, but NULL is semantically correct: the customer exists, but their personal data is unknown.
After the deletion, all 1,998,938 archived facts are still counted:
postgres=> SELECT
CASE WHEN c.customer_ref IS NULL THEN 'erased'
ELSE 'identified'
END AS identity_status,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
LEFT JOIN customer_identity c
ON c.customer_ref = (s['customer_ref'])::int
GROUP BY CASE WHEN c.customer_ref IS NULL THEN 'erased'
ELSE 'identified'
END
ORDER BY identity_status
;
identity_status | n_sales | revenue
-----------------+---------+--------------
erased | 39 | 1794.00
identified | 1998899 | 508736609.00
(2 rows)
It's always important to review the execution plan because SQL code is an abstraction that doesn't show how it's executed or how performant and scalable it is.
The full plan: which side is hashed?
Here is the full EXPLAIN (ANALYZE, COSTS OFF) from Azure Database for PostgreSQL:
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
SELECT
CASE WHEN c.customer_ref IS NULL THEN 'erased'
ELSE 'identified'
END AS identity_status,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
LEFT OUTER JOIN customer_identity c
ON c.customer_ref = (s['customer_ref'])::int
GROUP BY CASE WHEN c.customer_ref IS NULL THEN 'erased'
ELSE 'identified'
END
ORDER BY identity_status
;
QUERY PLAN
-------------------------------------------------
Custom Scan (DuckDBScan) (actual time=0.001..0.003 rows=0.00 loops=1)
DuckDB Execution Plan:
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END AS identity_status, count(*) AS n_sales, sum((s.amount)::numeric(10,2)) AS revenue FROM (system.main.read_parquet('az://lake/archive/sales_2021_minimized.parquet'::text) s LEFT JOIN pgduckdb.public.customer_identity c ON ((c.customer_ref = (s.customer_ref)::integer))) GROUP BY CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END ORDER BY CASE WHEN (c.customer_ref IS NULL) THEN 'erased'::text ELSE 'identified'::text END
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 0.515s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_strin│
│ g(#0) │
│ #1 │
│ #2 │
│ │
│ 2 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ CASE WHEN ((c │
│ .customer_ref IS NULL)) │
│ THEN (CAST('erased' AS │
│ VARCHAR)) ELSE CAST( │
│ 'identified' AS VARCHAR) │
│ END ASC │
│ │
│ 2 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_string_│
│ uhugeint(#0) │
│ #1 │
│ #2 │
│ │
│ 2 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_strin│
│ g(#0) │
│ #1 │
│ #2 │
│ │
│ 2 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: #0 │
│ │
│ Aggregates: │
│ count_star() │
│ sum(#1) │
│ │
│ 2 rows │
│ (0.02s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_string_│
│ uhugeint(CASE WHEN ( │
│ (customer_ref IS NULL)) │
│ THEN ('erased') ELSE │
│ 'identified' END) │
│ amount │
│ │
│ 1,998,938 rows │
│ (0.01s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: LEFT │
│ │
│ Conditions: ├──────────────┐
│customer_ref = customer_ref│ │
│ │ │
│ 1,998,938 rows │ │
│ (0.08s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ TABLE_SCAN ││ TABLE_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Function: ││ Table: │
│ READ_PARQUET ││ customer_identity │
│ ││ │
│ Projections: ││ Projections: │
│ customer_ref ││ customer_ref │
│ amount ││ │
│ ││ │
│ Total Files Read: 1 ││ │
│ ││ │
│ 1,998,938 rows ││ 49,999 rows │
│ (0.11s) ││ (0.01s) │
└───────────────────────────┘└───────────────────────────┘
Planning:
Buffers: shared hit=1
Planning Time: 223.697 ms
Execution Time: 223.914 ms
(126 rows)
One top-level engine over heterogeneous sources
The outer PostgreSQL plan is a single Custom Scan (DuckDBScan). Inside it, the DuckDB plan includes both leaves—the Blob READ_PARQUET scan and the PostgreSQL customer_identity scan—and the HASH_JOIN and HASH_GROUP_BY above them. DuckDB, not PostgreSQL, owns the cross-source join and aggregation.
That is different from the read-only foreign-data-wrapper solution. With a conventional FDW, PostgreSQL remains the top-level relational engine: the foreign side returns rows or a pushed-down fragment, and PostgreSQL typically joins the result with local tables. Here, DuckDB sees both sides together and can optimize the pipeline across the storage boundary, including the runtime join filter. Everything between the two scans and the result is one vectorized pipeline: the Parquet scan streams ~2M rows, probes the hash table, and HASH_GROUP_BY reduces them to 2 rows without materializing the join output.
DuckDB, a single engine, executes the query with all its analytics optimizations, while PostgreSQL continues to read its own storage. The PGDUCKDB_POSTGRES_SCAN leaf delegates to SQL, where the PostgreSQL optimizer chooses the heap or index access path. PostgreSQL handles only this scan leaf. DuckDB controls the join order, hash table, filtering between inputs, grouping, and final result.
Read a DuckDB plan bottom-up. A physical HASH_JOIN builds its in-memory hash table from the right child, then streams the left child through it:
- The right child is
customer_identity: 49,999 PostgreSQL rows, with onlycustomer_refprojected. This is the side that is hashed. - The left child is
READ_PARQUET: 1,998,938 Blob rows, with onlycustomer_refandamountprojected. This is the probe side. - The join emits all 1,998,938 facts, then
HASH_GROUP_BYreduces them to two rows.
Hashing 49,999 integers once is the natural shape. A nested loop would prevent DuckDB from performing a B-tree lookup in Parquet. It would replace one small hash build with repeated file probes. Even when the customer filter below leaves one row, DuckDB keeps a hash join because hashing one key is negligible.
What statistics does DuckDB see?
An EXPLAIN without ANALYZE (the default, or explicitly EXPLAIN (ANALYZE OFF, COSTS ON)) displays the estimated cardinalities (DuckDB prefixes them with ~):
Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0)
DuckDB Execution Plan:
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_strin│
│ g(#0) │
│ #1 │
│ #2 │
│ │
│ ~0 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ CASE WHEN ((c │
│ .customer_ref IS NULL)) │
│ THEN (CAST('erased' AS │
│ VARCHAR)) ELSE CAST( │
│ 'identified' AS VARCHAR) │
│ END ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_string_│
│ uhugeint(#0) │
│ #1 │
│ #2 │
│ │
│ ~999,469 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_decompress_strin│
│ g(#0) │
│ #1 │
│ #2 │
│ │
│ ~999,469 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: #0 │
│ │
│ Aggregates: │
│ count_star() │
│ sum(#1) │
│ │
│ ~999,469 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│__internal_compress_string_│
│ uhugeint(CASE WHEN ( │
│ (customer_ref IS NULL)) │
│ THEN ('erased') ELSE │
│ 'identified' END) │
│ amount │
│ │
│ ~1,998,938 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: LEFT │
│ │
│ Conditions: ├──────────────┐
│customer_ref = customer_ref│ │
│ │ │
│ ~1,998,938 rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ READ_PARQUET ││ PGDUCKDB_POSTGRES_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Function: ││ Table: │
│ READ_PARQUET ││ customer_identity │
│ ││ │
│ Projections: ││ Projections: │
│ customer_ref ││ customer_ref │
│ amount ││ │
│ ││ │
│ ~1,998,938 rows ││ ~50,000 rows │
└───────────────────────────┘└───────────────────────────┘
Those estimates come from different places:
- Parquet carries its row counts and per-row-group min/max statistics in the file metadata.
- For a PostgreSQL relation, pg_duckdb 1.1.0 calls PostgreSQL's
estimate_rel_sizeand passes that cardinality to DuckDB. - It does not expose PostgreSQL's per-column histograms, most-common values, or distinct-value estimates to DuckDB (
PostgresTable::GetStatisticsis not implemented in this release).
This explains the mixed quality of the estimates. DuckDB knows the inputs contain about 2 million and 50,000 rows, enough to place the small relation on the hash-build side. It does not predict that the final CASE expression has exactly two groups: the plain plan estimates ~999,469 groups, whereas the analyzed plan produces 2.
ANALYZE customer_identity still matters. PostgreSQL statistics maintain the relation-size estimate supplied to DuckDB, and PostgreSQL's own planner uses its full local statistics when pg_duckdb delegates a filtered scan back to it.
What if the filter starts from the customer?
The setup already created customer_identity_email_idx. Now ask for the archived sales of one email address. This deliberately uses an INNER JOIN: the question asks for one known identity, so unmatched or erased identities are irrelevant:
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
SELECT
c.full_name,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM customer_identity c
JOIN read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
ON (s['customer_ref'])::int = c.customer_ref
WHERE c.email = 'customer00043@example.test'
GROUP BY c.full_name
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (DuckDBScan) (actual time=0.002..0.002 rows=0.00 loops=1)
DuckDB Execution Plan:
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT c.full_name, count(*) AS n_sales, sum((s.amount)::numeric(10,2)) AS revenue FROM (pgduckdb.public.customer_identity c JOIN system.main.read_parquet('az://lake/archive/sales_2021_minimized.parquet'::text) s ON (((s.customer_ref)::integer = c.customer_ref))) WHERE (c.email = 'customer00043@example.test'::text) GROUP BY c.full_name
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 0.437s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: #0 │
│ │
│ Aggregates: │
│ count_star() │
│ sum(#1) │
│ │
│ 1 row │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ full_name │
│ amount │
│ │
│ 39 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ │
│ Conditions: ├──────────────┐
│customer_ref = customer_ref│ │
│ │ │
│ 39 rows │ │
│ (0.00s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ TABLE_SCAN ││ TABLE_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Function: ││ Table: │
│ READ_PARQUET ││ customer_identity │
│ ││ │
│ Projections: ││ Projections: │
│ customer_ref ││ customer_ref │
│ amount ││ full_name │
│ ││ │
│ Total Files Read: 1 ││ Filters: │
│ ││ email='customer00043 │
│ ││ @example.test' │
│ ││ │
│ 39 rows ││ 1 row │
│ (0.12s) ││ (0.00s) │
└───────────────────────────┘└───────────────────────────┘
Planning:
Buffers: shared hit=1
Planning Time: 566.542 ms
Execution Time: 213.829 ms
(78 rows)
The bottom of EXPLAIN (ANALYZE, COSTS OFF) is:
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ │
│ Conditions: ├──────────────┐
│customer_ref = customer_ref│ │
│ │ │
│ 39 rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ TABLE_SCAN ││ TABLE_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Function: ││ Table: │
│ READ_PARQUET ││ customer_identity │
│ ││ │
│ Projections: ││ Projections: │
│ customer_ref ││ customer_ref │
│ amount ││ full_name │
│ ││ │
│ Total Files Read: 1 ││ Filters: │
│ ││ email='customer00043 │
│ ││ @example.test' │
│ ││ │
│ 39 rows ││ 1 row │
└───────────────────────────┘└───────────────────────────┘
It starts with the selective customer side. The right child produces one row and becomes the hash-build side. DuckDB then applies the resulting join-key range while scanning Parquet: the READ_PARQUET leaf emits 39 rows, not 1,998,938. The runtime join filter is not printed as a static Filters: line in this plan, but the scan's actual row count makes it visible. When the predicate is written directly on the file side, the plan does print Filters: customer_ref=43.
Many databases push join filters only through a nested loop. In PostgreSQL, for example, each outer row re-parameterizes an inner index scan. YugabyteDB batches these scans to amortize the probes. Oracle and SQL Server instead attach a Bloom or bitmap filter to the hash join, allowing the probe scan to discard non-matching keys. DuckDB takes this latter approach, optimized for analytics: while building a hash table from the one-row customer_identity side, it derives a dynamic filter on the join key and injects it as a table filter into the probe-side READ_PARQUET scan.
The PostgreSQL table is not a blind full scan either. duckdb.log_pg_explain shows the inner SQL that pg_duckdb delegated and the access path PostgreSQL chose:
postgres=> SET duckdb.log_pg_explain = true;
SET
postgres=> SELECT
c.full_name,
count(*) AS n_sales,
sum((s['amount'])::numeric(10,2)) AS revenue
FROM customer_identity c
JOIN read_parquet(
'az://lake/archive/sales_2021_minimized.parquet'
) s
ON (s['customer_ref'])::int = c.customer_ref
WHERE c.email = 'customer00043@example.test'
GROUP BY c.full_name
;
NOTICE: (PGDuckDB/PostgresTableReader)
QUERY: SELECT customer_ref, full_name FROM public.customer_identity WHERE email='customer00043@example.test'
RUNNING: IN PROCESS THREAD.
EXECUTING:
Parallel Index Scan using customer_identity_email_idx on customer_identity
Index Cond: (email = 'customer00043@example.test'::text)
full_name | n_sales | revenue
----------------+---------+---------
Customer 00043 | 39 | 1833.00
(1 row)
DuckDB does not manage PostgreSQL indexes directly. It pushes columns and predicates into an inner PostgreSQL query, allowing the PostgreSQL optimizer to choose a sequential, bitmap, index, or index-only scan. Here:
postgres=> EXPLAIN (COSTS OFF)
SELECT customer_ref, full_name
FROM public.customer_identity
WHERE email='customer00043@example.test'
;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using customer_identity_email_idx on customer_identity
Index Cond: (email = 'customer00043@example.test'::text)
(2 rows)
For the unfiltered full join above, which requires only customer_ref, the delegated plan was a Parallel Index Only Scan using customer_identity_pkey on customer_identity:
postgres=> EXPLAIN (COSTS OFF)
SELECT customer_ref FROM public.customer_identity
;
QUERY PLAN
----------------------------------------------------------------------------------
Gather
Workers Planned: 1
-> Parallel Index Only Scan using customer_identity_pkey on customer_identity
(3 rows)
The flow works in the other direction too. With WHERE (s['customer_ref'])::int = 43 on the Parquet side, the delegated SQL became SELECT customer_ref, full_name FROM public.customer_identity WHERE customer_ref=43, and PostgreSQL chose Parallel Index Scan using customer_identity_pkey.
Using DuckDB over PostgreSQL via pg_duckdb, rather than PostgreSQL over DuckDB via a foreign data wrapper, is a major advantage here. DuckDB was designed for analytics, while PostgreSQL was primarily designed for OLTP. Regardless of whether storage is row- or columnar-based, the join algorithms are designed accordingly.
Pushdown is not the same as row-group pruning
The filter reaches READ_PARQUET, but whether that avoids I/O depends on how the file is laid out. Inspect the customer_ref statistics stored in three of this file's 17 row groups:
postgres=> SELECT *
FROM duckdb.query($duckdb$
SELECT row_group_id,
num_values AS n,
stats_min AS min_customer_ref,
stats_max AS max_customer_ref
FROM parquet_metadata(
'az://lake/archive/sales_2021_minimized.parquet'
)
WHERE path_in_schema = 'customer_ref'
AND row_group_id IN (0, 1, 16)
ORDER BY row_group_id
$duckdb$)
;
row_group_id | n | min_customer_ref | max_customer_ref
--------------+--------+------------------+------------------
0 | 122880 | 1 | 50000
1 | 122880 | 1 | 50000
16 | 32858 | 1 | 50000
(3 rows)
The generator I used cycles through customer IDs, so every row group can include customer 43. DuckDB filters correctly, but min/max statistics cannot eliminate any row group. This is why a highly selective result does not automatically mean a cheap object-storage read.
If customer-based lookups are important, sort by customer_ref when writing each time partition. I tested an alternate export with ORDER BY cust_id: its first row group covered customers 1–3,072, the next 3,072–6,148, and the last 49,179–50,000. A lookup for customer 43 can then reject 16 of 17 row groups based on metadata. For frequent point lookups with low-latency requirements, materializing the facts into an indexed PostgreSQL table may still be a better physical design than querying Parquet.
The 39-row output of READ_PARQUET in the earlier plan shows the filter still ran at the row level — it saved decompression and hash probes, just not reads. With Parquet, there are two levels of pruning, and only metadata-level pruning avoids touching a row group.
What this pattern does and does not guarantee
Keeping identity out of immutable files does more than avoid rewrites. It keeps the authoritative personal record in the operational database, where a single governed update or deletion can take effect across every analytical query, rather than requiring the same action across many Parquet objects and downstream copies.
But the boundary must be designed carefully:
- Data minimization matters. Do not put names, emails, phone numbers, addresses, or identifying free text in the fact file just because Parquet makes it convenient.
-
customer_refis pseudonymous while another system can map it back to a person. The sequential number in this lab is only for reproducibility; production should use a non-meaningful, opaque token and control every mapping. - PostgreSQL becomes the authoritative location for identity lifecycle rules: who may read personal data, who may correct or erase it, what must be audited, and how retention is enforced.
- The query is not a permission boundary. Users who can query the base tables and files can still see the bridge key. In production, expose a reviewed view or function and restrict direct access to both sources.
- Deleting the live row does not retroactively remove it from historical backups. Erasure procedures must also define how replicas, exports, caches, logs, downstream results, backups, and restored environments are handled under the applicable retention policy.
- Some records may have legal retention requirements. Whether data must be erased, retained, quarantined, or anonymized is a policy and legal decision, not something
pg_duckdbdecides.
This architecture gives governance a clear boundary: personal identity remains under PostgreSQL's operational controls, while the analytical tier receives only minimized, pseudonymous facts. It supports a GDPR program; it does not by itself prove compliance.
Takeaways
- Keep immutable, minimized transaction facts in Parquet and personal identity in the governed operational database.
- Join them on an opaque reference only when a query needs both.
- The central pg_duckdb value is a DuckDB relational plan that spans heterogeneous Blob and PostgreSQL inputs, with an analytic-optimized engine, rather than coordinating separate local and foreign execution fragments.
- The full join hashes the smaller PostgreSQL input and streams the Parquet facts through it.
- pg_duckdb supplies base-table cardinality to DuckDB, while PostgreSQL still chooses the access path for delegated scans and can use its indexes.
- Customer-side filters can reduce both inputs, but Parquet row-group pruning depends on physical ordering and min/max statistics.
- For repeated selective lookups, sort within each time partition or materialize into an indexed table rather than assuming object storage behaves like an OLTP index.
One file is enough to demonstrate the join. A real archive grows into a tree of yearly or monthly files. In the next post, I will lay out that tree using Hive-style partitions and show DuckDB pruning out every file the query does not need.
Top comments (0)