Thread Pool in Percona Server and MySQL (Part 2) The previous Part 1 focused on explaining the idea of thread pool, configuration options and analysis of thread pool efficiency in Percona Server for MySQL 9.7.1-1 against the base line, which is the same server without thread pooling. We recommend reading the post with Part 1 … Continued

The post Thread Pool in Percona Server and MySQL (Part 2) appeared first on Percona.

Over the period, we have witnessed how MySQL versioning evolved from 8.0 to 9.7 LTS, and now it has entered a new era: a calendar-based model. Let’s first take a brief look at how MySQL versioning has evolved over time. Phase 1 – MySQL 8.0 (2018–2026) used a traditional single-track release model, with all new … Continued

The post Meet MySQL 26.7: A New Versioning Era Begins appeared first on Percona.

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, not sales because raw_query runs inside the DuckDB engine, so it sees DuckDB's catalog, not PostgreSQL's search path. pg_duckdb mounts your Postgres tables under the pgduckdb.public.* catalog. A bare sales fails with Catalog Error: Table with name sales does not exist.
• duckdb.query() won't carry this — it only accepts a single SELECT, so the COPY must go through raw_query().
• The write target must be under the az:// allow-list (a local path fails with LocalFileSystem 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 with IO 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_duckdb on Azure Database for PostgreSQL writes Parquet to Blob via duckdb.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.

Atlas Infinite, which dropped in public preview on September 29 alongside MongoDB 9.0, delivers a significant architectural update payload: compute-storage disaggregation. Separating compute from storage is not new, and there are well-known ways to do it. But what makes Atlas Infinite interesting is how MongoDB departs from the standard design to keep WiredTiger's efficiency and to ensure that the shared storage layer never sees customer data unencrypted.

I am giving a high level overview of the architecture here distilling from the MongoDB Engineering blog post by Abhishek Chauhan, who led the effort. For detailed coverage, I refer you to that post. It is a great read, and parts 2 and 3 will drop soon.

Why not earlier, and why now

Disaggregated databases have existed for some time, so why did MongoDB wait? This is mainly because MongoDB didn't need disaggregation to scale out: when a workload outgrew one machine, sharding spread it across many nodes in a seamless manner.  MongoDB was among the first databases to ship sharding with automatic chunk balancing and routing. Since horizontal scaling has been a first-class feature for over a decade,  MongoDB never faced the single-node limit that pushed other systems toward  disaggregation early. That bought us time to do disaggregation right, without regressing on WiredTiger's performance or its security model.

Disaggregation is a good idea for several reasons anyways, and MongoDB adopted it to address customer needs. For one, storage gravity has become the  bottleneck for very large clusters. In Atlas's shared-nothing architecture, each node in a three-node replica set has its own disk with a full copy of the  data. That means, operations like growing storage, adding a replica, or restoring a backup, would require copying the whole dataset. On a large cluster  that can take hours when customers expect minutes. And since compute and  storage come bundled, needing more of one means paying for more of both. This  is why customers want compute and storage to scale independently. Disaggregation helps in right-sizing the customers and giving them flexibility for scaling. Finally, recent agentic development patterns want cheap snapshots,  clones, and branches of production data.

How MongoDB departs from the traditional disaggregated design

The standard approach works something like this. Compute is stateless. It ships a physical redo log (a record of every change to every page) to a quorum-replicated log service. Page servers read that log and rebuild pages by replaying the changes, so they can produce any page as of any point in the log. Object storage sits underneath as the durable copy.

MongoDB adopted much of this approach, but it broke with the traditional design in several places in order to keep WiredTiger's performance characteristics and the security guarantees customers expect.

Let's start with WiredTiger, one of the most sophisticated storage engines in production. WiredTiger achieves high performance by deferring and batching work. It is built around checkpoint-based consistency: rather than logging each page modification one by one, it lets writes accumulate in memory. A page can absorb many mutations and get written to disk just once, at a checkpoint. This amortizes the cost of turning writes into readable state. (This is increasingly where the field is heading as modern engines keep converging on copy-on-write, multi-version, checkpointed designs.) Forcing WiredTiger to log every page change for disaggregation sake would cripple its efficiency.

Secondly, because WiredTiger is lock-free and parallel and evicts pages speculatively, there is no canonical page image: two nodes holding the same data hold different bytes. So "replicate the pages" has no single correct target to replicate toward, which breaks from the traditional disaggregated design.

