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. Catalog error log
    1. Failed catalog requests
    2. Authentication failures
    3. Vended credentials
    4. Unsupported Iceberg features
  9. Catalog statistics
  10. 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. Its requests carry the header User-Agent: pg_lake/<version> with the major and minor version, such as pg_lake/3.6.

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 pg_lake requests for new writable tables in this catalog. pg_lake adds the database, schema, table name and table OID to it; the catalog may assign a different location.
catalog_name server Catalog (warehouse) that tables use. Without it, pg_lake uses the prefix the catalog advertises, or the database name. A table created by pg_lake keeps the catalog it was created in, even if this option changes later, see below.
warehouse server Warehouse to name when pg_lake asks the catalog for its prefix (GET /v1/config?warehouse=...). Polaris and Lakekeeper need it to advertise a prefix: use the catalog name on Polaris and the warehouse name on Lakekeeper, which returns the warehouse ID. catalog_name still wins if set.
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, or to true when the table is attached with catalog = 'snowflake' and the columns are inferred.

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.

A statement reads one version of the catalog: every reference to a table, every table of one object_store catalog, and every query the statement runs in functions, DO blocks, procedures and triggers reads the same version. Under READ COMMITTED, the next statement reads the version that stands by then, and a cursor, including the one a PL/pgSQL FOR loop opens, reads the version that stands when it opens. A procedure that commits reads the catalog anew after each commit, so a procedure that polls an attached table for new commits should commit between polls. REPEATABLE READ and SERIALIZABLE hold a transaction to the version its first read of an attached table used.

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.

pg_lake reads columns by their Iceberg field IDs. Data files written without field IDs, as added by add_files or left by migrate and snapshot, are read through the table’s name mapping (schema.name-mapping.default): each column is read from the file column that has one of the names the mapping lists for its field ID, and a column the mapping does not cover reads as NULL. If the table has no name mapping, a query that reads a column from such a file fails with the file has no field ids to match its columns by.

Fields nested in a struct are read by their current names instead, in files with or without field IDs. A nested field renamed in the source catalog therefore reads as NULL from the files written before the rename, and a query fails with STRUCT to STRUCT cast must have at least one matching member if no field of the struct kept its name.

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.

pg_lake reads manifests and manifest lists that are uncompressed or compressed with deflate (Iceberg’s default, gzip) or snappy. A table whose manifests are compressed with zstd or bzip2, as PyIceberg writes them when write.avro.compression-codec is set to one of those, cannot be read: queries fail with File header contains an unknown codec.

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 schema and table you used: here namespace public and table order_summary. An explicit table catalog_name takes precedence; otherwise the catalog (warehouse) is the server’s catalog_name, then the prefix the catalog advertises, then the current database name. pg_lake picks it once, when the table is created, and the table keeps it: changing the server’s catalog_name later does not move existing tables, and the table’s own catalog_name cannot be changed. A nondefault prefix is stored in the table’s options. Explicit prefixes are always preserved, including when replaying pg_dump DDL, and do not follow a database rename. Tables without a stored prefix continue to use the database name. Writable tables do not accept catalog_namespace or catalog_table_name overrides and cannot be renamed or moved to another schema. One transaction may modify only one REST server and prefix; rolling back a savepoint discards its pending catalog work and restores the prior binding.

pg_lake creates the namespace if it does not exist, then stages table creation with its schema. When location_prefix (or pg_lake_iceberg.default_location_prefix) is set, the staging request asks for pg_lake’s usual location, the prefix followed by the database, schema and table name and the table OID. The OID keeps a re-created table off a dropped table’s files. If the namespace reports a location and that path is not under it (Polaris names namespace locations after the raw schema name, so a schema name that needs URL-encoding falls outside), the same database/schema/table/OID tail is requested under the namespace location instead. The catalog must return a table UUID and storage location, and pg_lake uses the returned location for both data and metadata. Polaris and Lakekeeper keep the requested location; Nessie assigns its own; a catalog that rejects the location fails the CREATE TABLE. Without a prefix, the catalog chooses the location. An explicit location option, including one in replayed DDL, is requested as given; if the catalog assigns a different location, pg_lake reports it with a NOTICE and uses the catalog’s. As for other Iceberg tables, the chosen location must be empty, so replaying DDL whose old location still holds files fails. Existing tables retain their storage locations.

Storage access for writable tables must be configured directly in pgduck_server: pg_lake does not use catalog-vended credentials for writable REST tables, because their files (including queued deletions) outlive any vended credential. The chosen location must be empty and satisfy the configured managed-storage-region restriction. New writable tables request Iceberg format version 2 and uncompressed metadata JSON, which the current metadata-file reader requires. The catalog must support staged creation and the commit operations used by pg_lake; REST catalog support alone is not sufficient.

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. Manifest min/max bounds can exclude an equality delete from a data file when an integer key’s ranges are strictly disjoint and the delete file has a known zero NULL count for that key. This also works for a component of a composite key and for int to long promotion. Missing, ambiguous or invalid bounds and missing, ambiguous or nonzero delete NULL counts retain the delete file; other key types are not pruned by bounds. Count metric entries with NULL keys or values are rejected as structurally invalid. A delete excluded from every retained data file is not opened or included in footer validation.

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.

Catalog error log

pg_lake writes a line to the PostgreSQL server log, never to the client, when a catalog request fails, when no catalog credential can be obtained, when storage credentials from a catalog cannot be used, and when a table uses something pg_lake cannot handle. Each line is pg_lake_catalog: followed by one of the sentences below. The sentences carry no table, schema, namespace or catalog names, no URLs or paths, no SQL text and no error messages from the catalog, so the lines can be collected in places where the rest of the server log should not go.

