DEV Community: Franck Pachot

Patroni, Ubuntu 24.04, and two unplanned PostgreSQL failovers

Franck Pachot — Wed, 04 Nov 2026 13:33:50 +0000

I usually run PostgreSQL on managed services, like Azure Database for PostgreSQL Flexible Server but for once I was running PostgreSQL/Patroni on VMs. An overnight data-loading operation had been running for eight hours when its PostgreSQL connections were lost. The client-side symptoms were:

SSL connection has been closed unexpectedly

An SSL error is a symptom, not a diagnosis. PostgreSQL had not run out of memory or disk. The server was being stopped by service management. Neither PostgreSQL nor Patroni needed a package upgrade, but an unattended zlib shared-library update caused the issue.

Ubuntu 24.04's needrestart automatically restarted affected services, including my custom Patroni service. The primary stopped, another node was promoted, and the load failed. Then the new primary received its own unattended updates and stopped too, causing a second failover.

What was running

This was a self-managed PostgreSQL 17/Patroni lab on Azure:

Three PostgreSQL nodes: pg1, pg2 and pg3, across three availability zones.
Three separate etcd nodes for Patroni's distributed configuration store.
One application client VM.
Canonical Ubuntu 24.04 LTS guests.

Hostnames, service prefixes, and local policy filenames below are consistently anonymized. Timestamps, package versions, and database messages are unchanged.

The deployment record identifies the image as Canonical:ubuntu-24_04-lts:server:latest, resolved to 24.04.202610210, with Azure patchMode=ImageDefault and assessmentMode=ImageDefault. Those settings did not disable maintenance already configured inside Ubuntu.

Root cause analysis

I correlated PostgreSQL, Patroni, and systemd messages from the same journal. PostgreSQL's stderr was captured under the Patroni unit here. A patroni[PID] journal prefix does not mean every message was generated by Patroni itself: the PostgreSQL LOG and FATAL messages identify the database side.

On pg1:

sudo journalctl --utc --no-pager -o short-iso \
  --since '2026-11-04 06:18:50 UTC' \
  --until '2026-11-04 06:20:20 UTC' \
  -u franckpachot-lab-patroni.service |
  grep -E 'Stopping |Stopped |Started |shutdown request|administrator command'

Selected captured lines, with repeated connection-termination messages omitted:

2026-11-04T06:19:04+00:00 franckpachot-lab-pg1-kc-20261103 patroni[46600]: 2026-11-04 06:19:04 UTC-6aea534b.b608-LOG:  received smart shutdown request
2026-11-04T06:19:04+00:00 franckpachot-lab-pg1-kc-20261103 patroni[46623]: 2026-11-04 06:19:04 UTC-6aea534c.b61f-FATAL:  terminating connection due to administrator command
2026-11-04T06:19:04+00:00 franckpachot-lab-pg1-kc-20261103 systemd[1]: Stopping franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab...
2026-11-04T06:19:06+00:00 franckpachot-lab-pg1-kc-20261103 patroni[46600]: 2026-11-04 06:19:06 UTC-6aea534b.b608-LOG:  received fast shutdown request
2026-11-04T06:20:14+00:00 franckpachot-lab-pg1-kc-20261103 systemd[1]: Stopped franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab.
2026-11-04T06:20:14+00:00 franckpachot-lab-pg1-kc-20261103 systemd[1]: Started franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab.

This is positive evidence of an administered shutdown and a service restart, not merely an inference from lost connections. "Administrator command" does not identify a human: automated service management can produce it.

There is a service-unit detail worth checking as well:

systemctl show franckpachot-lab-patroni.service \
  -p KillMode -p KillSignal

The follow-up inspection returned:

KillMode=control-group
KillSignal=15

My custom unit had no explicit KillMode setting. With control-group, systemd's stop signal is not restricted to the Patroni parent process. Service-unit shutdown semantics matter too. I should not assume that restarting the supervisor leaves PostgreSQL untouched.

Prove that unattended upgrades actually ran

The familiar status command was a useful starting point:

systemctl status unattended-upgrades.service --no-pager

Before remediation, it showed:

Loaded: loaded (/usr/lib/systemd/system/unattended-upgrades.service; enabled; preset: enabled)
Active: active (running) since Tue 2026-11-03 16:25:04 UTC; 15h ago
Main PID: 1641 (unattended-upgr)
/usr/bin/python3 /usr/share/unattended-upgrades/unattended-upgrade-shutdown --wait-for-signal

This does not prove packages were being installed at that moment. That particular process waits to coordinate shutdown with unattended upgrades. The decisive evidence comes from the scheduled service and transaction logs.

On pg1:

sudo journalctl --utc --no-pager -o short-iso \
  --since '2026-11-04 06:10:00 UTC' \
  --until '2026-11-04 06:25:00 UTC' \
  -u apt-daily-upgrade.service

Selected captured lines:

2026-11-04T06:18:58+00:00 franckpachot-lab-pg1-kc-20261103 systemd[1]: Starting apt-daily-upgrade.service - Daily apt upgrade and clean activities...
2026-11-04T06:20:31+00:00 franckpachot-lab-pg1-kc-20261103 systemd[1]: Finished apt-daily-upgrade.service - Daily apt upgrade and clean activities.

The unattended-upgrades log supplies the package list:

sudo grep -E '^2026-11-04 06:.*(Starting unattended|Packages that will be upgraded|All upgrades installed)' \
  /var/log/unattended-upgrades/unattended-upgrades.log

The first transaction's relevant lines:

2026-11-04 06:18:58,776 INFO Starting unattended upgrades script
2026-11-04 06:19:02,465 INFO Packages that will be upgraded: bind9-dnsutils bind9-host bind9-libs libbrotli1 libmspack0t64 libnss3 libpcre2-8-0 zlib1g
2026-11-04 06:20:30,481 INFO All upgrades installed

This was automated package maintenance in the exact incident window.

What package changed, and when?

The ordinary dpkg database log gives timestamped package actions:

sudo grep -E '^2026-11-04 06:19:.*(upgrade zlib1g|configure zlib1g)' \
  /var/log/dpkg.log

Captured output:

2026-11-04 06:19:03 upgrade zlib1g:amd64 1:1.3.dfsg-3.1ubuntu2.2 1:1.3.dfsg-3.1ubuntu2.3
2026-11-04 06:19:03 configure zlib1g:amd64 1:1.3.dfsg-3.1ubuntu2.3 <none>

This proves dpkg performed the package upgrade/configuration. It is not a process-audit record of dpkg's full command-line arguments.

Where did the `systemctl restart` command come from?

Not from /var/log/dpkg.log. The literal command appears in the captured package-manager output:

sudo sed -n \
  '/^Log started: 2026-11-04  *06:19:02/,/^Log ended:/p' \
  /var/log/unattended-upgrades/unattended-upgrades-dpkg.log

Selected lines from that transaction:

Log started: 2026-11-04  06:19:02
Preparing to unpack .../zlib1g_1%3a1.3.dfsg-3.1ubuntu2.3_amd64.deb ...
Setting up zlib1g:amd64 (1:1.3.dfsg-3.1ubuntu2.3) ...
Restarting services...
 systemctl restart azuremonitor-coreagent.service franckpachot-lab-host-metrics.service franckpachot-lab-patroni.service fwupd.service gcd.service polkit.service rsyslog.service ssh.service udisks2.service walinuxagent.service
Log ended: 2026-11-04  06:20:15

The custom Patroni service is explicitly named.

These logs also print commands under a deferred-restarts heading elsewhere. A bare grep for systemctl restart is not sufficient: inspect the heading and surrounding transaction. Here the Patroni command is under Restarting services..., and systemd independently records that exact
service stopping and starting.

The output does not timestamp each printed command. I assign 06:19:04 to the observed Patroni stop/PostgreSQL shutdown from the journal.

Together, the transaction output and systemd journal establish the restart path. I did not have an auditd/execve trace recording every parent PID and argument, so this is log correlation, not a reconstructed process audit.

Why would a zlib upgrade restart Patroni?

The installed APT hook answers that question:

cat /etc/apt/apt.conf.d/99needrestart

Its active configuration line was:

DPkg::Post-Invoke {"test -x /usr/lib/needrestart/apt-pinvoke && /usr/lib/needrestart/apt-pinvoke -m u || true"; };

The invoked script ultimately executes:

    exec /usr/sbin/needrestart "$@"

needrestart checks running processes for outdated shared libraries. A service can therefore be selected without its own package being upgraded. It is not simply walking package dependencies.

The explanation was already installed on the VM:

cat /usr/share/doc/needrestart/README.Ubuntu

The first paragraph:

Starting from Ubuntu 24.04, needrestart in the default configuration will by default not show the Debconf prompt, and restart automatically the affected services. This includes when run as part of `unattended-upgrades` to ensure that the security updates it brings are actually applied to running services
rather than leaving the system vulnerable.

This is a documented Ubuntu 24.04 behavior, not a claim about every Ubuntu release or every customized Azure image. Canonical describes the change and its security rationale in
needrestart changes in Ubuntu 24.04: service restarts. While this may be great for services that are resilient to restarts, it's probably not a good idea for a monolithic database such as PostgreSQL, where restarting the primary interrupts all client sessions.

Updating a library on disk does not update the copy already mapped into a running process. Restarting affected services applies the fix. The missing piece in my setup was coordinating that legitimate maintenance with HA and a long-running load.

But why would Patroni have zlib loaded?

A direct import zlib in Patroni's own source is not required. Patroni runs inside Python, and imported modules or native dependencies can load shared libraries transitively. Python's zlib extension is one possible route: modules such as gzip and zipfile can use it. A native dependency of another
component in the service is another possibility.

However, being a Python process does not automatically mean that zlib is loaded at startup. On typical distribution builds, the Python zlib module is a dynamically loaded extension, not evidence that the interpreter itself unconditionally links and loads libz. The actual imports and native dependencies matter.

needrestart examines running processes, including their memory mappings, to identify obsolete library files and associate affected processes with services. It does not need to prove that application code is actively calling the library. A process can keep an old library mapped after an upgrade replaces the on-disk file.

There is another distinction: the Patroni systemd service also contains PostgreSQL processes. Selecting that unit does not, by itself, prove that the Patroni Python parent, rather than a process it manages, held the obsolete mapping.

For a running service, investigate with:

pid=$(systemctl show franckpachot-lab-patroni.service -p MainPID --value)
sudo grep -E 'libz\.so|deleted' "/proc/$pid/maps"
systemctl status franckpachot-lab-patroni.service --no-pager

Inspect the mappings of the other processes in the service as well. Use ldd on the relevant executable or native extension to investigate library dependencies, remembering that ldd alone does not list every library loaded later at runtime. These are diagnostic commands, not captured historical output: after a restart, the old mappings are gone.

The retained evidence establishes: zlib upgrade -> immediate restart list containing Patroni -> observed service shutdown -> failover. It does not include a pre-restart memory-map snapshot identifying the precise importing module or process, so I would not present a particular Python dependency chain as proven.

Nor would I explain it as zlib enabling compressed PostgreSQL connections: PostgreSQL's standard client/server protocol does not provide general-purpose compression of that kind. Any proposed libpq dependency path needs evidence from the installed build, not that assumption.

What about other releases and distributions?

There are three separate policy questions:

Are updates checked or downloaded automatically?
Are packages actually installed automatically?
Which running services are restarted afterward, and by whom?

A timer being enabled does not answer all three. Neither does an
Automatic-Reboot setting.

Ubuntu before and after 24.04

Canonical's description of the 24.04 change explicitly contrasts it with the previous needrestart behavior:

Before 24.04, the APT-integrated tool prompted for service restarts in interactive use. In noninteractive use, it listed affected services without restarting them. This is the pre-Noble behavior relevant when comparing older releases such as 20.04 and 22.04, where the tool is installed and configured that way.
Ubuntu 24.04 changed that APT-hook behavior to restart affected services directly in both contexts, including unattended upgrades. That is the behavior documented and observed on these VMs.
Other or later releases and customized images need their own checks. An upgraded host can retain configuration overrides, and a cloud image can add automation. Do not infer the effective policy from the release name alone.

The older behavior was not a promise that package upgrades could never restart a service: package maintainer scripts and other automation are separate paths. An older release also doesn't mean unattended security updates were disabled. The relevant change here is what happens to affected running processes after an APT transaction.

On an Ubuntu or Debian host, inspect rather than guess:

cat /etc/os-release
dpkg-query -W unattended-upgrades needrestart
apt-config dump | grep -E 'APT::Periodic|Unattended-Upgrade::Automatic-Reboot'
systemctl list-timers --all 'apt-*' --no-pager
cat /etc/apt/apt.conf.d/99needrestart
grep -R -n 'nrconf.*restart' /etc/needrestart

Debian, RHEL and Fedora

These are documented policy differences:

System	Automatic package installation	What to check for service disruption
Debian, including 12/13 deployments	`unattended-upgrades` is controlled by APT periodic configuration and installer/image choices. Do not assume every installation enables it.	Inspect the installed `needrestart` version, mode, hooks, and package scripts. Sharing APT does not establish that Ubuntu's specific `-m u` policy applies.
RHEL 9	Red Hat documents installing `dnf-automatic` and enabling a timer. By default, it downloads and reports updates without installing them.	The selected timer matters: `dnf-automatic-install.timer` explicitly installs updates and overrides the corresponding configuration options. Inspect package scripts and any external maintenance tooling too.
Fedora 41 and newer, traditional DNF-based systems	Fedora documents that the automatic-update component is not installed by default; after installation, its default policy downloads without installing. The documented setup uses `dnf5-plugin-automatic` and an explicitly enabled timer.	Check `/etc/dnf/automatic.conf`, the timer, and the restart/reboot policy. Desktop offline updates and image-based/Atomic systems are different update paths.

Sources:

For the documented DNF-based setups, useful inspection commands include:

systemctl list-timers --all 'dnf*' --no-pager
grep -E '^[[:space:]]*(apply_updates|download_updates|reboot)[[:space:]]*=' \
  /etc/dnf/automatic.conf

Defaults are often bad for critical systems such as monolithic databases. None of these host-local tools automatically knows which PostgreSQL node is the current leader or whether taking down another replica is safe. The HA maintenance policy still has to be designed for the full stack.

Every VM had its own maintenance window

The effective configuration enabled daily unattended upgrades:

cat /etc/apt/apt.conf.d/20auto-upgrades
apt-config dump | grep -E 'APT::Periodic::(Update-Package-Lists|Unattended-Upgrade)'

The configuration file contained:

APT::Periodic::Update-Package-Lists "1";
APT::Periodic::Unattended-Upgrade "1";

The packaged timer definition, captured on all seven hosts, contained:

cat /usr/lib/systemd/system/apt-daily-upgrade.timer

[Unit]
Description=Daily apt upgrade and clean activities
After=apt-daily.timer

[Timer]
OnCalendar=*-*-* 6:00
RandomizedDelaySec=60m
Persistent=true

[Install]
WantedBy=timers.target

The guests used UTC. Each machine independently scheduled maintenance around 06:00, with up to a 60-minute randomized delay. Persistent=true also permits catch-up after a missed timer activation. This is not a cluster-aware rolling
maintenance policy.

I checked the unattended-upgrades logs and dpkg logs on all seven VMs:

VM	Unattended upgrades started, UTC	zlib upgrade, UTC	Observed effect on the owned workload service
etcd2	06:09:38.423	06:09:48	No etcd restart recorded; process start remained November 3
pg1	06:18:58.776	06:19:03	Patroni stop at 06:19:04; first primary lost
pg3	06:19:28.899	06:19:44	Patroni stop at 06:19:45; standby also taken down
etcd1	06:24:02.614	06:24:09	No etcd restart recorded; process start remained November 3
client	06:26:39.054	06:26:44	Host-metrics service in restart list; load had already failed
etcd3	06:35:02.180	06:35:20	No etcd restart recorded; process start remained November 3
pg2	06:38:01.956	06:38:16	Patroni stop at 06:38:17; replacement primary lost

All seven listed the same set of packages for upgrade. Every PostgreSQL node's dpkg-output log explicitly included franckpachot-lab-patroni.service in the immediate service-restart list.

"Around the same time" means the same maintenance hour, not simultaneous restarts. pg1 and pg3 stopped only 41 seconds apart. pg2 stopped 19 minutes and 13 seconds after pg1, by which point it had become the primary.

Random jitter spread the jobs out. It did not prevent maintenance from overlapping recovery or targeting the new leader.

Patroni and PostgreSQL logs show two failovers

Run this on each PostgreSQL VM:

sudo journalctl --utc --no-pager -o short-iso \
  --since '2026-11-04 06:18:50 UTC' \
  --until '2026-11-04 06:45:00 UTC' \
  -u franckpachot-lab-patroni.service |
  grep -E 'Stopping |Stopped |Started |promoted self|selected new timeline'

On pg2, the first promotion is explicit:

2026-11-04T06:19:29+00:00 franckpachot-lab-pg2-kc-20261103 patroni[47953]: 2026-11-04 06:19:29,047 INFO: promoted self to leader by acquiring session lock
2026-11-04T06:19:29+00:00 franckpachot-lab-pg2-kc-20261103 patroni[48105]: 2026-11-04 06:19:29 UTC-6aea538b.bbe9-LOG:  selected new timeline ID: 23

pg3 was then stopped by its own maintenance:

2026-11-04T06:19:45+00:00 franckpachot-lab-pg3-kc-20261103 systemd[1]: Stopping franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab...
2026-11-04T06:21:15+00:00 franckpachot-lab-pg3-kc-20261103 systemd[1]: Started franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab.

Later, maintenance caught up with pg2, now the primary:

2026-11-04T06:38:17+00:00 franckpachot-lab-pg2-kc-20261103 systemd[1]: Stopping franckpachot-lab-patroni.service - franckpachot-lab PostgreSQL Patroni quorum lab...

Two seconds later, pg3 promoted:

