Catalogs and interoperability

An Iceberg catalog keeps track of which metadata file is the current version of each table. Engines find tables through a catalog, and commit changes by swapping the pointer to a new metadata file. pg_lake can use PostgreSQL itself as the catalog, or register tables in an external catalog, and it can read Iceberg tables written by other systems.

  1. Choosing a catalog
  2. The PostgreSQL catalog
    1. Reading tables from Spark
    2. Reading tables from Python
    3. Reading tables from any Iceberg engine
  3. REST catalogs
    1. Configure the built-in rest catalog
  4. External catalogs with CREATE SERVER
    1. Define a catalog server
    2. Credentials and permissions
    3. Query tables from an external catalog
      1. Lowercase column names from external engines
    4. Create tables in an external catalog
    5. Change or remove a catalog server
  5. The object store catalog
  6. External Iceberg tables from metadata files
  7. Reading external equality deletes
  8. Snowflake

Choosing a catalog

Every Iceberg table records its catalog in the catalog option when it is created. The choice cannot be changed afterwards.

Catalog catalog value What it does
PostgreSQL (default) postgres pg_lake is the catalog. Commits are part of the PostgreSQL transaction, and other engines read tables through the iceberg_tables view.
REST rest, or the name of a catalog server Tables are registered in an Iceberg REST catalog such as Apache Polaris, where every engine using that catalog can find them.
Object store object_store pg_lake publishes tables to a catalog file in object storage.

To change the default for new tables, set pg_lake_iceberg.default_catalog:

SET pg_lake_iceberg.default_catalog TO 'rest';

The PostgreSQL catalog

By default, pg_lake acts as its own Iceberg catalog. When a transaction modifies an Iceberg table, pg_lake writes the new metadata file and updates its catalog in the same transaction, so the table and the catalog never disagree.

The catalog is exposed as the iceberg_tables view:

SELECT catalog_name, table_namespace, table_name, metadata_location
FROM iceberg_tables;

 catalog_name | table_namespace |  table_name  |                              metadata_location
--------------+-----------------+--------------+------------------------------------------------------------------------------
 postgres     | public          | measurements | s3://testbucket/iceberg/postgres/public/measurements/metadata/00003-6403833e-0766-4496-ad47-ec9641ee965f.metadata.json

For tables created through PostgreSQL, catalog_name is the database name, and table_namespace is the schema. If the database is renamed, catalog_name changes with it.

The view has the layout that the Iceberg SQL catalog implementations expect: the Iceberg JDBC catalog (used by Spark, Flink and others), the pyiceberg SQL catalog and iceberg-rust. Those tools can connect to PostgreSQL and always read the latest committed version of each table. They cannot write to tables created by pg_lake; if they create tables of their own under a different catalog name, those tables have no corresponding PostgreSQL table.

Reading tables from Spark

Connect Spark’s Iceberg JDBC catalog to PostgreSQL. The catalog name must match the database name, postgres in this example:

export PGHOST="db host"
export PGDATABASE="postgres"
export PGUSER="user name"
export PGPASSWORD="your password"
export AWS_REGION="us-east-1"
export JDBC_CONN_STR="jdbc:postgresql://${PGHOST}/${PGDATABASE}?user=${PGUSER}&password=${PGPASSWORD}"

spark-sql --packages org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.4.1 \
  --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions \
  --conf spark.sql.catalog.postgres=org.apache.iceberg.spark.SparkCatalog \
  --conf spark.sql.catalog.postgres.catalog-impl=org.apache.iceberg.jdbc.JdbcCatalog \
  --conf spark.sql.catalog.postgres.uri=$JDBC_CONN_STR \
  --conf spark.sql.catalog.postgres.warehouse=s3:// \
  --conf spark.sql.catalog.postgres.io-impl=org.apache.iceberg.aws.s3.S3FileIO \
  --conf spark.sql.catalog.postgres.s3.endpoint=https://s3.${AWS_REGION}.amazonaws.com

A table created in PostgreSQL:

