Columnar storage for analytical queries

[bentsherman]

I am experimenting with surrealdb for a number of projects at my company. We are intrigued by the powerful query language, but one of our biggest needs is performant analytical queries. For example, clickhouse is our current top choice because it supports columnar storage and nested schemas, but now we're also looking at TiDB because it has a nice HTAP setup.

I see there are several efforts in progress to improve query performance, but it seems like surrealdb fundamentally depends on row-based storage. Do you have any plans to support columnar storage for analytical queries? Or are you taking some other path to achieve high performance in this area?

For example, since surrealdb can already use TiKV as a storage backend, I'm wondering if it would be possible to also support TiFlash. The TiDB setup is more complicated because you have to deploy both and TiDB automatically replicates data and routes queries to one backend or the other based on some cost estimate, but even being able to deploy a surrealdb instance with a columnar backend would be very powerful.

[tobiemh]

Hi @bentsherman could you give me an idea of the types of analytical queries that you are looking to run?

[bentsherman]

Hi Tobie, our core product is a workflow platform, so the data in question is a table of historical task runs. Every task has a timestamp, tags and labels, resource usage stats, etc. Here are some example queries:

• Query all records with tag == x
• Query all records with labels containing x
• Query all records with labels containing x within a given date range
• Sum cpus, memory, runtime of all records with workflowId == x
• Sum cpus, memory, runtime of all records submitted within a given date range
• Sum cpus, memory, runtime of all records submitted within a given date range, grouped by process name

[MordechaiHadad]

I also suggested this on discord general chat after I was prompted to by one of the staff, but received no actual answer.