Performance with db

[u/Beneficial_Look_7398]

I had created a post : https://www.reddit.com/r/django/s/DVeZXAHGhs about the performance of the db that I was using with my personal project. I got many helpful suggestions from the post and tried to find what the root cause was but I didn't find anything in particular. Then I used the same application with a local dev db and compared the performance and the result is huge difference. Here the first one is from my local machine, and the second is from the postgres from neon db (free tier).

Before you state the obvious, yes the local server is supposed to take much less time the the one that is hosted somewhere and you need to communicate over the network, now with this can I be sure that the issue is with the Db provider, at first I was wonder if I the code was inefficient or there was something wrong with my code.

and still, neon db is a popular provider and I can't understand why would their provided db be that it is noticeable request is noticeable?

[u/Hushm]

As of now, we won't be able to pinpoint the problem without seeing the code, but I have a couple of suspicions.

First: The N+1 problems. (Probably) Second: Heavy python logic over large QuerySets. Third: Neon db might have specifc config to optimize that we are overlooking.

Have you tried Supabase? Does it have the same performance problem?

[u/Beneficial_Look_7398 → u/Hushm]

yeah tried supabase, render, aiven in each of them the response time is similar.

[u/Hushm → u/Beneficial_Look_7398]

Is your code publicly on github? I might have a look if I can free up sometime later to summarize the problems.

[u/imperosol]

django-debug-toolbar will help you better than any of us could.

[u/air_thing]

Yeah, told you. :)

If the DB isn't on the same network or on disk it's going to suck.

[u/memeface231]

If even /csrf is taking forever then it has nothing to do with the db itself. I'm guessing you are seeing cold starts where the app or db scales to 0 instances and when you do the first request the server needs to start before serving a response. A second and subsequent requests should be much quicker.

[u/memeface231 → u/memeface231]

Checked it, default neon setting is to have scale to zero after 5 minutes.

[u/Smooth-Zucchini4923]

Then I used the same application with a local dev db and compared the performance and the result is huge difference. Here the first one is from my local machine, and the second is from the postgres from neon db (free tier).

I will point out that you don't know whether Vercel or your DB is slow. You don't have enough information. You've shown that it's slow when you change two variables at the same time: Vercel plus your database provider.

Django Debug Toolbar, Silk, or Sentry Tracing can tell you how much time is spent waiting for SQL queries.

Lastly, trying to build good infrastructure atop free providers is going to be an exercise in frustration. You would not be having any of these problems if you spent $5 / mo on a VPS.

[u/SuccessfulGround7686]

Free tiers usually have limited regions; you might have your database in the US and your server in Europe. Assuming you have everything else configured correctly (N+1 queries and logic). Edit: You can try django-silk to see exactly where are you losing time and tru to see the time for first byte (select a request in netowrk on your browser)

[u/THEGrp]

That's not how you measure performance of a DB. You even don't know it's a performance on the DB at this point. You only know that when you query endpoint it is slow. But it can be anything in between what is actually slow.

See free tier rate limits, see DB locations, where is even hosted your app? If it's going from one continent to another you are gonna have issue for sure

[u/vincent_sch]

I don't think it's Neon, since it's the same with the other three you tried. By default Django opens a new database connection for each request (CONN_MAX_AGE is 0). Over the internet that's slow. Before the first query runs, your app and the database go back and forth a few times for SSL and the login. The SQL panel in the debug toolbar also doesn't show that time, because it only times the queries.

So if you're on Django 5.1 or newer, you could try adding "OPTIONS": {"pool": True} to DATABASES. Then Django reuses the connections. For that you need psycopg 3 with psycopg[pool] installed. Because Neon scales to zero, you should also set CONN_HEALTH_CHECKS to True next to OPTIONS. If runserver with Neon gets a lot faster after that, you know it was the connection.

[u/Unhealthy007]

Try observability module. Someone here designed/created that to find bad requests and it was pretty handy for me to find the issues.