CREATE TABLE public.pg_lake_iceberg_table
USING iceberg
AS SELECT id FROM generate_series(0, 1000) id;

can then be queried from Spark:

spark-sql (default)> SELECT avg(id) FROM postgres.public.pg_lake_iceberg_table;
500.0

Reading tables from Python

pyiceberg’s SQL catalog works the same way:

from pyiceberg.catalog.sql import SqlCatalog

catalog = SqlCatalog(
    "postgres",  # must match the database name
    uri="postgresql+psycopg2://user:password@dbhost:5432/postgres",
    warehouse="s3://mybucket/iceberg",
)

table = catalog.load_table("public.measurements")
df = table.scan(row_filter="measurement > 20").to_pandas()

Reading tables from any Iceberg engine

Any engine that can open an Iceberg table from a metadata file can read a snapshot of a pg_lake table by its metadata_location. The location changes on every commit, so this gives a point-in-time view; use a catalog to always see the latest version. The sync use case reads a table this way from DuckDB.

REST catalogs

With a REST catalog, pg_lake creates and commits tables in an external catalog service instead of its own catalog, and other engines using that catalog see them immediately. pg_lake can also attach tables that other engines created there. It speaks the Iceberg REST catalog protocol with OAuth2 client credentials, and has been tested with Apache Polaris.

Writing to an external catalog is still experimental. See Create tables in an external catalog.

There are two ways to connect to a REST catalog:

  • The built-in rest catalog is configured once by a superuser, and every database user shares its credentials.
  • Catalog servers created with CREATE SERVER can point at any number of catalogs, each user can have their own credentials, and they do not need a superuser. See External catalogs with CREATE SERVER.

Configure the built-in rest catalog

The built-in rest catalog is configured by a superuser, for example in postgresql.conf or with ALTER SYSTEM:

ALTER SYSTEM SET pg_lake_iceberg.rest_catalog_host TO 'https://polaris.example.com/api/catalog';
ALTER SYSTEM SET pg_lake_iceberg.rest_catalog_client_id TO '<client id>';
ALTER SYSTEM SET pg_lake_iceberg.rest_catalog_client_secret TO '<client secret>';
SELECT pg_reload_conf();
Setting Description
pg_lake_iceberg.rest_catalog_host Base URL of the REST catalog API.
pg_lake_iceberg.rest_catalog_client_id OAuth2 client ID.
pg_lake_iceberg.rest_catalog_client_secret OAuth2 client secret.
pg_lake_iceberg.rest_catalog_oauth_host_path Token endpoint URL, if the catalog does not serve it at the default path.
pg_lake_iceberg.rest_catalog_scope OAuth2 scope. Default PRINCIPAL_ROLE:ALL.
pg_lake_iceberg.rest_catalog_enable_vended_credentials Ask the catalog for temporary storage credentials instead of using pgduck_server’s own. Default off.

Tables then use catalog = 'rest', and are created and attached the same way as with a catalog server.

External catalogs with CREATE SERVER

A catalog server describes one external Iceberg REST catalog: where it is and how to log in. You create it with CREATE SERVER and the iceberg_catalog foreign data wrapper, give database users credentials with user mappings, and then name the server in the catalog option of a table. A database can have several catalog servers, for example one per team or per cloud account, and a single query can combine tables from all of them with tables in the PostgreSQL catalog and regular PostgreSQL tables.

Define a catalog server

Any member of lake_write can create a catalog server. Catalog servers always have TYPE 'rest', and the names postgres, object_store and rest are reserved for the built-in catalogs:

CREATE SERVER polaris TYPE 'rest'
  FOREIGN DATA WRAPPER iceberg_catalog
  OPTIONS (rest_endpoint 'https://polaris.example.com/api/catalog',
           location_prefix 's3://lake-bucket');
