Database Index
A database index is an extra data structure that speeds up chosen searches, sorts, or rules. It uses storage and adds work when data changes.
Another example
A support system often finds open tickets by `tenant_id`, `status`, and `updated_at DESC`. An explain plan shows that one composite partial index cuts p95 from 780ms to 24ms. An index on `status` alone is rejected because it finds too many rows and adds write cost without helping.
Main parts
- 01Indexed keys
- 02Index structure and predicate
- 03Planner access path
Use it when
Add an index for a measured search, join, sort, unique rule, or foreign-key pattern. Check it with realistic data and an execution plan.
Do not use it when
Do not index every column or ignore extra writes and memory. Check the execution plan instead of assuming an index is used.
Name used in code
CREATE INDEX with column order, method, predicate, and measured query plan Before you ship
Check contracts and invariants, authorization, and input limits. Test retries, one-time effects, timeouts, and partial failures. Verify data integrity, compatibility, monitoring, recovery, and rollback.
Outcome: Design or review Database Index for this system while keeping data correct, secure, and easy to operate. User context: A support system often finds open tickets by `tenant_id`, `status`, and `updated_at DESC`. An explain plan shows that one composite partial index cuts p95 from 780ms to 24ms. An index on `status` alone is rejected because it finds too many rows and adds write cost without helping. Technical concept or control: Database Index. Why it fits: Add an index for a measured search, join, sort, unique rule, or foreign-key pattern. Check it with realistic data and an execution plan. Do not use it when: Do not index every column or ignore extra writes and memory. Check the execution plan instead of assuming an index is used. Engineering requirements: Define inputs, outputs, contracts, and rules that must always hold. Set authorization and input checks. Define failures, timeouts, retries, one-time effects, data moves, and compatibility limits. Operations: Add useful signals and logs that hide sensitive data. Set capacity limits and write an operator guide. Test recovery and rollback. Acceptance criteria: Contract and failure-path tests pass. Data and authorization rules still hold during retries and partial failures. Check performance limits, monitoring, recovery, and rollback. Evidence and limits (evidence boundary): It is official for that source. Other platforms or teams may use the term in another way. Unknowns to confirm: Where the code runs, data stores, traffic shape, consistency needs, and the security boundary. Also confirm the compatibility period, service goals, and rollback limits.
Check this request
Review the current use of Database Index. Definition: A database index is an extra data structure that speeds up chosen searches, sorts, or rules. It uses storage and adds work when data changes. Release checks: Check contracts and invariants, authorization, and input limits. Test retries, one-time effects, timeouts, and partial failures. Verify data integrity, compatibility, monitoring, recovery, and rollback. Before changing code, report the evidence you found, gaps, severity, and the smallest safe fix.