pg_duckdb: Splicing Duck and Elephant DNA

If / as we move to analytic workloads it would be awesome to see postgres pickup support for AsOf, time_bucket, etc that duckdb and timescale have.

I don't and never have enjoyed SQL and I much prefer the ergonomics of time_bucket to date_bin.

For example, I would do this in duckdb:

  SELECT
    count(*) as y
    , time_bucket(interval '2 weeks', at::timestamp) as x
  FROM analytics
  WHERE some_bool AND some_haystack = 'needle'
  GROUP BY x
  ORDER BY x

In postgres it looks more like:

  with counts as (
    SELECT 
        date_bin('1 hour'::interval, at, (now() - interval '2 weeks')::timestamp)
        , count(*) c
    FROM analytics a
    WHERE some_bool AND some_haystack = 'needle'
    GROUP BY date_bin
  )
  select series as x, coalesce(counts.c, 0) as y
  from generate_series(
    (now() - interval '2 weeks')::timestamp, 
    now()::timestamp,
    interval '1 hour'
  ) series
  LEFT JOIN counts
  ON counts.date_bin = series;

Very much agreed with this general idea, and believe a lot of this was inspired by the team we hired at Crunchy Data to build it as they were socializing it for a while. Looking forward to pg_duckdb advancing in time for now it still seems pretty early and has some maturing to do. As others have said, it needs to be a bit more stable and production grade. But the opportunity is very much there.

We recently submitted our (Crunchy Bridge for Analytics-at most broad level based on same idea) benchmark for clickbench by clickhouse (https://benchmark.clickhouse.com/) which puts us at #6 overall amongst managed service providers and gives a real viable option for Postgres as an analytics database (at least per clickbench). Also of note there are a number of other Postgres variations such as ParadeDB that are definitely not 1000x slower than Clickhouse or DuckDB.

Would be helpful to list the features. This link has the details:

https://github.com/duckdb/pg_duckdb

Sounds like it would be useful for Postgres users to interact with Parquet and CSV data within a single SQL query and in a performant way (due to DuckDB's vectorization).

Postgres IS missing an analytics engine. benchmark.clickhouse.com puts it at the bottom of the list and ~1000x slower than @duckdb and @ClickHouseDB.

Here are the scenarios and how to address them

1. Query Parquet and Iceberg from Postgres. When Parquet files are stored in S3 Postgres should be able to run analytical queries on them.

2. Postgres should allow creation of columnstore tables inside Postgres storage subsystem. Analytical queries on top of these table should be FAST. Top 10 on Clickbench fast. This allows to run analytics without S3 and have super low latencies for analytics.

3. Postgres should allow creation of secondary columnstore indexes to speed up analytical queries in mixed workloads. This is super useful for Oracle migrations since Oracle had this feature for a while.

So How do we get there? 10 years ago it would be a MASSIVE project, but today we have @duckdb - super fast analytical engine with an open license. The work is still not trivial, but it is much much simpler.

First you need to integrate an analytical query processor into Postgres and today @duckdblabs announced github.com/duckdb/pg_duck…. Yay and congrats!

This plugin runs duckdb alongside with Postgres and integrated Postgres syntax with the @duckdb query processor (QP)

With that it now can trivially query external files from S3. This addresses scenario 1.

With that it now can trivially query external files from S3. This addresses scenario 1.

Building columnar table requires either implementing columnar storage from scratch or integrating duckdb storage into the Postgres subsystem. You can of course let duckdb create duckdb files on local disk, but then all the Postgres machinery: replication, backup, recovery won't work

Duckdb tables have to mapped into 8kb Postgres pages pushed through the Postgres WAL for replication, recovery and transactionality. This will give us scenario 2

Scenario 3 is even more work. You need secondary index maintenance and it will require hybrid query execution. We will need to modify Postgres executor so that it can mix and match regular Postgres query operators and "vectorized" query operators from duckdb. Or built vectorized operators into Postgres

Scenarios 2 and 3 will take some time, but I'm excited for this roadmap: this will unlock a huge world for millions of Postgres users and simplify the lives of many developers dealing with moving data between transactional and analytical systems.

Excited about the work here. However, my 2 cents - for this to become a reality (serious production use at scale), I don’t think it’s just based on the choice of the analytical engine (here, DuckDB), but rather on how well the Postgres extension is built. The Postgres extension framework is complex, still maturing, and doesn’t offer full flexibility to implement features. We saw this closely at Citus. It was a deterrent to competing with native analytical databases like ClickHouse and Snowflake. A bunch of customers, including CloudFlare and Heap, switched from Citus to ClickHouse and SingleStore, respectively. This was one of the inspirations to start PeerDB , to make it magical for customers to move data from Postgres to native and purpose-built analytical databases like ClickHouse.

Being a Postgres fan, Good luck and best wishes with the effort here!

how are we doing with each of these extensions [ pg_duckdb, pg_analytics (paradedb) , crunchy analytics ] sql syntax being compatible ? (or maybe standardizing on https://substrait.io/ ) .

It would be great if one could have a diversity of postgres's in a data mesh but you can execute the same exact sql on them

Looking forward to this getting supported on Neon!

Does the pg_duckdb extension double-write data to DuckDB? I skimmed the blog post and didn't understand if the extension adds some capabilities to Postgres, or just makes it easier to maintain a consistent data model across two database engines.

They had to use duck DNA and not pig DNA because, as everyone already knows, pig and elephant DNA just don’t splice.

repo: https://github.com/duckdb/pg_duckdb

this looks awesome, I cant wait to play with it! we need to get this into RDS as soon as we can