Database Administrator interview questions and answers

An index is an extra data structure that lets the database find rows without scanning the whole table, a bit like the index at the back of a book. The most common type is a B-tree, which keeps values sorted, so it helps equality lookups, ranges, joins and ORDER BY on those columns. The cost is that indexes take disk space and memory, and every INSERT, UPDATE or DELETE must update each index on the table, which slows writes. Too many indexes also make the planner's job harder. So I add indexes based on real slow queries, check that they are used with EXPLAIN, and regularly look for unused or duplicate indexes, for example with pg_stat_user_indexes in PostgreSQL, and drop them.

Think of moving 1,000 rupees from account A to account B. Atomicity means both the debit and the credit happen, or neither does; if anything fails, the transaction rolls back. Consistency means the database moves from one valid state to another, so constraints like "balance cannot be negative" or foreign keys are never broken. Isolation means another transaction running at the same time does not see a half-finished transfer; how strict this is depends on the isolation level. Durability means once COMMIT returns, the transfer is saved even if the server loses power a second later. Databases achieve durability by writing changes to a write-ahead log and flushing it to disk before confirming the commit.

I start by asking the business two questions: how much data can we afford to lose (RPO) and how long can we be down (RTO). For a typical system I would take a full physical backup daily or weekly, plus continuous WAL or binlog archiving so I can do point-in-time recovery. Tools like pgBackRest or Percona XtraBackup handle full and incremental backups well. Backups are encrypted and copied to a different location, such as object storage in another region, so one failure cannot destroy both the database and its backups. I set a retention policy, for example 30 days. Most important, I schedule automatic restore tests, because a backup that has never been restored is only a hope, not a backup.

Normalization means organising tables so each fact is stored in one place. For example, a customer's city lives in the customers table, not copied into every order. Following the normal forms, usually up to third normal form, prevents anomalies: you cannot update a city in one row and forget the others. For most transactional (OLTP) systems I start normalized. I denormalize only when there is a measured performance problem, for example a dashboard that joins six tables on every page load, or for analytics and reporting tables. Then I add controlled duplication, such as a summary table or materialized view, and make sure there is a clear way to keep it in sync, like triggers, scheduled refreshes or application logic.

First I turn on slow query logging: in PostgreSQL log_min_duration_statement, in MySQL the slow query log with long_query_time. Then I use aggregated statistics, which are more useful than single log lines. In PostgreSQL that is the pg_stat_statements extension; in MySQL it is performance_schema or the sys schema. I sort by total execution time, because a 5 ms query running a million times can cost more than one 10 second report. For the top queries I run EXPLAIN ANALYZE to see if there is a sequential scan, a bad join or a wrong row estimate. I also check monitoring for CPU, disk IO and lock waits, because sometimes queries are slow only because they are blocked by something else.

I start with the things that can turn into an outage. First I confirm last night's backups completed and that WAL or binlog archiving has no failures, because without those we cannot recover. Then I check replication: every replica connected and lag within limits. Next I look at the dashboards for disk space and growth, CPU, memory, I/O and connection counts compared with max_connections. I scan the error log for new errors, such as authentication failures or out-of-memory messages, and the slow query report or pg_stat_statements for queries that got worse. Finally I look at pg_stat_activity or SHOW PROCESSLIST for long-running queries, sessions idle in transaction and lock waits. Anything repeated becomes an automated alert so the checks get shorter over time.

Replication means keeping copies of a database on other servers by sending them every change from the primary, usually by streaming the write-ahead log or binlog. We use it for high availability, so a replica can take over if the primary fails, for read scaling and for copies in another region. With synchronous replication the primary waits until at least one replica confirms it has the change before telling the client the commit succeeded. That gives zero data loss on failover but adds network latency to every write. With asynchronous replication the commit returns immediately, so it is faster, but replicas can lag, and if the primary dies the last few transactions may be lost. Either way replication is not a backup, because a bad DELETE replicates in seconds.

A logical backup, such as pg_dump or mysqldump, exports tables as SQL or a portable archive. It is great for small databases, for copying one table or schema, and for moving data between major versions or platforms. Its weakness is restore time, because every row is reloaded and every index rebuilt, and it only captures one moment. A physical backup, made with pgBackRest, pg_basebackup or Percona XtraBackup, copies the data files themselves. It restores much faster for large databases and, combined with archived WAL or binlogs, lets me recover to any point in time, for example one minute before a bad DELETE. For production I rely on physical backups with archiving as the main recovery path, keep logical dumps of important schemas as an extra, and test restores of both regularly.

