DuckDB Queries Over Supabase Postgres Data

Three ways to run DuckDB on Supabase data, and when each breaks.

· 9 min read · Updated
Cover illustration for “DuckDB Queries Over Supabase Postgres Data”
Supabase Analytics · October 16, 2026 · 9 min read · 2,096 words

DuckDB gives Supabase teams columnar, vectorized analytical performance on the same Postgres database they already operate, without migrating data, building ETL pipelines, or adopting a separate warehouse. The integration has three distinct patterns, each with trade-offs around network transfer, concurrency, and operational isolation. Which one you choose depends on data size, query frequency, and whether AI agents sit in the workflow.

Why Postgres slows down on analytical queries and what that forces teams to decide

Postgres is the right operational database for Supabase teams, but its row-oriented execution model becomes a liability the moment queries shift from point lookups to full-table scans and aggregations. A query that needs only four columns out of twenty still reads every column of every qualifying row in Postgres. DuckDB on the same data reads only the four referenced columns, in compressed vectorized batches. The difference is structural, not a matter of tuning: the row-oriented engine is built to retrieve complete records quickly, and that design choice carries a cost when the analytical question concerns only a subset of columns across millions of rows.

MVCC overhead compounds the problem. Postgres checks anyway, because that is what the visibility machinery does, and the cost scales with table size and update volume. This architectural mismatch is the exact problem Dreambase addresses: it extends Supabase projects into a governed analytical layer without requiring a separate warehouse or ETL pipeline, letting teams keep their operational Postgres database intact.

The consequence is a forced architectural choice that teams usually hit at hundreds of millions of rows: stay in Postgres and optimize aggressively through indexes, materialized views, and partitioning, or introduce a second system. Materialized views reduce scan cost by pre-computing aggregates, yet they must be refreshed and maintained. At some point you have to decide if you want to keep adding Postgres-specific machinery or bring in an engine whose storage model matches the analytical workload.

What DuckDB is and why its architecture produces faster analytical results on the same data

DuckDB is a columnar, vectorized, embeddable query engine, the architectural opposite of Postgres in every dimension that matters for analytics. Columnar storage means that all values of a single column are stored contiguously. When DuckDB scans a column segment, it works in vectorized batches, so the same operation runs across thousands of values at once, not row by row.

The engine is in-process. It links as a library inside Python, Node.js, or a Postgres extension, so you can embed DuckDB wherever analysis happens without provisioning infrastructure. It reads Parquet, CSV, JSON, and S3 objects natively. A query as simple as SELECT * FROM 'data.parquet' requires no import step.parquet'` requires no import step. For large scans, it uses all available CPU cores automatically, with no configuration. That combination of columnar storage, vectorized execution, and in-process deployment is why DuckDB can produce analytical results on the same data that would take Postgres substantially longer to compute.

The trade-off the architecture creates is equally important. Postgres is built for thousands of users doing many small things. This distinction determines every usage pattern that follows. Postgres does those things exceptionally well. The art is in putting each engine where its architecture fits.

Three ways to connect DuckDB to a Supabase Postgres database, and what each one actually does

Diagram: Three DuckDB–Supabase Integration Patterns at a Glance. Visualizes: Show three integration patterns as a ranked or stepped comparison along two axes: data movement (high to none) and concurrency ceiling (low to moderate).

There are three distinct integration paths, and choosing the wrong one for a given constraint produces the slowdown teams are trying to escape. The first is the DuckDB Postgres Extension, known as postgres_scanner. In this pattern DuckDB runs externally and reaches into Postgres to pull data for analysis. All analytical computation happens on the DuckDB side. The Postgres server is not burdened with the scan. This is the lowest-commitment entry point for teams experimenting with DuckDB over their Supabase data.

The second path is pg_duckdb, which embeds DuckDB inside the Postgres server process. Installing the pg_duckdb extension on the Postgres server allows DuckDB instances to run as part of that process, so DuckDB queries can be issued directly from a Postgres connection using standard SQL. This eliminates the network transfer entirely, because the engine runs beside the data rather than pulling it across a connection. Through the DuckDB engine, it can read Postgres tables and also pull external data such as Parquet on S3 or MotherDuck into the same query. Resource management is the critical operational concern in this pattern: DuckDB shares memory and cores with the Postgres process, and on a production primary that sharing creates contention between transactional and analytical workloads. If you run pg_duckdb on a dedicated read replica in production, analytical queries hit the replica while the primary stays clean. This is a collaborative project between DuckDB Labs, MotherDuck, Hydra, Neon, and Microsoft.

The third path is the Supabase Wrappers DuckDB integration, which runs in the opposite direction. The Wrappers extension and server are configured via standard SQL, through CREATE SERVER and CREATE FOREIGN DATA WRAPPER statements. This path is most useful when the analytical source is object storage, such as Parquet files on S3 or Supabase Storage, and the team wants to query it from within an existing Postgres and Supabase workflow without spinning up a separate DuckDB environment. It is not a way of running columnar analytics against Postgres row data. It is a bridge from Postgres to DuckDB-readable external storage.

Where each integration pattern performs well and where it hits a real ceiling

The integration that performs best is the one that minimizes data movement between the Postgres row store and the DuckDB columnar engine, and each pattern fails in a predictable way when that principle is violated. For the external postgres_scanner path, network transfer is the performance ceiling. The pattern works well when tables are moderately sized, queries are filter-heavy with WHERE clauses that reduce data volume before transfer, and the workload is exploratory rather than continuous. It breaks down when tables are very large, filters are not pushdown-eligible, or queries run concurrently.

