Create and Manage Apache Iceberg Tables

This document describes how to create, query, and manage Apache Iceberg tables in SynxDB. Apache Iceberg is an open table format widely used to build data lakes on object storage (such as Amazon S3). The datalake_fdw extension lets SynxDB manage Iceberg tables as native relations rather than foreign tables, so you can use standard SQL — CREATE, SELECT, INSERT, UPDATE, DELETE, and VACUUM — directly against them. Iceberg data files stay on the underlying object storage; SynxDB reads and writes them through the catalog and volume configured below.

This document covers only Iceberg tables. To query file-based external data (Parquet, ORC, Avro, Text, CSV) on object storage or HDFS, see Load Data from Object Storage and HDFS. To synchronize non-Iceberg tables from Hive Metastore, see Load Data from Hive.

Overview

Key capabilities

Manage Iceberg tables as SynxDB native relations, not foreign tables; all operations run through the native executor.
Support full DML on Iceberg tables: INSERT, UPDATE, and DELETE use Iceberg v2 merge-on-read semantics.
Compact data files through VACUUM.
Read Iceberg tables whose schema was evolved by external engines (Spark, Trino, or Flink) without rewriting old data files.
Prune columns automatically to reduce I/O, reading only the columns a query references.
Support multiple catalog backends: builtin, Apache Polaris, Hadoop, and S3.
Support S3-compatible object stores and HDFS as the storage backend.

When to use this

You host a data lake on S3 in Iceberg format and want to query and update it from SynxDB using SQL.
You share Iceberg tables across engines (Spark, Trino, Flink) and need SynxDB as a participating engine.
You need ACID writes to Iceberg from SynxDB rather than read-only access.
You want a single SQL surface that covers both native MPP tables and lake-format tables.

For purely read-only access to file-based data, the FDW external table flow described in the OSS/HDFS document is simpler and does not require an Iceberg catalog.

Three-layer architecture

To access and manage Iceberg tables, SynxDB uses a three-layer model. Iceberg data lives on object storage. SynxDB connects to the Iceberg metadata service through a FOREIGN CATALOG, connects to the underlying storage through a FOREIGN VOLUME, and then binds the two as an ICEBERG TABLE — a native relation visible inside the database. Each layer is configured separately, and the three are combined when you create an Iceberg table.

Layer	Database object	Role	Available types
Metadata	`FOREIGN CATALOG`	Resolves Iceberg metadata through `iceberg_catalog_fdw`	builtin / polaris / hadoop / s3
Storage	`FOREIGN VOLUME`	Accesses physical storage through `iceberg_volume_fdw`	s3 / hdfs
Data table	`ICEBERG TABLE`	Binds the metadata and storage layers as a native SynxDB relation	Table access method `iceberg`

Catalogs and volumes are reusable: many Iceberg tables can share the same catalog and volume. After you set the session-level defaults (iceberg_default_catalog, iceberg_default_volume), subsequent CREATE ICEBERG TABLE statements can omit the catalog and volume clauses.

Workflow

Before you can create and manage Iceberg tables in SynxDB, you must complete the catalog and volume setup. The full procedure has five steps:

Step	Goal	Section
1	Install the extension	Step 1. Install the extension
2	Configure a foreign catalog	Step 2. Configure a foreign catalog
3	Configure a foreign volume	Step 3. Configure a foreign volume
4	Create an Iceberg table	Step 4. Create an Iceberg table
5	Query and maintain Iceberg tables	Step 5. Query and maintain Iceberg tables

Steps 1 through 3 are typically configured once per cluster. Step 4 and Step 5 apply to each new Iceberg table.

For a single runnable walkthrough that touches every step, see End-to-end example: S3 with builtin catalog. For option tables, supported data types, limitations, and error messages, see Reference.

Step 1. Install the extension

This step loads the datalake_fdw extension into your database and registers the Iceberg-related FDWs and table access method.

CREATE EXTENSION IF NOT EXISTS datalake_fdw;

Installing datalake_fdw registers the iceberg_catalog_fdw and iceberg_volume_fdw foreign data wrappers and the iceberg table access method. Using a Hive Metastore as the Iceberg catalog does not require installing hive_connector — that extension is only for synchronizing non-Iceberg tables in Load Data from Hive.

Step 2. Configure a foreign catalog

This step creates the connection objects that let SynxDB reach an Iceberg metadata service. You create one SERVER, one USER MAPPING, and one FOREIGN CATALOG object. The FOREIGN CATALOG is the database object that represents the Iceberg metadata source inside SynxDB.

Choose the catalog type that matches your existing metadata service:

Type	When to use
`builtin`	Self-contained SynxDB-managed metadata. Good for testing or simple deployments.
`hive`	Hive Metastore manages Iceberg table metadata. Good for environments that already run a Hive Metastore.
`polaris`	Apache Polaris REST catalog provides centralized metadata.
`hadoop`	File-system-based catalog (Iceberg `HadoopCatalog`). Good for shared S3 warehouses.
`s3`	S3-only variant of `hadoop` with vended-credentials support.

For the compatibility matrix between catalog and volume types, see Catalog and volume compatibility in the reference.

Use the builtin catalog

