DEV Community

Sir Max
Sir Max

Posted on

3 Database Query Patterns That Took Our API From 800ms to 50ms

#database #webdev #tutorial #programming

3 Database Query Patterns That Took Our API From 800ms to 50ms

Last month, our /users/search endpoint was taking 800ms on a good day. Users were complaining. The frontend team added a loading spinner (bless them). But I knew the real problem: our database queries were doing way too much work.

Here are the three patterns that changed everything — with real before/after code.

Pattern 1: Stop SELECT * When You Only Need 3 Columns

This one sounds obvious. Everyone knows you shouldn't SELECT *. But in practice? I kept finding it everywhere.

Here's what our user search query looked like:

-- Before: 320ms avg
SELECT * FROM users
WHERE status = 'active'
  AND last_login > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;
Enter fullscreen mode Exit fullscreen mode

The users table had 47 columns. bio alone was a TEXT field averaging 2KB. We were hauling megabytes of data over the wire for columns the API never returned.

-- After: 45ms avg
SELECT id, username, avatar_url, created_at
FROM users
WHERE status = 'active'
  AND last_login > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;
Enter fullscreen mode Exit fullscreen mode

A covering index sealed the deal:

CREATE INDEX idx_users_search ON users (status, last_login, created_at)
INCLUDE (username, avatar_url);
Enter fullscreen mode Exit fullscreen mode

Lesson: Don't just avoid SELECT * — audit what your ORM is actually fetching. Django's only() and SQLAlchemy's load_only() exist for a reason.

Pattern 2: N+1 Queries Are the Silent Killer

Every ORM user has heard "beware the N+1 problem." But here's the thing: it doesn't always show up in development because your local DB has 50 rows.

Our /organizations/{id}/members endpoint fetched an org, then looped through members to get each user's profile:

# Before: 600ms for an org with 30 members
org = db.query(Organization).get(org_id)
members = db.query(Member).filter_by(org_id=org_id).all()

result = []
for member in members:
    user = db.query(User).get(member.user_id)  # N queries!
    result.append({
        "id": member.id,
        "role": member.role,
        "username": user.username,
        "avatar": user.avatar_url,
    })
Enter fullscreen mode Exit fullscreen mode

30 members = 31 queries. Scale that to 200 members and you're looking at 2+ seconds.

The fix took two lines:

# After: 55ms (single query with JOIN)
members = (
    db.query(Member, User)
    .join(User, Member.user_id == User.id)
    .filter(Member.org_id == org_id)
    .all()
)

result = [
    {
        "id": m.Member.id,
        "role": m.Member.role,
        "username": m.User.username,
        "avatar": m.User.avatar_url,
    }
    for m in members
]
Enter fullscreen mode Exit fullscreen mode

If you can't restructure to a JOIN, use selectinload (SQLAlchemy) or prefetch_related (Django) to batch it into 2 queries.

How to catch these: Enable SQL query logging in your dev environment. If you see the same query pattern repeating with different IDs, you've got an N+1.

Pattern 3: Paginate With Keys, Not Offsets

This one was the biggest surprise. Our activity feed endpoint used OFFSET pagination:

-- Before: 180ms on page 1, 2400ms on page 50
SELECT * FROM activities
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20 OFFSET 1000;
Enter fullscreen mode Exit fullscreen mode

OFFSET 1000 means the database reads 1020 rows, then throws away the first 1000. On page 50 with 10M rows in the table, the query planner gives up and does a full sequential scan.

Keyset pagination (also called "cursor-based") uses a WHERE clause on the sorted column:

-- After: 8ms on any page
SELECT * FROM activities
WHERE user_id = $1
  AND created_at < $2  -- the cursor
ORDER BY created_at DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

The API contract changes slightly — you return a next_cursor instead of total_pages:

{
  "data": [...],
  "next_cursor": "2026-07-10T14:32:00Z"
}
Enter fullscreen mode Exit fullscreen mode

Tradeoff: You can't jump to page 50. But honestly — when was the last time you clicked page 50 of anything? Most users never go past page 3. The speed gain is worth it.

The Numbers

Endpoint Before After Improvement
/users/search 320ms 45ms 86% faster
/orgs/{id}/members 600ms 55ms 91% faster
/activities (page 50) 2400ms 8ms 99.7% faster

Total effort: one afternoon of profiling, one morning of refactoring.

What I'd Do Differently Next Time

  1. Profile in production first. My local DB had 500 rows. Production had 10 million. The N+1 issue was invisible locally.
  2. Add query time metrics to your APM. A dashboard showing p50/p95/p99 per endpoint would have caught this months earlier.
  3. Write a linter rule for SELECT *. Seriously. Two hours of writing a SQL lint rule saved me from ever finding this pattern in code review again.

The database is usually the bottleneck. But it's also usually the easiest thing to fix once you know where to look.

Top comments (0)