Database query optimization in Flask - Performance & Optimization

Performance: Database query optimization

HIGH IMPACT

This affects how fast data loads from the database, impacting page load speed and responsiveness.

Fetching user data with related posts

Flask

from sqlalchemy.orm import joinedload
users = User.query.options(joinedload(User.posts)).all()

Loads users and their posts in a single query using eager loading, reducing database hits.

📈 Performance Gainsingle query regardless of user count, drastically reducing load time

Fetching user data with related posts

Flask

users = User.query.all()
for user in users:
    posts = Post.query.filter_by(user_id=user.id).all()

This runs one query for users plus one query per user for posts, causing many database hits (N+1 problem).

📉 Performance Costtriggers N+1 queries, increasing load time linearly with number of users

Performance Comparison

Pattern	DOM Operations	Reflows	Paint Cost	Verdict
N+1 Query Pattern	Minimal	Minimal	High due to delayed data	[X] Bad
Eager Loading with Joined Query	Minimal	Minimal	Low due to fast data fetch	[OK] Good

Rendering Pipeline

Database query optimization reduces the time waiting for data before rendering can start, improving the critical rendering path.

→Data Fetching

→Rendering Start

⚠️ BottleneckData Fetching from database

Core Web Vital Affected

LCP

This affects how fast data loads from the database, impacting page load speed and responsiveness.

Optimization Tips

1Avoid running queries inside loops to prevent many database hits.

2Use eager loading to fetch related data in a single query.

3Add indexes on columns used in filters to speed up queries.

Performance Quiz - 3 Questions

What is the main performance problem with running a query inside a loop to fetch related data?

A It reduces the number of queries, improving speed.

B It causes many database queries, increasing load time.

C It caches data automatically, saving time.

D It only affects CSS rendering, not data loading.

DevTools: Network

How to check: Open DevTools, go to Network tab, filter by XHR or fetch, and observe the number of API/database calls made during page load.

What to look for: Multiple repeated calls for similar data indicate N+1 query issues; a single call fetching all data is ideal.