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