Database Performance
When applications slow down, the database is frequently the culprit. A single inefficient query can dominate response times, and certain patterns — like the infamous N+1 problem — create performance disasters that scale with your data. Understanding database performance is essential for building responsive applications.
The N+1 Query Problem
The N+1 problem is the most common database performance issue. It happens when code fetches a list of items, then makes a separate query for related data on each item:
# Bad: N+1 queries
users = User.query.all() # 1 query
for user in users:
print(user.orders) # N queries (one per user)
With 100 users, this executes 101 queries. With 10,000 users, it's 10,001 queries. The fix is eager loading — fetching related data in a single query:
# Good: 1-2 queries total
users = User.query.options(joinedload(User.orders)).all()
This pattern appears in every ORM and framework. Learn to recognize it in your tools.
Understanding Query Plans
Every database can explain how it executes queries. In PostgreSQL and MySQL, prefix your query with EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
The output shows whether the database uses indexes, how many rows it scans, and what operations it performs. A "sequential scan" on a large table usually signals a missing index.
Learning to read query plans takes practice, but it's invaluable. When a query runs slowly, EXPLAIN tells you why.
Practical Optimizations
Add appropriate indexes for columns used in WHERE clauses, JOIN conditions, and ORDER BY. But don't over-index — each index slows down writes and consumes storage.
Limit data fetched by selecting only needed columns and using pagination. Fetching 10,000 rows when you display 20 wastes resources at every layer.
Use connection pooling to avoid the overhead of establishing new database connections for each request. Most frameworks handle this automatically, but verify it's configured correctly.
Caching Database Results
For expensive queries that don't change frequently, caching eliminates database load entirely:
@cache(ttl=300)
def get_popular_products():
return Product.query.order_by(
Product.views.desc()
).limit(10).all()
This query runs once every 5 minutes instead of on every request. For read-heavy applications, strategic caching transforms performance.
When to Investigate
Watch for queries taking more than 100ms, queries running thousands of times per minute, and growing response times as data increases. These patterns indicate database performance problems worth investigating.