Re: Postgresql Materialized views

On Sat, 12 Jan 2008, Jean-Michel Pour� wrote:

> You are well aware of Jonathan Gardner preliminary work:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

There's also PostgreSQL::Snapshots ; intro at
http://cunha17.cristianoduarte.pro.br/postgresql/snapshots.en_us.php and
main project page at http://pgfoundry.org/projects/snapshot/

> I run a 400.000+ message board using PhpBB 3.0...My database handles 10
> to 20 queries every second. There are 100 selects for 1 INSERT. But my
> database could well handle over 500 queries a second using materialized
> views.

I hope you don't take this the wrong way, but if you can't hit 500
queries/second on that volume of messages I would guess that something is
wrong with either the design scalability of the PhpBB software running
against a PostgreSQL database or some detail of how you've got it setup.
A quick read suggests it's not unusual for people to drop PhpBB and use
something vBulletin instead exactly because of PhpBB's issues handling
larger communities.  It's probably out of date but I found the discussion
of query optimization for larger message boards at
http://www.phpbb.com/community/viewtopic.php?t=135383 to be informative on
this topic.

> At my level, here are my plans:
> 1) Publish some long query LOGs from my database, longuer than 30
> milliseconds.

Rather than chasing after core product features that are some distance
off, I think what might be more productive for you in the short term is to
collect this information--including EXPLAIN ANALYZE plans--and include it
along with information about your server and how the postgresql.conf is
setup in a message to the performance list.  That might get you immediate
suggestions.  In addition to "query is badly written/indexed for
PostgreSQL" (which is another potential side to the query issues discussed
in the phpbb forum topic I referenced), problems you might not have caught
that could be nailing you include things like not allocating enough memory
for use by the database and tables not being analyzed frequently enough.

I would love to have materialized views in the core database.  But it's a
ways off no matter what, is moderately hard to accomplish, and you can
emulate some of the benefits using things like Gardner's trigger-based
approach.  All of that makes it harder to kick off such a project.  I
don't think you need to convince anyone that it's important--the
occasional person has been screaming about needing this feature for years
now.  The real question is who cares enough about the feature that it's
worth their trouble to fund development, and I'm not sure whether your
personal attempts to rouse demand will impact that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD