On 08/10/13 19:58, Alban Hertroys wrote:
> 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 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?
Hi Alban,
I had wondered if that was the case -- that they'd be implemented
similarly to tables under the hood.
In this instance, we have a lot of queries that build certain aggregate
results, which are very slow. The queries were initially all implemented
as views, but then we started doing a type of materialising of our own,
turning them into tables with CREATE TABLE AS SELECT ....
This does make the results very fast to access now, but the side effect
is a vast number of (very small) tables.
It would be better to use built-in materialised views, because it's a
standard way to do it, but it sounds like it won't solve the
too-many-tables-in-the-system problem.
Realistically we need to go back and use a different approach
altogether, but you know how it is with long-running production systems.
Significant changes can be hard to push through.
Toby