Database engineer behavioural interview questions

As asked

Walk me through the most complex slow query you have investigated in production. What were the symptoms, what tools did you use, what did you find, and how did you fix it?

Sample answer outline

A strong answer names the specific database and tools (EXPLAIN ANALYZE, pg_stat_statements, auto_explain, slow query log), describes the misleading symptoms (e.g., query was fast in dev but slow in prod due to data distribution), identifies the root cause (e.g., bad join order, stale statistics, parameter sniffing, missing index), and explains the fix. Bonus: the candidate tested the fix under realistic data volume and monitored for regression.

Expect these follow-ups

• How did you ensure the fix did not regress other queries that used the same index or plan?

As asked

Tell me about a time when you discovered that data was either lost or at serious risk of being lost in a production database. What happened, what did you do, and what did you change afterward?

Sample answer outline

A strong answer follows STAR and covers: the specific failure mode (e.g., accidental DELETE, missed WAL archiving, replication slot overflow), the immediate triage actions taken under pressure, what tools and SQL queries were used for recovery, how far back they had to restore, and what permanent process or tooling changes were implemented to prevent recurrence. Bonus points for mentioning communication to stakeholders during the incident.

Expect these follow-ups

• What monitoring would have caught this earlier?

As asked

Tell me about a time when a developer or product manager came to you with a schema change request that you believed would cause significant performance problems down the line. How did you handle it?

Sample answer outline

The answer should show technical confidence paired with collaboration: the candidate identified specific problems (e.g., EAV anti-pattern, over-normalized schema for analytical queries, missing index on a FK), quantified the risk where possible (estimated query cost, lock duration), proposed an alternative design, and worked with the stakeholder to reach a solution. The candidate should avoid being adversarial while still advocating for the right technical outcome.

Expect these follow-ups

• How did you make the case to a non-technical stakeholder?

As asked

Tell me about a time you were paged for a database outage at an inconvenient hour. What was the situation, how did you triage it, and what did you do to resolve it?

Sample answer outline

The answer should cover triage methodology (check process list, replication status, disk, memory, active locks), the specific failure and how it was identified, the recovery steps, and post-incident actions. Strong answers demonstrate calm under pressure, methodical diagnosis, and awareness of the blast radius (e.g., which downstream services were affected).

Expect these follow-ups

• What runbook or automation did you create afterward to speed up future response?

As asked

Describe a time you had to migrate a large production database (table restructuring, version upgrade, or data center move) with a hard deadline and limited maintenance window.

Sample answer outline

The answer should show planning rigor: rehearsal of the migration in staging with realistic data, timing each step, identifying rollback procedures, communicating clearly with the team, and managing risks that materialized during execution. The candidate should mention specific tools used and any creative solutions to compress the window (e.g., parallel index builds, pg_repack, delayed constraint validation).

Expect these follow-ups

• What would you have done differently in retrospect?

As asked

Tell me about a time you helped a junior engineer understand why their query was slow and taught them how to investigate and fix it themselves going forward.

Sample answer outline

The answer should demonstrate teaching approach (not just fixing the query for them but explaining the mental model), patience, and the ability to translate technical internals into concepts a junior can apply. Good answers include a follow-up where the junior independently solved a similar problem, showing the mentoring had lasting impact.

Expect these follow-ups

• What resources or internal documentation did you create to scale that knowledge to the whole team?

As asked

Describe a time you disagreed with a senior engineer or architect about a database design decision. How did you express your concerns, and how was it resolved?

Sample answer outline

The candidate should demonstrate respectful disagreement with data: they gathered benchmarks, wrote a technical proposal, requested a review meeting, and presented trade-offs rather than just objections. The resolution should show either that the candidate persuaded the team with evidence, or that they disagreed and committed gracefully when overruled. Either outcome is fine; the process is what is evaluated.

Expect these follow-ups

• Did you set up any monitoring to validate whether the decision held up over time?

As asked

Tell me about a time you reduced the cost of running a database infrastructure without sacrificing reliability or performance.

Sample answer outline

Strong answers are specific: the candidate identified waste (oversized instances, unused replicas, full table scans burning read IOPS, inefficient queries on a metered cloud DB), quantified the saving, and executed the change safely. They should mention how they validated that performance targets were still met after the change.

