Query a partitioned data lake in Azure Blob with pg_duckdb

A data lake is rarely one file. Once you archive or publish data by period, you get a tree of files — and the trick is querying the whole thing as one table while only reading the parts a query needs. pg_duckdb does both: globs read many files as one, and Hive-style partitioning turns the folder names into columns and lets DuckDB skip files it doesn't need. With read_parquet(), the query itself chooses the file set and reader options, rather than requiring a foreign table for each file. Here are some examples from Azure Database for PostgreSQL.

Setup

Only the Blob secret is needed for this post — everything else reads from the lake:

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
SELECT duckdb.create_azure_secret('<blob connection string>');

A partitioned layout

The convention: a folder per partition, named column=value, each holding one or more Parquet files. Here, four yearly files of sales data:

az://lake/sales_part/
   year=2022/data.parquet
   year=2023/data.parquet
   year=2024/data.parquet
   year=2025/data.parquet

You produce these one file per year. The only new thing here is the folder naming (year=YYYY/). The write from PostgreSQL is covered in the archive post. With the sales table from that post, one year's export looks like this:

SELECT duckdb.raw_query($$
  COPY (
    SELECT * FROM pgduckdb.public.sales
    WHERE sale_date >= DATE '2022-01-01'
      AND sale_date < DATE '2023-01-01'
  ) TO 'az://lake/sales_part/year=2022/data.parquet'
  (FORMAT parquet)
$$);

Note the SELECT before duckdb.raw_query and the pgduckdb.public. catalog prefix: inside raw_query you're in DuckDB's catalog, not PostgreSQL's. Keep this wrapper on the tested Flexible Server: plain PostgreSQL COPY ... TO 'az://...' is rejected by the service's file-COPY guard, even though self-managed pg_duckdb can intercept it. The export also requires permission to execute duckdb.raw_query(). A client-side export and upload is the alternative described in the archive post.

The column=value convention lets the reader interpret the folder names as partition columns, and a wildcard selects the files across those folders.

Read them all with one glob

A * in the path reads every matching file as a single table:

postgres=> SELECT count(*) FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet'
)
;
  count
---------
 8001032
(1 row)

A specific path reads one partition:

postgres=> SELECT count(*) FROM read_parquet(
 'az://lake/sales_part/year=2024/data.parquet'
)
;
  count
---------
 2003093
(1 row)

And an explicit list reads an exact set — note the ARRAY[...] syntax (a bare [...] is DuckDB dialect that the PostgreSQL parser rejects):

postgres=> SELECT count(*) FROM read_parquet(ARRAY[
 'az://lake/sales_part/year=2024/data.parquet',
 'az://lake/sales_part/year=2025/data.parquet'
           ])
;
  count
---------
 4003149
(1 row)

Want to know which files a glob actually touched? Add the filename column:

postgres=> SELECT DISTINCT r['filename']
           FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet',
 filename := true
) r
           ORDER BY 1
;
                  filename
---------------------------------------------
 az://lake/sales_part/year=2022/data.parquet
 az://lake/sales_part/year=2023/data.parquet
 az://lake/sales_part/year=2024/data.parquet
 az://lake/sales_part/year=2025/data.parquet
(4 rows)

(pg_duckdb passes DuckDB's optional arguments with PostgreSQL's named-argument syntax, name := value — so filename := true, hive_partitioning := true.)

Turn the folder name into a column

The partition value lives only in the path, not inside the files. Turn on hive_partitioning and year becomes a real column you can group and filter on:

postgres=> SELECT r['year'] AS year, count(*) AS n
           FROM read_parquet(
 'az://lake/sales_part/year=*/data.parquet',
  hive_partitioning := true
) r
           GROUP BY r['year']
           ORDER BY 1
;
 year |    n
------+---------
 2022 | 1998570
 2023 | 1999313
 2024 | 2003093
 2025 | 2000056
(4 rows)

Because the year is encoded in the layout, you can compute per-year totals even without the files carrying a date column at all.

Partition values are not necessarily text. On this server (PostgreSQL 18.6, pg_duckdb 1.1.0), SELECT pg_typeof(r['year']) over this reader returns bigint: DuckDB infers the type from the folder values. Both r['year'] = 2024 and the quoted literal below work and prune to one file.

When file schemas evolve

If a later year's file adds a column, use union_by_name := true to align columns by name rather than position:

SELECT count(*)
FROM read_parquet(
  'az://lake/sales_part/year=*/data.parquet',
  hive_partitioning := true,
  union_by_name := true
);

Columns absent from a file become NULL for that file's rows. This handles added or missing columns, not arbitrary incompatible type changes.

The payoff: partition pruning, visible in the plan

This is why you partition. Filter on the partition column and DuckDB reads only the matching files. EXPLAIN shows it — and here is the full verbatim plan:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*)
           FROM read_parquet('az://lake/sales_part/year=*/data.parquet',
                             hive_partitioning := true) r
           WHERE r['year'] = '2024'
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.78s               ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │       File Filters:       │
 │       (year = 2024)       │
 │                           │
 │    Scanning Files: 1/4    │
 │    Total Files Read: 1    │
 │                           │
 │       2,003,093 rows      │
 │          (0.11s)          │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 329.131 ms
 Execution Time: 330.574 ms

The lines that matter are Scanning Files: 1/4 and Total Files Read: 1. The glob matched four files, but the year = 2024 filter let DuckDB skip scanning data from three of them — it decided that from the folder names alone (File Filters: (year = 2024)). This does not mean zero listing or metadata requests: resolving the glob and binding the reader can still require them. The 2,003,093 rows at the scan is the one file's actual row count; a count(*) can use Parquet metadata rather than decode every row. With hundreds of monthly partitions, a filter selecting one month can restrict the data scan to that month's files, whether there is one file or several. That's why this layout pays off, and pg_duckdb gives it to you over Azure Blob with the filter sitting in ordinary SQL.

Does abfss:// work on Azure Database for PostgreSQL?

DuckDB's Azure extension supports both Blob (az://) and ADLS Gen2 (abfss://) paths, but the managed server's allow-list comes first. I retested on this Flexible Server with PostgreSQL 18.6 and pg_duckdb 1.1.0:

postgres=> SHOW duckdb.enable_external_access;
 duckdb.enable_external_access
-------------------------------
 off
(1 row)

postgres=> SHOW duckdb.allowed_directories;
 duckdb.allowed_directories
----------------------------
 az://
(1 row)

postgres=> SELECT count(*)
FROM read_parquet('abfss://lake/sales_part/year=2024/data.parquet');
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: Permission Error: Cannot access file "abfss://lake/sales_part/year=2024/data.parquet" - file system operations are disabled by configuration

LINE 1: SELECT count(*) AS count FROM system.main.read_parquet('abfss://lake/sales_part/year=2024...
                                      ^
postgres=>

Both a single-file read and a glob fail, including with the fully qualified abfss://<account>.dfs.core.windows.net/lake/... form. The same reads through az:// succeed. These settings require superuser privileges to change, which this login does not have.

So the two schemes are not interchangeable on this server. The rejection happens in DuckDB's filesystem permission check, before we can test the ADLS endpoint or compare listing performance. Use az:// here; there is no measured basis in this test for recommending abfss:// as faster.

Takeaways

• A glob (year=*/data.parquet) reads a whole partitioned lake as one table. A path reads one partition. ARRAY[...] reads an explicit set.
• hive_partitioning := true turns column=value folder names into queryable columns — group and filter on them like any column.
• Filtering the partition column prunes files before reading: the plan shows Scanning Files: 1/4. This is the main reason partitioning pays off.
• Pass DuckDB's reader options with PostgreSQL named-arg syntax (:=); use ARRAY[...] not [...] for file lists.
• Use union_by_name := true when files add or omit columns across partitions.
• On the tested Flexible Server, az:// is allowed but abfss:// is blocked by configuration, for both single files and globs.

Everything so far has read Parquet. Blob also collects the messier arrivals — vendor CSVs, newline-delimited JSON event streams. Next I'll point read_csv and read_json at those and load them into Postgres with a single statement.