Inside pg_duckdb: From PostgreSQL Heap Rows to DuckDB Vectors

pg_duckdb embeds DuckDB‘s vectorised analytical engine inside a PostgreSQL backend. It can accelerate aggregation- and join-heavy queries without exporting PostgreSQL data, but every qualifying PostgreSQL tuple delivered to DuckDB still has to cross a row-to-vector conversion boundary. This article follows one GROUP BY query through that integration and shows where pg_duckdb wins, where it loses, and what makes the difference.

This is the technical follow-up to our Introduction to DuckDB for data professionals.

Reviewed against: pg_duckdb 1.1.1 (DuckDB 1.4.3), PostgreSQL 17/18, source tag v1.1.1. Internal behaviour may change in later releases.

The query we will follow:

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

One compact SQL statement. Underneath it: PostgreSQL parsing and rewriting, DuckDB re-parsing and optimisation, and two cooperating execution layers. DuckDB itself is embedded inside the PostgreSQL backend; parallel PostgreSQL table scans may also use PostgreSQL worker processes. For this query over a local PostgreSQL table, no external database server, network transfer or data export is required.

1. PostgreSQL parses, rewrites — and stays the authority

The query first goes through PostgreSQL’s parser, parse analysis and query rewriter. Names and types are resolved, views expanded, and the rewritten query tree retains the information required to enforce privileges and row-level security. PostgreSQL remains the authority for access control and row visibility. pg_duckdb 1.1.x explicitly supports PostgreSQL RLS and performs view permission checks, but this integration has changed in recent releases, so security-sensitive workloads should still be validated on the exact version deployed.

2. The hook decides the route

pg_duckdb installs a planner_hook. For plain PostgreSQL tables, DuckDB execution is only attempted when you opt in with SET duckdb.force_execution = true; if DuckDB planning fails there, pg_duckdb can fall back to the native planner. Queries that require DuckDB – read_parquet(), Iceberg, Delta, MotherDuck tables – have no native plan to fall back to, and execution-time failures (memory, temp space, type edge cases) remain possible in both modes. Fallback reduces adoption risk; it is not a universal guarantee.

3. The query is parsed twice — via deparse

Here is the detail almost nobody knows: pg_duckdb does not translate PostgreSQL plan nodes into DuckDB plan nodes. It takes the rewritten PostgreSQL Query tree, deparses it into a normalised SQL string, and asks DuckDB to parse, bind, and optimise that representation:

In the DuckDB 1.4.3 version embedded by pg_duckdb 1.1.1, DuckDB still uses its PostgreSQL-derived parser, which helps keep the two SQL dialects relatively close. It is also exactly where dialect and type-inference differences can surface.

One planning caveat: DuckDB does not receive PostgreSQL’s full statistics model – histograms, MCV lists, correlation – in the form the native planner uses, though basic cardinality information is available. For a single-table aggregate this is irrelevant. For a five-way join, validate the join order rather than assuming it.

4. Crossing the boundary: tuples become vectors

In the plan used for this example, DuckDB shows POSTGRES_SEQ_SCAN followed by HASH_GROUP_BY. The aggregate is a pipeline breaker: it consumes the input chunks before producing grouped output. The whole DuckDB plan is exposed to PostgreSQL as a single CustomScan (DuckDBScan) node — PostgreSQL’s documented custom-scan interface, which the PostgreSQL documentation itself still classes as experimental — so the PostgreSQL executor still runs the show. Comparing EXPLAIN with force_execution on and off is the quickest way to see whether DuckDB actually took your query: look for Custom Scan (DuckDBScan) at the top of the plan.

The scan is where physics happens. pg_duckdb does not read heap pages directly: it constructs a SELECT over the target relation carrying the projected columns and any pushed-down filters, then hands that to the PostgreSQL executor. Because the filters arrive as an ordinary WHERE clause, PostgreSQL’s planner remains free to satisfy the scan with an index path rather than a sequential one — routing a query through DuckDB is not the same as forcing a full table scan. Rows are returned under the backend’s active MVCC snapshot and normal tuple-visibility rules. Depending on the chosen scan plan, PostgreSQL may use parallel worker processes; DuckDB can then use multiple threads to consume and convert their output – two separate layers of parallelism. For each qualifying tuple handed to DuckDB, only the required columns (customer_id, amount – projection pushdown) are extracted, detoasted where necessary, converted into DuckDB values, and appended to a DataChunk, whose standard vector capacity is 2,048 rows in the DuckDB 1.4.3 build reviewed here.

This conversion is genuine per-row work, and it is the tax of the whole architecture. On simple scan-heavy queries, it can dominate the runtime entirely – which is why a trivial query can run slower under pg_duckdb than natively. Storage stays row-oriented; you are buying vectorised execution, not columnar storage.

5. Where the trade pays off

The chunks flow into DuckDB’s vectorised hash aggregation. Instead of PostgreSQL’s tuple-at-a-time interpretation, group keys are hashed and aggregate states are updated in tight loops over up to 2,048 values at once, reducing interpretation and branching overhead and creating execution patterns that make effective use of SIMD-capable hardware. DuckDB’s analytical operators can use multiple threads inside the backend process, and eligible intermediates can spill to configured temporary storage, subject to storage limits and available disk space.

6. The return journey – with a twist

