Database Administrator Interview Questions: From Slow Queries to 3 AM Failover Calls

A predicate is sargable when the engine can use an index to satisfy it directly. WHERE YEAR(order_date) = 2026 wraps an indexed column in a function, which forces the planner to evaluate that function on every row before it can compare, defeating the index even though order_date itself is indexed. WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' does the same job and stays sargable.

Every index gets updated on every INSERT, UPDATE, and DELETE against that table, whether or not any query actually uses it. An index nobody's queries reference is pure write overhead with zero read benefit. DBAs who inherit a schema often find three or four indexes like this, added defensively at some point and never removed.

There's no clean number, and I'd distrust anyone who gives you one confidently. What actually matters is whether each index earns its write-side cost by getting used often enough on the read side. I've seen tables with fifteen indexes that were all fine because writes were rare and reads were constant, and tables with four indexes that were genuinely too many because the table got hammered with inserts all day.

Full captures everything, the baseline. Differential captures everything changed since the last full, faster to restore than replaying every log since the last full but slower than a fresh full. Transaction log backups capture every committed change since the last log backup, and they're what actually enables point-in-time recovery, restoring to 2:47 PM specifically instead of stopping at last night's full.

Yes, and yes. An unencrypted backup file is a full copy of your data sitting on tape, disk, or object storage, exactly the kind of thing that turns a stolen backup into a headline. It does add one real operational risk: if the encryption key gets lost or rotated without updating the backup process, you have a backup you technically can't restore. Key management has to be treated as part of the backup strategy, not a separate concern.

At-rest encryption (TDE, disk-level, file-system level) protects against someone getting physical or file-level access to the storage medium, a stolen drive, a misconfigured backup bucket. In-transit encryption (TLS) protects the data as it moves across a network between the application and the database. Neither one protects against an attacker who's already authenticated as a legitimate user with real credentials; that's what access control is for.

Masking replaces or obscures sensitive values (a real SSN becomes XXX-XX-1234) for users who don't need the real value, typically in non-production copies or for support staff. Unlike encryption, masked data usually can't be reversed back to the original; it's meant for people who should never see the real value at all, not for people who need it protected in transit and decrypted later.

Track growth trends over current usage alone, storage, connection counts, query volume, month over month, and project forward against actual budget and hardware lead times. Cheaper wins usually come first: killing unused indexes, archiving cold data out of the hot table, fixing the one query that's responsible for 40% of total load. Scaling hardware is the right answer eventually, but it's usually not the first or cheapest lever available.

DELETE is DML. It removes rows one at a time, can carry a WHERE clause, fires any triggers on the table, and every row removed gets logged individually, which is why deleting ten million rows with DELETE can blow up your transaction log. It does not touch the table definition and does not reset an identity or auto-increment counter.

TRUNCATE deallocates the data pages wholesale instead of logging row by row, so it's dramatically faster on large tables, and it does reset identity columns back to their seed value. The tradeoff is that it takes a stronger lock on the whole table, generally can't have a WHERE clause, and in engines like SQL Server it will refuse to run if another table has a foreign key pointing at it, even if that table is empty.

DROP removes the table itself, structure, indexes, constraints, permissions, everything. In practice I use TRUNCATE to clear a staging table before a nightly load, DELETE with a WHERE clause when I need to keep some rows or need trigger side effects to fire, and DROP only when the table shouldn't exist anymore.

A table gets exactly one primary key, and it disallows NULLs outright. In most engines the primary key also determines the physical storage order of the table by default, since it backs the clustered index unless you explicitly define the clustered index elsewhere. Foreign keys in other tables reference the primary key as their canonical target.

A unique constraint enforces the same "no duplicate values" rule but a table can have several of them, and the NULL handling differs by engine. Postgres treats NULL as unequal to itself, so a unique index there will happily allow multiple NULLs in that column. SQL Server allows only one NULL per unique index unless you build a filtered unique index to work around it.

A concrete case: a users table has an integer primary key for internal joins, plus a separate unique constraint on the email column so the application can enforce "one account per email address" without making email the join key everywhere.

First normal form means every column holds a single atomic value, no comma-separated lists jammed into one field. Second normal form means every non-key column depends on the entire primary key, which only really matters once you have composite keys, otherwise a table in 1NF is usually already in 2NF. Third normal form means no column depends on another non-key column instead of the key itself, so you don't store a customer's city and also their zip code redundantly derived from an address table elsewhere.

Normalization keeps data consistent, an update to a customer's address happens in exactly one place. The cost is that reading anything meaningful requires joining several tables back together.