Expect these follow-ups

• How did you communicate the change to the business and measure success?

As asked

You are trying to run ALTER TABLE orders ADD COLUMN flags SMALLINT DEFAULT 0 on a production table, but it is waiting because a report query has been running for 45 minutes. Killing the report will anger the finance team. What do you do?

Sample answer outline

The candidate should not simply kill the query without communication. First, assess the urgency of the deploy versus the report. In PostgreSQL 11+, adding a column with a constant default is instant and does not rewrite the table, so if the migration is only that statement, investigate why it is still waiting and whether it is safe to kill. Set a lock_timeout on the migration statement to auto-abort rather than holding the lock queue indefinitely. Communicate with the finance team and schedule the migration after the report completes if the deploy is not urgent.

Expect these follow-ups

• What would you do if the migration was blocking a critical security fix that needed to go out within 30 minutes?

As asked

You get a PagerDuty alert: a PostgreSQL streaming replica's disk is at 98% and growing. It is currently serving 40% of your read traffic. What do you do in the next 10 minutes?

Sample answer outline

Immediately remove the replica from the load balancer so reads stop routing to it (prevents query failures if it goes down). Check what is consuming disk: pg_database_size, pg_relation_size, du on WAL directory and pg_wal. If WAL is the culprit, check for a lagging replication slot or a stale archive_command. Delete or truncate unnecessary temp tables or logs if safe. If disk cannot be freed quickly, provision additional disk (cloud resize) or force a failover. Keep the primary monitored in case the replica's lag was hiding a primary issue.

Expect these follow-ups

• How do you safely remove a streaming replica from production read traffic without dropping active connections?

As asked

A team member notices that pg_stat_user_tables shows n_dead_tup is 50 million for your largest orders table and autovacuum has not run on it in 3 days. What do you investigate and what do you do?

Sample answer outline

Check pg_stat_activity for a long-running transaction that is holding back the vacuum horizon (it will show a very old xmin). Also check if autovacuum is being rate-limited by autovacuum_vacuum_cost_delay or overwhelmed by other tables. Check pg_stat_bgwriter for autovacuum workers. If a long transaction is the blocker, assess whether it can be killed. Manually trigger VACUUM ANALYZE orders if safe. Adjust autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold per-table for large tables where the default 20% threshold is impractically high.

Expect these follow-ups

• What is the risk of leaving 50 million dead tuples in the table?

As asked

You added an index to fix a slow query and deployed it to production. Within an hour, the engineering team notices that overall write throughput dropped by 30% and average query latency increased. What do you do?

Sample answer outline

Immediately drop the index to restore baseline performance (DROP INDEX CONCURRENTLY to avoid locking). The candidate should know that every index adds overhead to every INSERT, UPDATE, and DELETE on that table. After rollback, investigate whether the index was on a write-heavy column, whether there was a better partial index option, or whether the underlying slow query needed a schema-level fix rather than an index. Validate any future index addition in a staging environment under realistic write load before deploying.

Expect these follow-ups

• How would you use pg_stat_user_indexes to identify indexes that are never used and safe to drop?

As asked

A developer comes to you and asks to disable all foreign key constraints on a production table to speed up a large data import. How do you respond and what alternatives do you offer?

Sample answer outline

Disabling FKs in production without a plan to re-enable and validate is risky because invalid data can be inserted. The candidate should offer alternatives: use PostgreSQL's SET session_replication_role = replica (which skips FK checks for that session) if the data is pre-validated at the source, followed by RESET and a validation query after load. Or use ALTER TABLE DISABLE TRIGGER on a non-critical import table in a maintenance window, then re-enable and validate. The candidate should refuse to disable FKs permanently and insist on a validation step.

Expect these follow-ups

• How would you verify referential integrity after re-enabling the constraints?

As asked

Tell me about a time when the product team, data science team, and your database team had conflicting requirements for how data should be structured. How did you navigate it?

Sample answer outline

The answer should show the candidate as a neutral party who gathered requirements from each stakeholder, modeled trade-offs explicitly (e.g., OLTP-normalized vs. analytical-denormalized), and proposed a solution that served all parties, possibly with separate materialized views or a CDC-fed analytics schema. Good answers include how the candidate facilitated the conversation and documented the decision.

