Support for Materialized Views

[proddata]

Problem Statement

We have an application with a dashboard component and an ingestion layer.

• The dashboard is frequently accessed and needs to be responsive (Ideally <100ms, but up to 500ms tail latencies are acceptable). There is a hard timeout in the proxy after 5 minutes.
• We'd like to extend the dashboard with new information that requires a heavy query joining several tables, including correlated joins and aggregations. These take ~15 seconds to run.
• We'd need a way to speed up these queries, potentially by pre-computing their result as it is acceptable if the dashboard shows slightly outdated data (like up to an hour). The result of the query is fairly small (at most thousands of records, so the insert load and storage is not a real concern)

Possible solutions

• In PostgreSQL one could use materialized views
• See also: https://github.com/crate/crate/issues/8806

Considered alternatives

• Optimizing the query, but there is nothing left to squeeze out. It processes billions of records after all

• Using a manual variant of a materialized view:

  • Create table
  • Populate with insert into <tbl> (...) select (...) on conflict (...) do update set ...

This works, but is more difficult to use:

• You have to identify which columns should become part of the conflict condition
• You have to come up with the columns for the set assignments
• CrateDB on conflict only works on primary key columns, not on arbitrary columns, so you have to define these columns as primary key.

Other downsides:

• Snapshots will include the data, unless the tables to snapshot are manually managed
• The query logic needs to be managed externally. (Related to https://github.com/crate/crate/issues/10731)
• Only works for append-only cases. Not for removal of records

To address the last point, one can use a temporary table, insert into and swap tables, but that's even more tedious to use and shares the other downsides.

Discussion remarks

Options for refresh:

• Temporary index and then table swap
• Just delete and rebuild (but records will disappear and might take a long time)
• Can we delete but keep the readers open
• Delta based updates (seq#?) (Timely Dataflow and Differential Dataflow?)

Resources

• https://www.timescale.com/blog/how-postgresql-views-and-materialized-views-work-and-how-they-influenced-timescaledb-continuous-aggregates/?utm_source=timescaledb&utm_medium=twitter&utm_campaign=july-2022-advocacy&utm_content=blog-how-postgresql-views-materialized-views-work

Prerequisite

• [ ] https://github.com/crate/crate/issues/11939

[proddata]

The described workaround (CREATE TABLE / INSERT INTO) has some flaws:

• primary keys on the materialisation table are required for INSERT INTO
• during updates, there are inconsistencies in the result

A workaround for "simple" materialization would be:

/* CREATE MATERIALIZED VIEW my_table_rollup AS
SELECT
    device_id,
    date_trunc('week',ts) week,
    SUM(val) avg_val
FROM my_table
GROUP BY 1, 2;
*/
CREATE TABLE my_table_rollup AS
SELECT
    device_id,
    date_trunc('week',ts) week,
    SUM(val) avg_val
FROM my_table
GROUP BY 1, 2;

and then update via

/* REFRESH MATERIALIZED VIEW my_table_rollup; */
CREATE TABLE temp_my_table_rollup AS
SELECT
    device_id,
    date_trunc('week',ts) week,
    AVG(val) avg_val
FROM my_table
GROUP BY 1, 2;
REFRESH TABLE temp_my_table_rollup;
ALTER CLUSTER SWAP TABLE temp_my_table_rollup TO my_table_rollup WITH ("drop_source" = true);

While this might not look as too much effort in the first place, managing especially the update part is rather cumbersome, especially when one has to deal with updating 10,20,30, ... "views" with some sort of cron job.

Replacing view definitions, would not only need the change in the "view" (table), but also the mechanism that updates it accordingly. Especially in organisation, where this might not be handled by the same person, this becomes tedious.

Operations like snapshots or other blocking operations that would not allow dropping tables would break this

[proddata]

a better approach might be to use a partitioned table with a view on top of it, which gets updated only after the insert into a new partition was successful.

for a view like

CREATE VIEW my_table_rollup AS
SELECT
    device_id,
    date_trunc('week',ts) week,
    SUM(val) avg_val
FROM my_table
GROUP BY 1, 2;

one could use the following approach:

1. Gather the view definition of the original view using the http endpoint with 127.0.0.1:4200/_sql?types to recover column names and types:

   SELECT * FROM my_table_rollup LIMIT 0;

2. Create a partitioned table with an update field, which is used for partitioning:

   CREATE TABLE my_table_rollup_materialization (
   update TIMESTAMP,
   device_id TEXT,
   week TIMESTAMP,
   avg_val DOUBLE
   ) PARTITIONED BY(update);

3. create timestamp e.g. ts_now

4. Update data from the original view:

   INSERT INTO my_table_rollup_materialization SELECT <ts_now>, device_id,  week, avg_val FROM my_table;

5. refresh the table

   REFRESH TABLE my_table_rollup_materialization;

6. Update/create the "materialized" view, only if the insert was successful:

   CREATE OR REPLACE VIEW my_table_rollup_materialized AS 
   SELECT
   device_id,
   week,
   avg_val
   FROM my_table_rollup_materialization
   WHERE update = <ts_now>;

7. Delete all old records from the materialization table:

   DELETE FROM my_table_rollup_materialization WHERE update < <ts_now>

... of course it would be much nicer to have syntactic sugar for this within CrateDB, as this approach also doesn't really work with user privileges

[ckurze]

Another option would be the implementation of something like a MERGE statement, i.e. recalculate the new data in the source table and merge it into a target table:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/MERGE.html

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM hr.employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

[proddata]

Another option would be the implementation of something like a MERGE statement, i.e. recalculate the new data in the source table and merge it into a target table:

Is not really an alternative to materialized views, but rather to INSERT INTO ... SELECT ... ON CONFLICT SET ... which already cover a lot of the MERGE use cases.

[proddata]

Draft proposal following PostgreSQL beahviour ⚠️ Feel free to edit ⚠️ No decision to implement

The propose approach largely follows the PostgreSQL implementation of materialized views.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views. Criteria

• A User can define a materialized view using a CREATE MATERIALIZED VIEW statement.

  CREATE MATERIALIZED VIEW my_mat_view AS SELECT * FROM my_table;

• A User can optionally specify column definitions for the materialised views.
• If they are not specified, the behaviour is the same as CREATE TABLE AS
• The SELECT query is executed and used to populate the view right after the view is created.
• The statement requires DDL privileges.
• A User can update/refresh the data in a materialized view using REFRESH MATERIALIZED VIEW.

  REFRESH MATERIALIZED VIEW my_mat_view;

• The refresh statement can either succeed or fail and return an appropriate value.
• If the refresh fails, the latest full refresh is kept.
• If the refresh succeeds, all other data is cleaned up.
• There is the option to specify a number of replicas for a materialized view with WITH (number_of_replicas = '0-1') to specify how often it is replicated.
• There is the option to ignore the statement if the view already exits: IF NOT EXISTS.
• There is the option to disable the materialisation on creation with specifying WITH [ NO ] DATA.
• If there is no data in the materialised view, the view cannot be queried.
• If there is a materialization in progress the view returns the latest completed materialization.
• There is information in an information schema table that hold the last timestamp the view has been materialized.

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    AS query
    [ WITH [ NO ] DATA ]

• There is no automatic refresh of a materialized view
• If the table or views that the materialized view is referring to don’t exist anymore or don’t hold the requested data (e.g. columns missing), the view does not get invalidated, it just can’t get updated anymore.
• There is no delta-refresh or continuous refresh. Every refresh is a complete new materialization.

[hlcianfagna]

Related with https://github.com/crate/crate/issues/10731 and https://github.com/crate/crate/issues/12218

[ckurze]

Another option would be the implementation of something like a MERGE statement, i.e. recalculate the new data in the source table and merge it into a target table:

Is not really an alternative to materialized views, but rather to INSERT INTO ... SELECT ... ON CONFLICT SET ... which already cover a lot of the MERGE use cases.

Always something that could be positioned as "on-demand materialized view" and gives a good amount of freedom what do with the data in the "materialized view".

[robd003]

Being able to define an automatic refresh interval (once every 15 mins / once every hour) would be really helpful. Being able to tweak the refresh rate w/o having to destroy & recreate the materialized view would also be great.

[helenap]

I could really use materialized views for complicated computations that are used repeatedly in other queries. Could you please make this a priority in 5.8?

[hlcianfagna]

Thank you for the feedback @helenap , while waiting for this to be implemented I would suggest either using the new SQL job scheduler in CrateDB Cloud or dbt's table/incremental materializations.