PostgreSQL vs DuckDB

PostgreSQL is a row-store client/server database built for many concurrent transactions; DuckDB is an in-process columnar analytical engine — a library linked into your application, not a service you connect to. They are compared often because both speak SQL, but they answer different questions: "can a thousand users safely transact at once" versus "how fast can one process aggregate a billion rows".

Architecture comparison

The concurrency row is the practical divider: if the workload is "many writers connected from many machines", DuckDB is architecturally out of scope; if it is "one job reading columnar data as fast as the disk allows", a client/server round trip per query is overhead DuckDB simply does not have.

When to choose which

Choose DuckDB

• Interactive analysis over local or object-storage files (Parquet, CSV, JSON) without loading them anywhere.
• Data preparation and transformation steps in a pipeline or notebook.
• Edge and embedded analytics where shipping a server is not an option.

Choose PostgreSQL

• Multi-user transactional workloads with concurrent writes, constraints, and foreign keys.
• Anything that serves an API or an application around the clock.
• Workloads that need row-level security, logical replication, point-in-time recovery, or the extension ecosystem — see the extension ecosystem and the cloud service map for what that buys in practice.

The analytics boundary on the PostgreSQL side

PostgreSQL executes analytical queries correctly but row-at-a-time relative to a columnar engine; on large scans the gap is structural, not a tuning miss. Three ways to close it without leaving PostgreSQL data behind:

• Columnar extensions: pg_mooncake maintains a columnstore mirror of PostgreSQL tables in Iceberg and accelerates analytics with DuckDB execution inside PostgreSQL; Citus offers a columnar storage option alongside distribution.
• Export to DuckDB: keep PostgreSQL as the system of record and export snapshots to Parquet for analysis — DuckDB reads Parquet natively.
• Push analysis to the warehouse when the workload outgrows one node entirely.

Using both together

DuckDB reads PostgreSQL: postgres_scanner

DuckDB's official postgres extension attaches a live PostgreSQL database and runs queries against it, including pushing filters down:

INSTALL postgres;
ATTACH 'dbname=app user=analyst host=127.0.0.1' AS pg (TYPE postgres, READ_ONLY);

SELECT status, count(*), avg(total_cents)
FROM pg.orders
WHERE placed_at >= now() - interval '30 days'
GROUP BY status;

This is the standard pattern for "analyze production data without exporting it": DuckDB pulls the rows it needs, and the heavy aggregation happens in DuckDB's vectorized engine. Attach read-only and use a low-privilege PostgreSQL role so the analysis path cannot write back.

PostgreSQL reads files: FDW

In the other direction, PostgreSQL's foreign data wrapper mechanism can expose Parquet files as foreign tables (for example with parquet_s3_fdw). This suits cases where the files are inputs to relational processing and should be joinable with live tables under PostgreSQL permissions — not a replacement for DuckDB's scan speed.

CREATE EXTENSION parquet_s3_fdw;

CREATE SERVER parquet_files FOREIGN DATA WRAPPER parquet_s3_fdw;

CREATE FOREIGN TABLE lake_events (...)
SERVER parquet_files
OPTIONS (dirname 's3://analytics/events/', sorted 'event_time');

Server and table options depend on the FDW version; check its README for the exact syntax.

In AI data stacks

The two engines typically appear at different stages of the same pipeline:

• DuckDB for preparation: cleaning, joining, and aggregating raw exports and data-lake files into the documents and tables an AI application will actually serve. No server to run, and Parquet output drops straight into the next stage.
• PostgreSQL for serving: the online path — multi-tenant transactional state, hybrid retrieval with pgvector and full-text search for RAG, and agent state such as long-term memory, where concurrency, permissions, and auditability are the point.

A rule of thumb: data at rest being prepared belongs in DuckDB's reach; data being served to users and agents belongs in PostgreSQL.

AI prompt: pick the engine for a workload

Help me decide between PostgreSQL and DuckDB for this workload.

1. Workload: (describe data size, read/write ratio, query shapes)
2. Writers: (how many processes/users write concurrently, from where)
3. Serving: (is there an online API/agent reading this, or offline analysis only)
4. Please:
 - Recommend PostgreSQL, DuckDB, or both (with the split of responsibilities)
 - If both: say whether integration should be postgres_scanner, Parquet export, or an FDW
 - List the two failure modes most likely if I pick the wrong one
5. Constraints: (ops capacity, latency budget, compliance)

Related

• PostgreSQL lineage and compatible databases — a different comparison: systems that reuse PostgreSQL itself
• Cloud service map — managed PostgreSQL options when the choice lands on a server
• Extension ecosystem — pgvector, PostGIS, and the rest of what "choose PostgreSQL" includes
• RAG pipeline — the online retrieval path PostgreSQL serves