Concurrency is the ceiling for any single-instance DuckDB deployment, including pg_duckdb. Multiple concurrent dashboard queries scanning large tables will contend for the same resources. If you hit the concurrency ceiling, you have outgrown the embedded-engine pattern and need a governed data layer that serves pre-modeled results, not more scanning. There is no replication, no connection pooling suitable for multi-tenant applications, and no concurrent write handling. Every pattern described keeps Postgres as the write path and the operational authority. DuckDB is the read-side accelerator.

The Parquet export plus object storage pattern avoids many of these ceilings. Exporting Postgres data to Parquet files on S3 or Supabase Storage and then querying with DuckDB natively removes the network transfer and MVCC overhead entirely. DuckDB reads Parquet from object storage at full columnar speed. The trade-off is staleness: the export is a snapshot, not a live view. That is acceptable for daily or hourly reporting and wrong for real-time operational queries. The same mechanism is what makes the DuckLake pattern possible, using Postgres or another SQL database as the catalog for DuckLake's Parquet tables on object storage, queryable primarily by DuckDB at columnar speed.

Why letting AI agents query Postgres directly creates risks that the DuckDB integration patterns can solve

The same isolation properties that make a read replica or a Parquet export safer for heavy analytical queries also make them the correct architecture for AI agent data access, and the risks of skipping that isolation are categorically different when an agent is the client. AI agents querying a production Postgres database directly create two distinct risks. Correctness risk arises when a hallucinated query runs against live data and produces wrong results that go into a report or a decision. A large, unoptimized, natural-language-generated query can cause row-level locking or performance degradation on the primary node. When a query goes wrong, a human analyst notices most of the time. An agent will not, because it has no independent sense that the numbers it produced are suspicious.

The recommended architecture from a security standpoint is that agents should have read-only access exclusively to a replica or a governed dataset that exists in a separate threat boundary from production. No action the agent takes, including a hallucinated destructive statement, can reach the production database. Obscurity is not security, and an agent that can submit SQL statements will eventually submit one that no human would have written.

DuckDB's Parquet-on-object-storage pattern fits this threat model well. The MCP protocol is increasingly the delivery mechanism for this pattern. For teams looking beyond ad-hoc query patterns, the operational question shifts from integration mechanics to data governance: which of these paths can safely expose analytical data to AI agents without risk? Dreambase takes a different approach, pre-modeling data into governed Parquet datasets queryable through DuckDB, which means agents get fast, accurate analytical context without direct access to production Postgres at all.

What a governed data layer built on Parquet and DuckDB looks like for a Supabase team without a data engineering function

A Supabase team that pre-models its key metrics into governed Parquet datasets and exposes them via DuckDB gets the analytical speed of a columnar engine, the safety of production isolation, and a single definition of every metric, and it needs no data warehouse or data engineering team. The metric drift problem is what a governed layer solves. A semantic layer fixes this structurally: business metrics are declared once in version-controlled definitions, and every downstream consumer, whether a dashboard, an investor report, or an AI agent, reads the same pre-calculated result. For early-stage B2B SaaS, the canonical KPI set is small: MRR, CAC, LTV, monthly churn, and gross margin. The discipline is in locking those five definitions down before you build anything else.

What the governed Parquet plus DuckDB stack provides that ad-hoc queries cannot is speed, efficiency, and consistency in one mechanism. If you expose the governed layer through a single MCP server, human tools and AI agents get the same numbers through the same interface. The agent and the dashboard read identical results.

Dreambase is built specifically for teams running on Supabase and Postgres. It acts as a shared data layer that pre-calculates metrics into governed Parquet datasets queryable via DuckDB, and exposes them through a single MCP server. It extends the Supabase project into full analytics capability without ETL, schema changes, or a separate warehouse. Agents never touch the production database: all agent-facing data access goes through Dreambase's governed datasets, keeping the threat boundary the architecture requires. Metrics are pre-modeled and pushed to where teams work, into reports, dashboards, and Slack, rather than pulled on demand by whoever remembers to run a query.

How to decide which pattern fits the team's current situation

The right integration pattern is determined by three variables: the size of the data, the frequency and concurrency of queries, and whether AI agents are in the workflow. The DuckDB Postgres Extension is the right choice when the dataset fits comfortably in memory or can be substantially reduced by pushdown-eligible filters, when queries are infrequent and exploratory, and when no infrastructure changes to the Supabase project are acceptable. The trade-off is that network transfer becomes the bottleneck on large tables, and the performance ceiling is lower than the other paths.

pg_duckdb on a dedicated read replica fits when queries are complex and frequent enough that network transfer becomes the bottleneck, when the team can provision a replica specifically for analytical workloads, and when the workload is primarily read-heavy aggregation on Postgres tables rather than queries against external Parquet files. The trade-off is concurrency contention on a single DuckDB instance. This pattern scales to moderate concurrency, not to many simultaneous dashboard users.

Parquet export to object storage plus DuckDB fits when some staleness in the data is acceptable, when the query workload benefits from DuckDB's native columnar format for maximum speed on large analytical scans, when AI agents need access to the data because this pattern enforces the production threat boundary by design, and when the team wants to build toward a governed metrics layer rather than ad-hoc query acceleration. When none of the above is enough, when concurrency, governance, metric consistency, or agent safety has become the constraint rather than raw query speed, the answer is a pre-modeled, governed data layer that serves pre-calculated results. This is the point at which the integration pattern gives way to the infrastructure pattern: metrics calculated once, exposed via MCP, consumed safely by both humans and agents. That is the destination the article's argument has been building toward, not a faster scanner but a layer that removes the need to re-solve these trade-offs for every new query or agent.

More in Supabase Analytics