What a great post. Humble and honest and simple and focused on an issue most developers think is so simple (“why not just vibe code SQL?”, “whatever, just scale up the RDS instance”).

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.

> And then by magic the results of this query will just always exist and be up-to-date.

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

> (Technically speaking, if 100 people load the same page at the same time and the cache isn’t populated yet, then we’ll end up sending 100 queries to the database which isn’t amazing, but let’s just pretend we didn’t hear that.)

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

> I don’t know yet if the implementations of this yet are good enough to use at scale. Maybe they’re slow or maybe the bugs aren’t ironed out yet.

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...

In general counts are just so commonly needed in 1:N relationships. Specifically counts grouped by "status" of the object on the N side of the relationship. I've never seen a CRUD app where this didn't eventually make the feature list.

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.

Materialize.com and Snowflake have pretty reliable incremental materialised views now, with caveats that aren’t back breaking. If you can transform in SQL rather than having to build a whole new pipeline or microservice to do the work that’s a pure operational win. I consider this alongside hybrid transactional/analytical databases to be the holy grail of data infrastructure. Finally we can stop just shuffling data around, support almost all workloads in one place, and get some work done.

I curious why an index can't handle that first query well.

This triggered me in the article

'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'.

Here are some commercial and source-available options:

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

Materialize Readyset Feldera RisingWave

This is a weird article. The author doesn't even mention what database they are talking about then just drops in some SQL that looks like Postgres. If you think Postgres will magically have the right values in it for a materialized view you will be very disappointed...

Convex's queries[0] are another example, with perhaps a somewhat simpler approach to the tracking and invalidation.

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

FYI, Zero uses incremental view maintenance internally as the core of its sync engine:

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))

... and we sync updates to this query incrementally to the client.

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/

A 2020 HN thread on differential dataflow and some comments about materialized views: https://news.ycombinator.com/item?id=24837031

This is like React or SolidJS for database queries.

[After just using `count()`]

> 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.

Materialized views are great and (obviously) useful, but they have the usual tradeoffs of any caching mechanism (e.g. now you have to worry about cache data age and invalidation.

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.

This is silly. The proper way is to keep a task_count field on projects table and update it on create/delete action. Way more flexible and perfomant.