Finally, MongoDB refused to let shared storage read customer data. A storage tier that rebuilds pages from a log must be able to read them, so security ends up resting on operational controls. The better approach is to treat unencrypted user data as toxic. Holding it is a big responsibility, so keep it encrypted for as much of its lifetime as you can. That matters most when storage is shared across tenants, where one mistake no longer stops at one customer.

Because MongoDB owns the engine, the server, and the cloud service, it could co-design across all three layers. While the traditional design makes storage smart (by reading the data and rebuilding any page on demand), MongoDB went the other way and made storage blind to preserve WiredTiger's efficiency and to guarantee that storage can never read customer data unencrypted.

The architecture

Compute (primary, hot standby, and optional read replicas) runs queries and transactions and keeps only caches and recent changes, all of which can be rebuilt from storage if the node is lost. The shared storage layer, written in Rust, has three parts. A Log Service does only one thing, consensus on the log, which keeps it simple and steady. A Page Service spreads each customer's pages across many servers in every zone, with page materializers feeding it from the log. Object storage is responsible for long-term durability, organized by an Object Index Service.

Difference 1: two logs instead of one. A write is durable once the oplog, MongoDB's logical operation log, is on a majority of Log Service replicas across three zones. Separately, at checkpoint time, WiredTiger emits a phylog of physical page changes, mostly deltas. Page servers organize these by page after receiving them through the log service. This allows WiredTiger to keep its batching. Pages are written once per checkpoint, not once per change. Durability and page materialization are fully separate, with only the oplog on the critical path.

Difference 2: storage never sees your unencrypted data. In Atlas Infinite, data is encrypted on the compute node before any byte goes down, and decrypted only there, inside the customer's network, with keys the customer can hold and revoke. Storage can't see documents, collection names, indexes, or even tenant boundaries. Even obtaining root access to the whole storage fleet would yield an attacker only opaque bytes. Contrast this to the traditional design where storage must read data to rebuild pages, so security is partly "trust us" and partly "best intentions".

Difference 3: fresh replicas without page-level logging. In contrast to the traditional disaggregated approach where storage can build any page at any log point, in MongoDB pages only advance at checkpoints, so a replica reading only shared pages could potentially lag by seconds. This is easy to address however on the standby and read replica. Note that page servers build checkpoint-consistent pages from the phylog. On top of that, the compute node applies the oplog (as it arrives from the Log Service) to a local ingest table. Reads combine both to construct the fresh data. This way MongoDB gets millisecond replica lag while keeping checkpoint batching.

Finally, a word on availability. Every cluster has a hot standby, so a primary failure is a quick handoff rather than starting a new node. Data lives in three forms at once (the replicated log, the page servers, and object storage), the fleet is split into isolated cells so trouble in one can't cascade into the next, and recovery parallelizes across many machines rather than falling to one.

In the previous post, we discussed how pg_duckdb on Azure offers more than just reading Parquet files. It integrates DuckDB's columnar, vectorized engine directly into PostgreSQL. A practical first step requires no data lake: simply point DuckDB at your existing tables and run analytical queries more quickly — at times. This post guides you through enabling this on Azure Database for PostgreSQL (Flexible Server), highlights its advantages, and notes when it might not be as effective. It's a choice driven by your knowledge of the types of analytics you run, not a query planner decision.

Enable pg_duckdb on Azure Database for PostgreSQL

pg_duckdb loads through shared_preload_libraries, so it needs two server parameters:

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

Then, as your normal login:

CREATE EXTENSION pg_duckdb
;

This is the open-source DuckDB extension for PostgreSQL and you have access to all functions.

A table to analyze

I created a table with ten million sales rows spanning over five years. Note that amount is numeric(10,2) with precision, and that detail matters. I come back to it at the end:

postgres=> DROP TABLE IF EXISTS sales
;
NOTICE:  table "sales" does not exist, skipping
DROP TABLE
postgres=> SHOW duckdb.force_execution
;
 duckdb.force_execution
------------------------
 off
(1 row)
postgres=> 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)                                                  A S 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
;

SELECT 10000000

postgres=> VACUUM ANALYZE sales
;

VACUUM

Turning DuckDB on

For a query that touches only regular PostgreSQL tables, pg_duckdb does nothing until you ask. The switch is a session setting:

