1

There is a table

create table container_operations
(
    id uuid not null primary key,
    plant_id uuid not null,
    container_id uuid not null,
    contragent_id uuid,
    quantity integer not null,
    created_at timestamp not null
)

And there is a materialized view with rolling totals which contains container operations projections with debt and remains corresponding to each operation

create materialized view rolling_container_operations as (
    select
        id,
        plant_id,
        container_id,
        contragent_id,
        quantity,
        created_at,
        sum(-quantity) over (
            partition by plant_id, contragent_id
            order by created_at rows between unbounded preceding and current row
        ) contragent_debt_quantity,
        sum(quantity) over (
            partition by plant_id, container_id
            order by created_at rows between unbounded preceding and current row
        ) container_remaining_quantity,
        sum(-quantity) over (
            partition by plant_id, container_id, contragent_id
            order by created_at rows between unbounded preceding and current row
        ) contragent_container_debt_quantity
    from container_operations
)

And there is a problem with refresh materialized view concurrently for relatively big number of operations. I thought to create 2 materialized views in order to differentiate refresh frequency. One for old data and one for last year. And then to create a view which will do union all for materialized views. But I can't do so for rolling totals.

What can I do in order to reduce refresh time? Maybe I am moving in wrong direction?

Sidequestion: How can I search remains and debts for some day if there was no container operations present at container_operations table for that day? Should I use k-nearest approach with gist index and sql like

select ...
from ...
left join lateral (
    select ... 
    from ...
    order by ... <-> ...
    limit 1
) on true
whsv26
  • 63
  • 1
  • 6
  • You can split it into multiple views. You will need to apply the same window functions after the `union all`, but the data should hopefully be much smaller for that. Have you considered a daily history table to address this and your side question? Also, do you have indexes to cover your windows? – Mike Organek Aug 19 '20 at 17:42

0 Answers0