Skip to content

pg_lake_table

Data lake tables and Iceberg tables

pg_lake : Data lake tables and Iceberg tables

Overview

ID Extension Package Version Category License Language
2566 pg_lake_table pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd-- No Yes Yes Yes no no
Loaded via pg_extension_base.

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake btree_gist, pg_lake_engine, pg_lake_iceberg
RPM PIGSTY 3.7.0 19 18 17 16 15 pg_lake_$v -
DEB PIGSTY 3.7.0 19 18 17 16 15 postgresql-$v-pg-lake -
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/tree/main/pg_lake_table

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_table;		# install by extension name, for the current active PG version

pig install pg_lake_table -v 19;   # install for PG 19
pig install pg_lake_table -v 18;   # install for PG 18
pig install pg_lake_table -v 17;   # install for PG 17
pig install pg_lake_table -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_table CASCADE; -- requires btree_gist, pg_lake_engine, pg_lake_iceberg

Usage

Sources:

pg_lake_table exposes object-store files as PostgreSQL foreign tables and provides the USING iceberg table syntax. It owns the pg_lake and pg_lake_iceberg foreign servers, file inspection/cache utilities, table catalogs, and transaction hooks; Iceberg metadata encoding is delegated to pg_lake_iceberg.

The reviewed upstream release is 3.6.0, with SQL extension version 3.6 and PostgreSQL 16–19 support. Existing packaged versions are tracked separately. Keep libraries and the query server from the same release.

Query External Files

Install the complete stack, including the required pg_lake_engine, pg_lake_iceberg, and btree_gist dependencies:

CREATE EXTENSION pg_lake CASCADE;

An empty column list asks pg_lake to infer the file schema:

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

SELECT count(*) FROM external_events;

Create a writable Iceberg table through the extension-provided table access method:

CREATE TABLE managed_events (
    event_time timestamptz,
    payload jsonb
) USING iceberg;

File and Table Utility Index

  • lake_file.list(pattern): lists matching object paths, sizes, modification times, and ETags.
  • lake_file.size(path) and lake_file.exists(path): inspect one remote object.
  • lake_file.preview(url, format, compression): returns inferred column names and types.
  • lake_file.delete(url): deletes a remote object; restrict it to roles that are allowed to remove data.
  • lake_file_cache.add(path, refresh), remove(path), and list(): manage the local file cache for members of lake_read.
  • lake_iceberg.table_size(regclass): totals the current data-file sizes of an Iceberg table.
  • Foreign servers pg_lake and pg_lake_iceberg: read-file and writable-Iceberg entry points.
  • Access methods iceberg and pg_lake_iceberg: aliases used to translate CREATE TABLE ... USING into the extension’s foreign-table representation.
SELECT path, file_size
FROM lake_file.list('s3://analytics-bucket/events/**/*.parquet');

SELECT *
FROM lake_file.preview('s3://analytics-bucket/events/sample.parquet');

Operational Caveats

  • pg_extension_base must be preloaded and pgduck_server must be running with credentials for every referenced location.
  • lake_read, lake_write, and lake_read_write control access to servers, schemas, and utilities. Grant the narrowest role required by each application.
  • External tables are references to files, not imported copies. File replacement, cross-region access, and cache invalidation can change latency or results independently of PostgreSQL catalog state.
  • Iceberg inserts are optimized for batches rather than single rows. Use a staging heap table for high-rate row-at-a-time ingestion and periodically flush batches.
  • Internal lake_table.* catalogs track files, field IDs, partitions, and recovery state. Do not modify them directly.

Version 3.5 adds indexes to file-deletion mappings, column statistics, and partition-value catalogs to avoid repeated child-catalog scans during bulk removal. Apply the SQL migration as well as installing the new library.

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. The table component registers a separate object-store catalog-export worker and changes catalog-name helpers to STABLE. Apply its SQL migration; replacing binaries alone does not register the worker.

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.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.

Table reader