Best Database for range queries on a single column here's my usecase: I have these fields: id(int), frequency(int), created(timestamp).
I need a database that can optimize range queries on created field. Specifically for queries like: SELECT * FROM my_table WHERE created &gt;= val1 AND created &lt;= val2
This db needs to handle millions of queries and also scale properly.
I've looked at Cassandra and understood that it doesn't work well for range splice queries like this since it needs to query all partitions. I suppose I could simply create an index on the created column and query it in a SQL type db but the reason I'm not looking at SQL db is that it won't be able to scale well (like there's no auto-sharding available in SQL dbs for example).
Please let me know of any db suggestions for this usecase. Thank you!
I'll raise a_horse_with_no_name's ante, and tell you I've personally worked with multi-terabyte databases in Microsoft SQL Server that had single tables in the hundreds of billions of rows without any form of Sharding or Partitioning setup, and ran just fine. I've even discussed the topic with Microsoft representatives who've claimed the system can handle tables into the trillions of rows.
Specifically I've worked with range queries (including on fields like a CreatedDate) on tables in the tens of billions of rows, and they were efficient and performed index seeks when appropriate (based on the cardinality of my predicates). The hardware behind the servers these databases ran on were as modest as an average laptop, in some cases.
As far as any modern database system goes (relational or not), you shouldn't choose one based solely on the amount of data you plan to support. Rather you should choose one based on the type of data (inclusive of it being structured, semi-structured, unstructured, or highly variable), and your use-cases for that data such as the type of querying you plan to do.
With such a skinny and structured table of only three columns with lightweight data types, I see no reason why any modern relational database management system couldn't handle your requirements. Proper indexing and architecting will go the longest of ways towards your performance and scalability, as opposed to which database system you actually choose.