What changes when you attach DuckDB alongside PostgreSQL that's struggling with analytical queries

If you've ever run queries like monthly revenue aggregation or cohort analysis on a production PostgreSQL instance, you know the frustration. While the query runs, other API responses slow down, and the DBA peers at pg_stat_activity and tells you "the analytics query is eating up all the shared_buffers." Yet attaching Snowflake or BigQuery right now feels burdensome in both cost and pipeline engineering effort.

The DuckDB sidecar pattern resolves this dilemma quite practically. It leaves PostgreSQL in place as the transactional backend and attaches DuckDB alongside as a dedicated analytics engine. Because analytic query execution happens outside the PostgreSQL process, it doesn't consume the production DB's buffer pool, WAL, or connection resources. The key is workload isolation.

This article covers how to connect PostgreSQL directly to DuckDB using DuckDB's postgres extension (formerly postgres_scanner), how to export data to Parquet files and query them, and which patterns are actually usable as of 2026. Benchmark numbers won't be exaggerated, and the downsides will be addressed honestly.

Why It's Hard to Get By with PostgreSQL Alone

Row-Oriented vs. Column-Oriented — That Difference Is Everything

PostgreSQL stores data in row units. For transactions, this is perfect. When you quickly read or update a specific order, all the columns you need are in the same place on disk. On the other hand, computing "the monthly purchase total for a specific product category over the past 12 months" requires scanning millions of rows while using only 2–3 columns. The rest of the column data is read and then discarded.

DuckDB handles these queries with columnar storage and a vectorized execution engine. It reads only the needed columns and batch-processes them using CPU SIMD instructions. This is the fundamental reason for the OLAP performance difference.

What readers need to understand precisely about the sidecar pattern is "which process actually runs the aggregation." The diagram below shows that boundary.

Why the Sidecar Pattern Is Getting Attention Now

DuckDB itself has been around for years. The reason it's increasingly discussed as a low-cost alternative to so-called Budget HTAP (Hybrid Transactional/Analytical Processing, an architecture that handles both transactions and analytics in one stack) is that the ecosystem has matured. MotherDuck officially released pg_duckdb v1.0 in May 2025, making reverse integration possible — embedding the DuckDB engine inside the PostgreSQL process (MotherDuck release notes) — and DuckDB itself has steadily expanded its open table format extensions such as Iceberg and Delta. The pattern of achieving OLAP performance with a PostgreSQL + DuckDB combination, without a separate data warehouse, is spreading.

The postgres Extension — Querying PostgreSQL Directly from DuckDB

From Connection to Query

Installing DuckDB's official postgres extension (official name as of 2026, formerly postgres_scanner) lets you connect to a running PostgreSQL instance with the ATTACH command.

-- Inside a DuckDB shell or application
INSTALL postgres;
LOAD postgres;
 
-- Connect to a PostgreSQL instance
ATTACH 'host=localhost port=5432 dbname=mydb user=analyst password=secret'
    AS pg (TYPE postgres);
 
-- Aggregate a PostgreSQL table from DuckDB
SELECT
    date_trunc('month', created_at) AS month,
    COUNT(*)                        AS order_count,
    SUM(amount)                     AS total_amount
FROM pg.public.orders
GROUP BY 1
ORDER BY 1;

This way, DuckDB handles the aggregation while PostgreSQL is responsible only for data transfer. It leverages PostgreSQL's binary protocol to avoid text-conversion overhead and supports predicate pushdown, which requests only the rows matching the needed columns and conditions.

At first you might wonder, "If it reads all the data from PostgreSQL anyway, what's the point?" But the real burden isn't data transfer — it's the aggregation itself. The point of this pattern is that the sorting, hash aggregation, and joins that churn through shared_buffers are moved outside the production DB.

pg_duckdb — Integration in the Opposite Direction

MotherDuck's pg_duckdb, released in 2025, works in the opposite direction. It embeds the DuckDB engine inside the PostgreSQL process, so the DuckDB engine operates within ordinary PostgreSQL SQL.

-- After installing pg_duckdb in PostgreSQL
CREATE EXTENSION pg_duckdb;
 
-- Query S3 Parquet files with PostgreSQL SQL (handled by pg_duckdb)
SELECT * FROM read_parquet('s3://my-bucket/events/*.parquet')
WHERE event_date >= '2026-01-01';
 
