Senior SQL & Databases Interview Questions (50 MCQs)

Question 1

How does PostgreSQL implement MVCC?

Accepted Answer

Each row version carries `xmin`/`xmax`, and visibility is decided against a snapshot of transaction IDs

Answer

It keeps old versions in a separate undo log and overwrites rows in place

Answer

Reads always take shared locks on rows

Answer

Each transaction gets a full copy of the table

Question 2

What's the key operational benefit of MVCC compared to classic read/write locking (2PL)?

Accepted Answer

Readers don't block writers and writers don't block readers

Answer

Concurrent writes to the same row never wait

Answer

It never uses extra storage

Answer

It eliminates all anomalies at every isolation level

Question 3

Rule: at least one doctor must be on call. Two doctors concurrently, under Repeatable Read (snapshot isolation), check the other is on call and each removes themselves. What happens?

Accepted Answer

Both commit and no doctor remains on call (write skew)

Answer

The second aborts due to a write conflict on the same row

Answer

A deadlock occurs

Answer

Snapshot isolation guarantees the rule holds

Question 4

How does PostgreSQL's Serializable (SSI) work, and what must the application do?

Accepted Answer

Tracks dangerous read/write dependencies and aborts one with `40001`; the app must retry the transaction

Answer

Actually runs all transactions one at a time on a single thread

Answer

Takes exclusive locks on every row read

Answer

Only warns and never aborts transactions

Question 5

Under PostgreSQL Repeatable Read, what happens if a transaction updates a row that another transaction updated and committed after the snapshot began?

Accepted Answer

An error `could not serialize access due to concurrent update`, requiring a retry

Answer

The update applies to the new version, like Read Committed

Answer

The other update is silently overwritten (lost update)

Answer

The transaction waits forever

Question 6

Under Read Committed, `UPDATE t SET n = n + 1 WHERE n = 5` waits on a concurrent transaction that changes that row's `n` to `6` and commits. What happens?

Accepted Answer

The `WHERE` is re-evaluated on the new version, and since it's no longer `5`, the row isn't updated

Answer

The row is set to `6` based on the original snapshot

Answer

The row becomes `7` without rechecking the condition

Answer

A serialization error occurs

Question 7

What is the core principle of Write-Ahead Logging (WAL)?

Accepted Answer

Changes must be durably logged before the data pages are written

Answer

Data pages are written before the log

Answer

The log is only for replication, unrelated to durability

Answer

Each commit immediately flushes all modified data pages

Question 8

What trade-off does `synchronous_commit = off` make in PostgreSQL?

Accepted Answer

Faster commits, risking loss of the last few transactions on crash but no corruption

Answer

Risk of full database corruption on crash

Answer

Completely disabling WAL

Answer

Inability to roll back

Question 9

What negative effect do very frequent checkpoints have in PostgreSQL?

Accepted Answer

Heavy I/O and more WAL volume due to full-page writes after each checkpoint

Answer

Crash recovery time becomes longer

Answer

Transactions lose durability

Answer

Autovacuum is disabled

Question 10

What's the difference between `VACUUM` and `VACUUM FULL`?

Accepted Answer

`VACUUM` frees space for reuse without heavy locks; `VACUUM FULL` rewrites the table, returning space to the OS but taking `ACCESS EXCLUSIVE`

Answer

`VACUUM FULL` only vacuums indexes

Answer

`VACUUM` locks the table and `VACUUM FULL` doesn't

Answer

No difference; `FULL` is just verbose

Question 11

What effect does a transaction left open for hours (`idle in transaction`) have on a write-heavy database?

Accepted Answer

It prevents vacuum from removing dead tuples, causing bloat

Answer

No effect since it's not doing anything

Answer

It blocks all writes

Answer

It disables WAL

Question 12

What is transaction ID wraparound in PostgreSQL and why is it dangerous?

Accepted Answer

XIDs are 32-bit; without vacuum freezing rows, the database stops accepting writes to avoid data loss

Answer

When connections exceed `max_connections`

Answer

When a `serial` column's sequence overflows

Answer

When WAL grows larger than the disk

Question 13

When is a HOT (Heap-Only Tuple) update possible in PostgreSQL?

Accepted Answer

