When should I switch to a covering index with INCLUDE to get an index-only scan?

Short answer: switch when the query is genuinely hot (high frequency, low-latency expectation) and the heap access is a measured cost.

Short answer

But one caveat: a covering index alone does not guarantee an index-only scan — that depends on the table’s visibility-map state.

Why

1. INCLUDE stores columns in leaf pages only. INCLUDE (status, total) does not add these columns to the B-tree’s sort key; they carry no comparison cost but sit there to satisfy the query without a heap visit. That’s exactly your goal.

2. Index-only scans depend on the visibility map. Postgres skips the heap only if the page is marked “all-visible”. On a write-heavy table that map goes stale between vacuums; then, even with a covering index, EXPLAIN shows Heap Fetches: N. So sometimes the real lever isn’t the index — it’s tightening autovacuum.

   CREATE INDEX idx_orders_customer
     ON orders (customer_id)
     INCLUDE (status, total);
   
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT status, total FROM orders WHERE customer_id = $1;
   -- Want: "Index Only Scan ... Heap Fetches: 0"

3. It’s a write/space tradeoff. Wider leaf pages = bigger index, more WAL, slower writes, more to keep in cache. That cost is only worth it for genuinely hot reads; don’t slap a covering index on every query.

4. INCLUDE columns are for projection only. Don’t forget this: columns added via INCLUDE can’t be used in the WHERE filter or ORDER BY; they’re there purely to satisfy the SELECT output. If you’ll also filter on that column, it has to go into the key (possibly a composite index); if you only need to return it, INCLUDE is the right place.

What to do

1. Prefer INCLUDE over adding to the key. Putting status, total into the index key can bloat internal nodes and imposes an ordering you don’t need. INCLUDE keeps the tree lean and only widens the leaf — which is all you need to cover the query.

2. Verify with EXPLAIN (ANALYZE, BUFFERS). The job isn’t done until you see “Index Only Scan” and “Heap Fetches: 0”. If heap fetches stay high, the fix may be VACUUM (or lowering autovacuum thresholds), not more index. Track whether the index is actually used via pg_stat_user_indexes; a covering index nobody touches is just write overhead.

Bottom line: personally I’d first confirm with EXPLAIN (ANALYZE, BUFFERS) that the heap access really is the dominant cost, then rebuild the customer_id index with INCLUDE (status, total), and tighten autovacuum for that table until I see Heap Fetches: 0. If the index bloats over time, tidy it up with REINDEX CONCURRENTLY. In short: if these two columns really are the read-only payload of a hot query, a covering index pays off; if not, adding an index without measuring usually just slows down the write path.