Table options v1.7

Suggest edits

PGAA table options

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

OptionTypeDescription
pgaa.storage_locationtextPGFS storage location name.
pgaa.pathtextPath within storage location.
pgaa.formatenumTable format. Valid values are delta, iceberg, and parquet. (Some might not be supported depending on the feature).
pgaa.managed_bytextCatalog name for catalog-managed tables.
pgaa.catalog_namespacetextNamespace/schema in catalog.
pgaa.catalog_tabletextTable name in catalog.
pgaa.auto_truncatebooleanTruncate heap data on access method switch to PGAA. Requires PGD integration. See Offloading to analytics for details.
pgaa.purge_data_if_existsbooleanPurge existing analytics data when running CREATE TABLE AS.
pgaa.tiered_tablebooleanMark 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:

OptionTypeDescription
pgd.replicate_to_analyticsbooleanEnable or disable continuous replication to object storage. See Replicating to analytics for details.
pgd.purge_analytics_targetbooleanDelete 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;
← Prev

Functions

↑ Up

Reference

Next →

Data types and definitions