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

Optimization Pattern	Problem It Solves	Impact
Keyset Cursor Pagination	Eliminates slow OFFSET table scans and duplicate items on infinite feeds.	From $O(N)$ degradation to constant $O(\log N)$ query times.
selectinload / joinedload	Eliminates the N+1 query explosion across relationships.	Drops 40+ round-trips down to 2–3 batched queries.
Optimistic Locking	Prevents lost update bugs and eliminates database row lock contention.	High write concurrency without deadlocks.
Unique DB Constraints	Enforces data rules directly inside PostgreSQL engine.	Zero duplicate likes or duplicate follow relations.
Composite Indexes	Powers feed queries and keyset lookups directly from RAM indexes.	Millisecond response times even with millions of rows.

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