5 ms·

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,

by Refefer 13y ago

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.

masklinn 13y ago
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;h=3bf3ab8c563699138be02f9dc305b7b77a724307 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...
- jeltz 13y ago
  The cycles are 12 months, otherwise you are correct.
jkbyc 13y ago
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.
- daigoba66 13y ago
  The implementation used by MSSQL is pretty restrictive in terms of what kind of query can be indexed. But it works rather well.
megaman821 13y ago
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.

by Refefer 13y ago

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.

masklinn 13y ago

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;h=3bf3ab8c563699138be02f9dc305b7b77a724307 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...

jeltz 13y ago
The cycles are 12 months, otherwise you are correct.

jkbyc 13y ago

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.

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

megaman821 13y ago

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.