Skip to content
System Design

Databases interview questions and answers

50 questions, 50 with code. About 4 h 22 min to read them all.

By TestLab · Updated

Plan for an interview
50 questions

Track what you know and what to revise.

Sign in to track progress

You can open a few full answers without signing in. Sign in to read all

1

What is a database management system (DBMS), and what are some examples?

A database management system (DBMS) is software that stores, organises, retrieves and protects data, so applications don’t have to manage files directly. It sits between applications and the storage, and provides: - a data model and definition language: tables, documents, key-value pairs or graphs, defined with DDL such as CREATE TABLE; - a query language and optimiser: SQL, or APIs such as MongoDB’s query language, with a planner that picks efficient execution strategies and indexes; - integrity: constraints (primary and foreign keys, UNIQUE, CHECK, NOT NULL) and data types; - transactions and concurrency control: ACID guarantees, locking or MVCC so many users can work at once safely; - durability and recovery: write-ahead logs, crash recovery, backups and point-in-time restore; - security: authentication, roles and privileges, encryption, auditing; - scalability and availability: replication, partitioning and sharding, caching, connection management.

Types and examples: - Relational (RDBMS): PostgreSQL, MySQL, MariaDB, Oracle Database, Microsoft SQL Server, SQLite, IBM Db2. - Document: MongoDB, Couchbase, Firestore. - Key-value: Redis, Amazon DynamoDB, etcd. - Wide-column: Apache Cassandra, ScyllaDB, HBase, Bigtable. - Graph: Neo4j, Amazon Neptune. - Time-series: InfluxDB, TimescaleDB. - Search: Elasticsearch, OpenSearch. - Analytical / columnar: Snowflake, BigQuery, ClickHouse, DuckDB. - Distributed SQL / NewSQL: CockroachDB, YugabyteDB, Google Spanner, TiDB.

Read full answer

Applies todatabase fundamentals (examples use SQLite via Python 3.12)

Code Example: A DBMS Enforcing Structure and Rules

# A DBMS in action (SQLite, built into Python): schema, constraints, queries, transactions
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE,
    price REAL NOT NULL CHECK (price >= 0)
);
INSERT INTO products (name, price) VALUES ('Keyboard', 49.0), ('Mouse', 19.5), ('Monitor', 189.0);
""")

print(db.execute("SELECT name, price FROM products WHERE price < 100 ORDER BY price").fetchall())
# [('Mouse', 19.5), ('Keyboard', 49.0)]

try:
    db.execute("INSERT INTO products (name, price) VALUES ('Cable', -5)")   # the DBMS enforces rules
except sqlite3.IntegrityError as e:
    print("rejected:", e)                     # rejected: CHECK constraint failed: price >= 0

print("engine:", sqlite3.sqlite_version)      # SQLite version (varies by Python build)

The application states what it wants (SELECT ... WHERE price < 100). The DBMS decides how to fetch it, and refuses data that breaks the rules.

DBMS Versus Plain Files

Concern Files (CSV, JSON on disk) DBMS
Querying Custom code for every question Declarative queries, optimiser, indexes
Concurrent writers Corruption or lost updates Locking or MVCC, transactions
Integrity Application must check everything Constraints and types enforced centrally
Crash safety Partial writes possible WAL, atomic commits, recovery
Security File permissions only Users, roles, row- and column-level rules
Scale One machine, manual Replication, partitioning, caching

Choosing a DBMS

Need Typical choice
General transactional apps, strong consistency PostgreSQL, MySQL, SQL Server, Oracle
Embedded or local storage (mobile, desktop, tests) SQLite
Flexible JSON documents MongoDB, PostgreSQL jsonb
Sub-millisecond caching, counters Redis
Massive write throughput across regions Cassandra, DynamoDB
Relationships and traversals Neo4j
Analytics over billions of rows BigQuery, Snowflake, ClickHouse

Pitfalls

  • Confusing the database with the DBMS: the database is the data, the DBMS is the software managing it.

  • Choosing by popularity instead of data model and consistency needs.

  • Treating the DBMS as dumb storage, ignoring constraints and transactions it could enforce.

Interview Tips

  • Define a DBMS by its functions: storage, querying, integrity, transactions, recovery, security.

  • Name examples across categories (relational, document, key-value, wide-column, graph, analytical).

  • Mention how requirements drive the choice.

2

Explain the ACID properties in the context of databases.

ACID describes the guarantees a database gives for a transaction, a group of operations treated as one unit of work: - Atomicity (all or nothing): either every operation in the transaction takes effect, or none does. If anything fails (an error, a constraint violation, a crash), the database rolls back all its changes. Implemented with undo logs or write-ahead logs (WAL). - Consistency: a transaction moves the database from one valid state to another. Every constraint, foreign key, check and trigger holds afterwards. The database enforces declared rules, and the application must define its invariants correctly (for example, money is neither created nor destroyed in a transfer). - Isolation: concurrent transactions don’t interfere in ways that produce anomalies. Each behaves as if it ran alone, to a degree set by the isolation level: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Implemented with locking and/or MVCC (multi-version concurrency control). Weaker levels allow anomalies such as dirty reads, non-repeatable reads, phantoms and write skew, in exchange for concurrency. - Durability: once a transaction commits, its changes survive crashes and power loss. The WAL is flushed to stable storage (fsync) before the commit is acknowledged, and replication can add protection against losing a machine.

ACID matters for correctness-critical data: payments, inventory, bookings, ledgers. Many NoSQL and distributed systems relax some guarantees (BASE: eventual consistency) for availability and scale, though MongoDB (multi-document transactions), DynamoDB transactions and distributed SQL databases (Spanner, CockroachDB) now offer ACID transactions too.

Read full answer

Applies toPostgreSQL, MySQL InnoDB, SQL Server, Oracle, SQLite (examples use SQLite via Python 3.12)

Code Example: Atomicity Protecting a Transfer

# Atomicity and consistency: a failed transfer rolls back completely
import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)       # manage transactions explicitly
db.executescript("""
CREATE TABLE accounts (id TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES ('alice', 100), ('bob', 50);
""")

def transfer(src, dst, amount):
    try:
        db.execute("BEGIN")
        db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
        db.execute("COMMIT")                                   # durable once COMMIT returns
        return "committed"
    except sqlite3.IntegrityError as e:
        db.execute("ROLLBACK")                                 # undo the credit to dst too
        return f"rolled back ({e})"

print(transfer("alice", "bob", 30))     # committed
print(transfer("alice", "bob", 500))    # rolled back (CHECK constraint failed: balance >= 0)
print(db.execute("SELECT * FROM accounts ORDER BY id").fetchall())
# [('alice', 70), ('bob', 80)]  <- bob was NOT credited 500; total is still 150

The second transfer credited Bob first, then failed the balance check on Alice. Atomicity undid the credit, so the total stays 150, which is the consistency invariant.

Isolation Levels and Anomalies

Level Dirty read Non-repeatable read Phantom Typical default
Read Uncommitted Possible Possible Possible Rarely used
Read Committed Prevented Possible Possible PostgreSQL, Oracle, SQL Server
Repeatable Read Prevented Prevented Possible (SQL standard); prevented in PostgreSQL’s snapshot implementation MySQL InnoDB
Serializable Prevented Prevented Prevented Strongest; may abort transactions
-- PostgreSQL: run a critical section with full isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 'alice';
UPDATE accounts SET balance = balance - 30 WHERE id = 'alice';
COMMIT;   -- may fail with SQLSTATE 40001 (serialization_failure): retry the transaction

How Each Property Is Implemented

Property Mechanism
Atomicity Undo/redo logs, WAL, rollback
Consistency Constraints, triggers, application invariants
Isolation Locks, MVCC snapshots, serializable snapshot isolation
Durability WAL with fsync, checkpoints, replication

Pitfalls

  • Assuming the default isolation level prevents all anomalies: Read Committed allows lost updates if you read-then-write without locking (SELECT ... FOR UPDATE).

  • Autocommit mode splitting logically single operations into separate transactions.

  • Disabling fsync or using async commit for speed without understanding the durability loss.

  • Not retrying serialization failures and deadlocks.

Interview Tips

  • Define each letter with an example (bank transfer).

  • Explain isolation levels and anomalies, and WAL for atomicity and durability.

  • Contrast with BASE and eventual consistency in distributed NoSQL systems.

3

What are the differences between SQL and NoSQL databases?

SQL (relational) databases store data in tables with a fixed schema (rows and typed columns), link tables through keys and joins, and are queried with SQL. They emphasise ACID transactions, constraints and strong consistency, and traditionally scale vertically (bigger servers), with read replicas, partitioning or distributed SQL for more. Examples: PostgreSQL, MySQL, SQL Server, Oracle.

NoSQL databases use other data models: - document (MongoDB, Couchbase); - key-value (Redis, DynamoDB); - wide-column (Cassandra, ScyllaDB); - graph (Neo4j).

Read full answer

They usually have a flexible or schema-on-read structure. They are designed to scale horizontally across many nodes (sharding built in), and often favour availability and partition tolerance with tunable or eventual consistency (BASE). Queries are shaped around access patterns: you denormalise and model data for how it will be read, with limited or no joins.

Neither is better in general. Choose SQL for complex queries, relationships, reporting and strict integrity. Choose NoSQL for very high scale with simple access patterns, flexible or rapidly evolving structures, low-latency key lookups, or specialised models such as graphs. The lines are blurring: PostgreSQL has jsonb; MongoDB and DynamoDB support transactions; distributed SQL (CockroachDB, Spanner) scales horizontally with ACID.

Applies to: PostgreSQL, MySQL, MongoDB, DynamoDB, Cassandra, Redis, Neo4j

Comparison

Aspect SQL (relational) NoSQL
Data model Tables, rows, columns Documents, key-value, wide-column, graph
Schema Defined up front (schema-on-write) Flexible; enforced by app or optional validation
Relationships Foreign keys and joins Embedding, duplication, application-side joins, graph edges
Query language SQL (standardised) Product-specific APIs (MongoDB MQL, CQL, Cypher)
Transactions Multi-row ACID, standard Varies: single-item atomicity to full multi-document ACID
Consistency Strong by default Often tunable or eventual
Scaling Vertical + replicas; sharding is harder Horizontal sharding built in
Best for Complex queries, integrity, reporting Huge scale, flexible data, simple access patterns

Same Data, Two Models

-- Relational: normalised, joined at query time
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.id = 42;
// MongoDB: one document per order, customer details embedded for fast reads
db.orders.insertOne({
  _id: 1001,
  total: 114.0,
  customer: { id: 42, name: "Asha" },          // duplicated, so no join is needed
  items: [ { sku: "KB-1", qty: 1, price: 99 }, { sku: "CB-2", qty: 1, price: 15 } ]
});
db.orders.find({ "customer.id": 42 });
DynamoDB (single-table design, keys chosen for access patterns):
PK = CUSTOMER#42   SK = PROFILE           name=Asha
PK = CUSTOMER#42   SK = ORDER#1001        total=114.0
Query PK = CUSTOMER#42 AND begins_with(SK, "ORDER#")  -> all orders for customer 42

NoSQL Families

Type Example Typical use
Document MongoDB Product catalogues, content, user profiles
Key-value Redis, DynamoDB Sessions, caching, carts, high-scale lookups
Wide-column Cassandra Time-series, IoT, write-heavy workloads
Graph Neo4j Recommendations, fraud detection, social networks

Choosing

Requirement Leaning
Ad hoc queries, reporting, joins SQL
Strict integrity, financial data SQL (or NoSQL with transactions, carefully)
Millions of writes per second, multi-region Cassandra, DynamoDB
Schemaless or rapidly changing data Document store or PostgreSQL jsonb
Deep relationship traversal Graph database

Pitfalls

  • Choosing NoSQL for “scale” that never materialises, then reimplementing joins and constraints in code.

  • Modelling NoSQL like SQL (normalised collections and many lookups).

  • Ignoring consistency semantics (reading stale data after writes).

  • “Schemaless” meaning no schema discipline, so data quality decays.

Interview Tips

  • Compare data model, schema, relationships, transactions, consistency and scaling.

  • Give examples per category and use cases.

  • Mention the convergence (JSON in SQL, transactions in NoSQL, distributed SQL).

4

Describe a relational database schema.

A relational database schema is the blueprint of a database. It defines: - tables (relations) and their columns (attributes), with data types; - keys: a primary key that uniquely identifies each row, and foreign keys that link rows across tables; - constraints: NOT NULL, UNIQUE, CHECK and DEFAULT values, which keep data valid; - relationships: one-to-one, one-to-many (a customer has many orders), and many-to-many (orders and products, through a junction table); - indexes to speed up queries, plus views, sequences, triggers and stored procedures; - namespaces: in PostgreSQL a “schema” is also a named container (public, sales, audit) that groups objects and permissions.

The schema is written in DDL (CREATE TABLE, ALTER TABLE) and is usually designed from a conceptual model (entities and relationships in an ER diagram), refined through normalisation to remove redundancy. It is enforced on write (schema-on-write): rows that don’t fit are rejected. Changes go through versioned migrations (Rails migrations, Flyway, Liquibase, Alembic).

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, Oracle, SQLite

Example: E-commerce Schema (PostgreSQL)

CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku    TEXT NOT NULL UNIQUE,
    name   TEXT NOT NULL,
    price  NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),         -- one customer, many orders
    status       TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (                                          -- junction: orders <-> products
    order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10,2) NOT NULL,                             -- price at time of purchase
    PRIMARY KEY (order_id, product_id)                              -- composite key
);

CREATE INDEX idx_orders_customer ON orders (customer_id, placed_at DESC);

Entity-Relationship View

customers 1 ──< orders 1 ──< order_items >── 1 products
   (id)          (customer_id)   (order_id, product_id)    (id)

Schema Components

Component Purpose Example
Table Stores one entity type orders
Column + type Attribute with a domain price NUMERIC(10,2)
Primary key Unique row identity id
Foreign key Relationship and integrity orders.customer_id → customers.id
Constraints Business rules CHECK (quantity > 0)
Index Faster lookups and joins idx_orders_customer
View Saved query, abstraction CREATE VIEW paid_orders AS ...
Schema namespace Grouping and permissions sales.orders

Design Practices

  • Model entities and relationships first, then normalise (usually to 3NF), then denormalise deliberately for proven performance needs.

  • Choose precise types (NUMERIC for money, TIMESTAMPTZ for times).

  • Put business rules in constraints where possible, so every application path obeys them.

  • Index foreign keys and frequent filter columns.

  • Version every change as a migration, reviewed like code.

Pitfalls

  • Storing lists in a column ("1,4,7") instead of a junction table.

  • Floats for money.

  • Missing foreign keys, leading to orphaned rows.

  • Over-normalising into many tiny tables that every query must join.

Interview Tips

  • Describe tables, columns, types, keys, constraints, relationships and indexes.

  • Show a small schema with one-to-many and many-to-many relationships.

  • Mention normalisation, migrations and schema-on-write.

5

What is a primary key, and why is it important?

A primary key (PK) is a column, or set of columns, whose value uniquely identifies each row in a table. A table has at most one primary key. Its values must be unique and never NULL, and the database enforces both. Most databases also build an index on it automatically: in InnoDB (MySQL) and SQL Server by default, the table is even physically organised by the primary key (a clustered index).

Why it is important: - Entity integrity: every row can be identified unambiguously, with no duplicates. - Relationships: foreign keys in other tables reference it, which is how relational data links together. - Fast access: lookups, updates and deletes by key use the index rather than scanning the table. - Replication, CDC and ORMs: logical replication, change data capture and frameworks (Rails, Django, Hibernate) rely on a stable row identity. - Safe updates: you can target exactly one row (UPDATE ... WHERE id = 42).

Read full answer

Choices: - Natural keys are real-world identifiers (email, ISBN). They are meaningful, but they can change and may be long. - Surrogate keys are system-generated: auto-increment integers, identity columns, UUIDs (UUIDv7 is time-ordered and index-friendly), or short random string IDs. They are stable and meaningless. - Composite keys combine several columns (order_id, product_id in a junction table).

A common practice is a surrogate primary key plus a UNIQUE constraint on the natural key.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, SQLite (examples use SQLite via Python 3.12)

Code Example: Enforcement and Index Use

# Primary keys: uniqueness and NOT NULL enforced, fast lookups by key
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE users (
    id    INTEGER PRIMARY KEY,          -- surrogate key (auto-assigned rowid alias)
    email TEXT NOT NULL UNIQUE,         -- natural candidate key, kept unique
    name  TEXT NOT NULL)""")
db.execute("INSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha')")
db.execute("INSERT INTO users (email, name) VALUES ('ravi@example.com', 'Ravi')")

for sql, args in [("INSERT INTO users (id, email, name) VALUES (1, 'x@example.com', 'X')", ()),
                  ("INSERT INTO users (email, name) VALUES ('asha@example.com', 'Dup')", ())]:
    try:
        db.execute(sql, args)
    except sqlite3.IntegrityError as e:
        print("rejected:", e)
# rejected: UNIQUE constraint failed: users.id
# rejected: UNIQUE constraint failed: users.email

print(db.execute("EXPLAIN QUERY PLAN SELECT name FROM users WHERE id = 2").fetchall()[0][-1])
# SEARCH users USING INTEGER PRIMARY KEY (rowid=?)   <- index lookup, not a table scan

Defining Primary Keys

-- Surrogate identity key (PostgreSQL)
CREATE TABLE users (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

-- UUID key (PostgreSQL 13+; gen_random_uuid() is random v4, UUIDv7 is time-ordered)
CREATE TABLE events (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payload JSONB);

-- Composite key in a junction table
CREATE TABLE enrolments (
    student_id BIGINT REFERENCES students(id),
    course_id  BIGINT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

PostgreSQL 18 adds a built-in uuidv7() function.

Key Types Compared

Type Pros Cons
Auto-increment / identity Small, fast, ordered inserts Guessable; hard to merge across shards
Random UUID (v4) Globally unique, generated anywhere 16 bytes; random inserts fragment B-tree indexes
Time-ordered UUID (v7) / ULID Unique and index-friendly Larger than integers; reveals creation time
Natural key Meaningful, no extra column Can change; may be long or not truly unique
Composite Models junction tables naturally Verbose foreign keys and joins

Pitfalls

  • Tables without a primary key: duplicates, ambiguous updates, problems with replication and ORMs.

  • Mutable natural keys (emails, phone numbers) cascading updates everywhere.

  • Random UUIDv4 as a clustered key in high-insert tables, which causes page splits.

  • Exposing sequential IDs publicly, allowing enumeration of records.

Interview Tips

  • Define it: unique, not null, one per table, usually indexed.

  • Explain its role in integrity, relationships and performance.

  • Compare natural, surrogate (integer, UUID) and composite keys with trade-offs.

6

Explain what a foreign key is and its role in a database.

A foreign key (FK) is a column, or set of columns, in one table (the child or referencing table) that refers to the primary key or a unique key of another table (the parent or referenced table). For example, orders.customer_id references customers.id.

Its role: - Referential integrity: the database guarantees that every non-NULL foreign key value matches an existing parent row. You cannot create an order for a non-existent customer, and you cannot delete a customer who still has orders, unless a referential action says otherwise. This prevents orphaned rows. - Modelling relationships: foreign keys express one-to-many (customer → orders), one-to-one (user → profile, with a unique FK), and many-to-many (through junction tables with two FKs). - Referential actions: what happens when a parent row is deleted or its key updated: - ON DELETE CASCADE: delete the children too; - SET NULL or SET DEFAULT; - RESTRICT or NO ACTION: block the change, which is the default. - Documentation and tooling: foreign keys make relationships explicit for developers, ER diagram tools and ORMs, and the planner can use them for some optimisations, such as join elimination.

Read full answer

Considerations: foreign keys add checks on every insert, update and delete. The child column usually needs its own index, for fast joins and to avoid slow parent deletes (most databases don’t create it automatically; MySQL InnoDB does). Foreign keys don’t work across separate databases or shards, so sharded and microservice architectures often enforce relationships in application code instead.

Applies to: PostgreSQL, MySQL InnoDB, SQL Server, Oracle, SQLite (examples use SQLite via Python 3.12)

Code Example: Integrity and Cascade

# Foreign keys: referential integrity between orders and customers
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")              # SQLite enforces FKs only when enabled
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    total       REAL NOT NULL
);
CREATE INDEX idx_orders_customer ON orders(customer_id);   -- index FK columns for joins/deletes
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 99.0), (11, 1, 15.0), (12, 2, 42.0);
""")

try:
    db.execute("INSERT INTO orders VALUES (13, 99, 5.0)")   # customer 99 doesn't exist
except sqlite3.IntegrityError as e:
    print("rejected:", e)                                   # rejected: FOREIGN KEY constraint failed

db.execute("DELETE FROM customers WHERE id = 1")             # cascades to Asha's orders
print(db.execute("SELECT id, customer_id FROM orders").fetchall())   # [(12, 2)]

Syntax and Options

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT          -- block deleting customers that have orders
        ON UPDATE CASCADE
);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Add a foreign key to a large existing PostgreSQL table without a long lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer2
    FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer2;   -- checks existing rows, weaker lock

Referential Actions

Action On parent delete Use when
RESTRICT / NO ACTION Error if children exist Children are important records (orders, invoices)
CASCADE Delete children Children have no meaning alone (order items)
SET NULL Child FK becomes NULL Relationship is optional (assigned agent leaves)
SET DEFAULT Child FK set to default Reassign to a placeholder parent

Relationship Patterns

Relationship How
One-to-many FK on the “many” side (orders.customer_id)
One-to-one FK with a UNIQUE constraint (profiles.user_id UNIQUE)
Many-to-many Junction table with two FKs and a composite PK
Self-referencing FK to the same table (employees.manager_id → employees.id)

Pitfalls

  • Missing index on the FK column: slow joins, and parent deletes that scan the whole child table.

  • Overusing CASCADE, so one delete silently removes huge amounts of data.

  • SQLite without PRAGMA foreign_keys = ON: constraints are declared but not enforced.

  • Skipping FKs “for speed”, then fixing orphaned data later.

Interview Tips

  • Define it as a reference from a child column to a parent key, enforcing referential integrity.

  • Explain referential actions and relationship patterns.

  • Mention indexing FK columns and limits across shards and services.

7

What is database normalization, and why do we use it?

Normalization is the process of organising a relational schema to reduce redundancy and prevent anomalies. Each fact is stored once, in the table where it belongs, and tables are linked with keys. It proceeds through normal forms, each removing a kind of problematic dependency: - 1NF (First Normal Form): each column holds atomic values (no lists or repeating groups), and rows are uniquely identifiable. - 2NF: 1NF, plus every non-key column depends on the whole primary key, with no partial dependencies on part of a composite key. - 3NF: 2NF, plus non-key columns depend only on the key, with no transitive dependencies (for example zip → city stored in an orders table). - BCNF (Boyce–Codd): every determinant is a candidate key, which is a stricter 3NF. - 4NF and 5NF deal with multi-valued and join dependencies (rarely needed in practice).

Why we use it: - Avoids anomalies: - update anomalies: changing a fact in one row but not its copies; - insert anomalies: being unable to record a fact without unrelated data; - delete anomalies: losing a fact when deleting another. - Data integrity and consistency: one source of truth per fact. - Less storage and smaller write amplification from duplicated data. - Clear, flexible design that adapts to new queries.

Read full answer

The trade-off: normalised data needs joins to reassemble. For read-heavy workloads, teams sometimes denormalise deliberately. 3NF (or BCNF) is the usual target for transactional (OLTP) systems.

Applies to: relational database design (examples use SQLite via Python 3.12)

Code Example: Update Anomaly, Then the Normalised Fix

# Update anomaly in an unnormalised table, fixed by normalising into two tables
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
-- Unnormalised: customer details repeated on every order
CREATE TABLE orders_flat (order_id INTEGER PRIMARY KEY, customer TEXT, city TEXT, total REAL);
INSERT INTO orders_flat VALUES (1, 'Asha', 'Pune', 99), (2, 'Asha', 'Pune', 15), (3, 'Ravi', 'Delhi', 42);

-- Normalised (3NF): customer facts stored once
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total REAL);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Ravi', 'Delhi');
INSERT INTO orders VALUES (1, 1, 99), (2, 1, 15), (3, 2, 42);
""")

# Asha moves. A careless update touches only one of her rows in the flat table:
db.execute("UPDATE orders_flat SET city = 'Mumbai' WHERE order_id = 1")
print(db.execute("SELECT DISTINCT city FROM orders_flat WHERE customer = 'Asha'").fetchall())
# [('Mumbai',), ('Pune',)]   <- contradictory data (update anomaly)

db.execute("UPDATE customers SET city = 'Mumbai' WHERE id = 1")          # one row, one fact
print(db.execute("""SELECT o.id, c.name, c.city FROM orders o
                    JOIN customers c ON c.id = o.customer_id WHERE c.id = 1""").fetchall())
# [(1, 'Asha', 'Mumbai'), (2, 'Asha', 'Mumbai')]

Walking Through the Normal Forms

Unnormalised:
order_id | customer | city  | products
1        | Asha     | Pune  | "Keyboard, Mouse"          <- list in a column (violates 1NF)

1NF: one product per row
order_id | product  | customer | city | product_price
1        | Keyboard | Asha     | Pune | 49
1        | Mouse    | Asha     | Pune | 19.5

2NF: product_price depends only on product, not on (order_id, product): split it out
products(product, product_price)
order_lines(order_id, product)
orders(order_id, customer, city)

3NF: city depends on customer, not on the order: split it out
customers(customer_id, name, city)
orders(order_id, customer_id)

Anomalies Prevented

Anomaly Unnormalised example Normalised result
Update Asha’s city stored on 500 orders; one update misses some City stored once in customers
Insert Can’t add a product until someone orders it products table exists independently
Delete Deleting the only order loses the customer’s details Customer row remains

When to Stop or Reverse

Situation Approach
OLTP application Normalise to 3NF/BCNF
Read-heavy dashboards or feeds Denormalised copies, materialised views, caches
Data warehouse Star schema (deliberately denormalised dimensions)
Historical facts (price at purchase) Store a copy intentionally; it isn’t redundancy, it’s a different fact

Pitfalls

  • Over-normalising into many tiny tables that every query joins.

  • Treating snapshots as redundancy (order price must not change when the product price changes).

  • Denormalising before measuring a real performance problem.

Interview Tips

  • Define normalisation and its goal: remove redundancy and anomalies.

  • Explain 1NF, 2NF and 3NF with a concrete example; mention BCNF.

  • Discuss the join cost trade-off and deliberate denormalisation.

8

What is denormalization and when would you consider it?

Denormalization is the deliberate introduction of redundancy into a normalised schema to make reads faster or simpler. It stores duplicated or precomputed data so queries avoid expensive joins or aggregations. Common forms: - duplicated columns: storing customer_name on orders; - precomputed aggregates: order_count, comments_count or running totals; - summary or reporting tables, and materialised views; - embedded documents: JSON columns, or NoSQL documents holding related data together; - star schemas in data warehouses.

When to consider it: - Read-heavy workloads where the same expensive join or aggregate runs constantly (feeds, dashboards, product listings), and indexes, query tuning and caching weren’t enough. - Latency-critical paths that must answer from a single row or document. - Analytics and reporting (OLAP), where star or snowflake schemas and wide tables suit columnar engines. - Distributed and NoSQL systems without efficient joins (DynamoDB, Cassandra), where data is modelled per access pattern. - Historical snapshots, such as order line prices. Strictly, these are different facts, not redundancy.

Read full answer

The cost: - Write complexity: every copy must be kept in sync through triggers, application code, background jobs or CDC. - Risk of inconsistency when an update misses a copy. - More storage. - Harder schema changes.

Measure first, denormalise the specific hot path, define who maintains each copy, and decide whether it must be exactly consistent (same transaction or a trigger) or can be eventually consistent (async jobs).

Applies to: relational and NoSQL data modelling (examples use SQLite via Python 3.12)

Code Example: Counter Cache Maintained by Triggers

# Denormalisation: keep a precomputed order count on customers, maintained by a trigger
import sqlite3, time

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, order_count INTEGER NOT NULL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TRIGGER orders_ai AFTER INSERT ON orders BEGIN
    UPDATE customers SET order_count = order_count + 1 WHERE id = NEW.customer_id;
END;
CREATE TRIGGER orders_ad AFTER DELETE ON orders BEGIN
    UPDATE customers SET order_count = order_count - 1 WHERE id = OLD.customer_id;
