Обсуждение: [GENERAL] Partitioned "views"

Greetings,

* Job (Job@colliniconsulting.it) wrote:
> we use a materialized view to aggregate datas from a very big table containing logs.
> The source table is partitioned, one table for a day.
>
> Since the refresh of the materialized view seems to grow a lot about timing, we would like to know if it is pssible
tomake a "partitioned materialized views", in order to update *only* current day logs aggregation, leaving old days
untouchable.

You can do this, but you have to write the SQL for it yourself, there's
no current way in PG to ask for a materialized view to be partitioned.

The mat view takes longer and longer to update because it runs the full
query.  What you really want to do is have a side-table that you update
regularly with appropriate SQL to issue UPDATE statements for just the
current day (or whatever).

Another approach which can be used is to have a trigger which will
automatically update the side-table for every change to the 'big' table,
but that will mean every update on the 'big' table takes longer and if
the updates are happening concurrently then you may run into locking,
and possibly deadlocking if it's done wrong.

Thanks!

Stephen

Hi Stephen.

>>The mat view takes longer and longer to update because it runs the full query.  What you really want to do is have a
side-tablethat you update regularly with appropriate SQL to issue UPDATE statements for just the current day (or
whatever).

If correct, i leave only last datas into "side-table" and syncronize only these datas in the materialized view.
If i delete datas from the side-table (ex- truncate) during the next materialized view update they will be lost or
remainintact? 

Thank you
Francesco

Greetings,

* Job (Job@colliniconsulting.it) wrote:
> >>The mat view takes longer and longer to update because it runs the full query.  What you really want to do is have
aside-table that you update regularly with appropriate SQL to issue UPDATE statements for just the current day (or
whatever).
>
> If correct, i leave only last datas into "side-table" and syncronize only these datas in the materialized view.
> If i delete datas from the side-table (ex- truncate) during the next materialized view update they will be lost or
remainintact? 

I was suggesting that you, essentially, write your own SQL to have a
materialized view, *not* use the PG materialized view system.

In other words, the 'side-table' that you create would be *your*
materialized view, but to PG, it'd just look like a regular table.

You can't modify a PG materialized view.

Thanks!

Stephen