Search Postgresql Archives

Re: Partitioned "views"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Greetings,

* Job (Job@xxxxxxxxxxxxxxxxxxxx) 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 to make 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

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux