Query Tiered Tables from PGD and Lakehouse

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#

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:

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

Contents