When would you choose a partial index over a regular index?

Use a partial index when: 1) Only a fraction of rows are queried (e.g., WHERE status = 'PENDING' when 90% of orders are 'COMPLETED'). 2) A specific value is queried far more than others. Benefits: smaller index (only indexes matching rows → faster scans, less memory, faster maintenance). Example: CREATE INDEX ON orders(order_date) WHERE status = 'ACTIVE'. This index is 10× smaller than a full inde