That's exactly why reporting tables get denormalized on purpose. An orders reporting table might flatten customer name, product name, and category directly onto each order row instead of requiring six joins every time someone runs a dashboard query. You accept some redundancy and a harder update path in exchange for read speed on a table that's rebuilt nightly and rarely written to directly.

A clustered index determines the physical order rows are stored in on disk. The leaf level of the clustered index isn't a pointer to the data, it is the data. Because a table's rows can only physically sit in one order at a time, you can only have one clustered index per table, whether you defined it explicitly or the engine picked your primary key for you by default.

A non-clustered index is a separate structure entirely. It stores the indexed column's values in sorted order along with something that points back to the actual row, either a row identifier on a heap table or, in engines like InnoDB and SQL Server, the clustering key itself.

That last detail matters more than people expect. In InnoDB, every secondary index carries a copy of the primary key value at its leaf, so if you pick a fat primary key like a UUID or a long string, every single secondary index on that table gets bigger and slower along with it. That's a real reason teams stick with narrow integer or bigint primary keys even when a UUID feels more natural for the business logic.

Atomicity means a transaction is all or nothing. Without it, a transfer of $100 between two accounts could debit one account and crash before crediting the other, leaving the money gone. Consistency means the database moves from one valid state to another, constraints, foreign keys, check constraints all still hold after the transaction commits, so you never end up with an order row pointing at a customer_id that doesn't exist.

Isolation controls what one transaction can see of another transaction's in-flight changes. Without enough isolation you get dirty reads, where a report reads a balance that a different transaction later rolls back, meaning the number that went into a report never actually existed. Durability means once the database says a commit succeeded, it survives a crash a second later. That guarantee is what the write-ahead log or transaction log is actually for, it's written to disk before the commit is acknowledged, specifically so a power loss right after commit doesn't lose the write.

OLTP systems handle a high volume of small, fast transactions, an order gets inserted, a row gets updated, a lookup by primary key gets served in milliseconds. The schema is usually normalized and the indexes are tuned for point lookups and narrow range scans.

OLAP systems are built for the opposite pattern, a handful of queries that each scan and aggregate millions of rows, often across a star schema or columnar storage designed so the engine only reads the columns a query actually needs instead of full rows.

The problem with mixing them on one instance is contention. An analyst running a big GROUP BY against your production OLTP database will do a large sequential scan that floods the buffer pool with pages nobody else needs, evicting the hot pages your transactional workload depends on, and it can hold locks or consume connections long enough to make checkout transactions time out. The usual fix is a read replica dedicated to reporting, or an actual ETL pipeline feeding a warehouse like Snowflake, BigQuery, or ClickHouse, so analytics queries never touch the production instance at all.

Write-ahead logging means a change is written to the log on disk before the corresponding data page is flushed. So when the server crashes, the data files on disk might be missing changes that were already committed, but the log has a record of them. On restart, the recovery process replays the log forward, redoing every committed transaction that hadn't made it to the data files yet, and rolling back any transaction that was still in flight and never committed. That's the redo and undo pass every relational database runs before it accepts new connections after an unclean shutdown.

Turning logging off doesn't just save some overhead, it removes the mechanism the entire durability guarantee depends on. There's no redo log to replay after a crash, so you lose whatever was in memory but not yet flushed to disk, full stop. It also breaks anything downstream that reads that log stream, streaming replication, log shipping, and point-in-time recovery all consume the same log, so disabling it takes out your replicas and your ability to recover to a specific timestamp along with your crash safety.

EXPLAIN shows the planner's estimated plan without running anything, safe on a table you don't want to touch. EXPLAIN ANALYZE actually executes the query and reports real timings, real row counts, real buffer usage. On production, that's the catch: EXPLAIN ANALYZE on an UPDATE or DELETE actually runs the write. Wrap it in a transaction and roll back if you're testing a mutating query against live data.

pg_stat_statements on Postgres, sys.dm_exec_query_stats on SQL Server. Both aggregate execution stats across every query the engine has run since the stats were last reset, so you can sort by total time or mean time and find the real offenders instead of guessing.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The follow-up worth asking yourself before the interviewer asks it: is pg_stat_statements even installed? It's an extension, not a default, and a surprising number of production Postgres instances don't have it enabled until someone needs it during an incident.

The query planner picks a plan based on row-count estimates it keeps in statistics, not on the actual current data. After a large bulk load, a bulk delete, or just months of normal write traffic, those estimates drift from reality. The planner then makes a genuinely reasonable decision based on genuinely wrong numbers, choosing a sequential scan when an index scan would've been faster, or the reverse. Refreshing statistics gives it accurate numbers to reason from again.

