9 ms·

Materialized views are obviously useful

173 points by gz09 1y ago

erulabs 1y ago
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.
- malthejorgensen 1y ago
  Don’t you have to manually “refresh” Postgres materialized views, essentially making it an easier to implement cache (the Redis example in the blog post) rather than the type always-auto-updating materialized view the blog post author is actually touting?
  - lbreakjai 1y ago
    Out of the box, you're right, but there are extensions that do just that: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm It's however not available on RDS, so I've never had the chance to try it myself.
  - ropable 1y ago
    Yes, you need to refresh the materialized views periodically. Which mean that, just like any other caching mechanism, you're solving one problem (query performance) but introducing another (cache invalidation). I've personally used Postgres MVs to great success, but there are tradeoffs.
  - striking 1y ago
    The real bummer is not that you have to manually refresh them, it's that refreshing them involves refreshing the entire view. If you could pick and choose what gets refreshed, you might just sometimes have a stale cache here and there while parts of it get updated. But refreshing a materialized view that is basically just not small or potentially slightly interesting runs the risk of blowing your write instance up. For this reason I would strongly advise, in the spirit of https://wiki.postgresql.org/wiki/Don't_Do_This https://wiki.postgresql.org/wiki/Don't_Do_This, that you Don't Do Materialized Views. Sure, Differential/Timely Dataflow exist and they're very interesting; I have not gotten to build a database system with them and the systems that provide them in a usable format to end users (e.g. Materialize) are too non-boring for me to want to deploy in a production app.
    - inkyoto 1y ago
      This is where Oracle has a upper hand in the materialised view department: a materialised view can be refreshed either manually or automatically, with both, incremental and full, refresh options being available.
      - DerArzt 1y ago
        Yeah, but then you're using Oracle.
  - recroad 1y ago
    So the author is wrong that they’re automatic kept in sync?
    - JamesSwift 1y ago
      No, they conflate the two concepts together, though they acknowledge this is a special case here: > There are a few startups these days peddling a newfangled technology called “incremental view maintenance” or “differential dataflow” I think they should be a little more explicit about the differences though, because it can be very misleading for those who arent aware of the distinction.
  - erulabs 1y ago
    Oh interesting, I didn’t know that - I’ve been so far in MySQL/Vitess land for so long, I haven’t used Postgres in several years. That’s disappointing!
  - nine_k 1y ago
    I think it's impossible to do an incremental update in an arbitrary case. Imagine an m-view based on a query that selects top 100 largest purchases during last 30 days on an e-commerce site. Or, worse, a query that selects the largest subtree of followers on a social network site. Only certain kinds of conditions, such as a rolling window over a timestamp field, seem amenable to efficient incremental updates. What am I missing?
    - Karrot_Kream 1y ago
      That's probably beyond the scale level appropriate for a materialized view. For that I'd use something like DBT.
    - 392 1y ago
      https://youtu.be/iT4k5DCnvPU?si=cZ_TYuynKavksQKG https://youtu.be/iT4k5DCnvPU?si=cZ_TYuynKavksQKG
- enedil 1y ago
  Materialized views in ScyllaDB are (were?) known to be a buggy implementation. In particular, they often depended on the cluster being healthy at the time of propagating the changes.
- bdcravens 1y ago
  In lieu of good MV support, you can always just run a scheduled query to store results in a persisted table that you identify as a materialized view. For example, when doing this in SQL Server, I give the table name a "cache" prefix.
quectophoton 1y ago
> 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.
- 4ndrewl 1y ago
  Just landed here to write this. Materialized Views are _very_ implementation specific and are definitely _not_ magic. It's important to understand how your implementation works before committing to it.
  - shivasaxena 1y ago
    Curious if anyone know any implementation where they would be automatically updated? Now that would be awesome! EDIT: come to think of it, it would require going through CDC stream, and figuring out if any of the tables affected are a dependency of given materialized view. Maybe with some ast parsing as well to handle tenants/partitions. Sounds like it can work?
    - magicalhippo 1y ago
      MSSQL and Sybase SQLAnywhere has options for that, we use it a fair bit with both. At least on SQLAnywhere it seems to be implemented using triggers, ie not unlike what one would do if rolling your own.
    - cyanydeez 1y ago
      Postgesql knows when you try to drop its dependencies so shouldnt be to hard to watch traffic
    - 4ndrewl 1y ago
      Oracle will do - a couple of options, either a full rebuild or an incremental rebuild.
      - tanelpoder 1y ago
