Querying tiered data

Once rarely used data is tiered and migrated to the object storage tier, it can still be queried with standard SQL by enabling the timescaledb.enable_tiered_reads GUC. By default, the GUC is set to false so that queries on TimescaleDB do not touch tiered data.

The timescaledb.enable_tiered_reads GUC, or Grand Unified Configuration variable, is a setting that controls if tiered data is queried. The configuration variable can be set at different levels, including globally for the entire database server, for individual databases, and for individual sessions.

With tiered reads enabled, you can query your data normally even when it's distributed across different storage tiers. Your hypertable is spread across the tiers, so queries and JOINs work and fetch the same data as usual.

Warning

By default, tiered data is not accessed by queries. Querying tiered data may slow down query performance as the data is not stored locally on Timescale's high-performance storage tier.

querying tiered data in a single query

1. Enable timescaledb.enable_tiered_reads before querying the hypertable with tiered data and reset it after it is complete:

set timescaledb.enable_tiered_reads = true; SELECT count(*) FROM example; set timescaledb.enable_tiered_reads = false;

Copy

This queries data from all chunks including tiered chunks and non tiered chunks:

||count|
|---|
|1000|

Copy

All future queries within a session can be enabled to use the object storage tier by enabling timescaledb.enable_tiered_reads within a session.

1. Enable timescaledb.enable_tiered_reads for an entire session:

set timescaledb.enable_tiered_reads to true;

Copy

1. All future queries in that session are configured to read from tiered data and locally stored data.

You can also enable queries to read from tiered data always by following these steps:

1. Enable timescaledb.enable_tiered_reads for all future sessions:

alter database tsdb set timescaledb.enable_tiered_reads to true;

Copy

1. In all future created sessions, timescaledb.enable_tiered_reads initializes with enabled.

Queries over tiered data are expected to be slower than over local data. However, in a limited number of scenarios tiered reads can impact query planning time over local data as well. In order to prevent any unexpected performance degradation for application queries, we keep the GUC timescaledb.enable_tiered_reads to false.

• Queries without time boundaries specified are expected to perform slower when querying tiered data, both during query planning and during query execution. TimescaleDB's chunk exclusion algorithms cannot be applied for this case.

SELECT * FROM device_readings WHERE id = 10;

Copy

• Queries with predicates computed at runtime (such as NOW()) are not always optimized at planning time and as a result might perform slower than statically assigned values when querying against the object storage tier.

  For example, this query is optimized at planning time

  SELECT * FROM metrics WHERE ts > '2023-01-01' AND ts < '2023-02-01'

  Copy

  while the following query does not do chunk pruning at query planning time

  SELECT * FROM metrics WHERE ts < now() - '10 days':: interval

  Copy

  At the moment, queries against tiered data work best when the query optimizer can apply planning time optimizations.

• Text and non-native types (JSON, JSONB, GIS) filtering is slower when querying tiered data.