5 ms·

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

by fiatjaf 10y ago

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.

petepete 10y ago
How do you mean? Couldn't you use a trigger to update the view?
- phamilton 10y ago
  Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.
- mozumder 10y ago
  Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.
- ams6110 10y ago
  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.
- okket 10y ago
  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.
  - jeltz 10y ago
    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.
  - gnaritas 10y ago
    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.
  - petepete 10y ago
    So what would make the MV self-update?
rhaas 10y ago
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.
- fiatjaf 10y ago
  Thank you.
- pgaddict 10y ago
  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.
  - foota 10y ago
    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.
    - pgaddict 10y ago
      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.html#sql-createview-updatable-views 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.
rachbelaid 10y ago
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.GA1838%40localhost#20170119213859.GA1838@localhost https://www.postgresql.org/message-id/flat/20170119213859.GA... More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_server_roadmap#SQL_Features 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.

by fiatjaf 10y ago

petepete 10y ago

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

phamilton 10y ago
Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.
mozumder 10y ago
Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.
ams6110 10y ago
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.
okket 10y ago
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.
- jeltz 10y ago
  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.
- gnaritas 10y ago
  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.
- petepete 10y ago
  So what would make the MV self-update?

rhaas 10y ago

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.

fiatjaf 10y ago
Thank you.
pgaddict 10y ago
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.
- foota 10y ago
  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.
  - pgaddict 10y ago
    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.html#sql-createview-updatable-views 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.

rachbelaid 10y ago

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.GA1838%40localhost#20170119213859.GA1838@localhost https://www.postgresql.org/message-id/flat/20170119213859.GA... More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_server_roadmap#SQL_Features 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.