2026-11-04T06:38:19+00:00 franckpachot-lab-pg3-kc-20261103 patroni[101738]: 2026-11-04 06:38:19,889 INFO: promoted self to leader by acquiring session lock
2026-11-04T06:38:19+00:00 franckpachot-lab-pg3-kc-20261103 patroni[101843]: 2026-11-04 06:38:19 UTC-6aeacedd.18dd3-LOG:  selected new timeline ID: 24

The resulting leadership sequence was:

pg1 / timeline 22
  -> pg2 / timeline 23 at 06:19:29
  -> pg3 / timeline 24 at 06:38:19

Patroni uses the DCS leader lock to coordinate leadership. Stopping a leader can release the lock or expire the lease, allowing an eligible replica to acquire leadership. These logs prove the promotions and new timelines. They do not by themselves establish whether a particular lock was explicitly deleted or expired after ttl, as that would require additional
lease/key history.

For planned PostgreSQL restarts, Patroni provides patronictl restart and the REST /restart endpoint. Restarting the Patroni systemd service is not the same as a restart. A controlled maintenance plan may also require a deliberate switchover, replica checks, and application reconnection handling. HA does not make existing client sessions survive a primary shutdown.

Why "automatic reboot disabled" did not protect me

I initially looked at:

grep Reboot /etc/apt/apt.conf.d/50unattended-upgrades

It showed:

//Unattended-Upgrade::Automatic-Reboot "false";
// when Unattended-Upgrade::Automatic-Reboot is set to true
//Unattended-Upgrade::Automatic-Reboot-WithUsers "true";
//Unattended-Upgrade::Automatic-Reboot-Time "02:00";

Those are commented examples/default documentation, not active assignments. Use apt-config dump to inspect effective overrides rather than treating a comment as the final configuration.

More importantly, a service restart is not a VM reboot. That option controls unattended-upgrades' automatic reboot behavior, not needrestart's service-restart behavior.

To check the distinction:

uptime -s
journalctl --list-boots --no-pager
last -x reboot | head

The captured pg1 boot time remained:

2026-11-03 16:24:54

The PostgreSQL VMs' boot times all preceded the incident. The observed 06:19 event was a service shutdown/restart, not a fresh guest boot.

Durability is not availability

Even with healthy replicas, sufficient retained/archived WAL, and successful node rejoin, these unplanned failovers would still be an availability incident. Recovering the data does not recover the application sessions that were connected to the stopped primary.

It helps to separate three questions:

Durability / RPO: which acknowledged transactions survive?
Availability / RTO: when can the application successfully execute work again?
Session and transaction continuity: what happens to connections, in-flight transactions, and long-running operations?

WAL archiving is important for recoverability, but it does not preserve TCP connections or, by itself, guarantee zero data loss. The failover RPO depends on replication, commit settings, and which replica can safely be promoted. The point here is not a claim about lost committed data: even a zero-data-loss
failover can interrupt every session connected to the old primary.

A stable endpoint, virtual IP, or load balancer can direct new connections to the new leader. It cannot move an existing PostgreSQL session, its transaction, and its session state to another server. A proxy may keep a frontend socket open, but that is not a general guarantee that the backend transaction survives.

The logs show about 25 seconds between pg1's shutdown request and pg2's promotion, and about two seconds between pg2's shutdown and pg3's promotion. Those are server event intervals, not measured application downtime. Endpoint health checks, connection timeouts, pool recovery, retries, and transaction replay can extend the interruption.

For an application whose database sessions are terminated, that interruption is downtime, no matter how quickly a dashboard turns green again.

Resilience includes controlling avoidable failures

Three availability zones protect against some infrastructure failures. They do not isolate nodes from the same package update and the same host-local restart policy.

This was a correlated operational failure: the same maintenance mechanism ran independently on every VM, with no cluster-level gate. Random delay is not coordination. The second restart did not know that its target had just become the replacement primary.

Automatic failover remains valuable for actual failures. But it is a recovery mechanism, not permission to turn routine maintenance into repeated unplanned outages. The objective should be to avoid unnecessary failovers, not merely celebrate that promotion worked twice.

For resilience, I want both sides of the contract:

Cluster-aware maintenance. Patch replicas in a controlled sequence, verify health and catch-up before proceeding, preserve enough capacity for the configured synchronous-replication policy, and coordinate any leader transition. Apply equivalent quorum-aware sequencing to etcd. One host at a time is a starting point, not proof that every quorum configuration can tolerate that host being unavailable.
Application-aware recovery. Exercise connection-pool recovery and bounded retries with backoff. Retry the right transaction or resumable unit of work, not an arbitrary fragment of a transaction. Handle uncertain commit outcomes with idempotency or reconciliation rather than assuming that a lost connection means nothing committed.
Application-level measurement. Test successful requests, error rates, latency, and time to resume useful work, not just leader election. Include maintenance scenarios where a replica is already unavailable.
An explicit disruption budget. Define how many nodes may be unavailable and which checks must pass before continuing maintenance. Abort the rollout if recovery is incomplete rather than letting another timer fire.

A planned switchover can still disconnect clients. "Planned" does not mean "invisible." It makes the timing, sequencing, application handling, and expected impact deliberate. That is materially different from two independent package jobs unexpectedly stopping successive primaries.

What I changed to avoid the issue happening again

For this lab, I chose explicit maintenance windows rather
than independent automatic updates on seven machines.

First, I checked for active package transactions. I did not kill a running dpkg operation. Then I stopped, disabled, and persistently masked the named automatic-update units on all seven VMs:

sudo systemctl disable --now \
  apt-daily.timer apt-daily-upgrade.timer unattended-upgrades.service
sudo systemctl mask \
  apt-daily.timer apt-daily-upgrade.timer unattended-upgrades.service

I also set needrestart to list-only mode, so a later normal APT transaction would report affected services rather than automatically restarting them:

printf '%s\n' '$nrconf{restart} = "l";' |
  sudo tee /etc/needrestart/conf.d/zz-franckpachot-lab-list-only.conf

The earlier targeted exclusions for the owned database, DCS, and supporting services remain in place. The broader list-only setting was an explicit subsequent decision, not the initial policy.

The rollout verified each host's effective configuration, not just the existence of a new file. Every host reported:

VERIFIED: effective needrestart restart mode=l (list only)
apt-daily.timer enabled=masked active=inactive
apt-daily-upgrade.timer enabled=masked active=inactive
unattended-upgrades.service enabled=masked active=inactive

To inspect unit state:

systemctl show apt-daily.timer apt-daily-upgrade.timer unattended-upgrades.service \
  -p Id -p UnitFileState -p ActiveState

The effective needrestart configuration was verified by loading its main Perl configuration, which includes the sorted drop-ins, and checking that $nrconf{restart} resolves to l. On the installed version, an explicit restart-mode setting is honored by the APT hook's -m u mode.

I also persisted the policy in provisioning and added a pre-operation check across all seven VMs. It refuses to proceed if maintenance protection cannot be verified. Fixing running machines without fixing their replacement images would only postpone the same surprise.

This is not a recommendation to abandon security updates. It moves the responsibility to an explicit maintenance procedure:

Agree on a maintenance window and the acceptable application interruption.
Verify replication, DCS health, and recoverability.
Patch in a controlled sequence, coordinating service restarts and any necessary switchover through Patroni.
Verify the cluster and apply the restarts needed to use updated libraries.
Reverify maintenance protection and application readiness afterward.

The masks block these named systemd activation paths. List-only mode blocks needrestart's default automatic restart path. Neither is a universal barrier against explicit mode overrides, package maintainer scripts, manual restarts, VM reboots, or separately configured Azure patch orchestration.

The lesson

The failure domain was not just a VM, a disk, or an availability zone. It was also the same maintenance policy installed independently on every node.

Ubuntu was applying security fixes as designed. Patroni performed failovers when primaries stopped. The missing piece was coordination: routine maintenance should have been a planned operation, not a sequence of independently triggered primary failures.

Durable data is essential, but it is not the whole HA contract. If every connection to the primary is dropped and applications must recover their work, availability has been affected. Resilience means reducing that impact, preventing avoidable disruptions, and electing a new leader.

A database service spans layers that make decisions independently. In this case, Ubuntu restarted a service after a package upgrade, and Patroni responded to the resulting primary shutdown with a failover. Each did what it was configured to do, but the disruption stemmed from the lack of coordination between them. Kubernetes-based deployments can encounter similar interactions between controllers, as I explored in an earlier CloudNativePG transient-failure test. A managed service may use some of the same components, but the key difference is whether maintenance, failover, and client recovery are designed and tested together. PostgreSQL has no built-in high availability, but a managed service as a whole can provide it.

PostgreSQL document compression: TOAST thresholds, storage policies, and page size

Franck Pachot — Mon, 02 Nov 2026 21:14:00 +0000

A common way to store documents in PostgreSQL is a table with an ID and a JSONB column. The open-source DocumentDB extension instead stores BSON, the binary document format used by MongoDB, to provide more data types and MongoDB compatibility.

Both approaches commonly combine two patterns that do not perfectly fit what SQL databases were designed for:

Rows with a single variable-length column, whose values can range from a few dozen bytes to many megabytes.
Repeated data or metadata, such as similar field names and arrays of objects with the same schema, like time-series buckets.

MongoDB's storage engine, WiredTiger, packs many documents into B-tree leaf pages. The default maximum page sizes — 32 KB for leaf pages and 4 KB for internal pages — are limits on the uncompressed page image fed to the block compressor, not on individual documents: MongoDB configures leaf_value_max=64MB, so a document larger than 32 KB simply produces a larger leaf page rather than overflow storage. Snappy compresses each page image for ordinary collections (time-series collections use Zstd, which ignores the global compressor setting), and the compressed result is written in multiples of the 4 KB allocation_size. Because compression runs over the whole page image, several small documents — including their repeated field names — contribute redundant byte sequences to the same compression input.

A JSON or BSON document can contain substantial byte-sequence redundancy: repeated field names, common name prefixes, and repeated values or structures. LZ77-family compressors such as Snappy and LZ4 can replace matching sequences with backreferences to earlier bytes. They don't need to understand JSON, and matches aren't restricted to the beginning of a document.

Binary does not mean compressed: BSON includes field names, and repeated embedded objects repeat those names. JSONB can contain similar redundancy. PostgreSQL can compress those sequences in JSON, JSONB, or a BSON extension type. However, the decision to attempt compression is not driven by document redundancy. It belongs to TOAST's generic heap-row storage policy: reduce a wide row, trying column compression before storing permitted attributes externally in chunks.

This distinction matters for small documents. It also makes increasing the page size potentially counterproductive: the same document can stop being compressed on a larger-page build.

I will reproduce both effects, then compare the SQL controls with the hardcoded values. The examples use PostgreSQL 19 and core JSONB, so they can run without an extension or a custom patch. The distinction between whole-row activation and individual-column compression also applies to the TOAST-backed BSON values stored by DocumentDB.

What is compressed, and what is measured?

TOAST measures the whole tuple (row) to decide whether to start. However, compression acts on individual eligible column values, not on the entire row or all the documents in a page.

PostgreSQL's ordinary table storage uses fixed-size pages, 8 KB by default. A row cannot span those pages. Large column values can instead be moved to an associated TOAST table, and the main row then contains a pointer to access the chunks in the TOAST table by index scan.

JSONB still contains compressible byte sequences. It is a binary representation, not an already-compressed JSON string. Within one object, it normalizes key order and duplicate keys, but an array of similarly structured objects can repeat the same field names. No shared key dictionary exists across those objects or across independently stored JSONB rows.

The distinction between repeated names within a document and repeated names across documents is important. PostgreSQL compresses one column value at a time. PostgreSQL calls each value a datum in its source code. That per-datum compression can use the former repetition, not the latter.

MongoDB's WiredTiger storage engine uses Snappy block compression on disk by default for ordinary collections. Small documents can share a page, so one block-compression input can include repeated names and structures from several documents. This is not a separate compression call per small document. Other codecs can be configured, and time-series collections have different defaults. This is a difference in compression scope, not a capability reserved to Snappy.

This post measures stored datum representations, not table allocation or query performance. I use:

pg_column_size(doc::text::jsonb) to reconstruct an uncompressed logical JSONB datum for these ordinary JSON inputs
pg_column_compression(doc) to report the actual accepted method
pg_column_size(doc) for its stored representation
pg_column_toast_chunk_id(doc) IS NOT NULL to identify external values

The last function is available in PostgreSQL 19. For an external value, pg_column_size is not the inline pointer size. The on-disk pointer in the main row occupies 18 bytes. The external size excludes the outer four-byte varlena length word, which explains some four-byte differences below without attributing them to the codec.

Reproduce the environment and output

All SQL needed for the experiments is included below. Run the statements in order in a fresh test database. For each page-size comparison, start a separate PostgreSQL instance.

I use aligned ASCII output, with NULL displayed as (null). To reproduce that format, start an existing server's session with these options:

psql -X -P pager=off -P 'null=(null)' -P format=aligned \
  -P linestyle=ascii -P border=1 -U postgres -d postgres

These are the initial settings on the source-built 8 KB server:

postgres=# SHOW server_version;

 server_version 
----------------
 19rc1
(1 row)

postgres=# SHOW block_size;

 block_size 
------------
 8192
(1 row)

postgres=# SHOW default_toast_compression;

 default_toast_compression 
---------------------------
 lz4
(1 row)

The selected codec is explicit in every test. PostgreSQL 14-18's upstream default is pglz, but an LZ4-enabled PostgreSQL 19 RC1 build defaults to LZ4. SET COMPRESSION has been available since PostgreSQL 14.

LZ4 is a sensible starting choice when compression/decompression CPU matters, but the workload still determines the trade-off. It does not always produce the smallest result. DocumentDB's current setup/tuning configuration selects default_toast_compression = 'lz4' when supported. This is a launcher-chosen PostgreSQL setting, not a property of BSON, nor a guarantee that every installation is configured that way. Check the live server and column settings rather than assuming them from a product name.

A compressible document below the activation threshold

I construct a JSONB array containing eight objects with repeated temperature and humidity names. Both rows receive the same document. The only difference is an unrelated column stored PLAIN, so it cannot be compressed or moved externally.

postgres=# CREATE TABLE small_documents(
    id integer, doc jsonb, other text
);

CREATE TABLE

postgres=# ALTER TABLE small_documents
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE small_documents
            ALTER COLUMN other SET STORAGE PLAIN
;

ALTER TABLE

postgres=# INSERT INTO small_documents
    SELECT id,
           jsonb_build_object('samples', (
             SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
             FROM generate_series(1,8)
           )),
           repeat('q', CASE id WHEN 1 THEN 0 ELSE 1600 END)
    FROM generate_series(1,2) AS g(id)
;

INSERT 0 2

postgres=# SELECT id,
           pg_column_size(doc::text::jsonb) AS logical_bytes,
           octet_length(other) AS other_bytes,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM small_documents ORDER BY id
;

 id | logical_bytes | other_bytes | compression | stored_bytes | external 
----+---------------+-------------+-------------+--------------+----------
  1 |           508 |           0 | (null)      |          508 | f
  2 |           508 |        1600 | lz4         |          109 | f
(2 rows)

The first 508-byte JSONB datum remains uncompressed. The second compresses to 109 bytes, even though its uncompressed document bytes are identical. The additional 1,600-byte column made the tuple large enough to enter TOAST processing.

This proves both points:

Repeated field names and values in JSONB are compressible.
PostgreSQL does not independently ask whether that small JSONB datum would benefit from compression. The enclosing tuple controls activation.

I used an unrelated column for academic purposes. Of course, I'm not suggesting that adding more data to trigger compression is a good idea.

The hardcoded gate is not the per-table target

In the PostgreSQL source, the ordinary values are derived as follows:

#define TOAST_TUPLES_PER_PAGE 4
#define TOAST_TUPLE_THRESHOLD MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD

MaximumBytesPerTuple(4) accounts for page headers, line pointers, and alignment. It is not exactly block_size / 4. Here are the values for the default 8 KB page size and for PostgreSQL builds with 16 KB or 32 KB pages:

PostgreSQL page size	Fixed tuple activation threshold	Default tuple target	Maximum native TOAST chunk payload
8,192 bytes	2,032 bytes	2,032 bytes	1,996 bytes
16,384 bytes	4,080 bytes	4,080 bytes	4,044 bytes
32,768 bytes	8,176 bytes	8,176 bytes	8,140 bytes

These are the values for the tested eight-byte-aligned builds. The chunk capacities were also observed from actual external data, not inferred from page size alone.

Both heap_prepare_insert and heap_update check the fixed threshold before invoking the toaster for fresh inline tuples. Inside the toaster, toast_tuple_target provides the reduction target, after subtracting tuple header overhead.

It’s not simply "compress when the row cannot fit on a page.” A tuple just over 2 KB can still fit on an 8 KB page. By default, wide rows are reduced earlier, with the goal of keeping their size consistent with roughly four tuples per page. This is a row-density and oversized-attribute policy, rather than a separately tunable document-compression policy.

These are fresh values with one eligible document column. If a row has several eligible columns, the toaster works in phases rather than compressing the entire row as one object.

Change the target without changing activation

The following input view constructs fresh JSONB values from repetitive text. It deliberately samples several payload sizes. It is not intended to represent a natural document distribution.

A view is used rather than copying stored compressed values from another table. Reusing an existing compressed datum would confound a test of whether the destination attempts compression.

postgres=# CREATE TEMP VIEW input_documents AS
    SELECT n, jsonb_build_object('v', repeat('x', 1024 * (1 << n))) AS doc
    FROM (VALUES (0),(1),(2),(6),(9),(10),(11)) AS sizes(n)
;

CREATE VIEW

postgres=# CREATE TABLE extended_default(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# CREATE TABLE extended_200(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=200)
;

CREATE TABLE

postgres=# CREATE TABLE extended_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# ALTER TABLE extended_default
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE extended_200
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE extended_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO extended_default
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO extended_200
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO extended_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# SELECT policy, 1 << n AS payload_kib,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position, 'default' AS policy, * FROM extended_default
      UNION ALL SELECT 2,'target_200',* FROM extended_200
      UNION ALL SELECT 3,'target_4096',* FROM extended_4k
    ) AS cases
    ORDER BY position,n
;

   policy    | payload_kib | compression | stored_bytes | external 