In pg_duckdb 1.1.1 — and still on main at the time of writing — queries that reference PostgreSQL tables do not stream their DuckDB result directly into the PostgreSQL executor. DuckDB completes and materialises the result first; PostgreSQL then fetches it in chunks and converts the values back into PostgreSQL tuples. This increases time-to-first-row and can create significant memory pressure. Temporary-storage usage may also rise when eligible query intermediates spill. Strong cardinality reduction keeps that cost modest; high-cardinality results – a SELECT * over millions of rows being the extreme case – can make it dominant, with two-way conversion and almost nothing for vectorisation to accelerate in between.

Which brings us to the one principle worth remembering from this entire article:

The boundary-cost principle. Costs scale with the volume and conversion complexity of data crossing the engine boundaries – row count, projected width and data types. Benefits scale with the analytical work performed between those crossings.

Where pg_duckdb wins and loses

Query shape	Likely candidate	What decides it
Index point lookup	PostgreSQL	A selective index path usually avoids conversion and planning overhead
Large scan + strong aggregation	pg_duckdb	Work per input row; small result out
Large result sets (SELECT *)	PostgreSQL, often	Full-result materialisation and two-way conversion
Multi-way joins	Benchmark it	Join order without full PG statistics
Parquet / Iceberg / Delta	pg_duckdb	Core PostgreSQL has no built-in reader for these formats
High-precision numeric	Validate first	Wide values are rejected by default unless lossy conversion to DOUBLE is explicitly enabled (duckdb.convert_unsupported_numeric_to_double)

Future direction: smarter execution routing

force_execution is currently all-or-nothing per session. An index-selective point lookup can plan successfully in DuckDB, so no fallback occurs even when PostgreSQL’s native index path would be preferable.

We have therefore proposed an opt-in automatic execution mode that uses conservative query-shape and relation-size filters to exclude obviously non-analytical queries before attempting DuckDB execution. It would not be a full cost-based comparison, and some selective aggregate queries could still be misclassified. At the time of writing, this is a community proposal rather than a committed pg_duckdb roadmap item – see Discussion #1057. If it would help your workload – BI tools, mixed reporting replicas, shared analytics endpoints – an upvote on the discussion helps the maintainers gauge interest.

Before production

• Memory
  • duckdb.max_memory and duckdb.memory_limit are aliases for the same per-connection DuckDB limit, and that limit defaults to 4,096 MB — four gigabytes for every connection that touches DuckDB. Setting it to 0 is the more dangerous option, not the safer one: DuckDB then applies its own default of 80% of system RAM, again per connection. Fifty concurrently active DuckDB sessions therefore have an aggregate configured ceiling of roughly 200 GB at the default setting. This is not pre-allocated or reserved memory, and the setting should not be interpreted as an exact bound on total backend process memory. Treat the ~200 GB figure as a capacity-planning ceiling, and lower the per-connection limit before you raise the connection count.

• Parallelism
  • DuckDB threads and PostgreSQL parallel worker processes are governed by separate settings that do not constrain one another: duckdb.threads (default -1, meaning one thread per CPU core, per connection), duckdb.threads_for_postgres_scan (default 2), duckdb.max_workers_per_postgres_scan (default 2), and PostgreSQL’s own max_parallel_workers_per_gather. A single analytical query can therefore draw on both concurrency models at once. Plan capacity for them together, and note that the duckdb.threads default scales with connection count, not with the machine.

• Connection pooling
  • duckdb.force_execution is a session GUC. With PgBouncer transaction pooling, include it in track_extra_parameters so its value is synchronised across server connections.

• Version-sensitive limitations
  • Test mixed PostgreSQL/DuckDB writes, execution inside functions, RLS, views and coexistence with other planner-hook extensions on the exact release you deploy.

Key takeaways

• pg_duckdb routes queries per-statement via planner_hook and executes DuckDB plans inside a PostgreSQL CustomScan. For local PostgreSQL tables, no external query server or data export is required.
• The rewritten query tree is deparsed to SQL and re-parsed by DuckDB; in the reviewed DuckDB 1.4.3 build, the parser is PostgreSQL-derived. PostgreSQL remains the authority for access control and tuple visibility, while pg_duckdb must preserve that context across its integration boundary.
• Qualifying tuples are converted into columnar DataChunks containing up to 2,048 rows in the reviewed build – the tax; vectorised, multi-threaded aggregation between the boundaries – the payoff.
• Results referencing PostgreSQL tables are currently fully materialised before returning, which penalises high-cardinality outputs and delays time-to-first-row.
• Apply the boundary-cost principle, then benchmark: pg_duckdb can materially outperform native execution on suitable analytical query shapes, but the crossover point must be measured on the target workload.

Not sure whether pg_duckdb fits your workload? Baremon can compare native PostgreSQL and pg_duckdb execution on your representative queries, including concurrency, memory limits, type compatibility and failure scenarios. Get in touch!

Sources (pinned to tag v1.1.1)

• Routing, fallback and required DuckDB execution – src/pgduckdb_hooks.cpp
• Deparse and CustomScan plan creation – src/pgduckdb_planner.cpp
• PostgreSQL scan, pushdown, DataChunks and two-layer parallelism – src/scan/postgres_scan.cpp
• Result materialisation and chunked fetch – src/pgduckdb_node.cpp
• Settings (memory, threads, security)
• Release notes 1.0.0 / 1.1.0 / 1.1.1 (materialisation #877, RLS, PgBouncer track_extra_parameters, DuckDB 1.4.3)
• PostgreSQL 18 documentation: query rewriting and Custom Scan Providers
• DuckDB internals overview
• DuckDB execution format (Vector, DataChunk)