Postgres

Indexes speed reads by maintaining an ordered lookup structure, but every write has to maintain that structure.

• Use B-tree indexes for equality, range queries, sorting, and most common lookup patterns.
• Composite index order matters: put high-value equality filters first, then range or sort columns.
• Avoid indexing every column because inserts, updates, deletes, and storage all become more expensive.

Postgres protects concurrent data changes using MVCC, locks, and transaction isolation levels.

• Read Committed is the default and sees only committed data at the start of each statement.
• Repeatable Read gives a stable snapshot across the transaction and prevents non-repeatable reads.
• Serializable gives the strongest guarantees but can require retrying transactions after conflicts.

EXPLAIN helps you understand whether Postgres is scanning too much data or using the wrong access path.

• Look for sequential scans on large tables, bad row estimates, expensive sorts, and nested loops over many rows.
• Use EXPLAIN ANALYZE when it is safe because it runs the query and shows actual timing.
• Outdated statistics can mislead the planner, so VACUUM and ANALYZE matter for production performance.