Your Database Is Fast. Your Queries Are Slow.

Your app was fast when you launched. A few months later, it's slow.

So you upgrade the database. More CPU, more RAM, a bigger plan. It gets better for a week, then it's slow again.

Most teams upgrade a second time here. Don't.

The database is probably fine. Postgres can handle millions of rows without a problem. The issue is how your app asks for the data.
The Real Problems

When an app gets slow, people blame the server. Usually it's one of these four things. None of them get fixed by a bigger server.

1. Missing Indexes

This is the most common one.

Run WHERE email = '[e-mail]' on a table with no index, and Postgres has to check every row to find the match. With 500 rows, you won't notice. With 5 million, you wait.

An index is like the index in a book. Instead of reading every page, you go straight to the right one.

sql

CREATE INDEX idx_users_email ON users(email);

That one line can take a query from 4 seconds to a few milliseconds. No new server needed.

Don't add an index to every column though. Each one slows down writes a little and uses storage. Add them to the columns you search, join, and sort on.

2. The N+1 Query Problem

This one hides inside your ORM.

You load 100 posts. Then your code reads post.author for each one. That's 1 query for the posts and 100 more for the authors. 101 queries to load one page. And your code looks normal.

1 query: SELECT * FROM posts LIMIT 100
100 queries: SELECT * FROM users WHERE id = ? (one per post)

The fix is to load the authors all at once:

SELECT * FROM posts LIMIT 100
SELECT * FROM users WHERE id IN (1,2,3,...)

Two queries instead of 101. Your ORM can do this. It's usually called eager loading. You just have to remember to use it.

3. SELECT * Everywhere

You need a name and an email. You write SELECT * because it's quick. Now you're pulling all 30 columns, including a big JSON field and a base64 image you don't need.

You're sending megabytes to use two fields.

sql

SELECT id, name, email FROM users WHERE id = 42

Ask for what you need. It reads less from disk, sends less over the network, uses less memory.

4. No Pagination

SELECT * FROM orders is fine with 50 rows on your laptop.

In production with 2 million orders, it tries to load all of them at once. The query times out or the server runs out of memory. Nobody needs 2 million rows on one screen.

sql

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0

Show 20. Let them load the next 20.
Don't Guess. Use EXPLAIN.

You don't have to guess which query is slow. Postgres tells you.

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[e-mail]';

Look at one thing:

Seq Scan — Postgres is reading the whole table. On a big table, that's your problem.
Index Scan — Postgres is using an index. Good.

See a Seq Scan on a big table in a slow query? Add an index. You found it.
Why a Bigger Server Doesn't Help

A bigger server makes a slow query a little less slow. It doesn't fix it.

A missing index means scanning millions of rows. A faster CPU scans those rows a bit faster, but it still scans all of them. You pay more to do the same wasted work. The N+1 page that runs 101 queries still runs 101 queries on a bigger box. More RAM doesn't make your code ask for data better.

A bigger server buys you time. Sometimes that's worth it before a launch. But it's a patch, and it's expensive. You still have to fix the query later.
Our Take

At Qodors, when a client says their database is slow, the server size is the last thing we check, not the first.

Almost every time, the fix is in the queries:

Indexes on the columns that get used
Fixing N+1 with eager loading
Selecting only the columns you need
Paginating anything that can grow
Reading EXPLAIN ANALYZE instead of guessing

None of this costs money. It costs some time. And it usually beats doubling your server.

Your database can handle far more than you're giving it. The real question is whether your queries are getting in the way.
Before You Upgrade

Check these first:

Are your common queries indexed? Run EXPLAIN and look for Seq Scan on big tables.
How many queries does one page run? Count them. The number is often higher than you think.
Are you pulling 30 columns to use 2?
Is anything unpaginated that could grow large?
Have you profiled it, or are you about to buy RAM on a guess?

A bigger server hides the problem.

Fixing the queries removes it.

Start with the queries. Most teams find they had enough database the whole time.

Database #PostgreSQL #SQL #PerformanceOptimization #BackendDevelopment #SoftwareEngineering #StartupCTO #TechLeadership #QodorsEdge

Written by the team at Qodors — we make slow systems fast without throwing money at hardware. → www.qodors.com