# How's TTL performance **URL:** \ **Category:** General\ **Created:** [October 23, 2019, 9:46am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546 "2019-10-23T09:46:07Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [October 23, 2019, 9:46am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/1 "2019-10-23T09:46:07Z")
Hello, How’s TTL performance(Row level) in yugabyte? How it affect when write/read at same time? I’d like to know if there’s a a benchmark test especially for huge data?Or I need do it myself. The case is that there’s always huge timeseries data came in we want the stale data auto move out (delete/move to other place). TTL is one option. the other one is to split table by period(hour,day,week) But with the latter, it may need run multiple query for a request. Is there a good way to handle this? Look forward to your suggesstions. Thanks. ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 23, 2019, 12:42pm UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/2 "2019-10-23T12:42:25Z")
@Fulton_Fu see replies inline > [@Fulton\_Fu](#): > > How’s TTL performance(Row level) in yugabyte? Inserts are as usual. > [@Fulton\_Fu](#): > > How it affect when write/read at same time? Writes are as usual. While reads will also read from disk, and return data if the row hasn’t expired. Expired rows will be deleted from disk on compaction. If there are too many rows expired it will also trigger a compaction so there is no wasted space. > [@Fulton\_Fu](#): > > I’d like to know if there’s a a benchmark test especially for huge data?Or I need do it myself. This will depend on many factors like TTL, amount of data, type of reads, write volume, etc. > [@Fulton\_Fu](#): > > TTL is one option. the other one is to split table by period(hour,day,week) Time series (with some type of regularity) are best implemented as separate tables. This will make it possible to just drop old tables and not expire. > [@Fulton\_Fu](#): > > But with the latter, it may need run multiple query for a request. Can you explain your queries ? > [@Fulton\_Fu](#): > > Is there a good way to handle this? Look forward to your suggesstions. Explain your case (ttl, expected rows per day, date-range of queries) and we can see which is best. ---
**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 23, 2019, 12:50pm UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/3 "2019-10-23T12:50:34Z")
How TTL works underneath: [Persistence in YugabyteDB | YugabyteDB Docs](https://docs.yugabyte.com/latest/architecture/docdb/persistence/#ycql-time-to-live-ttl-example) table level: [CREATE TABLE statement [YCQL] | YugabyteDB Docs](https://docs.yugabyte.com/latest/api/ycql/ddl_create_table/#table-properties-1) row level: [INSERT statement [YCQL] | YugabyteDB Docs](https://docs.yugabyte.com/latest/api/ycql/dml_insert/#insert-a-row-with-expiration-time-using-the-using-ttl-clause) ---
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [October 24, 2019, 1:49am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/4 "2019-10-24T01:49:42Z")
Thank you @dorian_yugabyte for quick reply, the rows count would be billions. The ttl depends, it could be several days for data amount too large in one day, also could be hundreds/tens of days for the data amount not too large in one day. If we seperate the table like table\_day1,table\_day2 The query I mentioned: If need the data come from the two days which come from two tables. Is it possible to do this thing like lua in redis or procedure in relation database, or have to request two query? and one more question, not sure if I should create new topic, Assume we have table ((id:partitionkey), timestamp:sortkey), attributes For query: select attributes from table where timestamp\>=start and timestamp\=0 and partition\_hash(id)\<=65535 (split this condition) timestamp\>=start and timestamp\ **Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 24, 2019, 7:17am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/5 "2019-10-24T07:17:45Z") > [@Fulton\_Fu](#): > > The ttl depends, it could be several days for data amount too large in one day, also could be hundreds/tens of days for the data amount not too large in one day. So it’s per-row ttl ? How is it set, what’s the business logic ? > [@Fulton\_Fu](#): > > If we seperate the table like table\_day1,table\_day2 You can also separate per-week/month and also do filtering inside them. > [@Fulton\_Fu](#): > > Is it possible to do this thing like lua in redis or procedure in relation database, or have to request two query? It’s actually better in distributed systems to split some queries into several **parallel** ones when you’re filtering by partition. > [@Fulton\_Fu](#): > > Does it help if we split this kind of query to be: In the backend, it still has to do table/tablet-scan. This will be more efficient, since you can do the queries in parallel and because it will hit the leader node lower inter-node-networking. But to have selectivity, you have to filter by the partition column. You can also create an index ? (or double the writes ?) ---
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [October 24, 2019, 8:03am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/6 "2019-10-24T08:03:47Z")
Yes, row level, set it when new data came in. From these info, I suppose we should choose to seperate the tables. partition\_hash condition also made a table scan, we need think about index or double write. Thank you for suggesstion. ---
**Author:** ![kisg](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/kisg/32/154_2.png) [@kisg](https://forum.yugabyte.com/u/kisg)\ **Post date:** [October 24, 2019, 8:41am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/7 "2019-10-24T08:41:49Z")
Hi, without knowing much about your use case, this is just a shot in the dark, but the large amounts of continuous data could make it a good candidate for a streaming data architecture, e.g. using Kafka. In this architecture you could use Kafka Streams to process and aggregate the raw data, with YugabyteDB acting as a high performance, distributed state storage for Kafka Streams. This way YugabyteDB can provide a query interface for your aggregate / processed data. Best Regards, Gergely ---
**Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [October 25, 2019, 1:09am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/8 "2019-10-25T01:09:21Z")
Thank you, The use case is simple schema could be ((groupid, id),timestamp) attributes. we want the stale data could be removed after a period. For the query like select from table where timetamp\>=start and timestamp\ **Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\ **Post date:** [October 25, 2019, 12:04pm UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/9 "2019-10-25T12:04:32Z") > [@Fulton\_Fu](#): > > For the query like select from table where timetamp\>=start and timestamp\ **Author:** ![Fulton\_Fu](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/fulton_fu/32/169_2.png) [@Fulton\_Fu](https://forum.yugabyte.com/u/Fulton_Fu)\ **Post date:** [October 27, 2019, 1:00am UTC](https://forum.yugabyte.com/t/hows-ttl-performance/546/10 "2019-10-27T01:00:43Z") Hi It’s fixed, every minute/hour/day thanks