⏱️ Taming QuestDB Materialized View Refresh Frequency

Ever implemented a materialized view in QuestDB only to find it refreshing constantly, even when you just want daily snapshots? You're not alone. By default, QuestDB materialized views use INCREMENTAL refresh mode — which means they update as soon as new data arrives in the base table.

This is great for real-time dashboards… but wasteful if you only need a daily roll-up for analysis.

🔧 The Problem: Too Many Refreshes

Let’s say you have a trades table with market data arriving every second. If you create a materialized view like this:

CREATE MATERIALIZED VIEW trades_latest_1d AS (
  SELECT
    timestamp,
    symbol,
    side,
    last(price) AS price,
    last(amount) AS amount,
    last(timestamp) AS latest
  FROM trades
  SAMPLE BY 1d
)
PARTITION BY DAY;

It will refresh every time new trades are inserted — hundreds or thousands of times per day. That's the default behavior of INCREMENTAL refresh mode.

✅ The Solution: Use incremental_timer Instead

You can control how often your materialized view refreshes by specifying a scheduled interval. Here's how:

CREATE MATERIALIZED VIEW trades_latest_1d
  REFRESH START '2025-05-30T00:00:00.000000Z'
  EVERY 1d
AS (
  SELECT
    timestamp,
    symbol,
    side,
    last(price) AS price,
    last(amount) AS amount,
    last(timestamp) AS latest
  FROM trades
  SAMPLE BY 1d
)
PARTITION BY DAY;

This switches the view to incremental_timer mode, which means it will only refresh once per day, at midnight UTC. You can adjust the EVERY clause to suit your needs (e.g. 1h, 5m, 7d, etc).

🔍 How to Check If It’s Working

After creating the view:

SELECT * FROM materialized_views();

Look for:

• refresh_type = incremental_timer
• last_refresh_start_timestamp = null (initially)

Then, after the first scheduled refresh (in this case, at midnight), the view will populate and the timestamp fields will be updated.

🧪 If You Want More Frequent Updates

If you still want intraday updates but less noise, you could do:

REFRESH START '2025-05-30T00:00:00.000000Z'
EVERY 5m

Now the view only refreshes every 5 minutes — much saner than every insert.

🧠 TL;DR

📝 Final Thoughts

QuestDB’s default behavior favors low-latency use cases. But if you're building a daily view of activity, using REFRESH EVERY gives you total control over how often data is materialized — saving compute, reducing WAL activity, and improving predictability.

Perfect for finance, monitoring, and anyone with large data volumes but low update frequency requirements.