Expect these follow-ups

• What would you do if the requirements fundamentally could not be reconciled in a single schema?

As asked

Describe a time you forecasted that a database would hit a capacity limit (disk, connections, IOPS, or table size) before it became an incident. How did you detect it and what did you do?

Sample answer outline

The candidate should describe the signals they monitored (growth rate trends, autovacuum bloat, connection count trends), the projection method (linear or exponential extrapolation), and the preventive actions taken with enough lead time (adding partitioning, archiving old data, provisioning larger storage). Strong answers include how they communicated the risk and timeline to engineering leadership.

Expect these follow-ups

• What alerting did you set up to give you early warning in the future?

As asked

Describe a time when you discovered a SQL injection risk in an application. How did you find it, assess the severity, and fix it without breaking the application?

Sample answer outline

The answer should cover discovery method (code review, pen test, SAST tool), a specific example of the unsafe dynamic query construction, the risk assessment (what data could be exfiltrated or modified), the fix (parameterized queries or ORM binding), testing to confirm the fix, and any systemic changes (linting rules, code review checklist items) to prevent recurrence.

Expect these follow-ups

• What principle of least privilege changes did you make to the database role the application used?

As asked

Describe a time you discovered that production data was logically inconsistent or corrupted. How did you find the extent of the problem, and how did you fix it without making things worse?

Sample answer outline

The candidate should describe systematic scoping queries (counting rows that violate a business rule, joining tables to find orphaned records), a careful fix that was tested on a copy of the data first and run in a transaction with a ROLLBACK escape hatch, and a root-cause investigation that traced the corruption to its source (bug, race condition, missing FK). Good answers mention communication to stakeholders about the impact.

Expect these follow-ups

• How did you ensure the data fix was idempotent in case it had to be re-run?

As asked

A developer accidentally ran DELETE FROM users WHERE status='inactive' in production and wiped 200,000 rows they should not have touched. The error was discovered 4 minutes after it happened. What do you do?

Sample answer outline

The first question is whether the transaction was committed. If not, have the developer ROLLBACK. If committed, immediately check if continuous WAL archiving is enabled and find the WAL position 4 minutes ago for PITR. Do not touch the database further until a recovery plan is in place. Stand up a temporary instance, restore to T-4 minutes, extract the deleted rows with INSERT INTO ... SELECT, and insert them back into production. Confirm affected count matches. Conduct a post-mortem to add controls (require WHERE clause checkers, separate prod role without DELETE, confirmation prompts in tooling).

Expect these follow-ups

• What safeguards would you put in place so this cannot happen again?

As asked

You get an alert that pg_wal directory has grown to 200 GB (normally under 2 GB) and the disk is filling fast. You find a replication slot with a very old confirmed_flush_lsn. The consumer has been down for 12 hours. What do you do?

Sample answer outline

The slot is preventing WAL from being cleaned up because PostgreSQL must retain all WAL from the slot's restart_lsn. First, assess: is the slot consumer (e.g., Debezium, a logical subscriber) expected to come back? If not, drop the slot immediately. If it is coming back, assess how long before disk runs out and whether the consumer can catch up in time. As an emergency measure, set max_slot_wal_keep_size to limit slot WAL retention and accept that the slot may be invalidated. Alert the team owning the consumer so they can rebuild the slot from a fresh snapshot.

Expect these follow-ups

• What monitoring would give you advance warning before a slot's WAL retention becomes a disk risk?

As asked

A stored procedure that ran in 200ms for months now takes 45 seconds after last night's ANALYZE. Nothing in the code changed. What do you investigate and how do you restore performance quickly?

Sample answer outline

Run EXPLAIN ANALYZE on the slow query and compare the plan to a captured baseline. The new statistics likely changed row estimates enough that the planner chose a different, worse plan (e.g., switched from index scan to seq scan or changed join order). Quick fix: use pg_hint_plan (PostgreSQL) or an index hint to force the old plan while investigating. Root cause: the new statistics may be more accurate, meaning the old plan was coincidentally fast on older data distribution. Check if the data skew changed (e.g., a column now has a dominant value). Use extended statistics or increase the statistics target for that column.

Expect these follow-ups

• How would you use plan_cache_mode to force custom plans instead of generic plans for prepared statements?