cribwi

Great approach and good write-up! I’ve implemented a similar technique before on PostgreSQL, but with the materialisation in the application backend. Still works like a charm.

>So instead, we created a special kind of trigger that tracks the minimum and maximum times modified across all the rows in a statement and writes out the range of times that were modified to a log table. We call that an invalidation log.

Does this invalidation log also take into account cases where the view has an aggregate that is based on data from a bucket other than itself? For example, a lag() or lead() might be used to calculate a delta compared to the previous bucket. Then, if a data point inside bucket 1 is added into the realtime table and bucket 1 is invalidated and re-materialised, for integrity reasons also bucket 2 needs to be re-materialised?

In some cases for removing repeating (intermediate) calculations, I generally find it easier to use a lateral join (in postgres), like

    select
        title,
        country,
        avg(salary)         as average_salary,
        sum(salary)         as sum_salary,
        avg(gross_salary)   as average_gross_salary,
        sum(gross_salary)   as sum_gross_salary,
        avg(gross_cost)     as average_gross_cost,
        sum(gross_cost)     as sum_gross_cost,
        count(*)            as emp_count
    from
        employees,
        lateral ( select
            (salary + payroll_tax)                   as gross_salary,
            (salary + payroll_tax + healthcare_cost) as gross_cost
        ) employee_ext
    where
        country = 'usa'
        and gross_cost > 0
    group by  title, country
    having    count(*) > 200
    order by  sum_gross_cost
    limit 3;

You can assign a custom shortcut to it via Settings > Keymap and then the action "Main Menu > File > Local History". I myself have assigned the shortcut control+shift+=

I know that specific story from the book "How not to be wrong", by Jordan Ellenberg. It was nice to read it from a different angle in this article