A partial index indexes only rows matching a predicate—smaller and faster when queries always filter the same way (active users, open tickets).
Creating partial indexes
CREATE INDEX idx_users_active_email
ON users (email)
WHERE active = true;
CREATE UNIQUE INDEX idx_orders_open_number
ON orders (order_number)
WHERE status = 'open';Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.
Matching queries
SELECT email FROM users
WHERE active = true AND email LIKE 'a%';The planner can use the partial index when the query predicate implies the index WHERE clause.
Trade-offs
Partial indexes are invisible to generic ORMs unless migrations specify them—document predicates for the team.
Important interview questions and answers
- Q: Partial unique index?
A: Enforces uniqueness only among rows matching the predicate—e.g. one open order number per customer segment. - Q: When not to use?
A: When application queries lack the matching filter—planner will ignore the index.
Self-check
- What clause defines which rows enter a partial index?
- Why are partial indexes smaller?
Tip: Document the partial index predicate—ORMs won't infer it automatically.
Interview prep
- Partial index?
Indexes subset of rows matching a WHERE predicate.
- When useful?
Queries always filter the same way (e.g. active = true).