Tiered Tables
=============

Tiered Tables enable EDB Postgres® to manage large, time-based datasets
efficiently by automatically moving “cold” data to cost-effective object
storage, while keeping “hot” data in primary transactional storage.

This pattern optimizes both performance and cost while preserving full
analytical access to the entire dataset.

For details on how Tiered Tables are implemented and managed within
Hybrid Manager (HM), see `Tiered Tables <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/tiered_tables>`_  .

What are Tiered Tables
----------------------

Tiered Tables are a native capability of EDB Postgres Distributed (PGD),
supported by the Analytics Accelerator architecture.

They automatically offload older partitions of time-partitioned tables
from PGD to object storage (Apache Iceberg format) using:

- PGD AutoPartition for automated partitioning and lifecycle control

- PGAA and PGFS for querying and accessing offloaded data

- Optional Iceberg catalogs for governance and interoperability

The result: seamless, transparent access to data across hot (PGD) and
cold (object storage) tiers.

Related concept: :ref:`Data tiering <Data tiering>` 

Why Tiered Tables matter for EDB analytics
------------------------------------------

Tiered Tables help organizations:

- Keep PGD operational storage lean and performant

- Lower storage costs by offloading old data to object storage

- Maintain unified query access to the full dataset

- Support both OLTP and OLAP use cases on Postgres

- Implement lakehouse architectures for historical analysis

Related concept: :ref:`Analytics Accelerator concepts <Analytics Accelerator concepts>` 

How EDB implements Tiered Tables
--------------------------------

Core components:

- PGD AutoPartition:

- Creates new time-based partitions automatically

- Defines ``analytics_offload_period`` to control offload timing

- PGFS:

- Provides access to object storage for offloaded data

- PGAA:

- Enables unified querying across PGD and Iceberg tiers

- Creates an offloaded view (``table_offloaded`` ) for cold data only

- Optional Iceberg catalog:

- Supports governance and cross-platform interoperability

Query behavior:

- Queries on the parent PGD table automatically access both hot and cold
  data.

- The PGD query planner pushes WHERE clauses to optimize access across
  storage tiers.

Related concepts:

- :ref:`Separation of storage and compute <Separation of storage and compute>` 

- :ref:`ETL / ELT <ETL / ELT>` 

- :ref:`OLAP / OLTP <OLAP / OLTP>` 

Common use cases
----------------

.. csv-table::
  :header: Use case,Tiered Tables + Analytics Accelerator
  :widths: 10,30
  :align: left
  :class: longtable

  IoT and telemetry,Manage large time-series datasets with automated offload
  Regulatory and financial data retention,Cost-efficient storage of historical data with full auditability
  Analytical reporting on historical data,Use Lakehouse nodes to query offloaded data at scale
  Hybrid OLTP / OLAP patterns,"Keep current data fast on PGD, analyze large history in Iceberg"

Role-based guidance
-------------------

Database administrators (DBAs) :ref:`Analytics Accelerator for your role: DBA <a persona-based guide>` 

Data scientists / analysts :ref:`Analytics Accelerator for your role: Data scientist / analyst <a persona-based guide>` 

DevOps / SRE :ref:`Analytics Accelerator for your role: DevOps / SRE <a persona-based guide>` 

Application developers :ref:`Analytics Accelerator for your role: Application developer <a persona-based guide>` 

Learning paths
--------------

:ref:`Analytics Accelerator 101: Foundational concepts <Foundational concepts>` 

:ref:`Analytics Accelerator 201: Practical application and core solutions <Practical application and core solutions>` 

:ref:`Analytics Accelerator 301: Advanced techniques and optimization <Advanced techniques and optimization>` 

Related concepts
----------------

- :ref:`Data tiering <Data tiering>` 

- :ref:`Separation of storage and compute <Separation of storage and compute>` 

- :ref:`ETL / ELT <ETL / ELT>` 

- :ref:`OLAP / OLTP <OLAP / OLTP>` 

Next steps
----------

For Hybrid Manager users `Tiered Tables <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/tiered_tables>`_ 

How-To guides `Configure PGFS storage for Tiered Tables <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/learn/how-to/configure-tiered-pgfs>`_ 

`Configure PGD node group for analytics offload <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/learn/how-to/configure-tiered-offload>`_ 

`Configure BDR AutoPartition with analytics offload <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/learn/how-to/configure-tiered-autopartition>`_ 

`Query Tiered Tables from PGD and Lakehouse <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/learn/how-to/query-tiered-tables>`_ 

Explore more in the `Analytics Accelerator learning guide <https://enterprisedb.com/docs/edb-postgres-ai/hybrid-manager/analytics/learn/>`_  .