Proposal: opt-in automatic DuckDB execution for analytical PostgreSQL table queries

[jsuchane-cz]

Hi pg_duckdb maintainers,

I would like to discuss a possible opt-in auto execution mode for analytical queries over regular PostgreSQL tables. I am not opening a PR yet, because I would first like to check whether this direction fits pg_duckdb’s design.

If the idea is acceptable, I would be happy to start with a small MVP PR as described below.

Proposal: opt-in automatic DuckDB execution for analytical queries on PostgreSQL tables

Summary

An opt-in negative filter for uncontrolled analytical SQL workloads. It avoids routing obviously non-analytical queries to DuckDB, is conservative by default, does not double-plan, and does not change any existing behavior. The feature would be disabled by default and would not affect existing users unless they explicitly enable it.

Problem

Today, for queries that touch only regular PostgreSQL base tables, pg_duckdb effectively offers two execution-control modes:

• duckdb.force_execution = false (default): analytical queries never use DuckDB execution.
• duckdb.force_execution = true (e.g. via ALTER ROLE ... SET or SET LOCAL): every allowed SELECT with a FROM clause is attempted in DuckDB.

The second option already falls back to PostgreSQL when DuckDB planning fails, so the problem is not robustness. The problem is that many queries plan successfully in DuckDB but should never run there. An index-selective query like:

SELECT * FROM orders WHERE order_id = 123;

plans fine in DuckDB, so no fallback is triggered — yet it may avoid the efficient PostgreSQL index access path the normal planner would have chosen, and become orders of magnitude slower. force_execution per user/connection therefore penalizes every selective query issued by that user.

This matters most where SQL is not under the developer's control and cannot be routed per statement:

• BI tools (Tableau, Metabase, Power BI) generating mixed analytical + selective SQL on one connection
• ad-hoc SQL / shared analytics endpoints
• reporting read replicas serving mixed workloads
• managed PostgreSQL platforms that cannot ask users to SET anything

What is missing is a negative filter: "try DuckDB, but only for queries that look analytical and touch enough data."

Proposal

A new opt-in GUC:

SET duckdb.auto_execution = on;                       -- default: off
SET duckdb.auto_min_total_relation_rows = 1000000;    -- default TBD via benchmarks

When enabled, and only when NeedsDuckdbExecution() and duckdb.force_execution did not already decide, the planner hook attempts DuckDB execution for a query iff all of the following hold:

1. It is a SELECT with a FROM clause and passes the existing IsAllowedStatement() checks (no modifying CTEs, no catalog tables, not inside functions unless allowed).
2. It contains an aggregate, a window function, or a GROUP BY clause — checked recursively via query_tree_walker, covering common BI-generated wrappers such as outer subqueries and view-expanded RTE_SUBQUERY entries. Set-operation handling can be included where it fits cleanly into the analysed query tree traversal. GROUP BY without aggregates is intentionally included because BI tools generate it as a deduplication pattern; it is effectively similar to DISTINCT, which could be added later once the initial behavior is validated.
3. The estimated total size of the referenced base relations, using pg_class.reltuples as a cheap negative filter — not as a selectivity or cost estimate — exceeds the configured threshold. Relations with unknown or clearly unavailable statistics (such as reltuples = -1) are treated conservatively as below the threshold. The exact relation collection strategy is open for review; the intent is to keep this check cheap and conservative rather than complete.
4. As an additional conservative rule for the first version, plain COUNT(*) queries with a WHERE clause stay in PostgreSQL, because this pattern is often index-selective (see open questions). Here "plain COUNT(*) with WHERE" means a single count aggregate without GROUP BY, HAVING, DISTINCT, or window functions — so SELECT customer_id, count(*) ... WHERE ... GROUP BY customer_id remains a normal candidate. Aggregate FILTER clauses are a semantically similar selective pattern and could be excluded as well, depending on maintainer preference.

If DuckDB planning fails, the query falls back to PostgreSQL exactly as force_execution does today — but in the auto path only, the failure is logged at DEBUG1 instead of WARNING. With force_execution the user explicitly requested DuckDB, so a client-visible warning is useful there; in auto mode it would be noise for BI workloads.

The existing exclusions in ShouldTryToUseDuckdbExecution() (materialized view refresh, background worker) apply to the auto path as well.