postgres=> SET duckdb.force_execution = true;
SET

With it on, pg_duckdb hands the query to DuckDB when it can, and you see a single Custom Scan (DuckDBScan) node in EXPLAIN wrapping DuckDB's own plan.

Where it wins: a CUBE over 10M rows

A CUBE across three dimensions computes every combination of channel × country × segment — 2³ grouping sets in one pass:

postgres => SELECT channel_id, country, segment
             , count(*) AS n, sum(amount) AS rev
            FROM sales
            GROUP BY CUBE (channel_id, country, segment)
;

Native PostgreSQL (SET duckdb.force_execution = false) takes about 22 seconds:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT channel_id, country, segment
            , count(*) AS n, sum(amount) AS rev
           FROM sales
           GROUP BY CUBE (channel_id, country, segment)
;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 MixedAggregate (actual time=22501.584..22501.662 rows=216.00 loops=1)
   Hash Key: channel_id, country, segment
   Hash Key: channel_id, country
   Hash Key: channel_id
   Hash Key: country, segment
   Hash Key: country
   Hash Key: segment, channel_id
   Hash Key: segment
   Group Key: ()
   Batches: 1  Memory Usage: 264kB
   Buffers: shared hit=2824 read=87288
   ->  Seq Scan on sales (actual time=0.782..1175.074 rows=10000000.00 loops=1)
         Buffers: shared hit=2824 read=87288
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.247 ms
 Execution Time: 22501.821 ms
(17 rows)

DuckDB (SET duckdb.force_execution = true) is two times faster (look at Total Time, not Execution Time which doesn't include the time in DuckDB):

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT channel_id, country, segment
            , count(*) AS n, sum(amount) AS rev
           FROM sales
           GROUP BY CUBE (channel_id, country, segment)
;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.001 rows=0.00 loops=1)
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT channel_id, country, segment, count(*) AS n, sum(amount) AS rev FROM pgduckdb.public.sales GROUP BY CUBE(channel_id, country, segment)
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 12.46s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │          Groups:          │
 │             #0            │
 │             #1            │
 │             #2            │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#3)          │
 │                           │
 │          216 rows         │
 │          (3.95s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │         channel_id        │
 │          country          │
 │          segment          │
 │           amount          │
 │                           │
 │      10,000,000 rows      │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │                           │
 │      10,000,000 rows      │
 │          (20.87s)         │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.356 ms
 Execution Time: 1.257 ms
(63 rows)

The TABLE_SCAN's 20.87s is aggregated CPU/thread time, not wall-clock time, while Total Time: 12.46s is elapsed wall time. pg_duckdb scans the Postgres heap with parallelism at two layers — Postgres parallel workers feed tuples, and multiple DuckDB threads consume and convert them — so the per-operator timing adds up across threads and can exceed the total elapsed time. Additionally, execution is pipelined: the HASH_GROUP_BY consumes scan output concurrently rather than after it finishes.

Postgres's MixedAggregate processes all 10M rows one-at-a-time through eight grouping sets on a single backend. DuckDB does a single vectorized HASH_GROUP_BY over columnar chunks fed by a parallel scan.

Is the win just the parallel scan, though? It's a fair question — the native CUBE runs single-threaded (grouping sets disable parallel aggregation in PostgreSQL, so there's no Gather in that plan). I checked by forcing the native scan parallel (max_parallel_workers_per_gather = 4): a Gather + Parallel Seq Scan appears, the scan finishes in ~1.9s instead of ~1.3s — and the query still takes 22.8s, no improvement. The reason is that the scan was never the bottleneck: a bare count(*) over this table is ~1.5s whether serial or parallel, so the scan is barely a second of the 22s. The other ~21s is the MixedAggregate maintaining eight grouping sets row by row. That is exactly what DuckDB replaces with one vectorized pass. So the win here is the vectorized aggregation, not parallelism on the scan — parallelizing the scan changes nothing because there was almost nothing to parallelize.

Where it doesn't win: a plain aggregate

