⚡ Materialized Views

Sentio(Pro) supports Materialized Views (MVs) to help optimize query performance for your dashboards and data analysis.

What is a Materialized View?

Think of a Materialized View (MV) as a special kind of table that stores the pre-calculated results of a specific query.

• Regular View: A saved query definition. Every time you query the view, the database runs the underlying query on the original data. This guarantees fresh data but can be slow for complex queries.
• Materialized View: Stores the actual results of the query. Querying the MV is very fast (like querying a small table) because the complex calculations are already done. However, the data is only as fresh as the last time the MV was updated (refreshed).

In short, MVs trade potentially delayed data freshness for significantly faster query speeds, making them ideal for complex aggregations or frequently accessed reports.

Example Use Case: Daily cbETH Transfer Summaries

Let's say you are using a Sentio processor to monitor Coinbase's cbETH token , and your processor emits a Transfer event log every time cbETH is transferred.

Problem: You want a dashboard showing daily transfer statistics:

• Total volume transferred per day.
• Number of unique sending addresses per day.
• Number of unique receiving addresses per day.

If you have millions of transfer events, calculating these summaries directly from the raw event log table (e.g., Transfer) every time the dashboard loads can be very slow.

Solution: Create a Materialized View to pre-calculate these daily summaries.

Here's how you can do it in Sentio:

1. Define Query: Enter the SQL query that defines the data you want to materialize, and just Click Save View to create a materialized view.

2. Configure Refresh: Set up the refresh schedule (e.g., how often the view should be updated with new data from the source table).

3. Create & Verify: Submit the view creation request and wait for it to complete. You should see the new MV listed once it's ready.

4. Utilize: Now, your dashboard doesn't need to run the slow, complex query on the potentially huge source table. Instead, it can run a super-fast query against the small, pre-aggregated Materialized View.

5. Update MV (Optional): You can also modify the query or refresh settings later by clicking the edit icon if needed.

When to Use Materialized Views

Consider using Materialized Views when:

• You have queries that are slow due to complex joins, aggregations (SUM, COUNT, AVG), or processing large amounts of data.
• You need to power dashboards or reports that are accessed frequently.
• You can tolerate the data being slightly delayed (not perfectly real-time), based on your chosen refresh schedule.

Notes & Limitations

• ORDER BYRequired: When creating the view, you must explicitly specify an ORDER BY clause using column aliases. Expressions cannot be used directly in the ORDER BY clause.
• Minimum Refresh Interval: The minimum refresh interval is 10 minutes.
• Maximum Rows: A materialized view can generate a maximum of 1,000,000 rows.

Managing MVs via API

In addition to the UI, you can programmatically manage your Materialized Views using the Sentio API. This allows for automation and integration with your workflows.

The API provides endpoints for:

• Creating or updating MVs (Save Refreshable Materialized View)
• Listing existing MVs (List Refreshable Materialized Views)
• Getting the status of a specific MV (Get Refreshable Materialized View Status)
• Deleting MVs (Delete Refreshable Materialized View)

Refer to the Sentio API Reference for detailed information on these endpoints.