Resulting decision order — current behavior is unchanged unless the new GUC is enabled:

1. NeedsDuckdbExecution() → DuckDB (as today)
2. duckdb.force_execution = true → try DuckDB, fall back (as today)
3. duckdb.auto_execution = on and query is an analytical candidate → try DuckDB, fall back (new)
4. otherwise → PostgreSQL (as today)

Expected decisions

Query pattern	Expected auto decision
SELECT * FROM orders WHERE id = ?	PostgreSQL (no aggregate)
SELECT sum(amount) FROM large_orders GROUP BY customer_id	DuckDB
SELECT * FROM (SELECT customer_id, sum(amount) FROM large_orders GROUP BY 1) t	DuckDB (recursive detection)
SELECT customer_id FROM large_orders GROUP BY customer_id	DuckDB, if threshold is exceeded (deduplication pattern)
SELECT count(*) FROM small_lookup	PostgreSQL (below threshold)
SELECT row_number() OVER (...) FROM large_table	DuckDB
SELECT count(*) FROM large_table WHERE id = ?	PostgreSQL initially / configurable later
SELECT customer_id, count(*) FROM large_table WHERE ... GROUP BY 1	DuckDB (not a "plain" count)
INSERT / UPDATE / DELETE ...	PostgreSQL (not eligible)

Non-goals

To keep this reviewable and low-risk, the following are explicitly out of scope:

• no cost-based comparison of PostgreSQL vs DuckDB plans
• no double planning, no EXPLAIN-based inspection of the PostgreSQL plan
• no per-CTE or hybrid routing within one query
• no changes to default behavior (duckdb.auto_execution = off by default)
• no runtime learning / adaptive routing

Safety and eligibility

The auto path should not bypass any eligibility, permission, or RLS-related checks that apply to the existing force_execution path. It reuses the existing statement eligibility checks and only narrows the eligible set by requiring analytical shape and sufficient data volume.

Known limitations (acknowledged upfront)

• The threshold is only intended as a cheap negative filter for obviously small queries, not as a selectivity or cost estimate. A large table + aggregate + selective indexed WHERE could still be routed to DuckDB. This is the same trade-off users accept today with force_execution, but scoped to far fewer queries; the COUNT(*)-with-WHERE rule above removes the most common class of these.
• Joins of a large fact table with small dimensions pass the threshold by relation size even when the actual selectivity is low — accepted for v1 as a consequence of the negative-filter design.
• Partitioned-table handling in the threshold check should be designed conservatively to avoid expensive catalog traversal on highly partitioned schemas; the exact approach is open for discussion.
• Plan caching for prepared statements fixes the routing decision at plan time; I believe this is acceptable and matches how force_execution interacts with caching today.
• Semantics differences between engines (collations, numeric edge cases) are inherent to DuckDB execution of PostgreSQL tables and are the same as with force_execution; auto mode does not widen the per-query semantics gap. Hence: off by default, documented clearly.

Phasing

Following the contribution guidelines (small PRs, discuss first):

1. MVP PR: duckdb.auto_execution + duckdb.auto_min_total_relation_rows GUCs, recursive analytical-query classifier, reltuples threshold, fallback at DEBUG1 in the auto path, regression tests. Small enough to review, but complete enough to be a usable routing policy.
2. Observability PR: expose the routing decision and reason — possibly through EXPLAIN output, DEBUG logging, or per-session counters, depending on maintainer preference.
3. Later, if wanted: refined partitioned-table estimation, schema/table allowlists, making the COUNT(*)-with-WHERE rule configurable, DISTINCT as an analytical signal.

Questions

1. Is an opt-in auto mode acceptable in principle for pg_duckdb, or do you consider per-user force_execution + fallback the intended answer for mixed workloads?
2. For the MVP, would you prefer a separate boolean GUC such as duckdb.auto_execution, or should I align it with any broader execution-mode naming you already have in mind? The existing force_execution behavior stays unchanged either way.
3. Any objection to using DEBUG1 rather than WARNING for DuckDB planning failures in the auto path only?
4. Do you agree with keeping plain COUNT(*) with a WHERE clause in PostgreSQL in the first version, or would you prefer it to be routed by the threshold like any other aggregate?

If the direction is acceptable, I will start with the MVP PR as scoped above, with initial benchmark results to justify the proposed default threshold.