-------------+-------------+-------------+--------------+----------
 default     |           1 | (null)      |         1041 | f
 default     |           2 | lz4         |           39 | f
 default     |           4 | lz4         |           47 | f
 default     |          64 | lz4         |          288 | f
 default     |         512 | lz4         |         2083 | t
 default     |        1024 | lz4         |         4139 | t
 default     |        2048 | lz4         |         8252 | t
 target_200  |           1 | (null)      |         1041 | f
 target_200  |           2 | lz4         |           39 | f
 target_200  |           4 | lz4         |           47 | f
 target_200  |          64 | lz4         |          284 | t
 target_200  |         512 | lz4         |         2083 | t
 target_200  |        1024 | lz4         |         4139 | t
 target_200  |        2048 | lz4         |         8252 | t
 target_4096 |           1 | (null)      |         1041 | f
 target_4096 |           2 | (null)      |         2065 | f
 target_4096 |           4 | lz4         |           47 | f
 target_4096 |          64 | lz4         |          288 | f
 target_4096 |         512 | lz4         |         2087 | f
 target_4096 |        1024 | lz4         |         4139 | t
 target_4096 |        2048 | lz4         |         8252 | t
(21 rows)

The default JSONB storage strategy is EXTENDED, which permits both compression and external storage.

Three observations distinguish the decisions:

Even with a 200-byte target, the 1 KB-payload document still remains uncompressed and inline at 1,041 bytes. Its tuple did not enter TOAST because it is smaller than the hardcoded MaximumBytesPerTuple(4).
Once TOAST is active, lowering the target externalizes values sooner: the 64 KB payload is inline with the default target but external with the 200-byte target.
With a 4 KB target, the 2 KB-payload document remains uncompressed at 2,065 bytes. Its tuple enters TOAST, but already meets the larger target. The same document compressed to 39 bytes in the other two tables.

Raising the target is therefore not an independent "retain more compressed documents inline" control. It can also prevent a compression attempt.

The default and 200-byte cases report 288 and 284 bytes respectively for the same compressed 64 KB-payload document. This is the inline/external varlena accounting difference, not a better compression result.

Storage strategies: what SQL can control

There are four column storage strategies:

Strategy	Compression	External storage
`EXTENDED`	Permitted, attempted in the first phase when reduction is needed	Permitted
`EXTERNAL`	Not permitted	Permitted
`MAIN`	Permitted, attempted in a later phase when needed	Last resort
`PLAIN`	Not permitted	Not permitted

MAIN does not guarantee inline storage. MAIN attributes are also skipped during the first compression pass: they get their own compression round only after EXTENDED/EXTERNAL attributes have been compressed and externalized. They are likewise skipped by the out-of-line loops until the last-resort pass, which uses TOAST_TUPLE_TARGET_MAIN (MaximumBytesPerTuple(1) ≈ 8,160 bytes on an 8 KB build). EXTERNAL does not mean "externalize every document,” and an explicit LZ4 method cannot override its prohibition on compression.

Here EXTERNAL and MAIN use the same 4 KB target, making the comparison independent of an accidental target difference:

postgres=# CREATE TABLE external_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# CREATE TABLE main_4k(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=4096)
;

CREATE TABLE

postgres=# CREATE TABLE plain_case(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# ALTER TABLE external_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE main_4k
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE plain_case
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE external_4k
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# ALTER TABLE main_4k
            ALTER COLUMN doc SET STORAGE MAIN
;

ALTER TABLE

postgres=# ALTER TABLE plain_case
            ALTER COLUMN doc SET STORAGE PLAIN
;

ALTER TABLE

postgres=# INSERT INTO external_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO main_4k
    SELECT * FROM input_documents ORDER BY n
;

INSERT 0 7

postgres=# INSERT INTO plain_case
    SELECT * FROM input_documents WHERE n<=2 ORDER BY n
;

INSERT 0 3

postgres=# SELECT policy, 1 << n AS payload_kib,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position,'EXTERNAL' AS policy,* FROM external_4k
      UNION ALL SELECT 2,'MAIN',* FROM main_4k
      UNION ALL SELECT 3,'PLAIN',* FROM plain_case
    ) AS cases
    ORDER BY position,n
;

  policy  | payload_kib | compression | stored_bytes | external 
----------+-------------+-------------+--------------+----------
 EXTERNAL |           1 | (null)      |         1041 | f
 EXTERNAL |           2 | (null)      |         2065 | f
 EXTERNAL |           4 | (null)      |         4109 | t
 EXTERNAL |          64 | (null)      |        65549 | t
 EXTERNAL |         512 | (null)      |       524301 | t
 EXTERNAL |        1024 | (null)      |      1048589 | t
 EXTERNAL |        2048 | (null)      |      2097165 | t
 MAIN     |           1 | (null)      |         1041 | f
 MAIN     |           2 | (null)      |         2065 | f
 MAIN     |           4 | lz4         |           47 | f
 MAIN     |          64 | lz4         |          288 | f
 MAIN     |         512 | lz4         |         2087 | f
 MAIN     |        1024 | lz4         |         4143 | f
 MAIN     |        2048 | lz4         |         8252 | t
 PLAIN    |           1 | (null)      |         1041 | f
 PLAIN    |           2 | (null)      |         2065 | f
 PLAIN    |           4 | (null)      |         4113 | f
(17 rows)

For EXTERNAL, the 2 KB payload is still inline, but the 4 KB payload is external without compression.

For MAIN, the 1 MB payload compresses to 4,143 inline bytes. It can stay inline because MAIN postpones externalization to its last-resort target: the maximum tuple size that fits on one page. The larger 2 MB-payload document still becomes external after compression.

For PLAIN, no value compresses or becomes external. A row that cannot fit on the heap page fails. Run this separate statement expecting an error:

postgres=# INSERT INTO plain_case
    VALUES (3,jsonb_build_object('v',repeat('x',8192)))
;

ERROR:  54000: row is too big: size 8240, maximum size 8160
LOCATION:  RelationGetBufferForTuple, hio.c:531

The 8,160-byte maximum in this error is the heap-page tuple limit, not the 2,032-byte TOAST activation threshold. They answer different questions.

This is the complete SQL control surface relevant to these cases:

Control	Scope	Changes the fixed activation threshold?
`ALTER COLUMN ... SET STORAGE ...`	Column's permitted/preferred representation	No
`ALTER COLUMN ... SET COMPRESSION pglz/lz4`	Codec when compression is attempted	No
`default_toast_compression`	Default codec when the column has no override	No
`ALTER TABLE ... SET (toast_tuple_target=...)`	Whole-tuple reduction target after entry	No
`TOAST_TUPLE_THRESHOLD`, `TOAST_TUPLES_PER_PAGE`	Compiled activation policy	Source change
`TOAST_MAX_CHUNK_SIZE`, `EXTERN_TUPLES_PER_PAGE`	Compiled external chunk capacity	Separate from activation
`BLCKSZ`	Compiled page size	Changes several defaults together

Changing page and chunk sizes requires a freshly initialized cluster. They are not runtime GUCs. Changing only a main-tuple threshold is not inherently an on-disk page-format change, but a correct implementation still has to consider all heap entry paths and TOAST-table creation.

The compression algorithm still matters after activation

Compression is an attempt, not a storage guarantee. Pglz's default strategy has a 32-byte minimum input, a 25% minimum reduction policy, and a 1,024-byte encoded-output first-success limit: it aborts if it has emitted that much without finding a match.

Those are pglz strategy rules, not user-configurable document thresholds. LZ4 does not impose pglz's first-success or minimum-rate rules. Both must still pass TOAST's header/alignment savings check.

Changing the column codec does not rewrite existing values:

postgres=# CREATE TABLE codec_example(
    id integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# ALTER TABLE codec_example
            ALTER COLUMN doc SET COMPRESSION pglz
;

ALTER TABLE

postgres=# INSERT INTO codec_example
    VALUES
      (1,jsonb_build_object('v',repeat('x',4096)))
;

INSERT 0 1

postgres=# ALTER TABLE codec_example
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO codec_example
    VALUES
      (2,jsonb_build_object('v',repeat('x',4096)))
;

INSERT 0 1

postgres=# SELECT id, pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes
    FROM codec_example ORDER BY id
;

 id | compression | stored_bytes 
----+-------------+--------------
  1 | pglz        |           71
  2 | lz4         |           47
(2 rows)

The old value remains pglz. The freshly constructed new value uses LZ4. An update that leaves the document unchanged, or a copy of an already compressed datum, need not recompress it. Therefore, changing the setting is not proof that existing data now uses the new codec.

Larger pages: fewer documents become compression candidates

Larger pages can reduce the number of external chunks. That does not mean they preserve compression behavior.

I rebuilt PostgreSQL 19 RC1 with --with-blocksize=8, 16, and 32, keeping the other configure options the same, then initialized fresh clusters.

Do not reuse a data directory across page sizes. A different page size also requires rebuilding linked extensions against that PostgreSQL installation. This comparison deliberately uses core JSONB rather than mixing extension versions or a stock image with a different source-built baseline.

8 KB: all but the smallest repeated-field document compress

The samples array now contains 8, 64, 128, or 256 similarly structured objects. All tests explicitly select LZ4. One table retains the build's default target. The other sets it to 2040.

postgres=# CREATE TEMP VIEW page_inputs AS
    SELECT n, jsonb_build_object('samples', (
             SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
             FROM generate_series(1,n)
           )) AS doc
    FROM (VALUES (8),(64),(128),(256)) AS counts(n)
;

CREATE VIEW

postgres=# CREATE TABLE page_default(
    n integer PRIMARY KEY, doc jsonb
);

CREATE TABLE

postgres=# CREATE TABLE page_target(
    n integer PRIMARY KEY, doc jsonb
)
    WITH (toast_tuple_target=2040)
;

CREATE TABLE

postgres=# ALTER TABLE page_default
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# ALTER TABLE page_target
            ALTER COLUMN doc SET COMPRESSION lz4
;

ALTER TABLE

postgres=# INSERT INTO page_default
    SELECT * FROM page_inputs ORDER BY n
;

INSERT 0 4

postgres=# INSERT INTO page_target
    SELECT * FROM page_inputs ORDER BY n
;

INSERT 0 4

postgres=# CREATE VIEW page_observations AS
    SELECT policy, n AS objects,
           pg_column_size(doc::text::jsonb) AS logical_bytes,
           pg_column_compression(doc) AS compression,
           pg_column_size(doc) AS stored_bytes,
           pg_column_toast_chunk_id(doc) IS NOT NULL AS external
    FROM (
      SELECT 1 AS position,'default' AS policy,* FROM page_default
      UNION ALL SELECT 2,'target_2040',* FROM page_target
    ) AS cases ORDER BY position,n
;

CREATE VIEW

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | lz4         |          126 | f
 default     |     128 |          7708 | lz4         |          150 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | lz4         |          126 | f
 target_2040 |     128 |          7708 | lz4         |          150 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

16 KB: the 3,868-byte JSONB document no longer compresses

The complete setup is unchanged. This is the same final query on the 16 KB build:

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | lz4         |          150 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | lz4         |          150 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The tuple containing 3,868 JSONB bytes remains below the 4,080-byte activation gate. Lowering its target to 2040 does not make the caller invoke TOAST.

32 KB: the 7,708-byte document also stops compressing

The same query on the 32 KB build:

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | (null)      |         7708 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | (null)      |         7708 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The 7,708-byte JSONB value compressed to 150 bytes on the 8- and 16-KB builds. On the 32 KB build, the tuple stays below the 8,176-byte gate, and the datum remains at 7,708 bytes. A 2040-byte target does not restore compression.

The 15,388-byte positive control compresses on all three builds. Its tuple exceeds even the largest activation threshold.

UPDATE has the same outer gate

The following statements reconstruct each JSONB value on UPDATE, rather than reassigning an existing compressed value. On the 32 KB build:

postgres=# UPDATE page_default d
    SET doc=jsonb_build_object('samples', (
        SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
        FROM generate_series(1,d.n)
    ))
;

UPDATE 4

postgres=# UPDATE page_target d
    SET doc=jsonb_build_object('samples', (
        SELECT jsonb_agg(jsonb_build_object('temperature',20,'humidity',60))
        FROM generate_series(1,d.n)
    ))
;

UPDATE 4

postgres=# SELECT * FROM page_observations
;

   policy    | objects | logical_bytes | compression | stored_bytes | external 
-------------+---------+---------------+-------------+--------------+----------
 default     |       8 |           508 | (null)      |          508 | f
 default     |      64 |          3868 | (null)      |         3868 | f
 default     |     128 |          7708 | (null)      |         7708 | f
 default     |     256 |         15388 | lz4         |          198 | f
 target_2040 |       8 |           508 | (null)      |          508 | f
 target_2040 |      64 |          3868 | (null)      |         3868 | f
 target_2040 |     128 |          7708 | (null)      |         7708 | f
 target_2040 |     256 |         15388 | lz4         |          198 | f
(8 rows)

The INSERT and UPDATE results matched on all three servers. This is why looking only at the inner compression loop, without the INSERT/UPDATE callers, can give the wrong interpretation of toast_tuple_target.

Chunk capacity does grow

This 8 KB-page case inserts a 65,536-character text value with STORAGE EXTERNAL and reads its actual TOAST chunks. \gset assigns the TOAST relation name to a psql variable, avoiding a hardcoded relation OID:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            1996
(1 row)

Run the same statements in the 16 KB instance:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            4044
(1 row)

And in the 32 KB instance:

postgres=# CREATE TABLE chunk_probe(
    doc text
);

CREATE TABLE

postgres=# ALTER TABLE chunk_probe
            ALTER COLUMN doc SET STORAGE EXTERNAL
;

ALTER TABLE

postgres=# INSERT INTO chunk_probe
    VALUES (repeat('z',65536))
;

INSERT 0 1

postgres=# SELECT reltoastrelid::regclass AS toast_relation
    FROM pg_class WHERE oid='chunk_probe'::regclass
    \gset

postgres=# SELECT max(octet_length(chunk_data)) AS max_chunk_bytes FROM :toast_relation
;

 max_chunk_bytes 
-----------------
            8140
(1 row)

The maximum payloads are 1,996, 4,044, and 8,140 bytes.

That is an independent benefit larger pages can provide for sufficiently large values. It does not compensate automatically for the loss of compression on smaller documents. Neither fewer chunks nor larger pages alone establishes lower allocation or better read performance.

Why this behavior remains

The documented TOAST design handles values that cannot fit directly in heap tuples, and keeps main rows smaller so queries not needing large attributes can avoid fetching them. It is a general relational storage mechanism, predating JSONB, not a policy specifically optimized for repeated document schemas.

I searched the pgsql-hackers discussions with the pghacker MCP. This separation was already being discussed explicitly in 2019:

February 6, 2019: Pavan Deolasee proposed a separate table-level option to control compression, named compress_tuple_target, so smaller values could be compressed inline without requiring out-of-line storage.
March 5, 2019: Andrew Dunstan supported the idea, while calling for more review.
April 3, 2019: Michael Paquier identified the fixed-threshold caller checks independently of the per-table toast_tuple_target.

The subsequent discussion identified two broader problems: a bounded-width relation may have no TOAST table, so lowering its trigger could require creating one under a stronger lock. Rejecting previously accepted low targets could break dump restores. These are implementation and compatibility constraints, not evidence that the current fraction is optimal for small JSONB documents.

If you want to build and run the three page sizes

The SQL above is sufficient for an existing PostgreSQL 19 server. The following commands run in Bash on a machine with Docker. The Dockerfile is supplied inline. No repository checkout or external code file is required.

set -euo pipefail

for kb in 8 16 32; do
  docker build --build-arg BLOCK_SIZE="$kb" \
    -t "toast-pg19rc1:${kb}k" - <<'DOCKERFILE'
FROM postgres:19rc1-alpine
RUN apk add --no-cache build-base curl bison flex perl linux-headers \
    icu-dev lz4-dev openssl-dev readline-dev zlib-dev
ARG BLOCK_SIZE=8
RUN set -eu; \
    case "$BLOCK_SIZE" in 8|16|32) ;; *) exit 1 ;; esac; \
    mkdir /tmp/page-build; \
    curl --fail --location \
      "https://ftp.postgresql.org/pub/source/v${PG_VERSION}/postgresql-${PG_VERSION}.tar.bz2" \
      -o /tmp/page-build/postgres.tar.bz2; \
    tar -xjf /tmp/page-build/postgres.tar.bz2 -C /tmp/page-build; \
    cd "/tmp/page-build/postgresql-${PG_VERSION}"; \
    ./configure --prefix=/usr/local --with-blocksize="$BLOCK_SIZE" \
      --with-lz4 --with-icu --with-openssl; \
    make -s -j2; \
    make -s install; \
    cd /; \
    rm -rf /tmp/page-build
DOCKERFILE
done

Choose one page size, start a fresh disposable instance, and open psql:

kb=8                      # repeat with 16 and 32
pg="toast-research-${kb}k-$$"
docker run --detach --name "$pg" --network none \
  -e POSTGRES_HOST_AUTH_METHOD=trust "toast-pg19rc1:${kb}k"

ready=no
for attempt in $(seq 1 60); do
  if docker exec "$pg" pg_isready -U postgres; then
    ready=yes
    break
  fi
  sleep 1
done
if [ "$ready" != yes ]; then
  docker logs "$pg"
  docker rm --force --volumes "$pg"
  exit 1
fi

docker exec -it "$pg" psql -X -v ON_ERROR_STOP=1 \
  -P pager=off -P 'null=(null)' -P format=aligned \
  -P linestyle=ascii -P border=1 -U postgres -d postgres

Use the 8 KB instance for the storage-strategy cases. For the page comparison, run the same page_inputs, page_default, page_target, and observation statements on each instance. Repeat the chunk case on all three.

The deliberately failing PLAIN insert is a separate statement: do not include it in a successful setup batch with ON_ERROR_STOP.

After leaving psql with \q, remove only the container created above:

docker rm --force --volumes "$pg"

Conclusion: separate compression eligibility from row placement