END;
""")
db.executemany("INSERT INTO customers (id, name) VALUES (?, ?)", [(i, f"c{i}") for i in range(1000)])
db.executemany("INSERT INTO orders (customer_id, total) VALUES (?, ?)",
               [(i % 1000, 10.0) for i in range(200_000)])

t = time.perf_counter()
joined = db.execute("""SELECT c.id, COUNT(o.id) FROM customers c
                       LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id""").fetchall()
t_join = time.perf_counter() - t
t = time.perf_counter()
stored = db.execute("SELECT id, order_count FROM customers").fetchall()
t_read = time.perf_counter() - t

print("same answer:", dict(joined) == dict(stored))       # True
print("aggregate is slower:", t_join > t_read)            # True (exact times vary by machine)

The stored order_count answers instantly, while the join-and-group query scans 200,000 orders. The triggers keep it correct within the same transaction as each insert or delete.

Techniques

Technique Example Consistency
Duplicated column orders.customer_name Must be updated on rename (or kept as a snapshot)
Counter cache posts.comments_count (Rails counter_cache) Same transaction via trigger or callback
Materialised view CREATE MATERIALIZED VIEW sales_daily ... Refreshed periodically (REFRESH MATERIALIZED VIEW CONCURRENTLY)
Summary table daily_revenue built by a job Eventually consistent
JSON embedding orders.shipping_address jsonb Snapshot of the address at order time
Search index or cache Elasticsearch, Redis Eventually consistent (CDC, events)

Example: PostgreSQL Materialised View

CREATE MATERIALIZED VIEW product_sales AS
SELECT p.id, p.name, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name;

CREATE UNIQUE INDEX ON product_sales (id);                 -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales;      -- readers aren't blocked

Normalised Versus Denormalised

Aspect Normalised Denormalised
Reads Joins and aggregates at query time Fast, often single-row
Writes Simple, one place Multiple places to update
Consistency Guaranteed by design Must be maintained
Storage Minimal Larger
Best for OLTP, frequently changing data Read-heavy, analytics, NoSQL access patterns

Pitfalls

  • Denormalising before profiling: an index might have solved it.

  • No owner for keeping copies in sync, so data drifts silently.

  • Counter caches drifting after bulk operations that bypass triggers or callbacks: run periodic reconciliation.

  • Treating denormalised copies as the source of truth.

Interview Tips

  • Define it as intentional redundancy for read performance.

  • Give when: read-heavy hot paths, analytics, NoSQL access patterns, snapshots.

  • Discuss costs and sync mechanisms (triggers, materialised views, jobs, CDC), and measure first.

9

Compare and contrast the DROP, DELETE, and TRUNCATE commands.

All three remove data, but at different levels and with different costs and guarantees: - DELETE (DML) removes rows, optionally filtered with WHERE. It works row by row: each deletion is logged, row-level triggers fire, foreign keys are checked (with cascades), and it is fully transactional, so it can be rolled back. The table, its structure, indexes and permissions remain. Identity or auto-increment counters are not reset. Large unfiltered deletes are slow and generate a lot of log, and in MVCC databases (PostgreSQL) they leave dead tuples for VACUUM. - TRUNCATE removes all rows at once by deallocating the table’s data pages, which is very fast with minimal logging. It has no WHERE clause and doesn’t fire row-level DELETE triggers. It usually resets identity counters (RESTART IDENTITY in PostgreSQL, automatic in MySQL and SQL Server). It takes a strong lock on the table and fails if other tables reference it with foreign keys, unless you use CASCADE in PostgreSQL. Whether it can be rolled back depends on the database: PostgreSQL and SQL Server allow it inside a transaction; MySQL and Oracle commit implicitly (it is treated as DDL). - DROP (DDL) removes the table object itself: its structure, data, indexes, constraints, triggers and permissions. Dependent objects (views, foreign keys) block it unless you use CASCADE. It cannot be undone except by restoring from backup, Oracle’s recycle bin (FLASHBACK TABLE), or a transaction in databases with transactional DDL (PostgreSQL).

Summary: use DELETE for selective or audited removal, TRUNCATE to empty a table quickly (staging tables, test resets), and DROP when the table is no longer needed.

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, Oracle (example uses SQLite via Python 3.12)

Code Example: DELETE and DROP

# DELETE vs DROP (SQLite has no TRUNCATE; an unfiltered DELETE uses its "truncate optimisation")
import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.executescript("""
CREATE TABLE logs (id INTEGER PRIMARY KEY, level TEXT, msg TEXT);
INSERT INTO logs (level, msg) VALUES ('INFO', 'start'), ('ERROR', 'boom'), ('INFO', 'stop');
""")

db.execute("BEGIN")
db.execute("DELETE FROM logs WHERE level = 'INFO'")          # removes matching rows only
print(db.execute("SELECT * FROM logs").fetchall())           # [(2, 'ERROR', 'boom')]
db.execute("ROLLBACK")                                       # DELETE is transactional
print(db.execute("SELECT COUNT(*) FROM logs").fetchone()[0]) # 3

db.execute("DELETE FROM logs")                               # all rows; table and schema remain
print(db.execute("SELECT COUNT(*) FROM logs").fetchone()[0]) # 0

db.execute("DROP TABLE logs")                                # table definition is gone too
try:
    db.execute("SELECT * FROM logs")
except sqlite3.OperationalError as e:
    print(e)                                                 # no such table: logs

SQLite has no TRUNCATE statement. An unfiltered DELETE FROM logs uses its internal truncate optimisation (when there are no triggers).

SQL Examples

-- DELETE: selective, transactional, fires triggers
DELETE FROM sessions WHERE expires_at < now();

-- TRUNCATE: empty tables fast and reset sequences (PostgreSQL)
TRUNCATE TABLE staging_orders, staging_items RESTART IDENTITY;

-- DROP: remove the table and everything attached to it
DROP TABLE IF EXISTS old_reports;
DROP TABLE customers CASCADE;   -- PostgreSQL: also drops dependent views and FK constraints

Comparison

Aspect DELETE TRUNCATE DROP
Category DML DDL (in most databases) DDL
Removes Selected or all rows All rows Table + data + structure
WHERE clause Yes No No
Speed on big tables Slow (row by row) Very fast Very fast
Logging Every row Page deallocation (minimal) Minimal
Row triggers Fire Don’t fire Not applicable
Identity reset No Usually yes Gone with table
Rollback Yes PostgreSQL, SQL Server: yes; MySQL, Oracle: implicit commit PostgreSQL: yes; most others: no
Referenced by FKs Checked per row Blocked (unless CASCADE) Blocked (unless CASCADE)
Privilege DELETE TRUNCATE / ALTER DROP / ownership

Practical Tips

  • Batch large deletes (DELETE ... WHERE id IN (SELECT id ... LIMIT 10000)) to avoid huge transactions and lock contention.

  • For time-based data, use table partitioning and drop or detach old partitions, which is far cheaper than DELETE.

  • In PostgreSQL, run VACUUM (or rely on autovacuum) after big deletes.

  • Take backups and double-check the database before DROP or TRUNCATE in production.

Pitfalls

  • DELETE without WHERE by accident: always run the SELECT first, inside a transaction.

  • Expecting TRUNCATE to be rollback-safe in MySQL or Oracle.

  • TRUNCATE skipping triggers that maintain audit logs or counters.

  • Running DROP in the wrong environment.

Interview Tips

  • Contrast rows versus all rows versus the whole object.

  • Cover logging, triggers, rollback, identity reset and FK behaviour, with database-specific differences.

  • Mention partition dropping for large-scale retention.

10

What is the difference between a full join and an inner join?

An INNER JOIN returns only the rows that have a match in both tables according to the join condition. Rows without a partner on either side are excluded.

A FULL OUTER JOIN (FULL JOIN) returns all rows from both tables: - matched pairs, combined as in an inner join; - unmatched rows from the left table, with NULLs for the right table’s columns; - unmatched rows from the right table, with NULLs for the left table’s columns.

Read full answer

It is effectively the union of a LEFT JOIN and a RIGHT JOIN.

Use an inner join when you only care about related data, such as orders with their customers. Use a full join when you need to see everything from both sides, including mismatches, for example: - reconciliation (comparing two systems’ records); - data-quality checks for orphans on either side; - merging two lists.

Important details: - NULLs in the result show where matches are missing. Use COALESCE to merge key columns. - Filtering in WHERE on the outer side can accidentally turn an outer join into an inner join; put conditions in ON where appropriate. - MySQL doesn’t support FULL OUTER JOIN: emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. SQLite supports it from version 3.39.

Applies to: PostgreSQL, SQL Server, Oracle, SQLite 3.39+ (MySQL needs emulation; example uses Python 3.12)

Code Example

# INNER JOIN vs FULL OUTER JOIN (SQLite 3.39+ supports RIGHT and FULL joins)
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
CREATE TABLE departments (id INTEGER PRIMARY KEY, dept TEXT);
INSERT INTO employees VALUES (1, 'Asha', 10), (2, 'Ravi', 20), (3, 'Meera', NULL);
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'Legal');
""")

inner = db.execute("""SELECT e.name, d.dept FROM employees e
                      INNER JOIN departments d ON d.id = e.dept_id ORDER BY e.id""").fetchall()
full = db.execute("""SELECT e.name, d.dept FROM employees e
                     FULL OUTER JOIN departments d ON d.id = e.dept_id
                     ORDER BY e.id IS NULL, e.id""").fetchall()
print("inner:", inner)
# inner: [('Asha', 'Engineering'), ('Ravi', 'Sales')]
print("full: ", full)
# full:  [('Asha', 'Engineering'), ('Ravi', 'Sales'), ('Meera', None), (None, 'Legal')]

Meera has no department, and Legal has no employees. The inner join drops both; the full join keeps both, with NULLs.

Visual

INNER JOIN:          FULL OUTER JOIN:
   A ∩ B               A ∪ B
 [ ( ## ) ]          [ (####) ]
 only matches        matches + unmatched A + unmatched B

Reconciliation Query (PostgreSQL)

SELECT COALESCE(b.txn_id, l.txn_id) AS txn_id,
       b.amount AS bank_amount, l.amount AS ledger_amount,
       CASE WHEN b.txn_id IS NULL THEN 'missing in bank'
            WHEN l.txn_id IS NULL THEN 'missing in ledger'
            WHEN b.amount <> l.amount THEN 'amount mismatch' END AS issue
FROM bank_txns b
FULL OUTER JOIN ledger_txns l ON l.txn_id = b.txn_id
WHERE b.txn_id IS NULL OR l.txn_id IS NULL OR b.amount <> l.amount;

MySQL Emulation

SELECT e.name, d.dept FROM employees e LEFT JOIN departments d ON d.id = e.dept_id
UNION
SELECT e.name, d.dept FROM employees e RIGHT JOIN departments d ON d.id = e.dept_id;

UNION removes duplicates, including legitimately identical rows. A safer variant uses UNION ALL with the second query restricted to WHERE e.id IS NULL.

Join Types at a Glance

Join Returns
INNER Matches only
LEFT OUTER All left rows + matches (NULLs where no right match)
RIGHT OUTER All right rows + matches
FULL OUTER All rows from both sides
CROSS Every combination (Cartesian product)

Pitfalls

  • WHERE on the nullable side (WHERE d.dept = 'Sales') silently removes unmatched rows.

  • Duplicates multiplying rows when join keys aren’t unique.

  • Using a full join where an inner join suffices: more rows, more work.

  • Forgetting MySQL’s lack of FULL JOIN.

Interview Tips

  • Define both clearly: matches only versus everything from both sides with NULLs.

  • Give a small example and a use case (reconciliation).

  • Mention NULL handling, WHERE pitfalls and MySQL emulation.

11

How would you write an SQL query to fetch duplicate records?

First define what makes two records duplicates: one column (email), a combination (first name, last name, birth date), or a normalised form (case-insensitive, trimmed). Then: 1. Find the duplicated keys and their counts with GROUP BY ... HAVING COUNT(*) > 1: sql SELECT email, COUNT(*) FROM contacts GROUP BY email HAVING COUNT(*) > 1; 2. Fetch the full duplicate rows, either by joining back to that grouped result, or more flexibly with a window function: ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) numbers rows within each duplicate group. Rows with rn > 1 are the extra copies, and COUNT(*) OVER (PARTITION BY key) > 1 returns all members of duplicate groups. 3. Optionally remove the extra copies (keep the oldest or newest), then prevent recurrence with a UNIQUE constraint or unique index, case-insensitive where needed.

Read full answer

Applies toPostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+ (example uses Python 3.12)

Code Example: Find, Inspect and Remove Duplicates

# Finding duplicate records: GROUP BY/HAVING for keys, window functions for the full rows
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE contacts (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
INSERT INTO contacts (email, name) VALUES
  ('asha@example.com', 'Asha'), ('ravi@example.com', 'Ravi'),
  ('ASHA@example.com ', 'Asha K'), ('ravi@example.com', 'Ravi S'), ('meera@example.com', 'Meera');
""")

print(db.execute("""
    SELECT lower(trim(email)) AS e, COUNT(*) AS n FROM contacts
    GROUP BY lower(trim(email)) HAVING COUNT(*) > 1 ORDER BY e""").fetchall())
# [('asha@example.com', 2), ('ravi@example.com', 2)]

rows = db.execute("""
    SELECT id, email, name FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY lower(trim(email)) ORDER BY id) AS rn
        FROM contacts)
    WHERE rn > 1 ORDER BY id""").fetchall()
print(rows)   # [(3, 'ASHA@example.com ', 'Asha K'), (4, 'ravi@example.com', 'Ravi S')]  <- extra copies

db.execute("DELETE FROM contacts WHERE id IN (%s)" % ",".join("?" * len(rows)), [r[0] for r in rows])
print(db.execute("SELECT COUNT(*) FROM contacts").fetchone()[0])   # 3

Normalising with lower(trim(email)) catches near-duplicates that differ only by case or spaces.

Query Variants

-- 1. All rows that belong to a duplicate group (join back)
SELECT c.*
FROM contacts c
JOIN (SELECT email FROM contacts GROUP BY email HAVING COUNT(*) > 1) d
  ON d.email = c.email
ORDER BY c.email, c.id;

-- 2. Same with a window count
SELECT * FROM (
  SELECT c.*, COUNT(*) OVER (PARTITION BY email) AS cnt FROM contacts c
) t WHERE cnt > 1;

-- 3. Duplicates over several columns
SELECT first_name, last_name, birth_date, COUNT(*)
FROM people
GROUP BY first_name, last_name, birth_date
HAVING COUNT(*) > 1;

-- 4. Delete extra copies, keeping the lowest id (PostgreSQL)
DELETE FROM contacts c
USING contacts k
WHERE c.email = k.email AND c.id > k.id;

-- 5. Prevent new duplicates (case-insensitive, PostgreSQL)
CREATE UNIQUE INDEX uq_contacts_email ON contacts (lower(email));

In MySQL, a self-join delete looks like: DELETE c FROM contacts c JOIN contacts k ON c.email = k.email AND c.id > k.id;.

Choosing a Method

Goal Method
Which values are duplicated, and how often GROUP BY ... HAVING COUNT(*) > 1
All rows in duplicate groups Join back, or COUNT(*) OVER (PARTITION BY ...)
Only the extra copies ROW_NUMBER() ... WHERE rn > 1
Keep one specific row (newest) ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC)
Exact duplicate rows (all columns) GROUP BY every column

Pitfalls

  • NULLs: GROUP BY treats NULLs as one group, but = joins never match NULL, so join-back queries skip them.

  • Deleting without a backup or a transaction; check counts first.

  • Choosing the wrong survivor (keeping a stale copy); decide on a rule (oldest, newest, most complete).

  • Fixing data without adding a constraint, so duplicates return.

Interview Tips

  • Start with GROUP BY + HAVING, then show window functions for full rows.

  • Discuss normalisation of the key (case, whitespace) and multi-column duplicates.

  • Mention safe deletion and unique constraints for prevention.

12

What is a prepared statement, and why would you use one?

A prepared statement is an SQL statement sent to the database as a template with placeholders (?, $1, :name). It is parsed, validated and planned once, then executed one or more times with different parameter values supplied separately from the SQL text. Because the values never become part of the SQL string, the database always treats them as data, not code.

Why use them: - Security, preventing SQL injection: this is the main reason. Input like ' OR '1'='1 is compared as a literal string instead of changing the query’s logic. Parameterisation is the primary defence recommended by OWASP. - Performance: repeated executions skip parsing and planning. Plans can be cached (PostgreSQL may switch to a generic plan after several executions), and batch inserts are efficient. - Correct typing and escaping: dates, binary data, Unicode and quotes are handled by the driver, with no manual escaping bugs. - Cleaner code, without string concatenation.

Read full answer

Limits: placeholders work only for values, not identifiers or keywords (table names, column names, ASC/DESC). Validate those against an allowlist. Also note that some drivers emulate prepares client-side (for example PHP PDO with MySQL by default), and connection poolers in transaction mode (older PgBouncer versions) need care with server-side prepared statements. PgBouncer 1.21+ supports them.

Applies to: all SQL databases and drivers (example uses SQLite via Python 3.12)

Code Example: Injection Versus Parameters

# Prepared (parameterised) statements: data never becomes SQL, and the plan is reused
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT);
INSERT INTO users (name, role) VALUES ('asha', 'admin'), ('ravi', 'user');
""")

evil = "nobody' OR '1'='1"
unsafe = f"SELECT name, role FROM users WHERE name = '{evil}'"           # string building
print("concatenated:", db.execute(unsafe).fetchall())
# concatenated: [('asha', 'admin'), ('ravi', 'user')]   <- injection returned every row

print("parameterised:", db.execute("SELECT name, role FROM users WHERE name = ?", (evil,)).fetchall())
# parameterised: []   <- the whole input is compared as one string value

# Same statement, many executions: compiled once and cached by the sqlite3 module
db.executemany("INSERT INTO users (name, role) VALUES (?, ?)", [(f"u{i}", "user") for i in range(1000)])
print(db.execute("SELECT COUNT(*) FROM users").fetchone()[0])            # 1002

Prepared Statements in SQL and in Drivers

-- PostgreSQL server-side PREPARE / EXECUTE
PREPARE find_user (text) AS SELECT id, name FROM users WHERE email = $1;
EXECUTE find_user('asha@example.com');
DEALLOCATE find_user;
// JDBC
try (PreparedStatement ps = conn.prepareStatement("SELECT id FROM users WHERE email = ?")) {
    ps.setString(1, email);
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}
# Rails / ActiveRecord: bind parameters are used automatically
User.where(email: params[:email])
User.where("created_at > ?", 1.week.ago)

Identifiers Can’t Be Parameters: Use an Allowlist

SORTABLE = {"name": "name", "created": "created_at"}
def order_clause(field, direction):
    col = SORTABLE.get(field, "created_at")             # never interpolate raw user input
    return f"ORDER BY {col} {'DESC' if direction == 'desc' else 'ASC'}"
print(order_clause("name; DROP TABLE users", "desc"))   # ORDER BY created_at DESC

Benefits Summary

Benefit How
Injection protection Values sent separately from SQL text
Speed for repeated queries Parse and plan once, execute many times
Batch efficiency executemany, JDBC addBatch()
Type safety Driver binds typed values
Readability No string building

Pitfalls

  • Building SQL with f-strings or concatenation “just this once”.

  • Parameterising only some values in a query.

  • LIKE wildcards: the parameter is safe, but users can still send %; escape wildcards if needed.

  • Generic plans that are bad for skewed data (PostgreSQL plan_cache_mode can force custom plans).

Interview Tips

  • Define it as a precompiled template plus separately bound parameters.

  • Lead with SQL injection prevention, then performance.

  • Mention identifier allowlists, ORMs’ bind parameters, and pooler caveats.

13

What is the N+1 query problem and how can you solve it?

The N+1 query problem happens when code runs one query to fetch a list of N records, then one more query for each record to load related data: N+1 queries in total, where one or two would do. It typically appears with ORMs and lazy loading. For example, listing 100 posts and calling post.author.name in a loop triggers 100 separate SELECT ... FROM authors WHERE id = ? queries. Each query costs a network round trip, parsing and execution. At scale this means slow pages, high database load and connection pool pressure, even though each query is fast on its own. Logs show a burst of near-identical queries.

Solutions: - Eager loading / preloading: the ORM loads associations in one extra query using WHERE id IN (...): - Rails: includes, preload, eager_load; - Django: select_related (JOIN), prefetch_related (IN query); - Hibernate and JPA: JOIN FETCH, entity graphs, batch fetching; - SQLAlchemy: selectinload, joinedload; - Prisma: include. - A JOIN in a single query, when you need columns from both tables. - Batching / DataLoader pattern in GraphQL resolvers: collect IDs during a tick, then load them in one query. - Aggregates in SQL instead of per-row counts: GROUP BY, or counter caches. - Detection: query logging, APM traces, Bullet (Rails), nplusone or Django Debug Toolbar, Hibernate statistics, plus query-count assertions in tests. Rails also has strict_loading to raise errors on lazy loads.

Read full answer

Applies toORMs (ActiveRecord, Django ORM, Hibernate, SQLAlchemy, Prisma) and GraphQL (example uses SQLite via Python 3.12)

Code Example: Counting the Queries

# N+1 queries vs a JOIN vs batched IN (...) loading, counting statements with a trace callback
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER REFERENCES authors(id), title TEXT);
""")
db.executemany("INSERT INTO authors VALUES (?, ?)", [(i, f"author{i}") for i in range(1, 51)])
db.executemany("INSERT INTO posts (author_id, title) VALUES (?, ?)",
               [((i % 50) + 1, f"post{i}") for i in range(200)])

queries = []
db.set_trace_callback(queries.append)

def n_plus_one():
    out = []
    for pid, aid, title in db.execute("SELECT id, author_id, title FROM posts").fetchall():
        name = db.execute("SELECT name FROM authors WHERE id = ?", (aid,)).fetchone()[0]  # 1 per post
        out.append((title, name))
    return out

def joined():
    return db.execute("""SELECT p.title, a.name FROM posts p
                         JOIN authors a ON a.id = p.author_id""").fetchall()

def preload():                                     # what ORMs do with includes / prefetch_related
    posts = db.execute("SELECT id, author_id, title FROM posts").fetchall()
    ids = sorted({p[1] for p in posts})
    names = dict(db.execute(f"SELECT id, name FROM authors WHERE id IN ({','.join('?' * len(ids))})", ids))
    return [(t, names[a]) for _, a, t in posts]

for fn in (n_plus_one, joined, preload):
    queries.clear()
    result = fn()
    print(f"{fn.__name__:10} rows={len(result)} queries={len(queries)}")
# n_plus_one rows=200 queries=201
# joined     rows=200 queries=1
# preload    rows=200 queries=2

Same 200 rows: 201 queries with the naive loop, 1 with a JOIN, and 2 with preloading.

ORM Fixes

# Rails: N+1
Post.limit(100).each { |p| puts p.author.name }        # 1 + 100 queries
# Fixed
Post.includes(:author).limit(100).each { |p| puts p.author.name }   # 2 queries

The same fix in other ORMs: - Django: Post.objects.select_related("author") loads authors with a JOIN (1 query). Author.objects.prefetch_related("posts") loads posts with a second IN query (2 queries). - SQLAlchemy 2.x: select(Post).options(selectinload(Post.author)) uses 2 queries; joinedload(Post.author) uses 1 JOIN. - Hibernate/JPA: SELECT p FROM Post p JOIN FETCH p.author, or @BatchSize for batched lazy loading.

Choosing a Strategy

Strategy Queries Best when
JOIN (eager_load, select_related) 1 One-to-one or many-to-one; filtering on the association
Separate IN query (preload, prefetch_related, selectinload) 2 One-to-many; avoids duplicating parent rows
DataLoader batching 1 per type per tick GraphQL resolvers
SQL aggregate or counter cache 1 or 0 Counts, sums per parent

Pitfalls

  • Over-eager loading: preloading big associations you don’t use.

  • JOIN fan-out: joining several has-many associations multiplies rows (a Cartesian explosion).

  • N+1 hidden in views, serializers or templates rather than controllers.

  • Fixing in one place while another code path regresses: add tests that assert query counts.

Interview Tips

  • Explain the pattern (1 list query + N per-item queries) and why it hurts (round trips).

  • Show eager loading, JOINs and batching, with ORM-specific methods.

  • Mention detection tools and query-count tests.

14

Explain the function of GROUP BY and HAVING clauses in SQL.

GROUP BY collects rows that share the same values in one or more columns into groups, so that aggregate functions (COUNT, SUM, AVG, MIN, MAX, STRING_AGG) compute one result per group. For example, GROUP BY customer gives one row per customer with their order count and total.

HAVING filters groups after aggregation, using conditions on aggregates (HAVING SUM(total) >= 100). This is the key difference from WHERE, which filters individual rows before grouping and cannot use aggregates.

Read full answer

Logical order of evaluation: FROM/JOIN → WHERE (rows) → GROUP BY (form groups) → aggregates → HAVING (groups) → SELECT → DISTINCT → ORDER BY → LIMIT.

Rules: - every column in SELECT must be either in the GROUP BY or inside an aggregate. PostgreSQL allows functionally dependent columns when grouping by a primary key, and MySQL’s ONLY_FULL_GROUP_BY mode (on by default since 5.7) enforces this rule. - NULLs form one group. - Put row-level conditions in WHERE, not HAVING, so fewer rows are grouped, which is faster.

Applies to: standard SQL (PostgreSQL, MySQL, SQL Server, Oracle, SQLite; example uses Python 3.12)

Code Example: WHERE Versus HAVING

# GROUP BY forms groups; WHERE filters rows before grouping; HAVING filters groups after
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, status TEXT, total REAL);
INSERT INTO orders (customer, status, total) VALUES
  ('asha', 'paid', 120), ('asha', 'paid', 80), ('asha', 'refunded', 500),
  ('ravi', 'paid', 30),  ('ravi', 'paid', 25), ('meera', 'paid', 300);
""")

rows = db.execute("""
    SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
    FROM orders
    WHERE status = 'paid'              -- row filter (refund excluded before grouping)
    GROUP BY customer
    HAVING SUM(total) >= 100           -- group filter (uses an aggregate)
    ORDER BY spent DESC""").fetchall()
print(rows)   # [('meera', 1, 300.0), ('asha', 2, 200.0)]   <- ravi's 55 fails HAVING

Asha’s 500 refund is removed by WHERE before grouping. Ravi’s group (55) is removed by HAVING after aggregation.

More Examples

-- Monthly revenue per product category, only categories above 10,000
SELECT p.category,
       date_trunc('month', o.placed_at) AS month,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.status = 'paid'
GROUP BY p.category, date_trunc('month', o.placed_at)
HAVING SUM(oi.quantity * oi.unit_price) > 10000
ORDER BY month, revenue DESC;

-- Conditional aggregation: several counts in one pass
SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'paid')     AS paid,      -- PostgreSQL / SQLite
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded   -- portable
FROM orders
GROUP BY customer_id;

-- Subtotals and grand total
SELECT region, country, SUM(sales) FROM sales GROUP BY ROLLUP (region, country);

WHERE Versus HAVING

Aspect WHERE HAVING
Filters Rows Groups
When Before grouping After aggregation
Can use aggregates No Yes
Can use plain columns Yes Only grouped columns (or in aggregates)
Performance Reduces work early Applied to fewer, aggregated rows

Related Features

Feature Use
COUNT(DISTINCT col) Unique values per group
GROUPING SETS, ROLLUP, CUBE Multiple grouping levels in one query
Window functions (SUM() OVER (PARTITION BY ...)) Aggregates without collapsing rows
FILTER (WHERE ...) Conditional aggregates

Pitfalls

  • Selecting non-grouped columns (errors, or arbitrary values in permissive modes).

  • Using HAVING for row filters that belong in WHERE (slower, less clear).

  • COUNT(col) versus COUNT(*): the former skips NULLs.

  • Join fan-out inflating sums: aggregate before joining, or use COUNT(DISTINCT) carefully.

Interview Tips

  • Explain GROUP BY creates groups for aggregates, and HAVING filters groups.

  • State the logical processing order and the WHERE versus HAVING difference.

  • Give an example and mention window functions as the non-collapsing alternative.

15

What are indexes and how do they work in databases?

An index is a separate data structure that lets the database find rows quickly without scanning the whole table, much like a book’s index. It stores keys (the values of indexed columns) in a searchable order, with pointers to the rows: a row ID or tuple location, or the primary key in clustered designs.

