PostgreSQLmedium

What is a partial index in PostgreSQL and when is it useful?

Answer

A partial index indexes only rows that match a WHERE condition. It’s useful when most rows don’t need to be indexed (e.g., only active or not-deleted rows), making the index smaller and faster to scan and maintain.

CREATE INDEX idx_users_active_email
ON users (email)
WHERE active = true AND deleted_at IS NULL;

Related questions

PostgreSQL

How do `pg_stat_activity` and `pg_stat_statements` help in troubleshooting?

#postgres#monitoring#pg_stat_activity

PostgreSQL

Materialized view vs view: what’s the difference?

#postgres#views#materialized-view

PostgreSQL

LISTEN/NOTIFY: what problem does it solve?

#postgres#listen-notify#pubsub

PostgreSQL

Advisory locks: what are they and when would you use them?

#postgres#locks#advisory

PostgreSQL

BRIN index: when is it useful?

#postgres#brin#indexes

PostgreSQL

GIN vs GiST indexes: when would you use each?

#postgres#indexes#gin