This question is bigger than just Redwood, but I figured some people here might have some helpful thoughts.
I’m interested in deploying on Vercel but I’m worried about the scalability of using Postgres as a backend due to the overhead of database connections for each request. I know Prisma recommends using a connection pool such as pgbouncer, but even that sounds like it might not be good enough. I know Aurora Serverless has a Data API but it is not yet supported by Prisma.
Has anyone actually experimented with this or had any luck in production?
The production RW apps I know of all use Postgres. Making sure that your DB and your API lambda functions are all deployed in the same geographical location (like having it all on AWS us-east-1), and, as you say, using a connection pooler both go a long way to making DB access performant.
But I’m super interested in knowing where the limits are. What you could do is set up a basic app and run some load tests. If you do, and share the results, it’s something that would benefit the whole community. But only if you want, of course no pressure
I just created a new DB on SupaBase and connect to it using the connection pooling url. Works great!
But what’s RW’s solution to the migration issue?
Prisma Migrate uses database transactions to check out the current state of the database and the migrations table. However, the Migration Engine is designed to use a single connection to the database, and does not support connection pooling with PgBouncer. If you attempt to run Prisma Migrate commands in any environment that uses PgBouncer for connection pooling, you might see the following error:
Error: undefined: Database errorError querying the database: db error: ERROR: prepared statement "s0" already exists
To work around this issue, you must connect directly to the database rather than going through PgBouncer. How to achieve this depends on your setup or provider: