pg_lake

pg_lake : Data lake extension by Snowflake

Overview

Packages

Source

pig build pkg pg_lake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_lake;		# install via package name, for the active PG version

pig install pg_lake -v 18;   # install for PG 18
pig install pg_lake -v 17;   # install for PG 17
pig install pg_lake -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_extension_base';

Create this extension with:

CREATE EXTENSION pg_lake CASCADE; -- requires pg_lake_copy, pg_lake_table

Usage

Sources:

• Official pg_lake README
• Version 3.5 control file
• Official build and startup guide
• Official project documentation index
• pg_lake v3.5.3 release
• DuckDB secrets manager

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.

The pg_lake release and package version is 3.5.3; the SQL extension version is 3.5. Keep the libraries and query server from the same release.

Start the Packaged Stack

Version 3.5 supports PostgreSQL 16 through 18. The PIGSTY RPM and DEB packages install the extension files and a versioned pgduck_server binary, but they do not currently install or auto-start a systemd service. Running CREATE EXTENSION does not start pgduck_server either.

Add pg_extension_base to shared_preload_libraries and restart PostgreSQL:

shared_preload_libraries = 'pg_extension_base'

pgduck_server listens on /tmp/.s.PGSQL.5332 with mode 0770 by default. Run it as the PostgreSQL operating-system user so PostgreSQL can access the socket. Do not start it as an unrelated login user with the bare command.

# Debian/Ubuntu with PostgreSQL 18; use 16 or 17 as appropriate.
PG_LAKE_SERVER=/usr/lib/postgresql/18/bin/pgduck_server
# RHEL-compatible systems use /usr/pgsql-18/bin/pgduck_server.

sudo install -d -o postgres -g postgres -m 0700 \
  /var/lib/pg_lake /var/lib/pg_lake/extensions
sudo install -d -o postgres -g postgres -m 0750 /var/cache/pg_lake
sudo -u postgres -H "$PG_LAKE_SERVER" \
  --duckdb_database_file_path /var/lib/pg_lake/pgduck_server.db \
  --extensions_dir /var/lib/pg_lake/extensions \
  --cache_dir /var/cache/pg_lake

This command runs in the foreground and must remain running. Use a service supervisor for production. If you use a dedicated service account instead, make it a member of the postgres group and start the server with --unix_socket_group postgres --unix_socket_permissions 0770.

In another terminal, verify the query engine before creating the extensions:

sudo -u postgres psql -X \
  "host=/tmp port=5332 dbname=postgres connect_timeout=2" \
  -c 'SELECT version();'

Then create the complete dependency tree in the target database:

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

Configure Object-Store Access

Object-store credentials are resolved by pgduck_server, not by the PostgreSQL backend. AWS and GCP can use their normal credential chains. For a local S3-compatible endpoint such as MinIO, first create the bucket, connect directly to pgduck_server, and create a persistent DuckDB secret:

sudo -u postgres psql -X -h /tmp -p 5332 -d postgres

CREATE PERSISTENT SECRET pglake_object_store (
    TYPE S3,
    KEY_ID 'access-key',
    SECRET 'secret-key',
    REGION 'us-east-1',
    ENDPOINT 'minio.example.com:9000',
    SCOPE 's3://analytics-bucket',
    URL_STYLE 'path',
    USE_SSL false
);

Connect to PostgreSQL, then choose the managed Iceberg location in the same session that creates the table:

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 on every PostgreSQL host that can execute lake queries. Keep it supervised and verify its local socket before serving traffic.
• The default socket mode is 0770; its owner and group come from the account that starts pgduck_server. A mismatched service user causes ERROR: could not start query engine.
• S3 and compatible credentials are resolved by the DuckDB secrets/credential chain. Grant only the bucket permissions required by the workload.
• The first start may download the DuckDB spatial extension. Ensure the service account has the required network access and writable state/cache directories.
• CREATE PERSISTENT SECRET survives server restarts, but DuckDB stores it unencrypted under ~/.duckdb/stored_secrets. Keep the service account and its home directory stable, restrict permissions, and protect those files as credentials.
• The default memory limit is 80 percent of system memory. Set --memory_limit explicitly when PostgreSQL and pgduck_server share a production host.
• 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.

Upgrade the Stack

Deploy matching 3.5.3 extension files and pgduck_server, then restart the processes that have loaded the old libraries. In each database run the extension migration and check every component version:

ALTER EXTENSION pg_lake UPDATE TO '3.5';
SELECT extname, extversion
FROM pg_extension
WHERE extname LIKE 'pg_lake%'
   OR extname IN ('pg_extension_base', 'pg_extension_updater', 'pg_map')
ORDER BY extname;

The 3.5.3 patch hardens object deletion credential selection and avoids publishing nameless catalog entries. It retains SQL version 3.5, so SQL version alone cannot prove that the matching patch-level binaries are deployed.