First I make sure the database is not reachable from the internet: it lives in a private subnet with security group or firewall rules that only allow the app servers and admin hosts. Authentication uses strong methods like scram-sha-256 or certificates, default accounts are removed, and secrets are stored in a vault, not in code. Each application gets its own role with only the privileges it needs, so the app cannot drop tables, and humans use personal accounts. I require TLS for connections and enable encryption at rest for data files and backups. Then I keep the server patched to the latest minor version, log connections and privileged actions with something like pgAudit, and review who has access regularly.

An INNER JOIN returns only rows that have a match in both tables, for example orders with their customer details. A LEFT JOIN keeps every row from the left table and fills the right side with NULLs when there is no match, so I use it for questions like "all customers and their orders, including customers who never ordered", or with WHERE right.id IS NULL to find customers with no orders. A RIGHT JOIN is the mirror image, and most people just swap the tables and use LEFT. A FULL OUTER JOIN keeps unmatched rows from both sides, which is useful when reconciling two data sources. A CROSS JOIN returns every combination, for example generating all dates for every store. For performance, the join columns, especially foreign keys, should usually be indexed.

First I check whether the query itself is slow or just waiting. I look at pg_stat_activity or SHOW PROCESSLIST for lock waits, and at CPU and disk IO in monitoring. If it is not blocked, I run EXPLAIN ANALYZE and compare with the old plan if we store plans, for example with auto_explain. A sudden change usually means the planner picked a different plan. Common reasons are stale statistics after a big data load, so I run ANALYZE; an index that was dropped or became invalid; table bloat; or data that grew past a point where the planner switches to a sequential scan or a different join type. I also check for parameter-sensitive plans, where a generic prepared statement plan is bad for some values. Then I fix the root cause and add monitoring for it.

The SQL standard defines four levels. READ UNCOMMITTED can see other transactions' uncommitted changes, called dirty reads; PostgreSQL treats it as READ COMMITTED. READ COMMITTED, the PostgreSQL default, only sees committed data, but each statement gets a new snapshot, so reading a row twice can give different results. REPEATABLE READ, the InnoDB default, keeps one snapshot for the whole transaction. In PostgreSQL this also prevents phantoms; InnoDB uses next-key locks for locking reads. Both can still allow write skew, where two transactions read the same data and make conflicting decisions. SERIALIZABLE makes the result equal to some serial order; PostgreSQL uses SSI and aborts conflicting transactions. Higher levels are safer but cause more aborts, so applications must retry on serialization errors.

In PostgreSQL I use physical streaming replication, where replicas receive WAL from the primary; in MySQL, binlog replication, ideally with GTIDs. For monitoring I track lag in both bytes and time. In PostgreSQL pg_stat_replication shows write_lag, flush_lag and replay_lag; in MySQL SHOW REPLICA STATUS shows Seconds_Behind_Source, though a heartbeat table is more reliable. I export these to Prometheus and alert on thresholds. When lag grows I look for the cause: a big batch job or index build on the primary, a slow disk or weaker instance on the replica, long-running queries on the replica conflicting with replay, or network limits. Fixes include breaking big jobs into batches, upgrading replica hardware, enabling parallel apply in MySQL, and making the app stop reading from replicas that lag too much.

First I check what locks each step takes. In PostgreSQL 11 and later, adding a nullable column or one with a constant default is a quick metadata change, but it still needs a brief ACCESS EXCLUSIVE lock, so I set lock_timeout to a few seconds and retry, so it does not queue behind a long query and block everyone. For the index I use CREATE INDEX CONCURRENTLY, which does not block writes, and I check afterwards that it is valid. If existing rows need values, I backfill in small batches with pauses to limit replication lag. In MySQL I use InnoDB online DDL with ALGORITHM=INSTANT or INPLACE where possible, or gh-ost for bigger changes. I follow an expand and contract approach so old and new app versions both work during rollout.

I start with pg_stat_user_tables to see n_dead_tup and when autovacuum last ran. The default autovacuum_vacuum_scale_factor of 0.2 means a 100 million row table waits for 20 million dead rows, which is too late, so I set a lower scale factor or a fixed threshold on that table with ALTER TABLE ... SET. If vacuum runs but cannot keep up, I raise autovacuum_vacuum_cost_limit or lower the cost delay, and maybe add more workers. I also check what is stopping cleanup: long-running or idle in transaction sessions, old replication slots, or hot_standby_feedback from replicas all hold back the oldest visible transaction, so dead rows cannot be removed. Setting idle_in_transaction_session_timeout helps. For bloat that already exists, I use pg_repack instead of VACUUM FULL, which locks the table.

