News for hackers. Stuff that matters.
Re:Some SQL Tricks of an Application DBA by gavinray on Wednesday July 29, 2020 @08:12PM (#23992980)
I'm someone with limited DB wizardry, but a large admiration for Postgres so take this for what it's worth:

It's my understanding that there is a proposed feature, with a proof-of-concept floating around, called "Incremental View Maintenance", which is auto-update of dependent Materialized Views when their dependent base-tables change.

https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...

  Materialized view with IVM option created by CRATE INCREMENTAL MATERIALIZED VIEW command. Noted this syntax is just tentative, so it may be changed.

  When a materialized view is created, AFTER triggers are internally created on its all base tables.

  When the base tables is modified (INSERT, DELETE, UPDATE), this view is updated incrementally in the trigger function.
I think there are some performance/technical things that are getting sorted out with this, but it would be a killer feature.

It's the one thing I wish Postgres had that it doesn't. You have to use triggers to do this currently.

Re:Some SQL Tricks of an Application DBA by Normal_gaussian on Wednesday July 29, 2020 @09:12PM (#23993323)
Yes, this is a killer feature. There are many instances of what are essentially application side caches that try (and often fail) to do this; its non-trivial to do right, and I am greatly looking forwards to it.

In particular, an event-sourced world becomes much easier to implement and maintain with the DB handling all the incremental magic.