# pg_duckdb: running DuckDB's analytical engine inside PostgreSQL

> pg_duckdb is a PostgreSQL extension that routes analytical queries to DuckDB's vectorized engine while leaving your tables where they are. It is aimed at teams that want data lake reads and faster aggregates without a separate warehouse, and it is a poor fit if you cannot change a session setting.

**duckdb/pg_duckdb** — DuckDB-powered Postgres for high performance apps & analytics.

- Repository: https://github.com/duckdb/pg_duckdb
- Stars: 3,249 · Forks: 206
- Language: C++
- License: MIT
- Published: 2026-09-24 · Updated: 2026-09-24 · Language: en
- Canonical page: https://hysenlabs.com/projects/duckdb-pg-duckdb

## What pg_duckdb solves, and for whom

PostgreSQL's executor is built for transactional workloads. Aggregations over wide scans, especially on columnar files that live outside the database, are where it hurts. pg_duckdb attacks that gap from inside the server: the README describes it as the official PostgreSQL extension for DuckDB, integrating DuckDB's columnar-vectorized analytics engine into PostgreSQL. The stated audience is "high performance apps & analytics," and the project was built in collaboration with Hydra and MotherDuck.

The pitch has two halves. First, existing SQL does not have to change. According to the README, when you set duckdb.force_execution=true, pg_duckdb "will automatically use DuckDB's SQL engine to execute" your analytical queries. Second, data does not have to move: the README states that you do not need to export data to Parquet or any other format, because pg_duckdb works directly with existing PostgreSQL tables.

That second claim is the interesting one. The usual path to a faster analytical engine is a pipeline: dump tables to object storage, load them into something columnar, keep the two in sync. pg_duckdb skips the pipeline by reading PostgreSQL's own storage through DuckDB. If the pipeline is the part of your stack you resent maintaining, this is the feature to evaluate. If you already have a warehouse that works, the extension is solving a problem you do not have.

## How the extension sits between the planner and DuckDB

The repository layout tells you most of the architecture. There is a C++ source tree in src/ and include/, a SQL surface in sql/, and a third_party/ directory that is a git submodule, with .gitmodules present at the top level and the Dockerfile copying .git/modules/third_party/duckdb/HEAD. DuckDB is vendored as a submodule rather than linked as a system library, which is why the build is a full compile rather than a package install.

The control file pg_duckdb.control marks this as a standard PostgreSQL extension, so it is loaded the way extensions normally are. Execution is governed by a setting in the duckdb namespace: the README's example sets duckdb.force_execution=true before running a query, and describes that flag as the switch that makes pg_duckdb use DuckDB's engine. That is a session-level decision, not a global rewrite of the planner, and it means the same statement can run on either engine depending on how the session is configured.

External data arrives through DuckDB's own read functions. The README's example calls read_parquet('s3://your-bucket/reviews.parquet') and iterates over the returned row object with r['product_name'], treating the file as a table you can group and join. Credentials are handled in SQL through duckdb.create_simple_secret, which the README shows taking type, key_id, secret and region arguments for S3. There is a docs/secrets.md file for the full picture, and docs/functions.md for the read functions.

Modern table formats are handled by installing DuckDB extensions from inside SQL. The README shows duckdb.install_extension('iceberg') followed by iceberg_scan with a version argument for time travel, and duckdb.install_extension('delta') followed by delta_scan. MotherDuck is optional and enabled with CALL duckdb.enable_motherduck('<your_motherduck_token>'), after which the README says existing MotherDuck tables appear automatically and CREATE TABLE ... USING duckdb creates cloud tables.

## Installing pg_duckdb with Docker and running a first query

The fastest path in the README is the prebuilt image. This starts a PostgreSQL server with pg_duckdb already installed, using the password duckdb and the tag 18-v1.1.1:

```bash
docker run -d -e POSTGRES_PASSWORD=duckdb pgduckdb/pgduckdb:18-v1.1.1
```

The repository also ships a docker-compose.yml that maps port 5432 by default, sets POSTGRES_USER to postgres and POSTGRES_PASSWORD to duckdb, and mounts ./docker/postgresql.conf as the server configuration file. If you want MotherDuck from the same image, the README exports a token and passes it through:

```bash
export MOTHERDUCK_TOKEN=<your_token>
docker run -d -e POSTGRES_PASSWORD=duckdb -e MOTHERDUCK_TOKEN pgduckdb/pgduckdb:18-v1.1.1
```

Without Docker, the README's source path is a clone and a make install, with docs/compilation.md covering the details. The Dockerfile shows what that build expects: postgresql-server-dev-${POSTGRES_VERSION}, cmake, ninja-build, libc++-dev and libcurl4-openssl-dev, among others. Expect a long first compile because DuckDB is built from the submodule.

Once a server is up, the first real use is to flip the execution setting and run an aggregate over an ordinary table. The README uses a table called orders and this exact statement:

```sql
SET duckdb.force_execution = true;
SELECT
    order_date,
    COUNT(*) AS number_of_orders,
    SUM(amount) AS total_revenue
FROM
    orders
GROUP BY
    order_date
ORDER BY
    order_date;
```

What you should see is the same result set you would get from PostgreSQL, produced by DuckDB's engine instead. If the table does not exist, docs/gotchas_and_syntax.md has a section on creating one. The next step is external data: configure an S3 secret with duckdb.create_simple_secret, then query a Parquet file through read_parquet as the README demonstrates, and join it against a local table. Hydra is offered as a hosted way to try the same thing, installed with pip install hydra-cli and started by running hydra.