How the common B-tree index works: - Keys are kept sorted in a balanced tree of pages. Each lookup walks from the root through internal pages to a leaf in O(log n) page reads, usually 3–4 levels even for millions of rows. - Leaves are linked, so range scans (BETWEEN, >, ORDER BY, prefix LIKE 'abc%') read consecutive entries. - The query planner uses statistics to decide whether an index is cheaper than a full scan. For low-selectivity filters that match a large share of rows, a scan is often better.

Read full answer

Other index types: - Hash indexes for equality only. - GIN (PostgreSQL) for arrays, jsonb and full-text search. - GiST and SP-GiST for geometric and range data. - BRIN for huge, naturally ordered tables (time-series). - Bitmap indexes in Oracle and data warehouses. - Full-text and spatial indexes.

Variants: - Composite (multi-column) indexes follow the leftmost-prefix rule. - Unique indexes enforce uniqueness. - Covering indexes (INCLUDE columns) allow index-only scans. - Partial indexes (WHERE active) and expression indexes (lower(email)). - Clustered indexes (InnoDB, SQL Server) store the table in index order.

Costs: every insert, update and delete must also update each index (write amplification), indexes take storage and memory, and unused or redundant ones only slow writes. Index for actual query patterns, verified with EXPLAIN.

Applies to: PostgreSQL, MySQL InnoDB, SQL Server, Oracle, SQLite (example uses SQLite via Python 3.12)

Code Example: Scan Versus Search, and Column Order

# How an index changes the plan and the work done: full scan vs B-tree search
import sqlite3, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, country TEXT, created INTEGER)")
db.executemany("INSERT INTO users (email, country, created) VALUES (?, ?, ?)",
               [(f"user{i}@example.com", ["IN", "US", "DE", "BR"][i % 4], i) for i in range(300_000)])

q = "SELECT id FROM users WHERE email = 'user123456@example.com'"
def plan(sql):
    return db.execute("EXPLAIN QUERY PLAN " + sql).fetchall()[0][-1]
def timed(sql, n=20):
    t = time.perf_counter()
    for _ in range(n):
        db.execute(sql).fetchall()
    return (time.perf_counter() - t) / n

print("before:", plan(q))              # before: SCAN users
before = timed(q)
db.execute("CREATE INDEX idx_users_email ON users(email)")
print("after: ", plan(q))              # after:  SEARCH users USING COVERING INDEX idx_users_email (email=?)
after = timed(q)
print("index lookup faster by >50x:", before / after > 50)   # True (exact ratio varies by machine)

# Composite index: column order matters (leftmost prefix rule)
db.execute("CREATE INDEX idx_country_created ON users(country, created)")
print(plan("SELECT id FROM users WHERE country = 'IN' AND created > 299000"))
# SEARCH users USING COVERING INDEX idx_country_created (country=? AND created>?)
print(plan("SELECT id FROM users WHERE created > 299000"))
# SCAN users USING COVERING INDEX idx_country_created   <- reads the whole index: no seek on 'created'

B-tree Lookup Sketch

[ m ]                         root page
          /                 \
   [ d | h ]               [ r | w ]           internal pages
   /   |   \               /   |    \
[a-c][d-g][h-l]        [m-q][r-v][w-z]         leaf pages (sorted keys -> row pointers), linked ->
lookup 'k': root -> [d|h] -> leaf [h-l] -> pointer -> row   (3 page reads instead of all pages)

PostgreSQL Examples

CREATE INDEX idx_orders_customer_date ON orders (customer_id, placed_at DESC);
CREATE INDEX idx_users_lower_email ON users (lower(email));                 -- expression
CREATE INDEX idx_orders_pending ON orders (placed_at) WHERE status = 'pending';  -- partial
CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (total);      -- covering
CREATE INDEX idx_docs_tags ON docs USING gin (tags);                         -- arrays / jsonb
CREATE INDEX CONCURRENTLY idx_events_time ON events (created_at);            -- no long write lock

EXPLAIN (ANALYZE, BUFFERS) SELECT total FROM orders WHERE customer_id = 42;

Index Types

Type Good for
B-tree Equality, ranges, sorting, prefix LIKE (default everywhere)
Hash Equality only
GIN Arrays, jsonb, full-text
GiST / SP-GiST Geometry, ranges, nearest neighbour
BRIN Very large tables ordered by time or ID
Bitmap Low-cardinality columns in warehouses (Oracle)

When an Index Won’t Help

Case Why
Function on the column (WHERE lower(email) = ...) Needs an expression index
Leading wildcard (LIKE '%abc') B-tree can’t seek; use trigram (pg_trgm) or full-text
Non-leading column of a composite index Leftmost-prefix rule
Low selectivity (status = 'active' for 95% of rows) Scan is cheaper
Type mismatch or implicit cast Index not usable

Pitfalls

  • Indexing every column: slow writes, wasted memory.

  • Duplicate or overlapping indexes ((a) and (a, b)).

  • Forgetting indexes on foreign keys.

  • Never checking EXPLAIN, or stale statistics (run ANALYZE).

Interview Tips

  • Define an index as a sorted lookup structure with row pointers, and explain the B-tree (O(log n), range scans).

  • Cover composite (leftmost prefix), unique, covering, partial and expression indexes.

  • Discuss write costs and EXPLAIN-driven indexing.

16

What impact do JOIN operations have on database performance?

Joins are the core of relational queries. Their cost depends on how much data must be matched, and how. Done well, they are fast even across millions of rows; done badly, they dominate query time. The main factors: - Join algorithm chosen by the optimiser: - Nested loop: for each outer row, look up matches. It is great when the outer side is small and the inner side has an index on the join key, but disastrous without an index (O(n×m)). - Hash join: build a hash table on the smaller input, then probe it. Good for large, unsorted, equality joins, but needs memory (work_mem) and spills to disk if it doesn’t fit. - Merge join: both inputs sorted on the key, then merged. Efficient when indexes already provide order. - Indexes on join columns: especially foreign key columns, which most databases don’t index automatically (InnoDB does). - Row counts and filtering: filter early (selective WHERE conditions) so fewer rows reach the join. The optimiser relies on statistics to estimate sizes. Bad estimates (stale stats, correlated columns) lead to wrong algorithms or join orders. - Number of joins: the optimiser’s search space grows quickly. PostgreSQL switches to a genetic optimiser past 12 tables (geqo_threshold), and plans may become suboptimal. - Fan-out and row multiplication: joining several one-to-many relations multiplies rows, which inflates work and aggregates. - Data types and expressions: joining on mismatched types or wrapped columns (ON lower(a.x) = b.y) prevents index use. - Distribution: in distributed or sharded databases, joins across nodes require shuffling data over the network, which is expensive. Co-locate related data, or denormalise.

Tune with EXPLAIN ANALYZE: add the right indexes, select only needed columns, filter early, keep statistics fresh, rewrite queries (EXISTS instead of joins for existence checks), and denormalise only where proven necessary.

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, Oracle, SQLite (example uses SQLite via Python 3.12)

Code Example: An Index on the Join Key

# Join cost depends on indexes on the join keys and on filtering early
import sqlite3, time

db = sqlite3.connect(":memory:")
db.execute("PRAGMA automatic_index = OFF")      # SQLite would otherwise build a temporary index
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
""")
db.executemany("INSERT INTO customers VALUES (?, ?)", [(i, "IN" if i % 10 == 0 else "US") for i in range(20_000)])
db.executemany("INSERT INTO orders (customer_id, total) VALUES (?, ?)", [(i % 20_000, 10.0) for i in range(200_000)])
db.execute("ANALYZE")

q = """SELECT c.id, SUM(o.total) FROM customers c JOIN orders o ON o.customer_id = c.id
       WHERE c.country = 'IN' AND c.id < 200 GROUP BY c.id"""
def plan():
    return [row[-1] for row in db.execute("EXPLAIN QUERY PLAN " + q)]
def timed():
    t = time.perf_counter(); db.execute(q).fetchall(); return time.perf_counter() - t

print(plan())    # ['SCAN o', ..., 'SEARCH c USING INTEGER PRIMARY KEY (rowid=?)', ...]
                 # all 200,000 orders are read, because orders.customer_id has no index
slow = timed()
db.execute("CREATE INDEX idx_orders_customer ON orders(customer_id)")
db.execute("ANALYZE")
print(plan())    # ['SEARCH c USING INTEGER PRIMARY KEY (rowid<?)',
                 #  'SEARCH o USING INDEX idx_orders_customer (customer_id=?)']
                 # filter customers first, then seek only their orders (plan text varies by version)
fast = timed()
print("indexed join faster by >20x:", slow / fast > 20)   # True (exact ratio varies by machine)

Join Algorithms

Algorithm Best for Cost driver
Nested loop (indexed inner) Small outer set, indexed join key Outer rows × index lookup
Nested loop (no index) Tiny tables only Outer × inner (quadratic)
Hash join Large, unsorted, equality joins Memory for hash table; spills to disk
Merge join Inputs already sorted (indexes) Sorting if not presorted

Reading a PostgreSQL Plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, SUM(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'IN'
GROUP BY c.name;
-- Look for: Seq Scan on large tables, "rows=" estimate vs actual far apart,
-- Hash ... Batches > 1 (spilled to disk), Nested Loop with high loops count

Optimisation Checklist

Problem Fix
Missing index on FK / join column CREATE INDEX ON orders (customer_id)
Estimates far from actual rows ANALYZE, extended statistics, query rewrite
Hash join spilling to disk Increase work_mem for the query, reduce columns
Selecting * across joins Select only needed columns (enables index-only scans)
Join used only to check existence WHERE EXISTS (SELECT 1 ...)
Row explosion from multiple one-to-many joins Aggregate in subqueries first, then join
Cross-shard joins Co-locate by shard key, or denormalise

Pitfalls

  • Blaming joins when the real issue is missing indexes or stale statistics.

  • Denormalising prematurely to avoid joins that would be cheap with indexes.

  • Functions on join keys, defeating indexes.

  • Huge result sets where the cost is in transferring rows, not joining.

Interview Tips

  • Explain nested loop, hash and merge joins, and when each is used.

  • Stress indexes on join keys, early filtering, statistics and EXPLAIN ANALYZE.

  • Mention fan-out and distributed join costs.

17

Define what a subquery is and provide a use case for it.

A subquery (inner or nested query) is a SELECT statement inside another SQL statement. The outer statement uses its result as: - a scalar value: one row and one column, such as WHERE total > (SELECT AVG(total) FROM orders); - a list or set for IN, ANY or ALL; - an existence test with EXISTS / NOT EXISTS; - a derived table (inline view) in FROM; - a computed column in SELECT.

Subqueries can appear in SELECT, INSERT, UPDATE and DELETE statements. A subquery is non-correlated when it runs independently of the outer query (evaluated once). It is correlated when it references outer columns, which conceptually means once per outer row.

Read full answer

Use cases: - Comparing against an aggregate: orders above the average, or products priced above their category’s average. - Existence checks: customers with no orders (NOT EXISTS), or users who have a verified email. - Aggregate-then-filter or join: compute per-customer totals in a derived table, then join to details. - Targeted updates and deletes: DELETE FROM sessions WHERE user_id IN (SELECT id FROM users WHERE banned). - Top-N per group and data-quality checks.

Modern optimisers often rewrite subqueries as joins (semi-joins and anti-joins). For readability with several steps, CTEs (WITH) are often clearer.

Applies to: standard SQL (PostgreSQL, MySQL, SQL Server, Oracle, SQLite; example uses Python 3.12)

Code Example: Three Kinds of Subquery

# Subqueries: in WHERE (IN / EXISTS), as a scalar value, and as a derived table in FROM
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
INSERT INTO orders (customer_id, total) VALUES (1, 120), (1, 80), (2, 30), (1, 300), (2, 45);
""")

# 1. Customers whose spend is above the average order value (scalar subquery)
print(db.execute("""SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id
                    WHERE o.total > (SELECT AVG(total) FROM orders)""").fetchall())
# [('Asha',)]        average order = 115

# 2. Customers who never ordered (NOT EXISTS: safe with NULLs, unlike NOT IN)
print(db.execute("""SELECT name FROM customers c
                    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)""").fetchall())
# [('Meera',)]

# 3. Derived table: aggregate first, then filter and join
print(db.execute("""SELECT c.name, t.spent FROM
                      (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) AS t
                    JOIN customers c ON c.id = t.customer_id
                    WHERE t.spent > 100""").fetchall())
# [('Asha', 500.0)]

Use Case in Detail: Customers Without Orders

-- Anti-join with NOT EXISTS (preferred)
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT IN looks similar but is dangerous if orders.customer_id contains NULL:
-- "x NOT IN (1, NULL)" is never true, so the query returns no rows at all
SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

Same Logic as a CTE

WITH spend AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders GROUP BY customer_id
)
SELECT c.name, s.spent
FROM spend s JOIN customers c ON c.id = s.customer_id
WHERE s.spent > 100;

Subquery Positions

Position Returns Example
WHERE x = (subquery) Scalar Above-average orders
WHERE x IN (subquery) List Orders from VIP customers
WHERE EXISTS (subquery) True/false Customers with any order
FROM (subquery) AS t Table Aggregate then filter
SELECT (subquery) AS col Scalar per row Latest order date per customer
UPDATE ... SET col = (subquery) Scalar Recalculate stored totals

Pitfalls

  • NOT IN with NULLs returning nothing: use NOT EXISTS.

  • Scalar subqueries returning more than one row, which causes a runtime error.

  • Correlated subqueries in SELECT running once per row on large tables; prefer joins or window functions.

  • Deeply nested subqueries that are hard to read; use CTEs.

Interview Tips

  • Define it as a query inside another query, and name the positions and return shapes.

  • Give a use case (above average, or customers without orders) with SQL.

  • Mention correlated versus non-correlated, the NOT IN / NULL trap, and CTEs and joins as alternatives.

18

What is a correlated subquery?

A correlated subquery is a subquery that references columns from the outer query, so its result depends on the current outer row. Conceptually, the database evaluates it once for each row the outer query processes. This contrasts with a non-correlated subquery, which is independent and can be evaluated once.

Example: “employees earning more than their own department’s average”. The inner AVG(salary) is filtered by x.dept = e.dept, where e is the outer row.

Read full answer

Typical uses: - per-row comparisons with group statistics (above the department average, the latest order per customer); - existence tests: EXISTS / NOT EXISTS subqueries are almost always correlated, as in “customers who have an order in the last 30 days”; - correlated updates: setting each row’s value from related rows.

Performance: naive per-row execution can be O(n × m). Modern optimisers often decorrelate them into semi-joins, anti-joins or hash aggregates, especially EXISTS. Correlated subqueries in the SELECT list or with aggregates are sometimes still executed per row. Alternatives that are often faster and clearer: - JOIN with a grouped derived table; - window functions (AVG(...) OVER (PARTITION BY dept)); - LATERAL joins for top-N per group.

Check with EXPLAIN, and make sure the correlated column is indexed.

Applies to: standard SQL (PostgreSQL, MySQL, SQL Server, Oracle, SQLite; example uses Python 3.12)

Code Example: Correlated Subquery and Window Rewrite

# Correlated subquery: the inner query references the current outer row
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees (name, dept, salary) VALUES
  ('Asha', 'eng', 150), ('Ravi', 'eng', 110), ('Kiran', 'eng', 130),
  ('Meera', 'sales', 90), ('Dev', 'sales', 70);
""")

correlated = """SELECT name, dept, salary FROM employees e
                WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.dept = e.dept)
                ORDER BY dept, name"""
print(db.execute(correlated).fetchall())
# [('Asha', 'eng', 150), ('Meera', 'sales', 90)]   eng avg = 130, sales avg = 80

# Equivalent rewrite with a window function: one pass instead of a subquery per row
window = """SELECT name, dept, salary FROM (
              SELECT *, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees)
            WHERE salary > dept_avg ORDER BY dept, name"""
print(db.execute(window).fetchall() == db.execute(correlated).fetchall())   # True

The subquery (SELECT AVG(salary) ... WHERE x.dept = e.dept) references e.dept from the outer query, which makes it correlated. The window version computes every department’s average in one pass.

More Examples

-- EXISTS (correlated): customers with a recent order
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id AND o.placed_at > now() - interval '30 days');

-- Latest order per customer: correlated subquery vs LATERAL (PostgreSQL)
SELECT c.name,
       (SELECT max(o.placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

SELECT c.name, lo.placed_at, lo.total
FROM customers c
CROSS JOIN LATERAL (SELECT placed_at, total FROM orders o
                    WHERE o.customer_id = c.id
                    ORDER BY placed_at DESC LIMIT 1) lo;

-- Correlated UPDATE
UPDATE customers c
SET order_count = (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id);

Correlated Versus Non-correlated

Aspect Non-correlated Correlated
References outer query No Yes
Evaluation (conceptually) Once Once per outer row
Can run on its own Yes No
Typical forms IN (SELECT ...), scalar aggregate EXISTS, per-group comparisons
Performance risk Low Higher unless decorrelated or indexed

Pitfalls

  • Slow per-row execution on large tables without an index on the correlated column (orders.customer_id).

  • Correlated scalar subqueries in SELECT that return multiple rows (error).

  • Hard-to-read nesting: consider window functions, joins or LATERAL.

Interview Tips

  • Define it as a subquery referencing the outer row, evaluated per row conceptually.

  • Give the department-average example, and mention EXISTS.

  • Discuss performance and rewrites: joins, window functions, LATERAL, indexes.

19

Describe how you would optimize a slow SQL query.

Optimise methodically: measure, find the expensive step, fix it, then measure again. 1. Reproduce and measure: capture the exact query with real parameter values, its frequency and latency. Use pg_stat_statements, the MySQL slow query log, Query Store in SQL Server, or APM traces. Focus on queries with the highest total time (frequency × duration), not only the slowest single run. 2. Read the execution plan: EXPLAIN ANALYZE (PostgreSQL), EXPLAIN ANALYZE / EXPLAIN FORMAT=TREE (MySQL 8), or actual execution plans in SQL Server. Look for: - sequential scans on big tables; - estimated versus actual row counts far apart; - expensive sorts or hashes spilling to disk; - nested loops with huge loop counts; - large buffer reads. 3. Fix the access path: - add or adjust indexes that match filters, joins and sort order: composite indexes in the right column order, covering indexes, partial and expression indexes; - drop unused ones that slow writes. 4. Rewrite the query: - make predicates sargable: no functions on indexed columns, no implicit casts, no leading % wildcards; - select only needed columns instead of SELECT *; - replace OFFSET pagination with keyset pagination; - use EXISTS for existence checks; - aggregate before joining; - avoid N+1 patterns in the application; - split one-size-fits-all OR conditions. 5. Fix statistics and settings: run ANALYZE, add extended statistics for correlated columns, tune memory (work_mem) for sorts and hashes, and check locks or blocking if the query is waiting rather than working. 6. Change data design if needed: partitioning for huge time-based tables, materialised views or summary tables, denormalised counters, caching, or read replicas for read-heavy load. 7. Verify and guard: compare before and after under realistic data volumes, watch the impact on writes, and add monitoring for plan regressions.

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, Oracle (example uses SQLite via Python 3.12)

Code Example: Sargable Predicates and Keyset Pagination

# Two classic fixes: make the predicate index-friendly (sargable) and use keyset pagination
import sqlite3, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER, created TEXT, kind TEXT)")
db.executemany("INSERT INTO events (user_id, created, kind) VALUES (?, ?, ?)",
               [(i % 5000, f"2026-{(i % 12) + 1:02d}-{(i % 28) + 1:02d}", "click") for i in range(400_000)])
db.execute("CREATE INDEX idx_events_created ON events(created)")

def run(sql, args=()):
    plan = db.execute("EXPLAIN QUERY PLAN " + sql, args).fetchall()[0][-1]
    t = time.perf_counter(); n = len(db.execute(sql, args).fetchall())
    return plan, n, time.perf_counter() - t

slow = run("SELECT id FROM events WHERE substr(created, 1, 7) = '2026-03'")    # function on column
fast = run("SELECT id FROM events WHERE created >= '2026-03-01' AND created < '2026-04-01'")
print(slow[0], slow[1])    # SCAN events USING COVERING INDEX idx_events_created 33334
                           # (reads every index entry and runs substr() on each)
print(fast[0], fast[1])    # SEARCH events USING COVERING INDEX idx_events_created (created>? AND created<?) 33334
print("sargable faster:", slow[2] > fast[2])        # True (timings vary by machine)

offset = run("SELECT id, kind FROM events ORDER BY id LIMIT 20 OFFSET 390000")
keyset = run("SELECT id, kind FROM events WHERE id > ? ORDER BY id LIMIT 20", (390000,))
print(offset[0])           # SCAN events  (reads and discards 390,000 rows)
print(keyset[0])           # SEARCH events USING INTEGER PRIMARY KEY (rowid>?)
print("keyset faster:", offset[2] > keyset[2])      # True

PostgreSQL Workflow

-- 1. Find the heaviest queries
SELECT query, calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 2. Inspect the plan with real execution data
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY placed_at DESC LIMIT 20;

-- 3. Add an index that matches filter + sort, without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_cust_status_date
    ON orders (customer_id, status, placed_at DESC) INCLUDE (total);

Common Problems and Fixes

Plan symptom Fix
Seq Scan on a large table with a selective filter Index on the filter columns
rows= estimate wildly off ANALYZE, extended statistics, rewrite
Sort with “external merge Disk” Index providing order, or more work_mem
Nested Loop with millions of loops Index the inner join key, or let hash join happen
Index not used: WHERE date(created) = ... Range predicate or expression index
Deep OFFSET Keyset pagination
Fast query, slow response Locks, network, row count transferred; check pg_stat_activity

Pitfalls

  • Optimising without a plan: guessing indexes.

  • Testing on tiny dev datasets where everything is fast.

  • Adding many indexes, so writes slow down.

  • Caching around a bad query instead of fixing it.

  • Ignoring parameter sensitivity: plans differ for common and rare values.

Interview Tips

  • Present a process: measure → EXPLAIN → index → rewrite → stats/config → design → verify.

  • Name concrete techniques (sargable predicates, composite and covering indexes, keyset pagination, EXISTS).

  • Mention pg_stat_statements and the slow query log for finding targets.

20

Explain the EXPLAIN statement and how you use it in query optimization.

EXPLAIN shows the execution plan the database’s query optimiser has chosen for a statement: how it will get the data. It reports which tables are scanned and how (sequential or full scan, index scan, index-only scan), the join algorithms and order (nested loop, hash, merge), sorts, aggregations and filters, plus estimated costs and row counts. Plain EXPLAIN doesn’t run the query.

EXPLAIN ANALYZE (PostgreSQL, and MySQL 8.0.18+) executes the query and adds actual times, actual rows and loop counts. With BUFFERS in PostgreSQL it also shows memory and disk page reads. Run it inside a transaction you roll back for INSERT, UPDATE or DELETE, because it really executes them. Other systems: SQL Server’s estimated and actual execution plans (SET STATISTICS PROFILE, SSMS graphical plans), Oracle’s EXPLAIN PLAN with DBMS_XPLAN, and SQLite’s EXPLAIN QUERY PLAN.

Read full answer

How to use it in optimisation: 1. Find the most expensive nodes: high actual time, or many rows processed. 2. Compare estimated and actual rows: big mismatches mean bad statistics or correlated predicates, which lead to wrong join orders or algorithms. Run ANALYZE or create extended statistics. 3. Spot scans that should be index lookups, then add or adjust indexes, or make predicates sargable. 4. Check sorts and hashes: “external merge Disk” or hash batches above 1 mean spills to disk. 5. Check join strategies and loop counts: a nested loop executed 1,000,000 times is a red flag. 6. Change one thing, re-run EXPLAIN ANALYZE, and compare.

Applies to: PostgreSQL, MySQL 8, SQL Server, Oracle, SQLite (example uses SQLite via Python 3.12)

Code Example: Plans Before and After an Index

# Reading query plans: SQLite's EXPLAIN QUERY PLAN as a small stand-in for PostgreSQL's EXPLAIN
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, total REAL);
CREATE INDEX idx_orders_customer ON orders(customer_id);
""")

def explain(sql):
    print(sql.split("FROM")[0].strip()[:40], "...")
    for row in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("    ", row[-1])

explain("SELECT * FROM customers WHERE email = 'a@example.com'")
#     SCAN customers                                   <- no index on email: full table scan
explain("""SELECT c.email, SUM(o.total) FROM customers c JOIN orders o ON o.customer_id = c.id
           WHERE c.country = 'IN' GROUP BY c.email ORDER BY 2 DESC""")
#     SCAN c / SEARCH o USING INDEX idx_orders_customer (customer_id=?)
#     USE TEMP B-TREE FOR GROUP BY / USE TEMP B-TREE FOR ORDER BY   <- sorts needed
db.execute("CREATE INDEX idx_customers_email ON customers(email)")
explain("SELECT * FROM customers WHERE email = 'a@example.com'")
#     SEARCH customers USING INDEX idx_customers_email (email=?)

Reading a PostgreSQL Plan

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 10;

Limit  (cost=0.43..12.10 rows=10) (actual time=0.031..0.058 rows=10 loops=1)
  -> Index Scan using idx_orders_cust_date on orders  (cost=0.43..1180.2 rows=1011)
                                                      (actual time=0.030..0.055 rows=10 loops=1)
       Index Cond: (customer_id = 42)
       Buffers: shared hit=13
Planning Time: 0.12 ms
Execution Time: 0.08 ms
  • cost=start..total is in arbitrary planner units, not milliseconds.

  • rows (estimated) is compared with actual ... rows.

  • loops multiplies a node’s time and rows.

  • Buffers: shared hit means pages came from cache; read means from disk.

Plan Node Cheat Sheet

Node Meaning Watch for
Seq Scan Reads the whole table Large tables with selective filters
Index Scan / Index Only Scan Uses index (only-scan avoids the table) Heap fetches high: vacuum, covering index
Bitmap Heap Scan Many matches gathered via index Normal for medium selectivity
Nested Loop Per outer row, look up inner High loops without inner index
Hash Join Hash smaller side Batches > 1 (memory spill)
Merge Join Merge sorted inputs Extra sorts
Sort Ordering “external merge Disk”
Aggregate / HashAggregate GROUP BY Disk usage, large groups

MySQL Example

EXPLAIN ANALYZE
SELECT c.email, COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'IN' GROUP BY c.email;
-- Classic EXPLAIN columns: type (ALL = full scan, ref/range = index), key, rows, Extra
-- ("Using filesort", "Using temporary" are hints of extra work)

Pitfalls

  • Trusting estimates alone: use ANALYZE variants to see reality.

  • Running EXPLAIN ANALYZE on writes outside a rolled-back transaction.

  • Testing with unrealistic parameters or data sizes, which produces different plans.

  • Over-reading cost numbers as time.

Interview Tips

  • Explain EXPLAIN (plan) versus EXPLAIN ANALYZE (plan plus actual execution).

  • Describe what to look for: scans, joins, estimate errors, sorts or spills, loops.

  • Show the iterate-and-compare workflow, with a plan snippet.

21

What is an Entity-Relationship (ER) model, and why is it useful?

An Entity-Relationship (ER) model, introduced by Peter Chen in 1976, is a conceptual model of the data in a domain. It describes: - entities: things worth storing, such as Customer, Order and Product; - attributes: properties of entities, such as name, email and price. They can be key, composite, multi-valued or derived attributes; - relationships: how entities are associated, such as a Customer places Orders. Relationships have cardinality (one-to-one, one-to-many, many-to-many) and participation (mandatory or optional); - weak entities: entities that depend on another for identity, such as an Order Line within an Order.

It is drawn as an ER diagram (ERD), using Chen notation (rectangles, diamonds, ovals) or more commonly crow’s foot notation (boxes with line endings showing cardinality). UML class diagrams are another alternative.

Read full answer

Why it is useful: - Shared understanding: a picture that business stakeholders and engineers can discuss before writing SQL. - Captures rules early: cardinalities and optionality (“each order belongs to exactly one customer”) become constraints later. - Basis for the schema: the model maps systematically to tables, keys and junction tables. - Finds problems before they are costly: missing entities, ambiguous relationships, many-to-many needs and redundancy. - Documentation and onboarding: a living map of the data, often reverse-engineered from existing databases by tools.

Applies to: database design (tools: dbdiagram.io, draw.io, Lucidchart, ERwin, MySQL Workbench, DBeaver, Mermaid)

