Query Tiered Tables from PGD and Lakehouse

Once Tiered Tables are implemented, you can query both hot data (local PGD partitions) and cold data (offloaded Iceberg partitions) transparently.

This guide explains:

• How to query Tiered Tables from PGD
• How to query Tiered Tables from a Lakehouse cluster
• How the query planner routes queries
• How to query offloaded-only data

For background, see Tiered Tables in Hybrid Manager.

Goals

After completing this guide, you will:

• Be able to query Tiered Tables transparently from PGD and Lakehouse
• Understand how the query planner accesses hot and cold data
• Know how to query offloaded-only partitions directly
• Know when to use the offloaded view for reports and analysis

When to use this

You should follow this guide when:

• You have implemented Tiered Tables in Hybrid Manager
• You want to validate that hot + cold data is accessible in PGD queries
• You want to query only offloaded data from PGD or Lakehouse
• You want to optimize reports against historical (cold) data

Prerequisites

• BDR AutoPartition with analytics_offload_period configured See Configure BDR AutoPartition with analytics offload

• PGD node group offload configured See Configure PGD node group for analytics offload

• (Optional) Lakehouse cluster connected to your Iceberg catalog See Query existing Apache Iceberg tables

Querying the parent table in PGD

The parent partitioned table (e.g. public.application_logs) is the unified interface for hot + cold data.

Example query:

SELECT log_level, COUNT(*)
FROM public.application_logs
WHERE event_timestamp >= '2023-06-01' AND event_timestamp < '2024-06-01'
GROUP BY log_level;

What happens under the hood:

• Planner accesses local PGD partitions for hot data
• Planner accesses Iceberg offloaded partitions via PGAA for cold data
• Transparent to your query — no special syntax required

Tip: Run EXPLAIN ANALYZE to observe planner behavior.

Querying offloaded data only (PGD)

PGAA creates an offloaded view automatically:

SELECT * FROM public.application_logs_offloaded;

This view queries only offloaded partitions.

Example:

SELECT log_level, COUNT(*)
FROM public.application_logs_offloaded;

When to use the offloaded view:

• Reports targeting cold / historical data only
• Queries where you want to avoid scanning hot partitions
• Testing that offload is working as expected

Querying offloaded data from Lakehouse

If PGD offload uses a catalog (recommended), attach the same catalog in your Lakehouse cluster:

SELECT pgaa.add_catalog(...);

SELECT pgaa.attach_catalog(...);

Then query Iceberg tables directly:

SELECT * FROM iot_data.application_logs_part_2023_jan;

Alternatively, you can replicate the application_logs_offloaded view logic from PGD into Lakehouse:

• Create a view that selects all catalog-managed offloaded partitions
• Useful for BI tools to match the PGD experience

Notes on planner behavior

• The parent table is always the default recommended interface
• Planner automatically optimizes access to PGD vs Iceberg partitions
• Queries with wide time windows may benefit from targeting application_logs_offloaded
• The offloaded view is only created if AutoPartition was configured with analytics_offload_period

Next steps

Now that you have queried Tiered Tables:

• You can Monitor Tiered Tables status and storage savings to track partition movement and space savings
• You can Offload additional PGD data to Apache Iceberg for broader historical coverage
• You can Query existing Apache Iceberg tables to integrate with external tools and BI platforms

For a broader architecture view, see Analytics in Hybrid Manager.