When no indexed column changes and the page has free space for the new version

Answer

Only for tables without a primary key

Answer

When the update is inside an explicit transaction

Answer

Always, for every update

Question 14

A high-write table has 15 indexes. What's the most likely effect?

Accepted Answer

Severe write amplification, more WAL, cache pressure, and loss of HOT updates

Answer

Reads slow down because the planner gets confused

Answer

No downside other than disk space

Answer

The database automatically drops extra indexes

Question 15

What limitation does a PostgreSQL hash index have compared to B-tree?

Accepted Answer

Supports only equality (`=`); no ranges, no `ORDER BY`, no uniqueness

Answer

It isn't crash-safe in modern PostgreSQL

Answer

It only works on numeric columns

Answer

It's always larger than a B-tree

Question 16

Which kind of query is a GIN index suited for?

Accepted Answer

Containment on `jsonb`, arrays, and full-text search (e.g. `@>` and `@@`)

Answer

Range queries on timestamps

Answer

`ORDER BY` on a numeric column

Answer

A table's primary key

Question 17

What data is a BRIN index ideal for?

Accepted Answer

Very large append-only tables where the column correlates with physical order, like `created_at` in logs

Answer

Columns with fully random values like UUID v4

Answer

Full-text search

Answer

Unique primary key lookups

Question 18

A plan shows an Index Only Scan but very high `Heap Fetches`. Why?

Accepted Answer

The visibility map is stale, so the heap must be checked for visibility; vacuum is needed

Answer

The index is corrupted and must be dropped

Answer

Covering indexes always read the heap

Answer

`work_mem` is too low

Question 19

When does the planner usually prefer a Nested Loop over a Hash Join?

Accepted Answer

When the outer side has few rows and the inner side has a suitable index on the join key

Answer

When both tables have millions of rows and no indexes

Answer

Only for `CROSS JOIN`

Answer

When the join condition isn't an inequality

Question 20

`EXPLAIN ANALYZE` shows `Sort Method: external merge Disk: 512MB`. What does it mean?

Accepted Answer

The sort didn't fit in `work_mem` and spilled to disk

Answer

The table has 512MB of bloat

Answer

A 512MB index was used

Answer

The query result was cached to disk

Question 21

A prepared statement suddenly becomes very slow for some parameters after a few executions. What's the likely cause in PostgreSQL?

Accepted Answer

After 5 executions a generic plan may be chosen, which is bad for skewed values

Answer

Prepared statements clear their cache after 5 executions

Answer

Parameters get converted to strings after a few runs

Answer

The connection pool re-parses the query

Question 22

To rename column `name` to `full_name` with zero downtime in a system with rolling deploys, what's the right approach?

Accepted Answer

Expand/contract: add the new column, dual-write, backfill, switch reads, then drop the old one

Answer

One `ALTER TABLE ... RENAME COLUMN` deployed together with the code

Answer

Stop all services and run the migration

Answer

Create a view with the new name and drop the base table

Question 23

A simple, fast `ALTER TABLE` on a busy table caused a full outage. What's the most likely cause?

Accepted Answer

It waited for `ACCESS EXCLUSIVE` behind a long query, and all later queries queued behind it

Answer

`ALTER TABLE` kills all connections

Answer

The WAL filled up

Answer

`ALTER TABLE` always rewrites the whole table

Question 24

Why is a single `UPDATE` a bad idea for backfilling a new column on a 500-million-row table?

Accepted Answer

A long transaction, row locks, WAL explosion and replication lag, and bloat from new versions of every row

Answer

`UPDATE` doesn't support more than a million rows

Answer

The new column won't be indexed

Answer

It's just a bit slower, nothing more

Question 25

To add `NOT NULL` to an existing column on a large table in PostgreSQL 12+ without a long scan under lock, what's the common approach?

Accepted Answer

Add `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE`, then `SET NOT NULL`, which uses the valid constraint

Answer

A direct `ALTER COLUMN ... SET NOT NULL` never scans

Answer

Create a unique index on the column

Answer

Change the column type to `text`

Question 26

What's the core trade-off of synchronous versus asynchronous replication?

Accepted Answer

Sync: no data loss on failover but higher latency and availability tied to the replica

