Materialized views in the future?

Help Query Tuning
1

Hi everyone,

we are currently working on a BI use case in which we will have near real time requirements.
Essentially we will get the main entities (e.g. employee) pushed into MemSQL real time and then need to do analytics on those entities.
This will however require us to join multiple tables.
A view would make this easier for the BI folks but we’ll still have the join costs at run time.
Are there any plans to support materialized views?

Thanks!
Christoph

1 Like
2

Christoph, thank you for suggesting a feature. We have it on the roadmap, but can’t commit on the date yet. I’m wondering if something can be done with secondary indexes and vectorized joins that we already support. Can you share your schema, queries, sized of tables, and how selective predicates are.

1 Like
3

Thanks for reaching out Nikita!
Right now I cannot share the exact schemas but here are some more details:

  • There is going to be an “Employee” table with roughly 350k entries
  • There is also going to be a EmployeeHistory" table with multiple million rows
  • Employee salaries are going to be increased in “seasons”
  • There will be many many seasons as they will also be used for simulations

Once we have the exact requirements we will have a tool with 350k+ employees, millions of historic entries, thousands of seasons and in each season different salary increases per employee (e.g. you month fixed salary may change, your bonus may change, etc…).

Does this help you to get a very rough understanding on what we are trying to implement?
Thanks,
Christoph

4

Yes, and how fast do you want the query to work? Is this a three way join?

5

our goal is that every request is answered in <250ms as 250ms is perceived as instant by humans. Since there is latency and the backend needs some time, we are trying to keep all queries at below 100ms.
Does this help and make sense?

6

And how many joins? BTW i think we can meet your SLA without MVs

7

I don’t know yet for sure but I assume between 3 and 6 joins.
MVs would probably have the added benefit of resulting in less CPU usage as we wouldn’t have the costs of doing the join every time the statement is executed. We are looking at a self-service use case with a bunch of Tableau users so it is difficult to forecast the concurrency.
Thank you for your fast responses Nikita!

8

@nikita Any update or public roadmap defining the materialized views support timeline?

9

Hi @chrisgrasp – we don’t have a committed timeline for materialized views but we’re considering it for a future release. Thanks for checking back!

As you might expect some of our customers are using user-defined summary tables instead of materialized views, which can work for situations where maintaining them with your app is not too much of a burden. Mastering Data Warehouse Aggregates by Adamson is a good book about the technique.

10

Hello Singlestore,
Can we please know the latest on materialized views. Thanks

11

We’re planning to do it. No dates I can share.

12

Hi Hanson,

Just wondered if there was any news on this ?

Is there a page where we can view in-development/planned changes where a tentative timeline has been set ?

13

Hi @nlello! Materialized views are in our future, but I’m not ready to share dates. In general we don’t have a public roadmap. We do share it as needed with paying customers and sales prospects.

There’s a lot of breadth to materialized views. Is there something specific you’re looking for?