Partial Indexes & Left-Prefix Rule

Shrink index sizes by 99% by only indexing the rows that matter

⏱ 10 min

A Partial Index restricts the index to only contain rows that satisfy a specific WHERE clause. This is the ultimate database hack for keeping indexes small, keeping them entirely in RAM, and making writes much cheaper.

Key Concepts

Tiny Memory Footprint

If you have 10 million soft-deleted rows and 50,000 active rows, a partial index on status='ACTIVE' is 200x smaller. Smaller indexes stay completely in RAM.

Cheaper Writes

A standard index slows down every single INSERT in the table. A partial index only slows down inserts if the new row matches the WHERE condition.

Free Unique Constraints

You can enforce uniqueness conditionally. E.g. 'A user can only have ONE active session, but unlimited closed sessions'. CREATE UNIQUE INDEX ON sessions (user_id) WHERE status='ACTIVE'.

Standard vs Partial Index

The Mathematical Reality of Partial Indexes

When tables grow to billions of rows, a standard B-Tree index can become massive (e.g., 50GB). If your database only has 32GB of RAM, the index won't fit, causing immediate performance degradation due to swapping to disk.

**The Problem:** Indexing 10,000,000 users where 99% are inactive (banned/deleted). Standard index size ≈ 500MB.
**The Solution:** `CREATE INDEX ON users(email) WHERE status = 'ACTIVE'`.
**The Result:** The partial index only contains 50,000 rows. New index size ≈ 2MB. It is 250x smaller, stays perfectly in CPU Cache/RAM, and makes active-user lookups lightning fast.

PostgreSQL Prisma Examples

schema.prismaprisma

1// 1. Partial Index for Queue/Background Jobs
2// Only index the "PENDING" jobs. The index shrinks as jobs complete.
3model Job {
4  id        String   @id
5  type      String
6  status    String   // PENDING, PROCESSING, COMPLETED, FAILED
7  payload   Json
8
9  @@index([type], map: "idx_pending_jobs") // Prisma currently requires manual raw SQL for WHERE clauses on indexes
10}
11
12// Behind the scenes SQL:
13// CREATE INDEX idx_pending_jobs ON "Job"(type) WHERE status = 'PENDING';
14
15// 2. Conditional Uniqueness
16model Subscription {
17  id         String
18  userId     String
19  status     String // ACTIVE, CANCELLED
20  
21  // A user can have many CANCELLED subscriptions, but ONLY ONE ACTIVE subscription
22  // CREATE UNIQUE INDEX idx_one_active_sub ON "Subscription"(userId) WHERE status = 'ACTIVE';
23}

💡

Senior Architect Insight: The most common use cases for Partial Indexes are: 1) Queues (indexing only un-processed items), 2) Soft Deletes (indexing where deleted_at IS NULL), and 3) Conditional Uniqueness (ensuring a user has only one 'Default' shipping address).

PreviousComposite & Covering Indexes

Next LessonCache-Aside, Write-Through & Write-Behind