Not every query benefits from offloading to DuckDB. A full-table sum is an all-scan with no additional work on top:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*), sum(amount), avg(amount)
           FROM sales
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=11499.774..11507.049 rows=1.00 loops=1)
   Buffers: shared hit=4904 read=85208
   ->  Gather (actual time=11499.441..11507.032 rows=2.00 loops=1)
         Workers Planned: 2
         Workers Launched: 1
         Buffers: shared hit=4904 read=85208
         ->  Partial Aggregate (actual time=11488.395..11488.397 rows=1.00 loops=2)
               Buffers: shared hit=4904 read=85208
               ->  Parallel Seq Scan on sales (actual time=0.764..10322.313 rows=5000000.00 loops=2)
                     Buffers: shared hit=4904 read=85208
 Planning Time: 0.086 ms
 Execution Time: 11507.146 ms
(12 rows)

Native PostgreSQL does this in 11 seconds with a parallel scan. DuckDB doesn't improve that:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT count(*), sum(amount), avg(amount)
           FROM sales
;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT count(*) AS count, sum(amount) AS sum, avg(amount) AS avg FROM pgduckdb.public.sales
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 11.81s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#0)          │
 │          avg(#1)          │
 │                           │
 │           1 row           │
 │          (0.08s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           amount          │
 │           amount          │
 │                           │
 │      10,000,000 rows      │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │    Projections: amount    │
 │                           │
 │      10,000,000 rows      │
 │          (23.45s)         │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.093 ms
 Execution Time: 1.018 ms
(58 rows)

The query is the scan, so DuckDB only adds the cost of pulling rows across the boundary and vectorizing them — including decoding that numeric(10,2) column — while PostgreSQL aggregates its own heap in place with parallel workers. When there's nothing above the scan to vectorize, there's nothing to win.

So the rule of thumb isn't "turn DuckDB on for analytics". It's "use it where there's work above the scan". Cubes, rollups, grouping sets, and window functions are good candidates. Full-table sums, plain scans, and point lookups probably aren't, since PostgreSQL already handles those well in place.

Use pg_hint_plan to force at the query scope

Note that duckdb.force_execution is a session setting, not a per-query hint — once enabled, pg_duckdb routes everything it can through DuckDB. In practice, that means running analytical workloads on a dedicated connection or role with it enabled, rather than toggling it statement by statement. You can also use SET LOCAL to limit the scope to a transaction, but that requires multiple calls.

Alternatively, use pg_hint_plan, which is also available on Azure Database for PostgreSQL:

postgres=> \dconfig pg_hint_plan.enable_hint

 List of configuration parameters
        Parameter         | Value
--------------------------+-------
 pg_hint_plan.enable_hint | on

postgres=> \dconfig duckdb.force_execution

List of configuration parameters
       Parameter        | Value
------------------------+-------
 duckdb.force_execution | off

postgres=> -- the default: do not involve DuckDB for local tables

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT cust_id FROM sales GROUP BY cust_id HAVING COUNT(DISTINCT prod_id) > 1
;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 GroupAggregate (actual time=7863.960..7863.963 rows=0.00 loops=1)
   Group Key: cust_id
   Filter: (count(DISTINCT prod_id) > 1)
   Rows Removed by Filter: 50000
   Buffers: shared hit=4680 read=85411, temp read=44038 written=44159
   ->  Sort (actual time=5251.976..6707.319 rows=10000000.00 loops=1)
         Sort Key: cust_id, prod_id
         Sort Method: external merge  Disk: 176152kB
         Buffers: shared hit=4680 read=85411, temp read=44038 written=44159
         ->  Seq Scan on sales (actual time=0.291..1226.341 rows=10000000.00 loops=1)
               Buffers: shared hit=4680 read=85411
 Planning Time: 0.089 ms
 Execution Time: 9346.363 ms
(13 rows)

postgres=> -- add a hint to force for this query only

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           /*+ SET( duckdb.force_execution on ) */
           SELECT cust_id FROM sales GROUP BY cust_id HAVING COUNT(DISTINCT prod_id) > 1
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT cust_id FROM pgduckdb.public.sales GROUP BY cust_id HAVING (count(DISTINCT prod_id) > 1)
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 2.08s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │             #0            │
 │                           │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │           FILTER          │
 │    ────────────────────   │
 │ (count(DISTINCT prod_id) >│
 │             1)            │
 │                           │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │     count(DISTINCT #1)    │
 │                           │
 │        50,000 rows        │
 │          (0.25s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │          cust_id          │
 │          prod_id          │
 │                           │
 │      10,000,000 rows      │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │        Table: sales       │
 │                           │
 │        Projections:       
                                        by Franck Pachot