Answer

Sync: faster but risks data loss

Answer

Async: replicas never fall behind

Answer

They don't differ in durability

Question 27

What's a precise technique to guarantee read-your-writes when reading from replicas?

Accepted Answer

Store the LSN after writing and read from a replica only if its `pg_last_wal_replay_lsn()` has reached it

Answer

Always read from a random replica

Answer

Add more replicas

Answer

Use `SELECT ... FOR UPDATE` on the replica

Question 28

What does PostgreSQL logical replication enable that physical (streaming) replication doesn't?

Accepted Answer

Selective table replication and across different major versions (e.g. for low-downtime upgrades)

Answer

Byte-for-byte copy of the whole cluster including indexes

Answer

Automatic DDL replication

Answer

Synchronous replication with zero overhead

Question 29

What is split-brain in database failover and how is it prevented?

Accepted Answer

Two nodes both believe they're primary and accept writes; prevented with consensus/quorum and fencing

Answer

A replica stores data twice; fixed by vacuum

Answer

A table splits into two partitions; fixed with `DETACH`

Answer

The pool connects twice; fixed with PgBouncer

Question 30

What's the core difference between partitioning and sharding?

Accepted Answer

Partitioning splits data within one node; sharding distributes it across multiple independent nodes

Answer

Sharding is only for indexes

Answer

Partitioning always needs multiple servers

Answer

There is no difference

Question 31

For a multi-tenant SaaS, which shard key is usually best?

Accepted Answer

`tenant_id`, so each tenant's queries and transactions stay on one shard

Answer

`created_at`, so new data lands on a new shard

Answer

A random number per row

Answer

`country`, since it has few values

Question 32

A table is partitioned by `created_at`, but `WHERE user_id = 42` is slow and scans every partition. Why?

Accepted Answer

The filter doesn't include the partition key, so no pruning is possible and every partition's index is probed

Answer

Partitioning always makes queries slower

Answer

`user_id` can't be indexed on a partitioned table

Answer

`VACUUM FULL` must be run

Question 33

Why does PostgreSQL degrade with thousands of direct connections even if most are idle?

Accepted Answer

Process-per-connection model; per-backend memory and snapshot computation cost grow with backend count

Answer

Idle connections hold table locks

Answer

Each connection has a copy of all shared buffers

Answer

No cost at all; only active connections matter

Question 34

What is a PostgreSQL advisory lock (e.g. `pg_advisory_xact_lock(42)`) suited for?

Accepted Answer

Application-level coordination, like ensuring only one instance of a cron job runs

Answer

Automatically locking all rows of a table

Answer

Preventing SQL injection

Answer

Speeding up read queries

Question 35

Deleting a row from `users` is very slow and contends with `orders` (which has a foreign key to `users`). Likely cause?

Accepted Answer

`orders.user_id` has no index, so each delete's FK check does a seq scan of `orders`

Answer

The `users` primary key is corrupted

Answer

Foreign keys always exclusively lock the whole child table

Answer

`DELETE` in PostgreSQL doesn't support FKs

Question 36

In a ticketing system with very high demand for a few seats, what's the problem with optimistic locking?

Accepted Answer

High conflict rate causes many retries, wasted work, and extra load

Answer

Optimistic locking corrupts data

Answer

Optimistic locking isn't compatible with transactions

Answer

No problem; it's always the best choice

Question 37

A client retries a payment request after a timeout. How do you prevent double charging at the database level?

Accepted Answer

Store an idempotency key with a unique constraint in the same transaction as the payment

Answer

Check for the payment with `SELECT`, then `INSERT`, under Read Committed

Answer

Increase the client timeout

Answer

Remove client retries

Question 38

A service must save an order in the database and publish an event to Kafka. Why is the transactional outbox pattern recommended?

Accepted Answer

The event is written to an outbox table in the same transaction and published separately, avoiding inconsistent dual writes

Answer

Kafka natively participates in database transactions

Answer

It's the fastest way to publish events

Answer

It eliminates the need for any retries

Question 39

How does BCNF differ from 3NF?

Accepted Answer

In BCNF every non-trivial FD's left side must be a superkey, even if the right side is part of a candidate key

Answer

