Database engineer interview questions

As asked

A Postgres query on a 200 million row orders table takes 40 seconds. How do you read the query plan and decide what index to add?

Sample answer outline

Use EXPLAIN ANALYZE to compare estimated rows with actual rows, identify sequential scans, expensive sorts, nested loops over large inputs, and whether time is spent reading buffers or computing. Design the index around the predicate, join, and ordering pattern, not around every column in the table. For common queries, a composite or partial index may beat separate single-column indexes, but it must match selectivity and column order. Check write overhead, bloat, and whether the index supports index-only scans through included columns. Candidates often add indexes blindly without validating the plan before and after.

Expect these follow-ups

• When would a sequential scan be the right plan?
• How do stale statistics affect the optimiser?
• What is the tradeoff between a composite index and two single-column indexes?

As asked

You need to split a customer_name column into first_name and last_name on a large production table. How do you migrate without downtime?

Sample answer outline

Use an expand and contract migration. Add nullable new columns first, deploy application code that dual-writes old and new fields, and backfill in small batches with throttling and observability. After validating parity, switch reads to the new columns, keep dual-write briefly, then remove the old column in a later migration. Discuss locks, replication lag, rollback, and how to handle ambiguous names or bad historical data. Interviewers look for sequencing discipline; candidates often propose a single blocking ALTER and backfill that would hurt production.

Expect these follow-ups

• How would this change if the table receives 10,000 writes per second?
• What validation query proves the backfill is correct enough?
• When do you need a trigger instead of application dual-write?

As asked

Users update their profile and immediately see old data because reads go to replicas. How would you fix this?

Sample answer outline

First confirm the symptom is replication lag by measuring primary commit time, replica replay lag, and the request path. Common fixes include read-your-writes routing to the primary for a short window after a write, session stickiness, or using a version token that waits until a replica has caught up. Do not route all reads permanently to the primary unless the capacity tradeoff is acceptable. Add lag-aware load balancing and alerts so unhealthy replicas stop serving freshness-sensitive reads. Strong answers define which reads require consistency and which can tolerate stale data.

Expect these follow-ups

• How would you expose read consistency requirements in an application API?
• What happens if replica lag is caused by one expensive analytical query?
• How does this differ in eventually consistent NoSQL stores?

As asked

Regional sales managers should only see their own region's deals, while executives can see everything. How would you implement and test this in BI?

Sample answer outline

Model entitlements explicitly: user or group to permitted region, with executive groups mapped to all regions. Enforce row-level security in the BI semantic layer or warehouse policies, preferably close to the data if multiple tools consume it. Test with impersonation, fixture users, and negative cases so a dashboard change cannot bypass the rule. Handle edge cases like managers covering multiple regions, historical territory changes, and shared accounts. Strong answers consider privacy and auditability, not just a filter on a dashboard.

Expect these follow-ups

• Where would you enforce RLS if both Looker and ad-hoc SQL users need access?
• How do you test that a user cannot bypass the filter through drill-downs?
• What happens when a salesperson moves region mid-quarter?

As asked

A company says it has nightly backups but has never restored them. What backup and restore strategy would you put in place?

Sample answer outline

Define RPO and RTO first because backup frequency and restore architecture follow from business tolerance. Use automated full backups plus continuous WAL or binlog archiving for point-in-time recovery, encrypted and stored in a separate failure domain. Test restores regularly into an isolated environment and measure how long the restore takes, whether credentials and extensions work, and whether applications can connect. Include corruption, accidental delete, regional failure, and operator error scenarios. The trap is treating backup creation as success; recovery is the product.

Expect these follow-ups

• How do you test restore without exposing production data broadly?
• What is your plan for a bad migration that corrupts data but continues for six hours?
• How do backups interact with GDPR deletion requirements?