CREATE SERVER my_cat_srv FOREIGN DATA WRAPPER iceberg_catalog_fdw;
CREATE USER MAPPING FOR current_user SERVER my_cat_srv;
CREATE FOREIGN CATALOG my_catalog SERVER my_cat_srv;

Use a Hive Metastore catalog

The Hive catalog stores Iceberg table metadata in an external Hive Metastore service.

Note

Point warehouse_location_prefix at an HDFS location. A hive catalog cannot reach an S3 backend in this release: the bare s3:// scheme is unregistered, and s3a:// fails because the catalog agent never registers the S3A file system, so creating a table reports a missing S3AFileSystem class.

CREATE SERVER hive_cat_srv FOREIGN DATA WRAPPER iceberg_catalog_fdw
OPTIONS (
    type 'hive',
    url 'thrift://hive-metastore:9083'
);
CREATE USER MAPPING FOR current_user SERVER hive_cat_srv;
CREATE FOREIGN CATALOG hive_catalog SERVER hive_cat_srv
OPTIONS (
    catalog_name 'hive_location',
    default_namespace 'default',
    warehouse_location_prefix 'hdfs://mycluster/warehouse/hive/'
);

Hive catalog user mapping options:

Option	Description
`username`	OS user
`auth_method`	Authentication method (`simple` or `kerberos`)
`krb_service_principal`	Hive service principal (Kerberos)
`krb_client_principal`	Client principal (Kerberos)
`krb_client_keytab`	Keytab file path (Kerberos)

Use a Polaris catalog

Use Apache Polaris as a centralized REST-based catalog.

Note

A Polaris catalog still requires a foreign volume. Even though Polaris returns the table location, SynxDB reads and writes data files through the volume’s own endpoint and credentials, so you must complete Step 3 and keep the VOLUME clause (or iceberg_default_volume) when creating the table.

CREATE SERVER polaris_cat_srv FOREIGN DATA WRAPPER iceberg_catalog_fdw
OPTIONS (
    type 'polaris',
    url 'http://polaris:8181/api/catalog',
    polaris_server_realm 'default'      -- omit to use the POLARIS realm
);
CREATE USER MAPPING FOR current_user SERVER polaris_cat_srv
OPTIONS (
    client_id 'my_client_id',
    client_secret 'my_client_secret',
    scope 'PRINCIPAL_ROLE:ALL'
);
CREATE FOREIGN CATALOG polaris_catalog SERVER polaris_cat_srv
OPTIONS (
    catalog_name 'production',
    default_namespace 'public',
    enable_metadata_cache 'true',
    metadata_cache_ttl '300'
);

Set polaris_server_realm to the realm your Polaris server is configured with. When you leave it out, SynxDB sends the POLARIS realm. A mismatched realm makes the server reject the OAuth token request, and CREATE FOREIGN CATALOG fails with 404 MissingOrInvalidRealm.

CREATE FOREIGN CATALOG attaches to catalog_name on the Polaris server, creating it there if it does not exist. Creation takes the new catalog’s storage configuration from iceberg_default_volume, so set that first or the statement fails with storageConfig cannot be null or empty for catalog creation. The new catalog claims the volume server’s whole bucket rather than the volume’s base_path, so Polaris rejects a second catalog on the same bucket with One or more of its locations overlaps with an existing catalog. To put several catalogs on one bucket, create them through the Polaris management API, giving each catalog its own location, then attach by name.

Use a Hadoop catalog

The Hadoop catalog maintains Iceberg metadata directly in the storage file system. It requires no external metadata service.

-- Hadoop catalog backed by S3-compatible OSS storage
CREATE SERVER hd_cat_srv FOREIGN DATA WRAPPER iceberg_catalog_fdw
    OPTIONS (type 'hadoop');
CREATE USER MAPPING FOR current_user SERVER hd_cat_srv;
CREATE FOREIGN CATALOG hd_cat SERVER hd_cat_srv
    OPTIONS (warehouse_location_prefix 's3a://warehouse/iceberg/');

Notes:

