Slow database connection times with next.js + supavisor + vercel
Unanswered
Cape lion posted this in #help-forum
Cape lionOP
I'm working on an application that has been seeing slow response times on all calls to our database (several hundred milliseconds to
After experimenting, we've found:
- The query time in the database itself to be fast, < 1 ms
- Response time from the database after the connection has been made to be relatively fast, ~50ms
- The time to connect to the postgres database taking several hundred ms
We're using vercel for hosting with supavisor, as mentioned in the title. From my understanding, supavisor should be pooling connections, and handing our serverless function a live connection when we try to connect, to reduce overall query time.
Is this correct? I don't know if we're doing something wrong, or if this is the typical experience with nextjs server components using edge functions.
I'm used to a paradigm where we have a long-standing server that has an open database connection, and haven't had this problem in the past.
SELECT * from a table with < 10 rows).After experimenting, we've found:
- The query time in the database itself to be fast, < 1 ms
- Response time from the database after the connection has been made to be relatively fast, ~50ms
- The time to connect to the postgres database taking several hundred ms
We're using vercel for hosting with supavisor, as mentioned in the title. From my understanding, supavisor should be pooling connections, and handing our serverless function a live connection when we try to connect, to reduce overall query time.
Is this correct? I don't know if we're doing something wrong, or if this is the typical experience with nextjs server components using edge functions.
I'm used to a paradigm where we have a long-standing server that has an open database connection, and haven't had this problem in the past.
43 Replies
Southern rough shrimp
What ORM, if any, are you using
Cape lionOP
We've tried many different strategies, all showing the same result with slow initial connection to the database (node-postgres, prisma, drizzle, I think a few others)
I noticed this issue too with a app i used supabase database in
I haven't tried much, but it happens when you use their old pooler as well
Southern rough shrimp
Could it be because Vercel is deploying your API route to a serverless function, but the ORM you're using isn't designed to be used on serverless
Prisma, for example, has a serverless version
It seems to be a issue with location of vercel server and supabase or something related to the connection between the two
Southern rough shrimp
Good point, where is your cluster located, and where have you deployed your vercel app
@Southern rough shrimp Could it be because Vercel is deploying your API route to a serverless function, but the ORM you're using isn't designed to be used on serverless
Nah it happens when using prisma and since the OP has tried Drizzle too i doubt its anything to do with the ORM itself.
My serverless functions(Route Handlers in my case) time out cause most of these queries are taking 3-6 secs and sometimes more than 10 secs. So having multiple queries means you are almost always gonna timeout
Southern rough shrimp
(I've never used Drizzle) but that link suggests that you have to prepare statements in serverless?
Cape lionOP
I've had the same issue with https://node-postgres.com/ which I believe just does raw sql queries without any extras, the connection to the database itself takes a while
Which suggests to me that the pooler (supavisor) is not passing me a live connection?
Southern rough shrimp
Where are your Vercel and database deployed physically
i.e what region
Cape lionOP
US east
Southern rough shrimp
For both?
Cape lionOP
Yeah
@Cape lion Which suggests to me that the pooler (supavisor) is not passing me a live connection?
Its not even a problem with supavisor i think
Cape lionOP
once the connection has been established, subsequent queries are plenty fast
Southern rough shrimp
You dont happen to use the CITEXT extension at all?
Cape lionOP
Not that I'm aware of, how would I check?
Southern rough shrimp
I don't think it's installed by default
Cape lionOP
At best I'm seeing 150ms time to connect with
pgSouthern rough shrimp
And just to check that you live in the US?
Cape lionOP
Yeah I live in the US East region
@Cape lion At best I'm seeing 150ms time to connect with `pg`
Southern rough shrimp
150ms sounds about right considering the avg latency is 108ms
Cape lionOP
Wow, that's...not great
Southern rough shrimp
For comparison, my azure postgres flexible server takes 168ms to fetch 9 users.
We're both in the same region - that's average latency I think?
Cape lionOP
Well, after my connection has been establish it takes like 30-50ms to actually fetch the data from the database
Its even worse for me if i use multiple queries on a dynamic page and since im farther way its almost always timing out
So you'll need to consider this more.
Cape lionOP
This is just making me want to ditch the whole serverless paradigm
Southern rough shrimp
What needs to be so quick to fetch data?
150ms isn't bad
Cape lionOP
I think it's pretty bad when the database query takes less that 1ms, that's 150ms of establishing a connection and then 30ms it just network latency. The 150ms is just unnecessary lag for the user (and that's the best case scenario, sometimes it's like 300-800ms under load)
I guess we can mark this as solved, supavisor is just slow
Southern rough shrimp
Sorry I couldn't help more
Cape lionOP
All good, you helped me find the cause and it's just supavisor