Option Where Description
rest_endpoint server Base URL of the REST catalog API.
location_prefix server Storage location for tables pg_lake creates in this catalog. pg_lake adds the database, schema and table name to it.
catalog_name server Catalog (warehouse) to attach existing tables from. Without it, pg_lake uses the prefix the catalog advertises, or the database name. A server with catalog_name can only be used to attach tables, see below.
oauth_endpoint server OAuth2 token endpoint URL, if the catalog does not serve it at <rest_endpoint>/v1/oauth/tokens.
enable_vended_credentials server Ask the catalog for temporary storage credentials instead of using pgduck_server’s own.
scope server or user mapping OAuth2 scope. Default PRINCIPAL_ROLE:ALL. The user mapping wins if both set it.
client_id, client_secret user mapping OAuth2 client credentials.

Credentials and permissions

Each database user logs in to the catalog with the credentials in their user mapping, so the catalog’s own access control decides what that user can see and change. A catalog server never falls back to the pg_lake_iceberg.rest_catalog_client_* settings of the built-in catalog: a user without a user mapping gets the error no credentials found for REST catalog.

-- credentials for one user
CREATE USER MAPPING FOR data_eng SERVER polaris
  OPTIONS (client_id '<client id>', client_secret '<client secret>');

-- credentials for everyone else
CREATE USER MAPPING FOR PUBLIC SERVER polaris
  OPTIONS (client_id '<reader client id>', client_secret '<reader client secret>');

A user mapping for a specific user takes precedence over the PUBLIC one. As with any foreign server, PostgreSQL only shows the options of a user mapping to that user, the server owner and superusers.

Inside PostgreSQL, the usual privileges apply on top:

  • Creating a table that uses a catalog server needs USAGE on the server, which its owner has. Use GRANT USAGE ON FOREIGN SERVER polaris TO <role> to let others create tables in it. Creating Iceberg tables also needs the lake_read_write role.
  • Querying a table needs SELECT on it, and a user mapping for the user or for PUBLIC.

Query tables from an external catalog

A table that another engine created in the catalog is attached with read_only, naming it as the catalog knows it. The columns come from the catalog:

CREATE TABLE orders () USING iceberg
WITH (catalog = 'polaris', read_only = true,
      catalog_name = 'sales', catalog_namespace = 'analytics',
      catalog_table_name = 'orders');

SELECT region, sum(amount) FROM orders GROUP BY region;
Option Description
catalog_name Catalog (warehouse) that holds the table. Defaults to the server’s catalog_name, then to the prefix the catalog advertises, then to the database name.
catalog_namespace Namespace of the table. Defaults to the PostgreSQL schema name.
catalog_table_name Name of the table in the catalog. Defaults to the PostgreSQL table name.
lowercase_column_names Fold column and struct field names to lowercase. Valid for read-only REST and object_store catalog tables; defaults to false.

When a read-only table is created and its lowercase catalog, namespace or table name does not exist in a REST catalog, pg_lake tries the uppercase name, which is how Snowflake stores unquoted identifiers. The name that matched is stored in the table options. Names that contain uppercase letters, and names set later with ALTER, are used as given.

An attached table always reads the catalog’s current version, so queries see new commits from other engines without any changes in PostgreSQL. Dropping it only removes it from PostgreSQL.

To attach many tables from one catalog, set catalog_name on the server and mirror the namespaces as schemas. The other catalog options then follow from the names you use:

CREATE SERVER sales_catalog TYPE 'rest'
  FOREIGN DATA WRAPPER iceberg_catalog
  OPTIONS (rest_endpoint 'https://polaris.example.com/api/catalog',
           catalog_name 'sales');

CREATE USER MAPPING FOR PUBLIC SERVER sales_catalog
  OPTIONS (client_id '<client id>', client_secret '<client secret>');

CREATE SCHEMA analytics;
CREATE TABLE analytics.orders () USING iceberg
WITH (catalog = 'sales_catalog', read_only = true);
CREATE TABLE analytics.customers () USING iceberg
WITH (catalog = 'sales_catalog', read_only = true);

For equality-delete support and its limitations, see Reading external equality deletes.

