Functions Innovation Release

Suggest edits

This documentation covers the current Innovation Release of EDB Postgres AI. You may also want the docs for the current LTS version.

The reference section is a list of PGAA and PGFS functions available with Analytics Accelerator.

PGFS functions

Refer to PGFS functions for Pipelines for details.

PGAA functions

pgaa.list_tables

Lists all PGAA tables on the instance, their storage location, and path.

Synopsis

SELECT * FROM pgaa.list_analytics_tables();

Parameters

None.

Returns

Parameter	Type	Description
`schema_name`	`TEXT`	The schema of the table.
`table_name`	`TEXT`	The name of the table.
`managed_by`	`TEXT`	The name of the Iceberg REST Catalog from which this table is synced (attached).
`object_storage_snapshot_size_bytes`	`BIGINT`	Size in bytes of the latest snapshot in object storage.
`object_storage_total_size_bytes`	`BIGINT`	Size in bytes of all table data in object storage.
`replication_status`	`ENUM`	One of `disabled`, `initial_offload`, or `enabled`
`storage_location_name`	`TEXT`	Name of the `pgfs.storage_location` containing the table
`storage_location_path`	`TEXT`	Path within the storage location of the table
`catalog_name`	`TEXT`	Name of the Iceberg REST Catalog containing the table (origin or replication target)
`catalog_namespace`	`TEXT`	The namespace (i.e., schema) of the table in the Iceberg REST Catalog
`catalog_table`	`TEXT`	The name of the table in the Iceberg REST Catalog

A table can either have a storage location or a catalog. It cannot have both.

The catalog details refer to either the replication target, if performing a catalog-managed offload or replication to object storage, or to the source catalog location, if reading data from tables managed by an external catalog. See Metadata management for details.

pgaa.lakehouse_table_stats

Returns the size in bytes of the latest version of a Lakehouse table and the total size of all versions.

Synopsis

SELECT * FROM pgaa.lakehouse_table_stats();

Parameters

Parameter	Type	Description
`relation`	`REGCLASS`	The name of the table to get the size of.

Returns

Column	Type	Description
`latest_snapshot_size`	`BIGINT`	The size in bytes of the latest snapshot of the table.
`total_size`	`BIGINT`	The total size in bytes of all versions of the table.

pgaa.convert_to_tiered_table

Converts a heap table into a tiered table and initiates the automated process to offload older partitions to the configured object storage.

Synopsis

CALL pgaa.convert_to_tiered_table(
    'relation_name'::regclass,
    range_partition_column := 'your_timestamp_column',
    partition_increment := '1 year', 
    analytics_offload_period := '1 year',
    initial_lower_bound := '2010-01-01',
    enable_replication := true 
);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the table.
`range_partition_column`	`TEXT`	The column to partition by. It must be `DATE` or `TIMESTAMP`, and it must also be included in the primary key.
`initial_lower_bound`	`TEXT`	The chronological starting point for the first partition.
`partition_increment`	`INTERVAL`	The time interval in between partitions.
`analytics_offload_period`	`INTERVAL`	The age threshold that automatically triggers the migration of all older partitions to object storage, marking the data as "cold".
`enable_replication`	`BOOLEAN`	When set to `FALSE`, the table's data is only offloaded once partitions exceed the "cold" data threshold. If set to `TRUE`, it triggers immediate real-time replication.
`minimum_advance_partitions`	`INTEGER`	Minimum number of future partitions that the system must proactively create ahead of the current date.

Returns

None.

pgaa.enable_analytics_replication

Enables replication for a heap table to continuously stream data to object storage, setting up the table as an HTAP table.

Synopsis

CALL pgaa.enable_analytics_replication(`relation_name`);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the heap table.

Returns

None.

pgaa.disable_analytics_replication

Disables continuous replication on an HTAP table, returning it to a standard heap table state.

Synopsis

CALL pgaa.disable_analytics_replication(`relation_name`);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the HTAP table.

Returns

None.

pgaa.convert_to_analytics

Converts a synchronized HTAP table into a "cold", pure-analytics table and removes the data from local disk.

Synopsis

CALL pgaa.convert_to_analytics(`relation_name`);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the HTAP table.

Returns

None.

pgaa.restore_from_analytics

Restores a complete table from object storage (a PGAA table) back into the PGD cluster, materializing it as a standard HEAP table for transactional access. You can also restore a table that originated from a different PGD cluster.

Synopsis

CALL pgaa.restore_from_analytics(`relation_name`);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the PGAA table.

Returns

None.

pgaa.execute_compaction

Performs compaction to improve the performance and storage efficiency of a PGAA table.

Synopsis

CALL pgaa.execute_compaction(`relation_name`);

Parameters

Column	Type	Description
`relation`	`REGCLASS`	The name of the PGAA table.

Returns

None.

pgaa.add_catalog

Registers a new external catalog service (like an Iceberg REST Catalog) within the PGD cluster's metadata, enabling catalog-managed offload for metadata decoupling.

Synopsis

SELECT pgaa.add_catalog(
    catalog_name TEXT,
    catalog_type TEXT,
    catalog_options JSON
);

Parameters

Column	Type	Description
`catalog_name`	`TEXT`	The unique name to assign to the external catalog configuration.
`catalog_type`	`TEXT`	The type of catalog service. Currently only `iceberg-rest` is supported.
`catalog_options`	`JSON`	A JSON object containing the catalog service options.

The following is an example of a JSON object for catalog_options:

{
"url": "https://edbhcp.example.com/api/iceberg/prj_9MxU4B6KjXS3FcVW",
"token": "<<< censored >>>",
"warehouse": "1d729496-8a8c-11f0-967c-833da882bb39",
"warehouse_name": "prj_9MxU4B6KjXS3FcVW-catalog-RmDmVm",
"danger_accept_invalid_certs": "true"
}

Some Iceberg REST Catalog providers, like Snowflake Open Catalog, might expect different parameters. PGAA supports OAuth tokens, for example:

{
"oauth2.grant_type": "client_credentials",
"oauth2.client_id": "oauth-client-id",
"oauth2.client_secret": "oauth-client-secret",
"oauth2.token_uri": "https://EXAMPLE-polaristest.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens",
"oauth2.scope": "PRINCIPAL_ROLE:read_only"
}

Returns

None.

pgaa.attach_catalog

Makes an already registered external catalog (using pgaa.add_catalog) available for querying within the current PostgreSQL session.

Synopsis

SELECT pgaa.attach_catalog(
    catalog_name TEXT
);

Parameters

Column	Type	Description
`catalog_name`	`TEXT`	The unique name of the external catalog that was previously registered by `pgaa.add_catalog`.

Returns

None.

← Prev

Loading data

↑ Up

Reference - EDB Postgres® AI Lakehouse

Supported AWS instances

Functions Innovation Release

PGFS functions

PGAA functions

pgaa.list_tables

pgaa.lakehouse_table_stats

pgaa.convert_to_tiered_table

pgaa.enable_analytics_replication

pgaa.disable_analytics_replication

pgaa.convert_to_analytics

pgaa.restore_from_analytics

pgaa.execute_compaction

pgaa.add_catalog

pgaa.attach_catalog

← Prev

↑ Up

Next →