Dear Friends,
In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs.
You are well aware of Jonathan Gardner preliminary work:
http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html
When do you plan to add MATERIALIZED VIEWS to PostgreSQL?
This would be major improvement ni the case of Web applications.
I run a 400.000+ message board using PhpBB 3.0. After optimization, some
queries still need 30 millisecond to run. With Materialized views, it
should be possible to drive these queries to 1 millisecond. This means
that in some situations a PostgreSQL backend could handle 10 times more
queries.
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.
At my level, here are my plans:
1) Publish some long query LOGs from my database, longuer than 30
milliseconds.
2) Write some PL code to demonstrate the interest in Materialized Views.
Publish benckmarks showing time improvement, like 1 milisecond. 30x
faster.
3) Then wait for someone on Hackers mailing list to pick-up this
important issue and integrate Materialized views in PostgreSQL schema
and SQL language.
Any information and discussion about materialized views is welcome.
Please pick-up this important issue for developpers. There is no need to
concentrate on complex issues, when handling materialized views could
boost somme web apps. by a factor of 10 or more.
Kind regards and happy new year.
I hope that 2008 will be the year of materialized views.
Jean-Michel Pouré