Example: Online Shop in Crow’s Foot (Mermaid)

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_LINE : contains
    PRODUCT ||--o{ ORDER_LINE : "appears in"
    CUSTOMER {
        bigint id PK
        string email
        string name
    }
    ORDER {
        bigint id PK
        bigint customer_id FK
        datetime placed_at
        string status
    }
    ORDER_LINE {
        bigint order_id PK, FK
        bigint product_id PK, FK
        int quantity
        decimal unit_price
    }
    PRODUCT {
        bigint id PK
        string sku
        decimal price
    }

Reading it: a customer places zero or many orders (o{); an order contains one or many lines (|{); each line refers to exactly one product.

Core Concepts

Concept Meaning Example
Entity A thing with independent existence Customer
Weak entity Identified through its owner Order line (order + line number)
Attribute Property email, price
Key attribute Uniquely identifies an entity customer id, SKU
Multi-valued attribute Several values phone numbers (becomes a separate table)
Derived attribute Computable age from birth date, order total
Relationship Association Customer places Order
Cardinality How many on each side 1:1, 1:N, M:N
Participation Mandatory or optional Order must have a customer

Levels of Data Modelling

Level Focus Audience
Conceptual (ER) Entities and relationships, no technical detail Business + engineering
Logical Attributes, keys, normalised relations Data architects, developers
Physical Tables, types, indexes, partitions for a specific DBMS DBAs, developers

Pitfalls

  • Jumping straight to tables, missing business rules.

  • Wrong cardinality (assuming 1:N when it is really M:N, such as products in many categories).

  • Diagrams that drift from the real schema: generate them from the database or keep them in version control.

  • Modelling implementation details (indexes, surrogate keys) too early.

Interview Tips

  • Define entities, attributes, relationships, cardinality and participation.

  • Explain why: communication, capturing rules, systematic schema design, documentation.

  • Sketch a small ERD (customer–order–product) and mention notations.

22

Describe the process of converting an ER model into a relational database schema.

Converting (mapping) an ER model into relations follows standard rules, then refinement: 1. Strong entities → tables: each entity becomes a table. Simple attributes become columns with suitable types. Choose a primary key: the entity’s key attribute, or a surrogate ID plus a UNIQUE constraint on the natural key. 2. Composite attributes → separate columns: for example, address becomes street, city and postcode, or a separate address table if it is reused. 3. Multi-valued attributes → a new table: for example customer_phones(customer_id, phone), with a foreign key back to the owner and a composite primary key. 4. Derived attributes → usually not stored: compute them in queries or views, or store them deliberately, such as a cached total. 5. Weak entities → tables whose key includes the owner’s key: order_lines(order_id, line_no, ...), with ON DELETE CASCADE from the owner. 6. Relationships: - 1:N: put a foreign key on the N side (orders.customer_id → customers.id). Mandatory participation becomes NOT NULL. - 1:1: a foreign key with a UNIQUE constraint on one side (usually the optional or dependent side), or merge the tables if they are always used together. - M:N: create a junction (associative) table with two foreign keys and a composite primary key. Relationship attributes (enrolment grade, order line quantity) become its columns. - Recursive relationships: a self-referencing foreign key (employees.manager_id). - N-ary relationships: a table with foreign keys to all participants. 7. Specialisation and generalisation (inheritance): choose single table (one table plus a type column), class table (a parent table plus a child table per subtype, sharing the primary key) or concrete tables (one table per subtype). 8. Add constraints: NOT NULL, UNIQUE, CHECK and foreign key actions that encode business rules. 9. Normalise (check 3NF/BCNF), then add indexes for foreign keys and query patterns, and choose physical options (types, partitioning).

Read full answer

Applies torelational database design (example uses SQLite via Python 3.12)

Code Example: Mapping Rules Applied Programmatically

# Mapping an ER model to tables: entities -> tables, 1:N -> FK on the N side,
# M:N -> junction table, attributes on a relationship -> columns of the junction table
import sqlite3

entities = {
    "student": ["name TEXT NOT NULL", "email TEXT NOT NULL UNIQUE"],
    "course":  ["title TEXT NOT NULL", "credits INTEGER NOT NULL CHECK (credits > 0)"],
    "teacher": ["name TEXT NOT NULL"],
}
one_to_many = [("teacher", "course")]                       # a teacher teaches many courses
many_to_many = [("student", "course", ["grade TEXT"])]      # enrolment has its own attribute

ddl = []
for ent, attrs in entities.items():
    cols = [f"{ent}_id INTEGER PRIMARY KEY"] + attrs
    for one, many in one_to_many:
        if many == ent:
            cols.append(f"{one}_id INTEGER NOT NULL REFERENCES {one}({one}_id)")
    ddl.append(f"CREATE TABLE {ent} ({', '.join(cols)});")
for a, b, attrs in many_to_many:
    cols = [f"{a}_id INTEGER REFERENCES {a}({a}_id)", f"{b}_id INTEGER REFERENCES {b}({b}_id)"] + attrs
    ddl.append(f"CREATE TABLE {a}_{b} ({', '.join(cols)}, PRIMARY KEY ({a}_id, {b}_id));")

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
for stmt in ddl:          # SQLite checks FK targets at use time; PostgreSQL needs parents created first
    db.execute(stmt)
for (sql,) in db.execute("SELECT sql FROM sqlite_master WHERE type='table' ORDER BY name"):
    print(sql)
# CREATE TABLE course (course_id INTEGER PRIMARY KEY, title ..., teacher_id INTEGER NOT NULL REFERENCES teacher(teacher_id))
# CREATE TABLE student (student_id INTEGER PRIMARY KEY, name ..., email TEXT NOT NULL UNIQUE)
# CREATE TABLE student_course (student_id ..., course_id ..., grade TEXT, PRIMARY KEY (student_id, course_id))
# CREATE TABLE teacher (teacher_id INTEGER PRIMARY KEY, name TEXT NOT NULL)

The 1:N teacher–course relationship becomes course.teacher_id. The M:N student–course relationship becomes the student_course junction table, which carries the relationship’s own attribute, grade.

Mapping Rules Summary

ER construct Relational mapping
Strong entity Table with primary key
Weak entity Table with composite PK (owner key + partial key), FK with CASCADE
Composite attribute Several columns
Multi-valued attribute Separate table (owner FK + value)
Derived attribute Computed in queries or views (or cached deliberately)
1:1 relationship FK + UNIQUE, or merge tables
1:N relationship FK on the “many” side
M:N relationship Junction table with two FKs (+ relationship attributes)
Recursive relationship Self-referencing FK
Mandatory participation NOT NULL on the FK
Specialisation Single table, class table, or concrete tables

Inheritance Options (Payments: Card or Bank Transfer)

-- Class table inheritance: shared columns once, subtype columns in their own tables
CREATE TABLE payments (id BIGINT PRIMARY KEY, amount NUMERIC(10,2) NOT NULL,
                       kind TEXT NOT NULL CHECK (kind IN ('card', 'bank')));
CREATE TABLE card_payments (payment_id BIGINT PRIMARY KEY REFERENCES payments(id),
                            last4 CHAR(4) NOT NULL, network TEXT NOT NULL);
CREATE TABLE bank_payments (payment_id BIGINT PRIMARY KEY REFERENCES payments(id),
                            iban TEXT NOT NULL);
Strategy Pros Cons
Single table + type column Simple queries, no joins Many NULL columns; weaker constraints
Class table Normalised, clear constraints Joins for full records
Concrete tables No joins per subtype Queries across all subtypes need UNION

Pitfalls

  • Storing M:N as comma-separated IDs instead of a junction table.

  • Losing participation rules (forgetting NOT NULL or UNIQUE).

  • Putting relationship attributes on the wrong table (grade on student).

  • Skipping indexes on foreign keys after mapping.

Interview Tips

  • Walk through entities → tables, attributes → columns, relationships → FKs or junction tables.

  • Cover weak entities, multi-valued attributes, 1:1 options and inheritance strategies.

  • Finish with constraints, normalisation and indexing.

23

How do you design a scalable database schema for a high-traffic application?

A scalable schema starts from access patterns and growth, not only from entities. Key practices: - Know the workload: the top queries by frequency and latency target, the read/write ratio, data growth, hot entities, and future sharding keys. Design so the critical paths are single-index lookups or short range scans. - Normalise first, denormalise deliberately: keep a clean 3NF core for integrity. Add counter caches, summary tables, materialised views or JSON snapshots only for proven hot read paths, with a clear sync mechanism. - Keys and data types: - compact primary keys (BIGINT, or time-ordered UUIDv7 or ULID if IDs must be generated across services or shards); - avoid random UUIDv4 as clustered keys; - use the smallest correct types (timestamptz, numeric for money, enums or lookup tables for statuses). - Index for the queries: composite indexes in filter → sort order, covering indexes for hot reads, partial indexes for hot subsets (WHERE status = 'pending'), indexes on foreign keys, and no unused indexes, since each one slows writes. - Plan for large tables: - partition time-series and log-like tables by time (or by tenant), so queries prune partitions and retention is a cheap DROP/DETACH of old partitions; - archive cold data; - separate hot and cold columns (large text or JSON into side tables). - Design for horizontal scale: - choose a shard or partition key early (tenant_id, user_id); - include it in primary keys, unique constraints and most queries; - co-locate related data on the same shard; - avoid cross-shard joins and global sequences. - Avoid contention: - no single hot rows (global counters), so use sharded counters or async aggregation; - append-only event tables; - short transactions; - optimistic locking with version columns. - Keep queries efficient: keyset pagination, bounded result sets, and no SELECT * on wide tables. - Plan schema evolution: online, backward-compatible migrations (expand → migrate → contract), CREATE INDEX CONCURRENTLY, and no long table locks. - Support the surrounding architecture: read replicas, caching (Redis), connection pooling, a search engine for full-text search, and an OLAP store for analytics, so the OLTP schema stays lean.

Read full answer

Applies toPostgreSQL, MySQL, distributed SQL (Citus, CockroachDB, Vitess), cloud databases

Example: Multi-tenant Orders Schema (PostgreSQL)

CREATE TABLE orders (
    tenant_id   BIGINT      NOT NULL,                 -- shard / partition key
    id          BIGINT      NOT NULL,                 -- generated per tenant or Snowflake-style
    customer_id BIGINT      NOT NULL,
    status      SMALLINT    NOT NULL,                 -- compact enum code
    total_cents BIGINT      NOT NULL CHECK (total_cents >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, created_at, id)          -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_11 PARTITION OF orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE INDEX ON orders (tenant_id, customer_id, created_at DESC);          -- "my orders" page
CREATE INDEX ON orders (tenant_id, created_at) WHERE status = 1;           -- pending queue

-- Hot-path counter kept separately to avoid row contention on a global row
CREATE TABLE tenant_order_counts (tenant_id BIGINT, bucket SMALLINT, n BIGINT NOT NULL DEFAULT 0,
                                  PRIMARY KEY (tenant_id, bucket));       -- sum buckets to read

Design Checklist

Area Scalable choice
Access paths Every hot query hits an index; keyset pagination
Keys BIGINT or time-ordered IDs; shard key in PKs and uniques
Large tables Time partitioning, retention via partition drop
Hot rows Bucketed counters, async aggregation, append-only logs
Reads Replicas, caches, covering indexes, denormalised read models
Writes Short transactions, batching, minimal indexes
Search and analytics Offload to Elasticsearch/OpenSearch, a warehouse (via CDC)
Migrations Expand–migrate–contract, concurrent index builds

Pitfalls

  • Designing from entities only, ignoring query patterns.

  • One giant JSON column for everything: no constraints, poor indexing.

  • Global sequences or counters becoming bottlenecks.

  • Retrofitting a shard key years later, which is painful.

  • Index sprawl slowing writes.

Interview Tips

  • Lead with access patterns, growth and read/write mix.

  • Cover indexes, keys, partitioning, sharding keys, denormalisation and contention.

  • Mention replicas, caching, CDC to search and analytics, and online migrations.

24

What is database sharding, and what are its benefits and drawbacks?

Sharding is horizontal partitioning across multiple database servers. The rows of a large table, or the whole dataset of a tenant or user, are split into shards by a shard key, and each shard lives on a separate node with its own CPU, memory and disk. A routing layer (application logic, a proxy such as Vitess or ProxySQL, or the database itself in Citus, MongoDB, Cassandra, CockroachDB or DynamoDB) sends each query to the right shard.

Sharding strategies: - Hash-based: hash(key) mod N or consistent hashing. Even distribution, but range queries hit all shards. - Range-based: key ranges (IDs 1–1M, or by date). Range scans are efficient, but there is a risk of hot shards, for example the newest range receiving every write. - Directory or lookup-based: a mapping table (tenant → shard). Flexible placement and moves, but the directory must be highly available. - Geographic or tenant-based: by region or customer, which helps data residency and isolates noisy neighbours.

Read full answer

Benefits: - Write and storage scalability beyond one machine. - Smaller per-shard datasets, so indexes fit in memory and maintenance (vacuum, backups) is faster. - Fault isolation: one shard’s outage affects only part of the users. - Locality and compliance: shards placed near users or in required regions.

Drawbacks: - Complexity: routing, operations, monitoring and backups for many nodes. - Cross-shard queries: joins and aggregates need scatter-gather and merging. - Cross-shard transactions: need two-phase commit or sagas, or must be avoided by design. - Global constraints: unique constraints across shards are hard. - Rebalancing and resharding: moving data when shards grow uneven or more are added. - Hot spots from a poor shard key (celebrity users, big tenants). - ID generation: needs globally unique IDs (Snowflake IDs, UUIDv7).

Exhaust simpler options first: indexing, vertical scaling, read replicas, caching, partitioning within one server, and archiving.

Applies to: Vitess, Citus, MongoDB, Cassandra, DynamoDB, CockroachDB, application-level sharding (example uses SQLite via Python 3.12)

Code Example: Hash Routing and Scatter-Gather

# Hash sharding across 4 databases: route by shard key; single-shard vs scatter-gather queries
import sqlite3, hashlib

SHARDS = [sqlite3.connect(":memory:") for _ in range(4)]
for db in SHARDS:
    db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL)")

def shard_for(user_id):                       # stable hash, not Python's randomised hash()
    h = int.from_bytes(hashlib.sha256(str(user_id).encode()).digest()[:8], "big")
    return SHARDS[h % len(SHARDS)]

oid = 0
for user in range(1, 1001):
    for _ in range(3):
        oid += 1
        shard_for(user).execute("INSERT INTO orders VALUES (?, ?, ?)", (oid, user, 10.0))

print("rows per shard:", [db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] for db in SHARDS])
# rows per shard: [726, 765, 741, 768]   <- roughly even

# Query by shard key: touches one shard
print(shard_for(42).execute("SELECT COUNT(*) FROM orders WHERE user_id = 42").fetchone()[0])   # 3

# Query without the shard key: must ask every shard and merge (scatter-gather)
total = sum(db.execute("SELECT SUM(total) FROM orders").fetchone()[0] for db in SHARDS)
print("revenue across shards:", total)       # 30000.0

Queries that include the shard key go to one shard. Queries without it must visit every shard, which is why the shard key should match the most common access pattern.

Choosing a Shard Key

Property Why it matters
High cardinality Many possible values to spread load
Even distribution Avoids hot shards (beware celebrity users, big tenants)
Present in most queries Keeps queries single-shard
Groups related data Joins and transactions stay on one shard (tenant_id, user_id)
Stable Changing a row’s key means moving it

Strategies Compared

Strategy Pros Cons
Hash Even spread Range queries hit all shards; resharding moves data (mitigate with consistent hashing or many virtual shards)
Range Efficient ranges, easy splits Hot ranges (time-based keys)
Directory Flexible moves, per-tenant placement Lookup service must scale and stay available
Geo / tenant Residency, isolation Uneven tenant sizes

Pitfalls

  • Sharding too early, taking on complexity before a single node is exhausted.

  • A shard key misaligned with queries, so most traffic is scatter-gather.

  • Using hash % N with a small fixed N, which makes adding shards very disruptive. Start with many logical shards mapped to fewer physical nodes.

  • Ignoring cross-shard consistency (reporting totals, unique emails).

Interview Tips

  • Define sharding as horizontal partitioning across servers by a shard key.

  • Compare hash, range, directory and geo strategies, and shard key criteria.

  • Balance benefits (scale, isolation) with drawbacks (cross-shard queries and transactions, rebalancing, complexity), and alternatives to try first.

25

Explain the term “data integrity” and how it’s enforced in databases.

Data integrity means data is accurate, consistent, valid and trustworthy throughout its lifecycle. It conforms to defined rules, relationships stay intact, and it isn’t corrupted or altered without authorisation. Databases enforce it at several levels.

Logical integrity (rules on the data): - Entity integrity: every row is uniquely identifiable. Primary keys are unique and non-null. - Referential integrity: relationships stay valid. Foreign keys ensure referenced rows exist, with actions such as CASCADE or RESTRICT on delete and update. - Domain integrity: values fit their column’s domain, through data types, NOT NULL, CHECK constraints, DEFAULTs, enums and domain types. - Uniqueness: UNIQUE constraints and indexes on natural keys (emails, SKUs). - User-defined or business integrity: complex rules through triggers, exclusion constraints (PostgreSQL: no overlapping bookings), stored procedures, or application validation.

Read full answer

Transactional integrity: ACID transactions keep multi-step changes all-or-nothing. Isolation and locking prevent concurrent updates from corrupting data (lost updates, write skew).

Physical integrity: write-ahead logging, checksums on data pages (PostgreSQL data_checksums, InnoDB page checksums), replication, backups and crash recovery protect against hardware faults and corruption.

Security and process controls: access control (least-privilege roles, row-level security), audit logs, and validated, reviewed schema migrations prevent unauthorised or accidental changes.

Best practice is defence in depth: validate in the application for good error messages, but enforce the rules in the database, because every client, script and future service goes through it.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, SQLite (example uses SQLite via Python 3.12)

Code Example: The Database Rejecting Invalid Data

# Integrity rules enforced by the database itself: every insert below violates one of them
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE employees (
    id      INTEGER PRIMARY KEY,                                   -- entity integrity
    email   TEXT NOT NULL UNIQUE,                                  -- uniqueness
    salary  INTEGER NOT NULL CHECK (salary > 0),                   -- domain integrity
    dept_id INTEGER NOT NULL REFERENCES departments(id)            -- referential integrity
) STRICT;                                                          -- strict column types
INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO employees VALUES (1, 'asha@example.com', 120000, 1);
""")

bad = [
    ("duplicate key",    "INSERT INTO employees VALUES (1, 'x@example.com', 1, 1)"),
    ("duplicate email",  "INSERT INTO employees VALUES (2, 'asha@example.com', 1, 1)"),
    ("missing value",    "INSERT INTO employees VALUES (3, NULL, 1, 1)"),
    ("invalid salary",   "INSERT INTO employees VALUES (4, 'b@example.com', -5, 1)"),
    ("wrong type",       "INSERT INTO employees VALUES (5, 'c@example.com', 'lots', 1)"),
    ("unknown dept",     "INSERT INTO employees VALUES (6, 'd@example.com', 1, 99)"),
]
for label, sql in bad:
    try:
        db.execute(sql)
    except sqlite3.IntegrityError as e:
        print(f"{label:16} -> {e}")
# duplicate key    -> UNIQUE constraint failed: employees.id
# duplicate email  -> UNIQUE constraint failed: employees.email
# missing value    -> NOT NULL constraint failed: employees.email
# invalid salary   -> CHECK constraint failed: salary > 0
# wrong type       -> cannot store TEXT value in INTEGER column employees.salary
# unknown dept     -> FOREIGN KEY constraint failed

STRICT tables (SQLite 3.37+) enforce declared column types. PostgreSQL, MySQL and SQL Server enforce types by default.

More Enforcement Tools (PostgreSQL)

-- Domain type reused across tables
CREATE DOMAIN email AS TEXT CHECK (VALUE ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$');

-- No overlapping room bookings (exclusion constraint)
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
    room_id INT NOT NULL,
    during  TSTZRANGE NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- Business rule spanning rows: block deleting the last admin
CREATE FUNCTION keep_one_admin() RETURNS trigger AS $$
BEGIN
  IF OLD.role = 'admin' AND (SELECT count(*) FROM users WHERE role = 'admin') = 1 THEN
    RAISE EXCEPTION 'cannot remove the last admin';
  END IF;
  RETURN OLD;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_keep_admin BEFORE DELETE ON users FOR EACH ROW EXECUTE FUNCTION keep_one_admin();

The trigger’s count check can still race under concurrent deletes at Read Committed. For strict guarantees, lock the rows (SELECT ... FOR UPDATE) or use Serializable isolation.

Integrity Types and Mechanisms

Type Mechanism
Entity Primary key
Referential Foreign keys with actions
Domain Types, NOT NULL, CHECK, domains, enums
Uniqueness UNIQUE constraints and indexes
Business rules Triggers, exclusion constraints, procedures, app logic
Transactional ACID, isolation levels, locks
Physical WAL, page checksums, replication, backups
Access Roles, privileges, row-level security, auditing

Pitfalls

  • Validation only in the application: other clients, scripts and imports bypass it.

  • Disabled foreign keys for “performance” or convenience (SQLite’s default, bulk loads left disabled).

  • Free-text statuses without CHECK constraints or enums.

  • Never testing backups, so physical integrity is assumed rather than proven.

Interview Tips

  • Define data integrity, and list entity, referential, domain and user-defined integrity.

  • Explain enforcement: constraints, transactions, triggers, checksums, access control.

  • Stress enforcing rules in the database, not only in application code.

26

Explain the concept of transaction isolation levels.

Isolation levels define how much concurrent transactions can see of each other’s uncommitted or newly committed changes. They trade consistency for concurrency and performance. The SQL standard defines four levels by the read anomalies they prevent: - Dirty read: reading another transaction’s uncommitted changes, which may be rolled back. - Non-repeatable read: re-reading a row inside one transaction and getting a different value, because another transaction committed an update in between. - Phantom read: re-running a query and getting different rows, because another transaction inserted or deleted matching rows.

Beyond the standard there are also lost updates and write skew: two transactions read overlapping data and write different rows, together breaking an invariant.

Read full answer

The levels: 1. Read Uncommitted: allows dirty reads. Rarely used; PostgreSQL treats it as Read Committed. 2. Read Committed: each statement sees only committed data as of when that statement started. Non-repeatable reads and phantoms are possible. The default in PostgreSQL, Oracle and SQL Server. 3. Repeatable Read: rows read once won’t change within the transaction. MySQL InnoDB’s default, using a consistent snapshot. In PostgreSQL it is snapshot isolation: phantoms are prevented too, but write skew is still possible. 4. Serializable: the outcome is equivalent to running transactions one at a time. It prevents all these anomalies. Implemented with strict locking (range or predicate locks), or Serializable Snapshot Isolation in PostgreSQL, which aborts conflicting transactions. Applications must retry on serialization failures.

Implementation approaches are locking (shared and exclusive locks held until commit) and MVCC (each transaction reads a snapshot, so readers don’t block writers). Choose the lowest level that keeps your invariants safe, and protect specific critical sections with explicit locks (SELECT ... FOR UPDATE) or atomic updates.

Applies to: PostgreSQL, MySQL InnoDB, SQL Server, Oracle, SQLite (example uses SQLite WAL via Python 3.12)

Code Example: A Snapshot in Action

# Snapshot isolation in practice: a reader inside a transaction keeps seeing its snapshot
# (no non-repeatable read) while another connection commits. SQLite in WAL mode.
import sqlite3, tempfile, os

path = os.path.join(tempfile.mkdtemp(), "demo.db")
writer = sqlite3.connect(path, isolation_level=None)
writer.execute("PRAGMA journal_mode=WAL")
writer.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER)")
writer.execute("INSERT INTO stock VALUES ('KB-1', 10)")

reader = sqlite3.connect(path, isolation_level=None)
reader.execute("BEGIN")
print("reader sees:", reader.execute("SELECT qty FROM stock").fetchone()[0])     # 10 (snapshot starts)

writer.execute("BEGIN")
writer.execute("UPDATE stock SET qty = 3 WHERE sku = 'KB-1'")
print("before commit:", reader.execute("SELECT qty FROM stock").fetchone()[0])   # 10 (no dirty read)
writer.execute("COMMIT")
print("after commit: ", reader.execute("SELECT qty FROM stock").fetchone()[0])   # 10 (repeatable read)
reader.execute("COMMIT")
print("new txn:      ", reader.execute("SELECT qty FROM stock").fetchone()[0])   # 3

The reader’s transaction never sees uncommitted data, and keeps its snapshot even after the writer commits. A new transaction sees the new value.

Anomalies by Level (SQL Standard)

Level Dirty read Non-repeatable read Phantom
Read Uncommitted Possible Possible Possible
Read Committed Prevented Possible Possible
Repeatable Read Prevented Prevented Possible
Serializable Prevented Prevented Prevented

Real databases differ: PostgreSQL Repeatable Read also prevents phantoms, and SQL Server additionally offers SNAPSHOT and READ_COMMITTED_SNAPSHOT modes.

Setting the Level

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- MySQL (next transaction in this session)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Write Skew Example (Allowed Under Snapshot Isolation)

Rule: at least one doctor must be on call. Alice and Bob are both on call.
T1 (Alice): SELECT count(*) WHERE on_call -> 2;  UPDATE doctors SET on_call=false WHERE name='Alice'
T2 (Bob):   SELECT count(*) WHERE on_call -> 2;  UPDATE doctors SET on_call=false WHERE name='Bob'
Both commit under Repeatable Read (snapshot) -> nobody on call.
Serializable aborts one of them; or lock the rows first with SELECT ... FOR UPDATE.

Choosing a Level

Situation Choice
Typical web requests Read Committed + atomic updates and constraints
Reports needing a consistent view Repeatable Read (snapshot) or read-only transactions
Invariants across rows (bookings, balances, on-call rules) Serializable with retries, or explicit locks
Read-heavy analytics Read Committed or snapshot on replicas

Pitfalls

  • Assuming defaults are equal across databases.

  • Read-modify-write in application code under Read Committed (lost updates).

  • Serializable without retry logic.

  • Long transactions holding snapshots and locks, causing bloat and contention.

Interview Tips

  • Define the anomalies, then the four levels, in a table.

  • Explain locking versus MVCC snapshots, and the database-specific defaults.

  • Mention write skew, retries at Serializable and explicit locking.

27

What is a deadlock in databases, and how can it be resolved?

A deadlock occurs when two or more transactions wait for each other’s locks in a cycle, so none can proceed. Example: T1 locks row A and wants row B, while T2 locks row B and wants row A. Each holds what the other needs.

How databases resolve it: they detect deadlocks automatically. A detector builds a wait-for graph (transaction → transaction it is waiting on) and looks for cycles. PostgreSQL checks after deadlock_timeout (default 1 s); InnoDB detects immediately by default. The database then aborts one transaction (the victim), usually the cheapest to roll back. Its locks are released, the others continue, and the victim’s client receives an error: - PostgreSQL SQLSTATE 40P01; - MySQL error 1213 (ER_LOCK_DEADLOCK); - SQL Server error 1205.

Read full answer

The application must catch this and retry the whole transaction.

How to prevent or reduce deadlocks: - Consistent lock ordering: always touch rows and tables in the same order, such as sorting IDs before updating several accounts. - Short transactions: no user think-time or external API calls inside a transaction, and commit quickly. - Lock what you need up front: SELECT ... FOR UPDATE on all needed rows, in order, at the start. - Use indexes: without one, an UPDATE ... WHERE may scan and lock many rows (or gap ranges in InnoDB), increasing overlap. - Lower lock footprint: smaller batches, appropriate isolation levels (InnoDB gap locks under Repeatable Read cause many deadlocks), and avoiding lock escalation. - Lock timeouts (lock_timeout, innodb_lock_wait_timeout) to bound waiting. - Retry with backoff as a standard pattern for deadlocks and serialization failures.

Applies to: PostgreSQL, MySQL InnoDB, SQL Server, Oracle (Python 3.12 simulation of detection)

Code Example: Wait-for Graph Detection

# Deadlock detection the way databases do it: build a wait-for graph and look for a cycle,
# then abort a victim so the others can proceed
locks = {"row:A": "T1", "row:B": "T2", "row:C": "T3"}          # current lock holders
waits = {"T1": "row:B", "T2": "row:A", "T3": "row:A"}          # T1 waits for B, T2 for A, T3 for A

def wait_for_graph():
    return {t: locks[r] for t, r in waits.items() if r in locks}   # T waits for holder of r

def find_cycle(graph):
    for start in graph:
        seen, node = [], start
        while node in graph and node not in seen:
            seen.append(node)
            node = graph[node]
        if node in seen:
            return seen[seen.index(node):]
    return None

g = wait_for_graph()
print("wait-for:", g)              # {'T1': 'T2', 'T2': 'T1', 'T3': 'T1'}
cycle = find_cycle(g)
print("deadlock:", cycle)          # ['T1', 'T2']   (T3 is blocked, but not part of the cycle)

victim = min(cycle)                # real systems pick the cheapest to roll back
print("abort", victim)             # abort T1
for r, holder in list(locks.items()):
    if holder == victim:
        del locks[r]               # rollback releases its locks
waits.pop(victim)
locks["row:A"] = "T2"; waits.pop("T2")    # T2 gets row A and can finish
print("after:", find_cycle(wait_for_graph()))   # None (T3 now simply waits for T2)

Classic Deadlock and the Fix

-- Session 1                                   -- Session 2
BEGIN;                                         BEGIN;
UPDATE accounts SET bal = bal - 10 WHERE id = 1;
                                               UPDATE accounts SET bal = bal - 5 WHERE id = 2;
UPDATE accounts SET bal = bal + 10 WHERE id = 2;   -- waits for session 2
                                               UPDATE accounts SET bal = bal + 5 WHERE id = 1;  -- deadlock!
-- One session gets: ERROR: deadlock detected (PostgreSQL 40P01)

-- Fix: lock both rows in a consistent order first
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET bal = bal - 10 WHERE id = 1;
UPDATE accounts SET bal = bal + 10 WHERE id = 2;
COMMIT;

Retry Pattern

import random, time

class DeadlockError(Exception):
    pass

attempts = {"n": 0}
def transfer():                       # stand-in for a real transaction
    attempts["n"] += 1
    if attempts["n"] < 3:
        raise DeadlockError("deadlock detected")
    return "committed"

def with_retry(fn, tries=5):
    for i in range(tries):
        try:
            return fn()
        except DeadlockError:
            time.sleep(random.uniform(0, 0.01 * 2 ** i))   # jittered backoff (keep it short here)
    raise RuntimeError("gave up after retries")

print(with_retry(transfer), "after", attempts["n"], "attempts")   # committed after 3 attempts

Diagnosing

Database Where to look
PostgreSQL Server log “deadlock detected” with both queries; log_lock_waits = on; pg_locks
MySQL InnoDB SHOW ENGINE INNODB STATUS (LATEST DETECTED DEADLOCK); innodb_print_all_deadlocks
SQL Server system_health Extended Events deadlock graph

Pitfalls

  • No retry logic, so users see errors for a normal, recoverable event.

  • Inconsistent update order across code paths.

  • Long transactions with network calls inside.

  • Missing indexes turning row locks into broad locks.

Interview Tips

  • Define a deadlock as a cycle of lock waits.

  • Explain detection (wait-for graph) and victim rollback, plus retrying.

  • Give prevention: lock ordering, short transactions, indexes, timeouts.

28

Describe optimistic vs. pessimistic locking.

Both strategies handle concurrent updates to the same data, but they assume different things about conflicts.

Pessimistic locking assumes conflicts are likely, so it locks data before working on it: - SELECT ... FOR UPDATE (exclusive row lock) or FOR SHARE, table locks, or advisory locks; - other transactions trying to modify, or lock, the same rows wait until the lock is released at commit or rollback; - it guarantees no one else changes the row in between; - but it reduces concurrency, can cause deadlocks and lock waits, and must not be held across user think-time.

Read full answer

It suits high-contention, short critical sections: inventory decrements during flash sales, financial balances, seat assignment, job queues (FOR UPDATE SKIP LOCKED).

Optimistic locking assumes conflicts are rare, so it doesn’t lock while reading. Instead it detects conflicts at write time, usually with a version number or timestamp column: 1. Read the row, including its version. 2. Update with WHERE id = ? AND version = ?, incrementing the version. 3. If 0 rows were updated, someone else changed the row first, which is a conflict: reload, merge or ask the user, then retry.

It gives high concurrency and no lock waits, and works across long user interactions (edit forms) and stateless HTTP (ETags with If-Match). The costs are retries or failures under heavy contention and conflict-handling code. It is built into ORMs: Rails lock_version, JPA @Version, Entity Framework concurrency tokens.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, ORMs (example uses SQLite via Python 3.12)

Code Example: Optimistic Locking With a Version Column

# Optimistic locking with a version column vs pessimistic "lock the row first"
import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE docs (id INTEGER PRIMARY KEY, body TEXT, version INTEGER NOT NULL)")
db.execute("INSERT INTO docs VALUES (1, 'v1 text', 1)")

def load():
    return db.execute("SELECT body, version FROM docs WHERE id = 1").fetchone()

def save(new_body, read_version):
    cur = db.execute("UPDATE docs SET body = ?, version = version + 1 WHERE id = 1 AND version = ?",
                     (new_body, read_version))
    return cur.rowcount == 1          # 0 rows updated means someone changed it since we read

alice_body, alice_v = load()          # both users read version 1
bob_body, bob_v = load()
print("alice saves:", save("alice edit", alice_v))     # True  -> version 2
print("bob saves:  ", save("bob edit", bob_v))         # False -> conflict detected, not overwritten
print(load())                                          # ('alice edit', 2)
body, v = load()                                       # bob reloads, merges, retries
print("bob retries:", save(body + " + bob edit", v))   # True
print(load())                                          # ('alice edit + bob edit', 3)

Bob’s stale write is rejected instead of silently overwriting Alice’s edit (a lost update). Bob reloads and retries.

Pessimistic Locking in SQL

BEGIN;
SELECT qty FROM stock WHERE sku = 'KB-1' FOR UPDATE;   -- others wait here
UPDATE stock SET qty = qty - 1 WHERE sku = 'KB-1';
COMMIT;                                                -- lock released

-- Work queue: each worker grabs different rows without waiting
SELECT id FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED;

-- Fail fast instead of waiting
SELECT * FROM seats WHERE id = 42 FOR UPDATE NOWAIT;

ORM Support

# Rails: optimistic (add an integer lock_version column)
product = Product.find(1)
product.update!(price: 10)        # raises ActiveRecord::StaleObjectError on conflict

# Rails: pessimistic
Product.transaction do
  p = Product.lock.find(1)        # SELECT ... FOR UPDATE
  p.update!(stock: p.stock - 1)
end

Comparison

Aspect Optimistic Pessimistic
Assumption Conflicts are rare Conflicts are likely
When checked At write (version compare) Before reading/modifying (lock)
Blocking None Others wait
Failure mode Conflict error, retry or merge Lock waits, deadlocks, timeouts
Long user edits Works well Not suitable (locks held too long)
High contention Many retries Better throughput and fairness
Implementation Version or timestamp column, ETags FOR UPDATE, advisory locks

Choosing

  • Optimistic for typical web CRUD, collaborative edits, APIs with ETags and low-contention rows.

  • Pessimistic for hot rows, money movement, inventory under burst load and queue processing.

  • Atomic SQL updates (SET qty = qty - 1 WHERE qty > 0) often avoid the need for either on simple counters.

Pitfalls

  • Optimistic without checking the update count: conflicts go undetected.

  • Timestamp versions with coarse precision, missing fast concurrent updates.

  • Pessimistic locks held across network calls or user input.

  • Inconsistent lock order, causing deadlocks.

Interview Tips

  • Contrast detect-on-write (version) with lock-before-use (FOR UPDATE).

  • Give use cases and trade-offs (contention, user think-time).

  • Mention ORM support, SKIP LOCKED and ETags.

29

How does a database ensure consistency during concurrent transactions?

A database keeps data consistent under concurrency with a combination of concurrency control, constraints and logging: - Transactions (ACID): each unit of work is atomic (all or nothing), and must leave the database in a state satisfying all constraints. - Concurrency control stops interleavings from producing anomalies: - Locking (two-phase locking, 2PL): transactions take shared locks to read and exclusive locks to write. Under strict 2PL they hold them until commit, so conflicting operations are serialised. Lock types range from row to page, table and predicate or gap locks (which prevent phantoms). - MVCC (multi-version concurrency control): writers create new row versions, and readers see a consistent snapshot as of their statement or transaction start. Readers don’t block writers, and writers don’t block readers. Write-write conflicts are still detected: one waits, or aborts with “could not serialize access”. Used by PostgreSQL, Oracle, MySQL InnoDB and SQL Server’s snapshot modes. - Serializable Snapshot Isolation (SSI) in PostgreSQL tracks read/write dependencies and aborts transactions that could produce non-serialisable results. - Optimistic concurrency control validates at commit time. - Isolation levels let applications choose how strictly these mechanisms apply (Read Committed through Serializable). - Constraints and atomic operations: UNIQUE, foreign keys, CHECK and exclusion constraints are checked under concurrency (unique index insertion is race-safe). Atomic statements such as UPDATE ... SET qty = qty - 1 WHERE qty > 0 avoid read-modify-write races. - Deadlock detection breaks lock cycles by aborting a victim. - Write-ahead logging (WAL) with commit ordering ensures committed changes are durable and recovery restores a consistent state after a crash.

Applications contribute too: short transactions, explicit locks (SELECT ... FOR UPDATE) or version columns for read-modify-write logic, and retrying serialisation failures and deadlocks.

Read full answer

Applies toPostgreSQL, MySQL InnoDB, SQL Server, Oracle, SQLite (example uses SQLite via Python 3.12)

Code Example: Lost Update and Two Fixes

# Lost update from read-modify-write in the application, and two fixes
import sqlite3, tempfile, os

path = os.path.join(tempfile.mkdtemp(), "shop.db")
a = sqlite3.connect(path, isolation_level=None)
b = sqlite3.connect(path, isolation_level=None, timeout=0)
a.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER)")
a.execute("INSERT INTO stock VALUES ('KB-1', 10)")

def qty(conn):
    return conn.execute("SELECT qty FROM stock WHERE sku = 'KB-1'").fetchone()[0]

# Two checkouts interleave: both read 10, both write 9
qa, qb = qty(a), qty(b)
a.execute("UPDATE stock SET qty = ? WHERE sku = 'KB-1'", (qa - 1,))
b.execute("UPDATE stock SET qty = ? WHERE sku = 'KB-1'", (qb - 1,))
print("read-modify-write:", qty(a))          # 9   <- one sale was lost

# Fix 1: let the database do the arithmetic atomically (with a guard)
a.execute("UPDATE stock SET qty = 10 WHERE sku = 'KB-1'")
for conn in (a, b):
    conn.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'KB-1' AND qty > 0")
print("atomic update:    ", qty(a))          # 8

# Fix 2: take the write lock before reading (BEGIN IMMEDIATE ~ SELECT ... FOR UPDATE)
a.execute("BEGIN IMMEDIATE")
current = qty(a)
try:
    b.execute("BEGIN IMMEDIATE")             # b must wait; with no busy timeout it fails at once
except sqlite3.OperationalError as e:
    print("second writer:    ", e)           # database is locked
a.execute("UPDATE stock SET qty = ? WHERE sku = 'KB-1'", (current - 1,))
a.execute("COMMIT")
print("after locked txn: ", qty(b))          # 7

Interleaved read-modify-write lost a sale. Letting the database do the arithmetic atomically, or taking the write lock before reading, keeps the count correct. SQLite’s BEGIN IMMEDIATE plays the role of SELECT ... FOR UPDATE here, at database rather than row granularity.

MVCC Sketch (PostgreSQL)

row versions for stock 'KB-1':
  (qty=10, xmin=100, xmax=205)     <- visible to snapshots taken before txn 205 committed
  (qty=9,  xmin=205, xmax=null)    <- visible to snapshots after txn 205 committed
T300 (snapshot before 205 commit) reads qty=10; T301 (after) reads qty=9.
Old versions are removed later by VACUUM.

Mechanisms Compared

Mechanism How it protects Cost
Two-phase locking Conflicting access waits Blocking, deadlocks
MVCC snapshots Readers see consistent versions without blocking Old versions, vacuum or purge
SSI / Serializable Aborts dangerous interleavings Retries needed
Constraints Enforce invariants at commit Check overhead
Atomic SQL updates No read-modify-write gap Limited to expressible logic
Explicit row locks Serialise critical sections Reduced concurrency

Pitfalls

  • Assuming transactions alone prevent lost updates at Read Committed.

  • Invariants spanning rows (write skew) without Serializable or locks.

  • Long-running transactions holding locks or snapshots (bloat, blocking).

  • Not retrying serialisation failures and deadlocks.

Interview Tips

  • Explain locking (2PL) and MVCC, and how isolation levels use them.

  • Show a lost update and fixes (atomic update, FOR UPDATE, version column).

  • Mention constraints, deadlock detection, WAL and application retries.

30

What is a savepoint in a database transaction?

A savepoint is a named marker inside a transaction that you can roll back to without aborting the whole transaction. Work done after the savepoint is undone, work done before it is kept, and the transaction stays open, so it can continue and commit.

Commands: - SAVEPOINT name: create a marker. - ROLLBACK TO SAVEPOINT name: undo changes made after the marker. The savepoint itself remains and can be reused. - RELEASE SAVEPOINT name: discard the marker, keeping the changes. They still commit or roll back with the outer transaction.

Read full answer

Savepoints work like nested transactions (subtransactions). An outer ROLLBACK still undoes everything, including released savepoints, and nothing is durable until the outer COMMIT.

Uses: - Partial error recovery: in a batch import, skip a bad row and keep the others in one transaction. In PostgreSQL this is essential, because after any error the transaction is aborted until you roll back (to a savepoint, or entirely). - Optional or tentative steps: try an operation and undo just that step if a later check fails. - Nested transaction emulation in frameworks: - Rails: transaction(requires_new: true); - Django: nested atomic() blocks; - Spring: Propagation.NESTED; - SQLAlchemy: begin_nested(). - Test isolation: test frameworks wrap each test in a transaction or savepoint and roll back afterwards.

Cost: each savepoint has overhead. In PostgreSQL, many subtransactions in one transaction (more than 64 active per backend) can cause subtransaction cache overflow and slowdowns, especially on replicas. Avoid one savepoint per row in very large batches; validate first or use INSERT ... ON CONFLICT instead.

Applies to: PostgreSQL, MySQL InnoDB, SQL Server (SAVE TRANSACTION), Oracle, SQLite (example uses Python 3.12)

Code Example: Skip Bad Rows, Keep the Rest

# Savepoints: roll back part of a transaction and keep the rest
import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE imports (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)")

rows = ["a@example.com", "b@example.com", "a@example.com", "c@example.com"]   # one duplicate
db.execute("BEGIN")
skipped = []
for email in rows:
    db.execute("SAVEPOINT row_sp")                     # mark a point inside the transaction
    try:
        db.execute("INSERT INTO imports (email) VALUES (?)", (email,))
        db.execute("RELEASE SAVEPOINT row_sp")         # keep this row's work
    except sqlite3.IntegrityError:
        db.execute("ROLLBACK TO SAVEPOINT row_sp")     # undo only this row
        db.execute("RELEASE SAVEPOINT row_sp")
        skipped.append(email)
db.execute("COMMIT")                                   # the good rows commit together

print(db.execute("SELECT email FROM imports ORDER BY id").fetchall())
# [('a@example.com',), ('b@example.com',), ('c@example.com',)]
print("skipped:", skipped)                             # skipped: ['a@example.com']

SQL Syntax by Database

-- PostgreSQL / MySQL / SQLite / Oracle
BEGIN;
INSERT INTO orders (id, total) VALUES (1, 50);
SAVEPOINT before_bonus;
UPDATE wallets SET balance = balance + 5 WHERE user_id = 7;
ROLLBACK TO SAVEPOINT before_bonus;     -- undo the bonus only
COMMIT;                                 -- order 1 is committed

-- SQL Server
BEGIN TRANSACTION;
INSERT INTO orders (id, total) VALUES (1, 50);
SAVE TRANSACTION before_bonus;
UPDATE wallets SET balance = balance + 5 WHERE user_id = 7;
ROLLBACK TRANSACTION before_bonus;
COMMIT TRANSACTION;

Framework Equivalents

Framework Savepoint API
Rails ActiveRecord ActiveRecord::Base.transaction(requires_new: true) { ... }
Django Nested with transaction.atomic():
SQLAlchemy session.begin_nested()
Spring @Transactional(propagation = Propagation.NESTED)
JDBC conn.setSavepoint("sp"), conn.rollback(sp)

Savepoint Versus Transaction

Aspect Transaction Savepoint
Scope Whole unit of work Point inside a transaction
Commit Makes changes durable Can’t commit on its own
Rollback Undoes everything Undoes changes after the marker
Nesting Usually not nestable Many, nested

Pitfalls

  • Expecting a released savepoint to persist after an outer rollback.

  • PostgreSQL aborted-transaction state: after an error you must roll back to a savepoint before running more statements.

  • Thousands of savepoints in one transaction, causing subtransaction overhead.

  • Confusing ORM “nested transactions” that don’t create savepoints by default (Rails needs requires_new: true).

Interview Tips

  • Define a savepoint as a marker for partial rollback within a transaction.

  • Show SAVEPOINT / ROLLBACK TO / RELEASE, and a batch-import use case.

  • Mention framework nesting, PostgreSQL error behaviour and overhead.

31

What is SQL injection, and how do you prevent it?

SQL injection (SQLi) is a vulnerability where untrusted input is inserted into an SQL statement as code rather than as data, letting an attacker change the query’s meaning. It happens when applications build SQL by string concatenation or interpolation. Consequences include: - authentication bypass (admin' --); - data theft (UNION SELECT of other tables, blind or time-based extraction); - data modification or deletion; - in some configurations, command execution on the database server (xp_cmdshell, COPY ... PROGRAM), and pivoting into the network.

It remains one of the most common and damaging web vulnerabilities, part of the OWASP Top 10 “Injection” category.

Read full answer

Prevention, in order of importance: 1. Parameterised queries / prepared statements everywhere: values are sent separately from the SQL text, so they can never alter its structure. ORMs and query builders use bind parameters by default, but avoid their raw SQL with interpolation (where("name = '#{x}'")). 2. Allowlist identifiers and keywords: table and column names, sort direction and operators can’t be parameters, so map user choices to fixed, known values. 3. Least-privilege database accounts: the app’s role gets only the needed SELECT/INSERT/UPDATE on its tables, with no DDL, superuser, file or OS command rights. Use separate roles for migrations. 4. Safe stored procedures: procedures that themselves build dynamic SQL by concatenation are still injectable. Use EXECUTE ... USING, sp_executesql with parameters, or format('%I', ...) for identifiers. 5. Input validation: types, lengths and formats. This is defence in depth, not the main fix. 6. Error handling: don’t show database errors to users, because they help attackers. 7. Detection and extra layers: a WAF, logging and alerting on SQL errors, SAST/DAST tools (Semgrep, CodeQL, Brakeman, sqlmap in authorised tests) and code review.

Applies to: all SQL databases and languages (example uses SQLite via Python 3.12)

Code Example: Login Bypass and Fix

# SQL injection: a login bypass with string-built SQL, and the parameterised fix
import sqlite3, hashlib

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, pw_hash TEXT)")
db.execute("INSERT INTO users (username, pw_hash) VALUES ('admin', ?)",
           (hashlib.sha256(b"correct horse").hexdigest(),))   # (demo only: use bcrypt/argon2 for real)

def login_vulnerable(username, password):
    h = hashlib.sha256(password.encode()).hexdigest()
    sql = f"SELECT id FROM users WHERE username = '{username}' AND pw_hash = '{h}'"
    return db.execute(sql).fetchone() is not None

def login_safe(username, password):
    h = hashlib.sha256(password.encode()).hexdigest()
    sql = "SELECT id FROM users WHERE username = ? AND pw_hash = ?"
    return db.execute(sql, (username, h)).fetchone() is not None

attack = "admin' --"                       # comments out the password check
print("vulnerable:", login_vulnerable(attack, "anything"))   # True   <- logged in as admin
print("safe:      ", login_safe(attack, "anything"))         # False
print("safe, real:", login_safe("admin", "correct horse"))   # True

The input admin' -- closes the string and comments out the password check: WHERE username = 'admin' --' AND pw_hash = .... With parameters, the whole input is just a username that doesn’t exist.

Safe Patterns in Other Stacks

# Rails
User.where(email: params[:email])                        # safe: bind parameter
User.where("created_at > ?", params[:since])             # safe: placeholder
User.where("email = '#{params[:email]}'")                # UNSAFE: interpolation
User.order(Arel.sql(params[:sort]))                      # UNSAFE: allowlist instead
-- PostgreSQL dynamic SQL inside a function: identifiers with %I, values with USING
EXECUTE format('SELECT count(*) FROM %I WHERE owner_id = $1', tbl) USING owner;
// JDBC
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, email);

Attack Types

Type How it works
In-band (error-based, UNION) Results or errors appear in the response
Blind boolean Infer data from true/false page differences
Blind time-based pg_sleep() / SLEEP() delays reveal bits
Out-of-band Database makes DNS or HTTP requests to the attacker
Second-order Stored input is later used unsafely in another query

Defence Layers

Layer Control
Code Parameterised queries, ORM bind parameters, identifier allowlists
Database Least-privilege roles, no dangerous functions, row-level security
Application Validation, generic error messages
Perimeter WAF rules (CRS SQLi rules)
Process SAST, DAST, code review, dependency updates

Pitfalls

  • Escaping by hand instead of parameterising.

  • Second-order injection: trusting data because it came from your own database.

  • LIKE, ORDER BY and IN (...) lists built by string concatenation.

  • Over-privileged app users (superuser) magnifying the damage.

Interview Tips

  • Define SQLi as input changing the query’s structure, with the ' OR 1=1 / admin' -- example.

  • Lead with parameterised queries, then allowlists and least privilege.

  • Mention blind and second-order variants and testing tools.

32

Explain the role of access control in database security.

Access control decides who can connect to the database and what they can do with which data. It is a core security layer: it limits the damage from compromised applications, stolen credentials, mistakes and insider threats. It has several parts: - Authentication (who are you?): passwords with SCRAM-SHA-256 (PostgreSQL), certificates or mTLS, IAM-based authentication (AWS RDS IAM auth, Azure Entra ID, Google Cloud IAM), Kerberos or LDAP. Network-level rules restrict where connections may come from: pg_hba.conf, security groups, private networks. - Authorisation (what may you do?): - Privileges on objects (SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE) granted with GRANT and removed with REVOKE. - Role-based access control (RBAC): privileges go to roles (readonly, app_writer, migrator, analyst), and users or services are members of roles. - Least privilege: each application or person gets only what they need. The app connects as a role that can’t drop tables or read other schemas; migrations run as a separate role; admins use separate accounts. - Fine-grained controls: - column-level privileges (hide salary or ssn); - row-level security (RLS) policies (tenants see only their rows); - views and security-definer functions that expose a safe subset; - dynamic data masking (SQL Server, Oracle, Snowflake). - Accounting and auditing: logging who did what (pgAudit, MySQL Enterprise Audit, SQL Server Audit, cloud audit logs), and regular access reviews. - Separation of duties: developers don’t have production write access by default. Break-glass access is time-limited and logged.

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, Oracle, cloud databases (RDS, Cloud SQL, Azure SQL)

Example: Role Design in PostgreSQL

-- Group roles (no login), privileges granted to them
CREATE ROLE app_read  NOLOGIN;
CREATE ROLE app_write NOLOGIN;
GRANT USAGE ON SCHEMA shop TO app_read, app_write;
GRANT SELECT ON ALL TABLES IN SCHEMA shop TO app_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA shop TO app_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA shop GRANT SELECT ON TABLES TO app_read;   -- future tables

-- Login roles for services and people
CREATE ROLE orders_service LOGIN PASSWORD '...' IN ROLE app_write;   -- from a secret store
CREATE ROLE analyst_maria  LOGIN IN ROLE app_read;

-- Column-level: analysts can't read personal data
REVOKE SELECT ON shop.customers FROM app_read;
GRANT SELECT (id, country, created_at) ON shop.customers TO app_read;

-- Nobody but owners can create objects in public
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Row-Level Security for Multi-tenancy

ALTER TABLE shop.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON shop.orders
    USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- The app sets the tenant per request/transaction:
SET LOCAL app.tenant_id = '42';
SELECT * FROM shop.orders;     -- only tenant 42's rows, even without a WHERE clause

Table owners and superusers bypass RLS unless FORCE ROW LEVEL SECURITY is set, so the app shouldn’t connect as the owner.

Access Control Layers

Layer Examples
Network Private subnets, security groups, pg_hba.conf, no public endpoints
Authentication SCRAM, certificates, IAM auth, MFA for humans, rotation
Object privileges GRANT/REVOKE on schemas, tables, functions
Roles RBAC groups; least privilege per service
Fine-grained Column privileges, RLS, views, masking
Audit pgAudit, audit logs, alerts, periodic reviews

Pitfalls

  • Apps connecting as superuser or table owner.

  • Shared accounts for many people, with no individual accountability.

  • Grants to PUBLIC and default privileges left open.

  • Stale access for people who left or services that were retired.

  • Credentials in code instead of a secrets manager.

Interview Tips

  • Cover authentication, authorisation (RBAC, least privilege), fine-grained controls and auditing.

  • Show GRANT/REVOKE and roles, plus row-level security for multi-tenant data.

  • Mention network restrictions, secrets management and access reviews.

33

What are the best practices for storing passwords in a database?

Never store passwords in plaintext or with reversible encryption. Store only the output of a slow, salted, adaptive password-hashing function, so that even if the database leaks, attackers must spend large amounts of compute on each password guess. Best practices (OWASP Password Storage Cheat Sheet): - Use a dedicated password hashing algorithm: - Argon2id (the first choice, memory-hard); - scrypt; - bcrypt (widely supported; note its 72-byte input limit); - PBKDF2-HMAC-SHA256 with a high iteration count, where FIPS compliance is required. - Never plain MD5, SHA-1 or SHA-256, which are designed to be fast and therefore cheap to brute-force on GPUs. - Use a unique random salt per password (16+ bytes from a CSPRNG). It defeats rainbow tables and makes identical passwords hash differently. Modern libraries generate and embed the salt automatically. - Tune the cost parameters so a hash takes roughly tens to a few hundred milliseconds on your servers. Store the algorithm and parameters with the hash, for example a PHC string like $argon2id$v=19$m=19456,t=2,p=1$.... - Upgrade hashes on login when parameters or algorithms improve: verify against the old hash, then rehash with the new settings. - Optional pepper: a secret key (kept in a secrets manager or HSM, not in the database), applied as an HMAC before hashing, or used to encrypt the hashes. This protects against database-only leaks. - Compare in constant time (libraries do this) and use generic error messages (“invalid email or password”). - Limit and protect: - rate-limit and lock out brute-force attempts; - support MFA; - check new passwords against breached-password lists (the k-anonymity range API of Have I Been Pwned); - follow NIST SP 800-63B: favour length (at least 8, ideally 15+ characters) over composition rules, and no forced periodic resets. - Protect the database itself: least-privilege access to the credentials table, encryption at rest, and never log passwords. - Better still, avoid storing passwords at all: SSO, OAuth/OIDC or passkeys (WebAuthn).

Read full answer

Applies toany application database (example uses Python 3.12 standard library `hashlib.scrypt`)

Code Example: Salted, Memory-Hard Hashing

# Password storage with a slow, salted, memory-hard KDF (scrypt from the standard library)
import hashlib, hmac, os, base64

N, R, P = 2**15, 8, 1                        # cost parameters (~32 MiB memory per hash)

def hash_password(password: str) -> str:
    salt = os.urandom(16)                    # unique random salt per password
    dk = hashlib.scrypt(password.encode(), salt=salt, n=N, r=R, p=P, maxmem=64 * 2**20, dklen=32)
    b64 = lambda b: base64.b64encode(b).decode()
    return f"scrypt${N}${R}${P}${b64(salt)}${b64(dk)}"       # store parameters with the hash

def verify_password(password: str, stored: str) -> bool:
    _, n, r, p, salt, dk = stored.split("$")
    test = hashlib.scrypt(password.encode(), salt=base64.b64decode(salt), n=int(n), r=int(r),
                          p=int(p), maxmem=64 * 2**20, dklen=32)
    return hmac.compare_digest(test, base64.b64decode(dk))   # constant-time comparison

h1 = hash_password("correct horse battery staple")
h2 = hash_password("correct horse battery staple")
print(h1[:30] + "...")                       # scrypt$32768$8$1$<salt>$<hash>
print("same password, different hashes:", h1 != h2)                      # True (salts differ)
print("verify ok:  ", verify_password("correct horse battery staple", h1))  # True
print("verify bad: ", verify_password("Tr0ub4dor&3", h1))                  # False

In production, use a maintained library: argon2-cffi or bcrypt (Python), has_secure_password with bcrypt (Rails), Spring Security’s Argon2PasswordEncoder or BCryptPasswordEncoder, or password_hash() (PHP).

Storage Schema

CREATE TABLE user_credentials (
    user_id       BIGINT PRIMARY KEY REFERENCES users(id),
    password_hash TEXT NOT NULL,           -- e.g. $argon2id$v=19$m=19456,t=2,p=1$salt$hash
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
REVOKE ALL ON user_credentials FROM app_read;   -- reporting roles can't read hashes

Algorithm Choices

Algorithm Status Notes
Argon2id Recommended Memory-hard; OWASP minimum m=19 MiB, t=2, p=1
scrypt Good Memory-hard; N=2^17, r=8, p=1 suggested
bcrypt Good, legacy-friendly Cost 10+; 72-byte input limit
PBKDF2-HMAC-SHA256 Acceptable (FIPS) 600,000+ iterations
SHA-256 / MD5 (even salted) Never Fast hashes: billions of guesses per second on GPUs
Reversible encryption Never Key compromise reveals every password

Pitfalls

  • Unsalted or fast hashes, which breached sites routinely suffer from.

  • A global salt shared by all users.

  • Hard-coded low cost factors never revisited.

  • Passwords in logs, error reports or analytics.

  • Rolling your own crypto instead of using vetted libraries.

Interview Tips

  • State slow, salted, adaptive hashing: Argon2id, scrypt, bcrypt or PBKDF2, never fast hashes or encryption.

  • Mention per-password salts, cost tuning, rehash on login, peppers and constant-time comparison.

  • Add rate limiting, MFA, breached-password checks and passkeys or SSO as alternatives.

34

How do you secure data transmission to and from a database?

Secure the connection between applications and the database at several layers: - Encrypt with TLS: enable TLS on the server (PostgreSQL ssl = on, MySQL require_secure_transport = ON, SQL Server “Force Encryption”) with TLS 1.2+ and strong ciphers, and require it for every connection. In PostgreSQL, use hostssl lines in pg_hba.conf; in MySQL, REQUIRE SSL on users. - Verify the server’s identity: clients must validate the certificate and hostname, which prevents man-in-the-middle attacks: - PostgreSQL sslmode=verify-full; - MySQL ssl-mode=VERIFY_IDENTITY; - JDBC SQL Server encrypt=true;trustServerCertificate=false.

Modes such as sslmode=require encrypt but don’t authenticate the server. Use the provider’s CA bundle (the RDS global bundle, Cloud SQL server CA). - Authenticate clients strongly: mutual TLS (client certificates), IAM token authentication (RDS IAM, Cloud SQL IAM, Entra ID), or SCRAM-SHA-256 passwords from a secrets manager, rotated regularly. - Restrict the network path: - databases in private subnets with no public IP; - security groups or firewalls allowing only app hosts; - VPC peering, PrivateLink or Private Service Connect; - VPN or bastion/SSM sessions for humans; - Cloud SQL Auth Proxy / RDS Proxy to handle TLS and IAM. - Encrypt internal hops too: replication streams, backups sent to storage, connection poolers (PgBouncer, ProxySQL) and change-data-capture pipelines all need TLS. Poolers may otherwise terminate TLS and re-send in plaintext. - Protect against tampering and leaks: no credentials in connection strings checked into code, and monitoring for unencrypted connections (pg_stat_ssl, MySQL Ssl_cipher).

Read full answer

Applies toPostgreSQL, MySQL, SQL Server, cloud databases (example uses Python 3.12 `ssl`)

Code Example: What “Verify the Server” Means in TLS Settings

# Client-side TLS settings a database driver should use: verify the server and pin modern TLS
import ssl

ctx = ssl.create_default_context()              # loads system CAs; verify-full behaviour
ctx.minimum_version = ssl.TLSVersion.TLSv1_2
print("verify mode:", ctx.verify_mode == ssl.CERT_REQUIRED)     # True: server cert must validate
print("hostname check:", ctx.check_hostname)                    # True: cert must match the host
print("min version:", ctx.minimum_version.name)                 # TLSv1_2

weak = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
weak.check_hostname = False
weak.verify_mode = ssl.CERT_NONE                # what sslmode=require does in libpq: encrypts,
print("weak verifies server:", weak.verify_mode != ssl.CERT_NONE)  # False   but doesn't authenticate

Server and Client Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file  = '/etc/postgresql/server.key'
ssl_min_protocol_version = 'TLSv1.2'

# pg_hba.conf: only TLS connections, SCRAM passwords, app subnet only
hostssl  shop  orders_service  10.0.1.0/24  scram-sha-256
hostnossl all  all             0.0.0.0/0    reject
# Client: encrypt AND verify the server certificate and hostname
psql "host=db.internal.example.com dbname=shop user=orders_service \
      sslmode=verify-full sslrootcert=/etc/ssl/certs/db-ca.pem"

# Check which sessions use TLS
psql -c "SELECT pid, ssl, version, cipher FROM pg_stat_ssl JOIN pg_stat_activity USING (pid);"
-- MySQL: force TLS for an account
ALTER USER 'orders_service'@'10.0.1.%' REQUIRE SSL;

libpq sslmode Values

Mode Encrypts Verifies CA Verifies hostname
disable No No No
prefer (default) If server offers No No
require Yes No (unless a root cert file is present) No
verify-ca Yes Yes No
verify-full Yes Yes Yes

Pitfalls

  • sslmode=prefer or require in production, which is vulnerable to MITM.

  • trustServerCertificate=true copied from tutorials.

  • Publicly reachable database endpoints protected only by passwords.

  • Plaintext between pooler and database, or in replication.

  • Expired server certificates after a CA rotation.

Interview Tips

  • Cover TLS with server verification, strong client authentication and network isolation.

  • Explain the difference between encryption-only and verify-full.

  • Mention internal hops (poolers, replication, backups) and secrets management.

35

Describe the use of encryption within databases for data at rest.

Encryption at rest protects stored data (data files, logs, backups, snapshots) so that someone who obtains the disks, files or backup media can’t read it without the keys. Layers, from broadest to most targeted: - Storage or volume encryption: the disk or volume is encrypted (AWS EBS and RDS storage encryption, Azure managed disks, GCP default encryption, LUKS or dm-crypt, BitLocker, self-encrypting drives). It is transparent to the database, with almost no overhead, but it only protects against physical or media theft. Anyone who can query the database, or log into the OS, sees plaintext. - Transparent Data Encryption (TDE): the database engine encrypts data files, logs and often backups at the page level, decrypting them in memory. Supported by SQL Server, Oracle (TDE tablespaces), MySQL InnoDB data-at-rest encryption, MariaDB, MongoDB Enterprise, and PostgreSQL via distributions such as EDB and Percona. It protects copied data files and backups, but is still transparent to SQL users. - Column- or field-level encryption: specific sensitive columns (national IDs, card data, health data) are encrypted: - in the database with functions (PostgreSQL pgcrypto, SQL Server symmetric keys); - or, better, in the application (client-side) before data reaches the database: AWS Encryption SDK, Google Tink, SQL Server Always Encrypted, MongoDB Client-Side Field Level Encryption and Queryable Encryption.

Then even DBAs and database compromises can’t see plaintext. The cost is limited querying: no range queries or sorting on ciphertext unless you use deterministic or queryable schemes, plus key handling in apps. - Backup encryption: encrypt backups and exports independently (pgBackRest repo1-cipher-type, encrypted object storage with KMS keys).

Read full answer

Key management is the critical part: - use envelope encryption: data keys encrypted by a master key held in a KMS or HSM (AWS KMS, Azure Key Vault, GCP KMS, HashiCorp Vault); - separate key custody from database administrators; - rotate keys; - audit key usage; - never store keys alongside the data.

Applies to: SQL Server, Oracle, MySQL, MongoDB, PostgreSQL, cloud managed databases

Envelope Encryption

plaintext row data --(AES-256-GCM with data key DEK)--> ciphertext   (stored in DB/disk)
DEK --(encrypted with key-encryption key KEK in KMS/HSM)--> wrapped DEK (stored next to data)
To read: ask KMS to unwrap the DEK (audited, access-controlled) -> decrypt data in memory
Rotate KEK: re-wrap DEKs only; no need to re-encrypt all data

Examples

-- SQL Server TDE
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TdeCert;
ALTER DATABASE Shop SET ENCRYPTION ON;

-- MySQL InnoDB (keyring component configured): encrypt a table's tablespace
ALTER TABLE customers ENCRYPTION = 'Y';

-- PostgreSQL pgcrypto (symmetric, key supplied by the app at query time)
INSERT INTO patients (id, ssn_enc) VALUES (1, pgp_sym_encrypt('123-45-6789', :key));
SELECT pgp_sym_decrypt(ssn_enc, :key) FROM patients WHERE id = 1;

With pgcrypto, the key travels in the query and may appear in logs or pg_stat_activity. Prefer application-side encryption for high-value fields.

# AWS: create an encrypted RDS instance with a customer-managed KMS key
aws rds create-db-instance --db-instance-identifier shop-db --engine postgres \
  --storage-encrypted --kms-key-id alias/shop-db-key ...

Layers Compared

Layer Protects against Doesn’t protect against Query impact
Disk / volume Stolen disks, decommissioned hardware DB users, OS-level attackers None
TDE Copied data files, backups, media theft SQL users, compromised app Minimal
Column (DB-side) Readers without key access DBAs with keys, keys in queries Limited on encrypted columns
Client-side field-level DB compromise, DBAs, cloud operator Compromised application Limited (deterministic or queryable schemes help)
Backup encryption Leaked backup files Live database access None

Pitfalls

  • Assuming encryption at rest stops SQL injection or stolen app credentials: it doesn’t.

  • Keys stored on the same server or in the same backup.

  • Unencrypted backups, exports, replicas or logs alongside an encrypted primary.

  • No key rotation or recovery plan: losing keys means losing the data.

  • Encrypting columns you must search, without planning for blind indexes or deterministic encryption.

Interview Tips

  • Distinguish disk, TDE, column-level and client-side encryption, and what each protects against.

  • Emphasise key management (KMS/HSM, envelope encryption, rotation, separation of duties).

  • Mention backups and replicas, and compliance (PCI DSS, HIPAA, GDPR).

36

What is a database snapshot, and when would you use one?

A database snapshot is a point-in-time copy (or view) of a database’s data, as it existed at a specific moment. The term covers a few related mechanisms: - Storage or volume snapshots: cloud or SAN snapshots of the disks: AWS RDS/EBS snapshots, Azure disk snapshots, GCP persistent disk snapshots, ZFS or LVM snapshots. They are usually copy-on-write or incremental, so taking one is fast, and they become backups when stored durably (S3-backed RDS snapshots). For crash consistency, the database must be snapshotted atomically, or flushed or frozen first. - Engine-level snapshots: SQL Server database snapshots (CREATE DATABASE ... AS SNAPSHOT OF) are read-only, copy-on-write views on the same server that can be used to revert a database. Oracle Flashback and PostgreSQL exported snapshots (pg_export_snapshot(), used by pg_dump for consistency) are similar ideas. - MVCC transaction snapshots: the consistent view each transaction reads under snapshot isolation. These are an internal concept, not a stored copy. - Cloud database clones: Aurora fast cloning and Neon branching create writable copies that share storage via copy-on-write.

When to use them: - Before risky changes: schema migrations, bulk updates, major version upgrades. Take a snapshot, run the change, and revert or copy back data if it goes wrong. - Backups and disaster recovery: scheduled automated snapshots, copied to another region or account for DR and ransomware protection. - Test, staging and analytics copies: restore or clone production snapshots (with masking of personal data) for realistic testing, debugging or heavy reporting without loading the primary. - Consistent exports: a snapshot gives pg_dump, ETL or CDC initial loads a consistent starting point. - Investigation and audits: look at data as of a moment (before a bug or incident).

Read full answer

Limits: snapshots on the same storage as the source are not a backup by themselves. Restores create new instances (RDS) that need cut-over. Copy-on-write snapshots can slow writes and grow over time. Snapshots alone don’t give point-in-time recovery between snapshots: combine them with WAL or binlog archiving.

Applies to: AWS RDS/Aurora, Azure, GCP, SQL Server, Oracle, PostgreSQL, ZFS/LVM (example uses SQLite via Python 3.12)

Code Example: Repair From a Point-in-Time Copy

# A consistent point-in-time copy taken while the source keeps changing (SQLite online backup API)
import sqlite3

src = sqlite3.connect(":memory:")
src.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL)")
src.executemany("INSERT INTO orders (total) VALUES (?)", [(10.0,)] * 1000)
src.commit()

snap = sqlite3.connect(":memory:")
src.backup(snap)                                     # copy all pages as of this moment

src.execute("DELETE FROM orders WHERE id <= 500")    # a bad change after the snapshot
src.commit()

count = lambda db: db.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
print("live:", count(src), " snapshot:", count(snap))    # live: 500  snapshot: 1000

# Recover just the lost rows from the snapshot instead of restoring everything
lost = snap.execute("SELECT id, total FROM orders WHERE id <= 500").fetchall()
src.executemany("INSERT INTO orders VALUES (?, ?)", lost)
src.commit()
print("after repair:", count(src))                   # after repair: 1000

Recovering only the affected rows from a snapshot is often better than restoring the whole database, which would also lose good changes made after the snapshot.

Commands

# AWS RDS: manual snapshot before a migration, then copy it to another region
aws rds create-db-snapshot --db-instance-identifier shop-db --db-snapshot-identifier shop-pre-migration
aws rds copy-db-snapshot --source-db-snapshot-identifier arn:aws:rds:eu-west-1:123456789012:snapshot:shop-pre-migration \
  --target-db-snapshot-identifier shop-pre-migration-dr --region eu-central-1

# Restore creates a NEW instance
aws rds restore-db-instance-from-db-snapshot --db-instance-identifier shop-restored \
  --db-snapshot-identifier shop-pre-migration
-- SQL Server: create a snapshot and revert to it
CREATE DATABASE Shop_snap ON (NAME = Shop_Data, FILENAME = 'D:\snap\Shop_snap.ss')
AS SNAPSHOT OF Shop;
RESTORE DATABASE Shop FROM DATABASE_SNAPSHOT = 'Shop_snap';

Snapshot Types

Type Writable Survives source storage loss Typical use
Cloud storage snapshot (RDS, EBS) Restore to new instance Yes (stored in S3-backed storage) Backups, DR, pre-change safety
SQL Server database snapshot No (read-only) No (same server) Quick revert, reporting
ZFS / LVM snapshot Clone can be No (same pool) unless sent elsewhere Fast local rollback
Aurora clone / Neon branch Yes Shares storage Testing, analytics
MVCC snapshot Internal Not applicable Transaction consistency

Pitfalls

  • Treating same-disk snapshots as backups.

  • Restoring over production instead of to a new instance, then copying back selectively.

  • Production personal data in test environments without masking.

  • Never testing snapshot restores, or not knowing how long they take (RTO).

Interview Tips

  • Define a snapshot as a point-in-time copy or view; distinguish storage, engine, clone and MVCC snapshots.

  • Give uses: pre-change safety, backups and DR, test copies, consistent exports.

  • Mention limits (same-storage risk, PITR needs logs, restore creates new instances).

37

Explain the difference between logical and physical backups.

Logical backups export the database’s contents as logical objects: SQL statements (CREATE TABLE, INSERT) or a structured archive of schema and rows. Tools: pg_dump/pg_dumpall, mysqldump, MySQL Shell dump utilities, mongodump, Oracle Data Pump (expdp), and SQL Server BACPAC. - Pros: portable across versions, platforms and sometimes engines; selective (one table, schema or database); human-readable or easily inspected; good for migrations, upgrades and long-term archives. Restores rebuild indexes, which removes bloat. - Cons: slow to create and especially to restore for large databases, since every row is re-inserted and every index rebuilt. They produce a single point in time with no continuous recovery, and dumping adds load on the source.

Physical backups copy the database’s actual data files and pages (plus the WAL or redo logs needed for consistency) at the storage level. Tools: pg_basebackup, pgBackRest, Barman, Percona XtraBackup, MySQL Enterprise Backup, Oracle RMAN, SQL Server BACKUP DATABASE, and volume or cloud snapshots. - Pros: fast backup and restore for large databases (file copies, parallel and incremental). They combine with archived WAL or binlogs for point-in-time recovery, and can seed replicas. - Cons: tied to the same database version (major), architecture and configuration; usually whole-cluster granularity (no single-table restore without extra steps); they copy bloat and corruption along with the data.

Read full answer

Most production setups use both: physical backups plus continuous log archiving for fast disaster recovery and PITR, and periodic logical dumps for portability, selective restores, upgrades and an extra safety net.

Applies to: PostgreSQL, MySQL, MongoDB, Oracle, SQL Server (example uses SQLite via Python 3.12)

Code Example: Both Kinds From the Same Database

# Logical backup (SQL statements) vs physical backup (exact copy of database pages)
import sqlite3, tempfile, os

d = tempfile.mkdtemp()
src = sqlite3.connect(os.path.join(d, "app.db"))
src.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
CREATE INDEX idx_users_email_prefix ON users(substr(email, 1, 3));
""")
src.executemany("INSERT INTO users (email) VALUES (?)", [(f"user{i}@example.com",) for i in range(5000)])
src.commit()

# Logical: portable SQL text (schema + INSERTs), like pg_dump / mysqldump
dump = "\n".join(src.iterdump())
print(dump.splitlines()[1][:45])           # CREATE TABLE users (id INTEGER PRIMARY KEY,
print("logical statements:", dump.count("INSERT INTO"))          # 5000

# Physical: page-level copy, like pg_basebackup / file system or volume snapshots
phys_path = os.path.join(d, "app-physical.db")
with sqlite3.connect(phys_path) as dst:
    src.backup(dst)
print("physical size equals source:",
      os.path.getsize(phys_path) == os.path.getsize(os.path.join(d, "app.db")))   # True

# Restore the logical dump into a fresh database (indexes are rebuilt during restore)
fresh = sqlite3.connect(":memory:")
fresh.executescript(dump)
print("restored rows:", fresh.execute("SELECT COUNT(*) FROM users").fetchone()[0])   # 5000

Commands

# PostgreSQL logical: custom format (compressed, parallel restore, selective)
pg_dump -Fc -d shop -f shop.dump
pg_restore -l shop.dump | head                    # list contents
pg_restore -d shop_new -j 4 --table=orders shop.dump

# PostgreSQL physical: base backup with WAL, ready for PITR or a replica
pg_basebackup -D /backups/base_$(date +%F) -Ft -z -X stream -P

# MySQL logical / physical
mysqldump --single-transaction --routines --triggers shop > shop.sql
xtrabackup --backup --target-dir=/backups/xb && xtrabackup --prepare --target-dir=/backups/xb

Comparison

Aspect Logical Physical
Content SQL or object-level data Data files and pages + logs
Speed (large DBs) Slow, especially restore Fast
Size Often smaller (no indexes, compressible) Full size (incrementals help)
Portability Across versions, platforms Same major version, platform
Granularity Table, schema, database Usually whole instance or cluster
PITR No Yes, with WAL or binlog archiving
Seeding replicas No Yes
Bloat and corruption Not carried over Copied as-is (checksums help detect)
Typical tools pg_dump, mysqldump, mongodump, expdp pgBackRest, XtraBackup, RMAN, snapshots

Choosing

Need Choice
Restore a 2 TB database within an hour Physical
Recover to 14:03 before a bad deploy Physical + log archive
Move one schema to another server or version Logical
Major version upgrade (without pg_upgrade) Logical
Long-term archive readable in 10 years Logical (plus documented format)

Pitfalls

  • Logical-only backups for big databases: restores take far longer than the RTO allows.

  • Physical backups without the WAL needed for consistency.

  • Never test-restoring either kind.

  • Dumps without --single-transaction (MySQL InnoDB), giving an inconsistent backup.

Interview Tips

  • Define logical (export of objects) versus physical (copy of files or pages).

  • Compare speed, portability, granularity and PITR, and name tools.

  • Recommend combining both, with regular restore tests.

38

How would you restore a database from a backup file?

Restoring is a procedure, not a single command. Do it calmly and verifiably: 1. Assess and decide: what was lost or corrupted, and the recovery target: the latest state, a specific time (PITR), or specific tables only. Check RPO and RTO expectations, and communicate with stakeholders. If the live database is still running, stop writes or put the app in maintenance mode to prevent further damage. 2. Pick and verify the backup: the right file, date and type (logical dump, physical base backup plus WAL, or snapshot). Verify checksums and integrity (pg_restore -l lists contents; pgBackRest verify). 3. Prepare a target: restore into a new database or instance, never over the only copy. The target needs a compatible version (physical backups need the same major version), enough disk space, and the right extensions, roles and tablespaces. 4. Restore: - Logical: pg_restore (-j for parallel jobs), psql -f, mysql < dump.sql, mongorestore. Disable or deprioritise non-critical load, and consider loading data before creating indexes; pg_restore orders this automatically. - Physical: - PostgreSQL: pgBackRest restore, or extract a base backup, configure restore_command and a recovery target, and start the server. - MySQL: XtraBackup --prepare, then --copy-back. - Cloud: restore a snapshot or PITR to a new instance. 5. Validate: compare row counts, checksums or aggregates with expectations, run integrity checks, check recent records exist (or are correctly absent), and run application smoke tests. 6. Switch over or merge: - Point the application at the restored database (DNS or connection string), or - for partial recovery, copy just the affected rows or tables back into production.

Then re-enable jobs, replication and backups for the new primary. 7. Post-restore: run ANALYZE to refresh statistics, confirm monitoring and backups are working, rotate credentials if there was a security incident, and write up timings and lessons.

Read full answer

Practise restores regularly, because untested backups are guesses.

Applies to: PostgreSQL, MySQL, MongoDB, SQL Server, cloud databases (example uses SQLite via Python 3.12)

Code Example: Restore Into a New Target and Verify Before Switching

# Restore drill: restore a dump into a NEW database, verify it, then switch over
import sqlite3, hashlib

prod = sqlite3.connect(":memory:")
prod.executescript("""
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT NOT NULL, balance INTEGER NOT NULL);
INSERT INTO accounts (owner, balance) VALUES ('asha', 500), ('ravi', 250), ('meera', 900);
""")
backup_sql = "\n".join(prod.iterdump())                  # the backup file's contents
checksum = hashlib.sha256(backup_sql.encode()).hexdigest()

def fingerprint(db):                                     # what we verify after restoring
    rows = db.execute("SELECT COUNT(*), SUM(balance) FROM accounts").fetchone()
    return rows, db.execute("PRAGMA integrity_check").fetchone()[0]

expected = fingerprint(prod)

# 1. verify the backup file is the one we took (detect truncation or corruption)
assert hashlib.sha256(backup_sql.encode()).hexdigest() == checksum
# 2. restore into a fresh target, never over the only copy
target = sqlite3.connect(":memory:")
target.executescript(backup_sql)
# 3. validate before switching traffic
print("expected:", expected)           # expected: ((3, 1650), 'ok')
print("restored:", fingerprint(target))   # restored: ((3, 1650), 'ok')
print("safe to switch:", fingerprint(target) == expected)   # True

PostgreSQL Commands

# Logical (custom-format dump) into a new database, 4 parallel jobs
createdb shop_restored
pg_restore -d shop_restored -j 4 --no-owner --exit-on-error /backups/shop_2026-11-05.dump

# Only one table into a scratch database, then copy rows back
pg_restore -d scratch --table=orders /backups/shop_2026-11-05.dump

# Physical with pgBackRest to a point in time
pgbackrest --stanza=shop --delta --type=time "--target=2026-11-05 14:02:00+05:30" \
  --target-action=promote restore

MySQL and SQL Server

mysql -e "CREATE DATABASE shop_restored"
mysql shop_restored < /backups/shop.sql          # or: gunzip < shop.sql.gz | mysql shop_restored
-- SQL Server: full + log backups up to a time
RESTORE DATABASE Shop_Restored FROM DISK = 'D:\bak\shop_full.bak'
  WITH MOVE 'Shop' TO 'D:\data\Shop_R.mdf', MOVE 'Shop_log' TO 'D:\data\Shop_R.ldf', NORECOVERY;
RESTORE LOG Shop_Restored FROM DISK = 'D:\bak\shop_log.trn'
  WITH STOPAT = '2026-11-05T14:02:00', RECOVERY;

Validation Checklist

Check Example
Restore finished without errors --exit-on-error, log review
Row counts and aggregates Compare with known totals or pre-incident metrics
Integrity amcheck (PostgreSQL), CHECK TABLE, DBCC CHECKDB
Latest expected data present Orders up to the recovery target time
Application smoke tests Login, key read and write paths
Permissions and extensions Roles, grants, required extensions

Pitfalls

  • Restoring over the damaged database and losing evidence or good data.

  • Version mismatches for physical restores.

  • Missing roles or extensions, so a logical restore half-fails.

  • Forgetting to re-enable backups on the new primary.

  • First-ever restore during an outage.

Interview Tips

  • Walk through assess → choose and verify the backup → restore to a new target → validate → switch or merge → follow up.

  • Show commands for logical and physical restores, and PITR.

  • Stress testing restores regularly and measuring RTO.

39

What are the common strategies for database disaster recovery?

Database disaster recovery (DR) plans how to restore service and data after major failures: hardware loss, data-centre or region outages, corruption, human error (a dropped table), ransomware. Strategies are chosen by two targets: - RPO (Recovery Point Objective): how much data loss is acceptable, measured in time. - RTO (Recovery Time Objective): how quickly service must return.

Common strategies, from cheapest and slowest to most expensive and fastest: - Backup and restore: regular full and incremental physical backups plus continuous log archiving (WAL or binlogs) for point-in-time recovery, stored off-site: another region or account, immutable and versioned object storage (S3 Object Lock) for ransomware resistance. RPO is minutes (with log shipping); RTO is hours. - Pilot light / warm standby: a minimal replica in another region kept up to date (cross-region read replica, log shipping), scaled up and promoted in a disaster. RPO is seconds to minutes (asynchronous); RTO is minutes to an hour. - Hot standby with automatic failover: synchronous or semi-synchronous replicas in another availability zone, with an orchestrator such as Patroni (PostgreSQL), Orchestrator (MySQL), SQL Server Always On Availability Groups, Oracle Data Guard, or managed Multi-AZ (RDS, Cloud SQL HA, Azure zone-redundant). RPO is near zero (synchronous); RTO is seconds to minutes. - Multi-region active-active: distributed databases (Spanner, CockroachDB, YugabyteDB, Cosmos DB, DynamoDB global tables, Aurora Global Database for fast cross-region failover). Regions serve traffic simultaneously and survive a region loss. RPO is near zero; RTO is near zero. This is complex and expensive, with conflict and latency trade-offs.

Read full answer

Also needed: - Protection from logical errors: replication copies mistakes instantly, so keep PITR backups, delayed replicas (recovery_min_apply_delay) and snapshots. - Runbooks and automation for failover, DNS or connection updates, and failback. - Regular DR drills that measure real RPO and RTO. - Monitoring of replication lag and backup success. - Security: separate credentials and accounts for backups, encryption, and immutability. - Dependencies: applications, secrets, DNS and other infrastructure must also be recoverable.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, cloud databases

Strategy Comparison

Strategy RPO RTO Cost Notes
Backup + restore (with log archive) Minutes Hours Low Must test restore speed
Pilot light / warm standby (async replica) Seconds–minutes Minutes–1 hour Medium Manual or scripted promotion
Hot standby, synchronous, auto-failover ~0 Seconds–minutes Medium–high Usually within a region (AZs)
Multi-region active-active ~0 ~0 High Distributed DB, conflict and latency design
Delayed replica Configurable Minutes Medium Protects against human error

Example: PostgreSQL DR Stack

Region A (primary)                          Region B (DR)
  Patroni cluster: primary + sync standby     async standby (streaming replication, promotable)
  pgBackRest -> S3 (versioned, Object Lock)   pgBackRest repo copy in region B
  WAL archived continuously (archive_command)
  delayed standby (recovery_min_apply_delay = '1h') for human-error recovery
Runbook: promote region-B standby -> update DNS / service discovery -> repoint apps -> rebuild old primary as standby

AWS Managed Example

# Cross-region automated backup replication for RDS (PITR in the DR region)
aws rds start-db-instance-automated-backups-replication \
  --source-db-instance-arn arn:aws:rds:eu-west-1:123456789012:db:shop-db \
  --backup-retention-period 7 --region eu-central-1

# Cross-region read replica that can be promoted in a disaster
aws rds create-db-instance-read-replica --db-instance-identifier shop-dr \
  --source-db-instance-identifier arn:aws:rds:eu-west-1:123456789012:db:shop-db --region eu-central-1

DR Plan Checklist

Item Question
Objectives RPO and RTO per database, agreed with the business
Backups Off-site, immutable, encrypted, retention policy
Replication Synchronous within region, asynchronous cross-region
Failover Automated or runbook; who decides; DNS and connection handling
Logical errors PITR, delayed replicas
Testing Scheduled drills with measured results
Failback How to return to the primary region safely

Pitfalls

  • Replicas as the only “backup”: they replicate deletes and corruption.

  • Backups in the same account or region as the primary, which ransomware or an outage can take too.

  • Untested runbooks and unknown restore times.

  • Split brain after failover without fencing the old primary.

Interview Tips

  • Start with RPO and RTO, then map strategies to them.

  • Cover backups with PITR, warm and hot standbys, multi-region designs, and delayed replicas.

  • Stress drills, immutability and automation.

40

How does point-in-time recovery work in databases?

Point-in-time recovery (PITR) restores a database to its exact state at any chosen moment, such as 14:02:59, just before a bad DELETE at 14:03. It combines two ingredients: 1. A base backup: a physical copy of the data files taken at some earlier time (pg_basebackup or pgBackRest full backups, XtraBackup, RMAN, SQL Server full backups, cloud snapshots). 2. A continuous archive of the transaction log since that backup: PostgreSQL WAL segments (via archive_command or archive_library, or pgBackRest), MySQL binary logs, SQL Server transaction log backups, Oracle archived redo logs. Every committed change is recorded there in order.

Recovery process: - Restore the base backup to a new location or instance. - Configure a recovery target: - a time (recovery_target_time), or - a transaction ID, LSN or named restore point (pg_create_restore_point()); - in MySQL, a binlog position or GTID set (mysqlbinlog --stop-datetime). - Start the database in recovery mode. It replays the archived log records in order on top of the backup, stopping just before the target. - Inspect the result, then promote it to accept writes, opening a new timeline in PostgreSQL so it doesn’t conflict with the original history.

Read full answer

What determines the guarantees: - RPO depends on how often log segments are archived. Streaming archive tools such as pg_receivewal and pgBackRest async archiving bring it to seconds. - RTO depends on backup restore time plus replay time, which grows with the amount of log since the base backup. Frequent incremental or differential backups shorten it. - The retention window covers how far back you can go: the oldest base backup plus continuous logs from it.

Managed services automate PITR: RDS and Aurora (to any second within the retention period, up to 35 days, restored as a new instance), Cloud SQL, Azure Database.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, cloud managed databases (Python 3.12 simulation)

Code Example: Base Backup + Log Replay to a Target

# Point-in-time recovery: base backup + replay of the change log up to a target time
import copy

base_backup = {"taken_at": 100, "data": {"acct:1": 500, "acct:2": 250}}     # nightly full backup

wal = [   # write-ahead log records archived continuously after the backup: (time, key, new value)
    (105, "acct:1", 450),
    (110, "acct:2", 300),
    (118, "acct:3", 75),
    (121, "acct:1", None),   # 12:01 - accidental DELETE of acct:1 (the disaster)
    (125, "acct:2", 0),      # later changes we will also lose by stopping before 121
]

def recover(target_time):
    data = copy.deepcopy(base_backup["data"])               # 1. restore the base backup
    for t, key, value in wal:                               # 2. replay WAL in order
        if t > target_time:                                 # 3. stop at the recovery target
            break
        if value is None:
            data.pop(key, None)
        else:
            data[key] = value
    return data

print(recover(130))   # {'acct:2': 0, 'acct:3': 75}                 <- latest: deletion included
print(recover(120))   # {'acct:1': 450, 'acct:2': 300, 'acct:3': 75}  <- just before the mistake

Replaying everything brings back the accidental deletion. Stopping at t=120, just before the mistake, recovers the account. Changes after the target, such as t=125, must be re-applied manually from the original database if they are needed.

PostgreSQL Configuration

# postgresql.conf on the primary: continuous WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=shop archive-push %p'

# Recovery (PostgreSQL 12+): settings in postgresql.conf + an empty recovery.signal file
restore_command = 'pgbackrest --stanza=shop archive-get %f "%p"'
recovery_target_time = '2026-11-05 14:02:59+05:30'
recovery_target_action = 'pause'        # inspect first, then SELECT pg_wal_replay_resume() or promote
# Or let pgBackRest write the recovery settings
pgbackrest --stanza=shop --type=time "--target=2026-11-05 14:02:59+05:30" \
  --target-action=pause --delta restore

MySQL With Binary Logs

# 1. restore last night's physical or logical backup, then replay binlogs up to the moment before the mistake
mysqlbinlog --start-datetime="2026-11-05 00:00:00" --stop-datetime="2026-11-05 14:02:59" \
  binlog.000412 binlog.000413 | mysql -u root -p

Cloud Example

aws rds restore-db-instance-to-point-in-time --source-db-instance-identifier shop-db \
  --target-db-instance-identifier shop-pitr-1402 --restore-time 2026-11-05T08:32:59Z

PITR Components

Component Role
Base backup Starting point (physical copy)
Log archive (WAL, binlog, redo, log backups) Every change since the base backup
Recovery target Time, transaction, LSN or restore point
Replay Re-applies changes up to the target
Promotion / new timeline Makes the restored copy writable without clashing

Pitfalls

  • Gaps in the log archive (a failed archive_command unnoticed): PITR stops at the gap.

  • Time zone confusion in recovery targets.

  • Long replay times with infrequent base backups.

  • Recovering in place over the only copy instead of to a new instance.

  • Retention shorter than detection time (the mistake is noticed after the window).

Interview Tips

  • Explain base backup + continuous log archive + replay to a target.

  • Mention recovery targets (time, LSN, restore point), timelines and promotion.

  • Discuss RPO and RTO factors, monitoring of archiving, and managed PITR.

41

How would you handle a scenario where your database’s read load significantly increases?

Handle a read surge in layers, starting with the cheapest and most effective options: 1. Find out what is driving it: use pg_stat_statements, the slow query log or APM to see which queries grew, whether it is organic growth, a new feature, a bot or scraper, an N+1 regression, or a cache failure. Fixing one bad query often removes most of the load. 2. Make each read cheaper: - add or adjust indexes (composite, covering, partial); - fix N+1 patterns; - select only needed columns; - use keyset pagination; - make sure the working set fits in memory (buffer cache hit ratio). 3. Cache: - application caching with Redis or Memcached (cache-aside with TTLs and invalidation on writes); - HTTP and CDN caching for public pages; - materialised views or summary tables for expensive aggregates; - request coalescing, so a cache miss doesn’t stampede the database. 4. Add read replicas: route read-only queries to replicas (PostgreSQL streaming replicas, MySQL replicas, Aurora replicas behind a reader endpoint), using framework support such as Rails multiple databases, Django database routers, or ProxySQL and pgpool. Account for replication lag: send a user’s reads to the primary briefly after their own writes (read-your-writes), and keep critical reads on the primary. 5. Manage connections: a connection pooler (PgBouncer, RDS Proxy, ProxySQL) so many app instances don’t exhaust database connections. 6. Scale vertically if needed: more RAM and CPU, faster storage. This is often the quickest relief. 7. Offload specialised reads: full-text search to Elasticsearch or OpenSearch, analytics to a warehouse or OLAP store (via CDC), and time-series data to a dedicated store. 8. Protect the database: rate-limit abusive clients, apply timeouts (statement_timeout), and shed non-critical features under load.

Then monitor replica lag, cache hit rates and query latency, and plan capacity ahead of growth.

Read full answer

Applies toPostgreSQL, MySQL, Aurora, Redis, PgBouncer, ProxySQL (Python 3.12 simulation)

Code Example: Replica Routing With Read-Your-Writes

# Read scaling with replicas: route reads to replicas, but keep read-your-writes consistency
# by sending a user's reads to the primary for a short window after they write
import random

class Cluster:
    def __init__(self, replicas=3, lag=2.0):
        self.primary = {}
        self.replicas = [dict() for _ in range(replicas)]
        self.pending = []                     # (apply_at, key, value) replication stream
        self.lag, self.now = lag, 0.0
        self.last_write = {}                  # user -> time of their last write

    def tick(self, dt):
        self.now += dt
        due = [p for p in self.pending if p[0] <= self.now]
        self.pending = [p for p in self.pending if p[0] > self.now]
        for _, k, v in due:
            for r in self.replicas:
                r[k] = v

    def write(self, user, key, value):
        self.primary[key] = value
        self.last_write[user] = self.now
        self.pending.append((self.now + self.lag, key, value))

    def read(self, user, key, sticky_window=5.0):
        if self.now - self.last_write.get(user, -1e9) < sticky_window:
            return self.primary.get(key), "primary"
        return random.choice(self.replicas).get(key), "replica"

random.seed(1)
c = Cluster()
c.write("asha", "profile:asha", "new bio")
print("naive replica read:", random.choice(c.replicas).get("profile:asha"))   # None (lagging)
print("asha reads:", c.read("asha", "profile:asha"))      # ('new bio', 'primary')  her own write
print("ravi reads:", c.read("ravi", "profile:asha"))      # (None, 'replica')  stale for 2 s: acceptable
c.tick(6.0)
print("later, asha:", c.read("asha", "profile:asha"))     # ('new bio', 'replica')  load moved off primary

Reads move off the primary, but a user who just wrote is pinned to the primary for a short window, so they never see their own change “disappear”. Other users can tolerate seconds of lag.

Rails: Automatic Replica Switching

# config/database.yml defines primary and primary_replica; then in ApplicationRecord:
connects_to database: { writing: :primary, reading: :primary_replica }

# config/application.rb: GETs read from the replica unless the user wrote in the last 2 s
config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

Cache-Aside Sketch

read(key):  v = redis.get(key); if hit -> return v
            v = db.query(...); redis.set(key, v, ex=300); return v
write(key): db.update(...); redis.delete(key)     # invalidate after commit

Options Compared

Option Gain Trade-off
Query and index tuning Often 10–100× for hot queries Engineering time
Application cache (Redis) Removes repeated reads Invalidation, staleness
CDN / HTTP cache Removes whole requests Only cacheable, public responses
Read replicas Near-linear read capacity Replication lag, more servers
Vertical scaling Quick Cost ceiling
Materialised views Cheap reads of heavy aggregates Refresh lag
Search or analytics offload Specialised performance Extra systems, CDC pipelines

Pitfalls

  • Adding replicas before fixing a bad query that would overload them too.

  • Ignoring replication lag, so users see stale or missing data.

  • Cache stampedes when hot keys expire.

  • Connection explosions from more app instances without pooling.

  • Long analytical queries on replicas causing replication conflicts (PostgreSQL hot_standby_feedback trade-offs).

Interview Tips

  • Start with diagnosis, then optimise, cache, replicate, pool, scale up, offload.

  • Mention replica lag and read-your-writes handling.

  • Include monitoring and protective limits.

42

What strategies exist for scaling writes in a high-volume database system?

Writes are harder to scale than reads: replicas don’t help, because every replica must apply every write. Strategies: - Make each write cheaper: - batch inserts and updates (multi-row INSERT, COPY, executemany) and group them into fewer transactions, so one commit (fsync) covers many rows; - drop unused indexes, since each index multiplies write work; - use appropriate types; - avoid write amplification from triggers or wide rows; - use HOT updates in PostgreSQL (fillfactor, no index changes on frequently updated columns). - Reduce contention: - avoid hot rows such as global counters: use sharded or bucketed counters, or aggregate asynchronously; - use append-only designs (event logs) instead of updating the same row repeatedly; - keep transactions short; - choose primary keys that don’t cause page splits or hot index pages (time-ordered keys help B-trees; hash-distributed keys help distributed databases avoid hot ranges). - Absorb bursts asynchronously: put a queue or log (Kafka, SQS, RabbitMQ) in front, and have workers write in batches at a sustainable rate. Use write-behind caching for metrics-style data, accepting some delay. - Tune durability and I/O deliberately: - faster storage (NVMe, provisioned IOPS); - larger WAL and checkpoint settings; - group commit; - for non-critical data, relaxed durability (synchronous_commit = off per transaction in PostgreSQL, innodb_flush_log_at_trx_commit = 2), accepting a small window of loss on a crash. - Partition and scale up: table partitioning keeps indexes small and allows parallel maintenance, and a bigger primary is often the simplest step. - Scale out writes: - sharding by a key (tenant, user) across many primaries, with Vitess, Citus or application sharding; - distributed SQL (CockroachDB, YugabyteDB, Spanner, TiDB), which spreads ranges across nodes automatically; - write-optimised NoSQL with LSM-tree storage (Cassandra, ScyllaDB, DynamoDB) for very high ingest. - Separate workloads: move logs, metrics and events to time-series or streaming stores, keeping the OLTP database for transactional data. Use CQRS to split write and read models.

Read full answer

Applies toPostgreSQL, MySQL, distributed SQL, Cassandra, DynamoDB, Kafka (example uses SQLite via Python 3.12)

Code Example: Batching Commits

# Write throughput: one transaction per row vs batching many rows per transaction
import sqlite3, tempfile, os, time

path = os.path.join(tempfile.mkdtemp(), "w.db")
db = sqlite3.connect(path, isolation_level=None)
db.execute("PRAGMA journal_mode=WAL")
db.execute("PRAGMA synchronous=FULL")            # durable commits (fsync each commit)
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT)")
rows = [(f"event {i}",) for i in range(2000)]

