Re: Many, many materialised views - Performance?

On Oct 8, 2013, at 9:36, Toby Corkindale <toby.corkindale@strategicdata.com.au> wrote:

> Hi,
> I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of
smalltables. 
>
> I'm wondering if materialised views will fare better, or if they too create a lot of fluff in pg_catalog and many
fileson-disk? 


A materialised view is basically a view turned into a table, with some fluff around it to keep the data it contains
up-to-datewhen the underlying data gets modified. From the 9.3 documentation it appears that this step isn't done
automaticallyyet, but instead you have to issue a REFRESH MATERIALIZED VIEW command (meaning it's not much fluff). 

One of the main purposes of materialized views is to have differently organised versions of the same data available (to
allsessions and for a longer time than, say, temporary tables) that are, for example, more convenient/performant for
reporting.
In many cases, materialized views are a denormalization of your data and often grouped and aggregated.

Having hundreds of thousands of materialized views is going to hurt catalog performance just as much as having that
manytables, with the (manual) maintenance of keeping the data up-to-date added to that. 

Whether that improves or deteriorates performance depends on how you plan to use them. I can say though that it's
unusualto have hundreds of thousands of them; for what purpose do you intend to use them? 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.