a curated list of database news from authoritative sources

October 26, 2026

October 19, 2026

October 17, 2026

Franck Pachot

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.

by Franck Pachot

October 16, 2026

Percona Database Performance Blog

“MongoDB was slow on Thursday”: answering the app team with mongo-health

Monday, 9:00. The app team writes: “MongoDB was slow on Thursday afternoon. What happened?” No alert fired, nobody looked at the time, and now it is your problem. The answer is usually in the FTDC. Every mongod writes a sample of its own metrics once per second into diagnostic.data, and MongoDB calls this Full Time … Continued

The post “MongoDB was slow on Thursday”: answering the app team with mongo-health appeared first on Percona.

by Zelmar Michelini
Percona Database Performance Blog

When a Small Change Writes Too Much – How A Linux Kernel Improvement Can Strike You in PXC/Galera Workloads

We’ve recently been dealing with an interesting customer case where a PXC version upgrade from 5.7 to 8.0 seemed to trigger a completely unexpected problem. Even though the workload and MySQL settings did not change, all PXC members began exhibiting sparse but regular bursts of massive disk writes. Yet the MySQL side workload statistics that … Continued

The post When a Small Change Writes Too Much – How A Linux Kernel Improvement Can Strike You in PXC/Galera Workloads appeared first on Percona.

by Przemysław Malkowski

October 15, 2026

Percona Database Performance Blog

JavaScript in MySQL: Extending SQL, Not Moving the Application

At Percona Live Europe 2026, Dmitry Lenev and I presented JavaScript in My Database? Over My Dead Query, the story of component_js_lang, the V8-based JavaScript engine for Percona Server for MySQL 8.4. That experience told us few things: The presentation was seen as “for developers” What most DBA or DevOps heard was “JavaScript in the … Continued

The post JavaScript in MySQL: Extending SQL, Not Moving the Application appeared first on Percona.

by Marco Tusa
Franck Pachot

GDPR data minimization with pg_duckdb: join Azure Blob facts to erasable customer data in Postgres

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 only customer_ref projected. This is the side that is hashed.
  • The left child is READ_PARQUET: 1,998,938 Blob rows, with only customer_ref and amount projected. This is the probe side.
  • The join emits all 1,998,938 facts, then HASH_GROUP_BY reduces 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(#
                                    
                                    
                                    

                                        by Franck Pachot

October 14, 2026

AWS Database Blog - Amazon Aurora

Building Node.js applications with Sequelize ORM and Amazon Aurora DSQL

Node.js teams can run Sequelize ORM on Amazon Aurora DSQL with only a few adaptations. This post builds a hotel reservation application that shows reusable patterns: UUID primary keys, IAM authentication through the Aurora DSQL connector, individual DDL execution, and optimistic concurrency control retry logic.
by Lasita Bhattacharya
PlanetScale Blog

Sequences and other volatiles in Neki

How Neki evaluates PostgreSQL volatile expressions with the right cardinality to preserve sequence semantics across shards.
by Mats Rydberg
CedarDB Blog

CedarDB Cloud Is Now Generally Available

Today we’re launching CedarDB Cloud, the fully managed way to run CedarDB, our high-performance, PostgreSQL-compatible database for both transactional and analytical workloads. Keep the drivers, tools, and SQL you already know, with all the versatility of a full data stack and none of the trade-offs.

Why CedarDB?

Why CedarDB? Because your application and your analytics shouldn’t need two databases. CedarDB handles all your transactions, dashboards, reports, and AI agents in a single engine, so every query always sees the freshest data. There is no reason to tolerate brittle ETL pipelines or stale copies of your data.

Built from scratch for modern hardware, it puts every core to work on every query, running mixed workloads orders of magnitude faster than PostgreSQL and beats purpose-built analytical systems at their own game.

What’s Included?

CedarDB Cloud delivers all of the functionality of CedarDB with the infrastructure managed for you. Let us take care of provisioning servers, tuning them, and managing upgrades and backups for you. Sign up, create a database, load your data, and connect your favorite tools with just a few clicks.

Fully Managed, Ready in Seconds. We handle provisioning, infrastructure, and day-to-day operations for you. Go from sign-up to a connection string in seconds, then manage and query your data in our focused UI.

Sized for Your Workload. Pick from nine instance types, starting at 2 vCPUs and scaling all the way up to 192 vCPUs and 768 GiB of memory to support even the most demanding production workloads. Storage scales independently of compute, so you always get the right fit.

PostgreSQL-Compatible. Keep your favorite drivers and tools, and migrate your existing PostgreSQL deployment with minimal downtime. Our cookbooks cover a wide range of migration paths, from fully managed transitions via AWS DMS and other change-data-capture tools to classic PostgreSQL tools.

Point-and-Click Management. Handle backups, user access, and more from our UI with just a few clicks, and see your instance’s current configuration at a glance.

Easy Monitoring. Keep an eye on your instance’s health and activity in our always up-to-date interactive charts and drill down on any activity using the interactive shell.

Jump Right in with Our Free Trial

Explore on your own, or follow the short tutorial below.

Get Started and See It in Action

1. Start an Instance

After signing in, start a new instance from the overview page by picking an instance size and name. CedarDB Cloud then takes over, and moments later you already have access to your own instance with a PostgreSQL connection string that you can point your existing PostgreSQL tools at right away.

2. Monitor Your Database

Once your instance is up, switch over to the Monitoring tab to see what is happening on it in real time. Track disk, network, and memory pressure to catch bottlenecks before they affect your applications, or dig into the raw logs for detailed output. To drill down further, the Sessions tab shows you who is running what on your database at any time.

3. Manage Your Deployment

Hop over to the Backup & Restore tab to save the current state of your database for a later restore. Then open the Users & Permissions tab to add users and manage their access with role-based access control, making sure each person and application can only access what it needs.

Pricing and Free Trial

CedarDB Cloud pricing is based on three things:

  1. The instance size you choose
  2. The storage you provision for your instance
  3. Additional backups (three backups are included with every project)

You see the estimated monthly cost for any combination up front, before you create anything. To get an estimate right now, hop over to our pricing calculator. And the first month is free for selected instance sizes, so you can try CedarDB Cloud with your own workload before committing.

Looking for More?

Don’t worry, we’ve got plenty more lined up for CedarDB Cloud in the coming weeks and months. Watch this space for announcements on new management features, such as finer-grained connection controls, scheduled backups, schema management, and more. Rumor has it there might even be a dedicated space to give your agents safe and efficient access to your data.

October 13, 2026

AWS Database Blog - Amazon Aurora

Monitor Amazon Aurora DSQL costs with the Aurora DSQL console and CloudWatch alarms

As your Amazon Aurora DSQL workload scales, DPU consumption and storage costs can grow without a fixed instance ceiling. This post shows how to monitor Aurora DSQL usage and cost with the built-in Usage and cost panel, set up Amazon CloudWatch alarms for hourly DPU spend, and deploy the alarms as code with AWS CloudFormation.
by Tejas Dubey
ParadeDB Blog

Making COUNTs and the Rest of Search Really Fast (Part II)

How ctid-aware segment partitioning, an inverted DocId map, and bitmap postings accelerated ParadeDB's Wikipedia COUNT queries—then a look at the rest of search, where ParadeDB delivered 10–22x TIN's throughput on filtered and time-sorted Hacker News queries.
by Ming Ying
Franck Pachot

Automate the Postgres-to-Blob archive with a durable workflow (pg_durable + pg_duckdb)

In the archive post I moved cold rows out of a Postgres table into Parquet on Azure Blob and kept querying them. The steps were manual: export to Blob, verify the file, delete the rows, record what happened. If the process dies after the export but before the delete, you're stuck — re-run and you re-export (wasteful), skip ahead and you might delete rows that were never safely written (dangerous). That partial-failure gap is exactly what a durable workflow closes.

Azure Database for PostgreSQL ships another open-source Microsoft extension that fits here: pg_durable. It runs checkpointed, crash-safe workflows inside Postgres — define a graph of SQL steps, and the engine persists progress between them. If the database restarts or a step fails, execution resumes from the last completed step instead of starting over. No cron-plus-status-table-plus-worker scaffolding. This post turns the archive into one durable, schedulable workflow, with pg_duckdb doing the write to Blob.

Enabling pg_durable

Like pg_duckdb, pg_durable loads through shared_preload_libraries, so it needs allow-listing and a restart:

  1. Add pg_durable to azure.extensions (the allow-list).
  2. Add pg_durable to shared_preload_libraries (append to the current value, next to pg_duckdb) and restart the server.

Then create both extensions in the database:

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

pg_durable runs its steps in a background worker. Right after a restart that worker takes a moment to come up. The first df.start() can return pg_durable background worker not yet initialized — try again in a moment. Wait a few seconds and retry. A normal (non-superuser) login also needs a one-time grant:

SELECT df.grant_usage(current_user);

A quick smoke test confirms the engine runs:

postgres=> SELECT df.start('SELECT 1 AS ok')
;

 start
----------
 50a27f7d

(1 row)

postgres=> SELECT df.status('50a27f7d')
;

  status
-----------
 completed

(1 row)

postgres=> SELECT df.result('50a27f7d')
;

             result
--------------------------------
 {"rows": [{"ok": 1}], "row_count": 1}

(1 row)

The workflow status, errors, and results are persisted for each step and you can query them with pg_durable functions and tables.

The data to archive

A small orders table spanning three years — the 2023 rows are the cold slice we'll move out:

DROP TABLE IF EXISTS orders;
CREATE TABLE orders AS
SELECT g AS order_id,
       (DATE '2023-01-01' + ((g * 7) % 1095))::date AS order_date,
       ((g % 500) + 1)::numeric(10,2)              AS amount
FROM generate_series(1, 1000) g;

CREATE TABLE IF NOT EXISTS archive_log (
  id            serial,
  archived_year int,
  n_rows        bigint,
  blob          text,
  at            timestamptz DEFAULT now()
);

Here is the current data in this table:

postgres=> -- total number of rows
postgres=> SELECT count(*) FROM orders;
 count
-------
  1000

postgres=> -- the cold slice
postgres=> SELECT count(*) FROM orders 
           WHERE order_date < DATE '2024-01-01'
;
 count
-------
   364

I have a reproducible test case where I want to archive orders from before 2024. I'll create a one-time workflow for that and let it run.

The durable workflow

The whole archive is one df.start():

  • steps are chained with ~>
  • step's result is captured into a variable with |=> 'name'
  • next step can reuse the result as $name: 
SELECT df.start(
  -- 1. count the cold slice, capture it as $n
     $Step1$
        SELECT count(*) AS n FROM orders WHERE order_date < DATE '2024-01-01'
     $Step1$ |=> 'n'
  -- 2. write those rows to Blob as Parquet, through the DuckDB engine
  ~> $Step2$ 
        SELECT duckdb.raw_query(
        $DuckDB$
           COPY (
             SELECT order_id, order_date, amount
             FROM pgduckdb.public.orders
             WHERE order_date < DATE '2024-01-01'
           ) TO 'az://lake/archive/orders_2023.parquet' (FORMAT parquet)
        $DuckDB$
        )
     $Step2$
  -- 3. verify the file reads back, capture the row count as $v
  ~> $Step3$
        SELECT count(*) AS v
        FROM read_parquet('az://lake/archive/orders_2023.parquet')
     $Step3$ |=> 'v'
  -- 4. only now delete the cold rows from Postgres
  ~> $Step4$
        DELETE FROM orders WHERE order_date < DATE '2024-01-01'
     $Step4$
  -- 5. record what happened
  ~> $Step5$
        INSERT INTO archive_log(archived_year, n_rows, blob)
        VALUES (2023, $n, 'az://lake/archive/orders_2023.parquet')
     $Step5$,
  'durable-archive'   -- a label to find the run by
);

Two things about the write step (step 2), both carried over from the archive post:

  • The blob write goes through duckdb.raw_query(), not plain COPY TO 'az://…' so that DuckDB authorizes the write according to its own secret and allow-list — the object-storage controls that PostgreSQL's server-file gate knows nothing about and would otherwise reject.
  • Inside raw_query you're in DuckDB's catalog, so the table is pgduckdb.public.orders, not a bare orders.

And a word on the quoting: the steps are SQL statements declared as text arguments for df.start() and themselves include nested text. This would require multiple levels of double quotes in standard SQL. It is solved easily with PostgreSQL dollar quoting ($$ with an optional label inside).

Watching it run

df.start() returns immediately with an instance id. The worker executes the steps. You can check progress directly with the id or getting the last id with your label:

postgres=> SELECT instance_id, label, status, execution_count
           FROM df.list_instances()
           WHERE instance_id = (SELECT id FROM df.instances
                                WHERE label = 'durable-archive'
                                ORDER BY created_at DESC LIMIT 1)
;
 instance_id |      label      |  status   | execution_count
-------------+-----------------+-----------+-----------------
 b9b24177    | durable-archive | completed |               1

(1 row)

Each step is a node you can inspect — this is the checkpoint record:

postgres=> SELECT node_type, status, query
           FROM df.nodes
           WHERE instance_id = (SELECT id FROM df.instances
                                WHERE label = 'durable-archive'
                                ORDER BY created_at DESC LIMIT 1)
           ORDER BY created_at
;
 node_type |  status   |                                     query
-----------+-----------+-------------------------------------------------------------------------------
 SQL       | completed |                                                                              +
           |           |         SELECT count(*) AS n FROM orders WHERE order_date < DATE '2024-01-01'+
           |           |
 SQL       | completed |                                                                              +
           |           |         SELECT duckdb.raw_query(                                             +
           |           |         $DuckDB$                                                             +
           |           |            COPY (                                                            +
           |           |              SELECT order_id, order_date, amount                             +
           |           |              FROM pgduckdb.public.orders                                     +
           |           |              WHERE order_date < DATE '2024-01-01'                            +
           |           |            ) TO 'az://lake/archive/orders_2023.parquet' (FORMAT parquet)     +
           |           |         $DuckDB$                                                             +
           |           |         )                                                                    +
           |           |
 THEN      | completed |
 SQL       | completed |                                                                              +
           |           |         SELECT count(*) AS v                                                 +
           |           |         FROM read_parquet('az://lake/archive/orders_2023.parquet')           +
           |           |
 THEN      | completed |
 SQL       | completed |                                                                              +
           |           |         DELETE FROM orders WHERE order_date < DATE '2024-01-01'              +
           |           |
 THEN      | completed |
 SQL       | completed |                                                                              +
           |           |         INSERT INTO archive_log(archived_year, n_rows, blob)                 +
           |           |         VALUES (2023, $n, 'az://lake/archive/orders_2023.parquet')           +
           |           |
 THEN      | completed |
(9 rows)

Five SQL nodes, all completed — one per step: count, the raw_query COPY to Blob, the verify read, the DELETE, and the audit insert, in order.

It worked, end to end

As expected by the completed workflow, the archived file is in Blob that I can query though pg_duckdb functions:

postgres=> SELECT count(*) FROM read_parquet('az://lake/archive/orders_2023.parquet')
;
 count
-------
   364

(1 row)

The cold rows are gone from Postgres:

postgres=> SELECT count(*), min(order_date) FROM orders
;
 count |    min
-------+------------
   636 | 2024-01-01
(1 row)

The audit row is written in my custom table:

postgres=> SELECT archived_year, n_rows, blob FROM archive_log ORDER BY at
;
 archived_year | n_rows |                 blob
---------------+--------+---------------------------------------
          2023 |    364 | az://lake/archive/orders_2023.parquet

(1 row)

As we have seen in the previous post, history is still one query away, live rows unioned with the archived file, because pg_duckdb can run queries on heterogenous sources:

postgres=> SELECT yr, count(*) AS n
           FROM (
             SELECT extract(year FROM order_date)::int AS yr FROM orders
             UNION ALL
             SELECT extract(year FROM (r['order_date'])::date)::int
             FROM read_parquet('az://lake/archive/orders_2023.parquet') r
           ) u
           GROUP BY yr ORDER BY yr
;
  yr  |  n
------+-----
 2023 | 364
 2024 | 324
 2025 | 312

(3 rows)

2023 comes from the archived Blob file and 2024 and 2025 from the live Postgres table.

Why durable, not just a cron job

Steps 2→3→4 are export, verify, then delete — and because pg_durable checkpoints after each step, a crash between them is safe:

  • Die after the export but before the delete, and the run resumes at the verify and delete steps on restart. The export is not repeated. The delete is not skipped.
  • A failed step stops the workflow with its state intact in df.nodes, so you can see exactly where it stopped and why — no guessing, no half-done cleanup.

With a plain pg_cron + PL/pgSQL procedure you'd be rebuilding this yourself: a status table, retry counters, and a way to tell "exported but not yet deleted" apart from "nothing done yet." pg_durable keeps that bookkeeping inside Postgres.

Make it nightly

Wrap the same workflow in an eternal loop (@>) that waits on a cron schedule, and the archive runs on its own, forever, surviving restarts. Point the date window at a rolling boundary instead of a fixed year:

SELECT df.start(  
  @> (  
    $Step0$  
       SELECT (date_trunc('year', now()) - interval '1 year')::date AS cutoff  
    $Step0$ |=> 'cutoff'  
    ~> $Step1$  
         SELECT count(*) AS n FROM orders WHERE order_date < $cutoff  
       $Step1$ |=> 'n'   
    -- copy / verify 
    ~> $View$  
         CREATE OR REPLACE VIEW orders_all AS  
           SELECT order_id, order_date, amount FROM orders  
           WHERE order_date >= $cutoff          -- exclude rows about to be deleted  
           UNION ALL  
           SELECT order_id, order_date, amount  
           FROM read_parquet('az://lake/archive/orders_*.parquet')  
   $View$  
    -- delete / log steps, all referencing $cutoff ...  
    ~> df.wait_for_schedule('0 2 * * *')  
  ),  
  'nightly-archive'  
);

I added a step to (re)build a view that reads from the archives and adds the operational data. I'll explain how to query a partitioned data lake in a future post.

Takeaways

  • pg_durable is another open-source extension on Azure Database for PostgreSQL, enabled like pg_duckdb (azure.extensions + shared_preload_libraries + restart), usable in your database database after CREATE EXTENSION pg_durable, and by your user after df.grant_usage(current_user).
  • The archive becomes one df.start() graph: count → write to Blob (duckdb.raw_query) → verify → delete → log, checkpointed between steps.
  • Durability closes the export-then-delete gap: a crash resumes at the next uncompleted step instead of re-exporting or deleting unsafely.
  • @> + df.wait_for_schedule('0 2 * * *') turns it into a nightly job that survives restarts — no external scheduler, no status table.
  • Everything is plain SQL with additional operators to define the workflow.
  • Each step is a transaction, so intermediate results are durable — the workflow continues automatically after a failover.

You can visualize the workflow from VS Code with the PostgreSQL extension by Microsoft

by Franck Pachot