Re: How to improve performance in reporting database?

On Thu, Jul 22, 2010 at 8:45 AM, Matthew Wilson <matt(at)tplus1(dot)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.)