Hacker News Reader

Comment by polskibus | Hacker News Reader

All that is missing in Postgres is materialized views feature. I would happily contribute to a kickstarter project or similar for implementing them in Postgres.

View on HN

Good news! It's slated for the next release. :) http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...

I think most people used to a materialized view features in proprietary databases will find the incarnation of Materialized Views as committed quite limiting...nevertheless, it seems important to me that this foundational work has been integrated.

In particular, incremental updates of the materialized view are not yet a feature. That said, a lot of hang wringing has been going on in the lists on how to build on this work, so it looks like this whole subsystem will receive some attention.

That's how the Postgres team works. They tend to start with the primitives and build a bit more of the niceties in each release. And they do so very carefully, which is important.

People forget that this is true of every software system ever. No successful system ever starts with every feature it's going to have.

That is just the initial work to provide the basic infrastructure (and a solution to a couple of the simplest use cases) and not fully fledged materialized views.

In 9.3 materialized views will only be refreshable by recomputing the entire view and they will be locked while they are being refreshed. To shorten the lock you would have to swap the view with ALTER MATERIALIZED VIEW.

According to the PostgreSQL wiki there is a way to get something like a materialized view using triggers: http://wiki.postgresql.org/wiki/Materialized_Views

Worth looking at.

It's not the same performance-wise. Materialized views are required to do ROLAP analytics efficiently.

I don't think this is a very helpful or accurate statement. I would encourage people to actually investigate things for themselves. Generally you do trade some performance (depending upon implementation, they can be closer to the metal so to speak ) for flexibility ( again depending upon implementation ).

Consider this documentation from oracle, a very popular materialized view implementation:

"When changes are made to the master table or master materialized view using DML, an internal trigger records information about the affected rows in the materialized view log. This information includes the values of the primary key, rowid, or object id, or both, as well as the values of the other columns logged in the materialized view log. This is an internal AFTER ROW trigger that is automatically activated when you create a materialized view log for the target master table or master materialized view. "

Reply on news.ycombinator.com