API requests taking too much time

We recently noticed that our API is taking a lot of time on various requests.

Just to give context about our setup:

We are using Netlify serverless functions for the api
Most of our api is behind a graphql endpoint built using Apollo
We are using Prisma as our DB orm
Our database is hosted on Railway
We are using a Prisma Data Proxy service that acts as a middle man between our serverless functions & our DB because otherwise we will run into "max connections limit reached" issues.

Okay, so back to the issue... I did some debugging, & I noticed that there are 2 parts that are most likely where the issue lies.

1- Nested graphql resolvers are causing a LOT of queries. So for example, if we request a list of projects that are N items, each project has a category relation, so to fill the category relation, a separate DB query will be made to get the category of each project. And if each project has some tags, so that's another query for each project. So we end up with 1 + N + N queries!!

2- The other part is, there seems to be a HUGE latency when doing any query from our netlify functions to the Data proxy.

Here is the log for the request made on the main projects page AFTER I optimized the nested relations query to include the children from the parent if they were request. & as you can notice, although it's only 3 queries, & the amount of data is relatively small, the duration was around 2 seconds... projects_page_log.txt

Also for example, a query like this which is used to update a project:

prisma.project
                        .update({
                            where: {
                                id,
                            },
                            data: {
                                title,
                                description,
                                lightning_address,
                                tagline,
                                hashtag,
                                website,
                                discord,
                                github,
                                twitter,
                                slack,
                                telegram,
                                launch_status,

                                ...coverImageRel,
                                ...thumbnailImageRel,
                                screenshots_ids: final_screenshots_ids,

                                category: {
                                    connect: {
                                        id: category_id,
                                    },
                                },
                                members: {
                                    deleteMany: {},
                                    createMany: {
                                        data: newMembers.map((member) => {
                                            return {
                                                role: member.role,
                                                userId: member.id
                                            }
                                        }),
                                    }
                                },
                                recruit_roles: {
                                    deleteMany: {},
                                    createMany: {
                                        data: recruit_roles.map((role) => {
                                            return {
                                                level: 0,
                                                roleId: role
                                            }
                                        }),
                                    }
                                },
                                tournaments: {
                                    deleteMany: {},
                                    createMany: {
                                        data: tournaments.map((tournament) => {
                                            return {
                                                tournament_id: tournament,
                                            }
                                        }),
                                    }
                                },
                                capabilities: {
                                    set: capabilities.map((c) => {
                                        return {
                                            id: c,
                                        }
                                    }),
                                },
                            },
                        })

I know it's not considered trivial, but it's not THAT complicated, & it's taking around 6-7 seconds to complete...... 😐

During a discussion with someone, he pointed out a very important thing... The difference in the regions that are hosting our different providers, mainly the: Database, Data Proxy, netlify functions. So I went I checked them out, & it turned out that: Our DB is hosted in: US west Our Data Proxy is hosted in: EU central

Meaning that, each query is taking at least a minimum of 155ms just on the roundtrip between the different services.... Which can quickly add up & reach >10 seconds So the solution he suggested is to change the regions for all the services to one close place like US west preferably So both our Data Proxy & our netlify functions

The options we have to change the regions for each service are:

Database: current: US west options: none....
Data Proxy: current: EU central options: EU central, US east
Netlify functions: current: don't know options: almost everything now the best case would of course be if we can put all the services in one region either US west or US east (US east is better because it's closer to Europe) but unfortunately, that's not currently possible.

So the solutions we thought of were: 1- Leave the DB in US west & move the DP & NF to US east (we will still have an extra ~60ms roundtrip latency for each request (currently they are ~150ms) Not the best, but it's an improvement

2- We move the DB & NF to US east, & we also migrate the DB to another DB provider different than Railway, who provides a US east region Best latency, but the migration of the DB will take some work (but not as much as when we migrated from elephantSQL of course 😂 ) ...

3- Move from serverless to non-serverless Maybe either on Railway, or any other option 😅 (This will take the most amount of work of course, but it will be the most performance effective (and maybe even running costs effective)) & personally, I think we might have to do this anyway at some point eventually. So what do you think??

I think we will start with solution 1 because it's the quickest to try & we will see how the performance improves. The other thing that we will have to look into, is a way to optimize the nested resolvers queries somehow to reduce the number of queries each request make. I'm not very sure how this will look like, but I'll try to search more about this.

peakshift / makers.bolt.fun

API requests taking too much time #188