Attached tables cannot be written to. Writing would mean taking over the table’s metadata, field IDs and file inventory from whatever produced them, which pg_lake does not do: it writes only to tables it created. To move existing data under pg_lake, create a new table and copy into it.

Lowercase column names from external engines

Some engines write Iceberg column names in uppercase. PostgreSQL folds unquoted identifiers to lowercase, so a column named "ID" normally has to be quoted. Set lowercase_column_names = true to expose it as id; nested struct field names are folded too. The option changes column and struct field names only. Lowercase catalog, namespace and table names find their uppercase counterparts on their own, so the example below attaches PUBLIC.SALES:

CREATE TABLE sales () USING iceberg
WITH (catalog = 'polaris', read_only = true,
      catalog_name = 'sales', catalog_namespace = 'public',
      lowercase_column_names = true);

SELECT id, (address).city FROM sales;

The option cannot be changed after the table is created. Creation or query fails if two names in the same table or struct differ only in case, or if a column name folds to a system column name such as xmin or ctid.

Create tables in an external catalog

Writing to an external catalog is still experimental, both with catalog servers and with the built-in rest catalog.

Without read_only, pg_lake creates the table in the catalog and owns it: it writes the data and metadata, and other engines can read it through the catalog.

CREATE TABLE order_summary USING iceberg
WITH (catalog = 'polaris')
AS SELECT region, sum(amount) AS total FROM orders GROUP BY region;

pg_lake names the table in the catalog after the database, schema and table you used. Here that is the catalog (warehouse) named after the current database, namespace public and table order_summary, so the REST catalog needs a catalog with the same name as the database, and it must allow writes under the server’s location_prefix. pg_lake creates the namespace if it does not exist. The catalog options of attached tables are not accepted, and neither is a server with catalog_name, so that the location of a table in the catalog cannot change after it was created. For the same reason, these tables cannot be renamed or moved to another schema.

pg_lake sends its changes to the catalog after the PostgreSQL transaction commits. If the catalog rejects a change, for example because the location is not allowed, the transaction is still committed in PostgreSQL and you only get a WARNING. Check for warnings when you start using a new catalog server.

Change or remove a catalog server

Most server options can be changed with ALTER SERVER ... OPTIONS, with two exceptions that protect the tables and credentials that use the server:

  • rest_endpoint cannot change while the server has user mappings or tables, since that would send their credentials to a different URL.
  • A catalog server cannot be renamed. Drop it and create a new one instead.

DROP SERVER fails while tables or user mappings depend on the server. DROP SERVER ... CASCADE drops them too, and, like DROP TABLE, deletes the tables pg_lake created from the external catalog. Attached tables are only removed from PostgreSQL.

The object store catalog

With catalog = 'object_store', pg_lake publishes the current metadata location of each table to a catalog file under pg_lake_iceberg.object_store_catalog_location_prefix. The file is rewritten when tables change, and at least every pg_lake_iceberg.object_store_catalog_max_age seconds. Tables that another system publishes under the same prefix can be attached with read_only = true and catalog_table_name, the same way as for REST catalogs. This catalog is intended for managed integrations that exchange tables through object storage.

External Iceberg tables from metadata files

You can query any Iceberg table, whoever wrote it, by creating a pg_lake foreign table that points at one of its metadata files. If the file has uppercase column names, set lowercase_column_names to fold column and nested struct field names to lowercase:

CREATE FOREIGN TABLE external_iceberg ()
SERVER pg_lake
OPTIONS (path 's3://mybucket/table/metadata/v14.metadata.json', lowercase_column_names 'true');

The table is a fixed snapshot: later changes by the writer are not visible until you point it at a newer metadata file, which keeps dependent views and grants in place:

ALTER FOREIGN TABLE external_iceberg
OPTIONS (SET path 's3://mybucket/table/metadata/v15.metadata.json');

When the table is registered in a REST catalog, attaching it with read_only = true (above) avoids this manual step. The same lowercase_column_names option is accepted by CREATE TABLE with load_from or definition_from, and by COPY ... FROM an Iceberg metadata file. See the file formats reference for those forms.

