On this page
As a time-series database, one of the basic functions is setting the data's TTL (Time-To-Live). GreptimeDB stands out among other time-series databases with its support for highly flexible data expiration strategies:
- Database-level TTL
- Table-level TTL
Both strategies can be configured during table creation using the CREATE SQL statement with the WITH clause to specify the ttl option. Additionally, they can be adjusted independently. With the latest release, v0.10, dynamic modification of TTL is now supported.
Prometheus Remote Write as an example
Designate Database TTL
When writing to GreptimeDB through the Prometheus Remote Write protocol, you can create a separate database and specify its data TTL:
sql
CREATE DATABASE prometheus WITH (TTL='7d');Here, a database named Prometheus is created, and its data TTL is set to 7 days.
Database is the basic unit of tenant isolation in GreptimeDB.
TTL is a time range string, supported suffixes include:
nsec,ns– nanosecondusec,us– microsecondmsec,ms– millisecondseconds,second,sec,s– secondminutes,minute,min,m– minutehours,hour,hr,h– hourdays,day,d– dayweeks,week,w– weekmonths,month,M– monthyears,year,y– year
Multiple units can be combined, e.g., 1hour 12min 5s.
Next, specify this database in the remote write section of prometheus.yml:
yaml
remote_write:
- url: http://localhost:4000/v1/prometheus/write?db=prometheusThe db parameter in the URL specifies the database to write to. Once set correctly, each measurement written through Prometheus will automatically create a table. The default TTL of every table in the database will be the same as the database prometheus, which is 7 days. Read this documentation to learn more about the data model.
We can check the TTL of the database and tables through SQL queries:
sql
mysql> SHOW CREATE DATABASE prometheus;
+------------+------------------------------------------------------------------+
| Database | Create Database |
+------------+------------------------------------------------------------------+
| prometheus | CREATE DATABASE IF NOT EXISTS prometheus
WITH(
ttl = '7days'
) |
+------------+------------------------------------------------------------------+
1 row in set (0.01 sec)Let's check the up table:
sql
mysql> use prometheus;
Database changed
mysql> SHOW CREATE TABLE up;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| up | CREATE TABLE IF NOT EXISTS `up` (
`greptime_timestamp` TIMESTAMP(3) NOT NULL,
`greptime_value` DOUBLE NULL,
`instance` STRING NULL,
`job` STRING NULL,
TIME INDEX (`greptime_timestamp`),
PRIMARY KEY (`instance`, `job`)
)
ENGINE=metric
WITH(
on_physical_table = 'greptime_physical_table',
ttl = '7days'
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)This is a Metric logical table with a TTL of 7days, consistent with the database setting. The Metric engine is a dedicated storage engine designed by GreptimeDB for dealing with massive metric data in Observability scenarios. For more introduction, please read this article.
TTL represents the minimum effective retention period, meaning that actual data expiration will typically occur at or after this specified duration.
Alter Database TTL
If you want to modify a database TTL, you can use the ALTER clause below:
sql
ALTER DATABASE prometheus SET ttl='1month';You can check if it's successfully altered using the following command:
sql
mysql> SHOW FULL DATABASES LIKE 'prometheus';
+------------+--------------+
| Database | Options |
+------------+--------------+
| prometheus | ttl='1month' |
+------------+--------------+
mysql> SHOW CREATE TABLE 'up';
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| up | CREATE TABLE IF NOT EXISTS `up` (
`greptime_timestamp` TIMESTAMP(3) NOT NULL,
`greptime_value` DOUBLE NULL,
`instance` STRING NULL,
`job` STRING NULL,
TIME INDEX (`greptime_timestamp`),
PRIMARY KEY (`instance`, `job`)
)
ENGINE=metric
WITH(
on_physical_table = 'greptime_physical_table',
ttl = '1month'
) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)For the table created, if the TTL is not explicitly specified, the database-level TTL will be used by default. Once the database TTL is changed, all tables within it that do not have a specified TTL will change accordingly.
Specify and Modify Table TTL
Simliar to creating a database, you can set table-level ttl when creating tables:
sql
CREATE TABLE test (d double, ts TIMESTAMP TIME INDEX) WITH (ttl = '1year');You can also use ALTER clause to modify existing tables' TTL:
sql
ALTER TABLE test SET ttl='2years';To check whether created successfully or not:
sql
mysql> SHOW CREATE TABLE test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE IF NOT EXISTS `test` (
`d` DOUBLE NULL,
`ts` TIMESTAMP(3) NOT NULL,
TIME INDEX (`ts`)
)
ENGINE=mito
WITH(
ttl = '2years'
) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)The current version does not support TTL modifications for Metric physical tables, as Metric logical tables share the same underlying physical table, preventing independent TTL adjustments. This feature will be available in the next version—stay tuned! For updates, feel free to follow this issue.
Summary
GreptimeDB provides the greatest flexibility for managing time series data. You can specify a global TTL for a single database or a separate TTL for each table. As GreptimeDB iterates, we will continue to provide more flexible data validity management for automatic table creation in Prometheus and logs.
Hidden Tips: How to specify TTL for automatically created tables when writing
In addition to creating a database with a specified TTL in advance, you can also specify it through the HTTP header x-greptime-hint-ttl in the scenario of automatic table creation via gRPC. For example, x-greptime-hint-ttl :7d can specify the TTL of the automatically created table as 7 days when writing.
Let's take Java Ingester as an example, simply create a Conext and specify ttl when writing:
java
CompletableFuture<Result<WriteOk, Err>> puts = greptimeDB.writeObjects(metrics, Context.withHint("ttl", "7d"));About Greptime
Greptime offers industry-leading time series database products and solutions to empower IoT and Observability scenarios, enabling enterprises to uncover valuable insights from their data with less time, complexity, and cost.
GreptimeDB is an open-source, high-performance time-series database offering unified storage and analysis for metrics, logs, and events. Try it out instantly with GreptimeCloud, a fully-managed DBaaS solution—no deployment needed!
The Edge-Cloud Integrated Solution combines multimodal edge databases with cloud-based GreptimeDB to optimize IoT edge scenarios, cutting costs while boosting data performance.
Star us on GitHub or join GreptimeDB Community on Slack to get connected.