warehouse_location_prefix must include the protocol (s3a://). The option is not validated at CREATE FOREIGN CATALOG time, but omitting it causes errors when you create or access an Iceberg table through this catalog.
A table’s metadata.json and data files live under <warehouse_location_prefix>/<namespace>/<table>/.
Tables created here can be read by Spark, Trino, or Flink through Iceberg HadoopCatalog.

Use an S3 catalog

The S3 catalog is a specialization of the Hadoop catalog. It wraps the storage configuration as Iceberg IcebergS3Catalog, splitting warehouse_location_prefix into fs.defaultFS and fs.prefix. This is the option to choose when you need S3 vended credentials or separate internal/external endpoints. The companion volume must use type='s3'.

CREATE SERVER s3_cat_srv FOREIGN DATA WRAPPER iceberg_catalog_fdw
    OPTIONS (type 's3');
CREATE USER MAPPING FOR current_user SERVER s3_cat_srv;
CREATE FOREIGN CATALOG s3_cat SERVER s3_cat_srv
    OPTIONS (warehouse_location_prefix 's3a://warehouse/iceberg_s3/');

How the namespace is resolved

Catalog types that maintain a namespace directory (hive, hadoop, and polaris) need a namespace for every table. SynxDB resolves it from two sources, in this order:

The table’s own OPTIONS (namespace '...') in CREATE ICEBERG TABLE
The catalog’s OPTIONS (default_namespace '...') in CREATE FOREIGN CATALOG

Give every table a namespace: set namespace on the table, or default_namespace on the catalog. Setting default_namespace on the catalog spares you from repeating the namespace in every CREATE ICEBERG TABLE, and an individual table can still override it.

When the resolved namespace has no matching database in the underlying catalog, the error reports the precedence and every way to correct it:

org.apache.iceberg.exceptions.NoSuchNamespaceException: Iceberg catalog (hive) has
no database matching namespace "no_such_namespace_xyz". Resolution precedence is:
(1) CREATE ICEBERG TABLE ... OPTIONS (namespace '<ns>') [per-table override],
(2) CREATE FOREIGN CATALOG ... OPTIONS (default_namespace '<ns>') [per-catalog
default], (3) PostgreSQL schema name [fallback]. Either CREATE the namespace in the
underlying catalog (e.g. via spark-sql / beeline), or set OPTIONS namespace on the
table, or set OPTIONS default_namespace on the foreign catalog, or move the table
into a PostgreSQL schema whose name matches an existing namespace.

SynxDB does not create namespaces in an external catalog. Create the database there first, through the catalog’s own tooling.

The builtin catalog has no queryable namespace directory, so these rules do not apply to it: omit the namespace and table options and let the catalog manage table locations.

Step 3. Configure a foreign volume

This step creates the connection objects that let SynxDB reach the underlying object storage. You create the corresponding SERVER, USER MAPPING, and FOREIGN VOLUME objects. The FOREIGN VOLUME points to the physical storage that holds Iceberg data files.

Configure an S3 or S3-compatible OSS volume

CREATE SERVER s3_vol_srv FOREIGN DATA WRAPPER iceberg_volume_fdw
OPTIONS (
    type 's3',
    endpoint 'http://oss-endpoint:9000',
    region 'us-east-1',
    bucket_name 'warehouse',
    path_style_access 'true'        -- required by many S3-compatible OSS implementations
);
CREATE USER MAPPING FOR current_user SERVER s3_vol_srv
OPTIONS (access_key_id 'AKIAEXAMPLE', secret_access_key 'SECRET');
CREATE FOREIGN VOLUME my_volume SERVER s3_vol_srv
OPTIONS (base_path '/warehouse/', allow_writes 'true');

Configure an HDFS volume

Important

Supply the NameNode address through the SERVER options shown below.

Do not use an endpoint option for HDFS. It applies only to object storage (S3 or S3-compatible OSS).

For a single NameNode with simple authentication:

CREATE SERVER hdfs_vol_srv FOREIGN DATA WRAPPER iceberg_volume_fdw
OPTIONS (
    type 'hdfs',
    hdfs_namenodes '192.168.1.10:9000',
    hdfs_auth_method 'simple',
    hadoop_rpc_protection 'authentication'
);
CREATE USER MAPPING FOR current_user SERVER hdfs_vol_srv
OPTIONS (username 'gpadmin');
CREATE FOREIGN VOLUME hdfs_vol SERVER hdfs_vol_srv
OPTIONS (base_path '/iceberg-warehouse/', allow_writes 'true');

For HA HDFS with Kerberos:

CREATE SERVER hdfs_ha_vol_srv FOREIGN DATA WRAPPER iceberg_volume_fdw
OPTIONS (
    type 'hdfs',
    hdfs_namenodes 'mycluster',
    hdfs_auth_method 'kerberos',
    krb_principal 'gpadmin/master@REALM.COM',
    krb_principal_keytab '/home/gpadmin/gpadmin.keytab',
    krb_service_principal 'hdfs/namenode@REALM.COM',
    hadoop_rpc_protection 'privacy',
    is_ha_supported 'true',
    dfs_nameservices 'mycluster',
    dfs_ha_namenodes 'nn1,nn2',
    dfs_namenode_rpc_address '192.168.1.10:9000,192.168.1.11:9000',
    dfs_client_failover_proxy_provider
        'org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);
CREATE USER MAPPING FOR current_user SERVER hdfs_ha_vol_srv
OPTIONS (username 'gpadmin');

Step 4. Create an Iceberg table

This step creates an Iceberg table bound to the catalog and volume you configured. After the table exists, you can run standard SQL against it.

Set session defaults to avoid repeating the catalog and volume in every CREATE ICEBERG TABLE:

SET iceberg_default_catalog = 'my_catalog';
SET iceberg_default_volume  = 'my_volume';

Then create the table:

CREATE ICEBERG TABLE orders (
    id         BIGINT NOT NULL,
    product    VARCHAR(100),
    region     TEXT,
    amount     DECIMAL(15,2) NOT NULL,
    order_date DATE NOT NULL
);

To override the defaults for a single table, name the catalog and volume explicitly and pass OPTIONS:

CREATE ICEBERG TABLE sales (
    id BIGINT, region TEXT, amount NUMERIC(12,2)
)
CATALOG hive_catalog
VOLUME s3_volume
OPTIONS (
    namespace 'analytics',
    table 'sales_2024'
);

Catalog and volume specification rules:

Both can be omitted if session defaults (iceberg_default_catalog, iceberg_default_volume) are set.
All catalog types, including Polaris, require a volume (see the note in Step 2).
The namespace and table options attach the table to an existing entry in an external catalog. Only hive, hadoop, and polaris catalogs support this mapping. The builtin catalog does not maintain a queryable (namespace, table) directory; use it without namespace/table and let it create and manage the table (its location is generated under the volume’s base_path). Supplying namespace/table with a builtin catalog fails with metadataLocation is required for builtin catalog.
Omitting namespace falls back to the catalog’s default_namespace. See How the namespace is resolved for the precedence.

For the full option list, see CREATE ICEBERG TABLE options in the reference.

For supported column types, see Supported data types.

PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are rejected at CREATE ICEBERG TABLE time because Iceberg tables are always distributed RANDOMLY, which is incompatible with key-based constraints. CHECK constraints are accepted and enforced at runtime, but are not propagated to Iceberg metadata.

Iceberg tables are always distributed RANDOMLY because data fragments live on object storage rather than being hash-partitioned across segments. Specifying DISTRIBUTED REPLICATED or DISTRIBUTED BY (...) produces a warning and SynxDB ignores the clause. Running ALTER TABLE ... SET DISTRIBUTED BY on an Iceberg table is rejected with an error.

Step 5. Query and maintain Iceberg tables

After the previous four steps, you can operate on Iceberg tables like any other table in SynxDB. This section groups the day-to-day tasks:

Run DML statements
Evolve the schema
Compact tables with VACUUM
Column projection and filtering

Run DML statements

INSERT INTO orders VALUES (1, 'Widget', 'US-West', 99.99, '2024-01-15');
INSERT INTO orders SELECT * FROM staging_orders;

UPDATE orders SET amount = amount * 1.1 WHERE region = 'US-West';

DELETE FROM orders WHERE order_date < '2023-01-01';

SELECT region, SUM(amount) FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region;

UPDATE and DELETE use Iceberg v2 position-delete (merge-on-read). A single statement does not rewrite data files; it produces position-delete records that an anti-join eliminates during reads. VACUUM compaction reduces position-delete accumulation.

Empty a table with TRUNCATE

TRUNCATE empties an Iceberg table managed by the builtin catalog:

TRUNCATE orders;

The statement writes a new, empty snapshot and queues the table’s previous data and metadata files for deletion; the background consumer then removes them from the object storage. Points worth knowing:

It is transactional: rolling it back leaves the rows and data files unchanged. It does leave one unreferenced metadata.json, manifest, and snapshot list on the storage, which the deletion queue does not cover; remove them with an external orphan-file tool.
Running it on an already empty table does nothing and reports no error.

SynxDB rejects it for tables on an external catalog, because that catalog owns the table’s storage:

ERROR:  TRUNCATE is only supported for builtin-catalog iceberg tables
DETAIL:  table "ext_trunc" is on an external catalog that owns its storage

To empty such a table, use DELETE FROM <table>.

Evolve the schema

ALTER TABLE is not supported on Iceberg tables. Any ALTER TABLE statement — including ADD COLUMN, DROP COLUMN, and SET DISTRIBUTED BY — is rejected with the error ALTER TABLE is not supported on Iceberg tables. RENAME COLUMN is rejected with the error RENAME COLUMN is not supported on Iceberg tables.

To evolve the schema of an Iceberg table, use an external engine (Spark, Trino, or Flink) to perform the Iceberg-side schema change, then drop and recreate the SynxDB Iceberg table definition to pick up the new schema.

Compact tables with VACUUM

VACUUM triggers Iceberg data file compaction.

VACUUM orders;

Tune compaction thresholds at the session level:

SET datalake.iceberg_vacuum_compact_min_input_files = 5;
SET datalake.iceberg_vacuum_rewrite_target_file_size_mb = 256;
VACUUM orders;

VACUUM cannot run inside a transaction block or a PL/pgSQL function.

After compacting a table managed by the builtin catalog, VACUUM also reclaims the storage the rewritten files occupied: each file that compaction replaced is queued for deletion and removed from the object storage in the background. Row counts and query results are unaffected. Because the queueing is transactional, an aborted VACUUM leaves the original files in place. For tables on an external catalog, compaction runs but the superseded files stay, because the external catalog owns that storage.

VACUUM performs compaction and reclaims the files it rewrote. Snapshot expiration and stale metadata cleanup are handled separately (controlled by datalake.iceberg_max_snapshot_age and datalake.iceberg_max_file_removals_per_vacuum) and by external tools. See VACUUM scope for details.

Enable autovacuum

Autovacuum is enabled by default (datalake.iceberg_autovacuum = on) and checks tables every 10 minutes. Adjust the interval or disable autovacuum (requires superuser; reload required):

ALTER SYSTEM SET datalake.iceberg_autovacuum_naptime = 1200;  -- check every 20 minutes
ALTER SYSTEM SET datalake.iceberg_autovacuum = off;           -- disable if needed
SELECT pg_reload_conf();

Column projection and filtering

When you query an Iceberg table, the plan reads only the columns referenced by the query and shows the query filter on the Custom Scan (Iceberg Scan) node. Use EXPLAIN to inspect the plan:

EXPLAIN SELECT * FROM orders WHERE id = 12345;
-- "Filter: (id = 12345)" appears on the Iceberg Scan node.

Before it reads a data file, the scan prunes by the statistics of that file: it skips a data file entirely, reading none of it, when the value range of the predicate column does not overlap the predicate. Both equality and range predicates take part in pruning.

To observe how much a query prunes, compare the rows read by the scan node in EXPLAIN ANALYZE:

-- A predicate that statistics cannot serve reads the whole table.
EXPLAIN (ANALYZE) SELECT count(*) FROM orders WHERE note IS NOT NULL;
--   Custom Scan (Iceberg Scan) on orders (actual rows=500000 ...)

-- An equality predicate on a column loaded in increasing batches reads only the files that can match.
EXPLAIN (ANALYZE) SELECT count(*) FROM orders WHERE id = 350000 AND note IS NOT NULL;
--   Custom Scan (Iceberg Scan) on orders (actual rows=1 ...)
--   Rows Removed by Filter: 99999

When the predicate overlaps the value range of no data file, the scan skips every file, and Rows Removed by Filter no longer appears in EXPLAIN ANALYZE.

How much a query prunes depends on the physical layout of the data. Pruning works best when writes cluster on the predicate column, for example when you load data in batches by time or by an increasing ID, because the value ranges of the files do not overlap. When every file covers the full value range of the column, no pruning is possible.

Clean up objects

Drop dependent objects in the reverse order of creation:

DROP TABLE my_iceberg_table;
DROP VOLUME my_volume;
DROP USER MAPPING FOR current_user SERVER vol_server;
DROP SERVER vol_server;
DROP CATALOG my_catalog;
DROP USER MAPPING FOR current_user SERVER cat_server;
DROP SERVER cat_server;

To drop dependent objects in one command, use CASCADE:

DROP SERVER cat_server CASCADE;

See Object dependency order for the full ordering.

What happens to the data files

Dropping a table managed by the builtin catalog also removes its data and metadata files from the object storage. DROP TABLE records the table’s metadata tree in a deletion queue as part of the transaction, and a background consumer driven by autovacuum deletes the files it lists. The files therefore disappear shortly after the drop commits rather than during the statement itself, and a rolled-back DROP TABLE deletes nothing.

Dropping a table bound to an external catalog (one created with OPTIONS (namespace ..., table ...)) removes only the SynxDB table definition. The files stay, because the external catalog owns that storage and other engines might still reference them.

Tune the background consumer with these parameters. All four require a configuration reload: change them with gpconfig and then run gpstop -u.

Parameter	Default	Description
`datalake_fdw.deletion_queue_enabled`	`on`	Whether the consumer processes the queue. Turning it off leaves queued files on the storage; the consumer processes them after you turn it back on
`datalake_fdw.deletion_queue_min_interval`	`60`	Minimum seconds between two consumer runs
`datalake_fdw.deletion_queue_batch_size`	`100`	Maximum queue entries processed per run
`datalake_fdw.deletion_queue_max_retry`	`5`	Attempts per entry before it is set aside as failed

Entries that keep failing (for example, because the storage credentials no longer work) are retried up to datalake_fdw.deletion_queue_max_retry times and then set aside, so one unreachable file cannot block the rest of the queue. Because deletion depends on autovacuum, keep autovacuum enabled on databases holding Iceberg tables.

Rolled-back transactions are handled too: SynxDB removes the staging data files that an aborted INSERT, UPDATE, or DELETE had already written, so a failed statement leaves no unreferenced files behind.

End-to-end example: S3 with builtin catalog

The following script walks through every workflow step against an S3-compatible OSS endpoint, using the builtin catalog.

-- 1. Extension
CREATE EXTENSION IF NOT EXISTS datalake_fdw;

-- 2. Catalog (builtin, no external dependency)
CREATE SERVER cat FOREIGN DATA WRAPPER iceberg_catalog_fdw;
CREATE USER MAPPING FOR current_user SERVER cat;
CREATE FOREIGN CATALOG my_cat SERVER cat;
SET iceberg_default_catalog = 'my_cat';

-- 3. Volume (S3 / S3-compatible OSS)
CREATE SERVER vol FOREIGN DATA WRAPPER iceberg_volume_fdw
OPTIONS (type 's3', endpoint 'http://oss-endpoint:9000', region 'us-east-1',
         bucket_name 'warehouse', path_style_access 'true');
CREATE USER MAPPING FOR current_user SERVER vol
OPTIONS (access_key_id 'admin', secret_access_key 'password');
CREATE FOREIGN VOLUME my_vol SERVER vol OPTIONS (base_path '/data/');
SET iceberg_default_volume = 'my_vol';

-- 4. Create and use the table
CREATE ICEBERG TABLE users (id int, name text, score numeric(10,2));
INSERT INTO users VALUES (1, 'Alice', 95.5), (2, 'Bob', 87.0);
SELECT * FROM users WHERE score > 90;
UPDATE users SET score = 96.0 WHERE id = 1;
VACUUM users;
DROP TABLE users;

-- 5. Clean up
DROP VOLUME my_vol;
DROP USER MAPPING FOR current_user SERVER vol;
DROP SERVER vol;
DROP CATALOG my_cat;
DROP USER MAPPING FOR current_user SERVER cat;
DROP SERVER cat;

Reference

This section consolidates option tables, type mappings, dependency rules, limitations, and error messages.

Catalog and volume compatibility

Catalog `type`	Compatible volume `type`	Notes
`builtin`	`s3`, `hdfs`	Embedded metadata service
`hive`	`s3`, `hdfs`	Metadata managed by Hive Metastore
`polaris`	`s3`, `hdfs`	A volume is required (see the note in Step 2)
`hadoop`	`s3`, `hdfs`	`warehouse_location_prefix` required and must include protocol
`s3`	`s3`	Object storage only

Catalog options

Option	Where	Description
`type`	SERVER	`builtin` / `hive` / `polaris` / `hadoop` / `s3`
`url`	SERVER (hive, polaris)	Hive: `thrift://host:port`; Polaris: REST address
`polaris_server_realm`	SERVER (polaris)	Realm sent to the Polaris server (default `POLARIS`)
`catalog_name`	FOREIGN CATALOG (hive, polaris)	Remote catalog name
`default_namespace`	FOREIGN CATALOG	Namespace used by tables in this catalog that do not set their own. See How the namespace is resolved
`enable_metadata_cache`	FOREIGN CATALOG	Enable metadata cache
`metadata_cache_ttl`	FOREIGN CATALOG	Cache TTL in seconds
`auto_refresh_metadata`	FOREIGN CATALOG	Auto-refresh metadata
`warehouse_location_prefix`	FOREIGN CATALOG (polaris, hadoop, s3)	Warehouse path prefix (must include protocol). Not validated at creation time, but required for `hadoop` and `s3` catalogs when creating or accessing Iceberg tables

Volume options

iceberg_volume_fdw accepts its options without checking them: an unknown option name or an out-of-range value goes through silently and only surfaces later as a failed connection. Check names and values against the tables below.

Server options:

Option	Applies to	Description
`type`	All	`s3` or `hdfs`
`endpoint`	s3	Object storage endpoint (with protocol)
`region`	s3	Region
`bucket_name`	s3	Bucket name
`path_style_access`	s3	Path-style access (required by many S3-compatible OSS implementations)
`endpoint_internal`	s3 (Polaris)	Internal endpoint for Polaris-side access
`sts_endpoint` / `sts_unavailable`	s3 (Polaris)	STS endpoint and disable switch
`role_arn` / `external_id` / `user_arn`	s3 (Polaris)	Vended-credentials role information
`current_kms_key` / `allowed_kms_keys`	s3 (Polaris)	SSE-KMS keys
`hdfs_namenodes`	hdfs	NameNode address. Supports `host:port` format. In HA mode, set to the NameService name (without port)
`hdfs_port`	hdfs	NameNode RPC port (default 8020; not needed when using `host:port` format or HA mode)
`hdfs_auth_method`	hdfs	`simple` or `kerberos`
`krb_principal` / `krb_principal_keytab`	hdfs (Kerberos)	Client Kerberos credentials
`krb_service_principal`	hdfs (Kerberos)	NameNode service principal
`hadoop_rpc_protection`	hdfs	Quality of protection for the RPC connection: `authentication`, `integrity`, or `privacy`. Must match `hadoop.rpc.protection` in the cluster’s `core-site.xml`
`data_transfer_protocol`	hdfs	Enable SASL data transfer (`true` / `false`)
`data_transfer_protection`	hdfs	Quality of protection for DataNode data transfer: `authentication`, `integrity`, or `privacy`. Must match `dfs.data.transfer.protection` in the cluster’s `hdfs-site.xml`. Left unset, the client negotiates the level with the DataNode. Takes effect only when `hdfs_auth_method` is `kerberos`
`is_ha_supported`	hdfs	Enable HA
`dfs_nameservices` / `dfs_ha_namenodes` / `dfs_namenode_rpc_address` / `dfs_client_failover_proxy_provider`	hdfs (HA)	HA routing configuration
`dfs_client_use_datanode_hostname`	hdfs	Connect to DataNodes by hostname rather than IP address (`true` / `false`). Set it to `true` when the DataNodes register hostnames that the SynxDB hosts resolve

User mapping options:

Option	Applies to	Description
`username`	All	OS user
`access_key_id` / `secret_access_key`	s3	Static AK/SK

CREATE FOREIGN VOLUME options:

Option	Description
`base_path`	Storage base path
`allow_writes`	Allow write operations (default `false`). Parsed but not enforced in the current version
`enable_caching`	Enable caching (default `false`). Parsed but not enforced in the current version

`CREATE ICEBERG TABLE` options

Option	Type	Description
`catalog`	string	Catalog name (equivalent to the `CATALOG` clause; specify only one)
`namespace`	string	Namespace. See How the namespace is resolved
`table`	string	Table name registered in the catalog (defaults to the SynxDB table name)
`location`	string	Not settable. Records the table’s data directory after creation: the volume’s `base_path` for a `builtin` table, or the location the external catalog assigns
`compression`	string	Codec for Parquet writes: `zstd` (default), `snappy`, `gzip`, `lz4`, or `uncompress`
`compression_level`	int	Level for codecs that support one: `zstd` accepts 1–22, `gzip` accepts 1–9. Omit it to use the codec’s own default
`autovacuum_enabled`	bool	Whether the table participates in `datalake.iceberg_autovacuum` (default `true`)

SynxDB silently ignores unrecognized options. A misspelled option name (for example, table_name instead of table, or base_location instead of location) produces no error but has no effect.

SynxDB validates compression and compression_level when you write to the table rather than at CREATE ICEBERG TABLE time, so an unusable combination surfaces as an INSERT error:

ERROR:  invalid iceberg write compression "brotli"
HINT:  supported parquet codecs are uncompress, snappy, gzip, zstd, lz4

ERROR:  compression codec "snappy" does not support a compression level

ERROR:  zstd compression level 99 out of range
HINT:  valid zstd levels are 1..22

Both options are fixed after you create the table: Iceberg tables reject ALTER TABLE, so switching codec or level means recreating the table.

For a table managed by the builtin catalog, the files are laid out directly under the volume’s base_path:

<protocol>://<bucket>/<base_path>/metadata/...
<protocol>://<bucket>/<base_path>/data/...

All builtin tables on the same volume therefore share one data and one metadata directory. File names carry UUIDs, and each table’s metadata location is recorded in the catalog, so tables stay isolated without relying on separate directories. Two consequences:

You cannot choose the path: passing location to a builtin table fails with location option is not allowed for builtin iceberg tables. On an external catalog the option is accepted but has no effect, because that catalog assigns the location.
Avoid maintenance tools that operate on a directory prefix, such as an Iceberg remove-orphan-files action pointed at the shared directory. Those tools cannot tell which files belong to which table and might delete live data of a different table.

Tables created earlier keep the path recorded at their creation time, so the two layouts coexist and no migration is needed. Tables on an external catalog are unaffected: that catalog assigns their locations.

Supported data types

SynxDB type	Iceberg type	Notes
`boolean`	`boolean`
`smallint`	`int`
`integer`	`int`
`bigint`	`long`
`real`	`float`
`double precision`	`double`
`decimal(p,s)` / `numeric(p,s)`	`decimal(p,s)`	Precision is required
`text` / `varchar(n)`	`string`
`char(n)` / `bpchar`	`string`	Iceberg has no fixed-length character type. Trailing spaces and right-padding are not preserved
`date`	`date`
`timestamp`	`timestamp`	Without time zone
`timestamptz`	`timestamp`	Stored as a timezone-less timestamp in Iceberg metadata. SynxDB reads and writes values correctly, but external engines (Spark, Trino, Flink) see the column as a timezone-less timestamp
`bytea`	`binary`

numeric without explicit precision can be used in CREATE ICEBERG TABLE (the metadata registers the column as decimal(38,9) by default), but any INSERT into such a column fails with The precision of numeric in foreign tables with parquet format should be specified explicitly. Always declare numeric(p,s).

Types not listed above (such as uuid or json) produce a WARNING at CREATE ICEBERG TABLE time and are mapped to string. Use them with caution.

VACUUM scope

VACUUM compacts data files and reclaims the files it rewrote. Other Iceberg maintenance operations are handled separately:

Operation	Performed by `VACUUM`?	Owner
Compact small data files into larger files	Yes, when input files reach `datalake.iceberg_vacuum_compact_min_input_files`	`VACUUM`
Remove physical rows marked by position-delete	Indirectly, through compaction rewrites	`VACUUM`
Delete the data files that compaction replaced	Yes, for `builtin` tables (queued and deleted in the background)	`VACUUM` plus the deletion queue
Delete files of a dropped or truncated table	Not applicable	`DROP TABLE` / `TRUNCATE` plus the deletion queue. See What happens to the data files
Expire old snapshots (`expireSnapshots()`)	No	Background deletion queue (`datalake.iceberg_max_snapshot_age`, default 5 days)
Remove orphan files (`removeOrphanFiles()`)	No	External tools (Spark or Trino `remove_orphan_files`). Note the shared-directory caveat in `CREATE ICEBERG TABLE` options
Clean up superseded `metadata.json` files	No	Iceberg itself: new tables are created with `write.metadata.delete-after-commit.enabled=true`, so superseded metadata files are removed as the metadata log is trimmed. Set the property explicitly to override this default

Object dependency order

Iceberg objects depend on each other and must be created and dropped in order. The drop order is the reverse of the create order.

Order	Create	Drop
1	Install the `datalake_fdw` extension	Drop the `ICEBERG TABLE`
2	Create the `SERVER`	Drop the `FOREIGN VOLUME`
3	Create the `USER MAPPING`	Drop the `FOREIGN CATALOG`
4	Create the `FOREIGN CATALOG` and `FOREIGN VOLUME`	Drop the `USER MAPPING`
5	Create the `ICEBERG TABLE`	Drop the `SERVER`

ALTER limitations

ALTER TABLE is not supported on Iceberg tables. ADD COLUMN, DROP COLUMN, ALTER COLUMN TYPE, and SET DISTRIBUTED BY are rejected with the error ALTER TABLE is not supported on Iceberg tables. RENAME COLUMN is rejected with the error RENAME COLUMN is not supported on Iceberg tables. To evolve the schema, use Spark, Trino, or Flink to perform the Iceberg-side change, then drop and recreate the Iceberg table in SynxDB.

Limitations

Not yet implemented:

Feature	Notes
`ALTER TABLE` (all subcommands)	`ADD COLUMN`, `DROP COLUMN`, `ALTER COLUMN TYPE`, and `SET DISTRIBUTED BY` are rejected with `ALTER TABLE is not supported on Iceberg tables`. `RENAME COLUMN` is rejected with `RENAME COLUMN is not supported on Iceberg tables`. Use Spark, Trino, or Flink for schema evolution
`PARTITION BY` (identity / bucket / truncate / year, month, day, hour)	Tables are organized by file distribution. Partitioned tables written by Spark or other engines can be read
Time travel (`FOR SYSTEM_TIME AS OF` / `FOR SYSTEM_VERSION AS OF`)	Only the latest snapshot is read
Table-level Iceberg properties (`format-version`, `write.format.default`, `write.parquet.compression-codec`, `write.distribution-mode`)	Determined by catalog defaults; no SQL entry point overrides them
Cross-table distributed transactions	Single-table ACID only; multi-table writes are not atomic
Row-level / column-level security applied to the underlying Iceberg data	Enforced only at the SynxDB entry point; physical files have no access control
Generated columns and expression `DEFAULT` reflected into Iceberg	Accepted by the SynxDB parser but not reflected in the Iceberg schema

Silent no-ops (no error, but the operation has no effect on Iceberg state):

Operation	Actual behavior
`CREATE INDEX ... ON <iceberg_table>`	The build returns immediately with no entries; the index object exists but is empty
`ANALYZE <iceberg_table>`	Refreshes `pg_class.reltuples` and `pg_class.relpages` from Iceberg catalog metadata. Emits `NOTICE: ANALYZE on Iceberg tables refreshed pg_class.reltuples/relpages from Iceberg catalog metadata`. Does not sample row data
`CHECK` constraint	Accepted by the DDL parser and enforced at runtime, but not propagated to Iceberg metadata. `INSERT` or `UPDATE` that violates a `CHECK` constraint is rejected

Explicitly rejected:

Operation	Error
`PRIMARY KEY` / `UNIQUE` / `FOREIGN KEY` constraints at `CREATE ICEBERG TABLE`	`PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatible` (or similar for `UNIQUE`/`FOREIGN KEY`). Iceberg tables are always distributed `RANDOMLY`, which is incompatible with key-based constraints
TID range scan (`WHERE ctid = '(0,1)'`)	Returns unpredictable results (may return unrelated rows)
Internal `analyze_next_block` / `analyze_next_tuple` calls	`API not supported for iceberg relations`

Concurrent writes: Concurrent commits to the same Iceberg table use compare-and-swap with up to 10 retries (TRACKER_MAX_COMMIT_RETRIES). When all retries fail, the transaction aborts with failed to commit iceberg metadata for table <oid> after <N> retries due to concurrent updates. To reduce the chance of conflict, batch multiple rows per INSERT and limit the number of concurrent writers.

For features that SynxDB does not yet support, including ALTER TABLE, PARTITION BY, time travel, and table properties, run the Iceberg-side operations through Spark, Trino, or Flink and let datalake_fdw consume the data.

Runtime errors

Error message	Meaning	Resolution
`iceberg table "%s.%s" does not exist in external catalog "%s"`	The table is not registered in the catalog	Verify the remote catalog name, namespace, and table name. For Polaris, check `default_namespace`
`failed to resolve iceberg table location for relation "%s"`	The catalog cannot return the table location	Check catalog connectivity, `location`, and metadata integrity
`failed to load iceberg table metadata for relation %u`	metadata.json cannot be read	Check volume credentials, `base_path`, and the endpoint for object storage
`external catalog "%s" returned empty table location for "%s.%s"`	The catalog returned an empty location	Catalog bug or namespace registration issue. Verify the table is registered with a valid location in the external catalog (for example through Spark, Trino, or the catalog’s own client)
`empty iceberg table location suffix`	The builtin catalog parsed an empty path	Table name or namespace contains special characters; normalize and recreate
`iceberg metadata catalog is not available on this segment`	A QD-only metadata API was called on a QE	Avoid calling metadata functions in PL/pgSQL that runs on QEs
`foreign catalog with OID %u does not exist`	Stale catalog reference	Check whether the catalog was dropped without being recreated
`foreign volume with OID %u does not exist`	Stale volume reference	Same as above
`must be superuser to create iceberg metadata table`	A non-superuser triggered first-time metadata initialization	Have a superuser run any Iceberg DDL first to perform initialization
`failed to commit iceberg metadata for table %u after %d retries due to concurrent updates`	Concurrent CAS retries exhausted	Batch writes or reduce concurrent writers
`ALTER TABLE is not supported on Iceberg tables`	`ADD COLUMN`, `DROP COLUMN`, `ALTER COLUMN TYPE`, `SET DISTRIBUTED BY` on an Iceberg table	Use Spark/Trino/Flink for schema evolution, or drop and recreate the table
`RENAME COLUMN is not supported on Iceberg tables`	`RENAME COLUMN` on an Iceberg table	Recreate the table with the desired column name, or rename via Spark/Trino/Flink
`no foreign catalog specified`	`CREATE ICEBERG TABLE` without a catalog and no default	Specify `CATALOG` in the `CREATE ICEBERG TABLE` statement or set `iceberg_default_catalog`
`no foreign volume specified`	`CREATE ICEBERG TABLE` without a volume and no default	Specify `VOLUME` or set `iceberg_default_volume`