DEV Community

Cover image for Amazon Aurora's analytics is DuckDB: a reproducible side-by-side with pg_duckdb
Franck Pachot
Franck Pachot

Posted on

Amazon Aurora's analytics is DuckDB: a reproducible side-by-side with pg_duckdb

#postgres #duckdb #aws #azure

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
;
Enter fullscreen mode Exit fullscreen mode

pg_duckdb draws the plan as a box tree:

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
Enter fullscreen mode Exit fullscreen mode

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[]
 ...
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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()

Top comments (0)