The experiment does not show that PostgreSQL cannot compress document redundancy. It shows that the eligibility policy is tied to heap tuple size and page size, rather than to a document-column compression policy.

Under the ordinary defaults, increasing page size from 8 to 16 to 32 KB raises activation from about 2 to 4 to 8 KB. Some highly compressible documents therefore stop reaching the compressor. A lower toast_tuple_target cannot recover those missed attempts. A higher target can suppress attempts further.

A useful direction is to make the hardcoded activation policy configurable, separately from the reduction/inline target. For example, a 32 KB-page server could retain a lower activation threshold without adopting a smaller page. For document workloads, a per-column compression-eligibility threshold may be more directly useful than a whole-row threshold.

This isn't as simple as making the hardcoded value a parameter. A safe implementation must cover INSERT, UPDATE, rewrites, TOAST-table creation, locking, and existing representations. The appropriate default would still need insertion CPU and read/update benchmarks on representative document sizes. There is no basis here for declaring that every 500-byte document should be compressed.

The narrower conclusion is reproducible: the quarter-page activation gate can prevent useful compression, and larger pages may widen the gap. Currently, the DocumentDB extension relies on the TOAST mechanism, trading MongoDB’s compression behavior for compatibility with PostgreSQL storage. I've seen many MongoDB workloads store small documents (and ran a poll on LinkedIn), much like a relational model—even though document models are meant to store larger aggregates to favor single-document data locality and single-IO transactions.

Load CSV and JSON from Azure Blob into Postgres with pg_duckdb

Franck Pachot — Fri, 23 Oct 2026 13:02:31 +0000

Data arrives as files. A vendor drops a CSV in a container, an upstream system exports newline-delimited JSON, a daily extract lands in Blob. The usual way into Postgres is a client-side load tool and a hand-written COPY with a column list. With pg_duckdb you can skip most of that: point read_csv() / read_json() at the file in Blob, let DuckDB infer the schema, and CREATE TABLE AS straight into a normal PostgreSQL table — or just query the file in place and never import it.

Setup

Only the Blob secret is needed:

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

The files in Blob

A vendor list as CSV and an event stream as newline-delimited JSON:

az://lake/incoming/vendors.csv
   vendor_id,name,country,active,rating
   1,Acme Corp,US,true,4.5
   2,Globex,CH,false,3.9
   3,Initech,FR,true,4.1
   4,Umbrella,DE,true,2.8
   5,Soylent,JP,false,4.9

az://lake/incoming/events.json
   {"event_id":101,"ts":"2026-01-05T10:00:00","user":"alice","action":"login","value":1}
   {"event_id":102,"ts":"2026-01-05T10:02:13","user":"bob","action":"purchase","value":42}
   {"event_id":103,"ts":"2026-01-05T10:05:41","user":"alice","action":"logout","value":1}

The image cover of this blog post shows those files in Microsoft Azure Storage Explorer. I can read them from Azure Database for PostgreSQL with the pg_duckdb extension enabled.

Read a CSV with inferred types

read_csv() parses and infers types from the file — no column list, no cast boilerplate to look at the data:

postgres=> SELECT r['vendor_id'], r['name'], r['country'], r['active'], r['rating']
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
 vendor_id |   name    | country | active | rating
-----------+-----------+---------+--------+--------
 1         | Acme Corp | US      | t      | 4.5
 2         | Globex    | CH      | f      | 3.9
 3         | Initech   | FR      | t      | 4.1
 4         | Umbrella  | DE      | t      | 2.8
 5         | Soylent   | JP      | f      | 4.9
(5 rows)

Note active came back as a boolean and rating as a number — DuckDB inferred them from the file; you didn't declare anything.

Materialize into a real PostgreSQL table

To bring it in, wrap it in CREATE TABLE AS and cast each column to the Postgres type you want. (Casting on the way in is good practice — it pins the schema rather than trusting inference, and it gives money/number columns an explicit precision.)

postgres=> CREATE TABLE vendors AS
           SELECT r['vendor_id']::int       AS vendor_id,
                  r['name']::text           AS name,
                  r['country']::text        AS country,
                  r['active']::boolean      AS active,
                  r['rating']::numeric(3,1) AS rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
SELECT 5

"vendors" is now an ordinary PostgreSQL table — index it, join it, update it, EXPLAIN it like any other. Two plans make the point.

With duckdb.force_execution = true, DuckDB will still scan the table (because it can), through a TABLE_SCAN on Table: vendors — which already proves "vendors" is a real Postgres relation, not a file handle:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.021 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││             Total Time: 0.0012s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │       Table: vendors      │
 │      Filters: active      │
 │                           │
 │           3 rows          │
 │          (0.00s)          │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.196 ms
 Execution Time: 1.034 ms