LOG:  pg_lake_catalog: could not load a table from the Horizon catalog: HTTP 403 (ForbiddenException)
LOG:  pg_lake_catalog: a table uses Iceberg deletion vectors, which pg_lake does not support

To collect them, match on the prefix. Set pg_lake_iceberg.log_catalog_events to off to turn the lines off.

Where a sentence names the catalog, it is one of:

  • the Horizon catalog: the built-in rest catalog when pg_lake_iceberg.rest_catalog_auth_type is horizon.
  • the built-in REST catalog: the built-in rest catalog with any other authentication.
  • a catalog server: a catalog defined with CREATE SERVER.

Failed catalog requests

could not <request> <catalog>: HTTP <status> (<error type>)
could not <request> <catalog>: HTTP <status>
could not <request> <catalog>: <network problem>

<request> is one of get the configuration of, get an access token from, list namespaces in, look up a namespace in, create a namespace in, load a table from and create a table in, or, for the changes sent to the catalog after a transaction commits, publish a new table to, publish a table drop to and publish committed changes to. A failed publish means PostgreSQL committed a change that the catalog does not have.

The error type is the Iceberg exception type or OAuth2 error code the catalog answered with. It is left out when the response names none, or one pg_lake does not know. When no response arrived, the network problem is one of host not found, connection failed, timed out, TLS error, connection lost, out of memory or network error.

A request is logged once its retries are over. A 404 is not logged, because pg_lake looks up namespaces and tables that may not exist as part of creating and attaching tables, except when publishing a new table or committed changes: there it means the catalog lost the namespace or table. A request interrupted by a cancel is not logged either.

Authentication failures

could not authenticate to <catalog> (SQLSTATE <code>)

No credential could be obtained for the catalog, either from pg_lake’s own OAuth2 token request or from a credential provider supplied by another extension.

Vended credentials

<catalog> returned no vended credentials for a table
ignoring vended credentials for <storage> from <catalog> that pg_lake cannot use
ignoring a vended Azure endpoint that is not allowed
could not resolve storage credentials for a table (SQLSTATE <code>)

The first is logged when a table asks its catalog for vended credentials and gets none. <storage> is S3, Azure Data Lake Storage, Google Cloud Storage or an unrecognized kind of storage. An Azure endpoint is not allowed when its host is not under pg_lake.allowed_azure_host_suffixes or is not the storage account’s own. Each of these three is logged once per session for each table or endpoint. The last is logged when resolving the credentials a query needs fails.

Unsupported Iceberg features

a table uses Iceberg deletion vectors, which pg_lake does not support
a table uses Iceberg column default values, which pg_lake does not support
a table uses Iceberg files in a format other than Parquet, which pg_lake does not support
a table uses Iceberg format version <version>, which pg_lake cannot read
a table uses Iceberg format version <version>, which pg_lake cannot write
a table uses the Iceberg type <type>, which pg_lake does not support
a table uses an Iceberg type pg_lake does not support

<type> is geography, geometry, timestamp_ns, timestamptz_ns, unknown or variant; any other type gets the last sentence. Each is logged along with the error that refuses the table.

Catalog statistics

lake_iceberg.catalog_stats() returns counters of REST catalog activity for the whole server since it started, one row for each counter that is not zero:

SELECT * FROM lake_iceberg.catalog_stats();
        metric        | catalog |   operation   |    result    | value
----------------------+---------+---------------+--------------+--------
 requests             | horizon | load_table    | ok           |    412
 requests             | horizon | load_table    | client_error |      3
 request_microseconds | horizon | load_table    |              | 928114
 retries              | horizon | load_table    | rate_limited |      1
 vended_credentials   | horizon |               | vended       |    412
 table_scans          | horizon |               |              |    530

Like the catalog error log, the counters name no catalog, namespace or table. catalog is horizon for the built-in rest catalog when pg_lake_iceberg.rest_catalog_auth_type is horizon, builtin for the built-in rest catalog with any other authentication, and user for catalogs defined with CREATE SERVER. The metrics are:

  • requests: requests sent to the catalog, by operation and by result: ok, not_found, client_error (other 4xx), server_error (5xx) or network_error (no response). A request counts once, after its retries. Requests interrupted by a cancel are not counted.
  • request_microseconds: time spent on those requests, retries included. When a retry needs a fresh token, the time includes getting it, which pg_lake’s own OAuth2 login also counts under token. If getting the token fails, the request is not counted, and the failure shows in auth_failures.
  • retries: requests sent again, by operation and by result: rate_limited (HTTP 429), unavailable (HTTP 503) or auth_refresh (an expired credential was refreshed).
  • auth_failures: times no credential could be obtained for the catalog.
  • vended_credentials: table loads and creates that asked the catalog for vended credentials, by result: vended (at least one credential pg_lake can use), unusable (only credentials pg_lake cannot use) or none.
  • table_scans: scans of read-only tables from a REST catalog that queries ran, one for each time a query reads a table. EXPLAIN without ANALYZE is not counted. Scans can outnumber load_table requests, because pg_lake asks the catalog for a table at most once per statement, or once per transaction under REPEATABLE READ and SERIALIZABLE.

operation is one of config, token, list_namespaces, get_namespace, create_namespace, load_table, create_table, publish_create_table, publish_drop_table and publish_commit. The counters are kept only when pg_lake_iceberg is loaded at server start, which pg_extension_base arranges; otherwise the function returns no rows. They reset when the server restarts. Calling the function requires membership in pg_read_all_stats.

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.