Index (Database)

Detailed Explanation

An index is a data structure in your database that makes queries faster. Think of it like the index in the back of a book — instead of reading every page to find a topic, you look it up in the index and jump straight to the right page.

Why Indexes Matter

Without an index, the database has to scan every single row in a table to find what you're looking for. This is called a "full table scan" and it gets slow as your table grows. With an index, the database can jump directly to the matching rows.

When to Add Indexes

Add indexes on columns you frequently filter or sort by:

• Foreign keys like user_id (finding all notifications for a user)
• Status columns like is_read (finding unread notifications)
• Timestamp columns like created_at (sorting by newest)

Example

In Threadly's notifications system, we create two indexes:

CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_unread ON notifications(user_id, is_read);

The first index makes "get all notifications for this user" fast. The second makes "get unread notifications for this user" even faster because it covers both columns.

Trade-offs

Indexes speed up reads but slightly slow down writes (INSERTs and UPDATEs), because the database has to update the index too. For most web apps, this trade-off is worth it since you read data far more often than you write it.

Indexes work alongside primary keys (which are automatically indexed), foreign keys, and RLS policies.