16 ms·
How PlanetScale Boost serves SQL queries faster
- emptysea 4y agoI’m really curious how this works and how it’s implementation compares to something like materialize — I wonder if there are any caveats around consistency
- nickvanw 4y agoGreat question! We have a technical blog post about how PlanetScale Boost is implemented: https://planetscale.com/blog/how-planetscale-boost-serves-your-sql-queries-instantly https://planetscale.com/blog/how-planetscale-boost-serves-yo... In short, it can be compared in consistency to an up-to-date read replica; PlanetScale Boost uses Vitess' VStream to process events as they happen and keep itself up to date. The blog has much more information if you're curious.
- deleted 4y ago[deleted]
- dang 4y ago(We've merged the threads so that blog post is now the URL at the top)
- nickvanw 4y agoThank you as always for everything that you do @dang!
- deleted 4y ago[deleted]
- valbu 4y agoI think the SQL example should be bit more extreme, the count() group by is quite common and has just linear scaling and is plenty fast for majority of use cases. Tested with 1 process thread, 1k stars = 0.285ms; 10k = 2.85 ms; (and 0.1k = 45us that is same as overhead or just selecting 1 row without join and group by). So with 1k stars you need the system to average 3500 calls/s to saturate 1 thread or have meaningful latency impact. Sure, for bigger IO or row counts this does not scale and materialized view is indeed >100x faster.
- giovannibonetti 4y agoIt seems similar to MIT's Noria [1] > Noria is a new streaming data-flow system designed to act as a fast storage backend for read-heavy web applications based on Jon Gjengset's Phd Thesis, as well as this paper from OSDI'18. It acts like a database, but precomputes and caches relational query results so that reads are blazingly fast. Noria automatically keeps cached results up-to-date as the underlying data, stored in persistent base tables, change. Noria uses partially-stateful data-flow to reduce memory overhead, and supports dynamic, runtime data-flow and query change. [1] https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria
- Eclyps 4y agoI just started using Planetscale for small projects here and there. More and more of my projects are FE-heavy and don't require a big dedicated database (NextJS apps, mostly hardcoded designs or headless CMS like Sanity). There are times where I need to store just small bits of data, maybe contact form submissions or something. It's been super great to be able to quickly hook up planetscale to a nextjs api function and have that data persisted within a matter of minutes. I've yet to use it on anything large-scale, though, so I can't speak to performance when you're really pushing it.
- deleted 4y ago[deleted]
- dianfishekqi 4y agoIt looks like it uses the same ideas as Noria https://www.youtube.com/watch?v=s19G6n0UjsM https://www.youtube.com/watch?v=s19G6n0UjsM https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria
- wwarner 4y agoYes and good for Planetscale to build it out!
- dang 4y agoDiscussed in a few small past threads: Noria: Dynamic, partially-stateful data-flow for high-perf web applications - https://news.ycombinator.com/item?id=29615085 https://news.ycombinator.com/item?id=29615085 - Dec 2021 (10 comments) Noria: dynamic, partially-stateful data-flow for high-performance web apps - https://news.ycombinator.com/item?id=18330477 https://news.ycombinator.com/item?id=18330477 - Oct 2018 (1 comment) Noria: dynamic, partially-stateful data-flow for high-performance web apps - https://news.ycombinator.com/item?id=18176135 https://news.ycombinator.com/item?id=18176135 - Oct 2018 (1 comment)
- joshstrange 4y ago> As rows are inserted, updated, and deleted in the database, the cache is kept up-to-date in real-time, just like a read replica. No TTLs, no invalidation logic, and no caching infrastructure to maintain. This is so freaking neat. Caching is one of the harder things to get consistently right and even if this was a tool that had TTLs+API to invalidate it would be cool but not even having to worry about that is even better. PlanetScale continues to be an awesome service that lets you not worry about your DB and instead focus on your application. My only wish for PlanetScale would be a few more (lower) tiers. Their free tier is very generous but has a few little things (like more than 1 dev/prod branch) that aren't supported and I always feel antsy about not having a prod-like DB for qa/staging. I normally use 3 branches and the free plan only supports 2, which I think changed, I thought I used more than 1 dev branch before I started paying. I have a very burst-y application (it's for events, so it ramps up a few months before the event, then is crazy for 2-7 days during, then usage drops to pretty much 0 for the next ~9 months), I'd love to lower my costs for those 9 months (I could look into downgrading to the free plan but I'd rather pay just a little less and have my quotas drop accordingly). In the end PlanetScale is still worth it for me at $360/yr so I'm not complaining too much. For smaller projects I just worry about using the PS free tier since if I go over those limits the jump is steep ($0->$30/mo), that said I might be overthinking it.
- datalopers 4y agothey don’t say but I assume this is an implementation of differential dataflow (edit: changed to a better link) [1] [1] https://www.microsoft.com/en-us/research/wp-content/uploads/2013/01/differentialdataflow.pdf https://www.microsoft.com/en-us/research/wp-content/uploads/...
- ignoramous 4y agoSee also https://readyset.io/ https://readyset.io/ and https://materialize.com/ https://materialize.com/ There's also the exotic https://dynimize.com/ https://dynimize.com/ (unsure of their current state).
- rorymalcolm 4y ago
- bsnnkv 4y agoPlanetScale is such a cool name, fits really well for a database company. Just goes to show that even these days when I think that naming something new is impossible, there is still a lot of room to be creative.
- bearjaws 4y agoThey probably paid a pretty penny for it. My first startup job was a skunks work project and we had around 128 noun-adjective pairs we wanted to find a .com domain for. All of them were taken. We had to settle on a .io domain, and this was 7 years ago. 2 year in we came up with a better name and managed to get a .com... with a dash in the URL.
- swozey 4y agoI worked at a 4 letter .com startup and we paid $800k for the domain. We never made revenue anywhere near our domain and when the company eventually shuttered the most revenue we'd ever made was selling the domain again.
- laristine 4y agoI deal in domains and stories like this still never cease to amaze me. In any case, I'm glad your company was able to sell the domain for a good sum back.
- ushakov 4y agothere are still plenty of great .com available, you just gotta be more creative
- emptysea 4y agoStartup I worked at paid like 100k + equity for a two word .com domain name and it wasn’t anywhere near as nice of name as planet scale is
- vyrotek 4y agoThis reminds me a little of "materialized views". But essentially every query is potentially a view you can materialize (cache). And with this being managed at the DB level it knows when new data invalidated the previous results. Traditionally, other materialized view implementations have very strict query requirements though. The queries had to be deterministic. No left joins, dates, etc. This is required in order to properly detect when data changes "impact" the view. I wonder how they get around it. Update: Ah, ok! Here's a write up on how it works a bit. My last startup built a system like this specifically to power a gamification engine. Would have been nice to have this 10 years ago. https://planetscale.com/blog/how-planetscale-boost-serves-your-sql-queries-instantly https://planetscale.com/blog/how-planetscale-boost-serves-yo... > The Boost cluster lives alongside your database’s shards and continuously processes the events relayed by the VStream. The result of processing these events is a partially materialized view that can be accessed by the database’s edge tier. This view contains some, but not all, of the rows that could be queried.
- dang 4y ago(We've merged the threads so that writeup is now the URL at the top)
- obviyus 4y agoHas anyone who has used PlanetScale in production comment about their experience? I was evaluating a few options a couple of weeks ago but ended up going with just RDS due to lack of feedback for PlanetScale here on HN.
- mythrwy 4y agoWe looked at it, but it was a little "different" and we didn't want the learning curve, so we went with ScaleGrid instead. This caching does look cool, perhaps I'll revisit PlanetScale later on my own time.
- gtCameron 4y agoWe have been running PlanetScale as our production database for about 6 months, migrated from Aurora Serverless. I love it, their query insights tool has been a game changer for us and has allowed us to optimize a ton of queries in our application. Their support is always available and highly technical. For a sense of scale, we have ~150gb of data running around 5 trillion row reads + 500 million row writes per month
- revicon 4y agoWe’re you using the Aurora Serverless data APIs? Curious if there is something equivalent on PlanetScale.
- samlambert 4y agohttps://github.com/planetscale/database-js https://github.com/planetscale/database-js
- deleted 4y ago[deleted]
- gtCameron 4y agoI was not, we are a Laravel PHP backend, using the standard PHP stuff for connection management
- stalluri 4y agoVstream looks super cool. Can we also use it create subscriptions that can bind with ReactHooks on the front-end ? I think PlanetScale can easily deliver amazing or better than firebase subscriptions. All we need is React and NextJs SDKs to get started with :-)
- httgp 4y agoSupabase does real-time subscriptions really well! And it does have great guides for use with React and Next.js
- aantix 4y agoDidn't MySQL implement query level caching a while back?
- ryanisnan 4y agoI assume this is a bit of a joke, but query caching at least was not good in 5.5-5.7, so it would often be disabled. I don't know how 8 performs.
- rbranson 4y agoIt was removed from MySQL in 8.0 because it wasn't very useful. MySQL query caching does exact matching on the query string and any row update to a table used for a given cached query nukes the entire cache. So it's only useful for a small set of niche use cases where tables are essentially static.
- CharlesW 4y agoDupe: https://news.ycombinator.com/item?id=33610996 https://news.ycombinator.com/item?id=33610996
- p10jkle 4y agoSee also https://readyset.io/ https://readyset.io/ for generic SQL support (not just Planetscale)
- _ben_ 4y agoFor database caching outside of PlanetScale, PolyScale.ai [1] provides a serverless database edge cache that is compatible with Postgres, MySQL, MariaDB and MS SQL Server. Requires zero configuration or sizing etc. 1.https://www.polyscale.ai/ https://www.polyscale.ai/
- rbranson 4y agoI tried to use PolyScale in the past but had issues with performance because updating a row would invalidate the entire cache. I wonder if that has improved?
- _ben_ 4y agoYes, in the early versions of the automated invalidation, the logic cleared all cached data based on tables. That is no longer the case. The invalidations only remove the affected data from the cache, globally. You can read more here: https://docs.polyscale.ai/how-does-it-work#smart-invalidation https://docs.polyscale.ai/how-does-it-work#smart-invalidatio...
- rbranson 4y agoIt didn't impact everything, I think I was hitting this case: > When a query is deemed too complex to determine what specific cached data may have become invalidated, a fallback to a simple but effective table level invalidation occurs.
- saybar 4y agoWe've made a lot of changes - give it a try again or feel free to reach out to support@polyscale.ai and we'd be happy to assist you.
- lern_too_spel 4y agoThe Noria solution seems superior. It doesn't necessarily have to rerun queries from scratch because a single row changed.
- xmorse 4y agoQuery memoization with optimistic updates
- capableweb 4y agoSlightly off-topic but trying to understand something from the landing page: > Powered by open source tech - Built at Google to scale YouTube.com to billions of users Is this a Google project/business owned by Alphabet? The text seems to indicate so, but I find no information about it when doing some quick searching or browsing through the website.
- aarondf 4y agoNope! That part you quoted is referring to Vitess, which was built at Google to scale YouTube. See more: https://planetscale.com/vitess https://planetscale.com/vitess
- capableweb 4y agoAha, I see. Thanks for explaining! So I'm guessing PlanetScale now helps maintain Vitess and PlanetScale is somewhat of a hosted Vitess for people who don't want to self-host?
- aarondf 4y agoYup! Vitess is at the core of PlanetScale and enables us to add lots of cool stuff on top (branching, Boost, etc) but Vitess itself is still open source!
- kerblang 4y agoIt appears the catch is that you have to use their managed service; no DIY installation. https://planetscale.com/docs/concepts/deployment-options https://planetscale.com/docs/concepts/deployment-options Acceptable for some, maybe not others
- kevinburke 4y agoSeems neat, but why is this better than Hadoop?
- gigatexal 4y agoBecause Hadoop is super duper slow? Isn’t that why the industry moved away from it years ago?
- LewisJEllis 4y agoHadoop isn't a database, they don't do anything close to the same thing. Nobody is cross-shopping PlanetScale vs Hadoop. The cross-shop is PlanetScale vs Amazon RDS, Amazon Aurora, Google Cloud SQL, Firebase, Supabase, self-hosting Vitess or MySQL, etc.
- edmundsauto 4y agoI just started a small hobby project and selected supabase for my db provider. Anyone with experience in both Supa and PlanetScale care to comment about the differences? To me, it looks like supabase is designed to take full advantage of postgres features. plpgsql triggers + RLS + clientside auth + streaming changes to subscribers (including via web hooks) are my favorite features. (They also have js edge functions, but I use lambda instead b/c I prefer python) Supabase feels like the scrappy company with amazing focus, akin to an early MailChimp (circa 2007). PlanetBase feels more like early Snowflake - massive scale, focus on performance, can match anything feature-by-feature. One is a master of their craft, the other is a gorilla at scale. Curious what others think. I haven't used PlanetBase extensively so don't have much to go on except their marketing.
- Jarwain 4y agoThat sounds about right from my understanding. Supabase was made as an alternative to firebase, acting as a data layer with a lot of features simplifying application development. Planet Base feels like Snowflake, or some aspects of fly.io, or timescale's managed cloud offering; their focus is on the core database tech and delivering that in a scalable manner.
- greg-m 4y agoReadySet (readyset.io) supports the same style of caching and works with Supabase, if you want to check us out :) I have a few extra cloud invites: greg@readyset.io
- edmundsauto 4y agoMuch appreciated, I will check you out for my next project. Right now I'm not able to migrate as I'm trying to get an MVP up and running and have spent a few days deeply integrating w/ Supabase. What are your core value prop differences between your service and sb? Just curious how I should think about your offering compared to what I'm familiar with.
- greg-m 4y ago
- marzoevam 4y agoIt's super exciting to see Noria-based partially materialized views get this well-deserved airtime! Eliminating error-prone caching logic without any code or infrastructure changes in the context of _any_ database is our core mission over at ReadySet, and is the reason why Jon Gjengset and I spun the company out of MIT research on Noria back in 2020. You can read more in our initial announcement here: https://readyset.io/blog/introducing-readyset https://readyset.io/blog/introducing-readyset If you're reading this announcement post and want to play around with instant query caching àla Noria in your existing Postgres or MySQL database, shoot me a me an email and we'll bump you up on our cloud waitlist :) alana@readyset.io
- saybar 4y agoAt PolyScale [1], we agree that eliminating error-prone caching logic without any code or infrastructure changes is a worthy goal. However, we have taken a different approach to caching, zero configuration, fully automated. You can get connected today in a few minutes, without code or configuration. PolyScale supports Postgres, MySQL, MariaDB and SQL Server, with GraphQL with others coming soon. You can also try the live demo [2]. [1] https://www.polyscale.ai/ https://www.polyscale.ai/ [2] https://playground.polyscale.ai/ https://playground.polyscale.ai/
- vyrotek 4y agoThis looks really great. Happy to see SQL Server support there.
- Jonhoo 4y ago:wave: Author of the paper this work is based on here. I'm so excited to see dynamic, partially-stateful data-flow for incremental materialized view maintenance becoming more wide-spread! I continue to think it's a _great_ idea, and the speed-ups (and complexity reduction) it can yield are pretty immense, so seeing more folks building on the idea makes me very happy. The PlanetScale blog post references my original "Noria" OSDI paper (https://pdos.csail.mit.edu/papers/noria:osdi18.pdf https://pdos.csail.mit.edu/papers/noria:osdi18.pdf), but I'd actually recommend my PhD thesis instead (https://jon.thesquareplanet.com/papers/phd-thesis.pdf https://jon.thesquareplanet.com/papers/phd-thesis.pdf), as it goes much deeper about some of the technical challenges and solutions involved. It also has a chapter (Appendix A) that covers how it all works by analogy, which the less-technical among the audience may appreciate :) A recording of my thesis defense on this, which may be more digestible than the thesis itself, is also online at https://www.youtube.com/watch?v=GctxvSPIfr8 https://www.youtube.com/watch?v=GctxvSPIfr8, as well as a shorter talk from a few years earlier at https://www.youtube.com/watch?v=s19G6n0UjsM https://www.youtube.com/watch?v=s19G6n0UjsM. And the Noria research prototype (written in Rust) is on GitHub: https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria. As others have already mentioned in the comments, I co-founded ReadySet (https://readyset.io/ https://readyset.io/) shortly after graduating specifically to build off of Noria, and they're doing amazing work to provide these kinds of speed-ups for general-purpose relational databases. If you're using one of those, it's worth giving ReadySet a look to get these kinds of speedups there! It's also source-available @ https://github.com/readysettech/readyset https://github.com/readysettech/readyset if you're curious.
- brancz 4y agoI don’t really know either very well, but how does Noria compare to Naiad? Are they comparable at all? I already had Naiad on my reading list, definitely adding Noria as well! Thank you very much for your work!
- exabrial 4y agofor readyset: Is there a deb package available or something lighter weight than docker, kubernets, etc? I'd just like to run it as a regular unix process and start/stop it with systemd.
- hotdamnson 4y agoWhy do these new big thing databases make SQL look like some witchcraft? Here is some proper SQL query: SELECT DISTINCT r.id, r.owner_id, r.name, COUNT(r.id) OVER (PARTITION BY r.id) AS COUNT FROM repository r JOIN star s ON s.repository_id = r.id ORDER BY 4 DESC;
- jakewins 4y agoThis is not what the query in the post is doing. You are counting all stars of all repos, they are counting stars of one (parameterized) repo id.
- hotdamnson 4y agoI just posted the essence of the query, add Where r.id = :repo and you will have the same thing.
- Nican 4y agoAwesome! I have seen PlanetScale hype up this release for weeks, and glad to finally be reading about it. My initial thoughts after reading the blog post, just to poke holes in their new product: 1. Costs. This can save time on read, but it is also introducing additional writes to the database, that can be pretty expensive. PlanetScale can scale horizontally, but have to watch out how much it is going to be paying for the extra machines. (Albeit- machines are usually always cheaper than developers) 2. Consistency. It was not clear if it is going to make committing transactions slower to keep all the views up to date, or if the materialized view is running slightly behind real-time. 2a. And how does the materialized view handle large/slow transactions? Is there going to be any kind of serialization locks? Are the views correct inside of the transaction? 3. Predictability. Query planning is a necessary hell, and different queries might have different patterns that might introduce slightly different materialized views, that could have been maybe served under the same view. Increasing the cost. 3a. SQL Server took a slightly different route lately for performance, in which queries will have different plans depended on the table statistics. I wonder how such a feature would play with Boost, and if slightly different query plans might generate different materialized views.
- mwarkentin 4y agoThe docs indicated the cache may be behind by a few hundred MS: https://planetscale.com/docs/concepts/query-caching-with-planetscale-boost https://planetscale.com/docs/concepts/query-caching-with-pla... > There is a small delay between when these changes are committed to the database and when the cache has been updated. This delay is typically measured in hundreds of milliseconds. Those of you familiar with MySQL replication can think of it as reading from a replica. Typically we've found that most use cases work perfectly fine, even when returning results that may be slightly out-of-date.
- tanoku 4y agoHey Nican! Thanks for the feedback. It wasn’t clear from the blog post, but as the sibling poster points out, the system has full eventual consistency: it behaves like a replica, but it replicates a whole cluster of MySQL instances simultaneously (i.e. your full PlanetScale database). Because of this design, we never lock or affect the performance of writes to the main database. As for predictability, we’re working on some interesting optimizations that allow similar queries to reuse the internal state of each other, so the system becomes more efficient the more queries it’s caching. Stay tuned!
- endisneigh 4y agoAnyone compare this and cockroachdb?
- theonealtair 4y agoEverything about their product is overstated and/or not relevant for most apps. Easy to get 1000x query performance improvement by starting with an extremely slow query. By that standard I could say that I've used create index statements to get 1,000,000x performance. The language is so over-the-top it makes me not even want to read the article through. I work in a real world with real database problems everyday. I would love to have real discussions and solutions to performance improvements. Making irrelevant claims just shuts that down.
- netcraft 4y agometa: There is a typo in this sentence (you -> your) > But there are also disadvantages: these views are not very ergonomic when developing you application
- ISL 4y agoJust once, I want the solution presented by a headline like this to be, "Well, we used a lot more computers."