pg_duckdb: Postgres analytics just got faster with DuckDB

Hands-On Demo: The 500x Performance Gain

Let's see it in action. The easiest way to get started is with the official Docker image, which comes with everything pre-configured.

Copy code
# Run the container with a permissive password setting for this demo
# In production, you should manage secrets properly!
docker run -d --name pgduckdb -e POSTGRES_HOST_AUTH_METHOD=trust ghcr.io/hydradatabase/pg_duckdb:main

Once the container is running, you can connect directly with psql:

Copy code
docker exec -it pgduckdb psql -U postgres

For this demo, we've pre-loaded a 1GB TPC-DS dataset, a standard benchmark for analytical systems. First, let's run a complex analytical query using the native PostgreSQL engine. We'll use \timing to measure how long it takes.

Copy code
-- psql
\timing
-- TPC-DS Query 1
SELECT
    c.c_customer_id AS customer_id,
    c.c_first_name AS customer_first_name,
    c.c_last_name AS customer_last_name,
    -- ... (rest of the complex query)
FROM
    customer c,
    customer_address ca,
    customer_demographics cd
WHERE
    ca.ca_city = 'Hopewell'
    -- ... (more joins and conditions)
GROUP BY
    c.c_customer_id,
    c.c_first_name,
    c.c_last_name;

Running this query on our instance took 1 minute and 29 seconds.

Copy code
Time: 89000.123 ms (01:29.000)

Now, let's unleash the duck. With pg_duckdb, all we have to do is enable the DuckDB engine with a simple SET command and run the exact same query.

Copy code
-- Enable the DuckDB engine for this session
SET pg_duckdb.enable = true;

-- Run the exact same TPC-DS query again
SELECT
    c.c_customer_id AS customer_id,
    -- ... (same query as before)

The result? 137 milliseconds.

Copy code
Time: 137.000 ms

That's not a typo. We went from nearly a minute and a half to just over a tenth of a second—a ~500x speedup. This is the power of DuckDB's columnar engine, which is purpose-built for analytical queries, reading only the data it needs from each column. This incredible boost is achieved with zero changes to how your data is stored; it's still a regular PostgreSQL table, but you're swapping in a more efficient engine for the job.

Connecting Postgres to MotherDuck

Connecting is simple. First, sign up for a free MotherDuck account and get a service token from the UI.

Next, you need to add this token to your postgresql.conf file.

Copy code
# postgresql.conf
# Add this line with your token
motherduck.token = 'md_my_super_secret_token'

After adding the token, restart your PostgreSQL instance.

Security Best Practice: Hardcoding secrets is not ideal. For a more secure setup, pg_duckdb also supports reading the token from an environment variable. You can set motherduck_token in your environment and use this line in postgresql.conf instead: motherduck.token = '${motherduck_token}'

Hybrid Queries: The Best of Both Worlds

Once connected, you can query MotherDuck directly from psql. MotherDuck includes a shared database, sample_data, which you can query immediately. Let's count the mentions of "DuckDB" in Hacker News titles from 2022.

Copy code
-- This query runs on MotherDuck's cloud infrastructure
SELECT
    date_trunc('month', "timestamp") AS month,
    count(*) AS mentions
FROM sample_data.hacker_news.stories_2022
WHERE
    lower(title) LIKE '%duckdb%'
GROUP BY 1
ORDER BY 1;

The true power lies in moving data effortlessly between your local Postgres instance and MotherDuck.

1. Pulling analytical results from MotherDuck into a local PostgreSQL table:

Copy code
-- Create a local Postgres table from a MotherDuck query result
CREATE TABLE local_duckdb_mentions AS
SELECT
    date_trunc('month', "timestamp") AS month,
    count(*) AS mentions
FROM sample_data.hacker_news.stories_2022
WHERE
    lower(title) LIKE '%duckdb%'
GROUP BY 1;

2. Pushing a local PostgreSQL table up to MotherDuck:

Copy code
-- Create a table in MotherDuck from a local Postgres table
CREATE TABLE my_motherduck_backup.public.customer_archive
USING duckdb AS
SELECT * FROM public.customer;

This seamless, bi-directional data movement gives you ultimate flexibility, all without leaving the comfort of your psql prompt.

Get Started Today

• Try it now: The fastest way to start is with the Docker container.
• Check out the code: The project is open source on GitHub.
• Share your feedback: We're actively developing the roadmap. Please open an issue with feature requests or feedback!

In the meantime, keep quacking and keep coding.