Reading external equality deletes

Read-only external Iceberg v2 tables apply Parquet equality delete files as well as position deletes. Equality keys are Iceberg field IDs, independent of column names or physical column order. Single and composite keys, NULL values, multiple key sets, and duplicate rows are supported. Deletes apply only to older data sequence numbers and matching partition specs and tuples; a delete written with an unpartitioned spec (including a spec containing only void transforms) applies globally. Position deletes retain their existing behavior, including deletes of rows added in the same commit.

For compatibility with writers using PartitionSpec.unpartitioned(), an equality delete with an empty partition tuple applies globally even if its registered spec ID names a partitioned spec. The spec ID must still exist in the table metadata, and the strict data sequence number rule still applies.

Partition matching preserves the distinction between positive and negative floating-point zero while treating all NaNs as equal. It accepts both date and legacy int encodings for day partitions, and decimal precision widening with unchanged scale, including Avro fixed decimal partition values. Avro named type references to earlier partition field definitions are supported.

Equality keys currently support top-level Iceberg int, long, and string fields (integer, bigint, and text). Renames, added nullable keys missing from older data files, and int to long promotion are supported. Dropped keys, nested keys, other key types, and incompatible key type evolution are rejected with an error. These are pg_lake implementation limits, not Iceberg format requirements. Other table columns and partition fields retain their supported types. By default, a delete file missing a declared key is rejected rather than projecting that key as NULL. Incompatible physical key types are also rejected. pg_lake does not write equality delete files.

Both query pushdown and Foreign Scan apply the same deletion rules, including when a query projects only non-key columns or uses count(*). Plain EXPLAIN does not scan delete contents, but may inspect file footers, as for other Parquet scans. Read-query construction validates referenced delete file footers through the existing file access and credential path. When pruning removes all data files, delete files are not opened.

pg_lake_table.enable_equality_delete_validation defaults to on. Set it to off to skip the additional Parquet footer validation when the external writer guarantees valid delete key columns. Equality deletes still apply, and manifest and Iceberg schema validation remain enabled. With this check disabled, a malformed delete file with a missing key can silently produce incorrect results because the reader fills missing columns with NULL. This setting can be changed per session or transaction with SET or SET LOCAL.

Planning indexes partition candidates and groups data files by their exact applicable delete set. Each group uses one anti join per equality key set; groups are combined with a balanced UNION ALL tree. This keeps parser depth logarithmic, but SQL size and execution work still grow with the number of groups and key sets. Global deletes or many deletes in one partition can require checking every data/delete pair. Key-set consolidation also depends on the number of distinct key sets. No fixed scale or linear planning guarantee is implied. A shared delete file can be read by multiple groups.

The existing Deletion Files Scanned EXPLAIN metric counts referenced equality files once per table scan, regardless of how many groups reference them. It does not count physical reader executions or object-storage requests. Footer reads and repeated group reads can cause additional I/O.

Snowflake

Snowflake can query pg_lake’s Iceberg tables in place, without copying the data:

  • Snowflake Postgres instances come with pg_lake, and a Snowflake catalog integration with CATALOG_SOURCE = SNOWFLAKE_POSTGRES reads their Iceberg tables directly. See the Snowflake documentation.
  • Self-managed pg_lake tables can be read through an object storage catalog integration from the table’s metadata file, or through a REST catalog that both systems use.

For tables that Snowflake reads, create them with compatibility_mode = 'snowflake' (or set pg_lake_iceberg.default_compatibility_mode). This stores uuid values nested inside arrays and composite types as strings, which Snowflake requires, while keeping the column type uuid in PostgreSQL.

Conversely, when pg_lake reads tables written by Snowflake, column and nested field names may be uppercase. Set lowercase_column_names = true when attaching the table through a read-only catalog or a metadata file. Lowercase catalog, namespace and table names in a REST catalog resolve to Snowflake’s uppercase names when the lowercase ones do not exist. See lowercase column names from external engines and external metadata files.

The sync use case walks through both setups.