Index answer

An index helps the database find rows faster, but every index also costs storage and write overhead.

Start from a real query

Indexing has strong search intent because slow queries become obvious as apps grow.

Do not index every column. Index the queries your app actually runs, especially filters, joins, and ordering patterns.

Query pattern Index clue
WHERE user_id = ? Index user_id
ORDER BY created_at Consider ordering index
WHERE user_id AND status Composite index may help
Tiny table Index may not matter yet

Index a fraud-review lookup

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

This example is intentionally small. In a real codebase, the surrounding details matter: naming, error handling, tests, runtime config, permissions, and how easy the next developer can understand the change.

Read the query plan before and after

  • Look at real slow queries.
  • Use EXPLAIN before guessing.
  • Create composite indexes for common combined filters.
  • Remove unused indexes when write cost matters.
  • Keep pagination queries index-friendly.

Indexes that cost more than they save

  • Adding indexes without measuring.
  • Indexing low-value columns randomly.
  • Forgetting indexes slow writes.
  • Using functions in queries that bypass indexes.
  • Ignoring sort order in pagination.

Explain the workload, not the keyword

Use a concrete sentence:

I used this pattern because [problem]. The main tradeoff was [tradeoff]. I verified it by [test or check].

That structure works because it shows judgment. Anyone can name a tool. Strong developers explain why they chose it, what could go wrong, and how they checked the result.

PostgreSQL planner references

Measure on representative data

An index helps the database find rows faster, but every index also costs storage and write overhead. Keep the implementation small, verify the edge cases, and write the decision down so the next person can trust it.

Design from the query and ordering together

Imagine SentinelFi needs the latest held decisions for one account. The query filters by account and status, then orders newest first:

SELECT id, score, decision, created_at
FROM fraud_decisions
WHERE account_id = $1
  AND decision = 'review'
ORDER BY created_at DESC
LIMIT 50;

A candidate index is:

CREATE INDEX CONCURRENTLY fraud_decisions_review_queue_idx
ON fraud_decisions (account_id, decision, created_at DESC);

The column order follows the equality filters and then the requested ordering. That does not make it universally correct. If almost every row has decision = 'review', the second column may add little selectivity. If the real queue is global rather than account-specific, a different or partial index may fit better.

Capture evidence with EXPLAIN ANALYZE

Run the representative query before and after on data that resembles the expected distribution. Record execution time, rows examined, scan type, and write impact. Do not publish a “20 ms to 2 ms” claim from an empty local table or one warm-cache run.

Indexes consume disk and add work to inserts, updates, vacuum, backups, and replication. Remove redundant indexes only after checking production query usage and constraints; a unique index may enforce correctness even when it is not the fastest path for a read.

Questions before keeping an index

Which exact query needs help, how often does it run, and how many rows does it return? Does the proposed column order match equality filters, ranges, and ordering? How much additional write and storage cost appears? Is another index already a usable prefix?

Keep the before-and-after plan with the change. Recheck after the table grows because data distribution changes planner choices. An index is an operational tradeoff, not a permanent trophy attached to a column.