## Where pg_duckdb is the wrong tool

The execution model is the main constraint. DuckDB's engine is chosen by a session setting, and the README frames the feature as acceleration for analytical queries. Nothing in the documentation suggests it is meant for high-concurrency transactional traffic, and routing short point lookups or heavy write workloads through a columnar engine is not what it is for. If your workload is mostly small transactions, the extension adds a build dependency and a second engine without addressing your bottleneck.

The build is the second constraint. DuckDB is a submodule under third_party/, and the Dockerfile installs a long list of development packages including postgresql-server-dev-${POSTGRES_VERSION}. That means the extension is tied to the PostgreSQL major version you compile against, and the image tag in the README encodes both: 18-v1.1.1. Compiling from source is a real compile, not a package install, and docs/compilation.md is the place to check before promising anyone a deadline.

There is also a documentation gap worth naming plainly. The README does not state what happens to a query when duckdb.force_execution is left at its default, and it does not describe a rollback or fallback path for a query that DuckDB cannot execute. The docs point to docs/settings.md and docs/gotchas_and_syntax.md for the details, and those are the pages to read before you turn the setting on for an application's connection pool rather than for one analyst's session.

## pg_duckdb against plain PostgreSQL and against DuckDB on its own

The honest comparison is two-sided. Plain PostgreSQL keeps one engine, one planner and one set of semantics. You give up columnar scans and the ability to point a query at s3://your-bucket/reviews.parquet without a loader. pg_duckdb gives you both of those in the same process, and the README's join example, aggregating a local orders table and a remote Parquet file in one statement, is the concrete thing plain PostgreSQL cannot do without an extension or a pipeline.

DuckDB on its own is the other alternative, and the difference is where your data lives. A standalone DuckDB process reads files and its own database files; reaching PostgreSQL tables means going through a scanner, which is the direction the related searches point at with "DuckDB postgres scanner." pg_duckdb inverts that: the PostgreSQL server is the host, the tables are already there, and DuckDB is the execution engine inside it. If your source of truth is Postgres and your analytics are secondary, the extension removes a hop. If your source of truth is already Parquet in a lake, a standalone DuckDB process or a warehouse is simpler than running a PostgreSQL server to host an engine that mostly reads files.

MotherDuck is a third position and it is optional here. The README describes it as a cloud analytics platform you can use as a compute provider, enabled with CALL duckdb.enable_motherduck and a token. That is a different trade: you keep the PostgreSQL interface but move compute off your own machine.

## Licence and the cost of staying current

The repository is MIT licensed, with LICENSE and NOTICE both at the top level. MIT is permissive, so redistributing the extension inside a container image is not the kind of thing that normally requires legal review, but the vendored DuckDB submodule and any DuckDB extensions you install from SQL carry their own terms, and this article is not legal advice. If you plan to ship a product that bundles the extension, read the third_party/ directory and the licence of each DuckDB extension you call with duckdb.install_extension.

Upgrade cost is driven by the build. Because DuckDB is a submodule and the extension compiles against a specific PostgreSQL major version, an upgrade is a rebuild, not a package bump. The release history shows a steady cadence: v1.0.0 on 2025-09-04, v1.1.0 on 2025-12-11, and v1.1.1 on 2025-12-18. The last push to the repository was on 2026-07-17, so the project is not archived but the recent commit activity is older than the release tags above suggest. Teams that pin the Docker tag 18-v1.1.1 get a reproducible image and take on the job of moving that tag deliberately.

## Conclusion

Adopt pg_duckdb if you already run PostgreSQL and want analytical queries or data lake reads without exporting data to another system, and if you can set duckdb.force_execution per session. Do not adopt it if you need every query to stay on the PostgreSQL planner, if you cannot build the extension against a matching postgresql-server-dev package, or if your workload is short transactional statements. Before committing, verify that the extension builds for your PostgreSQL major version, that read_parquet can reach your object store with the credentials you plan to use, and what happens to a query when duckdb.force_execution is left off.

## FAQ

### What is pg_duckdb?

It is the official PostgreSQL extension for DuckDB, described in the README as integrating DuckDB's columnar-vectorized analytics engine into PostgreSQL for high performance analytics and data-intensive applications. It was built in collaboration with Hydra and MotherDuck.

### How to install pg_duckdb?

The README's quick start runs a prebuilt image with docker run -d -e POSTGRES_PASSWORD=duckdb pgduckdb/pgduckdb:18-v1.1.1, or you can clone the repository and run make install, with docs/compilation.md covering the build requirements.

### How is pg_duckdb different from using DuckDB on its own?

With pg_duckdb the PostgreSQL server is the host: your existing tables stay in PostgreSQL and DuckDB runs as the execution engine inside it once duckdb.force_execution is set. A standalone DuckDB process would instead need a way to scan PostgreSQL tables, which is the reverse arrangement.

## Sources

- [duckdb/pg_duckdb on GitHub](https://github.com/duckdb/pg_duckdb)
- [Issues](https://github.com/duckdb/pg_duckdb/issues)
- [License: MIT](https://github.com/duckdb/pg_duckdb/blob/main/LICENSE)
- [README](https://github.com/duckdb/pg_duckdb/blob/main/README.md)
- [Releases](https://github.com/duckdb/pg_duckdb/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/duckdb-pg-duckdb