pg_lakehouse: A DuckDB Alternative in Postgres

Written by Ming Ying on June 3, 2024

Our newest extension, pg_lakehouse, transforms Postgres into a DuckDB alternative. Like DuckDB, pg_lakehouse allows Postgres to directly query external object stores like S3, table formats like Delta Lake, and file formats like Parquet.

There are two main goals of pg_lakehouse:

1. Enable fast analytics over data lakes without any tools besides Postgres.
2. Easily join and move data between Postgres tables and data lakes.

​

Putting a Foreign Data Wrapper on Steroids

Under the hood, pg_lakehouse uses the foreign data wrapper API to connect to external data sources. While there are other foreign data wrappers in the Postgres ecosystem, pg_lakehouse differentiates in two ways:

1. Highly performant: Queries are pushed down to Apache DataFusion, an analytical query engine that’s 8X faster than Elasticsearch and rivals the performance of state-of-the-art analytical engines.

2. Breadth of coverage: Foreign data wrappers like parquet_fdw or aws_s3 are built for a specific file format or data store. Because integrating with a new data source typically means writing a new extension, the long tail of data sources, table formats, and file formats are not supported in Postgres. pg_lakehouse solves this problem by using Apache OpenDAL, a data access library for 40+ data stores.

​

How It’s Built

pg_lakehouse uses two Postgres APIs: the executor hook and foreign data wrapper. In Postgres, the executor hook is a function — run immediately after a query plan is generated — that executes the query plan. Extensions can override this function with custom logic. pg_lakehouse’s executor hook reroutes foreign table queries to DataFusion, which executes the query instead of the Postgres query engine.

If the query fails for any reason inside DataFusion, it gracefully falls back to Postgres. This is where the foreign data wrapper comes in. One responsibility of the foreign data wrapper is to scan the foreign table and send rows to the Postgres query engine.

Typically, a query will fall back to Postgres if it contains a clause that cannot (yet) be pushed down to DataFusion. Users can determine which query engine was used by running EXPLAIN.

EXPLAIN SELECT COUNT(*) FROM trips;
                  QUERY PLAN
----------------------------------------------
 DataFusionScan: LogicalPlan
  Projection: COUNT(*)
   Aggregate: groupBy=[[]], aggr=[[COUNT(*)]]
     TableScan: trips
(4 rows)

​

Getting Started

We’ve provided an example Parquet file of 3 million NYC taxi trips hosted in a public S3 bucket for testing. After the extension is installed, you can run the following code to query this dataset:

CREATE EXTENSION pg_lakehouse;

CREATE FOREIGN DATA WRAPPER s3_wrapper
HANDLER s3_fdw_handler
VALIDATOR s3_fdw_validator;

-- Provide S3 credentials
CREATE SERVER s3_server FOREIGN DATA WRAPPER s3_wrapper
OPTIONS (
    region 'us-east-1',
    allow_anonymous 'true'
);

-- Create foreign table
CREATE FOREIGN TABLE trips (
    "VendorID"              INT,
    "tpep_pickup_datetime"  TIMESTAMP,
    "tpep_dropoff_datetime" TIMESTAMP,
    "passenger_count"       BIGINT,
    "trip_distance"         DOUBLE PRECISION,
    "RatecodeID"            DOUBLE PRECISION,
    "store_and_fwd_flag"    TEXT,
    "PULocationID"          REAL,
    "DOLocationID"          REAL,
    "payment_type"          DOUBLE PRECISION,
    "fare_amount"           DOUBLE PRECISION,
    "extra"                 DOUBLE PRECISION,
    "mta_tax"               DOUBLE PRECISION,
    "tip_amount"            DOUBLE PRECISION,
    "tolls_amount"          DOUBLE PRECISION,
    "improvement_surcharge" DOUBLE PRECISION,
    "total_amount"          DOUBLE PRECISION
)
SERVER s3_server
OPTIONS (
    path 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet',
    extension 'parquet'
);

-- Success! Now you can query the file like a regular Postgres table
SELECT COUNT(*) FROM trips;
  count
---------
 2964624
(1 row)

To connect your own object store, please refer to our documentation.

​

What’s Next

Our development efforts over the coming months are focused around three areas:

1. Write Support: pg_lakehouse is currently read-only from object stores. Adding write support will enable developers to further centralize data lake operations inside Postgres.

2. Iceberg Support: We are building support for Apache Iceberg tables. This will likely involve contributing to the iceberg-rust project.

3. Wider Object Store Coverage: ParadeDB uses Apache OpenDAL to integrate with object stores. This makes it straightforward to add support for many other object stores. To request prioritization for a specific object store, please open a GitHub issue.

pg_lakehouse is open-source and licensed under AGPL. If you’d like to contribute, the best place to start is our Slack community. And please don’t hesitate to show your support by giving us a star!