pg_lake_iceberg

pg_lake : Iceberg implementation in Postgres

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

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

Create this extension with:

CREATE EXTENSION pg_lake_iceberg CASCADE; -- requires pg_lake_engine, plpgsql

Usage

Sources:

• Official Iceberg table guide
• Version 3.5 control file
• Iceberg metadata SQL API
• Version 3.4 catalog FDW SQL

pg_lake_iceberg implements Iceberg metadata, snapshots, manifests, partition specifications, and catalog integration inside PostgreSQL. The familiar CREATE TABLE ... USING iceberg syntax is exposed by the dependent pg_lake_table component; users normally install both through pg_lake.

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.

Create and Inspect an Iceberg Table

CREATE EXTENSION pg_lake CASCADE;

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

CREATE TABLE events (
    event_time timestamptz NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb
) USING iceberg
WITH (partition_by = 'day(event_time), bucket(32, user_id)');

SELECT table_namespace, table_name, metadata_location
FROM iceberg_tables
WHERE table_name = 'events';

Inspect an Iceberg metadata file and its referenced state:

SELECT lake_iceberg.metadata(metadata_location)
FROM iceberg_tables
WHERE table_name = 'events';

SELECT f.*
FROM iceberg_tables AS t
CROSS JOIN LATERAL lake_iceberg.files(t.metadata_location) AS f
WHERE t.table_name = 'events';

Metadata and Catalog API

• iceberg_tables: pg_catalog view combining local managed tables and external catalog entries.
• iceberg_namespace_properties: catalog namespace properties.
• lake_iceberg.metadata(uri): raw Iceberg metadata JSON.
• lake_iceberg.files(uri): manifest path, content kind, data-file path/format, spec ID, record count, and file size.
• lake_iceberg.snapshots(uri): sequence number, snapshot ID, timestamp, and manifest-list path.
• lake_iceberg.data_file_stats(uri): per-file sequence and lower/upper bounds; execution is granted to lake_read rather than PUBLIC.
• iceberg_catalog: version 3.4 FDW for named PostgreSQL, object-store, or REST catalog configurations.

Define a user-managed REST catalog server and keep credentials in a user mapping:

CREATE SERVER my_polaris TYPE 'rest'
FOREIGN DATA WRAPPER iceberg_catalog
OPTIONS (rest_endpoint 'https://polaris.example.com');

CREATE USER MAPPING FOR app_role SERVER my_polaris
OPTIONS (client_id 'app', client_secret 'secret');

CREATE TABLE catalog_events (id bigint)
USING iceberg
WITH (catalog = 'my_polaris');

Catalog and Storage Caveats

• User-created catalog servers require their own USER MAPPING credentials and do not fall back to the built-in REST catalog credential GUCs.
• The built-in postgres, object_store, and rest catalogs map to immutable extension-owned servers. Configure them through the documented GUCs rather than altering those servers.
• External modifications to iceberg_tables are blocked by default because changing metadata behind pg_lake can break transaction and query-engine consistency.
• Iceberg writes should be batched. Each statement can add Parquet files and snapshots; regular VACUUM compacts small files and expires data according to table/GUC policy.
• Iceberg has narrower representations for some PostgreSQL values. The default out_of_range_values = 'error' preserves integrity; clamp silently changes out-of-range temporal values and replaces some unsupported values with NULL.

The 3.4 to 3.5 Iceberg SQL migration is empty, while patch-level catalog and object-deletion fixes require the matching 3.5.3 binaries.