4 ms·

Yes this is the exact situation where sql falls short. You can't make cross-table indexes to serve OLAP-esq queries, and the most recent X is the common one for

Yes this is the exact situation where sql falls short. You can't make cross-table indexes to serve OLAP-esq queries, and the most recent X is the common one for pagination in applications. I prefer to denormalize manually at write time in a transaction, rather than use triggers or materialized views.

ako 3y ago
Why do you prefer manually doing this rather than using materialized views? Materialized views seem easier to create and maintain?
- tlarkworthy 3y ago
  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
  - aidos 3y ago
    Agreed. Matarialized views will be limited in use until we get nice incremental updating versions.
  - gen220 3y ago
    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.
    - tlarkworthy 3y ago
      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.

ako 3y ago

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

tlarkworthy 3y ago
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
- aidos 3y ago
  Agreed. Matarialized views will be limited in use until we get nice incremental updating versions.
- gen220 3y ago
  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.
  - tlarkworthy 3y ago
    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.