pg_lake
pg_lake : Data lake extension by Snowflake
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 2560 | pg_lake | pg_lake | 3.7 |
OLAP | Apache-2.0 | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
| --sLd-- | Yes | Yes | Yes | Yes | no | no |
| Relationships | |
|---|---|
| Schemas | lake pg_catalog |
| Requires | pg_lake_copy pg_lake_table |
| See Also | pg_ducklake pg_duckdb pg_parquet pg_mooncake duckdb_fdw aws_s3 file_fdw pg_bulkload pg_clickhouse columnar |
| Siblings | pg_extension_base pg_extension_updater pg_map pg_lake_engine pg_lake_iceberg pg_lake_table pg_lake_copy |
Packages
| Linux / PG | PG19 | PG18 | PG17 | PG16 | PG15 |
|---|---|---|---|---|---|
| el8.x86_64 | N/A | N/A | N/A | N/A | N/A |
| el8.aarch64 | N/A | N/A | N/A | N/A | N/A |
| el9.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| el9.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| el10.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| el10.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| d12.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| d12.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| d13.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| d13.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u22.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u22.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u24.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u24.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u26.x86_64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
| u26.aarch64 | N/A | PIGSTY 3.6.0 | PIGSTY 3.6.0 | PIGSTY 3.6.0 | N/A |
Source
github.com/Snowflake-Labs/pg_lake
pg_lake-3.7.0.tar.gz
Install
Make sure PGDG and PIGSTY repo available:
Install this extension with pig:
Config this extension to shared_preload_libraries:
Create this extension with:
Usage
Sources:
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 packaged startup example below retains the established 3.5.3 paths. The reviewed upstream release is 3.6.0, with SQL version 3.6 and PostgreSQL 16–19 support; this does not assert that 3.6 packages are available. Keep libraries and the 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:
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.
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:
Then create the complete dependency tree in the target database:
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:
Connect to PostgreSQL, then choose the managed Iceberg location in the same session that creates the table:
Core Workflows
Create and modify a transactional Iceberg table:
Import or export Parquet, CSV, or newline-delimited JSON through COPY:
Query files without loading them into PostgreSQL:
Component Index
pg_lake: meta-extension andlake.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:COPYinterception for object-store files and lake formats.pg_lake_engine: shared query rewrite, type conversion, cleanup, andpgduck_serverclient layer.pg_extension_base: preload and lifecycle-worker infrastructure.pg_map: generated PostgreSQL map types used for nested lake data.
Operational Caveats
pgduck_serveris 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 startspgduck_server. A mismatched service user causesERROR: 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 SECRETsurvives 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_limitexplicitly when PostgreSQL andpgduck_servershare a production host. - Iceberg writes create Parquet files per statement. Batch inserts and run regular
VACUUMto 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:
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.
Upstream 3.5.10 Changes
Release 3.5.10 hardens catalog-provided secret endpoints and file paths, pins background-worker search paths, and adds validation for Iceberg metadata. It supports vended Azure SAS tokens and a custom Horizon TLS CA path through pg_lake_iceberg.horizon_tls_ca_path. The query server also handles invalid nested values and oversized results as errors instead of crashing. These are upstream changes; the documented 3.5.3 packages do not establish that these fixes are installed. Deploy matching extension libraries and query-server binaries when upgrading.
3.5.10 Changes
Upstream 3.5.10 drops cached vended credentials after catalog vending stops, preserves Iceberg catalog URLs in object storage, and updates Brotli to 1.1.1 for CVE-2026-111497. Deploy matching extension libraries and pgduck_server; the SQL version remains 3.5.
Upstream 3.6.0
Upstream 3.6.0 adds PostgreSQL 19 support and uses DuckDB 1.5.6. Deploy matching libraries and pgduck_server, keep pg_extension_base preloaded, restart loaded processes and apply the supplied SQL updates to version 3.6. This component’s 3.5-to-3.6 SQL migration is empty; the matching runtime and its dependencies still need the coordinated release upgrade.
Upstream 3.6.1 catalog behavior
Read-only tables attached with catalog = 'snowflake' and inferred columns now default lowercase_column_names to true. Other documented read-only catalog cases retain their existing default; the option is fixed when the table is created and does not rename existing columns. Review inferred names and case collisions when attaching new tables.
The family retains SQL version 3.6. Deploy matching extension libraries and pgduck_server: the patch also includes a Brotli decoder security correction in the query server. Existing package versions are recorded independently and are not evidence that this upstream patch is installed.
Upstream 3.6.2
The 3.6.2 patch corrects byte-by-byte CSV-value transmission in pgduck_server. Deploy the matching query-server binary with the extension libraries and reconnect/restart processes holding old code. The component SQL version remains 3.6; an unchanged extversion does not prove the query-server fix is installed. Package availability remains a separate catalog fact.
Upstream 3.7.0
The current reviewed release is 3.7.0 and this component’s control version is 3.7. Earlier version sections describe historical behavior and package examples. Upgrade the complete family with matching libraries and pgduck_server, restart processes holding old code, and apply the supplied extension updates to SQL version 3.7. This does not establish package availability.
For newly attached read-only catalog = 'snowflake' tables, lowercase_column_names defaults to true and is fixed at creation. Case collisions are rejected. out_of_range_values = 'error' rejects infinite intervals on PostgreSQL 17+; clamp stores them as NULL without a warning. Review this loss of information before enabling it.