PostgreSQL interview question · Most asked
What is a partial index, and when is it useful?
Summary
A partial index covers only rows matching a WHERE condition, for example CREATE INDEX ON jobs (created_at) WHERE status = 'queued'. It is small, cheap to maintain, and fast for queries that use the same condition.
Longer Answer
Typical uses: the few 'active' or 'pending' rows in a large table, soft-deleted tables (WHERE deleted_at IS NULL), and partial unique indexes (CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL), which allow re-registering an email after a soft delete. The query's WHERE clause must imply the index predicate for the planner to use it.
Code Example
CREATE UNIQUE INDEX users_email_active ON users (lower(email)) WHERE deleted_at IS NULL;
Key Points
- Indexes only matching rows.
- Great for small 'hot' subsets.
- Partial unique indexes for soft deletes.
- Query must imply the predicate.
More most-asked PostgreSQL questions
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- How does MVCC work in PostgreSQL?
- What does VACUUM do, and how does autovacuum work?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?