t = time.perf_counter()
for r in rows:                                    # autocommit: one durable commit per row
    db.execute("INSERT INTO events (payload) VALUES (?)", r)
per_row = time.perf_counter() - t

t = time.perf_counter()
db.execute("BEGIN")
db.executemany("INSERT INTO events (payload) VALUES (?)", rows)   # one commit for 2000 rows
db.execute("COMMIT")
batched = time.perf_counter() - t

print("rows:", db.execute("SELECT COUNT(*) FROM events").fetchone()[0])   # 4000
print("batched is faster:", per_row > 5 * batched)    # True (ratio depends on disk fsync speed)

Each autocommitted row pays for a durable flush. Batching amortises that cost over thousands of rows.

PostgreSQL Bulk-Load and Hot-Row Patterns

-- Fastest bulk ingest
COPY events (user_id, kind, created_at) FROM STDIN WITH (FORMAT csv);

-- Multi-row insert with conflict handling
INSERT INTO page_views (page_id, day, views) VALUES (1, '2026-11-05', 10), (2, '2026-11-05', 4)
ON CONFLICT (page_id, day) DO UPDATE SET views = page_views.views + EXCLUDED.views;

-- Sharded counter: spread updates over 16 pre-created rows instead of one hot row
-- (the uncorrelated subquery runs once; random() directly in WHERE would run per row)
UPDATE like_counts SET n = n + 1
WHERE post_id = 42 AND bucket = (SELECT floor(random() * 16)::int);
SELECT sum(n) FROM like_counts WHERE post_id = 42;

