Welcome everyone to my first Blog post ever.
Let me introduce myself quickly:
I'm a youtuber that creates videos about re-creating existing applications (for example a reddit-clone
). In the past I worked a lot with react and node / express + mongodb as backend.
So SQL and further PostgreSQL is very new to me.
In my last project I've decided to use this stack:
- Next.js
- AWS RDB (with PostgreSQL)
- Prisma as ORM
I'm pretty new to hosting something in general on AWS and I'm also using Prisma the first time.
With that said here is what I've learned:
-
Vercel
deploys hosted sites per default to SFO1 (San Francisco) region - I deployed my RDB on AWS to Frankfurt Gateway (since I thought it will be the fastest solution because I'm from Austria)
- I also chose the free tier and created nothing that a naked PostgreSQL database
So what I'm trying to say here is the following:
- Having the db in another region then the hosted application is a bad idea - which you will see later
- Having a naked PostgreSQL db without a PGBouncer is also not a good idea performance wise
So I deployed my db to AWS and my Next.js App to Vercel.
This is my main api route and all I do (the db has 2 posts total with 0 comments):
const sub = await prisma.subreddit.findUnique({
where: { name: String(req.query.name) },
include: {
posts: {
include: { subreddit: true, user: true, votes: true },
},
joinedUsers: true,
},
});
I turned on Vercel
Analytic and waited a day and this is what I got:
As you can see the performance is horrible for my simple application and loading just 2 posts.
I further added a /performance route that does just this:
var t0 = performance.now();
const sub = await prisma.subreddit.findUnique({
where: { name: String(req.query.name) },
include: {
posts: {
include: { subreddit: true, user: true, votes: true },
},
joinedUsers: true,
},
});
var t1 = performance.now();
const timeItTook = t1 - t0;
console.log("Fetching full Sub took " + timeItTook + " milliseconds.");
The result was always between 3 to 5 seconds.
Fetching full Sub took 3358.1404000520706 milliseconds.
At first I thought it's a problem with Prisma
and more specific it's include
syntax. I somehow thaught that nested Queries are slow.
So I opened an issue here: https://github.com/prisma/prisma/issues/4884
The awesome contributors took a closer look and found the problem(s):
- The datbase was hosted on one side of the world
- And the Application that connects to it on the other side of the world
- It also has no bouncer (for handling many session since it's serverless / lambda functions)
As I read that, it instantly clicked!
At first I wanted to create a RDB Proxy, I think it's called on AWS - but it didn't give me the option to chose the right database. I decided to use Digital Ocean
So I headed over to Digital Ocean and created an account + db hosted in San Francisco.
It also gave me the suggestion to add a "connection pool" (that's basically the same as PGBounce
- as far as my understanding goes. That was also very new to me and didn't even know existed or I needed.
Next I ...
...changed the .env
database url to Digital Oceans one.
...Then I migrated the Schema with yarn prisma migrate dev --preview-feature
and deployed the app to Vercel.
The performance route is now between 20 - 30 ms (compared to about 3000ms before).
This is how it looks on Analytics:
So the performance is now absolutely beautiful and I learned a lot in the process.
Thank you to all the Prisma
contributors to helped me out here. These guys are awesome and I can really say:
I love Prisma
Shameless plug: Here is my youtube channel if you are interested: https://www.youtube.com/channel/UCOo3r8Do1Xa97UfQdqK2MSQ
Top comments (0)