Re: Many, many materialised views - Performance?

On Oct 8, 2013, at 9:36, Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)au> wrote:

> Hi,
> I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables.
>
> I'm wondering if materialised views will fare better, or if they too create a lot of fluff in pg_catalog and many files on-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-date when the underlying data gets modified. From the 9.3 documentation it appears that this step isn't done automatically yet, 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 all sessions 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 many tables, 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 unusual to 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.