I run EXPLAIN (ANALYZE, BUFFERS) with the real parameters, ideally on production-like data, because plans change with data size. The plan is a tree, so I read it from the innermost nodes up and look for where the actual time jumps. The most useful check is estimated rows versus actual rows: if the planner expected 10 rows and got 1 million, it probably chose the wrong join type, so I look at statistics, stale ANALYZE, correlated columns or a function hiding a column. Then I look for sequential scans on big tables that should use an index, sorts showing external merge on disk, hash joins in batches, and nested loops with huge loop counts. BUFFERS shows whether time goes to reading from disk. I change one thing, such as an index or rewrite, and compare the plan again.

I start from the total RAM and the expected number of connections. shared_buffers is usually about 25 percent of RAM, because PostgreSQL also benefits from the OS page cache. effective_cache_size does not allocate memory; it tells the planner how much cache is likely available, so I set it to around 50 to 75 percent of RAM. work_mem is the tricky one: it applies to each sort or hash in each query, so I keep it modest globally, for example 16 to 64 MB, and raise it per role or session for reporting jobs. maintenance_work_mem can be higher, like 1 to 2 GB, to speed up vacuum and index builds, keeping autovacuum workers in mind. Then I watch temp file usage, cache hit ratio and memory headroom, and use a pooler so connection counts stay sane.

I would collect metrics with exporters into Prometheus and build Grafana dashboards, and keep alerts focused on things that need action. Critical alerts: database down, replica disconnected or lag above the RPO, disk predicted to fill within a few hours, backups or WAL archiving failing, and for PostgreSQL transaction ID age approaching wraparound. Warning alerts: connections above about 80 percent of max_connections, long-running or idle-in-transaction sessions, lock waits, rising deadlocks and high I/O latency. For performance I track query latency and throughput from pg_stat_statements or performance_schema, cache hit ratio, temp file usage and checkpoints. Each alert links to a short runbook. I also review trends weekly for capacity planning, because slow growth in data size or load is easier to fix early.

Both are solid, but they behave differently day to day. PostgreSQL MVCC stores old row versions inside the table, so vacuum and autovacuum tuning, bloat and transaction ID wraparound are big topics. InnoDB keeps old versions in undo logs and cleans them with purge, so long transactions grow the undo history instead. InnoDB stores each table as a clustered index on the primary key, so key choice affects layout and secondary indexes include the primary key; PostgreSQL uses heap tables with separate indexes. PostgreSQL replicates by streaming WAL, while MySQL uses the binlog, usually with GTIDs and row format. Defaults differ too: PostgreSQL uses READ COMMITTED, InnoDB REPEATABLE READ. The tooling also differs: pgBackRest, Patroni and pgBouncer on one side; XtraBackup, Orchestrator or InnoDB Cluster and ProxySQL on the other.

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.

I would run three nodes across three availability zones: one primary and two standbys. For near-zero RPO I use synchronous replication with synchronous_standby_names set to ANY 1 of the two standbys, so a commit waits for one copy, but losing one standby does not stop writes. Patroni manages failover, using etcd or another consensus store with three members, so only the node holding the leader key can be primary. That quorum plus fencing, where the old primary demotes itself if it loses the lock, prevents split-brain. Applications connect through HAProxy or PgBouncer that follows the leader, so failover takes seconds. Sync replication adds latency to every commit, which we accept. HA does not protect against a bad DELETE, so pgBackRest backups with WAL archiving and PITR remain essential, and we test failover regularly.

Sharding adds a lot of complexity, so I first check simpler options: query and index tuning, archiving old data, partitioning, read replicas for read load, caching, connection pooling, and vertical scaling. If write volume or data size still exceeds one server, I shard. The most important choice is the shard key. It should match the main access pattern, like tenant_id or user_id, so most queries hit one shard, and it should spread load evenly. Time-based or sequential keys create hot spots. I prefer a lookup directory or many logical shards mapped to fewer physical servers, so rebalancing is easy. Cross-shard joins, global unique IDs and distributed transactions become hard, so the app design must accept that. I would consider Citus for PostgreSQL or Vitess for MySQL, and migrate gradually using logical replication and verification.

First I read the release notes and test the application against the new version in staging, including extension versions. There are two main methods. pg_upgrade with --link mode is fast even for large databases because it reuses data files, but the database is down during the upgrade and rollback is harder once the new server starts. For minimal downtime I prefer logical replication: build a new cluster on the new version, copy the schema, subscribe to the old primary, and let it catch up. Logical replication does not copy DDL or sequence values, so I freeze schema changes and set sequences before cutover. At cutover I stop writes, wait for zero lag, sync sequences, and switch the connection endpoint. Managed services offer a similar blue-green deployment. I rehearse on a production copy, run ANALYZE afterwards, and keep the old cluster ready for rollback.

