Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

Developers send you schema changes and new queries to review. What do you look for?

What the interviewer wants to hear

  • Correct data types, constraints, primary and foreign keys
  • Indexes that match the query patterns, and no unneeded duplicates
  • Locking impact of the migration on large tables and a safe rollout plan
  • Query plans on realistic data volumes, avoiding N+1 patterns and unbounded queries
  • Backward compatibility with the running application version

Model answer

For schema changes I first check modelling: sensible data types such as timestamptz and numeric for money, a primary key, foreign keys and NOT NULL or CHECK constraints so bad data cannot get in. Then I look at how the change will be applied on a big live table: does it take a long ACCESS EXCLUSIVE lock or rewrite the table, does it need CREATE INDEX CONCURRENTLY, a lock_timeout, or batched backfills. It must be backward compatible with the app version still running, so renames happen in expand and contract steps. For queries I ask for the access pattern and run EXPLAIN on realistic data, check that indexes match the WHERE, JOIN and ORDER BY, and look for N+1 loops, SELECT star on wide tables and queries without LIMIT. I also make sure there is a rollback plan.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions