pg_lake_table

Overview

pg_extension_base auto-loads pg_lake_engine, pg_lake_iceberg, and pg_lake_table in dependency order. 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:

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_table CASCADE;  -- requires: btree_gist, pg_lake_engine, pg_lake_iceberg

Usage

Sources:

• Official data-lake file query guide
• Official Iceberg table guide
• Version 3.4 control file
• FDW, server, utility, and access-method SQL

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.

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.