B-tree handles equality and range queries and stays the default for almost everything. Hash indexes only support exact-match equality, no ranges, no ORDER BY, but they can be marginally faster for pure lookups on very large equality-only workloads. Postgres hash indexes only became crash-safe (WAL-logged) starting in version 10, which is part of why they were avoided in production for years before that and still get treated with some suspicion by DBAs who remember why.

Postgres uses MVCC, so an UPDATE doesn't overwrite a row in place, it writes a new row version and marks the old one dead. Indexes accumulate pointers to those dead versions until vacuum cleans them up. A table with constant updates and infrequent or badly tuned autovacuum can end up with an index that's mostly pointing at rows that no longer exist, which bloats its size and slows down every scan that touches it.

A covering index includes every column a query needs, so the engine never has to go back to the table itself (a "bookmark lookup" or "heap fetch") after finding the index entry. Worth it for a query that runs constantly and only touches a handful of columns; not worth it for a wide table where covering everything would mean duplicating most of the table into the index.

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_date, total_amount, status);

When fragmentation, not missing coverage, is the actual problem. High insert/delete/update churn leaves an index's physical page order scattered relative to its logical order, which hurts range scans specifically. A REBUILD (rewrites the whole index) or REORGANIZE (defragments in place, lighter weight) fixes that. Neither one helps if the real issue is that no index exists at all for the query pattern in question.

Textbooks present the four levels as a clean ladder from loose to strict. Production is messier: Postgres, Oracle, and SQL Server default to Read Committed, MySQL's InnoDB defaults to Repeatable Read, and most applications never touch the default at all. The real question a DBA answers isn't "which level is theoretically safest," it's "does this specific workload's tolerance for a stale read or a phantom row actually justify the extra locking cost of a stricter level." Most of the time, the honest answer is no.

SQL Server escalates from many row-level or page-level locks to a single table-level lock once a transaction crosses an internal threshold, roughly 5,000 locks by default, to save memory overhead. The tradeoff: a query that only needed a handful of rows can suddenly hold the entire table, blocking unrelated reads and writes that had nothing to do with its actual working set. Batching large updates in smaller pieces avoids crossing that threshold in the first place.

The 3-2-1 rule: three copies of the data, on two different media types, with one copy offsite. In practice that's a full weekly backup, daily differentials, continuous transaction log backups for point-in-time recovery, replicated to a separate region or provider, plus a monthly restore drill that actually proves the backup works rather than just existing.

RTO (recovery time objective) is how long you're allowed to be down. RPO (recovery point objective) is how much data you're allowed to lose, measured in time. A 15-minute RPO means transaction logs ship at least every 15 minutes; a 30-minute RTO means your failover process, automated or manual, has to complete that fast, which usually rules out a cold restore from scratch and points toward a warm standby that's already caught up.

You restore it. Not conceptually, actually, on a schedule, to an isolated environment, and you time how long it takes. A backup nobody has ever restored is a backup you don't actually have, you just have a file that might work. Monthly restore drills catch the corrupted backup, the missing log file, and the process that quietly stopped running three weeks ago, all things a backup-succeeded notification will never tell you.

A hot standby stays continuously synchronized and can take over in seconds, usually through streaming replication. A warm standby lags a bit further behind, minutes rather than seconds, often because it applies changes in batches rather than streaming continuously. Log shipping is the older, simpler cousin: transaction logs get copied and replayed on a schedule, often every 15 minutes, cheap to run but with a real gap between primary and standby if something fails right before the next shipment.

DBCC CHECKDB on SQL Server, or checksums plus pg_amcheck on Postgres, scheduled at least weekly and always immediately after any restore. It reads every page and validates internal consistency, catching disk-level corruption long before an application error surfaces it, sometimes months later, at the worst possible time.

A dedicated role, scoped to exactly the tables and operations that application needs, never the schema owner or a superuser account for an app connection string. Quarterly access reviews catch permissions that got added for a one-time migration and never revoked.

CREATE ROLE app_orders_service NOLOGIN;
GRANT SELECT, INSERT, UPDATE ON orders TO app_orders_service;
GRANT SELECT ON customers TO app_orders_service;
REVOKE DELETE ON orders FROM app_orders_service;

TDE encrypts the database files on disk, the data and log files themselves, transparently to any application querying through the normal connection. It protects against someone lifting a physical disk or a backup file and reading it directly. It does nothing against a compromised application account or a DBA with legitimate access misusing it, which is a distinction worth stating clearly, since it's a common misconception that TDE alone satisfies a compliance checklist.