-- Join a PostgreSQL table with an S3 Parquet file
SELECT c.customer_id, c.name, COUNT(e.event_id) AS event_count
FROM customers c
JOIN read_parquet('s3://my-bucket/events/*.parquet') e
    ON c.customer_id = e.customer_id
GROUP BY c.customer_id, c.name;

The advantage of this pattern is that application code changes are minimal. You keep the existing PostgreSQL connection as-is and simply add SQL.

Three Scenarios

Which approach to choose depends on your data freshness requirements and your willingness to accept operational complexity.

CDC (Change Data Capture)-based near-real-time pipelines are also a valid option, but they require a separate streaming stack and are left outside the scope of this article. They will be mentioned briefly at the end if needed.

Scenario 1 — Nightly Batch Parquet Export

This is the simplest pattern with zero load on the production DB. Each night during low-traffic hours, PostgreSQL tables are exported to Parquet, and all analytics queries are handled by DuckDB reading the files directly.

One caveat: PostgreSQL's built-in COPY does not support the Parquet format. To export directly to Parquet, you need either pg_duckdb installed, or a separate tool (e.g., using the postgres extension in the duckdb CLI to SELECT and then COPY ... TO ... (FORMAT parquet)).

-- Parquet export with pg_duckdb (requires the pg_duckdb extension to be installed)
COPY (SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '90 days')
TO '/data/exports/orders_recent.parquet' (FORMAT parquet);

Without pg_duckdb, a two-step approach works fine: use vanilla PostgreSQL's COPY ... TO ... (FORMAT csv) to extract CSV/TSV, then convert on the DuckDB side with COPY (SELECT * FROM read_csv_auto('...')) TO '...' (FORMAT parquet);.

# Query the exported file with DuckDB in Python
import duckdb
 
con = duckdb.connect()
result = con.execute("""
    SELECT
        date_trunc('month', created_at) AS month,
        product_category,
        COUNT(*) AS order_count,
        SUM(amount) AS revenue
    FROM read_parquet('/data/exports/orders_recent.parquet')
    GROUP BY 1, 2
    ORDER BY 1, revenue DESC
""").fetchdf()

For reports and dashboards where T+1 data is sufficient, this is the best option. Implementation complexity is low, and PostgreSQL is unaffected except during the batch export window.

When to avoid it. Workloads like real-time dashboards, fraud detection, or operational alerts that require "data from this morning." If batch latency conflicts with your SLA, it becomes a reliability issue.

Scenario 2 — Real-Time Aggregation with the postgres Extension

When T+1 isn't enough but it's too early to build a CDC pipeline, the ATTACH approach is a realistic choice.

import duckdb
 
# Connect to DuckDB and load the postgres extension (call each statement separately)
con = duckdb.connect()
con.execute("INSTALL postgres")
con.execute("LOAD postgres")
 
# Connect to PostgreSQL
con.execute("""
    ATTACH 'host=pg-analytics-replica.internal port=5432
             dbname=mydb user=analyst password=secret'
    AS pg (TYPE postgres, READ_ONLY)
""")
 
# Real-time aggregation query — aggregation is handled by DuckDB
df = con.execute("""
    SELECT
        date_trunc('week', o.created_at)   AS week,
        p.category,
        COUNT(DISTINCT o.user_id)          AS unique_buyers,
        SUM(o.amount)                      AS revenue
    FROM pg.public.orders o
    JOIN pg.public.products p ON o.product_id = p.id
    WHERE o.created_at >= NOW() - INTERVAL '3 months'
    GROUP BY 1, 2
    ORDER BY 1 DESC, revenue DESC
""").fetchdf()

With large tables, the network can become a bottleneck in this approach. In particular, transferring hundreds of millions of rows without a WHERE condition can actually burden the production DB. Writing WHERE clauses explicitly so that predicate pushdown applies is important.

When to avoid it. When multiple analysts are simultaneously running ad hoc exploratory queries with weak filter conditions against the primary. In that case, moving to Scenario 3 is better.

Scenario 3 — Read Replica + pg_duckdb

