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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
