Table options

PGAA table options

You can run CREATE TABLE , ALTER TABLE statements with the USING PGAA WITH ... clause and the following options:

Option	Type	Description
pgaa.storage_location	text	PGFS storage location name.
pgaa.path	text	Path within storage location.
pgaa.format	enum	Table format. Valid values are delta, iceberg, and parquet. (Some might not be supported depending on the feature).
pgaa.managed_by	text	Catalog name for catalog-managed tables.
pgaa.catalog_namespace	text	Namespace/schema in catalog.
pgaa.catalog_table	text	Table name in catalog.
pgaa.auto_truncate	boolean	Truncate heap data on access method switch to PGAA. Requires PGD integration. See Offloading to analytics for details.
pgaa.purge_data_if_exists	boolean	Purge existing analytics data when running CREATE TABLE AS.
pgaa.tiered_table	boolean	Mark as tiered table. Requires PGD integration. See Implementing tiered tables for details.

Examples

• Creating a catalog-managed table:

CREATE TABLE managed_sales_data (
    id BIGINT,
    sale_date TIMESTAMP,
    amount NUMERIC
)
USING PGAA
WITH (
    pgaa.format = iceberg,
    pgaa.managed_by = my_iceberg_catalog,
    pgaa.catalog_namespace = public,
    pgaa.catalog_table = managed_sales_data
);

Note

If you leave the column definitions empty (() ), PGAA will automatically discover the schema from the metadata files when you first query the table and will map columns by name to the fields found in the remote Iceberg/Delta metadata or Parquet headers.

• Altering the table from the previous example to point at a PGFS storage location:

ALTER TABLE managed_sales_data
SET ACCESS METHOD PGAA,
SET (
    pgaa.format = iceberg,
    pgaa.storage_location = my_lake_data,
    pgaa.path = warehouse_data
);

PGD table options

You can run CREATE TABLE , ALTER TABLE statements for heap or HTAP tables and specify the following PGD options:

Option	Type	Description
pgd.replicate_to_analytics	boolean	Enable or disable continuous replication to object storage. See Replicating to analytics for details.
pgd.purge_analytics_target	boolean	Delete any pre-existing tables from the object store directory or catalog entry. See Implementing tiered tables for details.

Examples

• Alter an existing table to enable continuous replication to object storage:

ALTER TABLE my_table SET (pgd.replicate_to_analytics = TRUE);

Managing access methods

Transition tables between transactional and analytical storage by altering the table’s access method. This provides a direct way to manually move data between the local Postgres disk and object storage. See Offloading to analytics for details.

Switching from HTAP to PGAA

To offload an HTAP table to object storage and reclaim local disk space, change the access method to PGAA. The local data will be removed, leaving the data exclusively in the data lake. This is a manual alternative to the automated lifecycle offloading.

ALTER TABLE sales SET ACCESS METHOD PGAA;
SET (
  pgaa.auto_truncate = true,
  pgaa.format = iceberg,
  pgaa.storage_location = my_lake_data,
  pgaa.path = analytical_data
);

Switching from PGAA to heap

To restore an analytical table to a local transactional table, change the access method from PGAA to heap. Postgres will pull the data back from object storage and reconstruct it into local storage pages. Use this when you need to perform high-frequency updates on data that was previously offloaded.

ALTER TABLE sales_history SET ACCESS METHOD HEAP;