pg_lake

Overview

Pigsty packages this release for PG16-18. Configure shared_preload_libraries=pg_extension_base and run the matching PG-major pgduck_server process. RPM supports EL9/EL10 only; EL8 is rejected because OpenSSL 3 is required. DEB supports Debian 12/13 and Ubuntu 22.04/24.04/26.04 on amd64/arm64. DuckDB and Avro are private per PG major. Co-installation with pg_duckdb, pg_mooncake, and duckdb_fdw is file-safe, but overlapping hooks and COPY behavior can be preload-order-sensitive. Extension SQL/control version is 3.4; source and DEB/RPM package version is 3.4.0.

Version

Build

You can build the RPM / DEB packages for pg_lake using pig build:

pig build pkg pg_lake         # build RPM / DEB packages

Install

You can install pg_lake directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_lake;          # Install for current active PG version

pig ext install -y pg_lake -v 18  # PG 18
pig ext install -y pg_lake -v 17  # PG 17
pig ext install -y pg_lake -v 16  # PG 16

dnf install -y pg_lake_18       # PG 18
dnf install -y pg_lake_17       # PG 17
dnf install -y pg_lake_16       # PG 16

apt install -y postgresql-18-pg-lake   # PG 18
apt install -y postgresql-17-pg-lake   # PG 17
apt install -y postgresql-16-pg-lake   # PG 16

Preload:

shared_preload_libraries = 'pg_extension_base';

Create Extension:

CREATE EXTENSION pg_lake CASCADE;  -- requires: pg_lake_copy, pg_lake_table

Usage

Sources:

• Official pg_lake README
• Version 3.4 control file
• Official build and startup guide
• Official project documentation index

pg_lake is the top-level extension for Snowflake’s PostgreSQL lakehouse stack. It installs the table, Iceberg, copy, query-engine, extension-base, and map components needed to query object-store files and create transactional Iceberg tables. The PostgreSQL extensions orchestrate planning and transactions while a separate local pgduck_server process executes vectorized work with DuckDB.

Start the Stack

Version 3.4 supports PostgreSQL 16 through 18. Preload the common extension infrastructure, restart PostgreSQL, and start pgduck_server on the database host:

shared_preload_libraries = 'pg_extension_base'

pgduck_server --cache_dir /var/cache/pg_lake

Create the complete dependency tree in the target database:

CREATE EXTENSION pg_lake CASCADE;
SELECT lake.version();

Configure object-store credentials for pgduck_server, then choose the managed Iceberg location:

SET pg_lake_iceberg.default_location_prefix =
    's3://analytics-bucket/warehouse';

Core Workflows

Create and modify a transactional Iceberg table:

CREATE TABLE measurements (
    station_name text NOT NULL,
    measured_at timestamptz NOT NULL,
    value double precision
) USING iceberg;

INSERT INTO measurements VALUES
    ('Istanbul', now(), 18.5),
    ('Haarlem', now(), 9.3);

Import or export Parquet, CSV, or newline-delimited JSON through COPY:

COPY (SELECT * FROM measurements)
TO 's3://analytics-bucket/export/measurements.parquet';

COPY measurements
FROM 's3://analytics-bucket/import/measurements.parquet';

Query files without loading them into PostgreSQL:

CREATE FOREIGN TABLE external_events ()
SERVER pg_lake
OPTIONS (path 's3://analytics-bucket/events/*.parquet');

SELECT count(*) FROM external_events;

Component Index

• pg_lake: meta-extension and lake.version().
• pg_lake_table: data-lake FDW, Iceberg table syntax, file utilities, and table catalogs.
• pg_lake_iceberg: Iceberg metadata, snapshots, manifests, and catalog integration.
• pg_lake_copy: COPY interception for object-store files and lake formats.
• pg_lake_engine: shared query rewrite, type conversion, cleanup, and pgduck_server client layer.
• pg_extension_base: preload and lifecycle-worker infrastructure.
• pg_map: generated PostgreSQL map types used for nested lake data.

Operational Caveats

• pgduck_server is required for lake queries and must have working object-store credentials and local socket connectivity from PostgreSQL.
• S3 and compatible credentials are resolved by the DuckDB secrets/credential chain. Grant only the bucket permissions required by the workload.
• Iceberg writes create Parquet files per statement. Batch inserts and run regular VACUUM to avoid many small files.
• The PostgreSQL extensions, pgduck_server, object-store data, and Iceberg catalog form one deployment unit. Back up and upgrade them as separate evidence layers; creating the extension alone does not prove the external services are usable.