Hacker News Reader

Comment by ArneVogel | Hacker News Reader

Couldn't you use views for that? Everything you described sounds like a view to me. Whats the difference between them?

Traditional views are just stored queries that are built on the fly, so there's no performance improvement. Materialized views are stored/cached on disk and will give you some performance improvements but you'll have deal with when/how the cache is updated and the resulting penalties.

Yep, Postgres grants flexibility (you can build temporal materialized views + other constructs some other database servers refuse as nondeterministic) but with the burden of having to refresh the materialized view yourself. This was a little bit of a surprise after doing materialized views in MSSQL and Oracle, which treat 'em more like big old indexes updated in lockstep with the base tables.

Reduced index size is the most important one, IMHO.

Once your indices are larger than than your available memory, write and read performance plummets. This obviously only applies to fairly large tables, but in our case it will be a godsend.

The problem with views in Postgres is that they are not updated on the fly, they require manual updating. For real time data, it's not a very useful feature.

Erm I think you're thinking of materialized views. Regular views are essentially stored queries.

https://www.postgresql.org/docs/10/static/rules-materialized...

Oh, yes. I tend not to gravitate towards views, so got myself mixed up there :).

Reply on news.ycombinator.com