Instagram Clone Backend — Part 5: Performance & Database Optimization (Pagination, Zero N+1 Queries, Optimistic Locking & Integrity Constraints)

When you have 10 users, almost any database schema and API structure will feel fast. But when your Instagram clone scales to thousands of active users refreshing feeds, uploading posts, and liking photos simultaneously, poorly tuned database queries will bring your entire system to a crawl.

In this tutorial, we dive into the core performance engineering patterns we implemented in our backend:

1. Keyset / Cursor-Based Pagination (Eliminating slow OFFSET / LIMIT scans)
2. Zero N+1 Query Architecture (Using SQLAlchemy eager loading with selectinload & joinedload)
3. Optimistic Locking & Concurrency Control (Preventing lost updates and race conditions)
4. Database-Level Integrity Constraints (Foreign keys, uniqueness, and composite indexes)

1. Keyset (Cursor-Based) Pagination vs. Offset Pagination

Most beginner tutorials implement pagination using SQL LIMIT and OFFSET:

-- ❌ The Naive Offset Way: SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000; 

Why OFFSET Fails on Social Feeds:

1. Disastrous Performance ($O(N)$ table scans): For OFFSET 10000, PostgreSQL must read 10,020 rows off the disk, sort them in memory, discard the first 10,000, and return only the last 20. As the user scrolls deeper, requests get slower and consume high CPU.
2. The "Page Drift" Bug: If a user is on Page 1 and someone publishes 3 new posts, scrolling to Page 2 causes items to shift down—the user sees duplicate posts they already scrolled past!

The Modern Solution: Keyset (Cursor) Pagination

Instead of telling the database how many rows to skip, we tell the database the exact cursor coordinates of the last item the user saw (created_at timestamp and tie-breaker id):

-- ✅ The Keyset (Cursor) Way: SELECT id, caption, image_url, created_at FROM posts WHERE (created_at, id) < ('2026-10-21 09:30:00.000', 482) ORDER BY created_at DESC, id DESC LIMIT 20; 

Why Keyset Pagination is Superior:

• Consistent $O(\log N)$ or $O(1)$ Performance: Thanks to our composite B-Tree index, PostgreSQL jumps directly to the cursor index entry in microseconds, regardless of whether you are looking at row 10 or row 1,000,000.
• Immune to Page Drift: New posts added to the top of the feed never push older posts into subsequent pages.

Implementing Cursor Pagination in Python:

import base64 from datetime import datetime from typing import Optional, Tuple from sqlalchemy import select, and_, tuple_ def decode_cursor(cursor_str: str) -> Tuple[datetime, int]: """Decodes a base64 cursor string into (created_at, id).""" raw = base64.b64decode(cursor_str.encode()).decode() created_at_iso, post_id = raw.split("__") return datetime.fromisoformat(created_at_iso), int(post_id) def encode_cursor(created_at: datetime, post_id: int) -> str: """Encodes (created_at, id) into a URL-safe base64 cursor string.""" raw = f"{created_at.isoformat()}__{post_id}" return base64.b64encode(raw.encode()).decode() async def get_paginated_feed(db: AsyncSession, limit: int = 20, cursor: Optional[str] = None): query = select(Post).order_by(Post.created_at.desc(), Post.id.desc()).limit(limit + 1) if cursor: last_created_at, last_id = decode_cursor(cursor) # Fast tuple comparison utilizing our composite index query = query.where( tuple_(Post.created_at, Post.id) < tuple_(last_created_at, last_id) ) result = await db.execute(query) posts = list(result.scalars().all()) has_next = len(posts) > limit items = posts[:limit] next_cursor = encode_cursor(items[-1].created_at, items[-1].id) if (has_next and items) else None return {"items": items, "next_cursor": next_cursor, "has_more": has_next} 

2. Eliminating the N+1 Query Problem

The N+1 Query Problem is the #1 silent performance killer in ORM-based web applications.

What is the N+1 Bug?

Imagine fetching 20 posts for your home feed. For each post, you also want to display the author's username and the list of comments:

# ❌ The N+1 Disaster: posts = await db.scalars(select(Post).limit(20)) for post in posts: # Triggers 1 query to fetch author! # Triggers 1 query to fetch comments! print(post.author.username, len(post.comments)) 

• 1 query to fetch the 20 posts.
• 20 queries to fetch each post's author.
• 20 queries to fetch each post's comments.
• Total: 41 database round-trips for a single screen! Over a network connection, this adds massive latency.

