SQL functions reference

These functions let you inspect analytics query performance, resource usage, and cache state, and manage foreign table definitions. Before you use them, install the extension. Run CREATE EXTENSION aurora_analytics in each database where you want to use analytics.

The following table summarizes the analytics SQL functions.

aurora_analytics_stat_statements()

aurora_analytics_stat_statements()

Returns cumulative analytics performance statistics for each normalized query. Use this function to find which queries read from Amazon S3 rather than from cache, which queries spill to disk, and which queries spend the most CPU time.

The following table describes the return columns.

The function also returns the normalized query text for each row.

The following example lists queries that read data, sorted by remote reads from Amazon S3.

SELECT calls,
       pg_size_pretty(analytics_cache_hit_bytes) AS cache_hits,
       pg_size_pretty(analytics_remote_read_bytes) AS s3_reads,
       pg_size_pretty(analytics_total_disk_spill) AS spill,
       round(analytics_total_cpu_time::numeric, 2) AS cpu_ms
FROM aurora_analytics_stat_statements()
WHERE analytics_cache_hit_bytes > 0 OR analytics_remote_read_bytes > 0
ORDER BY analytics_remote_read_bytes DESC;

Interpret the results as follows.

For guidance on interpreting these statistics and tuning your workload, see Monitoring and troubleshooting and Resource management.

aurora_analytics_stat_resource_usage()

aurora_analytics_stat_resource_usage()

Returns a live snapshot of analytics resource usage for each backend. Use this function to find which backend is consuming memory, worker threads, and spill space right now.

The following table describes the return columns.

The following example lists backends that are using memory, sorted by memory usage.

SELECT pid,
       pg_size_pretty(current_memory_usage) AS mem_used,
       active_threads,
       pg_size_pretty(disk_spill_size) AS spill
FROM aurora_analytics_stat_resource_usage()
WHERE current_memory_usage > 0
ORDER BY current_memory_usage DESC;

aurora_analytics_instance_metrics()

aurora_analytics_instance_metrics()

Returns cumulative analytics metrics for the whole instance since the engine started. Use this function to see how much Amazon S3 request and cache activity the instance has done. The counters reset if the engine restarts.

The following table describes the return columns.

The following example returns the instance-wide request and cache metrics.

SELECT s3_head_request_count,
       s3_get_request_count,
       pg_size_pretty(s3_cache_hit_bytes) AS cache_hit,
       pg_size_pretty(s3_remote_read_bytes) AS s3_read
FROM aurora_analytics_instance_metrics();

aurora_analytics_cache_size()

aurora_analytics_cache_size()

Returns a single bigint value that represents the current size, in bytes, of the on-disk read cache for the whole instance. Because the function returns raw bytes, wrap it in pg_size_pretty() for readable output.

The following example returns the current cache size.

SELECT pg_size_pretty(aurora_analytics_cache_size()) AS cache_size;

aurora_analytics_clear_cache()

aurora_analytics_clear_cache()

Clears the on-disk read cache for the whole instance to free local storage. After you clear the cache, subsequent queries re-fetch data from Amazon S3.

The following example clears the read cache.

SELECT aurora_analytics_clear_cache();

aurora_analytics_refresh_foreign_table()

aurora_analytics_refresh_foreign_table(table_name regclass, apply_changes boolean)

Re-infers a foreign table's schema in place from the current source metadata, keeping the table definition in sync with the underlying Amazon S3 data. The first argument, table_name, is the name or OID of the foreign table. The second argument, apply_changes, is a required positional boolean that controls whether the function previews or applies the changes. The function returns a boolean value that is true on success.

Permission: foreign table owner or rds_superuser.

The following example first previews the changes and then applies them.

-- Preview the changes
SELECT aurora_analytics_refresh_foreign_table('ft_orders', false);

-- Apply the changes
SELECT aurora_analytics_refresh_foreign_table('ft_orders', true);

aurora_analytics_available_features()

aurora_analytics_available_features()

Lists the PostgreSQL operators, functions, and types that the analytics engine supports for vectorized execution pushdown. Use this function to determine which SQL constructs can be pushed down and executed natively rather than falling back to PostgreSQL.

This function takes no arguments.

The following table describes the return columns.

Permission: all users.

The following example lists the supported types that have pushdown restrictions.

SELECT t.typname, f.description
FROM aurora_analytics_available_features() f
JOIN pg_type t ON f.oid = t.oid
WHERE f.feature_type = 'type' AND f.description IS NOT NULL
ORDER BY t.typname;

aurora_analytics_stats_duckdb_memory()

aurora_analytics_stats_duckdb_memory()

Reports memory usage and temporary storage usage per internal memory category for the current backend. Use this function to understand how the analytics engine allocates memory across internal components during query execution.

This function takes no arguments.

The following table describes the return columns.

Permission: all users.

The following example returns the memory usage for the current backend.

SELECT * FROM aurora_analytics_stats_duckdb_memory();

aurora_analytics_remote_tables()

aurora_analytics_remote_tables(catalog text, schema_name text)

Discovers the tables available in a remote catalog (an AWS Glue database or an Amazon S3 Tables namespace) and returns their names and storage locations.

This function takes the following arguments.

The following table describes the return columns.

Permission: USAGE privilege on the foreign server.

The following example lists the tables in a remote catalog and schema.

SELECT * FROM aurora_analytics_remote_tables('arn:aws:glue:us-east-1:123456789012:catalog', 'my_database');

aurora_analytics_iceberg_snapshots()

aurora_analytics_iceberg_snapshots(table_name regclass)

Lists all available Iceberg snapshots for a foreign table. Use this function to discover which snapshots exist before you set the snapshot option for time-travel queries.

This function takes one argument, table_name, which is the name or OID of an Iceberg foreign table.

The following table describes the return columns.

Permission: foreign table owner or rds_superuser. SELECT on the table alone is not sufficient.

The following example lists the five most recent snapshots for a foreign table.

SELECT sequence_number, snapshot_id, timestamp_ms
FROM aurora_analytics_iceberg_snapshots('ft_transactions')
ORDER BY sequence_number DESC
LIMIT 5;

aurora_analytics_iceberg_metadata()

aurora_analytics_iceberg_metadata(table_name regclass)

Lists the Iceberg manifest entries and data files for a foreign table. Use this function for debugging, understanding table layout, or inspecting which data files back a table.

This function takes one argument, table_name, which is the name or OID of an Iceberg foreign table.

The following table describes the return columns.

Permission: foreign table owner or rds_superuser. SELECT on the table alone is not sufficient.

The following example lists the five largest existing data files for a foreign table.

SELECT file_path, file_format, record_count, status
FROM aurora_analytics_iceberg_metadata('ft_transactions')
WHERE status = 'EXISTING'
ORDER BY record_count DESC
LIMIT 5;

aurora_analytics_iceberg_raw_metadata()

aurora_analytics_iceberg_raw_metadata(table_name regclass)

Returns the raw metadata.json of an Iceberg foreign table as a single JSON value. Use this function for debugging or reading non-standard metadata fields that aurora_analytics_iceberg_snapshots and aurora_analytics_iceberg_metadata don't expose, such as table properties, partition specs, or sort orders.

This function takes one argument, table_name, which is the name or OID of an Iceberg foreign table.

This function returns a single json value that contains the complete Iceberg metadata.json content.

Permission: foreign table owner or rds_superuser. SELECT on the table alone is not sufficient.

The following example returns the raw Iceberg metadata for a foreign table.

SELECT aurora_analytics_iceberg_raw_metadata('ft_transactions');