3 ms·

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.

rtfeldman 14y ago
Good news! It's slated for the next release. :) http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-materialized-views/ http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...
- fdr 14y ago
  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.
  - jacques_chester 14y ago
    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.
- jeltz 14y ago
  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.
X-Istence 14y ago
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 http://wiki.postgresql.org/wiki/Materialized_Views Worth looking at.
- polskibus 14y ago
  It's not the same performance-wise. Materialized views are required to do ROLAP analytics efficiently.
  - sendob 14y ago
    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. "

rtfeldman 14y ago

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

fdr 14y ago
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.
- jacques_chester 14y ago
  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.
jeltz 14y ago
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.

X-Istence 14y ago

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 http://wiki.postgresql.org/wiki/Materialized_Views Worth looking at.

polskibus 14y ago
It's not the same performance-wise. Materialized views are required to do ROLAP analytics efficiently.
- sendob 14y ago
  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. "