Hacker News Reader

Comment by Refefer | Hacker News Reader

Materialized Views! Well, sort of; it appears they don't automatically refresh when data has been updated in the underlying tables ala Oracle's implementation, but this gets you rather close.

View on HN

FWIW, since Posgtres has hard version cycles (6 months I believe?) the point was to have a working base out and about, future versions will likely add auto-update:

> This is a minimal implementation, but should still be useful in many cases. Currently data is only populated "on demand" by the CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW statements.

> It is expected that future releases will add incremental updates with various timings, and that a more refined concept of defining what is "fresh" data will be developed. At some point it may even be possible to have queries use a materialized in place of references to underlying tables, but that requires the other above-mentioned features to be working first.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...

The cycles are 12 months, otherwise you are correct.

Implementing efficient updates of materialized views is difficult. They have to support recursive queries, aggregation, and probably even recursive views that they list as new in "Additional Features."

Algorithms for updates of materialized views exist since at least 20 years but maybe they don't cover all these cases efficiently.

The implementation used by MSSQL is pretty restrictive in terms of what kind of query can be indexed. But it works rather well.

That is awesome. I think caching at the ends of your app is a lot more elegant and easier to maintain than littering your app with cache logic. I use Varnish for caching HTTP responses and have been waiting forever for more databases to support materialized views.

Reply on news.ycombinator.com