-- Relax durability only for low-value writes
BEGIN; SET LOCAL synchronous_commit = off; INSERT INTO analytics_events ...; COMMIT;

Strategy Map

Strategy Scale gain Cost
Batching / COPY Large (often 10×+) Latency per item, code changes
Fewer indexes Moderate Slower reads that used them
Queue + batch workers Smooths bursts Asynchronous, eventual consistency
Bucketed counters Removes hot-row contention Read must sum buckets
Partitioning Smaller indexes, easier maintenance Partition key in queries and constraints
Vertical scaling Quick Ceiling, cost
Sharding / distributed SQL Near-linear Cross-shard queries, complexity
LSM NoSQL stores Very high ingest Data model and consistency trade-offs

Pitfalls

  • Adding replicas to fix write load: it doesn’t help.

  • Autoincrement or timestamp keys as distributed shard keys, creating a hot range.

  • Disabling durability globally for speed.

  • Huge single transactions that hold locks and bloat WAL; batch in chunks instead.

Interview Tips

  • Explain why writes don’t scale with replicas.

  • Cover batching, contention removal, async queues, durability tuning, partitioning, sharding and distributed databases.

  • Mention hot keys and CQRS or workload separation.

43

Describe how connection pooling benefits database performance.

Opening a database connection is expensive: TCP handshake, TLS negotiation, authentication (SCRAM, IAM token checks) and, in PostgreSQL, forking a new backend process with its own memory. That often takes several milliseconds or more, sometimes longer than the query itself. Each open connection also consumes server resources: memory per backend, locks and process slots, plus file descriptors. Too many connections cause context switching and contention, and performance drops.

