Why architecture matters here
Wrong indexes are worse than no indexes. Every index is maintained on every write. Duplicate or unused indexes waste storage + write throughput. Missing indexes cause table scans.
The architecture matters because index choice is workload-dependent. With the pieces mapped, you can audit and optimize.
The architecture: every piece explained
The top strip is the mechanics. Query planner picks index based on statistics. B-tree index handles range + point queries; the workhorse. LSM index optimizes writes at cost of read amplification. Hash index only serves equality but is cheap.
The middle row is the specialties. Partial index covers a WHERE-filtered subset — great when 90% of rows have status=archived. Expression index indexes lower(email) or a JSON path — enables case-insensitive lookup. Covering index includes payload columns so queries answer from index alone. Concurrency: MVCC + latches keep indexes consistent under load.
The lower rows are ops. Maintenance: rebuild + reindex + statistics update. Observability: index usage + bloat. Ops: audit unused indexes, cost + write penalty analysis.
End-to-end flow
End-to-end: a user table has B-tree on user_id (PK), partial index on active=true, expression index on lower(email), and covering index on (org_id, created_at) INCLUDE (name). Query "SELECT name FROM users WHERE org_id=X AND created_at > Y AND active=true" uses the partial + covering combination — no table read needed. Weekly audit finds an unused index; dropped, saving write penalty.