Jupe 1y ago
> (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"?
deleted 1y ago
[deleted]
bob1029 1y ago
> 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/views/create-indexed-views https://learn.microsoft.com/en-us/sql/relational-databases/v... https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver17#schemabinding https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...
- sophiebits 1y ago
  TIL, thanks! I know Postgres and MySQL don’t include an equivalent.
- MangoToupe 1y ago
  Can we inspect MSSQL's source or is it shipped as a blob? I can't find any serious information about how this works. I can't imagine who would want to spend money on this.
- mike_hearn 1y ago
  Yes the idea it's newfangled is odd. It's only newfangled if you ignore the existence of database engines that are better than the open source ones. Oracle's implementation scales horizontally and can do incremental view maintenance using commit logs: https://oracle-base.com/articles/misc/materialized-views https://oracle-base.com/articles/misc/materialized-views But it may not even be necessary because Oracle also supports query caching both server and client side. By default you have to opt-in on the query level: SELECT /*+ RESULT_CACHE */ .... FROM ..... but if you do then queries might not even hit the database at all, the results are cached by the drivers. The main limitation of that feature is that it's meant for read-mostly tables and cache keys are invalidated on any write to the table. So for aggregating counts in a large multi-tenant table the hit rate would be low and a materialized view is more appropriate. It's a bit unclear why the author of the article ignores triggers as not "in vogue" though. That's supported by every DB and will work fine for this use case. You do have to learn how to use these features but it can save a lot of work, especially when introducing non-consistent data sources like a Redis cache. Consistency is so valuable. Disclosure: work for Oracle, opinions are my own (albeit in this case, presumably highly aligned).
porridgeraisin 1y ago
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.
- mkleczek 1y ago
  That way you increase contention and harm scalability. In sqlite it does not matter as writes are single-threaded anyway. But in other DBMSes it does. What's more: in PostgreSQL you increase bloat as every update creates a new row version. Most of the times the solution is not pre-aggregation but proper indexes on foreign key columns (missing fk indexes is one of the most common mistakes in RDB design).
  - the_sleaze_ 1y ago
    I have to admit I do the same as GP here. However I would always do both - index and keep a count on the parent as a first step. > increase contention and harm scalability Contention, concurrent connections, high-throughput and the associated race conditions are absolute table stakes for RDBMs. They just won't be taken seriously if they can't handle thousands of concurrent updates. So imho for 90% of projects this just won't be an issue. > PostgreSQL you increase bloat as every update creates a new row version This is true, but the complexity has to live somewhere. There will be as many rows added to a materialized view as there will be in the original table. > Most of the times the solution is not pre-aggregation This is wrong. Caching = pre-aggregation and is almost always either the solution or a part of the solution to scalability, latancy etc. Don't compute what you can retrieve.
  - porridgeraisin 1y ago
    Yeah if index + count(*) works fine, then that is enough. This will usually not be a purely index-only scan though in practice, in MVCC databases. Since it sometimes has to inspect the actual row to see if its visible to the current transaction or not. So it's "mostly" an index scan. If you're not getting an index-only scan, count(*) can become a bottleneck in my experience even with FK indexes. [All just my understanding]
thom 1y ago
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.
- viccis 1y ago
  Yep Databricks does this pretty well too. I think their sales jargon for it is the "Enzyme engine"
- MangoToupe 1y ago
  > Finally we can stop just shuffling data around Bro that's your entire job description. What is left?
  - thom 1y ago
    Deciding what colour it should be.
jmull 1y ago
I curious why an index can't handle that first query well.
- th0ma5 1y ago
  This is my thing, I often thought of these views as a way to bridge organizational divides rather than technical ones. Still cool! But if you own everything you can do all kinds of other stuff just as easily.
- mb7733 1y ago
  Indexes can only help narrow down to the issues for the project (more generally: matching rows for the query). Once the index narrows down the rows, Postgres still has to count them all, and Postgres isn't particularly fast at that, especially in an active table[0]. That's what the author meant by 'complete index scan of the tasks for the project'. Of course this isn't really relevant until there are a very large number of rows to count for a given query. Much larger than what is likely for "tasks in a project". I've run into this only with queries that end up counting 10e7/8/9 rows, i.e. more like OLAP workloads [0] https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting
- crazygringo 1y ago
  There is no reason. An index is the proper solution for dealing with <1K tasks per project, conservatively. (On modern SSD's you'd probably still be plenty fast for <100K tasks.) In fact, the query would return the result straight from counting the project_id index entries, never even needing to scan the table itself (as the author acknowledges). If you're really dealing with many, many thousands of tasks per project, then materialized views are going to be just as slow to update as to view. They're not "magic". The standard performant solution would be to keep a num_tasks field that was always incremented or decremented in a transaction together with inserting or deleting a task row. That will actually be lightning fast. Materialized views aren't even supported in many common relational databases. They're a very particular solution that has very particular tradeoffs. Unfortunately, this article doesn't go into the tradeoffs at all, and picks a bad example where they're not even an obviously good solution in the first place.
jamesblonde 1y ago
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'.
- PerryStyle 1y ago
  +1. Learned about this in DB research course during grad school. Feldera is really cool. Also I love their website design.
  - lsuresh 1y ago
    Thanks for the kind words (Feldera co-founder here). I'll pass it on to the design team. :)
- anon84873628 1y ago
  I mean, everything you said sounds exactly like the definition of "new fangled" to me. I don't think the term is meant to be so pejorative or dismissive, just that the tech is currently intimidating to people not on the cutting edge. (Edit: e.g. taking graduate level database courses, as mentioned by a sibling comment :-) There is constantly so much new stuff in software, you have to be a bit willfully ignorant of some things some of the time.
  - jamesblonde 1y ago
    Does going from O(N) to O(1) sound like "new fangled"? That is the smell of progress
    - Zen1th 1y ago
      You're taking "new fangled" too literally. Is it new, as in not everyone concerned is aware of it? Yes! I think the author is as enthusiastic about this as you are.
lysergic 1y ago
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
globular-toast 1y ago
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...
- quasarj 1y ago
  Yeah, that was my thoughts as well. What database is this? In Postgres you definitely have to update materialized views manually....
xixixao 1y ago
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 https://stack.convex.dev/how-convex-works
aboodman 1y ago
FYI, Zero uses incremental view maintenance internally as the core of its sync engine: https://zero.rocicorp.dev/docs/introduction 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 https://github.com/rocicorp/mono/tree/main/packages/zql. Or come find us in Discord: https://discord.rocicorp.dev/ https://discord.rocicorp.dev/
wslh 1y ago
A 2020 HN thread on differential dataflow and some comments about materialized views: https://news.ycombinator.com/item?id=24837031 https://news.ycombinator.com/item?id=24837031
Aeolun 1y ago
This is like React or SolidJS for database queries.
joaomacp 1y ago
[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.
ropable 1y ago
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.
npn 1y ago
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.
hyperadvanced 1y ago
I don’t want to be rude, but this is a bad use case for materialized views, and does not provide an adequate technical solution to the problems discussed. 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
Terr_ 1y ago
This lineup makes my think of "Read Models" on an event-driven system. 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.
garyclarke27 1y ago
I love Postgres but my biggest disappointment with them over recent years, is they are still missing automatic incremental materialized view maintenance. Has been proposed for many years but never implemented, there are extensions but they are not popular which makes me weary to use them. Would be so nice to have in the core, would also be nice to have an explanation as to why they can't do it?? Oracle has had this for many years.
whalesalad 1y ago
At least for Postgres, this won't work the way the author describes. > 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.

173 points by gz09 1y ago

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.

malthejorgensen 1y ago
Don’t you have to manually “refresh” Postgres materialized views, essentially making it an easier to implement cache (the Redis example in the blog post) rather than the type always-auto-updating materialized view the blog post author is actually touting?
- lbreakjai 1y ago
  Out of the box, you're right, but there are extensions that do just that: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm It's however not available on RDS, so I've never had the chance to try it myself.
- ropable 1y ago
  Yes, you need to refresh the materialized views periodically. Which mean that, just like any other caching mechanism, you're solving one problem (query performance) but introducing another (cache invalidation). I've personally used Postgres MVs to great success, but there are tradeoffs.
- striking 1y ago
  The real bummer is not that you have to manually refresh them, it's that refreshing them involves refreshing the entire view. If you could pick and choose what gets refreshed, you might just sometimes have a stale cache here and there while parts of it get updated. But refreshing a materialized view that is basically just not small or potentially slightly interesting runs the risk of blowing your write instance up. For this reason I would strongly advise, in the spirit of https://wiki.postgresql.org/wiki/Don't_Do_This https://wiki.postgresql.org/wiki/Don't_Do_This, that you Don't Do Materialized Views. Sure, Differential/Timely Dataflow exist and they're very interesting; I have not gotten to build a database system with them and the systems that provide them in a usable format to end users (e.g. Materialize) are too non-boring for me to want to deploy in a production app.
  - inkyoto 1y ago
    This is where Oracle has a upper hand in the materialised view department: a materialised view can be refreshed either manually or automatically, with both, incremental and full, refresh options being available.
    - DerArzt 1y ago
      Yeah, but then you're using Oracle.
- recroad 1y ago
  So the author is wrong that they’re automatic kept in sync?
  - JamesSwift 1y ago
    No, they conflate the two concepts together, though they acknowledge this is a special case here: > There are a few startups these days peddling a newfangled technology called “incremental view maintenance” or “differential dataflow” I think they should be a little more explicit about the differences though, because it can be very misleading for those who arent aware of the distinction.
- erulabs 1y ago
  Oh interesting, I didn’t know that - I’ve been so far in MySQL/Vitess land for so long, I haven’t used Postgres in several years. That’s disappointing!
- nine_k 1y ago
  I think it's impossible to do an incremental update in an arbitrary case. Imagine an m-view based on a query that selects top 100 largest purchases during last 30 days on an e-commerce site. Or, worse, a query that selects the largest subtree of followers on a social network site. Only certain kinds of conditions, such as a rolling window over a timestamp field, seem amenable to efficient incremental updates. What am I missing?
  - Karrot_Kream 1y ago
    That's probably beyond the scale level appropriate for a materialized view. For that I'd use something like DBT.
  - 392 1y ago
    https://youtu.be/iT4k5DCnvPU?si=cZ_TYuynKavksQKG https://youtu.be/iT4k5DCnvPU?si=cZ_TYuynKavksQKG
enedil 1y ago
Materialized views in ScyllaDB are (were?) known to be a buggy implementation. In particular, they often depended on the cluster being healthy at the time of propagating the changes.
bdcravens 1y ago
In lieu of good MV support, you can always just run a scheduled query to store results in a persisted table that you identify as a materialized view. For example, when doing this in SQL Server, I give the table name a "cache" prefix.

quectophoton 1y ago

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

4ndrewl 1y ago
Just landed here to write this. Materialized Views are _very_ implementation specific and are definitely _not_ magic. It's important to understand how your implementation works before committing to it.
- shivasaxena 1y ago
  Curious if anyone know any implementation where they would be automatically updated? Now that would be awesome! EDIT: come to think of it, it would require going through CDC stream, and figuring out if any of the tables affected are a dependency of given materialized view. Maybe with some ast parsing as well to handle tenants/partitions. Sounds like it can work?
  - magicalhippo 1y ago
    MSSQL and Sybase SQLAnywhere has options for that, we use it a fair bit with both. At least on SQLAnywhere it seems to be implemented using triggers, ie not unlike what one would do if rolling your own.
  - cyanydeez 1y ago
    Postgesql knows when you try to drop its dependencies so shouldnt be to hard to watch traffic
  - 4ndrewl 1y ago
    Oracle will do - a couple of options, either a full rebuild or an incremental rebuild.
    - tanelpoder 1y ago

Jupe 1y ago

> (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"?

deleted 1y ago

[deleted]

bob1029 1y ago

> 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/views/create-indexed-views https://learn.microsoft.com/en-us/sql/relational-databases/v... https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver17#schemabinding https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...

sophiebits 1y ago
TIL, thanks! I know Postgres and MySQL don’t include an equivalent.
MangoToupe 1y ago
Can we inspect MSSQL's source or is it shipped as a blob? I can't find any serious information about how this works. I can't imagine who would want to spend money on this.
mike_hearn 1y ago
Yes the idea it's newfangled is odd. It's only newfangled if you ignore the existence of database engines that are better than the open source ones. Oracle's implementation scales horizontally and can do incremental view maintenance using commit logs: https://oracle-base.com/articles/misc/materialized-views https://oracle-base.com/articles/misc/materialized-views But it may not even be necessary because Oracle also supports query caching both server and client side. By default you have to opt-in on the query level: SELECT /*+ RESULT_CACHE */ .... FROM ..... but if you do then queries might not even hit the database at all, the results are cached by the drivers. The main limitation of that feature is that it's meant for read-mostly tables and cache keys are invalidated on any write to the table. So for aggregating counts in a large multi-tenant table the hit rate would be low and a materialized view is more appropriate. It's a bit unclear why the author of the article ignores triggers as not "in vogue" though. That's supported by every DB and will work fine for this use case. You do have to learn how to use these features but it can save a lot of work, especially when introducing non-consistent data sources like a Redis cache. Consistency is so valuable. Disclosure: work for Oracle, opinions are my own (albeit in this case, presumably highly aligned).

porridgeraisin 1y ago

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.

mkleczek 1y ago
That way you increase contention and harm scalability. In sqlite it does not matter as writes are single-threaded anyway. But in other DBMSes it does. What's more: in PostgreSQL you increase bloat as every update creates a new row version. Most of the times the solution is not pre-aggregation but proper indexes on foreign key columns (missing fk indexes is one of the most common mistakes in RDB design).
- the_sleaze_ 1y ago
  I have to admit I do the same as GP here. However I would always do both - index and keep a count on the parent as a first step. > increase contention and harm scalability Contention, concurrent connections, high-throughput and the associated race conditions are absolute table stakes for RDBMs. They just won't be taken seriously if they can't handle thousands of concurrent updates. So imho for 90% of projects this just won't be an issue. > PostgreSQL you increase bloat as every update creates a new row version This is true, but the complexity has to live somewhere. There will be as many rows added to a materialized view as there will be in the original table. > Most of the times the solution is not pre-aggregation This is wrong. Caching = pre-aggregation and is almost always either the solution or a part of the solution to scalability, latancy etc. Don't compute what you can retrieve.
- porridgeraisin 1y ago
  Yeah if index + count(*) works fine, then that is enough. This will usually not be a purely index-only scan though in practice, in MVCC databases. Since it sometimes has to inspect the actual row to see if its visible to the current transaction or not. So it's "mostly" an index scan. If you're not getting an index-only scan, count(*) can become a bottleneck in my experience even with FK indexes. [All just my understanding]

thom 1y ago

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.

viccis 1y ago
Yep Databricks does this pretty well too. I think their sales jargon for it is the "Enzyme engine"
MangoToupe 1y ago
> Finally we can stop just shuffling data around Bro that's your entire job description. What is left?
- thom 1y ago
  Deciding what colour it should be.

jmull 1y ago

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

th0ma5 1y ago
This is my thing, I often thought of these views as a way to bridge organizational divides rather than technical ones. Still cool! But if you own everything you can do all kinds of other stuff just as easily.
mb7733 1y ago
Indexes can only help narrow down to the issues for the project (more generally: matching rows for the query). Once the index narrows down the rows, Postgres still has to count them all, and Postgres isn't particularly fast at that, especially in an active table[0]. That's what the author meant by 'complete index scan of the tasks for the project'. Of course this isn't really relevant until there are a very large number of rows to count for a given query. Much larger than what is likely for "tasks in a project". I've run into this only with queries that end up counting 10e7/8/9 rows, i.e. more like OLAP workloads [0] https://wiki.postgresql.org/wiki/Slow_Counting https://wiki.postgresql.org/wiki/Slow_Counting
crazygringo 1y ago
There is no reason. An index is the proper solution for dealing with <1K tasks per project, conservatively. (On modern SSD's you'd probably still be plenty fast for <100K tasks.) In fact, the query would return the result straight from counting the project_id index entries, never even needing to scan the table itself (as the author acknowledges). If you're really dealing with many, many thousands of tasks per project, then materialized views are going to be just as slow to update as to view. They're not "magic". The standard performant solution would be to keep a num_tasks field that was always incremented or decremented in a transaction together with inserting or deleting a task row. That will actually be lightning fast. Materialized views aren't even supported in many common relational databases. They're a very particular solution that has very particular tradeoffs. Unfortunately, this article doesn't go into the tradeoffs at all, and picks a bad example where they're not even an obviously good solution in the first place.

jamesblonde 1y ago

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

PerryStyle 1y ago
+1. Learned about this in DB research course during grad school. Feldera is really cool. Also I love their website design.
- lsuresh 1y ago
  Thanks for the kind words (Feldera co-founder here). I'll pass it on to the design team. :)
anon84873628 1y ago
I mean, everything you said sounds exactly like the definition of "new fangled" to me. I don't think the term is meant to be so pejorative or dismissive, just that the tech is currently intimidating to people not on the cutting edge. (Edit: e.g. taking graduate level database courses, as mentioned by a sibling comment :-) There is constantly so much new stuff in software, you have to be a bit willfully ignorant of some things some of the time.
- jamesblonde 1y ago
  Does going from O(N) to O(1) sound like "new fangled"? That is the smell of progress
  - Zen1th 1y ago
    You're taking "new fangled" too literally. Is it new, as in not everyone concerned is aware of it? Yes! I think the author is as enthusiastic about this as you are.

lysergic 1y ago

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

globular-toast 1y ago

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

quasarj 1y ago
Yeah, that was my thoughts as well. What database is this? In Postgres you definitely have to update materialized views manually....

xixixao 1y ago

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 https://stack.convex.dev/how-convex-works

aboodman 1y ago

FYI, Zero uses incremental view maintenance internally as the core of its sync engine: https://zero.rocicorp.dev/docs/introduction 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 https://github.com/rocicorp/mono/tree/main/packages/zql. Or come find us in Discord: https://discord.rocicorp.dev/ https://discord.rocicorp.dev/

wslh 1y ago

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

Aeolun 1y ago

This is like React or SolidJS for database queries.

joaomacp 1y ago

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

ropable 1y ago

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.

npn 1y ago

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.

hyperadvanced 1y ago

I don’t want to be rude, but this is a bad use case for materialized views, and does not provide an adequate technical solution to the problems discussed. 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

Terr_ 1y ago

This lineup makes my think of "Read Models" on an event-driven system. 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.

garyclarke27 1y ago

I love Postgres but my biggest disappointment with them over recent years, is they are still missing automatic incremental materialized view maintenance. Has been proposed for many years but never implemented, there are extensions but they are not popular which makes me weary to use them. Would be so nice to have in the core, would also be nice to have an explanation as to why they can't do it?? Oracle has had this for many years.

whalesalad 1y ago

At least for Postgres, this won't work the way the author describes. > 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.