A connection pool keeps a set of open connections and lends them to requests: - Lower latency: requests skip connection setup and reuse warm connections, including cached prepared statements. - Higher throughput: less CPU spent on connection churn on both sides. - Protection for the database: the pool caps concurrent connections. A database is fastest with a limited number of active connections (often a small multiple of CPU cores); beyond that, queuing in the pool is better than thrashing in the database. - Handling many clients: hundreds of app instances, serverless functions or workers share a small number of server connections through an external pooler. - Resilience: pools can validate connections, replace broken ones, apply timeouts and queue bursts.

Read full answer

Types: - Application-side pools: - HikariCP (Java); - the ActiveRecord pool (Rails); - SQLAlchemy QueuePool; - node-postgres Pool; - Go database/sql. - External poolers or proxies: - PgBouncer: session, transaction or statement pooling; - pgcat and Odyssey; - RDS Proxy; - ProxySQL for MySQL.

Transaction pooling multiplexes thousands of clients over few server connections, but breaks session state (SET, advisory locks, LISTEN, and server-side prepared statements before PgBouncer 1.21).

Applies to: PostgreSQL, MySQL, SQL Server, Oracle; HikariCP, PgBouncer, RDS Proxy, ProxySQL (Python 3.12 simulation)

Code Example: Pooled Versus New Connections

# Connection pooling: reuse a few open connections instead of paying setup cost per request
import queue, threading, time

CONNECT_COST = 0.02          # simulated TCP + TLS + auth + backend process start (20 ms)
QUERY_COST = 0.002           # the actual query (2 ms)
opened = 0
lock = threading.Lock()

class Conn:
    def __init__(self):
        global opened
        time.sleep(CONNECT_COST)
        with lock:
            opened += 1
    def query(self):
        time.sleep(QUERY_COST)

class Pool:
    def __init__(self, size):
        self.free = queue.Queue()
        for _ in range(size):
            self.free.put(Conn())
    def run(self):
        conn = self.free.get()            # waits if all connections are busy (bounded load on DB)
        try:
            conn.query()
        finally:
            self.free.put(conn)           # return, don't close

