Archive cold Postgres data to Azure Blob and keep querying it with pg_duckdb
A Postgres table that only grows is a familiar problem: queries touch recent data, but old rows still consume storage and slow maintenance. With pg_duckdb, you can move cold data to Parquet in Azure Blob Storage and still query it — on its own and joined back to the live table — from the same connection. Here's the full cycle for a sales table: 10M rows over five years, with 2021 archived out.
Setup (same table as the previous post)
I'll use the same data as in the previous post DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL:
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>');
I set force_execution = false, the default, in case you activated it when reproducing the previous post. The table needs to be built on the PostgreSQL executor. If DuckDB is enforced, it issues a warning: Binder Error: +(DATE, BIGINT) on this generate_series DDL and defaults back to Postgres.
Writing to Blob from inside Azure Database for PostgreSQL
One point up front, because it shapes the workflow. pg_duckdb can write Parquet to Azure Blob from inside the database — but on Azure Database for PostgreSQL the plain COPY … TO 'az://…' syntax is refused:
COPY (SELECT …) TO 'az://lake/archive/sales_2021.parquet' (FORMAT parquet);
-- ERROR: relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL
On self-managed PostgreSQL, the pg_duckdb extension intercepts COPY ... TO 'az://...' and runs it in DuckDB for you. However, Azure ensures that extensions cannot bypass PostgreSQL security checks. It considers any path without a leading / as "relative", causing the az:// URL to be rejected. This occurs before the server requires superuser privileges or pg_write_server_files permissions for server-side file operations. Azure allows PostgreSQL extensions but limits their ability to bypass the managed environment's security controls.
The way through is duckdb.raw_query, which runs the COPY inside the embedded DuckDB engine. This isn't a way around the platform's guard — it's a write to a different resource, governed by a different control: the Blob write is governed instead by DuckDB's own secret and allow-list (duckdb.allowed_directories, here az://), so the write lands through the layer that actually owns that resource. That's the in-database export used below. If you'd rather not write from the database at all, a client-side export works too — see the alternative at the end of Step 2.
Step 1 — Identify the cold slice
I'll consider sales prior to 2022 as old data to be archived:
postgres=> SELECT count(*)
FROM sales
;
count
----------
10000000
postgres=> SELECT count(*)
FROM sales
WHERE sale_date < DATE '2022-01-01'
;
count
---------
1998938
We will retain just under 2 million rows from 2021 in the operational database and archive the older data.
Step 2 — Export to Parquet in Blob (in-database)
Hand the COPY to the DuckDB engine with duckdb.raw_query(), writing straight to Blob. Keep types faithful — the source amount is already numeric(10,2), so DuckDB writes it as DECIMAL(10,2) and reads it back natively:
postgres=> SELECT duckdb.raw_query($$
COPY (
SELECT sale_id, sale_date, cust_id, prod_id, channel_id,
country, segment, qty, amount
FROM pgduckdb.public.sales
WHERE sale_date < DATE '2022-01-01'
) TO 'az://lake/archive/sales_2021.parquet' (FORMAT parquet)
$$)
;
raw_query
-----------
(1 row)
Four things to know:
- Qualify the table as
pgduckdb.public.sales, notsalesbecauseraw_queryruns inside the DuckDB engine, so it sees DuckDB's catalog, not PostgreSQL's search path.pg_duckdbmounts your Postgres tables under thepgduckdb.public.*catalog. A baresalesfails withCatalog Error: Table with name sales does not exist. -
duckdb.query()won't carry this — it only accepts a singleSELECT, so theCOPYmust go throughraw_query(). - The write target must be under the
az://allow-list (a local path fails withLocalFileSystem has been disabled by configuration). - If the target blob already exists and was created by something other than DuckDB (an
az storage blob upload, say), the write can fail withIO Error: AzureStorageFileSystem … 'InvalidBlobType'. DuckDB happily overwrites files it wrote itself, but not a blob another tool created with an incompatible type. Write to a fresh path, or delete the stale blob first.
The result is a sales_2021.parquet of about 17 MB for these ~2M rows, already in Blob — no upload step.
Alternative — client-side export
If you can't or don't want to write from the database, pull the rows out and write the file client-side, then upload it.
The DuckDB CLI can read Postgres directly:
-- in the duckdb CLI
ATTACH 'host=… dbname=… user=… password=… sslmode=require' AS pg (TYPE postgres);
COPY (
SELECT sale_id, sale_date, cust_id, prod_id, channel_id,
country, segment, qty, amount
FROM pg.sales WHERE sale_date < DATE '2022-01-01'
) TO 'sales_2021.parquet' (FORMAT parquet);
The resulting file can be uploaded:
az storage blob upload \
--account-name <account> --account-key <key> \
-c lake -f sales_2021.parquet -n archive/sales_2021.parquet --overwrite
This method is a possible alternative, but using PostgreSQL with a DuckDB COPY command offers the best security because it keeps the data within the cloud services protected by their native authentication methods.
Step 3 — Read it back through pg_duckdb
You can read the files from PostgreSQL to verify their content:
postgres=> SELECT count(*)
FROM read_parquet('az://lake/archive/sales_2021.parquet')
;
count
---------
1998938
(1 row)
The 2021 data is queryable straight from Blob. Note that the count(*) didn't read all rows, as the row count is available in the Parquet metadata footer and DuckDB gets the count from it.
Step 4 — Delete the cold rows from Postgres
Now that it's safely in Blob and verified, reclaim the space for new inserts:
postgres=> DELETE FROM sales
WHERE sale_date < DATE '2022-01-01'
;
DELETE 1998938
postgres=> SELECT count(*) FROM sales
;
count
---------
8001062
(1 row)
If lifecycle management wasn't in place from the start and you've deleted a large amount of data, you may need to reorg the table to actually shrink it on disk (pg_repack and pg_squeeze are available on Azure Database for PostgreSQL). Plain DELETE only marks rows as dead — it doesn't free disk space. A subsequent VACUUM reclaims that space, but it doesn't return it to the operating system: the freed heap space becomes available for any new row inserted into that table (tracked via the Free Space Map). In B-Tree indexes, reclaimed space is only reusable by new index entries whose key value falls into the same page/block — because B-Tree pages are key-ordered, not by arbitrary new values.
Step 5 — Keep querying history, concatenated with live data
The 2021 rows are gone from Postgres, but they are accessible from Parquet, and one query can span both. Here is an aggregate of the revenue per year, with hot rows from Postgres and old ones from Blob:
postgres=> SELECT yr, count(*) AS n, sum(amount) AS revenue
FROM (
-- Operational data in PostgreSQL
SELECT extract(year FROM sale_date)::int AS yr, amount
FROM sales
WHERE sale_date >= DATE '2022-01-01'
-- Heterogeneous Federation
UNION ALL
-- Archived data in Blob
SELECT extract(year FROM (r['sale_date'])::date)::int,
(r['amount'])::numeric(10,2)
FROM read_parquet('az://lake/archive/sales_2021.parquet') r
) u
GROUP BY yr ORDER BY yr
;
yr | n | revenue
------+---------+--------------
2021 | 1998938 | 508738403.00
2022 | 1998897 | 508731504.00
2023 | 1998897 | 508723449.00
2024 | 2004372 | 510107354.00
2025 | 1998896 | 508699290.00
(5 rows)
The execution plan shows one DuckDBScan with a UNION over the Postgres scan and the Blob read:
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
SELECT yr, count(*) AS n, sum(amount) AS revenue
FROM (
SELECT extract(year FROM sale_date)::int AS yr, amount
FROM sales
WHERE sale_date >= DATE '2022-01-01'
UNION ALL
SELECT extract(year FROM (r['sale_date'])::date)::int,
(r['amount'])::numeric(10,2)
FROM read_parquet('az://lake/archive/sales_2021.parquet') r
) u
GROUP BY yr ORDER BY yr
;
QUERY PLAN
-------------------------------------------------
Custom Scan (DuckDBScan) (actual time=0.002..0.003 rows=0.00 loops=1)
DuckDB Execution Plan:
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 6.45s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ ORDER_BY │
│ ──────────────────── │
│ u.yr ASC │
│ │
│ 5 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: #0 │
│ │
│ Aggregates: │
│ count_star() │
│ sum(#1) │
│ │
│ 5 rows │
│ (0.21s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ yr │
│ amount │
│ │
│ 10,000,000 rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNION │
│ ──────────────────── │
│ 0 rows ├──────────────┐
│ (0.00s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ PROJECTION ││ PROJECTION │
│ ──────────────────── ││ ──────────────────── │
│ yr ││ extract │
│ amount ││ r │
│ ││ │
│ 8,001,062 rows ││ 1,998,938 rows │
│ (0.03s) ││ (0.01s) │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ TABLE_SCAN ││ TABLE_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Table: sales ││ Function: │
│ ││ READ_PARQUET │
│ Projections: ││ │
│ sale_date ││ Projections: │
│ amount ││ sale_date │
│ Filters: ││ amount │
│ sale_date>='2022-01-01': ││ Total Files Read: 1 │
│ :DATE ││ │
│ ││ │
│ 8,001,062 rows ││ 1,998,938 rows │
│ (4.68s) ││ (6.60s) │
└───────────────────────────┘└───────────────────────────┘
Planning:
Buffers: shared hit=1
Planning Time: 446.973 ms
Execution Time: 224.008 ms
The left leaf reads the live table (with the sale_date >= 2022 filter pushed into the scan). The right leaf reads the archive from Blob. DuckDB unions and aggregates them. Note you don't need SET duckdb.force_execution = true — a query that references read_parquet goes to DuckDB automatically.
Making it a routine
If you partition the table by time (pg_partman is available on Azure Database for PostgreSQL), archiving a month becomes: export that partition, upload, DROP the partition. It is far cheaper than a big DELETE + VACUUM.
Lay the archive out by period (archive/year=2021/…) so you can read one period or glob a range — I will cover that in a future post.
Keep the export faithful — the source amount is numeric(10,2), so DuckDB writes and reads it as DECIMAL(10,2) natively (a bare numeric falls back to the Postgres executor).
I will show a fully automated information lifecycle management with pg_durable in a future blog post.
Takeaways
-
pg_duckdbon Azure Database for PostgreSQL writes Parquet to Blob viaduckdb.raw_query($$ COPY (…) TO 'az://…' $$) -
read_parquet('az://…')makes archived data queryable again.UNION/join it with the live table in one statement. - You reclaim Postgres storage without losing query access to the history.
In the next post, we will see how to join a live Postgres table with reference data in Blob.
