Once rarely used data is tiered and migrated to the object storage tier, it can still be queried
with standard SQL by enabling the
By default, the GUC is set to false so that queries on TimescaleDB do not touch tiered data.
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
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.
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.
timescaledb.enable_tiered_readsbefore 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;
This queries data from all chunks including tiered chunks and non tiered chunks:
All future queries within a session can be enabled to use the object storage tier by enabling
timescaledb.enable_tiered_reads within a session.
timescaledb.enable_tiered_readsfor an entire session:
set timescaledb.enable_tiered_reads to true;
- 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:
timescaledb.enable_tiered_readsfor all future sessions:
alter database tsdb set timescaledb.enable_tiered_reads to true;
- 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;
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 timeSELECT * FROM metrics WHERE ts > '2023-01-01' AND ts < '2023-02-01'
while the following query does not do chunk pruning at query planning timeSELECT * FROM metrics WHERE ts < now() - '10 days':: interval
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.
Found an issue on this page?Report an issue!