PostgreSQLeasy

What is an index in PostgreSQL and how does it help performance?

Answer

An index is a separate data structure (usually B‑tree) that stores ordered keys and pointers to rows. It lets PostgreSQL find matching rows without scanning the whole table, speeding up lookups and joins at the cost of extra storage and slower writes.

CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Advanced answer

Deep dive

Expanding on the short answer — what usually matters in practice:

Context (tags): index, btree, performance
Data model and access patterns: dominant queries (read/write ratio, sorting, pagination).
Indexes: when they help vs hurt (write amplification, memory).
Consistency & transactions: what’s guaranteed and what can bite you.
Explain the "why", not just the "what" (intuition + consequences).
Trade-offs: what you gain/lose (time, memory, complexity, risk).
Edge cases: empty inputs, large inputs, invalid inputs, concurrency.

Examples

Here’s an additional example (building on the short answer):

CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Common pitfalls

Too generic: no concrete trade-offs or examples.
Mixing average-case and worst-case (e.g., complexity).
Ignoring constraints: memory, concurrency, network/disk costs.

Interview follow-ups

When would you choose an alternative and why?
What production issues show up and how do you diagnose them?
How would you test edge cases?