gem-hunter

Native data lakehouse in PostgreSQL, powered by DuckLake (a lakehouse format built on SQL database and Parquet files).

Key Features

See it in action

Your first Data Lake in PostgreSQL

CREATE TABLE my_table (
    id INT,
    name TEXT,
    age INT
)
-- WITH (ducklake.table_path = 's3://my-bucket/prefix/') -- Or use AWS S3 as data storage.
USING ducklake;

INSERT INTO my_table VALUES (1, 'Alice', 25), (2, 'Bob', 30);

-- Each commit is a snapshot; capture the one before the DELETE.
SELECT max(snapshot_id) AS before_delete FROM ducklake.ducklake_snapshot \gset

DELETE FROM my_table WHERE id = 1;

SELECT * FROM my_table;

-- Time-travel back to the snapshot that still had Alice.
SELECT * FROM ducklake.time_travel('my_table'::regclass, :before_delete);

Access your data with external DuckDB

INSTALL ducklake;
LOAD ducklake;
ATTACH 'ducklake:postgres:dbname=postgres host=localhost' AS my_ducklake (METADATA_SCHEMA 'ducklake');
SELECT * FROM my_ducklake.public.my_table;

Quick Start

Docker

docker run -d -e POSTGRES_PASSWORD=duckdb --name pgducklake pgducklake/pgducklake:18-main

docker exec -it pgducklake psql

Compile from source

git clone https://github.com/relytcloud/pg_ducklake
cd pg_ducklake
make install

Usecases

Convert a PostgreSQL heap table into a DuckLake table

-- Create a PostgreSQL row-store (heap) table.
CREATE TABLE row_store_table AS
SELECT i AS id, 'hello pg_ducklake' AS msg
FROM generate_series(1, 10000) AS i;

-- Create a DuckLake column-store table via ETL.
CREATE TABLE col_store_table USING ducklake AS
SELECT *
FROM row_store_table;

-- Run analytics against the converted table.
SELECT max(id) FROM col_store_table;

Load an external dataset

CREATE TABLE titanic USING ducklake AS
SELECT * FROM ducklake.read_csv('https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv');

SELECT "Pclass", "Sex", COUNT(*), AVG("Survived") AS survival_rate
FROM titanic
GROUP BY "Pclass", "Sex";

Data Inlining

CALL ducklake.set_option('data_inlining_row_limit', 100);

CREATE TABLE events (id INT, kind TEXT) USING ducklake;

-- Inlined rows are immediately visible to every reader.
INSERT INTO events VALUES (1, 'login'), (2, 'click');
SELECT * FROM events ORDER BY id;

-- Flush inlined rows out to Parquet (the background worker does this too).
SELECT * FROM ducklake.flush_inlined_data('events'::regclass);

Sorted Tables & Bucket Partitioning

CREATE TABLE measurements (
    device_id INT,
    ts TIMESTAMP,
    reading DOUBLE PRECISION
) USING ducklake;

-- Distribute files by a hash bucket on device_id and by month of the timestamp.
CALL ducklake.set_partition('measurements'::regclass, 'bucket(4, device_id)', 'month(ts)');

-- Keep rows ordered within each file (or use CALL ducklake.set_sort(...)).
CREATE INDEX ON measurements USING ducklake_sorted (device_id, ts);

INSERT INTO measurements VALUES
    (1, '2024-01-15 10:00', 21.5),
    (2, '2024-02-20 11:00', 22.1);

Maintenance

-- Compact small adjacent Parquet files into fewer, larger ones.
SELECT * FROM ducklake.merge_adjacent_files('my_table'::regclass);

-- Expire snapshots beyond a retention window, then delete their files.
CALL ducklake.set_option('expire_older_than', '7 days');
SELECT * FROM ducklake.expire_snapshots();
SELECT * FROM ducklake.cleanup_old_files();

Cloud storage credentials

SELECT ducklake.create_s3_secret(
    's3', 'AKIA...', 'secret...',
    region => 'us-east-1', endpoint => 's3.amazonaws.com');

SET ducklake.default_table_path = 's3://my-bucket/prefix/';

Documentation

Contributing

Acknowledgments