Postgres + DuckDB for AI Analytics: pg_duckdb Speeds Up OLAP 100x (2026)

TL;DR — pg_duckdb 1.0+ ships DuckDB's columnar engine inside Postgres. Set duckdb.force_execution=true and your existing analytic SQL gets vectorized — TPC-DS queries see up to 1500x speedups, AI dashboards land under 1 sec.

What you'll build

A pg_duckdb-enabled Postgres where transcripts and metrics live in row-store tables, while monthly aggregate dashboards execute through DuckDB's vectorized engine — same SQL, 100x faster.

Schema

CREATE EXTENSION pg_duckdb;

CREATE TABLE call_events (
 ts TIMESTAMPTZ NOT NULL,
 tenant_id UUID,
 agent_id UUID,
 duration_sec INT,
 cost_cents INT,
 topic TEXT
);
CREATE INDEX ON call_events (ts) WHERE ts > now() - interval '30 days';

-- Iceberg / Parquet on S3, queryable via DuckDB

Architecture

flowchart LR
 APP[App] --> PG[(Postgres OLTP)]
 PG --> ROW[Row-store tables]
 ANALYST[Analyst dashboard] --> EXEC{duckdb.force_execution?}
 EXEC -->|Yes| DDB[DuckDB vectorized]
 EXEC -->|No| PG_ENG[Postgres planner]
 DDB --> ROW
 DDB --> ICE[Iceberg / Parquet S3]

Step 1 — Install pg_duckdb

sudo apt install postgresql-17-pg-duckdb
psql -c "CREATE EXTENSION pg_duckdb;"

Step 2 — Toggle execution

SET duckdb.force_execution = true;

EXPLAIN ANALYZE
SELECT date_trunc('day', ts), tenant_id,
 count(*), avg(duration_sec), sum(cost_cents)
FROM call_events
WHERE ts >= now() - interval '90 days'
GROUP BY 1, 2;

EXPLAIN now shows DuckDB Execution instead of Aggregate.

Step 3 — Query S3 Parquet directly

SELECT count(*) FROM read_parquet(
 's3://callsphere-lake/calls/year=2026/month=04/*.parquet'
);

No copy, no foreign data wrapper config — just point at the bucket.

Step 4 — Mix Postgres + Parquet

SELECT pg.tenant_id, pg.body, ce.duration_sec
FROM conversations pg
JOIN read_parquet('s3://lake/events/*.parquet') ce
 ON pg.id = ce.conversation_id
WHERE pg.created_at >= now() - interval '7 days';

DuckDB plans the join across both data sources.

Step 5 — MotherDuck (managed) integration

INSTALL motherduck;
CALL duckdb.attach_motherduck('my_token');
SELECT count(*) FROM motherduck.warehouse.calls;

Use MotherDuck for cross-org analytics with shared compute.

Step 6 — Schedule DuckDB-backed materialized views

CREATE MATERIALIZED VIEW daily_call_summary AS
SELECT date_trunc('day', ts) AS d, tenant_id,
 count(*), avg(duration_sec)
FROM call_events
WHERE ts >= now() - interval '180 days'
GROUP BY 1, 2;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_call_summary;

Set duckdb.force_execution = true in the refresh job — 90-second refresh becomes 5 seconds.

Pitfalls

• duckdb.force_execution on transactional queries — slows them. Use it per session, not globally.
• Mutable rows + DuckDB scan — DuckDB sees a snapshot; recent writes may not appear in the same transaction.
• S3 credentials — set SET s3_access_key_id per session; don't put creds in postgresql.conf.
• Incompatible SQL — most works, but uncommon Postgres operators may need rewrites.

CallSphere production note

Explore a live demo and compare current plans to find the right fit for your business.

FAQ

Q: pg_duckdb vs pg_analytics (ParadeDB)? Both wrap DuckDB. pg_duckdb is closer to upstream DuckDB; pg_analytics targets data-lake-only.

Q: Can pg_duckdb write? Yes — COPY... TO 's3://...' (FORMAT parquet) works.

Q: Memory limits? DuckDB obeys memory_limit setting; spillover to disk on big aggregates.

Q: Does it bypass RLS? DuckDB executes as the calling user — RLS still applies on Postgres tables.

Q: When NOT to use pg_duckdb? Pure OLTP point queries. Postgres planner is already optimal there.

Sources

• pg_duckdb GitHub
• MotherDuck — pg_duckdb 1.0 release
• The New Stack — Postgres analytics with DuckDB
• MotherDuck — Postgres + DuckDB integration methods

Postgres + DuckDB for AI Analytics: pg_duckdb Speeds Up OLAP 100x (2026): production view

Postgres + DuckDB for AI Analytics: pg_duckdb Speeds Up OLAP 100x (2026) usually starts as an architecture diagram, then collides with reality the first week of pilot. You discover that vector store choice (ChromaDB vs. Postgres pgvector vs. managed) is not really a vector store choice — it's a latency, freshness, and ops choice. Picking wrong forces a re-platform six months in, exactly when you have customers depending on it.

Shipping the agent to production

Production AI agents live or die on three loops: evals, retries, and handoff state. CallSphere runs 37 agents across 6 verticals, each with its own eval suite — synthetic call transcripts replayed nightly with assertion checks on extracted entities (date, time, party size, insurance, address). Without that loop, prompt regressions ship silently and you only find out when bookings drop.

Structured tools beat free-form text every time. Our 90+ function tools all enforce JSON schemas validated server-side; if the model hallucinates an integer where a string is required, we retry with a corrective system message before falling back to a deterministic path. For long-running flows, we treat agent handoffs as a state machine — booking → confirmation → SMS — so context survives turn boundaries.

The Realtime API vs. async decision usually comes down to "is the user holding the phone right now?" If yes, Realtime; if no (callback queue, after-hours voicemail), async wins on cost-per-conversation, which we track per agent in 115+ database tables spanning all 6 verticals.

FAQ

Is this realistic for a small business, or is it enterprise-only? The healthcare stack is a concrete example: FastAPI + OpenAI Realtime API + NestJS + Prisma + Postgres healthcare_voice schema + Twilio voice + AWS SES + JWT auth, all HIPAA aligned. For a topic like "Postgres + DuckDB for AI Analytics: pg_duckdb Speeds Up OLAP 100x (2026)", that means you're not starting from scratch — you're configuring an agent template that's already been hardened across thousands of conversations.

Which integrations have to be in place before launch? Day one is integration mapping (scheduler, CRM, messaging) and prompt tuning against your top 20 real call transcripts. Day two through five is shadow-mode running, where the agent transcribes and recommends but a human still answers, so you can compare side-by-side. Go-live is the moment your eval pass-rate clears your internal bar.

How do we measure whether it's actually working? The honest answer: it scales until your tool catalog gets stale. The agent is only as good as the integrations it can actually call, so the operational discipline is keeping schemas, webhooks, and fallback paths green. The platform handles the rest — observability, retries, multi-region routing — without your team owning the GPU layer.

Talk to us

Explore a live demo and compare current plans to find the right fit for your business.