RLS attaches a policy to a table that filters which rows a given user or role can see or modify, enforced by the database itself rather than trusted to application code remembering to add a WHERE tenant_id = clause everywhere. Worth it for multi-tenant systems where a missed filter in one query would leak another customer's data; the enforcement lives in one place instead of scattered across every query that touches the table.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
 USING (tenant_id = current_setting('app.tenant_id')::int);

Somewhat, not fully. Parameterized queries are genuinely the application's job to fix; a DBA can't patch that from the database side. But least-privilege access limits blast radius: an injected query running under an account that can only SELECT from three specific tables can't drop the schema or read a customers table it was never granted access to. That's damage limitation, not prevention, and it's worth being honest about the difference in an interview rather than overselling what the database layer can actually do here.

90 days is a common baseline for compliance frameworks, though the honest constraint is usually operational, not security. What breaks: hardcoded connection strings in old scripts nobody remembers exist, cached credentials in a connection pool that doesn't gracefully pick up the new password, and scheduled jobs that fail silently at 3 AM because nobody updated a config file three services deep. Rotation without a real secrets-management setup causes more outages than it prevents.

Once a table gets large enough that maintenance operations, vacuum, index rebuilds, backups, start taking uncomfortably long, partitioning splits it into smaller physical pieces the engine can manage independently. Range partitioning fits time-series data (partition by month, drop old partitions instead of running a slow DELETE). Hash partitioning spreads rows evenly when there's no natural range to split on. List partitioning fits a small, known set of categories, like partitioning by region.

