---
title: "PostgreSQL Partial and Expression Indexes: Cut Mobile Backend Query Times by 90%"
published: true
description: "A hands-on workshop on partial indexes, expression indexes, and covering indexes that took our production mobile backend from 120ms to 11ms at 50K RPM."
tags: postgresql, performance, api, architecture
canonical_url: https://blog.mvpfactory.co/postgresql-partial-expression-indexes-90-percent-faster-queries
---
## What You Will Build
By the end of this tutorial, you will have three PostgreSQL index techniques in your toolkit that reduced our production p95 latency from 120ms to 11ms while shrinking total index size by 60%. We are working with a real mobile backend handling 50K requests per minute — not toy benchmarks.
Here is the minimal setup to get this working on your own tables today.
## Prerequisites
- PostgreSQL 15+ (16 recommended — its planner is smarter about partial indexes)
- A table with soft deletes (`deleted_at` column) or JSONB columns
- Access to run `EXPLAIN (ANALYZE, BUFFERS)` against your database
- Basic familiarity with B-tree indexes
## Step 1: Partial Indexes for Soft-Deleted Records
Let me show you a pattern I use in every project. If your table has soft deletes, your indexes are carrying dead weight.
Our `orders` table had 22M rows. 18.5M were soft-deleted. Every query filtered on `WHERE deleted_at IS NULL`, but the index covered all 22M rows.
sql
-- Before: indexing the entire table
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Index size: 472 MB | Avg query: 86ms
-- After: index only the rows queries actually touch
CREATE INDEX idx_orders_user_id_active ON orders(user_id)
WHERE deleted_at IS NULL;
-- Index size: 75 MB | Avg query: 8ms
Run this on your own tables to see the opportunity:
sql
SELECT
count() AS total,
count() FILTER (WHERE deleted_at IS NOT NULL) AS deleted,
round(100.0 * count() FILTER (WHERE deleted_at IS NOT NULL) / count(), 1) AS deleted_pct
FROM orders;
If more than half your rows are deleted, this is your biggest quick win.
## Step 2: Expression Indexes for JSONB Columns
Our mobile clients store preferences as JSONB. The most frequent query hit a single nested key:
sql
SELECT * FROM user_profiles
WHERE preferences->>'theme' = 'dark' AND active = true;
A GIN index on the entire `preferences` column was 310 MB. Total overkill. We combined an expression index with a partial predicate:
sql
CREATE INDEX idx_profiles_theme ON user_profiles ((preferences->>'theme'))
WHERE active = true;
That took us from a sequential scan to a bitmap index scan with a 94% reduction in index size. The docs do not mention this, but you can stack expression and partial index techniques in a single `CREATE INDEX` statement — and the planner handles it cleanly.
## Step 3: Covering Indexes to Kill Heap Fetches
The last bottleneck was random I/O. Our listing endpoint needed `user_id`, `status`, and `created_at`. Even with an index on `(user_id, status)`, PostgreSQL visited the heap for `created_at`.
sql
CREATE INDEX idx_orders_listing ON orders(user_id, status)
INCLUDE (created_at, total_amount)
WHERE deleted_at IS NULL;
`INCLUDE` stores extra columns in the index leaf pages without affecting sort order. Result: true index-only scans, zero heap fetches. This was the easiest win of the bunch for us.
## Production Results (50K RPM, 72-Hour Window)
| Metric | Before | After | Improvement |
|---|---|---|---|
| p50 latency | 45 ms | 5 ms | 89% |
| p95 latency | 120 ms | 11 ms | 91% |
| Total index size | 1.8 GB | 720 MB | 60% |
| Heap fetches/sec | 12,400 | 380 | 97% |
Smaller indexes meant more working set fit in `shared_buffers`, creating a compounding effect where each optimization made the others work better.
## Gotchas
Here is the gotcha that will save you hours:
- **Partial index predicates are literal.** `WHERE deleted_at IS NULL` in your index will not match a query with `WHERE deleted_at IS NULL AND status = 'active'` unless the planner can derive the subset. Always verify with `EXPLAIN`.
- **Expression indexes require immutable functions.** Put `now()` in a predicate and it fails at creation. Stick to operators like `->>'key'` or `lower()`.
- **Covering indexes slow writes.** Every `INSERT` and `UPDATE` maintains the extra leaf data. Profile write-heavy tables before adding `INCLUDE` columns.
- **GIN is not always wrong.** If your JSONB queries are truly varied and ad-hoc, GIN still wins. Expression indexes target known, stable access patterns.
## What to Do Next
Start with `pg_stat_statements` to find your top five queries by total time. For each one:
1. Check if soft-delete filtering makes a partial index viable
2. Check if JSONB access targets a stable path for an expression index
3. Check `pg_statio_user_tables` for heap fetches that `INCLUDE` can eliminate
The gap between a generic index strategy and a targeted one is often the gap between a backend that chokes at 5K RPM and one that handles 50K without breaking a sweat. PostgreSQL gives you the tools — you just have to be specific about what you ask it to index.
Top comments (0)