Course contentsShow
← System Design
Lesson 1563 of 1,91938. Real-World System: PastebinFree lesson

Indexing for Ownership and Search

Index paste metadata by user ID, creation date, or tags for efficient user dashboard and search queries.

Indexing for Ownership and Search

What you'll learn: How to index paste metadata to enable fast user dashboards and search queries without scanning entire tables.

The Core Problem

In Pastebin, users need to view their pastes quickly, and the system may support searching pastes by tags, creation date, or content type. Without proper indexing, retrieving "all pastes by user X" or "all pastes created last week with tag 'python'" would require full table scans—catastrophically slow at scale.

Why Indexes Matter

Think of indexes like a book's table of contents. Instead of flipping through every page to find "Chapter 7," you jump directly to page 142. Similarly, a database index on user_id lets you instantly locate all rows for a specific user without checking every paste record.

Key Indexing Strategies for Pastebin

Ownership Index:
Create an index on user_id in your paste metadata table. This powers the "My Pastes" dashboard, allowing queries like SELECT * FROM pastes WHERE user_id = 12345 to execute in milliseconds instead of seconds.

Temporal Index:
Index created_at (timestamp) to support time-range queries: "Show pastes from the last 7 days." Often combined with user ID: (user_id, created_at) for "my recent pastes."

Tag/Category Index:
If pastes have tags, index the tags column (or use a separate paste_tags join table with indexed tag_id). This enables filtering like "all public Python snippets."

Composite Indexes:
For queries like "my pastes from last month sorted by creation date," a composite index (user_id, created_at) is more efficient than separate indexes.

Trade-offs

Indexes speed reads but slightly slow writes (the index must update with each insert). For read-heavy Pastebin, this trade-off is worthwhile.

Key Takeaway:
Strategically index user_id, created_at, and tags in your paste metadata to transform expensive full-table scans into instant lookups, making user dashboards and search queries performant at scale.