Composite index column order and covering indexes

-- ======================================================================
-- Q: Composite index: in what order do you put the columns? What is a covering
-- index?
--
-- Rule: equality columns first, then the range or ORDER BY column. The index (a,
-- b, c) serves WHERE a, WHERE a AND b, but not WHERE b alone (leftmost prefix).
-- INCLUDE adds columns to the leaf pages without making them part of the key, so
-- the query can be answered from the index (Index Only Scan, if the visibility
-- map is up to date from VACUUM). Every index has a cost on writes, so I drop
-- them when pg_stat_user_indexes shows idx_scan = 0.
-- ======================================================================

-- Query: WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50
CREATE INDEX CONCURRENTLY ix_alerts_tenant_status_created
    ON alerts (tenant_id, status, created_at DESC)
    INCLUDE (severity, title);           -- index-only scan, no heap fetch

-- Partial: only the open alerts, a much smaller index
CREATE INDEX CONCURRENTLY ix_alerts_open
    ON alerts (tenant_id, created_at DESC) WHERE status = 'open';