On 08/10/13 19:58, Alban Hertroys wrote:
On Oct 8, 2013, at 9:36, Toby Corkindale
<toby.corkindale@xxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