The TABLE_SCAN with Table: vendors and 3 rows (the active vendors) proves "vendors" is a real Postgres relation DuckDB reads, not a file handle. Turn forcing back off (the default) and the data is served entirely by the PostgreSQL executor — no DuckDB in the picture at all:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate (actual time=0.024..0.025 rows=1.00 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on vendors (actual time=0.018..0.019 rows=3.00 loops=1)
         Filter: active
         Rows Removed by Filter: 2
         Buffers: shared hit=1
 Planning Time: 0.071 ms
 Execution Time: 0.062 ms

That Seq Scan on vendors is the proof the load worked: the rows are physically in Postgres, queryable by the native engine like any table you created by hand.

Same thing for JSON

read_json() handles newline-delimited JSON (and arrays), inferring fields:

postgres=> SELECT r['event_id'], r['user'], r['action'], r['value']
           FROM read_json('az://lake/incoming/events.json') r
;
 event_id | user  |  action  | value
----------+-------+----------+-------
 101      | alice | login    | 1
 102      | bob   | purchase | 42
 103      | alice | logout   | 1
(3 rows)

And into a table, parsing the timestamp as you go:

postgres=> CREATE TABLE events_log AS
           SELECT r['event_id']::int   AS event_id,
                  (r['ts'])::timestamp AS ts,
                  r['user']::text      AS usr,
                  r['action']::text    AS action,
                  r['value']::int      AS value
           FROM read_json('az://lake/incoming/events.json') r
;
SELECT 3

postgres=> SELECT * FROM events_log ORDER BY event_id
;
 event_id |         ts          |  usr  |  action  | value
----------+---------------------+-------+----------+-------
 101      | 2026-01-05 10:00:00 | alice | login    | 1
 102      | 2026-01-05 10:02:13 | bob   | purchase | 42
 103      | 2026-01-05 10:05:41 | alice | logout   | 1
(3 rows)

For a recurring load, use INSERT INTO events_log SELECT … FROM read_json(…) instead of CREATE TABLE AS, and point the path at the new file (or a glob over a folder of drops — see the partitioned-lakes post).

Or don't import at all

Sometimes you don't need the data in Postgres — you just need an answer. You can aggregate the Blob file directly:

postgres=> SELECT r['country'] AS country,
                  count(*)     AS n,
                  avg((r['rating'])::numeric(3,1)) AS avg_rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
           GROUP BY r['country']
           ORDER BY 1
;
 country | n | avg_rating
---------+---+------------
 CH      | 1 | 3.9
 DE      | 1 | 2.8
 FR      | 1 | 4.1
 JP      | 1 | 4.9
 US      | 1 | 4.5
(5 rows)

That's the nice property of this approach: the same read_csv() / read_json() is either a one-off query or the source of an import, depending on whether you wrap it in CREATE TABLE AS. Query in place for exploration and ad-hoc checks. Materialize when you need indexes, constraints, or OLTP access.

The reverse direction: writing a result back out

Loading isn't one-way. You can also write a Postgres query result back to Blob as Parquet — handy for publishing an aggregate or an extract. On Azure Database for PostgreSQL the plain COPY … TO 'az://…' syntax is blocked for a normal login (it needs superuser), so you drive the write through the DuckDB engine with duckdb.raw_query, which authorizes it with the secret and the az:// allow-list — the object-storage controls PostgreSQL's server-file gate has no say over:

postgres=> SELECT duckdb.raw_query($$
             COPY (SELECT country, count(*) AS n
                   FROM pgduckdb.public.vendors GROUP BY country)
             TO 'az://lake/exports/vendors_by_country.parquet' (FORMAT parquet)
           $$)
;
 raw_query
-----------

(1 row)

Note the pgduckdb.public. prefix on the table: inside raw_query you're in DuckDB's catalog, not PostgreSQL's search path, so a bare vendors won't resolve. The archive post covers this write path in full.

A few practical notes

Cast on import. Inference is convenient for a look, but pin the types in your CREATE TABLE AS so the table schema is deterministic — and give decimals an explicit precision (numeric(p,s)), which also keeps later DuckDB queries on the table from falling back (see the numeric gotcha side-note).
CSV options mirror DuckDB's, passed with PostgreSQL named-arg syntax: read_csv('az://…/f.csv', delim := ';', header := true) and so on.
Globs work here too. read_csv('az://lake/incoming/*.csv') loads a whole drop folder in one statement.
Big loads stream. DuckDB reads the file in chunks, so you're not limited to what fits in memory for the parse.

Takeaways

read_csv / read_json over az:// read files straight from Blob with type inference — no pre-defined table, no external load tool.
CREATE TABLE AS SELECT … FROM read_csv(…) materializes into a normal Postgres table (cast the columns to pin the schema); the native Seq Scan plan proves the rows really landed in Postgres. INSERT … SELECT appends for recurring loads.
Or skip the import and aggregate the file in place — same function, your choice.
Globs and DuckDB's CSV options (delim :=, header :=, …) are available via PostgreSQL named-argument syntax.

The archive post wrote cold data out to Blob by hand, step by step. In the last post of the series I turn that export-verify-delete cycle into a single durable, scheduled workflow with pg_durable — so a crash mid-way resumes instead of re-exporting.

pg_duckdb quietly ignored force_execution on my numeric column

Franck Pachot — Fri, 23 Oct 2026 12:35:08 +0000

In this series, I encountered a pg_duckdb gotcha with unconstrained numeric column. With duckdb.force_execution = true, I expected DuckDB to run my aggregate. But it ran on the PostgreSQL engine instead, with a warning. Here's how to see it and fix it.

Reproduce

I created a table with four columns of the same number, differing only in declared type:

DROP TABLE IF EXISTS nf;
CREATE TABLE nf (
 id bigint
 , n_bare  numeric
 , n_10_2  numeric(10,2)
 , d       double precision
);
INSERT INTO nf SELECT g, g+0.5, g+0.5, g+0.5 
 FROM generate_series(1,100000) g
;
ANALYZE nf
;

Here are their Postgres type modifiers (-1 means "no declared precision"):

postgres=> SELECT attname, atttypmod FROM pg_attribute
           WHERE attrelid='nf'::regclass AND attnum>0 AND NOT attisdropped ORDER BY attnum
;
 attname | atttypmod
---------+-----------
 id      |        -1
 n_bare  |        -1
 n_10_2  |    655366
 d       |        -1
(4 rows)

n_bare and d show -1 (no declared precision)
n_10_2 shows 655366, the encoded form of numeric(10,2)

The symptom

I forced DuckDB on, then sum the bare numeric:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: Binder Error: No function matches the given name and argument types 'sum(UnsupportedPostgresType(DuckDB requires the precision of a NUMERIC to be set))'
                      QUERY PLAN
-----------------------------------------------------
 Aggregate  (cost=2074.00..2074.01 rows=1 width=32)
   ->  Seq Scan on nf  (cost=0.00..1824.00 rows=100000 width=8)
(2 rows)

That's a plain PostgreSQL plan. I've set force_execution = true, yet the top node is a plain Aggregate, not a Custom Scan (DuckDBScan). DuckDB declined and PostgreSQL ran it. A WARNING at plan time is emitted when pg_duckdb tries to build the DuckDB plan and bails on the unconstrained numeric. But it's a WARNING, not an error, easy to miss from an application, and the query still returns the right answer on the native executor. If you were only watching the response time you'd think pg_duckdb didn't help, when it never ran the query in DuckDB at all.

I've run the same sum over the numeric(10,2) column:

postgres=> EXPLAIN SELECT sum(n_10_2) FROM nf
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │    Aggregates: sum(#0)    │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           n_10_2          │
 │                           │
 │       ~100,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │         Table: nf         │
 │    Projections: n_10_2    │
 │                           │
 │       ~100,000 rows       │
 └───────────────────────────┘
(25 rows)

DuckDB runs it. The only difference between the two queries is that one column has a declared precision and the other doesn't.

Why

DuckDB's DECIMAL is fixed precision, up to 38 digits. A Postgres numeric with a declared (p,s) maps straight onto it. A bare numeric has no bound — there's nothing for DuckDB to map it to — so pg_duckdb leaves the whole query to PostgreSQL. (A numeric(38,10) works fine, only the unconstrained one falls back.) The fallback itself is a feature: forcing DuckDB can never break a query, it just might not accelerate it. The problem is only that it's silent.

The fix

One session setting makes the bare numeric run in DuckDB, read as double:

postgres=> SET duckdb.convert_unsupported_numeric_to_double = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │    Aggregates: sum(#0)    │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           n_bare          │
 │                           │
 │       ~100,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │         Table: nf         │
 │    Projections: n_bare    │
 │                           │
 │       ~100,000 rows       │
 └───────────────────────────┘
(25 rows)

No more warning, and the top node is now Custom Scan (DuckDBScan) — DuckDB runs it, reading the bare numeric as double.

The caveat is in the name: double is floating point, so you can lose the exact decimal precision numeric guarantees — fine for analytical sums and averages, think twice for exact financial rounding. The cleaner fix is to declare the precision in the schema (numeric(12,2)), so DuckDB runs it exactly, no flag.

Takeaway

With force_execution = true, always EXPLAIN and check the top node is Custom Scan (DuckDBScan). If it's a plain PostgreSQL node, DuckDB declined — and a bare numeric (typmod -1) is the usual reason. Declare the precision, or set duckdb.convert_unsupported_numeric_to_double to true if your can accept that decimal fractions that cannot be stored with exact base-10 precision are rounded.

Even with duckdb.force_execution = true, you should always run EXPLAIN to verify that the query utilizes the Custom Scan (DuckDBScan) node. If the planner falls back to native PostgreSQL nodes, it indicates that DuckDB declined the query—most commonly due to an unconstrained PostgreSQL NUMERIC column (with a type modifier of -1). To resolve this, explicitly declare the column's precision and scale, or set duckdb.convert_unsupported_numeric_to_double = true if your workload can tolerate the inherent precision loss and rounding behavior of binary floating-point (DOUBLE) conversions.

Query a partitioned data lake in Azure Blob with pg_duckdb

Franck Pachot — Sat, 17 Oct 2026 21:57:05 +0000

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.

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

Franck Pachot — Thu, 15 Oct 2026 07:59:08 +0000

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(#1)          │
 │                           │
 │       ~999,469 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_string_│
 │   uhugeint(CASE  WHEN (   │
 │  (customer_ref IS NULL))  │
 │    THEN ('erased') ELSE   │
 │     'identified' END)     │
 │           amount          │
 │                           │
 │      ~1,998,938 rows      │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │    ────────────────────   │
 │      Join Type: LEFT      │
 │                           │
 │        Conditions:        ├──────────────┐
 │customer_ref = customer_ref│              │
 │                           │              │
 │      ~1,998,938 rows      │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │       READ_PARQUET        ││  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   ││    ────────────────────   │
 │         Function:         ││           Table:          │
 │        READ_PARQUET       ││     customer_identity     │
 │                           ││                           │
 │        Projections:       ││        Projections:       │
 │        customer_ref       ││        customer_ref       │
 │           amount          ││                           │
 │                           ││                           │
 │      ~1,998,938 rows      ││        ~50,000 rows       │
 └───────────────────────────┘└───────────────────────────┘

Those estimates come from different places:

Parquet carries its row counts and per-row-group min/max statistics in the file metadata.
For a PostgreSQL relation, pg_duckdb 1.1.0 calls PostgreSQL's estimate_rel_size and passes that cardinality to DuckDB.
It does not expose PostgreSQL's per-column histograms, most-common values, or distinct-value estimates to DuckDB (PostgresTable::GetStatistics is not implemented in this release).

This explains the mixed quality of the estimates. DuckDB knows the inputs contain about 2 million and 50,000 rows, enough to place the small relation on the hash-build side. It does not predict that the final CASE expression has exactly two groups: the plain plan estimates ~999,469 groups, whereas the analyzed plan produces 2.

ANALYZE customer_identity still matters. PostgreSQL statistics maintain the relation-size estimate supplied to DuckDB, and PostgreSQL's own planner uses its full local statistics when pg_duckdb delegates a filtered scan back to it.

What if the filter starts from the customer?

The setup already created customer_identity_email_idx. Now ask for the archived sales of one email address. This deliberately uses an INNER JOIN: the question asks for one known identity, so unmatched or erased identities are irrelevant:

postgres=> EXPLAIN (ANALYZE, COSTS OFF)
           SELECT
             c.full_name,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM customer_identity c
           JOIN read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
             ON (s['customer_ref'])::int = c.customer_ref
           WHERE c.email = 'customer00043@example.test'
           GROUP BY c.full_name
;

                                                                                                                                                                             QUERY PLAN                                                                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.002..0.002 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT c.full_name, count(*) AS n_sales, sum((s.amount)::numeric(10,2)) AS revenue FROM (pgduckdb.public.customer_identity c JOIN system.main.read_parquet('az://lake/archive/sales_2021_minimized.parquet'::text) s ON (((s.customer_ref)::integer = c.customer_ref))) WHERE (c.email = 'customer00043@example.test'::text) GROUP BY c.full_name
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.437s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │         full_name         │
 │           amount          │
 │                           │
 │          39 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │    ────────────────────   │
 │      Join Type: INNER     │
 │                           │
 │        Conditions:        ├──────────────┐
 │customer_ref = customer_ref│              │
 │                           │              │
 │          39 rows          │              │
 │          (0.00s)          │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         TABLE_SCAN        ││         TABLE_SCAN        │
 │    ────────────────────   ││    ────────────────────   │
 │         Function:         ││           Table:          │
 │        READ_PARQUET       ││     customer_identity     │
 │                           ││                           │
 │        Projections:       ││        Projections:       │
 │        customer_ref       ││        customer_ref       │
 │           amount          ││         full_name         │
 │                           ││                           │
 │    Total Files Read: 1    ││          Filters:         │
 │                           ││    email='customer00043   │
 │                           ││       @example.test'      │
 │                           ││                           │
 │          39 rows          ││           1 row           │
 │          (0.12s)          ││          (0.00s)          │
 └───────────────────────────┘└───────────────────────────┘


 Planning:
   Buffers: shared hit=1
 Planning Time: 566.542 ms
 Execution Time: 213.829 ms
(78 rows)

The bottom of EXPLAIN (ANALYZE, COSTS OFF) is:

┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│customer_ref = customer_ref│              │
│                           │              │
│          39 rows          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│         Function:         ││           Table:          │
│        READ_PARQUET       ││     customer_identity     │
│                           ││                           │
│        Projections:       ││        Projections:       │
│        customer_ref       ││        customer_ref       │
│           amount          ││         full_name         │
│                           ││                           │
│    Total Files Read: 1    ││          Filters:         │
│                           ││    email='customer00043   │
│                           ││       @example.test'      │
│                           ││                           │
│          39 rows          ││           1 row           │
└───────────────────────────┘└───────────────────────────┘

It starts with the selective customer side. The right child produces one row and becomes the hash-build side. DuckDB then applies the resulting join-key range while scanning Parquet: the READ_PARQUET leaf emits 39 rows, not 1,998,938. The runtime join filter is not printed as a static Filters: line in this plan, but the scan's actual row count makes it visible. When the predicate is written directly on the file side, the plan does print Filters: customer_ref=43.

Many databases push join filters only through a nested loop. In PostgreSQL, for example, each outer row re-parameterizes an inner index scan. YugabyteDB batches these scans to amortize the probes. Oracle and SQL Server instead attach a Bloom or bitmap filter to the hash join, allowing the probe scan to discard non-matching keys. DuckDB takes this latter approach, optimized for analytics: while building a hash table from the one-row customer_identity side, it derives a dynamic filter on the join key and injects it as a table filter into the probe-side READ_PARQUET scan.

The PostgreSQL table is not a blind full scan either. duckdb.log_pg_explain shows the inner SQL that pg_duckdb delegated and the access path PostgreSQL chose:

postgres=> SET duckdb.log_pg_explain = true;
SET

postgres=> SELECT
             c.full_name,
             count(*) AS n_sales,
             sum((s['amount'])::numeric(10,2)) AS revenue
           FROM customer_identity c
           JOIN read_parquet(
             'az://lake/archive/sales_2021_minimized.parquet'
           ) s
             ON (s['customer_ref'])::int = c.customer_ref
           WHERE c.email = 'customer00043@example.test'
           GROUP BY c.full_name
;

NOTICE:  (PGDuckDB/PostgresTableReader)

QUERY: SELECT customer_ref, full_name FROM public.customer_identity WHERE email='customer00043@example.test'

RUNNING: IN PROCESS THREAD.

EXECUTING:

Parallel Index Scan using customer_identity_email_idx on customer_identity
  Index Cond: (email = 'customer00043@example.test'::text)

 full_name      | n_sales | revenue
----------------+---------+---------
 Customer 00043 | 39      | 1833.00
(1 row)

DuckDB does not manage PostgreSQL indexes directly. It pushes columns and predicates into an inner PostgreSQL query, allowing the PostgreSQL optimizer to choose a sequential, bitmap, index, or index-only scan. Here:

postgres=> EXPLAIN (COSTS OFF)
       SELECT customer_ref, full_name
       FROM public.customer_identity
       WHERE email='customer00043@example.test'
;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using customer_identity_email_idx on customer_identity
   Index Cond: (email = 'customer00043@example.test'::text)
(2 rows)

For the unfiltered full join above, which requires only customer_ref, the delegated plan was a Parallel Index Only Scan using customer_identity_pkey on customer_identity:

postgres=> EXPLAIN  (COSTS OFF)
           SELECT customer_ref FROM public.customer_identity
;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather
   Workers Planned: 1
   ->  Parallel Index Only Scan using customer_identity_pkey on customer_identity
(3 rows)

The flow works in the other direction too. With WHERE (s['customer_ref'])::int = 43 on the Parquet side, the delegated SQL became SELECT customer_ref, full_name FROM public.customer_identity WHERE customer_ref=43, and PostgreSQL chose Parallel Index Scan using customer_identity_pkey.

Using DuckDB over PostgreSQL via pg_duckdb, rather than PostgreSQL over DuckDB via a foreign data wrapper, is a major advantage here. DuckDB was designed for analytics, while PostgreSQL was primarily designed for OLTP. Regardless of whether storage is row- or columnar-based, the join algorithms are designed accordingly.

Pushdown is not the same as row-group pruning

The filter reaches READ_PARQUET, but whether that avoids I/O depends on how the file is laid out. Inspect the customer_ref statistics stored in three of this file's 17 row groups:

postgres=> SELECT *
           FROM duckdb.query($duckdb$
             SELECT row_group_id,
                    num_values AS n,
                    stats_min AS min_customer_ref,
                    stats_max AS max_customer_ref
             FROM parquet_metadata(
               'az://lake/archive/sales_2021_minimized.parquet'
             )
             WHERE path_in_schema = 'customer_ref'
               AND row_group_id IN (0, 1, 16)
             ORDER BY row_group_id
           $duckdb$)
;
 row_group_id | n      | min_customer_ref | max_customer_ref
--------------+--------+------------------+------------------
 0            | 122880 | 1                | 50000
 1            | 122880 | 1                | 50000
 16           | 32858  | 1                | 50000
(3 rows)

The generator I used cycles through customer IDs, so every row group can include customer 43. DuckDB filters correctly, but min/max statistics cannot eliminate any row group. This is why a highly selective result does not automatically mean a cheap object-storage read.

If customer-based lookups are important, sort by customer_ref when writing each time partition. I tested an alternate export with ORDER BY cust_id: its first row group covered customers 1–3,072, the next 3,072–6,148, and the last 49,179–50,000. A lookup for customer 43 can then reject 16 of 17 row groups based on metadata. For frequent point lookups with low-latency requirements, materializing the facts into an indexed PostgreSQL table may still be a better physical design than querying Parquet.

The 39-row output of READ_PARQUET in the earlier plan shows the filter still ran at the row level — it saved decompression and hash probes, just not reads. With Parquet, there are two levels of pruning, and only metadata-level pruning avoids touching a row group.

What this pattern does and does not guarantee

Keeping identity out of immutable files does more than avoid rewrites. It keeps the authoritative personal record in the operational database, where a single governed update or deletion can take effect across every analytical query, rather than requiring the same action across many Parquet objects and downstream copies.

But the boundary must be designed carefully:

Data minimization matters. Do not put names, emails, phone numbers, addresses, or identifying free text in the fact file just because Parquet makes it convenient.
customer_ref is pseudonymous while another system can map it back to a person. The sequential number in this lab is only for reproducibility; production should use a non-meaningful, opaque token and control every mapping.
PostgreSQL becomes the authoritative location for identity lifecycle rules: who may read personal data, who may correct or erase it, what must be audited, and how retention is enforced.
The query is not a permission boundary. Users who can query the base tables and files can still see the bridge key. In production, expose a reviewed view or function and restrict direct access to both sources.
Deleting the live row does not retroactively remove it from historical backups. Erasure procedures must also define how replicas, exports, caches, logs, downstream results, backups, and restored environments are handled under the applicable retention policy.
Some records may have legal retention requirements. Whether data must be erased, retained, quarantined, or anonymized is a policy and legal decision, not something pg_duckdb decides.

This architecture gives governance a clear boundary: personal identity remains under PostgreSQL's operational controls, while the analytical tier receives only minimized, pseudonymous facts. It supports a GDPR program; it does not by itself prove compliance.

Takeaways

Keep immutable, minimized transaction facts in Parquet and personal identity in the governed operational database.
Join them on an opaque reference only when a query needs both.
The central pg_duckdb value is a DuckDB relational plan that spans heterogeneous Blob and PostgreSQL inputs, with an analytic-optimized engine, rather than coordinating separate local and foreign execution fragments.
The full join hashes the smaller PostgreSQL input and streams the Parquet facts through it.
pg_duckdb supplies base-table cardinality to DuckDB, while PostgreSQL still chooses the access path for delegated scans and can use its indexes.
Customer-side filters can reduce both inputs, but Parquet row-group pruning depends on physical ordering and min/max statistics.
For repeated selective lookups, sort within each time partition or materialize into an indexed table rather than assuming object storage behaves like an OLTP index.

One file is enough to demonstrate the join. A real archive grows into a tree of yearly or monthly files. In the next post, I will lay out that tree using Hive-style partitions and show DuckDB pruning out every file the query does not need.

Automate Postgres-to-Blob with durable workflow (pg_durable + pg_duckdb) in Azure Database for PostgreSQL

Franck Pachot — Tue, 13 Oct 2026 09:07:59 +0000

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:

Add pg_durable to azure.extensions (the allow-list).
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
if-then-else is declared with (), ?>, !>

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. delete only if the archive count matches
  ~> ( $Check$ SELECT $v = $n AS archive_complete $Check$
       ?> $Del$  DELETE FROM orders WHERE order_date < DATE '2024-01-01' $Del$
       !> $Fail$ DO $do$ BEGIN
                   RAISE EXCEPTION 'archive incomplete: expected % rows, file has %', $n, $v;
                 END $do$;
                $Fail$
     )
  -- 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.

The condition is a safety check to run the delete only when the count matches, or fail otherwise.

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)

The steps are visible with df.explain():

postgres=> SELECT df.explain('b9b24177')
;
                                explain
-----------------------------------------------------------------------
 Instance: b9b24177 (durable-archive)                                 +
 Status:   ✓ Completed                                                +
 Output:   {"rows":[],"row_count":0}                                  +
                                                                      +
 SQL:                                                                 +
         SELECT count(*) AS n FROM orders WHER... |=> 'n' ✓           +
 → SQL:                                                               +
         SELECT duckdb.raw_query(                                     +
         $Du... ✓                                                     +
 → SQL:                                                               +
         SELECT count(*) AS v                                         +
         FROM re... ✓                                                 +
 → IF ✓                                                               +
     ? condition:                                                     +
       └─SQL:  SELECT $v = $n AS archive_complete  ✓                  +
     ✓ then:                                                          +
       └─SQL:   DELETE FROM orders WHERE order_date < DATE '2... ✓    +
     ✗ else:                                                          +
       └─SQL:  DO $do$ BEGIN                                          +
                    RAISE EXCEPT... ⊘                                 +
 → SQL:                                                               +
         INSERT INTO archive_log(archived_year... ✓
(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 updated_at
;
 node_type |  status   |                                             query
-----------+-----------+------------------------------------------------------------------------------------------------
 SQL       | pending   |  DO $do$ BEGIN                                                                                +
           |           |                    RAISE EXCEPTION 'archive incomplete: expected % rows, file has %', $n, $v; +
           |           |                  END $do$;                                                                    +
           |           |
 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 |  SELECT $v = $n AS archive_complete
 SQL       | completed |   DELETE FROM orders WHERE order_date < DATE '2024-01-01'
 IF        | completed | {"condition_node":"3149a512"}
 THEN      | completed |
 SQL       | completed |                                                                                               +
           |           |         INSERT INTO archive_log(archived_year, n_rows, blob)                                  +
           |           |         VALUES (2023, $n, 'az://lake/archive/orders_2023.parquet')                            +
           |           |
 THEN      | completed |
(12 rows)

The gate adds nodes: the SELECT $v = $n AS archive_complete check, its IF node, and both branches — the DELETE ran and is completed, while the DO ... RAISE EXCEPTION branch stayed pending because it was never taken.

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$  
    -- the same ( check ?> delete !> fail ) gate, then log — 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 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

Archive cold Postgres data to Azure Blob and keep querying it with pg_duckdb

Franck Pachot — Sun, 11 Oct 2026 17:16:16 +0000

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.

DuckDB analytics on your PostgreSQL tables in Azure PostgreSQL

Franck Pachot — Fri, 09 Oct 2026 19:07:22 +0000

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:

Add pg_duckdb to azure.extensions (the allow-list).
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:       │
 │          cust_id          │
 │          prod_id          │
 │                           │
 │      10,000,000 rows      │
 │          (1.82s)          │
 └───────────────────────────┘


 Planning:
   Buffers: shared hit=1
 Planning Time: 0.966 ms
 Execution Time: 1.973 ms
(78 rows)

This example demonstrates DuckDB finishing the task in 2 seconds, whereas PostgreSQL requires 9 seconds to list customers who ordered more than one product. There is no risk in leaving the parameter as it was set within the query scope with a hint.

The precision detail

I declared amount as numeric(10,2) on purpose. A bare numeric (no precision) is something DuckDB can't represent, so pg_duckdb falls back to PostgreSQL — force_execution = true and all, your query just runs on the normal executor. The only tell is a WARNING at plan time (the prepared query fails inside CreatePlan, Postgres emits a warning, then falls back), which is easy to miss, plus the absence of Custom Scan (DuckDBScan) in EXPLAIN. If the top node isn't a DuckDBScan, DuckDB declined.

If you want DuckDB to run it anyway, SET duckdb.convert_unsupported_numeric_to_double = true casts the unsupported numeric to DOUBLE instead of falling back — fine for aggregates where a little floating-point imprecision is acceptable, not when you need exact decimal arithmetic.

Takeaways

Enable PG_DUCKDB on Azure Database for PostgreSQL (Flexible Server) with azure.extensions + shared_preload_libraries + restart, then CREATE EXTENSION pg_duckdb. Add PG_HINT_PLAN if you want to force per query.
SET duckdb.force_execution = true runs analytics over your existing tables — no Parquet, no data movement, but a faster engine.
It wins on heavy grouping, not on plain scans. Read the plan: DuckDBScan means it ran in DuckDB.
Declare numeric(p,s) precision on columns you aggregate, or DuckDB falls back (or set convert_unsupported_numeric_to_double).
Control the scope of duckdb.force_execution at session, transaction, or query level.

In a future post, I'll demonstrate how pg_duckdb can query Parquet files stored in Azure Blob Storage to archive cold data from Postgres and enable heterogeneous queries. We might even use pg_durable to automate lifecycle management. While it can read Parquet files, the true strength of pg_duckdb is executing analytics across diverse data sources.

Full pg_duckdb on Azure Database for PostgreSQL

Franck Pachot — Thu, 08 Oct 2026 11:26:47 +0000

In my previous post, Amazon Aurora's analytics is gated DuckDB: comparison with pg_duckdb, I examined Aurora Analytics in detail alongside pg_duckdb. I noted that the pg_duckdb extension, installed on a PostgreSQL container, offers more features. For this article, I configured it on Azure Database for PostgreSQL (Flexible Server), a managed service running the community PostgreSQL with popular open-source extensions.

Enabling pg_duckdb on Azure Database for PostgreSQL

I've run this demonstration on Azure Database for PostgreSQL flexible server newly provisioned in Canada Central region, with 2 vCores, 4 GiB RAM, 32 GiB storage, and PostgreSQL 18.6:

The pg_duckdb extension is a shared_preload_libraries extension, so two settings are needed. First, allow-list it, then add it to the preload libraries and restart.

The cover image of this article shows the manual way in the portal's Server parameters, with two clicks on the checkboxes, or you can do the same via the CLI:

# allow-list the extension
az postgres flexible-server parameter set \
  --resource-group <rg> --server-name <server> \
  --name azure.extensions --value pg_duckdb

# load the library (append to whatever is already there), then restart
az postgres flexible-server parameter set \
  --resource-group <rg> --server-name <server> \
  --name shared_preload_libraries --value "<existing>,pg_duckdb"

az postgres flexible-server restart \
  --resource-group <rg> --name <server>

If you only do the first step, CREATE EXTENSION fails with a clear hint:

ERROR: pg_duckdb needs to be loaded via shared_preload_libraries
HINT:  Add pg_duckdb to shared_preload_libraries.

After the restart, with a normal login, you CREATE EXTENSION in the database where you want to run DuckDB functions:

postgres=> CREATE EXTENSION pg_duckdb
;
CREATE EXTENSION
postgres=> \dx *duckdb*
                         List of installed extensions
   Name    | Version | Default version | Schema |         Description
-----------+---------+-----------------+--------+-----------------------------
 pg_duckdb | 1.1.0   | 1.1.0           | public | DuckDB Embedded in Postgres
(1 row)

postgres=> SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_duckdb';
  extname  | extversion
-----------+------------
 pg_duckdb | 1.1.0

postgres=> SELECT * FROM duckdb.query('SELECT version();');

 "version"()
-------------
 v1.4.3

That's how you load an extension in PostgreSQL — since this is PostgreSQL itself, not a re-implementation. You load pg_duckdb using the same shared_preload_libraries and CREATE EXTENSION commands you'd use anywhere else, and everything afterward works exactly like the standard community server.

Reading Parquet from Azure Blob

The pg_duckdb extension reads object storage through a DuckDB secret. For Blob, a connection string is the simplest form:

SELECT duckdb.create_azure_secret(
  'DefaultEndpointsProtocol=https;AccountName=…;AccountKey=…;EndpointSuffix=core.windows.net'
)
;
 create_azure_secret
---------------------
 azure_secret_42
(1 row)

I uploaded the same title.parquet used in the previous post (500,000 rows: id, title, kind_id, production_year) to a container named lake:

Then a Blob path is just a table source for DuckDB:

postgres=> SELECT count(*) 
           FROM read_parquet('az://lake/job/title.parquet')
;
 count
--------
 500000
(1 row)

postgres=> SELECT r['id'], r['title'], r['production_year']
           FROM read_parquet('az://lake/job/title.parquet') r
           LIMIT 5
;
 id |  title  | production_year
----+---------+-----------------
  1 | Title 1 |            1921
  2 | Title 2 |            1922
  3 | Title 3 |            1923
  4 | Title 4 |            1924
  5 | Title 5 |            1925
(5 rows)

The execution plan is exposed by pg_duckdb:

postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT r['id'], r['title'], r['production_year']
           FROM read_parquet('az://lake/job/title.parquet') r
           LIMIT 5
;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.002 rows=0.00 loops=1)
   Output: id, title, production_year
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT r.id, r.title, r.production_year FROM system.main.read_parquet('az://lake/job/title.parquet'::text) r LIMIT 5
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.374s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      STREAMING_LIMIT      │
 │    ────────────────────   │
 │           5 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │        Projections:       │
 │             id            │
 │           title           │
 │      production_year      │
 │                           │
 │         4,096 rows        │
 │          (0.00s)          │
 └───────────────────────────┘
 Query Identifier: 7627283629006825662
 Planning:
   Buffers: shared hit=1
 Planning Time: 254.221 ms
 Execution Time: 249.641 ms
(51 rows)

This is the DuckDB representation of EXPLAIN ANALYZE.

The same query, the same plan

Here is the query from the last post, run against the Blob file through pg_duckdb on Azure Database for PostgreSQL (Flexible Server):

postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT r['production_year'] AS production_year, count(*) AS count
           FROM read_parquet('az://lake/job/title.parquet') r
           WHERE r['kind_id'] = 1
           GROUP BY r['production_year']
           ORDER BY count DESC
;
                                                                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.002..0.003 rows=0.00 loops=1)
   Output: production_year, count
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT r.production_year AS production_year, count(*) AS count FROM system.main.read_parquet('az://lake/job/title.parquet'::text) r WHERE (r.kind_id = 1) GROUP BY r.production_year ORDER BY (count(*)) DESC
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││               Total Time: 1.14s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_integ│
 │   ral_integer(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │          ORDER_BY         │
 │    ────────────────────   │
 │     count_star() DESC     │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_integra│
 │    l_utinyint(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_decompress_integ│
 │   ral_integer(#0, 1920)   │
 │             #1            │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │   PERFECT_HASH_GROUP_BY   │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │          21 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │      production_year      │
 │                           │
 │        100,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │__internal_compress_integra│
 │    l_utinyint(#0, 1920)   │
 │                           │
 │        100,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │         Function:         │
 │        READ_PARQUET       │
 │                           │
 │        Projections:       │
 │      production_year      │
 │                           │
 │     Filters: kind_id=1    │
 │    Total Files Read: 1    │
 │                           │
 │        100,000 rows       │
 │          (1.28s)          │
 └───────────────────────────┘
 Query Identifier: 4315756270624112827
 Planning:
   Buffers: shared hit=1
 Planning Time: 678.580 ms
 Execution Time: 252.476 ms
(112 rows)

That's how the DuckDB extension displays its execution plan within the PostgreSQL query plan: pg_duckdb draws its box tree. DuckDB is a different engine, gathering execution statistics differently and pg_duckdb displays it as-is so you can understand them better. For example, each box shows that operator's thread-summed CPU time, per-node and not cumulative — unlike PostgreSQL's EXPLAIN ANALYZE, where a parent node's time includes its children.

With a different display, this is the identical plan operations produced though aurora_analytics for the same file in the previous post — down to __internal_compress_integral_utinyint(#0, 1920), DuckDB's frame-of-reference integer compression with the base constant 1920 (the minimum production_year in the data).

Custom Scan (DuckDBScan)
  DuckDB Execution Plan:
  PROJECTION   __internal_decompress_integral_integer(#0, 1920), #1
  ORDER_BY     count_star() DESC
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920), #1
  PERFECT_HASH_GROUP_BY   Groups: #0   Aggregates: count_star()
  PROJECTION   production_year
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920)
  READ_PARQUET
     Function: READ_PARQUET
     Projections: production_year
     Filters: kind_id=1
     ~100,000 rows

It fully matches the open-source pg_duckdb running in a local Docker container. Three independent deployments — a laptop, Aurora, and Azure Database for PostgreSQL — emit the same DuckDB plan for the same query. It is the same engine in all three. But there's more in the pg_duckdb extension.

Where the extension approach actually differs

Same engine, so the interesting part is the packaging. Here Azure's choice — ship the extension itself — leads to concrete, observable differences from Aurora's closed FDW.

1. The engine is directly usable

On Aurora, DuckDB is sealed behind the FDW: read_parquet() as a function, list/struct literals, QUALIFY, duckdb.query() — all are rejected, because only a foreign-table scan is handed to DuckDB. On Azure Database for PostgreSQL, the whole extension surface is present. \dx+ pg_duckdb lists hundreds of objects (235 functions, 23 types, an FDW, casts and operators), and you can call DuckDB directly:

postgres=> SELECT * 
           FROM duckdb.query(
            'SELECT 1 AS a, [1,2,3] AS lst
');
 a |   lst
---+---------
 1 | {1,2,3}
(1 row)

That is a DuckDB list literal evaluated by DuckDB, returned to PostgreSQL as an integer[] array datatype. On Aurora, it's a parser error. If you want DuckDB's SQL dialect — not just its scan — the extension gives it to you.

2. It accelerates your existing PostgreSQL tables

This is the sharpest functional difference. Aurora's aurora_analytics only engages DuckDB for foreign (S3) tables. A query on a regular Aurora table runs on the normal PostgreSQL executor. With pg_duckdb you can push a query on an ordinary heap table through DuckDB:

postgres=> CREATE TABLE local_sales AS
           SELECT g AS id, (g % 1000) AS cust, (random()*100)::int AS amt
           FROM generate_series(1, 200000) g
;
SELECT 200000
postgres=> SET duckdb.force_execution = true
;
postgres=> EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
           SELECT cust, count(*), sum(amt)
           FROM local_sales GROUP BY cust
           ORDER BY 2 DESC LIMIT 5
;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.001 rows=0.00 loops=1)
   Output: cust, count, sum
   DuckDB Execution Plan:
 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 EXPLAIN ANALYZE  SELECT cust, count(*) AS count, sum(amt) AS sum FROM pgduckdb.public.local_sales GROUP BY cust ORDER BY (count(*)) DESC LIMIT 5
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││              Total Time: 0.126s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │           TOP_N           │
 │    ────────────────────   │
 │           Top: 5          │
 │                           │
 │         Order By:         │
 │     count_star() DESC     │
 │                           │
 │           5 rows          │
 │          (0.01s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │         1,000 rows        │
 │          (0.02s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │            cust           │
 │            amt            │
 │                           │
 │        200,000 rows       │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │     Table: local_sales    │
 │                           │
 │        Projections:       │
 │            cust           │
 │            amt            │
 │                           │
 │        200,000 rows       │
 │          (0.18s)          │
 └───────────────────────────┘
 Query Identifier: 6568044750341216650
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.394 ms
 Execution Time: 1.360 ms
(75 rows)

This runs DuckDB scans and aggregation on PostgreSQL tables:

Custom Scan (DuckDBScan)
  TOP_N  Top: 5  Order By: count_star() DESC
  HASH_GROUP_BY  Aggregates: count_star(), sum(#1)
  PROJECTION  cust, amt
  PGDUCKDB_POSTGRES_SCAN  Table: local_sales

DuckDB is now the executor, reading the PostgreSQL heap through PGDUCKDB_POSTGRES_SCAN. Whether that is faster than the native plan depends entirely on the query — it is a win for scans, joins and big aggregations, and a loss for point lookups — but the point here is that the option exists at all, which it does not on Aurora's proprietary extension.

3. It is the same extension you run anywhere

pg_duckdb on Azure Database for PostgreSQL is the same open-source extension (version 1.1.0 here) you can run in Docker on a laptop or on a self-managed server. The plans I showed match my local container exactly. There is no Azure-specific dialect to learn: it is the community extension, managed. That's the freedom PostgreSQL users expect: they run on a managed service because the operations, reliability, and performance provide the best experience, not because they are locked into something different from the open source postgres with extensions.

When pg_duckdb on Azure is different than self-managed?

Being honest, any managed service that packages an extension might also need to impose constraints. The hardening is real, and one capability—write-back to the lake—requires a detour you should be aware of.

Hardening from superuser configuration

Azure Database for PostgreSQL locks pg_duckdb access to local directories. These are all superuser-context settings, and a normal login can't touch them:

postgres=> \dconfig duckdb.*
                             List of configuration parameters
                   Parameter                    |                  Value
------------------------------------------------+-----------
...
 duckdb.allowed_directories                     | az://
...
 duckdb.enable_external_access                  | off
...
(27 rows)

The effect: only az:// is reachable. Local files are blocked outright:

postgres=> SELECT count(*) 
           FROM read_parquet('/tmp/my.parquet')
;
ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: Permission Error: File system LocalFileSystem has been disabled by configuration

This also blocks s3:// and arbitrary https:// URLs. That is a deliberate, security-first posture (DuckDB can otherwise fetch arbitrary URLs), and it is the right default for a managed service, but it does mean the extension is not exactly the "wide open DuckDB" you get self-managed.

These are the extension's own settings, not an Azure bolt-on. duckdb.allowed_directories and duckdb.enable_external_access are upstream pg_duckdb GUCs (added March 2026) — allowed_directories allowlists prefixes that stay reachable even when enable_external_access is off. So Azure is configuring the community extension's built-in allow-list, not patching restrictions on top of it. enable_external_access is also applied at initialization and can't be flipped at runtime afterward — DuckDB rejects changes to allowed_directories once external access is disabled — which is why it reads as a fixed, image-level setting.

Write-back works, but not with the `COPY TO` syntax

In the self-managed world, you can write results back to object storage directly with a PostgreSQL command: COPY (…) TO 's3://…' or az://. As a normal azure_pg_admin login, though, that statement is refused:

postgres=> COPY (
            SELECT cust, count(*) 
            FROM local_sales GROUP BY cust
) TO 'az://lake/exports/agg.parquet'
;
ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

In PostgreSQL, a server-side COPY … TO <file> requires superuser privileges or the pg_write_server_files role, neither of which Azure Database for PostgreSQL grants to a normal login. It also rejects non-absolute paths before the privilege check, so the error you see depends on the path shape:

-- any path that doesn't start with "/" is classed as relative, rejected first:

postgres=> COPY (SELECT 1 a) TO 'az://lake/my.parquet';

ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

postgres=> COPY (SELECT 1 a) TO 'relative/my.csv';

ERROR:  relative path not allowed for COPY to/from a file in Azure Database For PostgreSQL

-- a genuine absolute path gets past that and hits the privilege check:

postgres=> COPY (SELECT 1 a) TO '/tmp/my.csv';

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

postgres=>

The error message names Azure for a reason: the managed service adds its own security gate ahead of PostgreSQL's normal COPY checks, and that gate, like PostreSQL, rejects any path without a leading / as "relative" — az:// included. So an az:// URL is stopped at that first gate before it ever reaches the privilege check, while a genuine absolute path like /tmp/my.csv clears the gate and then hits PostgreSQL's own superuser requirement. Two separate checks, and a normal login is stopped by one or the other.

But write-back is not actually unavailable on Azure Database for PostgreSQL — because the extension exposes the DuckDB engine directly, with its own security that allows access to az://. You can hand the COPY straight to DuckDB with duckdb.raw_query:

postgres=> SELECT duckdb.raw_query($$
             COPY (
               SELECT cust, count(*) AS n
                FROM pgduckdb.public.local_sales GROUP BY cust
             ) TO 'az://lake/exports/agg.parquet' (FORMAT parquet)
           $$);

postgres=> SELECT count(*) FROM      
           read_parquet('az://lake/exports/agg.parquet')
;
 count
-------
  1000
(1 row)

This doesn't bypass any security — it writes through DuckDB's own storage controls instead of PostgreSQL's server-file gate, which is unaware of additional extension's capabilities and object storage URLs. The two protect different things: PostgreSQL's gate restricts writes to local filesystem, fir superuser, while DuckDB governs object-storage access through its secret and allow-list (duckdb.allowed_directories, which here permits only az://). An object-storage write must go through the layer that actually owns that resource.

The file is really written to Blob and reads straight back. Three caveats:

Qualify Postgres tables as pgduckdb.public.<table>, because raw_query runs inside the DuckDB engine, which sees DuckDB's catalog, not PostgreSQL's search path. A bare FROM local_sales fails with Catalog Error: Table with name local_sales does not exist. pg_duckdb mounts your Postgres tables under pgduckdb.public.*, so that's how you name them here.
duckdb.query() won't do it — it only accepts a single SELECT, so the COPY must go through raw_query.
The storage allow-list still applies: you can only write under az:// (a local /tmp target fails with LocalFileSystem has been disabled by configuration).

So write-back is there and under control. It just isn't the PostgreSQL COPY TO, and you reach your tables through the DuckDB catalog name.

This is where the two cloud systems really diverge. Aurora's foreign tables are strictly read-only, and I verified every write attempt — not just one. Both INSERT … VALUES, bulk INSERT … SELECT, UPDATE, and DELETE commands all fail with the same message: "Aurora Analytics foreign tables are read-only — update the source files in S3 … directly". Pointing a foreign table to a new S3 path and trying to insert also fails, suggesting the wrapper is the issue, not a locked file. Additionally, COPY (…) TO 's3://…' results in "COPY to a file is not supported", and the extension does not provide any write, export, or unload functions. The FDW has no write capabilities—the DuckDB engine is sealed behind it.

Conversely, on Azure, pg_duckdb exposes the DuckDB engine itself. Queries can read from and write to the data lake. The key difference is that the engine is accessible rather than sealed behind a restricted interface. pg_duckdb embeds a DuckDB execution engine alongside PostgreSQL, allowing DuckDB operations on both PostgreSQL tables and external data sources.

Summary

	`pg_duckdb` on Microsoft Azure for PostgreSQL	`aurora_analytics` on Amazon Aurora (AWS)
What it is	the open-source `pg_duckdb` extension	a closed extension embedding DuckDB
Engine	✅ DuckDB (identical plan)	✅ DuckDB (identical plan)
How you reach the lake	`read_parquet('az://…')`, `r['col']`	`CREATE FOREIGN TABLE … OPTIONS(format 'parquet')`
Direct DuckDB SQL (`duckdb.query`, list/struct)	✅ Yes	❌ No — sealed behind the FDW
Accelerate your own PostgreSQL tables	✅ Yes (`force_execution`)	❌ No — foreign tables only
Read object storage	✅ Yes (`az://`, secret)	✅ Yes (`s3://`, IAM role)
Write back to the lake	✅ Yes, via `duckdb.raw_query` to use the extension's security model	❌ No — foreign tables read-only
External access	locked to `az://`, superuser-only GUCs	IAM-scoped to S3/Glue
Portability of what you learn	same extension as laptop/self-managed	Aurora-specific

Both clouds embed the same DuckDB engine in PostgreSQL. Aurora wraps it as a managed, catalog-oriented, read-only foreign-data wrapper. Azure ships the community extension, so you can use DuckDB directly, accelerate your existing tables, and even write back to the lake. Azure limits only the granted permissions, without changing the open-source software's behavior. Where the managed guardrails bite, they bite on both (external access is locked down on each), but the extension approach keeps the engine in your hands rather than sealed behind an FDW.

In the next posts of this series, we will go through some examples of using pg_duckdb in Azure Database for PostgreSQL.

Amazon Aurora's analytics is gated DuckDB: comparison with open-source pg_duckdb

Franck Pachot — Mon, 05 Oct 2026 21:09:00 +0000

Microsoft announced the public preview of the pg_duckdb extension for Azure Database for PostgreSQL Flexible Server at Ignite on November 18, 2025. In August 2026, Amazon announced an agreement to acquire DuckLabs, the company behind DuckDB, but the DuckDB open-source project itself remains independent. On September 30, AWS announced that Amazon Aurora PostgreSQL can use embedded DuckDB to query Apache Iceberg and Parquet data in data lakes alongside operational data. Let's compare.

Amazon Aurora PostgreSQL can now query Parquet and Iceberg in S3 through the aurora_analytics extension, and AWS says DuckDB is embedded. I wanted to prove that rather than take it on faith. The method is simple: get an execution plan out of Aurora, then get one out of a known-DuckDB engine (the open-source pg_duckdb) for the same query over the same file, and compare. Here is every step.

Provision Aurora with aurora_analytics

aurora_analytics needs two things: an Aurora PostgreSQL cluster on a supported version (17.11+ or 18.6+), and an IAM role attached to the cluster with the AuroraAnalytics feature so Aurora can read your S3 bucket. I used a CloudFormation stack The essential pieces are:

an Aurora PostgreSQL Serverless v2 cluster, EngineVersion=17.11;
a cluster parameter group with aurora_analytics.enabled = 1;
an IAM role trusting rds.amazonaws.com, with S3 read on the data bucket (and Glue read if you want Iceberg), attached to the cluster via AssociatedRoles with FeatureName: AuroraAnalytics;
an S3 bucket for the Parquet data, plus an S3 gateway VPC endpoint.

I deployed it with CloudFormation:

aws cloudformation deploy --stack-name aurora-analytics ...

aws cloudformation describe-stacks --stack-name aurora-analytics --query 'Stacks[0].Outputs' --output table

I uploaded the Parquet file and connected to Aurora:


aws s3 cp title.parquet s3://my-bucket/job/title.parquet

psql "host=<cluster-endpoint> port=5432 dbname=analytics user=dbadmin sslmode=require"

Query Parquet in Aurora and capture the plan

I enabled the extension and defined a foreign table over the file. The empty column list makes Aurora infer the schema from the Parquet footer:

CREATE EXTENSION aurora_analytics;

CREATE FOREIGN TABLE title_ft ()
  SERVER aurora_analytics_server
  OPTIONS (location 's3://my-bucket/job/title.parquet', format 'parquet');

SELECT count(*) FROM title_ft;

Here is the query I compared, and its plan:

EXPLAIN (ANALYZE, VERBOSE)
 SELECT production_year, count(*)
 FROM title_ft
 WHERE kind_id = 1
 GROUP BY production_year
 ORDER BY 2 DESC
;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (actual time=65.848..65.919 rows=21 loops=1)
  Output: aurora_analytics.production_year, aurora_analytics.count
  Pushdown SQL: SELECT production_year, count(*) AS count FROM (SELECT kind_id::INTEGER AS kind_id, production_year::INTEGER AS production_year FROM system.main.read_parquet($aurora_analytics_parameter_1) AS title_ft) title_ft WHERE (kind_id = 1::INTEGER) GROUP BY production_year ORDER BY (count(*)) DESC
  Total CPU Time: 0.0 ms
  Effective Parallelism: 0.7
  Total Thread Wait Time: 0.0 ms
  ->  PROJECTION (actual time=51.637..51.637 rows=21 loops=1)
        Projections: __internal_decompress_integral_integer(#0, 1920), #1
        ->  ORDER_BY (actual time=51.614..51.614 rows=21 loops=1)
              Order By: count_star() DESC
              ->  PROJECTION (actual time=46.880..46.880 rows=21 loops=1)
                    Projections: __internal_compress_integral_utinyint(#0, 1920), #1
                    ->  PROJECTION (actual time=46.873..46.873 rows=21 loops=1)
                          Projections: __internal_decompress_integral_integer(#0, 1920), #1
                          ->  PERFECT_HASH_GROUP_BY (actual time=46.844..46.844 rows=21 loops=1)
                                Groups: #0
                                Aggregates: count_star()
                                ->  PROJECTION (actual time=16.582..43.982 rows=100000 loops=1)
                                      Projections: production_year
                                      ->  PROJECTION (actual time=16.571..43.982 rows=100000 loops=1)
                                            Projections: __internal_compress_integral_utinyint(#0, 1920)
                                            ->  READ_PARQUET (actual time=14.609..43.981 rows=100000 loops=1)
                                                  Table: title_ft
                                                  Filters: kind_id=1
                                                  Projections: production_year
                                                  Total Files Read: 1
                                                  Rows Removed by Filter: 400000
Query Identifier: 5750582199016602255
Planning Time: 369.237 ms
Execution Time: 81.265 ms
Analytics Cache Hit Bytes: 93kB
Analytics Remote Read Bytes: 0kB
S3 HEAD Request Count: 0
S3 GET Request Count: 0

This is already suggestive: READ_PARQUET, PERFECT_HASH_GROUP_BY, count_star(), and a Pushdown SQL line that reads FROM system.main.read_parquet(...). Those are DuckDB's names. But let's prove it.

The cover image of this post shows the same with the Microsoft PostgreSQL extension for VSCode on Kiro.

The same query in pg_duckdb on Docker

pg_duckdb is the open-source DuckDB-in-PostgreSQL extension. I run it locally:

docker run -d --name pgduckdb -e POSTGRES_PASSWORD=duckdb -e POSTGRES_DB=lab \
  -p 55432:5432 pgduckdb/pgduckdb:17-v1.1.1

# put the SAME file where the container can read it
docker cp title.parquet pgduckdb:/tmp/title.parquet

docker exec -it pgduckdb psql -U postgres -d lab

I execute the same query with the pg_duckdb functions:

CREATE EXTENSION pg_duckdb;
SET duckdb.force_execution = true;

EXPLAIN
 SELECT r['production_year'] AS production_year, count(*) AS count
 FROM read_parquet('/tmp/title.parquet') r
 WHERE r['kind_id'] = 1
 GROUP BY r['production_year']
 ORDER BY count DESC
;

pg_duckdb draws the plan as a box tree:

One rendering difference worth noting: pg_duckdb prints DuckDB's native plan verbatim, so per-node times are DuckDB's thread-summed CPU (a child can read higher than the total wall time). That's different from Aurora which reformats the DuckDB plan into PostgreSQL's convention, where each node's time is cumulative wall-clock and the top node matches Execution Time.

Here are the operators:

Custom Scan (DuckDBScan)
  DuckDB Execution Plan:

  PROJECTION   __internal_decompress_integral_integer(#0, 1920), #1
  ORDER_BY     count_star() DESC
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920), #1
  PERFECT_HASH_GROUP_BY   Groups: #0   Aggregates: count_star()
  PROJECTION   production_year
  PROJECTION   __internal_compress_integral_utinyint(#0, 1920)
  READ_PARQUET
     Function: READ_PARQUET
     Projections: production_year
     Filters: kind_id=1
     ~100,000 rows

We recognize the same functions, presented differently.

Comparison

I put them next to each other:

	Aurora `aurora_analytics`	pg_duckdb (Docker)
Wrapper node	`Custom Scan` (provider `aurora_analytics`)	`Custom Scan (DuckDBScan)`
Scan	`READ_PARQUET` Filters: kind_id=1, Projections: production_year	`READ_PARQUET` Filters: kind_id=1, Projections: production_year
Group by	`PERFECT_HASH_GROUP_BY` / `count_star()`	`PERFECT_HASH_GROUP_BY` / `count_star()`
Sort	`ORDER_BY count_star() DESC`	`ORDER_BY count_star() DESC`
Compression op	`__internal_compress_integral_utinyint(#0, 1920)`	`__internal_compress_integral_utinyint(#0, 1920)`
Decompression op	`__internal_decompress_integral_integer(#0, 1920)`	`__internal_decompress_integral_integer(#0, 1920)`

They are the same plan. The detail that removes all doubt is the compression operator: both engines, independently, encoded production_year with __internal_compress_integral_utinyint(#0, 1920). That is DuckDB's frame-of-reference integer compression, and 1920 is the minimum production_year in the file — the base value DuckDB subtracts so the column fits in an unsigned byte. A separate implementation would not reproduce DuckDB's internal operator names and independently pick the same base constant from the
data. Aurora’s plan exposes DuckDB operators, indicating that aurora_analytics uses DuckDB or DuckDB-derived components under its Foreign Data Wrapper.

How differently they package it

It's the same engine, but opposite exposure. We can look at what each extension registers.

With pg_duckdb the whole DuckDB surface is SQL-visible:

postgres=# \dx+ pg_duckdb
                                                                                                                                                      Objects in extension "pg_duckdb"
                                                                                                                                                             Object description
--------------------------------------------------------------------
 access method duckdb
 cast from duckdb.unresolved_type to bigint
 cast from duckdb.unresolved_type to bigint[]
 ...

The full output shows 366 objects, including 235 function entries (overloads counted separately), 1 foreign-data wrapper (duckdb), and 23 type entries (including arrays). The remaining 107 objects include casts, operators, triggers, tables, and other objects.

With aurora_analytics the engine is sealed behind an FDW:

postgres=# \dx+ aurora_analytics
                                                                                                                                                      Objects in extension "aurora_analytics"
                                                                                                                                                             Object description
--------------------------------------------------------------------
foreign-data wrapper aurora_analytics_fdw
function aurora_analytics_fdw_handler()
function aurora_analytics_fdw_validator(text[],oid)
server aurora_analytics_server

In Aurora the DuckDB SQL dialect is not reachable — read_parquet() as a function, list/struct literals, QUALIFY, approx_count_distinct, ::HUGEINT are all rejected by PostgreSQL’s SQL layer, because only the foreign-table scan is handed to DuckDB. With pg_duckdb you can call the engine directly (duckdb.query('…')).

So, it's the same DuckDB underneath, but without exposing its functions. in summary:

	pg_duckdb	aurora_analytics
Where it runs	Any PostgreSQL (open source)	Aurora PostgreSQL only (17.11+/18.6+)
Engine	DuckDB	DuckDB, embedded behind a foreign-data wrapper
Lake interface	`read_parquet('…') r` with `r['col']`	`CREATE FOREIGN TABLE … OPTIONS(format 'parquet')`, schema inferred
Direct DuckDB SQL	Yes — `duckdb.query()`, DuckDB dialect, extensions	No — engine sealed; only foreign-table scans
Accelerates your own PostgreSQL tables	Yes — `SET duckdb.force_execution = true`	No — foreign (lake) tables only
Write back to the lake	Yes — `COPY (…) TO 's3://…'` / `az://`	No — read-only
Join live tables with the lake	Yes	Yes
Iceberg / Delta	`iceberg_scan` / `delta_scan` (DuckDB extensions)	Iceberg via Glue catalog; no Delta
Credentials	DuckDB secret (connection string / keys)	IAM role attached to the cluster
Memory / spill knob	`duckdb.max_memory`	`aurora_analytics.query_mem`
Observability	`EXPLAIN`, `duckdb.log_pg_explain`	`EXPLAIN`, `aurora_analytics_stat_statements()`

AWS's acquisition of DuckDB should not affect the open-source DuckDB and pg_duckdb. Here are some quotes from the Founder & CEO of MotherDuck (from his blog post):

Some day, Amazon will likely release their own service based on DuckDB. After all, they’re not acquiring Duck Labs just because they love open source.

That's what they did with aurora_analytics, even though it's a proprietary foreign data wrapper in Amazon Aurora rather than a separate service.

The core open-source engine remains fully open. Tigani notes:

Duck Labs is being kept as a wholly-owned subsidiary, with its organization intact. The Duck Labs team will still be working in open source. The DuckDB Foundation has iron clad control over the DuckDB IP, and DuckDB license is going to stay permissively open.

Because pg_duckdb is governed under the DuckDB Foundation's intellectual property, it is similarly protected and will remain community-driven and open-source.

How to Do Pagination Right: Row Comparison, Datatypes, and Index Range Scans

Franck Pachot — Mon, 05 Oct 2026 13:46:52 +0000

OFFSET is simple to implement, but the database still needs to read and discard rows before reaching the requested page. Keyset pagination improves this by beginning after the last row from the previous page. Since different databases have various query planner optimizations and index access methods, the only way to ensure correctness is to examine the execution plan.

The usual example, which I take from Vlad Mihalcea's blog post, orders posts by date and uses the identifier as a unique tie-breaker:


ORDER BY created_on DESC, id DESC

The next page should begin after the last row, and the most effective way to specify this in SQL is through row comparison:


WHERE (created_on, id) < (:last_created_on, :last_id)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

There are two details that make the difference between a direct index seek and an index scan with a filter:

Use a row comparison when the database can turn it into a composite index boundary.
Pass values with exactly the right data types.

The second point is easy to miss. A query might look flawless and produce correct results, yet still omit the second index column because it compares a bigint with a numeric. Whether you're a developer or an agent, always verify this by checking the execution plan.

Reproducing it on PostgreSQL

This is the table and index used in the original example:


CREATE TABLE post (
    created_on timestamp(6),
    id         bigint NOT NULL,
    title      varchar(255),
    PRIMARY KEY (id)
);

CREATE INDEX idx_post_created_on_id
    ON post (created_on DESC, id DESC);

The complete reproductions are available for PostgreSQL 18 and PostgreSQL 15 which I used in a Linkedin discussion.

The best predicate: one correctly typed row comparison

SQL can compare rows. The SQL standard defines the comparison predicate, including greater than, with row value constructors:


EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on                      , id           )
    < ( timestamp '2019-10-02 21:00:00' , 4951::bigint )
ORDER BY created_on DESC, id DESC
LIMIT 50;

PostgreSQL puts the complete row comparison in the Index Cond:


Limit (actual rows=50.00 loops=1)
  Buffers: shared hit=39
  ->  Index Only Scan using idx_post_created_on_id on post
        (actual rows=50.00 loops=1)
        Index Cond: (ROW(created_on, id) <
                     ROW('2019-10-02 21:00:00'::timestamp without time zone,
                         '4951'::bigint))
        Heap Fetches: 50
        Index Searches: 1
        Buffers: shared hit=39
Planning:
  Buffers: shared hit=60 read=4
Planning Time: 1.925 ms
Execution Time: 0.308 ms

This is what I want for pagination. The B-tree navigates to the composite value and reads the next 50 entries in index order. No sort or filter is needed. It seeks directly to the index entry of the first row to fetch, and it reads only what is necessary for the result.

The id is not optional here. Many posts can have the same timestamp. Pagination needs a total and stable order, so the last ordering column must make the key unique.

The exact OR expression is correct, but not as good on PostgreSQL

A row comparison is equivalent, for non-null values, to:

    created_on < :last_created_on
OR (
    created_on = :last_created_on
    AND id < :last_id
)

This query returns the same rows:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2019-10-02 21:00:00'
   OR (
      created_on = timestamp '2019-10-02 21:00:00'
      AND id < 4951::bigint
   )
ORDER BY created_on DESC, id DESC
LIMIT 50;

But PostgreSQL keeps the expression as a filter, after scanning all rows:

Limit (actual rows=50.00 loops=1)
  Buffers: shared hit=39
  ->  Index Only Scan using idx_post_created_on_id on post
        (actual rows=50.00 loops=1)
        Filter: ((created_on <
                  '2019-10-02 21:00:00'::timestamp without time zone)
              OR ((created_on =
                   '2019-10-02 21:00:00'::timestamp without time zone)
                  AND (id < '4951'::bigint)))
        Heap Fetches: 50
        Index Searches: 1
        Buffers: shared hit=39
Planning:
  Buffers: shared hit=3
Planning Time: 0.133 ms
Execution Time: 0.049 ms

This small test does not show a performance problem because the first entries happen to qualify. The important difference is the plan operation: Filter is not Index Cond.

With many rows sharing the cursor timestamp, or with a cursor deep in the index, this form may scan and reject many entries before returning 50 rows. The row comparator gives PostgreSQL the composite boundary directly.

Do not replace it with one of these:

-- Too restrictive
created_on < :created_on AND id < :id

-- Too broad
created_on < :created_on OR id < :id

-- Also wrong: it loses older rows having a larger id
created_on <= :created_on AND id < :id

Lexicographic order needs the equality prefix:


created_on < :created_on
OR (created_on = :created_on AND id < :id)

This is logically equivalent to the row comparison, but database query planners rarely recognize x < y OR x = y as a single bound.

The datatype can silently remove part of the seek

Now I change only one value:


EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on                      , id            )
    < ( timestamp '2019-10-02 21:00:00' , 4951::numeric )
ORDER BY created_on DESC, id DESC         -------------
LIMIT 50;

The column is bigint, but the value is numeric. Here is the PostgreSQL plan:

Limit (actual rows=50.00 loops=1)
  Buffers: shared hit=39
  ->  Index Only Scan using idx_post_created_on_id on post
        (actual rows=50.00 loops=1)
        Index Cond: (created_on <=
                     '2019-10-02 21:00:00'::timestamp without time zone)
        Filter: (ROW(created_on, (id)::numeric) <
                 ROW('2019-10-02 21:00:00'::timestamp without time zone,
                     '4951'::numeric))
        Heap Fetches: 50
        Index Searches: 1
        Buffers: shared hit=39
Planning:
  Buffers: shared hit=9
Planning Time: 0.288 ms
Execution Time: 0.107 ms

The significant lines are:

Index Cond: (created_on <= ...)
Filter: (ROW(created_on, (id)::numeric) < ...)

The index stores id as bigint, but the comparison casts the indexed column to numeric. The second column is no longer usable as the bigint B-tree boundary.

PostgreSQL still extracts a safe condition from the leading column:

created_on <= :last_created_on

It must be <=, not <, because rows at the same timestamp can qualify when their identifier is lower. PostgreSQL then evaluates the original row expression as a filter to preserve the correct result.

The optimizer source calls this a lossy version of the row comparison. It means that the index condition identifies a superset of the required rows and the complete condition must be rechecked.

The fix is only a cast, but it must be on the value:

WHERE (created_on, id)
    < (:last_created_on::timestamp, :last_id::bigint)

Do not cast the indexed column (except if it is indexed with an expression-based index):

-- Avoid this
WHERE (created_on, id::numeric)
    < (:last_created_on, :last_id::numeric)

For a column defined as bigint, the application should bind the cursor as int8/bigint, not as numeric or BigDecimal when that changes the PostgreSQL parameter type.

A prepared statement makes the contract explicit:

PREPARE next_page(timestamp, bigint) AS
SELECT id, created_on
FROM post
WHERE (created_on, id) < ($1, $2)
ORDER BY created_on DESC, id DESC
LIMIT 50;

A prepared statement or function is the best way to ensure the execution plan uses the right data type, regardless of what is passed.

A test that exposes wasted index work

The previous fiddle helps you inspect the plan, but its data doesn't magnify the difference. This creates one million rows, with 500,000 rows sharing each timestamp:

DROP TABLE IF EXISTS post;

CREATE UNLOGGED TABLE post (
    id         bigint PRIMARY KEY,
    created_on timestamp NOT NULL,
    title      text
);

INSERT INTO post
SELECT g,
       timestamp '2024-01-01'
         - ((g - 1) / 500000) * interval '1 day',
       'post ' || g
FROM generate_series(1, 1000000) AS g;

CREATE INDEX post_seek_idx
    ON post (created_on DESC, id DESC);

VACUUM (ANALYZE) post;

Run the three alternatives separately:

-- Exact composite boundary
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
    < (timestamp '2024-01-01', 100::bigint)
ORDER BY created_on DESC, id DESC
LIMIT 50;

-- Only a leading-column boundary because bigint is compared with numeric
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
    < (timestamp '2024-01-01', 100::numeric)
ORDER BY created_on DESC, id DESC
LIMIT 50;

-- Exact logical expansion, but commonly retained as a filter
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2024-01-01'
   OR (
       created_on = timestamp '2024-01-01'
       AND id < 100::bigint
   )
ORDER BY created_on DESC, id DESC
LIMIT 50;

The first query can position near (2024-01-01,99). The other two may start near the beginning of the 500,000 entries for 2024-01-01 and filter almost all of them.

Always run this with your distribution and parameters. A query is not efficient because it contains Index Scan. Inspect Index Cond, Filter, rows removed, buffers, and the number of entries actually read.

Which databases support an index for row comparison?

The short answer depends on whether “support” means syntax or a direct composite index boundary.

Database	Ordered row syntax	Composite-index behavior for pagination
PostgreSQL	✅	Yes , with compatible B-tree operators and matching datatypes
YugabyteDB	✅	The original PostgreSQL-style pattern is still not an exact DocDB seek. A new hash-aware form exists
MySQL	✅	Yes when the row is the leftmost index prefix. Important limitation when it follows separate key predicates
MongoDB	❌	The exact `$or` can use two index scans combined with `SORT_MERGE`
Oracle	❌	Use scalar predicates. Typically a leading range plus filter, or bounded `UNION ALL` branches
SQL Server	❌	The exact `OR` can become multiple ranges in one ordered `Index Seek`

Accepting the syntax is not a sufficient test because the goal of this pagination filter is to read only what is necessary. The execution plan is the evidence. Here are some detailed tests

YugabyteDB: improved, but the old case still needs the workaround

I wrote about this in Efficient pagination in YugabyteDB & PostgreSQL.

The index was:

CREATE UNIQUE INDEX demo1_key_ts_id
ON demo1(key, ts DESC, id DESC);

In YugabyteDB, the first unspecified index column is hash-sharded, so this is effectively:

(key HASH, ts DESC, id DESC)

The elegant PostgreSQL query was:

WHERE key = $1
  AND (ts, id) < ($2, $3)
ORDER BY ts DESC, id DESC
LIMIT $4

On YugabyteDB 2.8, the complete historical plan showed why Index Cond was not sufficient evidence:

Limit  (actual time=26446.024..26748.760 rows=1000 loops=1)
  ->  Nested Loop
        ->  Index Scan using demo1_key_ts_id on demo1
              Index Cond:
                ((key = 1)
                 AND
                 (ROW(ts, id) <
                  ROW('2022-01-01 00:00:01+00'::timestamp with time zone,
                      '000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
              Rows Removed by Index Recheck: 998000
Planning Time: 1.159 ms
Execution Time: 26751.767 ms

The storage layer had not started at the complete row value. PostgreSQL rechecked and rejected 998,000 entries.

The workaround was to provide a scalar bound for the first range column:

WHERE key = $1
  AND ts <= $2
  AND (
       ts < $2
       OR (ts = $2 AND id < $3)
  )
ORDER BY ts DESC, id DESC
LIMIT $4

The historical plan became:

Limit  (actual time=18.769..305.666 rows=1000 loops=1)
  ->  Nested Loop
        ->  Index Scan using demo1_key_ts_id on demo1
              Index Cond:
                ((key = 1)
                 AND
                 (ts <=
                  '2022-01-01 00:00:01+00'::timestamp with time zone))
              Filter:
                ((ts <
                  '2022-01-01 00:00:01+00'::timestamp with time zone)
                 OR
                 ((ts =
                   '2022-01-01 00:00:01+00'::timestamp with time zone)
                  AND
                  (id <
                   '000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Planning Time: 1.039 ms
Execution Time: 309.985 ms

What about now?

YugabyteDB 2026.1 added a useful row boundary for hash indexes, but it is a different key shape. It starts with the hash code and includes a contiguous key prefix:

CREATE TABLE hc_rc_t1 (
    h  int,
    r1 int,
    r2 int,
    v  text,
    PRIMARY KEY (h HASH, r1 ASC, r2 ASC)
);

INSERT INTO hc_rc_t1
SELECT i % 5, i, i * 10, 'val' || i
FROM generate_series(1, 50) AS i;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT *
FROM hc_rc_t1
WHERE (yb_hash_code(h), h, r1, r2)
      > (yb_hash_code(1), 1, 10, 100)
LIMIT 5;

The released 2026.1.2 regression plan is:

Limit (actual rows=5 loops=1)
  ->  Index Scan using hc_rc_t1_pkey on hc_rc_t1
        (actual rows=5 loops=1)
        Index Cond:
          (ROW(yb_hash_code(h), h, r1, r2)
           > ROW(4624, 1, 10, 100))

Without the leading hash code:

SELECT *
FROM hc_rc_t1
WHERE (h, r1, r2) > (1, 10, 100)
LIMIT 5;

the released test expects:

Limit (actual rows=5 loops=1)
  ->  Seq Scan on hc_rc_t1
        Filter: (ROW(h, r1, r2) > ROW(1, 10, 100))
        Rows Removed by Filter: 2

This does not fix the original portable predicate:

key = :key AND (ts, id) < (:ts, :id)

Issue #11794 remains open, and a July 2026 issue still describes those row-comparison bounds as loose bounds requiring recheck. For the original index and ordering, I would still use the decomposed predicate and verify it with:

EXPLAIN (ANALYZE, DIST, COSTS OFF)

In YugabyteDB, check Storage Index Rows Scanned and Rows Removed by Index Recheck, not only Index Cond.

MySQL: native row comparison, with a prefix caveat

MySQL 8.4 supports the same lexicographic syntax:

CREATE TABLE post (
    id         bigint NOT NULL PRIMARY KEY,
    created_on datetime(6) NOT NULL,
    payload    varchar(100),
    INDEX post_seek_i (created_on DESC, id DESC)
) ENGINE = InnoDB;

EXPLAIN ANALYZE
SELECT id, created_on
FROM post
WHERE (created_on, id)
    < ('2026-01-01 12:00:00', 50000)
ORDER BY created_on DESC, id DESC
LIMIT 50;

With the row constructor covering the leftmost index prefix, the desired plan shape is:

Limit: 50 row(s)
  -> Index range scan on post using post_seek_i

But MySQL documents an important exception. With:

CREATE INDEX post_tenant_seek_i
    ON post (tenant_id, created_on DESC, id DESC);

WHERE tenant_id = ?
  AND (created_on, id) < (?, ?)

the row starts at the second index part. MySQL may use only tenant_id. Its documented example reports:

type: ref
key: PRIMARY
key_len: 4
Extra: Using where

Expanding the row comparison:

WHERE tenant_id = ?
  AND (
       created_on < ?
       OR (created_on = ? AND id < ?)
  )

allows the documented example to use all three key parts:

type: range
key: PRIMARY
key_len: 12
Extra: Using where

So MySQL supports index range access for row comparison, but do not generalize that to every position in a composite index. Check access_type, used_key_parts, actual rows, and using_filesort.

Reference: MySQL row-constructor optimization.

MongoDB: no tuple syntax, but two index seeks

MongoDB has no equivalent of:

(created_on, id) < (:created_on, :id)

For this index:

db.events.createIndex(
  {created_on: -1, _id: -1},
  {name: "created_on_-1__id_-1"}
);

use the exact lexicographic expansion:

const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");

const after = {
  $or: [
    {created_on: {$lt: T}},
    {created_on: T, _id: {$lt: I}}
  ]
};

db.events.find(after)
  .sort({created_on: -1, _id: -1})
  .limit(50);

Here is a complete small reproduction:

db.events.drop();

db.events.insertMany([
  {_id:ObjectId("000000000000000000000006"), created_on:ISODate("2026-01-01T12:02:00Z"), n:"A"},
  {_id:ObjectId("000000000000000000000005"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"B"},
  {_id:ObjectId("000000000000000000000004"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"C"},
  {_id:ObjectId("000000000000000000000003"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"D"},
  {_id:ObjectId("000000000000000000000002"), created_on:ISODate("2026-01-01T12:00:00Z"), n:"E"},
  {_id:ObjectId("000000000000000000000001"), created_on:ISODate("2026-01-01T11:59:00Z"), n:"F"}
]);

db.events.createIndex(
  {created_on:-1, _id:-1},
  {name:"created_on_-1__id_-1"}
);

const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");

const q = {
  $or: [
    {created_on: {$lt:T}},
    {created_on:T, _id:{$lt:I}}
  ]
};

db.events.find(q, {n:1, created_on:1})
  .sort({created_on:-1, _id:-1})
  .limit(3)
  .hint("created_on_-1__id_-1")
  .explain("executionStats");

MongoDB can plan this as:

LIMIT
└─ FETCH
   └─ SORT_MERGE
      ├─ IXSCAN created_on_-1__id_-1
      └─ IXSCAN created_on_-1__id_-1

SORT_MERGE merges two already ordered index streams. It is not a blocking in-memory SORT. MongoDB 9.0 regression tests require this shape for sortable $or queries.

Explain output varies between the classic and slot-based engines. Inspect the complete result for:

{
  executionStats: {
    nReturned: 3,
    totalKeysExamined: 3,
    totalDocsExamined: 3,
    executionStages: {
      stage: "LIMIT",
      inputStage: {
        stage: "FETCH",
        inputStage: {
          stage: "SORT_MERGE",
          inputStages: [
            {
              stage: "IXSCAN",
              indexName: "created_on_-1__id_-1"
            },
            {
              stage: "IXSCAN",
              indexName: "created_on_-1__id_-1"
            }
          ]
        }
      }
    }
  }
}

This is the plan structure and the decisive counters, not a captured complete JSON plan from a specific server. Run the reproduction on the target MongoDB version because the optimizer can also choose one broader index scan with a residual $or.

An array expression is not a replacement:

{$expr: {$lt: [["$created_on", "$_id"], [T, I]]}}

It compares a computed array and does not provide compound index bounds. Materializing an array field is also different because it becomes a multikey index.

Oracle: no ordered row comparison

Oracle didn't implement the full SQL standard. it supports expression lists for equality and IN, but not:

(created_on, id) < (:created_on, :id)

Use:

CREATE TABLE post (
    id         number NOT NULL,
    created_on timestamp NOT NULL,
    payload    varchar2(100),
    CONSTRAINT post_pk PRIMARY KEY (id)
);

CREATE INDEX post_seek_i ON post (created_on, id);

SELECT id, created_on, payload
FROM post
WHERE created_on <= :last_created_on
  AND (
       created_on < :last_created_on
       OR (
           created_on = :last_created_on
           AND id < :last_id
       )
  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

The redundant created_on <= :last_created_on exposes a simple leading range to the optimizer. The desired shape is:

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN DESCENDING POST_SEEK_I

Predicate Information:
  access("CREATED_ON" <= :LAST_CREATED_ON)
  filter("CREATED_ON" < :LAST_CREATED_ON
         OR ("CREATED_ON" = :LAST_CREATED_ON
             AND "ID" < :LAST_ID))

That is a leading-column range followed by the exact filter, not one native two-column seek.

If the filter reads too many entries, use two disjoint bounded branches:

WITH same_time AS (
  SELECT id, created_on, payload
  FROM post
  WHERE created_on = :last_created_on
    AND id < :last_id
  ORDER BY created_on DESC, id DESC
  FETCH FIRST 50 ROWS ONLY
),
older AS (
  SELECT id, created_on, payload
  FROM post
  WHERE created_on < :last_created_on
  ORDER BY created_on DESC, id DESC
  FETCH FIRST 50 ROWS ONLY
)
SELECT *
FROM (
  SELECT * FROM same_time
  UNION ALL
  SELECT * FROM older
)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

Each branch can have its own index range. Fetching at most one page from each disjoint branch is sufficient to produce one global page.

Capture actual operations and predicates with:

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  NULL, NULL, 'ALLSTATS LAST +PREDICATE +OUTLINE'
));

The displayed Oracle plan above is the target shape, not a plan captured for this draft.

SQL Server: no tuple syntax, but multiple ranges in one seek

T-SQL does not support an ordered row constructor. Use the exact disjunction:

CREATE INDEX IX_post_seek
ON dbo.Post (created_on DESC, id DESC)
INCLUDE (payload);

SELECT TOP (@page_size)
       id, created_on, payload
FROM dbo.Post
WHERE created_on < @last_created_on
   OR (
       created_on = @last_created_on
       AND id < @last_id
   )
ORDER BY created_on DESC, id DESC;

SQL Server can compile the two disjoint ranges into one ordered Index Seek:

Top
  Index Seek
    Object: IX_post_seek
    Ordered: True
    Scan Direction: FORWARD
    Seek Predicates:
      1. created_on < @last_created_on
      2. created_on = @last_created_on
         AND id < @last_id

This is not native tuple syntax, but it has the required physical behavior. In the XML or graphical plan, confirm that both branches are under Seek Predicates, not only under a residual Predicate.

For more columns, continue the equality prefixes:

WHERE (a = @a AND b = @b AND c = @c AND d > @d)
   OR (a = @a AND b = @b AND c > @c)
   OR (a = @a AND b > @b)
   OR  a > @a
ORDER BY a, b, c, d;

The plan shape above is documented from reproducible SQL Server investigations, but it was not captured on a SQL Server instance for this draft.

Direction, NULLs, and concurrent changes

For a homogeneous order, the comparator reverses with the direction:

ORDER BY a ASC,  b ASC  -> (a,b) > (:a,:b)
ORDER BY a DESC, b DESC -> (a,b) < (:a,:b)

For mixed directions, one tuple operator does not express the order:

-- ORDER BY created_on DESC, id ASC
WHERE created_on < :created_on
   OR (
       created_on = :created_on
       AND id > :id
   )

The index must use the same mixed directions when the database supports them:

(created_on DESC, id ASC)

Pagination columns should preferably be NOT NULL. A comparison with NULL is not true, and databases may order nulls differently. If nulls are part of the application order, encode that order explicitly and index the same expression.

Finally, keyset pagination does not freeze the dataset. Separate requests under READ COMMITTED can observe inserts, deletes, or updates to ordering columns between pages. Decide whether this is acceptable for a live feed or whether all pages must use one consistent snapshot.

The practical rule

For PostgreSQL, start with:

WHERE (created_on, id)
    < (:last_created_on::timestamp, :last_id::bigint)
ORDER BY created_on DESC, id DESC
LIMIT :page_size

Then read the plan:

the complete row value should be in Index Cond
there should be no residual cursor Filter
the index should provide the order
the number of entries read should be close to the pagination size

For other databases, preserve the same lexicographic semantics but use the form their optimizer can seek:

PostgreSQL: correctly typed row comparison
YugabyteDB: verify storage pushdown, the original hash-plus-range case still needs the scalar range workaround
MySQL: row comparison when it covers the leftmost index prefix, otherwise expand and inspect all used key parts
MongoDB: two $or branches and SORT_MERGE
Oracle: leading range plus filter, or two bounded branches
SQL Server: expanded predicate compiled into multiple seek ranges.

The SQL text is only the request. The execution plan tells you where pagination really starts.

Does Hibernate generate the best for each database?

Hibernate ORM does not use the dialect capability flags to choose the predicate shape for keyset pagination. For ORDER BY created_on DESC, id DESC it generates created_on < ? OR (created_on = ? AND id < ?), i.e. exactly the "exact OR expression" shown above, on every database, including PostgreSQL where the row comparison (created_on, id) < (?, ?) would yield an Index Cond composite boundary.

Hibernate does contain dialect-aware machinery for row comparisons, true by default and overridden to false by Oracle, SQL Server, DB2, Sybase, HSQL and others, but it only applies to tuple/subquery comparisons written explicitly in HQL, not to keyset pagination.

When a dialect must emulate a tuple comparison, the generation depends on an indexOptimized flag:

the "Normal" form produces the top-level OR (a > 1 or a = 1 and b > 2), which defeats index seeks as we have seen above
the "Optimized" form, selected by the dialects that lack row-value syntax (Oracle, SQL Server, DB2, Sybase, etc.), emits a >= 1 and not (a = 1 and b <= 2) — a top-level AND with a broadened leading bound that the optimizer can turn into an index range and then recheck, the same "leading range plus filter" that we have seen above.

However, the key set pagination doesn't use this and then doenst have this optimization

So on PostgreSQL and MySQL 8.0.30+, Hibernate's output is correct but uses the weaker filterable form instead of a composite index boundary. On SQL Server the expansion can still be compiled into multiple seek ranges, and on Oracle it may produce the scan-plus-filter plan unless you write the bound predicate yourself.

In short, Hibernate guarantees correct results everywhere, but guarantees the optimal plan nowhere — the same 'verify with EXPLAIN' rule applies to ORM-generated SQL as to hand-written SQL.

DEV Community: Franck Pachot

Patroni, Ubuntu 24.04, and two unplanned PostgreSQL failovers

What was running

Root cause analysis

Prove that unattended upgrades actually ran

What package changed, and when?

Where did the systemctl restart command come from?

Why would a zlib upgrade restart Patroni?

But why would Patroni have zlib loaded?

What about other releases and distributions?

Ubuntu before and after 24.04

Debian, RHEL and Fedora

Every VM had its own maintenance window

Patroni and PostgreSQL logs show two failovers

Why "automatic reboot disabled" did not protect me

Durability is not availability

Resilience includes controlling avoidable failures

What I changed to avoid the issue happening again

The lesson

PostgreSQL document compression: TOAST thresholds, storage policies, and page size

What is compressed, and what is measured?

Reproduce the environment and output

A compressible document below the activation threshold

The hardcoded gate is not the per-table target

Change the target without changing activation

Storage strategies: what SQL can control

The compression algorithm still matters after activation

Larger pages: fewer documents become compression candidates

8 KB: all but the smallest repeated-field document compress

16 KB: the 3,868-byte JSONB document no longer compresses

32 KB: the 7,708-byte document also stops compressing

UPDATE has the same outer gate

Chunk capacity does grow

Why this behavior remains

If you want to build and run the three page sizes

Conclusion: separate compression eligibility from row placement

Load CSV and JSON from Azure Blob into Postgres with pg_duckdb

Setup

The files in Blob

Read a CSV with inferred types

Materialize into a real PostgreSQL table

Same thing for JSON

Or don't import at all

The reverse direction: writing a result back out

A few practical notes

Takeaways

pg_duckdb quietly ignored force_execution on my numeric column

Reproduce

The symptom

Why

The fix

Takeaway

Query a partitioned data lake in Azure Blob with pg_duckdb

Setup

A partitioned layout

Read them all with one glob

Turn the folder name into a column

When file schemas evolve

The payoff: partition pruning, visible in the plan

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

Takeaways

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

Lab setup

Write a minimized fact file

The join before erasure

Delete the identity, not the history

The full plan: which side is hashed?

One top-level engine over heterogeneous sources

What statistics does DuckDB see?

What if the filter starts from the customer?

Pushdown is not the same as row-group pruning

What this pattern does and does not guarantee

Takeaways

Automate Postgres-to-Blob with durable workflow (pg_durable + pg_duckdb) in Azure Database for PostgreSQL

Enabling pg_durable

The data to archive

The durable workflow

Watching it run

It worked, end to end

Why durable, not just a cron job

Where did the `systemctl restart` command come from?

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

Use `pg_hint_plan` to force at the query scope

Write-back works, but not with the `COPY TO` syntax