Skip to content

pg_lake_iceberg

Iceberg implementation in Postgres

pg_lake : Iceberg implementation in Postgres

Overview

ID Extension Package Version Category License Language
2565 pg_lake_iceberg pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--s-d-- No Yes No Yes no no
Requires plpgsql.

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake pg_lake_engine, plpgsql
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_iceberg

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 19;   # install for PG 19
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:

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

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.

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

lake_iceberg.catalog_stats() reports nonzero REST-catalog counters since server startup, including requests, retries, scans and credential outcomes; it does not expose table or namespace names.

Table reader