B-tree is PostgreSQL’s default index method for equality, ranges, and ordered access. A useful index follows a real query’s filters, ordering, and selectivity; column order and sort direction affect which prefixes are usable. Every index also consumes storage, memory, WAL, vacuum effort, and write time, so verification and removal criteria matter.
An index is a sorted access structure, not a speed switch. Design from the query:
Candidate:
Equality columns generally form a useful prefix, followed by ordering/range needs, but workload and PostgreSQL capabilities require measurement.
Capture baseline plan, build the index in the learning environment, refresh statistics if appropriate, repeat representative plans, and compare execution, buffers, and write/storage effects. For production, CREATE INDEX CONCURRENTLY reduces blocking but takes longer, uses more resources, has transaction restrictions, and can leave an invalid index after failure; follow current docs and monitor it.
PostgreSQL does not automatically index referencing foreign-key columns. Index them when parent updates/deletes or child lookup joins need it.
Tenant, active-version, access-policy, and time filters often surround vector or full-text search. Their relational indexes prevent AI retrieval from scanning unrelated data. An embedding index cannot compensate for a slow document-version or authorization join.
Run inbox queries with different status/selectivity and ordered limits before/after the index. Show which index prefix each query can use. Measure index size with pg_relation_size and insert/update impact in a repeatable local benchmark.
Design indexes for “latest tickets by customer within tenant” and “all tickets for a customer.” Decide whether one index serves both.
Acceptance criterion: plans demonstrate usable prefixes and you quantify the extra index’s marginal value.