When would it make sense to use such a setup? What kind of applications?
Once I have experimented with Tigris’ global data replication then it could be useful for a distributed user-base doing a lot of reads. The idea is that you can point a fresh postgres instance to the S3 bucket for read access (rather than keeping a hot standby using Postgres native replication)
This makes it feasible to build multi-tenant applications with 1-db-per-tenant. I know the same can be achieved with RLS policies or application-layer permissions, but nothing beats the peace mind (for developer and customers alike) of having tenant data in entirely separate databases. This also makes it easier to have custom schema for different subsets of users. Side note: SQLite (the OG serverless DB) also has the same benefit but is decidedly less feature-full than postgres/mysql.
Another pattern that I have seen emerging is that certain products allow end-users to create DBs on the fly as a part of their own product offering. For example, Retool (which uses Neon) allows users to create a new database in seconds, even on their free plan. I don't think this would have been possible (both cost and DX wise) if they provisioned a new Postgres cluster for each customer.
On that note, and in light of what happened with the Planetscale free plan, this would make the Supabase free offering and branching to be significantly less expensive to operate. btw: thank you for the free plan and all the amazing OSS contributions from the supabase team!!
One could easily have a single small Postgres instance for a few dollars that hosts all these small scale peanut sized toy databases and forget about it.
if your main business critical database is not doing transactions and stays idle, you should be working on bringing more transactions (sales sales sales) - not trying to save pennies on downsized postgres by moving from SSD-fast postgres to S3-slow-postgres.
I get that there are benefits to scaling up/down, but to me: Serverless is a billing model for multi-tenant services, where user is billed by service usage, not the actual underlying resource consumption. Because in multi-tenant SAAS all your tenants share the underlying infrastructure