BCNF allows transitive dependencies

Answer

BCNF only applies to tables without a primary key

Answer

BCNF is just 2NF under another name

Question 40

For products with many variable attributes, why is `jsonb` with GIN usually preferred over an EAV (Entity-Attribute-Value) model?

Accepted Answer

EAV needs many joins, lacks type safety, and complicates queries; `jsonb` keeps data with the row and is indexable

Answer

`jsonb` automatically enforces all relational constraints

Answer

EAV isn't supported in PostgreSQL

Answer

`jsonb` always uses less space than regular columns

Question 41

Why is UUID v7 better than UUID v4 as a primary key on write-heavy tables?

Accepted Answer

Its timestamp prefix makes B-tree inserts nearly sequential, with fewer page splits and better locality

Answer

UUID v7 is only 8 bytes

Answer

UUID v7 is more unpredictable than v4

Answer

UUID v7 removes the need for an index

Question 42

Why can a `serial`/`identity` column in PostgreSQL have gaps?

Accepted Answer

`nextval` isn't transactional; values taken by rolled-back transactions or failed inserts aren't returned

Answer

Because of a PostgreSQL bug

Answer

Because vacuum reassigns numbers

Answer

Only when rows are deleted

Question 43

A stored procedure uses `EXECUTE 'SELECT * FROM t WHERE name = ''' || p_name || ''''`. Is calling it with parameters from the app enough?

Accepted Answer

No; string concatenation inside dynamic SQL still allows injection; use `EXECUTE ... USING` or `format('%L')`

Answer

Yes; app-side parameters stay safe everywhere

Answer

Yes; stored procedures are inherently immune to injection

Answer

No, but only if the database is MySQL

Question 44

What benefit does PostgreSQL Row-Level Security (RLS) give a multi-tenant app?

Accepted Answer

The database enforces the tenant filter on every query, so a forgotten `WHERE` doesn't leak data

Answer

It always makes queries faster

Answer

It removes the need for authentication

Answer

It encrypts each tenant's data

Question 45

Why isn't a replica a substitute for backups, and what does Point-In-Time Recovery (PITR) add?

Accepted Answer

An accidental `DROP TABLE` replicates instantly; PITR with a base backup plus archived WAL restores to just before the mistake

Answer

Replicas don't compress data

Answer

Replicas only hold read-only data that can't be restored

Answer

PITR only exists for MySQL

Question 46

Per PACELC, what does a PostgreSQL cluster with synchronous replication choose in normal operation (no partition)?

Accepted Answer

Consistency at the cost of higher latency (EC)

Answer

Low latency at the cost of consistency (EL)

Answer

Both zero latency and full consistency

Answer

PACELC only applies to NoSQL databases

Question 47

To get each user's 3 latest orders with an index on `orders(user_id, created_at DESC)`, which approach is usually most efficient?

Accepted Answer

`users u CROSS JOIN LATERAL (SELECT ... WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3)`

Answer

`ROW_NUMBER()` over all of `orders` and filtering `rn <= 3` is always faster

Answer

`GROUP BY user_id` with `LIMIT 3`

Answer

`SELECT DISTINCT` on `user_id`

Question 48

In PostgreSQL 12+, what effect does `WITH x AS MATERIALIZED (SELECT * FROM big) SELECT * FROM x WHERE id = 5` have?

Accepted Answer

The CTE acts as an optimization fence and `id = 5` isn't pushed down into it

Answer

The CTE result is permanently stored on disk

Answer

The planner pushes the condition down and uses the index

Answer

Syntax error; `MATERIALIZED` is only for views

Question 49

How does MySQL InnoDB under Repeatable Read prevent phantoms for a range `SELECT ... FOR UPDATE`?

Accepted Answer

With next-key locks (record plus gap locks) on the scanned index range

Answer

By locking the entire table

Answer

By automatically aborting other transactions after commit

Answer

It doesn't prevent phantoms

Question 50

The primary's disk slowly fills with WAL files while checkpoints look normal. What's the most likely cause?

Accepted Answer

An inactive replication slot whose consumer is gone, retaining WAL

Answer

Autovacuum is too aggressive

Answer

There are too many indexes

Answer

`work_mem` is too large