Why do you prefer manually doing this rather than using materialized views? Materialized views seem easier to create and maintain?

Because they are o(n) complexity to refresh so either you settle for eventual complexity or have expensive writes. By forwarding just the index data to the right table you maintain an consistent idiomatic index at 0(1) write cost

Agreed. Matarialized views will be limited in use until we get nice incremental updating versions.

Have you considered implementing this with database triggers instead of in your application logic?

Requires a bit of brainpower to set up a system around it, but it makes your application logic dramatically simpler.

(You don't have to remember to update `foo.bar` every time you write a function that touches `moo.bar`, and if you run migrations in SQL, the updates will also cascade naturally).

It's really high up on my personal wish list for Postgres to support incrementally-materialized views. It doesn't seem like it would be impossible to implement (since, as I suggested, you can implement it on your own with triggers), but IDK, I assume there are higher-priority issues on their docket.

considered and rejected because triggers complicate other database operations like restores. Triggers to me are like global operations, they really make a strong statement about storage which does not tend to be true over longer time horizons when you consider all the stackeholders of the data storage system. They make sense as an application feature, but they are implemented as globals, and this is where the problems begin.