Materialized views are obviously useful

Compliments aside, where this article stops is where things get exciting. Postgres shines here, as does Vitess, Cassandra, ScyllaDB, even MongoDB has materialized views now. Vitess and Scylla are so good, it’s a shame they’re not more popular among smaller startups!

What I haven’t seen yet is a really good library for managing materialized views.

This technique is very well supported in the big commercial engines. In MSSQL's Indexed View case, the views are synchronously updated when the underlying tables are modified. This has implications at insert/update/delete time, so if you are going to be doing a lot of these you might want to do it on a read replica to avoid impact to production writes.

https://learn.microsoft.com/en-us/sql/relational-databases/v...

https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...

With PostgreSQL the materialized view won't be automatically updated though, you need to do `REFRESH MATERIALIZED VIEW` manually.

'There are a few startups these days peddling a newfangled technology called “incremental view maintenance” or “differential dataflow”. '

Incremental view maintenance can change recomputation cost of view updates from O(N) to O(1). DBSP is based on z-sets, a generalization of relational algebra. The paper won best paper at SIGMOD. There is a startup, Feldera, commercializing it.

This is just ignorance to dismiss as 'new fangled'.

IMO a slept-on database feature is table partitioning to improve query performance. If you have a frequently-used filter field that you can partition on (e.g. creation timestamp), then you can radically improve query performance of large databases by having the DB only need to full-scan the given partitions. The database itself manages where records are placed, so there is no additional overhead complexity beyond initial setup. I've only used this for PostgreSQL, but I assume that other databases have similar partition mechanisms.

> And then by magic the results of this query will just always exist and be up-to-date. You can just query it and it’s instant;

This is not true of a materialized view, which needs to be refreshed after changes. PostgreSQL does not support incremental refresh out of the box, so you must refresh the entire view. A standard view would be "magically" up to date, as it is going to defer execution to runtime.

A better, canonical use case for matviews is subdividing the projects table into project-tasks-reporting by month by customer by market vertical, or what have you.

All said, still not a massive needle-mover

So I just pre-emptively break "normal form" and maintain counts in the database right from the start. I either update it with multiple stmts everytime in a txn, or with triggers. In sqlite I prefer triggers mostly because I know the operation and edge cases of sqlite trigger impl better than I know other DBs'.

Caching can just be invalidation-based in this case.

Isn't their tech to address that, like golang's "singleflight"?

Of course, that assumes you somehow have an event when interesting things change, and that you've gone through the effort of figuring out how you want to record (or update the record) for later.

[0] https://stack.convex.dev/how-convex-works

https://zero.rocicorp.dev/docs/introduction

IVM is what allows the core feature of our DX: that users can just do a fairly large query with sorts and joins, like:

  zero.query.posts.where('authorID', 42)
    .orderBy('created', 'desc')
    .limit(1000)
    .related('comments',
      c => c.orderBy('created', 'desc').limit(10))

TanStack DB also uses IVM, but only client-side currently.

If you are interested in such things, you can take a poke around the source here: https://github.com/rocicorp/mono/tree/main/packages/zql. Or come find us in Discord: https://discord.rocicorp.dev/

I'm not affiliated with any of these names, I'm just really interested in IVM:

Materialize Readyset Feldera RisingWave

> Uh oh, someone is tapping you on the shoulder and saying this is too slow because it has to do a complete index scan of the tasks for the project, every time you load the page

Just ask them if that's actually the bottleneck and go for a walk outside, before sweating over anything else discussed in this post.