Interview prep · 🧮 Database Administrator (DBA)
Database Administrator interview questionsand answers (30, by level).
The questions Indian employers actually ask for this role — fresher, mid-level and senior — with model answers written by engineers who sit on the other side of the table. Use with the role path and the 30-day lab plan.
Fresher / L1
Fundamentals they test on day one
1What are ACID properties?
Atomicity, Consistency, Isolation, Durability — the guarantees of a transaction.
2Primary key vs unique key vs foreign key?
PK uniquely identifies rows (not null, one per table); unique allows one null (varies by DB); FK enforces referential integrity to another table.
3What is an index and when does it hurt?
A structure (B-tree usually) for fast lookups; hurts write performance and space; useless for low-selectivity columns or functions on columns.
4Inner vs left join?
Inner returns matching rows in both; left returns all rows from the left table with nulls where no match.
5Normalisation: 1NF, 2NF, 3NF in one line each.
1NF atomic values; 2NF no partial dependency on part of a composite key; 3NF no transitive dependency on non-key columns.
6What is a transaction isolation level?
How concurrent transactions see each other’s changes: read uncommitted, read committed, repeatable read, serializable — trade consistency vs concurrency.
7How do you take a backup in MySQL/PostgreSQL?
mysqldump/mysqlpump or XtraBackup; pg_dump (logical) or pg_basebackup + WAL archiving (physical, point-in-time).
8What is a deadlock?
Two transactions wait on each other’s locks; the DB kills one; fix by consistent lock ordering and shorter transactions.
9Explain EXPLAIN.
Shows the query plan: scans vs index use, join order, estimated rows; use EXPLAIN ANALYZE for actuals.
10SQL vs NoSQL — when NoSQL?
Flexible schema, horizontal scale, document/key-value access patterns (MongoDB, Redis, Cassandra); SQL for relational integrity and complex queries.
2–5 years / L2
What a working engineer must know
1Set up PostgreSQL streaming replication with failover.
Primary with WAL streaming to standbys, replication slots, synchronous option for RPO 0, Patroni + etcd for automatic failover, HAProxy/PgBouncer for connection routing.
2Slow query at 9 AM every day — approach?
Capture with pg_stat_statements/slow log, check plan, missing index, stale statistics, lock waits, maintenance jobs, parameter sniffing; fix and verify.
3MySQL InnoDB tuning basics?
innodb_buffer_pool_size (~70% RAM), log file size, flush settings, connections, query cache off (removed in 8), slow log, proper indexes.
4How do you do point-in-time recovery?
Restore the last full/base backup, replay WAL/binlogs up to the target timestamp just before the bad transaction.
5Vacuum and bloat in PostgreSQL?
MVCC leaves dead tuples; autovacuum reclaims; bloat grows if it falls behind — tune thresholds, use pg_repack, watch transaction ID wraparound.
6Partitioning vs sharding?
Partitioning splits a table within one server (range/list/hash) for manageability; sharding splits across servers for scale with application-level routing.
7How do you secure a database?
Least-privilege roles, TLS, no public exposure, row-level security where needed, audit logs, encrypted backups, secrets rotation, patching.
8Oracle → PostgreSQL migration: hard parts?
PL/SQL to PL/pgSQL, data types, sequences, packages, hints, proprietary features; use ora2pg, test thoroughly, plan dual-run.
9Connection pooling — why and how?
DB connections are expensive; PgBouncer/ProxySQL multiplex app connections; choose transaction vs session pooling carefully.
10Monitoring a database — what metrics?
Connections, QPS, latency p95, cache hit ratio, replication lag, locks, disk/IOPS, long transactions, errors; alert on lag and connection saturation.
Senior / L3
Design, judgement and leadership
1Design HA + DR for a 2 TB PostgreSQL serving a fintech app.
Patroni cluster (3 nodes) in primary DC, sync replica for RPO 0, async replica in DR city, WAL archive to object storage, PgBouncer, backups with pgBackRest, quarterly failover drills, encryption, audit.
2Zero-downtime major version upgrade?
Logical replication to the new version, cutover with brief write freeze; or pg_upgrade --link with short downtime; test on a clone first.
3How do you handle a 5 billion-row table that must stay online?
Partitioning by time, online index creation (CONCURRENTLY), batch backfills, archiving old partitions, careful autovacuum settings.
4Database cost optimisation on cloud?
Right-size instances, reserved pricing, storage tiering, read replicas only when needed, consider self-managed on Proxmox for steady workloads (we often see 50%+ savings).
5How do you decide between SQL Server, PostgreSQL and MySQL for a new product?
Team skills, licensing (SQL Server cost), features needed (JSON, extensions like PostGIS/TimescaleDB), ecosystem; default to PostgreSQL unless a hard reason.
6Describe a data corruption incident and your response.
Detect (checksums, errors), isolate, restore from verified backup + PITR, validate, root cause (storage/firmware/bug), add checksums and verification.
7Multi-tenant SaaS: schema-per-tenant vs shared tables?
Schema per tenant isolates and simplifies restores but scales poorly past thousands; shared tables with tenant_id + RLS scale better with careful indexing.
8Caching strategy with Redis in front of a DB?
Cache-aside with TTLs, invalidate on write, protect against stampede, monitor hit ratio; never make Redis the source of truth without persistence design.
9How do you govern access and changes in a regulated environment?
Migration scripts in Git with review, change windows, DBA-only DDL, audit logging, quarterly access reviews, separate prod credentials, masking in non-prod.
10Vector databases and AI workloads — how does a DBA adapt?
pgvector in PostgreSQL covers most needs; understand ANN indexes (HNSW), embedding size vs memory, hybrid search; same ops discipline applies.
How to answer
Four habits that pass interviews
- Say the structure first“I’d check three things: …” — interviewers reward a method more than a lucky answer.
- Numbers and namesMention real tools, versions and figures from your lab; vague answers lose.
- Admit gaps cleanly“I haven’t run X in production; here is how I’d approach it” beats bluffing.
- Ask about the environmentScale, on-call, tooling — it shows you think like an operator.
Ready to be hired, or ready to hire?
Engineers: join our vetted network for projects and placements. Companies: we shortlist database administrators within 48 hours.