Skip navigation
Let Postgres own the Iceberg catalog and delegate analytics to DuckDB.
0:00 / 0:24

Let Postgres own the Iceberg catalog and delegate analytics to DuckDB.

Denis Magda

@denismagda5.1K followers

Visit on X
369 likesDownloadOriginal post

28,597 views

Let Postgres own the Iceberg catalog and delegate analytics to DuckDB. The result => transactional lakehouse updates with fast analytical queries. This isn’t a concept. It’s exactly what pg_lake delivers today. pg_lake combines a set of extensions and components that let you query and modify Iceberg tables (and other lakehouse formats) directly from Postgres. DuckDB is used to accelerate analytical queries and runs in a sidecar process called pgduck_server, which communicates with Postgres during query execution. How it works (diagram below): 1. An application sends a query to Postgres to calculate unrealized PnL (Profit and Loss) for the Disney ticker. 2. Postgres parses the query and identifies the part that computes the average price from historical lakehouse data. 3. That part is forwarded to pgduck_server for accelerated execution. 4. pgduck_server delegates execution to DuckDB, which queries the lakehouse (reusing cached data if available). 5. DuckDB computes the average price and returns it to Postgres. 6. Postgres joins the result with local portfolio data and computes the unrealized PnL. 7. The final result is returned to the application.

10 Comments

Comments from the original post on X (Twitter)

  • Data is Life's profile picture

    @Data_ls_Life

    Just use DuckLake

    2 likes

  • Denis Magda's profile picture

    @denismagda

    Does it let me query and join transactional data stored in Postgres with historical data in the lakehouse?

    1 like

  • joaah's profile picture

    @joasahacker

    @Data_ls_Life YES

    3 likes

  • Denis Magda's profile picture

    @denismagda

    @Data_ls_Life I’l check it out. Thanks for sharing

    1 like

  • Dipankar Sarkar's profile picture

    @dipankarsarkar

    Sounds great!

    1 like

  • Alexander Schröder's profile picture

    @flugelduvel

    Classic federation. Works well if query can be pushed down and cardinality of fetched data is small. Will be painfull as soon as there is a big cross-database join or so.

    1 like

  • Tobias_Petry.sql's profile picture

    @tobias_petry

    > 4. pgduck_server delegates execution to DuckDB, which queries the lakehouse (reusing cached data if available). How does it reuse cached data? Did you setup a file-based cache for the lakehouse data?

    1 like

  • fictiousxxl's profile picture

    @Mr_D_V_D

    can we see the implementation of this so we understand the actual setup

  • Lars Hansen's profile picture

    @larshansen

    So compared to using DuckLake directly, here the DuckLake tables becomes first-class Postgres citizens? (instead of using special client)

  • Denis Magda's profile picture

    @denismagda

    I'm not familiar with DuckLake yet. So, can't compare architectural differences.