I look at pg_stat_activity together with pg_blocking_pids() in PostgreSQL, or performance_schema.data_lock_waits and the sys.innodb_lock_waits view in MySQL, to build the blocking tree. Usually there is one root blocker. Common culprits are a session that is idle in transaction holding row locks after an app bug, a long batch update, or an ALTER TABLE waiting for an exclusive lock, which then blocks every query behind it in the queue. If the root blocker is safe to stop, I cancel it with pg_cancel_backend or end it with pg_terminate_backend, after checking with the owning team. Then I prevent repeats: keep transactions short, set idle_in_transaction_session_timeout and statement_timeout, always use lock_timeout for DDL, update rows in a consistent order, and review the deadlock entries in the logs.

First I tell the incident channel and stop the damage, for example by pausing jobs that depend on that table. Normal replicas already applied the DELETE, so they do not help, unless we have a delayed replica, which would be the fastest source. Otherwise I restore the latest base backup to a separate server and use point-in-time recovery to replay WAL or binlog up to just before the DELETE. I find the exact time from logs or, in MySQL, by reading the binlog with mysqlbinlog. I do not restore over production, because that would lose 20 minutes of other good writes. Instead I export the deleted rows from the recovered copy and insert them back, checking foreign keys and conflicts. After that, a blameless postmortem: remove direct write access, require reviewed changes, and consider a delayed replica.

I would start by agreeing RPO and RTO for a regional disaster, which are usually looser than for a single server failure. Inside the main region I would keep HA across availability zones. For the region loss I would run an asynchronous replica in a second region, or a managed option like Aurora Global Database, because synchronous replication across regions adds too much commit latency. Backups and WAL or binlog archives would also be copied to the second region, encrypted, with separate credentials so one compromised account cannot delete both. The runbook defines who declares a disaster, how we promote the remote replica, switch DNS or endpoints and start application servers there, and how we fence the old primary to avoid split-brain. I would also plan failback and run drills twice a year, measuring the real RPO and RTO.

First I would check compatibility: the target version, the extensions we use, features that need superuser, and network bandwidth and latency to the cloud, because 5 TB takes time to copy. A dump and restore would take far too long, so I would use continuous replication: logical replication or a service like AWS DMS, with an initial copy followed by streaming changes. Tables need primary keys or replica identity, and DDL must be frozen or applied to both sides. While it runs I validate row counts and checksums on key tables, test application performance on the target, and rehearse the cutover. On the day I stop writes, wait for lag to reach zero, sync sequences, switch the connection strings or DNS and run smoke tests. If possible I set up reverse replication so we can roll back.

First I would confirm the bloat with pgstattuple or a reliable estimate, and separate table bloat from index bloat. Then I would find why vacuum fell behind: very long or idle-in-transaction sessions, an abandoned replication slot, old prepared transactions, or hot_standby_feedback from a replica running long reports. Fixing that comes before any rebuild, otherwise the bloat returns. To recover space online I would use pg_repack, which builds a new copy and swaps it in with brief locks, after checking there is enough free disk and replica lag headroom; for indexes REINDEX CONCURRENTLY is enough. To prevent it, I would set per-table autovacuum settings with a much lower scale factor and higher cost limit, lower fillfactor if updates are frequent so HOT updates work, add timeouts for idle transactions, and alert on dead tuple counts.

I would start with data, not guesses: current database size and monthly growth, peak queries per second, CPU, memory, I/O latency and IOPS, connection counts and replication lag, and how close each is to its limit. Then I model what ten times traffic means for each resource and find the first wall, which is often writes on the primary or a few expensive queries. Cheap wins come first: tuning the top queries from pg_stat_statements, better indexes, application caching, read replicas for read traffic and a connection pooler. Next come bigger instances and faster storage, partitioning large tables and archiving old data. Sharding is the last step because of its complexity. I would validate the plan with load tests on production-like data, set budget estimates, and review the forecast every quarter.

I would start by classifying data and storing as little as possible: card numbers should be tokenised by the payment provider rather than stored, and personal data limited to what is needed. Access follows least privilege, with separate roles per application, named accounts for people, MFA through SSO or IAM authentication, and quarterly access reviews. Data is encrypted in transit with TLS and at rest, including backups and replicas, using a key management service with rotation, and the most sensitive columns can use application-level encryption. I enable audit logging, for example pgAudit, for logins, DDL and access to sensitive tables, shipped to tamper-resistant storage the DBAs cannot edit. Non-production environments get masked or synthetic data. Finally, retention and deletion jobs support rules like the right to be forgotten, and everything is documented for auditors.