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