CREATE TABLE orders (
  order_id BIGINT,
  order_date DATE NOT NULL,
  customer_id INT
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Partitioning splits a table into pieces that still live on one database instance, one set of hardware, one connection to manage. Sharding splits data across separate database instances entirely, each shard is its own independent database with its own connections, its own failover, its own backups. Partitioning solves a maintenance and query-performance problem on one machine. Sharding solves a capacity problem when one machine genuinely isn't enough anymore, at real added operational cost.

Synchronous replication waits for the standby to confirm a write before the primary reports success, guaranteeing zero data loss on failover but adding real latency, often 20 to 40ms round-trip for a same-region standby, more across regions. Asynchronous replication reports success immediately and ships changes after the fact, faster writes with a real risk of losing the last few seconds or milliseconds of transactions if the primary dies before shipping catches up. Financial ledgers usually justify the synchronous cost. A high-write analytics feed usually doesn't.

pg_stat_replication on Postgres, sys.dm_hadr_database_replica_states on SQL Server, both report how far behind each replica sits.

SELECT client_addr, state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

When lag grows, diagnose before acting: is it network saturation between primary and replica, a long-running query on the replica blocking WAL replay, or the replica's hardware genuinely falling behind the primary's write rate. Killing the blocking query fixes the first case immediately; the third case needs a bigger replica, not a quick command.

Every database has a hard cap on concurrent connections, and pool exhaustion happens when the application layer opens more connections than the pool (or the database itself) can serve, so new requests queue or fail even though the database's actual CPU and disk are barely working. The tell: check the database's own resource usage during the incident. High connection counts with low CPU and low disk I/O points to pool exhaustion or a connection leak, not genuine overload. High CPU or high I/O alongside those connection counts means the database really is the bottleneck.

There's no fixed number, and honestly I don't have a clean threshold to give you here, it depends on how fast the table's data distribution actually shifts. A slowly changing reference table can go months without needing fresh statistics. A table absorbing a nightly bulk import can go stale within a day if autoanalyze isn't tuned to trigger after that specific pattern. The practical answer: if query plans start looking wrong relative to actual row counts in EXPLAIN ANALYZE, check statistics first, before anything else.

It genuinely helps for read-heavy, rarely-changing data, session state, computed aggregates, product catalogs that update a few times a day. It just relocates the problem for write-heavy or frequently-invalidated data, since now you've got a cache-invalidation problem on top of the original database load problem, and cache invalidation bugs are notoriously harder to track down than a slow query ever was.

Run EXPLAIN ANALYZE. EXPLAIN alone only shows the planner's guess; ANALYZE actually executes the query and shows real row counts and timings measured against that guess. A big gap between estimated and actual rows almost always means stale statistics. If the plan shows a sequential scan where you'd expect an index scan, check whether the table crossed a size threshold that made the planner reconsider, or whether the predicate got wrapped in a function that silently defeats the index.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 48213
 AND order_date > '2026-01-01';

Only after ruling out stale statistics and a broken predicate would I look at adding an index. Most slow-query incidents I've seen traced back to one of the first two, not a missing index at all.

SQL Server compiles a query plan optimized for whatever parameter value it first sees, then caches and reuses that plan for every later execution, even with wildly different parameters. A stored procedure that runs fast for a customer with 12 orders and grinds to a halt for one with 400,000 is the classic symptom, same query, same plan, very different data shape.

Fixes range from OPTION (RECOMPILE) on the specific statement, to query hints that force a different plan shape, to just splitting the procedure into two paths for the skewed case. Recompiling every call has its own cost, so it's a tradeoff, not a free fix.

First, don't just kill it blind, check what it's actually doing and how close it is to finishing; killing a 19-minute job at minute 20 sometimes costs more than waiting three more minutes. If it needs to die, kill the session and confirm the rollback completes (a large rollback can itself take a while and hold locks during that time). Longer term, the fix is breaking the batch into smaller committed chunks so no single transaction holds a lock for more than a few seconds.

Restore the most recent full backup without bringing it online. Apply the most recent differential on top of it, if one exists. Then replay transaction logs in order, stopping precisely at the target timestamp instead of the end of the log chain. Bring the database online only after that replay completes, and run an integrity check before anyone touches it.

RESTORE DATABASE Orders FROM DISK = 'full.bak' WITH NORECOVERY;
RESTORE DATABASE Orders FROM DISK = 'diff.bak' WITH NORECOVERY;
RESTORE LOG Orders FROM DISK = 'log.trn'
 WITH STOPAT = '2026-03-14 14:47:00', RECOVERY;
DBCC CHECKDB (Orders);

Skipping WITH NORECOVERY on the intermediate steps is the mistake that costs people the most time here, it brings the database online prematurely and blocks the next restore step entirely.

A quorum of nodes, or an external witness, monitors heartbeat signals from the primary and votes to promote a standby once the primary misses enough consecutive checks. The failure mode worth naming out loud: a network partition, not an actual primary failure, can make the standby think the primary is dead while the primary is still serving writes fine on its own side of the split. That's split-brain, two nodes both believing they're primary, and it's the reason a proper quorum (an odd number of voters, or a dedicated witness) matters more than people expect going in.

Honestly, it depends on what's actually failing over and how, and I'd push back on anyone who answers this in one sentence. 99.95% allows about 4.4 hours of downtime a year. A single synchronous standby with automatic failover can hit that if failover genuinely completes in under a minute and failures are rare. But a single standby in the same region doesn't protect against a regional outage, and if that's in scope for the SLA, one standby isn't enough regardless of how fast it fails over.

This only works if audit logging was already turned on before the question mattered, which is exactly the point interviewers are testing. Native audit features (SQL Server Audit, pgAudit on Postgres) log DML against flagged tables with the executing principal and timestamp. Without that in place ahead of time, you're stuck reconstructing from transaction logs, which weren't built for readability and often get purged or archived long before six months pass.

Column-level encryption or masking on the SSN field itself, combined with an access log showing which roles have decrypt privileges and how few people actually hold them. The audit isn't satisfied by "we have a policy," it wants to see the technical control (masking, column encryption, RLS) plus the access log proving the control is actually enforced, rather than merely documented somewhere nobody reads.

Read replicas scale read throughput, not write throughput, every replica still has to apply every write the primary makes, so a write-heavy workload doesn't get any relief from adding more replicas. What breaks first is usually replication lag under sustained write pressure: replicas fall further behind, and any application logic that reads its own writes immediately after committing them starts seeing stale data on a replica that hasn't caught up yet. That's the classic read-after-write consistency bug that shows up as "it worked when I tested it slowly."

Postgres never overwrites a row in place on UPDATE or DELETE, it writes a new version and marks the old one dead, which is how MVCC gives every transaction a consistent snapshot without blocking readers. Autovacuum reclaims those dead rows so the table doesn't grow forever and indexes don't bloat. Default settings assume a fairly average table; a table doing millions of updates a day can accumulate dead tuples faster than default autovacuum settings clean them up, which is why DBAs tune autovacuum thresholds per table instead of leaving every table on the same global defaults.

Pull the deadlock graph the engine already captured, most databases log it automatically the moment they detect one and pick a victim. On SQL Server that's the system_health extended event or trace flag 1222; on MySQL's InnoDB, SHOW ENGINE INNODB STATUS surfaces the last detected deadlock directly.

SHOW ENGINE INNODB STATUS;
-- look for the LATEST DETECTED DEADLOCK section

From there, identify which two statements grabbed locks in opposite order, that's almost always the actual cause. The fix lives upstream in application code: enforce a consistent lock-acquisition order across every code path that touches those tables together.