We have 55 indexes on a 1M row table. Without partial indexes, writes would be painfully slow. Here’s how partial indexes keep our insert performance acceptable while giving us fast reads.
The Problem
Most of our queries filter by is_active = true AND visibility = 'public'. Only ~60% of rows match this condition. But a full index includes ALL rows — even the 40% we never query.
Full Index vs Partial Index
-- Full index: indexes ALL 1M rows
CREATE INDEX idx_jobs_posted ON jobs (posted_date DESC);
-- Partial index: only indexes ~600K active public rows
CREATE INDEX idx_jobs_posted_active ON jobs (posted_date DESC)
WHERE is_active = true AND visibility = 'public';
The partial index is 40% smaller, which means:
- Faster index scans (less data to traverse)
- Faster writes (less to update on INSERT/UPDATE)
- Less disk space
- Better cache utilization (index fits in memory)
Real Examples From Production
Job search (most common query)
CREATE INDEX idx_jobs_active_public
ON jobs (is_active, visibility)
WHERE is_active = true AND visibility = 'public';
Skills lookup (GIN index on array, only active jobs)
CREATE INDEX idx_jobs_skills_gin
ON jobs USING GIN (skills)
WHERE is_active = true AND visibility = 'public';
Dedup check (only checks against active jobs)
CREATE INDEX idx_jobs_content_dedup
ON jobs (lower(title), lower(company))
WHERE is_active = true;
Match recalculation (only matches worth recalculating)
CREATE INDEX idx_job_matches_stale_recalc
ON job_matches (user_id, job_id)
WHERE match_percentage >= 50;
This last one only indexes matches above 50% — the ones we actually recalculate. Low matches are ignored, saving index space.
The Trade-Off
PostgreSQL only uses a partial index when the query’s WHERE clause implies the index condition. If you query without WHERE is_active = true, the partial index won’t be used.
This is usually fine — if you’re always filtering by the same condition, the partial index is strictly better than a full one.
These indexes power MisuJob, keeping 55 indexes performant on 1M+ rows with 10K daily writes.
What’s your favorite PostgreSQL feature that most people don’t know about?
