Hacker News Reader

Comment by fiatjaf | Hacker News Reader

Ok, I'm not a database manager for enormous projects, so these changes may be great, but I don't understand them and don't care about them. Postgres is already the most awesome thing in Earth to me.

Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.

View on HN

The work that has been done on transition tables is intended to enable future work on automatically updated materialized views; the idea is that the system will automatically derive a query to update the view based on the deltas between the set of old rows and the set of new rows. That will take more work, though. I do agree it would be valuable. It's possible to set up similar things by writing your own triggers, and having transition tables available in PL/pgsql will make it easier, but it's not necessarily easy to figure it all out by hand for a complex view involving joins and aggregates.

Thank you.

I wonder why not to go for a changelog-based implementation. Instead of modifying the materialized view directly, write the changes into a changelog, and then update the matview in the background. More efficient, less locking issues, etc.

How do you know when the change will affect the matview? I don't know the syntax for creating it off the top of my head, but imagine a materialized view limited to the top ten records of a table.

I assume there will be some limitations what matviews can be updated automatically/ incrementally. E.g. updatable views (regular, not materialized) have a range of such restrictions, see: https://www.postgresql.org/docs/devel/static/sql-createview....

Hopefully, some of those restrictions will disappear, because matviews are often used to store pre-aggregted data, so the restriction on GROUP BY would be unfortunate.

Some work started in this direction. I didn't follow closely the whole thread but I don't think that got commited in PG10.

https://www.postgresql.org/message-id/flat/20170119213859.GA...

More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_serve...

Postgres has been amazing in shipping the foundation required to deliver complex feature.. Logical Replication is an example of it, all the piece commited in the last 6y allowed to make this patch achievable.

How do you mean? Couldn't you use a trigger to update the view?

Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.

Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.

IMHO triggers are almost always best avoided. There are some exceptions but most of the time you want changes to be explicit not happening "by magic" behind the scenes as a side-effect of something else.

A trigger on what? Every update, insert, delete, etc.? On every table in the view?

Even if that is possible, it may be a major performance killer. This has to be done internally, I think.

I am not sure it necessarily would be that bad. After all foreign keys are implemented with triggers and they are usually fast enough. You just need to write trigger functions which are fast enough.

It is possible, that's how I do it, and it doesn't really affect performance at all. You just don't do it real time, the triggers just kick an id into a queue and you have a background task work the queue updating the materialized table from the view it's a copy of just for the id's in the queue.

So what would make the MV self-update?

Reply on news.ycombinator.com