hi everybody, Can QuestDB use ShardingSphere for sharding table？

Hi @李华 afaik no one tested it before, perhaps the best way is to give it a try and see what works and what not. Could you share more about the scenarios when you’d need sharding?

I have a table, and the data volume is about 30 million per day. If the table query is conducted by week, the expected effect will not be reached, so I want to table by day.

Thank you! Do you perhaps have a query example?

This is an SQL query for a week's data.There are currently 200 million pieces of data in this table, and the query time is as follows.

select ts, id, first(value) as value from 'historyData_AM' where ts BETWEEN '2021-11-01' AND '2021-11-14' AND id = 7003 SAMPLE BY 30m;

Thank you so much! I’ll check with the engineering team on this see how we could help.

Hello, so essentially you have a week worth of data and you want to make 7 tables with daily data instead ?

From the query it might be more about sharding the table based on the ID (but I could be wrong). @Bolek Ziobrowski I think we’ve seen similar situation before, do you remember whether using symbol would improve the query performance potentially?

@李华 If id is selective you could improve response time by making id a symbol and adding an index.

Which data is cold data.

Cold data is data on disk that can't be found in os cache .

Yes, the query result of cold data above, is there any way to shorten the query time of cold data

Have you tried making id a symbol and adding index?

The query efficiency is the same after the id index is added.

Is it the same with and without index for cold and hot data ? Can you run the following ?

select count_distinct(id) from historyData_AM

select count(*) as value from 'historyData_AM' where ts BETWEEN '2021-11-01' AND '2021-11-14' AND id = 7003 SAMPLE BY 30m;