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