def workload(handler, requests=200, threads=20):
    t = time.perf_counter()
    ts = [threading.Thread(target=lambda: [handler() for _ in range(requests // threads)])
          for _ in range(threads)]
    [x.start() for x in ts]; [x.join() for x in ts]
    return time.perf_counter() - t

no_pool = workload(lambda: Conn().query())
opened_without = opened
opened = 0
pool = Pool(size=5)
with_pool = workload(pool.run)

print("connections opened without pool:", opened_without)   # 200
print("connections opened with pool:   ", opened)           # 5
print("pool faster:", no_pool > with_pool)                  # True

200 requests opened 200 connections without a pool, paying the setup cost every time. With a pool, 5 connections served all of them.

PgBouncer Configuration

[databases]
shop = host=10.0.1.20 port=5432 dbname=shop

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction          ; server connection returned after each transaction
max_client_conn = 5000           ; clients (app processes) that may connect
default_pool_size = 40           ; actual PostgreSQL connections per db/user pair
reserve_pool_size = 10
server_idle_timeout = 600
max_prepared_statements = 200    ; PgBouncer 1.21+: prepared statements in transaction mode

Application Pool Sizing (HikariCP-style)

maximumPoolSize=10               # per app instance; total = instances x size must fit the DB
minimumIdle=10
connectionTimeout=3000           # ms to wait for a free connection before failing
maxLifetime=1800000              # recycle before network or DB idle timeouts

A common starting point for the database’s active connections is cores × 2 + effective spindles. Measure and adjust: many small pools across many instances add up quickly.

Pool Modes (PgBouncer)

Mode Server connection held for Compatible with session features Multiplexing
Session Whole client session Yes Low
Transaction One transaction No SET, advisory locks, LISTEN across transactions High
Statement One statement No multi-statement transactions Highest

Pitfalls

  • Oversized pools (instances × pool size exceeding max_connections), causing errors or thrashing.

  • Leaked connections that are never returned (missing close or ensure): use pool leak detection.

  • Session state used under transaction pooling.

  • Stale connections after failover: configure validation and max lifetimes.

  • Serverless functions opening connections per invocation without a proxy.

Interview Tips

  • Explain the cost of connections and resource use per connection.

  • Describe reuse, capping and queuing, and app-side versus external poolers.

  • Mention pool sizing, PgBouncer modes and common pitfalls.

44

How do you monitor and identify database performance bottlenecks?

Monitoring should answer: is the database healthy, is it the bottleneck, and which queries or resources are responsible? Work in layers: - Golden signals for the database: query latency (p95/p99), throughput (queries and transactions per second), errors (failed queries, deadlocks, timeouts), and saturation: CPU, memory, disk I/O (IOPS, throughput, latency), connections in use against the maximum, and replication lag. - Query-level visibility: the most important tool. Normalised statement statistics ranked by total time: - PostgreSQL pg_stat_statements; - MySQL performance_schema digests and the sys schema; - SQL Server Query Store and DMVs; - Oracle AWR and ASH.

Add the slow query log (log_min_duration_statement, slow_query_log) and auto_explain for plans of slow statements. - Waits and locks: what sessions are waiting on: - PostgreSQL pg_stat_activity.wait_event, pg_locks, pg_blocking_pids(); - MySQL performance_schema wait events and sys.innodb_lock_waits; - SQL Server sys.dm_os_wait_stats; - Oracle wait events.

Read full answer

Lock contention and I/O waits show up here. - Internal health: - cache hit ratio (shared buffers or the InnoDB buffer pool); - checkpoints and WAL volume; - temp file usage (sorts spilling to disk); - table and index bloat, autovacuum progress, dead tuples; - unused and missing indexes (pg_stat_user_indexes, seq scan counts); - long-running transactions. - Correlation with the application: APM and distributed tracing (OpenTelemetry, Datadog, New Relic) link slow endpoints to specific queries, and connection pool metrics show waits for connections. - Tooling: Prometheus exporters (postgres_exporter, mysqld_exporter) with Grafana dashboards, cloud insights (RDS Performance Insights, Cloud SQL Query Insights), pganalyze, Percona Monitoring and Management.

Workflow: 1. Detect the symptom (alerts on latency or saturation). 2. Check whether it is the database (APM breakdown) or something else. 3. Find the top queries by total time and their wait events. 4. Run EXPLAIN ANALYZE on the culprits. 5. Fix them (indexes, query rewrites, configuration, capacity). 6. Verify on the same metrics.

Applies to: PostgreSQL, MySQL, SQL Server, Oracle, cloud databases (Python 3.12 illustration)

Code Example: Rank Normalised Queries by Total Time

# Finding the queries that matter: normalise statements and rank by TOTAL time
# (what pg_stat_statements / performance_schema digests do)
import re
from collections import defaultdict

log = [  # (duration_ms, statement) from a slow log or tracing
    (2.1, "SELECT * FROM users WHERE id = 42"),
    (1.9, "SELECT * FROM users WHERE id = 7"),
    (850, "SELECT * FROM report_daily WHERE day = '2026-11-01'"),
    (2.3, "SELECT * FROM users WHERE id = 99"),
    (35, "SELECT * FROM orders WHERE customer_id = 12 ORDER BY placed_at DESC"),
    (38, "SELECT * FROM orders WHERE customer_id = 5 ORDER BY placed_at DESC"),
] + [(2.0, f"SELECT * FROM users WHERE id = {i}") for i in range(3000)] \
  + [(36, f"SELECT * FROM orders WHERE customer_id = {i} ORDER BY placed_at DESC") for i in range(400)]

def normalise(sql):
    sql = re.sub(r"'[^']*'", "?", sql)            # string literals
    return re.sub(r"\b\d+\b", "?", sql)           # numbers

stats = defaultdict(lambda: [0, 0.0])
for ms, sql in log:
    s = stats[normalise(sql)]
    s[0] += 1; s[1] += ms

for q, (calls, total) in sorted(stats.items(), key=lambda kv: -kv[1][1]):
    print(f"total={total:8.0f} ms  calls={calls:5d}  mean={total / calls:6.1f} ms  {q}")
# total=   14473 ms  calls=  402  mean=  36.0 ms  SELECT * FROM orders WHERE customer_id = ? ORDER BY ...
# total=    6006 ms  calls= 3003  mean=   2.0 ms  SELECT * FROM users WHERE id = ?
# total=     850 ms  calls=    1  mean= 850.0 ms  SELECT * FROM report_daily WHERE day = ?

The 850 ms report looks scary, but a 36 ms query called 402 times costs 17× more in total. Total time finds the real bottleneck.

PostgreSQL Queries

-- Top queries by total time
SELECT left(query, 80) AS query, calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS mean_ms, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- What are active sessions waiting on right now?
SELECT wait_event_type, wait_event, state, count(*)
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3 ORDER BY 4 DESC;

-- Who is blocking whom?
SELECT pid, pg_blocking_pids(pid) AS blocked_by, now() - query_start AS waiting, left(query, 60)
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Cache hit ratio and temp file spills per database
SELECT datname, round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS hit_pct, temp_files, temp_bytes
FROM pg_stat_database;
# postgresql.conf: capture slow statements with their plans
shared_preload_libraries = 'pg_stat_statements,auto_explain'
log_min_duration_statement = 500ms
auto_explain.log_min_duration = 500ms
auto_explain.log_analyze = on

Symptom → Likely Cause

Symptom Likely cause
High CPU, many seq scans Missing indexes, bad plans
High I/O wait, low cache hit ratio Working set exceeds RAM; heavy scans
Many Lock waits Contention, long transactions, hot rows
Connections near max, pool waits Pool sizing, leaks, slow queries holding connections
Temp files growing Sorts and hashes spilling (work_mem, indexes)
Replica lag rising Write bursts, long queries on replicas, I/O limits
Latency spikes every few minutes Checkpoints, autovacuum, cron jobs

Pitfalls

  • Watching averages only, missing tail latency.

  • Optimising the single slowest query instead of the biggest total consumer.

  • No baseline, so “normal” is unknown.

  • Logging every statement in production, causing I/O overhead.

Interview Tips

  • Cover golden signals, query statistics, wait events and internal health.

  • Name tools: pg_stat_statements, performance_schema, Query Store, Performance Insights.

  • Describe the workflow and rank by total time.

45

What is horizontal and vertical scaling in databases?

Vertical scaling (scale up) means making the database server bigger: more CPU cores, more RAM (so more of the working set fits in the buffer cache), faster storage (NVMe, provisioned IOPS) and more network bandwidth. It is the simplest option, with no application changes, and it keeps full ACID transactions, joins and constraints on one node. - Limits: a hardware ceiling (the largest instance available), rising cost at the high end, usually downtime or a failover to resize, and the server remains a single point of failure, which needs replicas for HA.

Horizontal scaling (scale out) means adding more servers and spreading data or load across them: - Read replicas spread reads. The primary still takes all writes, and replicas lag. - Sharding or partitioning across nodes spreads both data and writes by a shard key: Vitess, Citus, MongoDB sharding, application-level shards. - Distributed databases do this natively: Cassandra, DynamoDB, CockroachDB, Spanner, YugabyteDB, TiDB. - Benefits: near-linear capacity growth, fault tolerance (losing a node loses only part of the capacity), elasticity, and geographic distribution. - Costs: complexity. Cross-node queries and joins are slower, distributed transactions need coordination (2PC, consensus), consistency becomes a trade-off (CAP/PACELC), rebalancing is needed, and operations are harder.

Read full answer

In practice, teams scale up first (with replicas for HA and reads), optimise and cache, then scale out writes when a single primary can’t keep up or data no longer fits. Some managed services blur the line: Aurora separates compute from distributed storage, and serverless options scale capacity automatically.

Applies to: PostgreSQL, MySQL, SQL Server, MongoDB, Cassandra, DynamoDB, distributed SQL

Comparison

Aspect Vertical (scale up) Horizontal (scale out)
How Bigger server More servers (replicas, shards, distributed nodes)
App changes None Routing, shard keys, query patterns
Read scaling Limited by one node Replicas or shards: high
Write scaling Limited by one node Shards or distributed nodes: high
Transactions and joins Full, local Cross-node costs; distributed transactions
Fault tolerance Needs separate replicas Built-in redundancy (often)
Limit Largest machine Coordination overhead, design
Cost curve Steep at the top Roughly linear (commodity nodes)
Downtime to scale Often a restart or failover Add nodes online (usually)

Typical Evolution

1. Single primary + standby (HA)                     -> tune queries, add indexes
2. Bigger instance (vertical)                         -> more RAM, faster disks
3. Read replicas + cache                              -> horizontal for reads
4. Partitioning, archiving, workload separation       -> keep tables manageable
5. Sharding or distributed SQL (horizontal writes)    -> when one primary isn't enough

Example: Scale Up Versus Out on AWS

# Vertical: change instance class (Multi-AZ failover keeps downtime short)
aws rds modify-db-instance --db-instance-identifier shop-db \
  --db-instance-class db.r7g.4xlarge --apply-immediately

# Horizontal reads: add a replica
aws rds create-db-instance-read-replica --db-instance-identifier shop-db-replica-2 \
  --source-db-instance-identifier shop-db
-- Horizontal writes with Citus (PostgreSQL extension): distribute a table by tenant
SELECT create_distributed_table('orders', 'tenant_id');

Choosing

Situation Approach
Moderate growth, complex queries, strong consistency Scale up + replicas
Read-heavy Replicas + caching
Write-heavy, huge data, multi-region Sharding or distributed database
Unpredictable spikes Serverless or autoscaling options, caching

Pitfalls

  • Sharding too early, adding complexity a bigger instance would have avoided.

  • Waiting too long, so the largest instance is reached under pressure.

  • Assuming replicas scale writes.

  • Ignoring HA in a vertically scaled single node.

Interview Tips

  • Define scale up (bigger node) and scale out (more nodes).

  • Compare limits, complexity, consistency and costs.

  • Describe the typical progression and when to shard.

46

What is a document store, and where is it appropriate to use?

A document store is a NoSQL database that stores data as self-describing documents, usually JSON or BSON. Each document is a nested structure (objects, arrays, sub-documents) identified by a key (_id) and grouped into collections. Documents in the same collection can have different fields, so the schema is flexible: it is enforced by the application or by optional validation rules (MongoDB $jsonSchema). Unlike key-value stores, document databases can query and index fields inside documents (nested paths, arrays, text, geospatial) and run aggregation pipelines. Examples: MongoDB, Couchbase, Amazon DocumentDB, Azure Cosmos DB, Google Firestore, CouchDB, RavenDB; PostgreSQL’s jsonb offers similar capabilities inside a relational database.

The modelling principle is “data that is accessed together is stored together”: you embed related data (an order with its line items, a product with its variants) so one read returns the whole aggregate without joins. You reference data that is shared or unbounded.

Read full answer

Appropriate when: - data is naturally hierarchical and read as a whole: product catalogues with varying attributes per category, content management (articles, pages), user profiles and preferences, event or game state, mobile and offline-first apps (Firestore, Couchbase Lite); - schemas evolve quickly, or records are heterogeneous; - you need horizontal scaling (sharding) with access patterns centred on one entity at a time; - you want developer productivity, with documents mapping directly to application objects.

Less appropriate when: - data is highly relational with many-to-many relationships and ad hoc joins, such as complex reporting; - you need strict, cross-entity integrity and multi-entity transactions everywhere. MongoDB supports multi-document ACID transactions, but they cost performance and should be the exception; - the same data is duplicated in many documents and changes often (update anomalies).

Applies to: MongoDB, Couchbase, Firestore, Cosmos DB, DocumentDB, PostgreSQL jsonb (example uses SQLite JSON functions via Python 3.12)

Code Example: Querying Inside Flexible Documents

# Document model: each product is one self-contained JSON document with a flexible shape.
# Stored in SQLite's JSON functions here, as a stand-in for MongoDB-style queries.
import json, sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (id TEXT PRIMARY KEY, doc TEXT NOT NULL CHECK (json_valid(doc)))")
docs = [
    {"_id": "p1", "name": "Trail shoe", "category": "shoes", "price": 89,
     "attrs": {"size": [40, 41, 42], "waterproof": True}},
    {"_id": "p2", "name": "Laptop 14", "category": "laptops", "price": 999,
     "attrs": {"ram_gb": 16, "cpu": "8-core"}, "reviews": [{"user": "asha", "stars": 5}]},
    {"_id": "p3", "name": "Road shoe", "category": "shoes", "price": 120,
     "attrs": {"size": [42, 43], "waterproof": False}},
]
db.executemany("INSERT INTO products VALUES (?, ?)", [(d["_id"], json.dumps(d)) for d in docs])
db.execute("CREATE INDEX idx_category ON products (json_extract(doc, '$.category'))")

# Like db.products.find({category: "shoes", "attrs.waterproof": true}, {name: 1})
rows = db.execute("""SELECT json_extract(doc, '$.name') FROM products
                     WHERE json_extract(doc, '$.category') = 'shoes'
                       AND json_extract(doc, '$.attrs.waterproof') = 1""").fetchall()
print(rows)                                   # [('Trail shoe',)]

# One read returns the whole aggregate: no joins for attributes or reviews
p2 = json.loads(db.execute("SELECT doc FROM products WHERE id = 'p2'").fetchone()[0])
print(p2["attrs"]["ram_gb"], p2["reviews"][0]["stars"])    # 16 5

The shoe and laptop documents have different attributes in the same collection, and the laptop embeds its reviews.

MongoDB Equivalent

db.products.createIndex({ category: 1, price: 1 });
db.products.find(
  { category: "shoes", "attrs.waterproof": true },
  { name: 1, price: 1 }
);
// Aggregation: average price per category
db.products.aggregate([
  { $group: { _id: "$category", avgPrice: { $avg: "$price" }, n: { $sum: 1 } } },
  { $sort: { avgPrice: -1 } }
]);

Embed Versus Reference

Embed when Reference when
Data is read together (order + items) Data is shared by many documents (customer referenced by orders)
Child count is bounded (addresses, variants) Child list is unbounded (comments on a viral post, logs)
Child has no independent lifecycle Child is queried or updated independently
Atomic updates of the whole aggregate are needed Document would exceed size limits (MongoDB: 16 MB)

Fit Guide

Use case Document store?
Product catalogue with varied attributes Yes
CMS, blogs, user profiles Yes
Mobile/offline sync Yes (Firestore, Couchbase)
Financial ledger with strict invariants Prefer relational
BI reporting with complex joins Prefer relational or a warehouse
Social graph traversals Prefer a graph database

Pitfalls

  • Modelling relationally (many small collections and $lookup everywhere).

  • Unbounded arrays growing documents without limit.

  • No schema discipline, so field names and types drift.

  • Missing indexes on query fields, causing collection scans.

Interview Tips

  • Define documents, collections, flexible schema and indexing of nested fields.

  • Explain embed versus reference with the access-pattern principle.

  • Give good fits (catalogues, CMS, profiles) and poor fits (heavy joins, strict cross-entity integrity).

47

What is a graph database, and what are its typical use cases?

A graph database stores data as a graph: - nodes (vertices): entities such as people, accounts, products or devices; - relationships (edges): typed, directed connections such as FOLLOWS, PURCHASED or TRANSFERRED_TO; - properties on both, as key-value attributes.

In native graph databases, relationships are stored as direct pointers (“index-free adjacency”), so traversing connections costs time proportional to the part of the graph explored, not to total data size. Multi-hop queries (“friends of friends who bought X”) that would need many self-joins in SQL become simple, fast traversals.

Read full answer

Models and languages: - Labelled property graphs, queried with Cypher (Neo4j, Memgraph, Amazon Neptune via openCypher) or Gremlin (Apache TinkerPop: JanusGraph, Neptune, Cosmos DB). GQL (ISO/IEC 39075, published 2024) standardises property-graph querying. - RDF triple stores (subject–predicate–object), queried with SPARQL: GraphDB, Stardog, Neptune. Used for knowledge graphs and linked data.

Typical use cases: - Social networks: friends of friends, mutual connections, influence and community detection. - Recommendations: “people who bought this also bought”, collaborative filtering through shared neighbours. - Fraud detection: rings of accounts sharing devices, addresses or cards; money-laundering paths across transfers. - Knowledge graphs and semantic search: entities and their relationships, often feeding GraphRAG retrieval for LLM applications. - Network and IT operations: dependency maps, impact analysis (“what breaks if this switch fails?”), root cause analysis. - Identity and access management: who can reach which resource through groups and roles. - Supply chains and logistics: routes, shortest paths, bottlenecks. - Master data management: linking records about the same customer across systems.

When not to use one: simple CRUD, heavy aggregations over whole tables, or analytics without relationship traversal are better served by relational, document or columnar stores. Graph databases can also be harder to shard for very large graphs.

Applies to: Neo4j, Amazon Neptune, Memgraph, TigerGraph, JanusGraph, ArangoDB (Python 3.12 traversal illustration)

Code Example: Traversals That Graphs Make Natural

# Graph traversal: friends-of-friends recommendations and shortest connection path.
# (What Cypher's MATCH (a)-[:FOLLOWS*2]->(c) expresses in a graph database.)
from collections import deque

follows = {
    "asha": ["ravi", "meera"], "ravi": ["kiran", "dev"], "meera": ["dev", "zoya"],
    "kiran": ["zoya"], "dev": [], "zoya": ["asha"],
}

def recommend(user):
    direct = set(follows[user])
    scores = {}
    for friend in direct:
        for fof in follows[friend]:
            if fof != user and fof not in direct:
                scores[fof] = scores.get(fof, 0) + 1        # number of mutual connections
    return sorted(scores.items(), key=lambda kv: (-kv[1], kv[0]))

def shortest_path(src, dst):
    prev, q = {src: None}, deque([src])
    while q:
        node = q.popleft()
        if node == dst:
            path = []
            while node:
                path.append(node); node = prev[node]
            return path[::-1]
        for nxt in follows[node]:
            if nxt not in prev:
                prev[nxt] = node; q.append(nxt)

print(recommend("asha"))               # [('dev', 2), ('kiran', 1), ('zoya', 1)]
print(shortest_path("ravi", "asha"))   # ['ravi', 'kiran', 'zoya', 'asha']

Cypher Equivalents (Neo4j)

// Friends-of-friends recommendations ranked by mutual connections
MATCH (me:User {name: 'asha'})-[:FOLLOWS]->(f)-[:FOLLOWS]->(fof:User)
WHERE fof <> me AND NOT (me)-[:FOLLOWS]->(fof)
RETURN fof.name, count(f) AS mutual ORDER BY mutual DESC, fof.name;

// Shortest path between two users
MATCH p = shortestPath((a:User {name: 'ravi'})-[:FOLLOWS*..6]->(b:User {name: 'asha'}))
RETURN [n IN nodes(p) | n.name];

// Fraud ring: accounts sharing a device with a known fraudster
MATCH (bad:Account {flagged: true})-[:USED]->(d:Device)<-[:USED]-(other:Account)
RETURN other.id, collect(d.id) AS shared_devices;

Relational Versus Graph for Multi-hop Queries

-- Friends-of-friends in SQL needs a self-join per hop (and recursive CTEs for variable depth)
SELECT f2.followee AS fof, COUNT(*) AS mutual
FROM follows f1 JOIN follows f2 ON f2.follower = f1.followee
WHERE f1.follower = 'asha' AND f2.followee <> 'asha'
  AND f2.followee NOT IN (SELECT followee FROM follows WHERE follower = 'asha')
GROUP BY f2.followee;

Use Case Fit

Use case Why a graph fits
Social and recommendations Many-hop neighbourhood queries
Fraud detection Finding hidden shared links and rings
Knowledge graphs Rich, evolving relationship types
IT dependencies Impact and root-cause traversals
Access control Transitive group and role membership
Routing and logistics Shortest paths, centrality

Pitfalls

  • Using a graph for tabular reporting.

  • Supernodes (a celebrity with millions of edges) slowing traversals: model and query carefully.

  • Unbounded variable-length patterns (* without limits) exploding.

  • Duplicating the system of record without a sync strategy.

Interview Tips

  • Define nodes, relationships and properties, and index-free adjacency.

  • Name query languages (Cypher, Gremlin, SPARQL, GQL) and products.

  • Give use cases (social, recommendations, fraud, knowledge graphs, IT) and when not to use one.

48

Describe the key-value store model and its typical applications.

A key-value store is the simplest NoSQL model: data is a collection of unique keys, each mapped to a value that the store usually treats as opaque: a string, a blob, a serialised JSON object. Operations are minimal and fast: GET key, PUT/SET key value, DELETE key, often with TTL (expiry), atomic increments, compare-and-set, and batch operations. Lookups are O(1) by key (hash tables) or ordered (in sorted KV engines), but you can’t query by value contents without extra structures.

Because of this simplicity, key-value stores offer very low latency (sub-millisecond for in-memory stores) and easy horizontal scaling, since keys are hash-partitioned across nodes.

Read full answer

Families and examples: - In-memory stores: Redis and Valkey (rich data structures: lists, sets, sorted sets, hashes, streams; optional persistence), Memcached (pure cache). - Distributed, durable stores: - Amazon DynamoDB: key-value plus document, with partition and sort keys; - Riak KV; - Aerospike; - FoundationDB. - Coordination stores: etcd (backing store for Kubernetes) and Consul KV, strongly consistent through Raft. - Embedded engines: RocksDB, LevelDB, LMDB, BoltDB, often used as storage engines inside other databases.

Typical applications: - Caching of database queries, rendered pages and API responses (cache-aside with TTL); - session storage and shopping carts; - rate limiting and counters (INCR with expiry); - feature flags and configuration, and service discovery (etcd, Consul); - leaderboards (Redis sorted sets) and real-time analytics counters; - distributed locks and idempotency keys (with care); - queues and streams (Redis lists and streams); - user preferences and device state at huge scale (DynamoDB).

Limits: - no joins or ad hoc queries; - secondary access paths must be designed in (extra keys or indexes); - larger values reduce efficiency; - consistency and durability vary by product and configuration.

Applies to: Redis/Valkey, Memcached, DynamoDB, etcd, RocksDB, Aerospike (Python 3.12 illustration)

Code Example: TTL, Atomic Counters and LRU Eviction

# Key-value store essentials: O(1) get/set by key, TTL expiry, atomic increment, LRU eviction
from collections import OrderedDict

class KV:
    def __init__(self, max_keys):
        self.data, self.max = OrderedDict(), max_keys     # key -> (value, expires_at)

    def set(self, key, value, now, ttl=None):
        self.data[key] = (value, now + ttl if ttl else None)
        self.data.move_to_end(key)
        while len(self.data) > self.max:
            evicted, _ = self.data.popitem(last=False)   # least recently used
            print("evicted", evicted)

    def get(self, key, now):
        item = self.data.get(key)
        if item is None:
            return None
        value, exp = item
        if exp is not None and now >= exp:               # lazy expiry, like Redis
            del self.data[key]
            return None
        self.data.move_to_end(key)
        return value

    def incr(self, key, now):                            # atomic in a single-threaded server
        value = (self.get(key, now) or 0) + 1
        self.set(key, value, now)
        return value

kv = KV(max_keys=3)
kv.set("session:abc", {"user": 42}, now=0, ttl=1800)     # session with 30-minute TTL
kv.set("cart:42", ["sku-1", "sku-9"], now=0)
print(kv.incr("ratelimit:42:minute-0", now=1), kv.incr("ratelimit:42:minute-0", now=2))   # 1 2
print(kv.get("session:abc", now=100))                    # {'user': 42}
print(kv.get("session:abc", now=2000))                   # None (expired)
kv.set("feature:dark_mode", True, now=2001)
kv.set("cache:home", "<html>...", now=2002)              # evicted cart:42 (least recently used)

Redis Equivalents

SET session:abc '{"user":42}' EX 1800      # value with a 30-minute TTL
GET session:abc
INCR ratelimit:42:2026-11-05T10:15          # per-minute counter
EXPIRE ratelimit:42:2026-11-05T10:15 120
ZADD leaderboard 4200 "asha" 3900 "ravi"    # sorted set for rankings
ZREVRANGE leaderboard 0 9 WITHSCORES
SET lock:invoice:77 worker-3 NX PX 30000    # simple lock: only if absent, 30 s expiry

Key Design

Pattern Example key Purpose
Namespaced keys user:42:profile Readability, avoiding collisions
Time-bucketed keys views:post:9:2026-11-05 Counters per period, easy expiry
Composite keys (DynamoDB) PK=USER#42, SK=ORDER#2026-11-05#981 Range queries within a partition
Versioned values config:v17 Safe rollouts and rollbacks

Strengths and Limits

Strengths Limits
Sub-millisecond reads and writes No querying by value
Simple horizontal scaling No joins or relational integrity
Built-in TTL, atomic ops Hot keys can overload one shard
Flexible values Memory cost (in-memory stores)

Pitfalls

  • Using a cache as the source of truth without persistence or backups.

  • Hot keys (one viral item) concentrating load: replicate or split them.

  • Huge values or giant collections under one key (slow operations, network spikes).

  • KEYS * in production Redis (blocking): use SCAN.

  • Naive distributed locks without fencing tokens.

Interview Tips

  • Define the model: unique key → opaque value, with GET/SET/DELETE.

  • Name examples (Redis, Memcached, DynamoDB, etcd, RocksDB).

  • Give applications (caching, sessions, rate limiting, leaderboards, config) and limits (no value queries, hot keys).

49

How do you balance consistency and availability in NoSQL using the CAP theorem?

The CAP theorem (Brewer, proved by Gilbert and Lynch) states that a distributed data store can’t guarantee all three of these at once: - Consistency ©: linearisability. Every read sees the latest write. - Availability (A): every request to a non-failed node gets a response. - Partition tolerance (P): the system keeps working despite lost or delayed messages between nodes.

Since network partitions happen in real distributed systems, P isn’t optional. The real choice is what to do during a partition: - CP: refuse or delay some requests (reduced availability) to stay consistent. - AP: keep answering on all sides, accepting stale reads or conflicting writes that are reconciled later (eventual consistency).

Read full answer

PACELC extends this: if Partitioned, choose A or C; Else (normal operation), choose Latency or Consistency, because even without failures, strong consistency costs coordination latency.

How to choose, per use case and often per operation: - Prefer consistency (CP) where incorrect or conflicting data is costly: payments and balances, inventory reservations, unique usernames, leader election, configuration and locks. - Prefer availability (AP) where staleness is tolerable and downtime is costly: social feeds, likes and view counts, product reviews, shopping-cart additions (Amazon’s Dynamo paper), IoT telemetry, caching and session data.

Mechanisms in NoSQL systems: - Tunable quorums (Cassandra, ScyllaDB, Riak) with N replicas, W write acknowledgements and R read replicas. R + W > N gives read-your-latest-write behaviour (QUORUM/QUORUM, or LOCAL_QUORUM per data centre). ONE/ONE favours availability and latency. - Per-request consistency: - DynamoDB: eventually consistent reads by default, strongly consistent reads on request; - Cosmos DB: five levels (strong, bounded staleness, session, consistent prefix, eventual); - MongoDB: write concern majority, read concern majority/linearizable, read preference primary or secondary. - Consensus-based CP stores: etcd, ZooKeeper, Consul, and distributed SQL such as Spanner and CockroachDB (Raft or Paxos): a minority partition can’t accept writes. - Conflict handling for AP: last-writer-wins (simple, can lose updates), vector clocks with application merges, and CRDTs (counters, sets) that merge automatically.

Applies to: Cassandra, DynamoDB, MongoDB, Cosmos DB, Riak, etcd, CockroachDB (Python 3.12 quorum simulation)

Code Example: Quorum Settings With N = 3

# Tunable consistency with N replicas: a read sees the latest write when R + W > N
import random

N = 3

def run(W, R, trials=10_000, partitioned=0):
    stale = unavailable = 0
    for _ in range(trials):
        replicas = [("v1", 1)] * N                       # (value, version) on each replica
        up = set(random.sample(range(N), N - partitioned))
        if len(up) < W:
            unavailable += 1                             # can't reach W replicas: write rejected
            continue
        for i in random.sample(sorted(up), W):           # write v2 to W reachable replicas
            replicas[i] = ("v2", 2)
        if len(up) < R:
            unavailable += 1
            continue
        read = [replicas[i] for i in random.sample(sorted(up), R)]
        if max(read, key=lambda x: x[1])[0] != "v2":     # newest version among those read
            stale += 1
    return f"W={W} R={R}: stale {stale / trials:.0%}, unavailable {unavailable / trials:.0%}"

random.seed(3)
print(run(W=1, R=1))                  # W=1 R=1: stale ~67%, unavailable 0%   (fast, eventual)
print(run(W=2, R=2))                  # W=2 R=2: stale 0%, unavailable 0%     (quorum, R+W>N)
print(run(W=2, R=2, partitioned=1))   # W=2 R=2: stale 0%, unavailable 0%     (majority still reachable)
print(run(W=2, R=2, partitioned=2))   # W=2 R=2: stale 0%, unavailable 100%  (consistency over availability)

With W=1, R=1, reads often miss the newest write. With quorum (R + W > N), every read overlaps a replica that has it, and one node can fail. With two of three nodes unreachable, the quorum configuration refuses requests. That is the CP choice during a partition.

Cassandra: Choosing Per Query

-- cqlsh: strong-ish per data centre (R + W > RF within the DC)
CONSISTENCY LOCAL_QUORUM;
INSERT INTO accounts (id, balance) VALUES (42, 100);
SELECT balance FROM accounts WHERE id = 42;

-- Fast, available, possibly stale
CONSISTENCY ONE;
SELECT count FROM page_views WHERE page_id = 9;
// MongoDB: durable majority writes, majority reads
db.orders.insertOne({ _id: 1, total: 50 }, { writeConcern: { w: "majority" } });
db.orders.find({ _id: 1 }).readConcern("majority");

Decision Guide

Data / operation Choice Example setting
Money, stock reservations, unique constraints Consistency QUORUM/majority, strongly consistent reads, consensus stores
User’s own profile edits Session / read-your-writes Cosmos DB session, sticky primary reads
Feeds, likes, analytics Availability + low latency ONE, eventual reads, CRDT counters
Multi-region writes AP with conflict resolution, or CP with higher latency LWW, CRDTs, or Spanner-style consensus

Pitfalls

  • “Choosing two of three” as if partitions could be ignored.

  • Assuming CAP “consistency” equals ACID consistency: different meanings.

  • Last-writer-wins on counters, which loses increments.

  • One global setting instead of per-operation choices.

  • Clock skew breaking timestamp-based conflict resolution.

Interview Tips

  • State CAP precisely: during a partition, choose C or A; mention PACELC.

  • Map use cases to CP or AP, and show tunable quorums (R + W > N) and per-request levels.

  • Discuss conflict resolution (LWW, vector clocks, CRDTs) for AP systems.

50

What is eventual consistency, and in what scenarios is it used?

Eventual consistency is a consistency model for replicated systems: if no new updates are made to an item, all replicas will eventually converge to the same value. In the meantime, reads from different replicas may return stale or different values. The system accepts writes on one replica (or a few), propagates them asynchronously, and reconciles differences in the background. It guarantees convergence, not freshness.

How systems converge: - Asynchronous replication: replication logs, hinted handoff. - Anti-entropy: replicas compare data, often using Merkle trees, and gossip. - Read repair: stale replicas are fixed when a read notices a mismatch. - Conflict resolution for concurrent writes: last-writer-wins timestamps, vector clocks with application merges, or CRDTs (conflict-free replicated data types such as counters, sets and maps) that merge deterministically.

Read full answer

Stronger variants built on top make it easier to use: - read-your-writes (session consistency); - monotonic reads (never going back in time); - consistent prefix (seeing writes in order); - bounded staleness: at most X seconds or versions behind, as in Cosmos DB.

Scenarios where it is used: - Highly available, globally distributed data where latency and uptime matter more than instant agreement: Amazon’s shopping cart (the Dynamo paper), DynamoDB default reads and global tables, Cassandra with low consistency levels, Riak, and Cosmos DB eventual or session levels. - Social and content features: likes, follower counts, feeds, comments, view counters. - Caches and CDNs: cached copies refresh after TTL or invalidation. - DNS: record changes propagate within TTLs. - Read replicas in relational databases: asynchronous replicas lag the primary. - Search indexes and analytics updated through CDC or event streams (Elasticsearch, warehouses). - Microservices with event-driven integration: services update their own data from events (sagas, CQRS read models). - Offline-first and collaborative apps: mobile sync, collaborative editing with CRDTs.

Avoid it, or add stronger guarantees, for operations needing immediate correctness: balances, inventory reservations, uniqueness checks, authorisation changes.

Applies to: DynamoDB, Cassandra, Riak, Cosmos DB, MongoDB secondaries, read replicas, caches, CDNs (Python 3.12 simulation)

Code Example: Divergence, Gossip and Convergence

# Eventual consistency: replicas accept writes independently, then converge through
# anti-entropy gossip using last-writer-wins (LWW) timestamps
import random

random.seed(5)
replicas = [dict() for _ in range(4)]                    # key -> (timestamp, value)

def write(r, key, value, ts):
    replicas[r][key] = (ts, value)

def gossip_round():
    for a in range(len(replicas)):
        b = random.randrange(len(replicas))
        for key in set(replicas[a]) | set(replicas[b]):
            newest = max(replicas[a].get(key, (0, None)), replicas[b].get(key, (0, None)))
            replicas[a][key] = replicas[b][key] = newest   # both keep the newer version

write(0, "likes:post42", 10, ts=1)
write(2, "likes:post42", 11, ts=2)        # concurrent update accepted elsewhere
write(3, "bio:asha", "hello", ts=3)

rounds = 0
while len({tuple(sorted(r.items())) for r in replicas}) > 1:
    print(f"round {rounds}:", [r.get("likes:post42", (0, None))[1] for r in replicas])
    gossip_round(); rounds += 1
print(f"converged after {rounds} rounds:", replicas[0])
# round 0: [10, None, 11, None]   <- replicas disagree (reads may be stale)
# converged after 3 rounds: {'likes:post42': (2, 11), 'bio:asha': (3, 'hello')}
# LWW keeps only the newest value: if both writes were +1 increments, one would be lost.
# Counters and sets need CRDTs (e.g. a G-Counter) instead.

Replicas temporarily disagree, then gossip spreads the newest versions until all four match. Last-writer-wins is simple, but it discards concurrent updates. Use CRDTs for counters and sets.

Making Eventual Consistency Safe for Users

Technique Effect
Read-your-writes (route the author to the primary or a session token) Users always see their own changes
Monotonic reads (sticky replica) No “time travel” between requests
Version or timestamp checks Detect stale reads before acting
Idempotent, commutative updates Safe to apply in any order or more than once
CRDT counters and sets Concurrent updates merge without loss
UI design (“updating…”, optimistic UI) Hides propagation delay

DynamoDB Example

# Eventually consistent read (default, half the read cost)
aws dynamodb get-item --table-name Users --key '{"pk":{"S":"USER#42"}}'
# Strongly consistent read (same region only; not supported on global secondary indexes)
aws dynamodb get-item --table-name Users --key '{"pk":{"S":"USER#42"}}' --consistent-read

Strong Versus Eventual

Aspect Strong consistency Eventual consistency
Read freshness Always latest May be stale temporarily
Availability during partitions Reduced High
Latency Higher (coordination) Lower
Conflict handling Prevented Must be resolved
Typical uses Payments, inventory, uniqueness Feeds, counters, caches, global replicas

Pitfalls

  • Read-after-write surprises (a user saves and the change “disappears”).

  • Lost updates with last-writer-wins.

  • Building invariants (no overselling) on eventually consistent reads.

  • Unbounded lag going unnoticed: monitor replication delay.

Interview Tips

  • Define it as convergence when updates stop, with temporary staleness.

  • Explain propagation and conflict resolution (gossip, read repair, LWW, vector clocks, CRDTs).

  • Give scenarios (carts, feeds, DNS, caches, replicas, event-driven services) and session guarantees.

Sign in to keep reading

You've opened 2 full answers. Sign in to read all 50 Databases answers, mark what you know and pick up where you left off.

Report a mistake

What's wrong?

All System Design subjects