PostgreSQL Partial Indexes and Expression Indexes: Cutting Your Mobile Backend Query Times by 90%

TL;DR

Most mobile backends index entire tables when queries only touch a fraction of rows. By applying partial indexes to soft-deleted records, expression indexes to JSONB columns, and covering indexes with INCLUDE to eliminate heap fetches, we reduced p95 query latency from 120ms to 11ms on a production backend handling 50K RPM, while shrinking total index size by 60%.

---

The problem: your indexes are doing too much work

After building enough production systems for mobile apps, I’m convinced the most common database performance mistake is over-indexing. Teams create broad B-tree indexes across entire tables, then can’t figure out why API response times degrade as data grows.

The instinct is to treat indexes as a “set it and forget it” concern. But think about a 20M-row table where 85% of records are soft-deleted. Your index is carrying 17M rows it will never serve in 99% of queries. That’s absurd.

Three techniques fixed this for us on a mobile backend serving 50K requests per minute.

---

Technique 1: partial indexes for soft-deleted records

Our orders table had 22M rows. 18.5M were soft-deleted (deleted_at IS NOT NULL). Every query filtered on WHERE deleted_at IS NULL, but our index covered all 22M rows.

Before:

CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Index size: 472 MB | Avg query: 86ms

After:

CREATE INDEX idx_orders_user_id_active ON orders(user_id)
  WHERE deleted_at IS NULL;
-- Index size: 75 MB | Avg query: 8ms

The EXPLAIN ANALYZE output confirmed an index-only path with 84% fewer pages scanned. One gotcha: the partial index predicate must match your query’s WHERE clause exactly. PostgreSQL’s planner is literal about this.

---

Technique 2: expression indexes for JSONB columns

Our mobile clients send user preferences as JSONB. The most frequent query filtered on a nested key:

SELECT * FROM user_profiles
WHERE preferences->>'theme' = 'dark' AND active = true;

A GIN index on the entire preferences column was 310 MB. Total overkill. An expression index targeting the exact path did the job with a fraction of the overhead:

CREATE INDEX idx_profiles_theme ON user_profiles ((preferences->>'theme'))
  WHERE active = true;
-- Combines expression index + partial index

94% reduction in index size. Moved us from a sequential scan to a bitmap index scan. I was honestly surprised how much of a difference this made compared to the GIN approach.

---

Technique 3: covering indexes (INCLUDE) to kill heap fetches

The last bottleneck was heap fetches. Our API’s listing endpoint needed user_id, status, and created_at. Even with an index on (user_id, status), PostgreSQL had to visit the heap to retrieve created_at.

CREATE INDEX idx_orders_listing ON orders(user_id, status)
  INCLUDE (created_at, total_amount)
  WHERE deleted_at IS NULL;

The INCLUDE clause stores extra columns in the index leaf pages without affecting sort order. Result: true index-only scans with zero heap fetches.

---

Production benchmarks (50K RPM)

Metric	Before	After	Improvement
p50 latency	45 ms	5 ms	89%
p95 latency	120 ms	11 ms	91%
p99 latency	340 ms	28 ms	92%
Total index size	1.8 GB	720 MB	60%
Heap fetches/sec	12,400	380	97%
Buffer cache hit ratio	91%	99.2%	+8.2pp

Collected over a 72-hour window using pg_stat_user_indexes and pg_statio_user_tables on PostgreSQL 16 running on a 4-vCPU / 16GB instance. Smaller indexes meant more of the working set fit in shared_buffers, which created a compounding effect where each optimization made the others work better too.

---

Index comparison matrix

Index type	Best for	Trade-off
Partial	Tables with skewed filter predicates (soft deletes, status flags)	Must match WHERE clause exactly
Expression	JSONB paths, computed values, lower() text searches	Immutable expressions only
Covering (INCLUDE)	Eliminating heap fetches on read-heavy endpoints	Larger leaf pages, slower writes
Composite (traditional)	Multi-column equality + range queries	Full table coverage, larger size

---

When not to use these

Partial indexes hurt if your filter predicate changes frequently. Each schema migration means a REINDEX. Expression indexes only work with immutable functions; stick now() in a predicate and it’ll fail. Covering indexes add write overhead since every INSERT and UPDATE must maintain the extra leaf data. Profile with EXPLAIN (ANALYZE, BUFFERS) before committing to any of this.

---

What to do with this

Audit your soft-delete pattern. Run SELECT count(*) FILTER (WHERE deleted_at IS NOT NULL) FROM your_table. If more than half the rows are deleted, a partial index will cut both index size and query latency right away.

If you’re using GIN indexes on JSONB, you’re probably over-indexing. Check your top JSONB query patterns with pg_stat_statements and create expression indexes matching those exact paths. We saw roughly 10x size reduction.

For high-traffic read endpoints, look at pg_statio_user_tables for heap fetch counts. If an index scan keeps pulling 1-2 extra columns from the heap, INCLUDE turns that expensive random I/O into free index-only reads. This was the easiest win of the bunch for us.

PostgreSQL gives you fine-grained control over what gets indexed and how. 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.