If you want to fully protect the production primary, installing pg_duckdb on a read replica is currently the safest approach. MotherDuck's pg_duckdb v1.0 release announcement presents a case where certain TPC-DS benchmark queries dropped from 90 seconds with plain PostgreSQL to 137 milliseconds with pg_duckdb (roughly a 650x difference by simple arithmetic). The range varies significantly by workload, so you should re-measure with your own data.

-- Install pg_duckdb on the read replica PostgreSQL
CREATE EXTENSION pg_duckdb;
 
-- Afterward, write ordinary PostgreSQL SQL and the DuckDB engine handles it
-- Complex aggregation queries work with the same PostgreSQL syntax
SELECT
    region,
    product_line,
    SUM(quantity * unit_price) AS total_revenue,
    AVG(quantity * unit_price) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY region, product_line
HAVING SUM(quantity * unit_price) > 100000
ORDER BY total_revenue DESC;

Nothing changes on the primary, and the replica synchronizes via PostgreSQL streaming replication. The analytics team only needs to change their connection string to point to the replica.

When to avoid it. Workloads that cannot tolerate replication lag (e.g., showing a user their dashboard immediately after they commit a transaction). Also, if your managed PostgreSQL service doesn't yet support the pg_duckdb extension, you'll need a self-hosted replica.

Trade-offs — An Honest Assessment

DuckDB supports its own ACID transactions, so saying "it has no MVCC at all" is inaccurate. More precisely, it lacks a multi-writer concurrency model, which is essentially the same point as the first row in the table above.

Common Mistakes

Using the postgres extension without a WHERE clause. Transferring a table with tens of millions of rows without a filter means that even though DuckDB aggregates quickly, the production DB is busy pushing data out over the network. Queries must be written so that predicate pushdown applies.

Giving analytics users direct access to DuckDB. DuckDB lacks fine-grained access control like PostgreSQL's. In environments that require GRANT or RLS, access control must be implemented separately at the application layer.

Misconfiguring the batch export schedule. If the analytics team expects "today's data" but is looking at yesterday's batch file, a trust problem arises. File-based approaches work best when the data freshness SLA is explicitly agreed upon within the team before adoption.

A Recommended Starting Point for Practitioners

The decision framework was covered earlier, so here the focus is on which order to proceed to minimize pain in practice.

Start with Scenario 1 (nightly Parquet batch). No new extension needs to be installed on the production DB, and the team can run internal rehearsals with a single DuckDB file. Most dashboards and weekly reports are already addressed at this stage.

When freshness demands increase, migrate to Scenario 3 (read replica + pg_duckdb). Because the primary is untouched and the SQL dialect is preserved, the team's learning cost is lowest. For teams that can operate a single self-hosted replica, this is likely to be the final destination.

Keep Scenario 2 (direct postgres extension connection) for development/staging environments or ad hoc analytics tools. Connecting directly to the production primary is something to consider only after understanding the traffic and query patterns.

When sub-second freshness becomes a requirement, that's the point to discuss CDC (Debezium, pg_duckpipe, etc.). The benefit is greatest when you have the staff and monitoring infrastructure to operate a Kafka stack; forcing it in prematurely loses the "lightness" that is the sidecar's original advantage.

In one sentence: start with the simplest combination your team can maintain, and move up one step at a time only when freshness requirements increase.

References

Sources Cited in the Article

• pg_duckdb v1.0 release and TPC-DS benchmark figures — Announcing Pg_duckdb Version 1.0 (MotherDuck)
• Introduction to the postgres extension (formerly postgres_scanner) — Querying Postgres Tables Directly from DuckDB (DuckDB Blog)

Official Documentation

• PostgreSQL Extension – DuckDB Official Docs
• PostgreSQL Import Guide – DuckDB
• GitHub – duckdb/pg_duckdb

Further Reading (Community Secondary Sources)

• PostgreSQL + DuckDB Integration: 3 Methods to Boost Analytical Performance – MotherDuck
• Postgres Powered by DuckDB: The Modern Data Stack in a Box – Crunchy Data
• Postgres to MotherDuck: Stream Real-Time Analytics with CDC – Estuary
• pg_duckpipe: Real-time CDC for streaming Postgres Table into Columnar Ducklake – DEV Community
• Turbocharging Postgres Analytics with DuckDB – Medium