The Solution: Explicit Eager Loading in SQLAlchemy 2.0

SQLAlchemy provides two high-performance eager loading strategies:

1. joinedload (Single SQL LEFT OUTER JOIN): Ideal for many-to-one relationships (e.g., loading Post.author).
2. selectinload (Secondary IN (...) Query): Best for one-to-many relationships (e.g., loading Post.comments and Post.likes), avoiding Cartesian product multiplication bugs.

from sqlalchemy import select from sqlalchemy.orm import joinedload, selectinload async def get_optimized_feed(db: AsyncSession): query = ( select(Post) # 1. Join author directly in the primary SELECT .options(joinedload(Post.author)) # 2. Fetch all comments for all 20 posts in ONE single secondary SELECT ... WHERE post_id IN (...) .options(selectinload(Post.comments)) # 3. Fetch all likes for all 20 posts in ONE single secondary SELECT ... WHERE post_id IN (...) .options(selectinload(Post.likes)) .order_by(Post.created_at.desc(), Post.id.desc()) .limit(20) ) result = await db.execute(query) posts = result.unique().scalars().all() # Executed in exactly 3 batch queries total instead of 61! return posts 

3. Optimistic Locking & Concurrency Control

In a high-traffic social platform, multiple users can modify state at the exact same instant:

• What happens if two moderators edit a post caption at the same millisecond?
• What happens when a user attempts to accept a follow request that was just cancelled?

Pessimistic vs. Optimistic Locking

• Pessimistic Locking (SELECT FOR UPDATE): Locks database rows at the start of a transaction, preventing others from reading or writing until committed. This causes severe bottlenecks and potential deadlocks under high load.
• Optimistic Locking: Allows concurrent reads and writes without row locks. We simply add a version_id column to the record. When saving changes, we ensure the version has not incremented since we read it.

from sqlalchemy import Integer from sqlalchemy.orm import Mapped, mapped_column class Post(Base): __tablename__ = "posts" id: Mapped[int] = mapped_column(primary_key=True) caption: Mapped[str] = mapped_column(String(2200)) version_id: Mapped[int] = mapped_column(Integer, default=1, nullable=False) __mapper_args__ = { "version_id_col": version_id # Enables SQLAlchemy native optimistic locking } 

When an update is issued:

UPDATE posts SET caption = 'New Caption', version_id = version_id + 1 WHERE id = 42 AND version_id = 1; 

If another transaction updated the post first, version_id is now 2. The WHERE clause matches 0 rows, and SQLAlchemy raises a StaleDataError. We can catch this error and gracefully notify the user instead of silently overwriting someone else's changes!

4. Database-Level Integrity Constraints & Composite Indexes

Never rely purely on application code for data consistency—bugs happen! Production databases must enforce business rules at the schema level.

1. Unique Compound Constraints (Duplicate Likes & Follows)

A user should never be able to like the same post multiple times:

class Like(Base): __tablename__ = "likes" user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE")) post_id: Mapped[int] = mapped_column(ForeignKey("posts.id", ondelete="CASCADE")) __table_args__ = ( # PostgreSQL generates a unique index preventing duplicate entries UniqueConstraint("user_id", "post_id", name="uq_user_post_like"), ) 

2. Tailored Composite Indexes (Alembic Migration 0004_feed_indexes)

Feed generation is the most frequent query in Instagram. In our migration files, we introduced tailored composite indexes:

# From alembic/versions/0004_feed_indexes.py: # 1. Composite index for filtering user posts and sorting by created_at & id op.create_index( "idx_posts_author_created_id", "posts", ["author_id", "created_at", "id"] ) # 2. Composite covering index to quickly resolve a user's approved followers op.create_index( "idx_follows_follower_status", "follows", ["follower_id", "status", "following_id"] ) 

Why Order Matters in Composite Indexes:

In the index ["author_id", "created_at", "id"]:

1. author_id quickly narrows down the table to posts by that specific creator.
2. created_at provides a pre-sorted tree ordering for reverse-chronological feeds.
3. id acts as a unique tie-breaker cursor for exact keyset pagination pagination.

Because all three columns are in the index tree, PostgreSQL performs an Index-Only Scan without touching the disk table blocks!

Performance Summary Checklist

In the next post, we will explore Testing, CI & Reliability: pytest-asyncio, HTTPX TestClient, and Integration Suites!