How to improve performance in reporting database?
I have a daily job that pushes data from the production database into
the reporting database, which right now, is an exact copy.
I have a webapp that builds lots of reports for users. Most of these
reports involve elaborate joins of lookup tables and lots of summations,
and they take too long to run, even after using everything I know to
tune the queries.
Since I know this is a read-only data, it seems like I should be able to
speed everything up dramatically if I run the queries offline and then
save the results into new tables. Then the web app could just grab the
cached results out of these new tables and then spit them out quickly.
I've heard people talking about using "materialized views" for this, but
that was with Oracle.
What's the postgresql way here?
More generally, any advice on running reporting databases well is
welcome.
Matt
Matthew Wilson wrote:
I've heard people talking about using "materialized views" for this, but
that was with Oracle.
You can build those manually with PostgreSQL if you really want them:
http://wiki.postgresql.org/wiki/Materialized_Views
The fundamental architecture is sound for a lot of problems in this
area, you just have to figure out how to build them efficiently. In
your case, you might just consider if there's a way way to update the MV
in batches, rather than rely on triggers to keep the data up to date,
after each data import.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
On Thu, Jul 22, 2010 at 8:45 AM, Matthew Wilson <matt@tplus1.com> wrote:
I have a daily job that pushes data from the production database into
the reporting database, which right now, is an exact copy.I have a webapp that builds lots of reports for users. Most of these
reports involve elaborate joins of lookup tables and lots of summations,
and they take too long to run, even after using everything I know to
tune the queries.Since I know this is a read-only data, it seems like I should be able to
speed everything up dramatically if I run the queries offline and then
save the results into new tables. Then the web app could just grab the
cached results out of these new tables and then spit them out quickly.I've heard people talking about using "materialized views" for this, but
that was with Oracle.What's the postgresql way here?
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
They're kinda roll your own, but they're not that hard to do.
More generally, any advice on running reporting databases well is
welcome.
Throw more drives and RAM at the problem, and use materialized views.
Also you're often better off with fewer faster cpus than more slower
ones for reporting servers (the opposite of OLTP where number of cores
is far more important.)
On Thu, Jul 22, 2010 at 2:31 PM, Greg Smith <greg@2ndquadrant.com> wrote:
You can build those manually with PostgreSQL if you really want them:
http://wiki.postgresql.org/wiki/Materialized_Views
Another thing to consider... In our case we use a materialized view to
keep track of counts of various things that are expensive to count,
such as number of members of a specific account, or number of times a
particular URL was viewed. What we end up with is many processes
trying to update the same counter row, and we end up with lots and
lots of lock contention.
Which reminds me... Greg, are you done with your book yet and when can
I get you back in here to help with my locking problems? ;-)