DBMS Interview Questions — Deadlock Debugging & Index Traps

Most developers can write a SELECT statement in their sleep. But interviews don't test that — they test whether you understand what happens underneath when that query runs, why your app grinds to a halt under load, or why two users can accidentally corrupt each other's data without any error message. DBMS knowledge is the difference between a developer who writes queries and one who designs systems that survive production.

The real problem is that most learning resources dump definitions at you — ACID, normalization, indexes — without explaining why any of it was invented. Normalization wasn't invented to annoy you with extra joins; it was invented because data duplication causes silent corruption. Transactions weren't invented to slow things down; they were invented because power cuts happen mid-write. Understanding the 'why' is what separates good interview answers from great ones.

By the end of this article you'll be able to explain normalization forms with a concrete example, describe ACID properties with a real scenario an interviewer can't poke holes in, differentiate clustered vs non-clustered indexes in a single sentence, and confidently answer the tricky follow-up questions that trip most candidates up.

What DBMS Interview Questions Actually Test

DBMS interview questions assess your understanding of how databases manage concurrent access and optimize queries. The core mechanic is the interplay between isolation levels, locking strategies, and index structures. A deadlock occurs when two transactions each hold a lock the other needs, creating a cycle that the database must detect and break by aborting one transaction. Index traps arise when a query looks efficient but forces a full scan because the index doesn't match the filter or sort order.

In practice, databases use lock-based or multi-version concurrency control (MVCC). MVCC avoids read locks by keeping old row versions, reducing deadlock probability but not eliminating it. Indexes like B-trees provide O(log n) lookups, but composite indexes require leftmost prefix matching — a query on the second column alone cannot use the index. Understanding these mechanics lets you predict performance and concurrency behavior without guessing.

You need this knowledge when designing schemas for high-throughput systems. A payment service processing 10,000 transactions per second must avoid deadlocks by accessing tables in a consistent order. An analytics query scanning millions of rows can be made sub-millisecond by adding a covering index. These concepts separate a developer who writes queries from one who designs robust, scalable data access.

ACID Properties — What They Are and Why Every Transaction Needs Them

ACID is the four-part promise a database makes to you every time you run a transaction. Think of it as a bank's promise when you transfer money: either the full transfer happens or nothing does — you'll never lose money into thin air.

Atomicity means the transaction is all-or-nothing. If you're moving £500 from Account A to Account B and the server crashes after the debit but before the credit, atomicity rolls the debit back. No money disappears.

Consistency means the database only moves from one valid state to another. If your schema says 'balance cannot be negative', a consistent database will reject any transaction that violates that — even mid-flight.

Isolation means concurrent transactions don't see each other's dirty work. If two cashiers are processing transactions simultaneously, isolation makes it look like they ran one after the other, not at the same time.

Durability means once the database says 'committed', the data survives a crash. It achieves this through write-ahead logs — it writes the intention to disk before actually doing it.

Interviewers love asking which property is hardest to implement. The answer is Isolation, because perfect isolation requires serialisable transactions which kills performance, so databases let you tune it with isolation levels.

-- Scenario: Production-grade bank transfer logic
-- Package: io.thecodeforge.db.transactions

BEGIN TRANSACTION;

    -- Step 1: Debit the sender's account
    -- We assume a CHECK constraint on 'balance >= 0' exists for Consistency
    UPDATE io_thecodeforge.bank_accounts
    SET balance = balance - 500
    WHERE account_id = 'ACC-001';

    -- Step 2: Check the balance explicitly for custom business logic
    -- If this fails, the whole transaction rolls back (Atomicity)
    IF (SELECT balance FROM io_thecodeforge.bank_accounts WHERE account_id = 'ACC-001') < 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT 'Transfer failed: insufficient funds. No changes were saved.';
        RETURN;
    END

    -- Step 3: Credit the receiver's account
    UPDATE io_thecodeforge.bank_accounts
    SET balance = balance + 500
    WHERE account_id = 'ACC-002';

COMMIT TRANSACTION;
-- Durability: The DBMS flushes the WAL (Write-Ahead Log) to disk here.
PRINT 'Transfer committed successfully.';

Normalization Explained — From Messy Spreadsheet to Clean Schema

Normalization is the process of restructuring your database tables to eliminate data redundancy and prevent update anomalies. The best way to understand why it exists is to see what happens when you skip it.

Imagine a single Orders table where you store the customer's name, address, and email alongside every order they've ever placed. If a customer moves house, you need to update their address in 50 rows. Miss one? Congratulations, you now have inconsistent data — two 'truths' for the same customer.

Normalization solves this by separating concerns into distinct tables and linking them with keys. There are several normal forms, but interviewers focus on 1NF through 3NF.

1NF (First Normal Form): Every column holds atomic (indivisible) values, and each row is unique. No comma-separated lists in a single cell.

2NF (Second Normal Form): Achieves 1NF AND every non-key column is fully dependent on the entire primary key — not just part of it. This only matters for composite primary keys.

3NF (Third Normal Form): Achieves 2NF AND no non-key column depends on another non-key column (no transitive dependencies). Classic example: storing both ZipCode and City — City depends on ZipCode, not on the primary key directly.

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF. Interviewers may test whether you know it exists, even if you don't memorise the formal definition.

-- ============================================================
-- AFTER NORMALIZATION: Separated into three clean tables (3NF)
-- Package: io.thecodeforge.db.schema
-- ============================================================

-- Customers table: 3NF compliant, no transitive dependencies
CREATE TABLE io_thecodeforge.customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_zip  VARCHAR(10)  NOT NULL,
    customer_city VARCHAR(100) NOT NULL 
);

-- Products table: Centralized product definitions
CREATE TABLE io_thecodeforge.products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL
);

-- Orders table: Linked via Foreign Keys for Referential Integrity
CREATE TABLE io_thecodeforge.orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES io_thecodeforge.customers(customer_id),
    product_id  INT NOT NULL REFERENCES io_thecodeforge.products(product_id),
    quantity    INT NOT NULL,
    order_date  DATE NOT NULL
);

-- If a customer moves, we update ONE row. Zero Risk of Update Anomalies.
UPDATE io_thecodeforge.customers
SET customer_city = 'Manchester', customer_zip = 'M1 1AA'
WHERE customer_id = 42;

SELECT 'Update complete. Data integrity preserved.' AS result;

Indexes — The Phone Book Trick That Makes Queries 1000x Faster

An index is a separate data structure the database maintains alongside your table to make lookups faster. Without an index, a query like WHERE email = 'alice@example.com' forces the database to scan every single row — called a full table scan. With an index on email, the database jumps directly to the matching rows like finding a name in an alphabetically sorted phone book.

The two types interviewers always ask about are clustered and non-clustered indexes.

A clustered index physically reorders the rows in the table to match the index order. Because the rows themselves are the index, you can only have ONE clustered index per table. In most databases, the primary key is automatically the clustered index.

A non-clustered index is a separate structure that stores the indexed column values alongside pointers back to the actual rows. You can have many of these. Think of it as the index pages at the back of a textbook — the book (table) isn't reordered, but the index tells you exactly which page to turn to.

The performance trade-off is real: indexes speed up reads dramatically but slow down writes (INSERT, UPDATE, DELETE) because the database must update the index every time data changes. This is why you don't just index every column — you index the columns that appear in WHERE, JOIN, and ORDER BY clauses on your most frequent queries.

-- Scenario: High-traffic user lookup optimization
-- Package: io.thecodeforge.db.indexing

CREATE TABLE io_thecodeforge.user_sessions (
    session_id    BIGINT PRIMARY KEY,  -- Clustered index
    customer_email VARCHAR(255) NOT NULL,
    login_time    DATETIME NOT NULL
);

-- ============================================================
-- ANALYZE: Before adding a non-clustered index
-- ============================================================
EXPLAIN ANALYZE 
SELECT * FROM io_thecodeforge.user_sessions
WHERE customer_email = 'dev@thecodeforge.io';
-- Expect: Sequential Scan (O(N))

-- ============================================================
-- OPTIMIZE: Creating a B-Tree Non-Clustered Index
-- ============================================================
CREATE INDEX idx_user_sessions_email
    ON io_thecodeforge.user_sessions (customer_email);

-- ============================================================
-- VERIFY: After indexing
-- ============================================================
EXPLAIN ANALYZE 
SELECT * FROM io_thecodeforge.user_sessions
WHERE customer_email = 'dev@thecodeforge.io';
-- Expect: Index Seek/Scan (O(log N))

Joins, Keys & Isolation Levels — The Questions That Actually Catch People Out

Most candidates can explain INNER JOIN. Fewer can explain why NULL breaks a FOREIGN KEY check or what happens when two transactions read the same row simultaneously. These are the questions that separate solid candidates from exceptional ones.

Transaction isolation levels control how much one transaction can see of another's uncommitted work. The four standard levels (lowest to highest isolation) are: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

READ UNCOMMITTED allows dirty reads — you can see another transaction's changes before they commit. This is almost never safe. READ COMMITTED (the default in PostgreSQL) means you only see committed data, but a value you read once might change if you read it again in the same transaction (non-repeatable read). REPEATABLE READ (MySQL InnoDB default) guarantees the same value every time you read within a transaction, but phantom rows — new rows that match your WHERE clause — can still appear. SERIALIZABLE is the strictest: transactions execute as if they're running one at a time.

The practical impact: most web apps run fine at READ COMMITTED. Financial systems that require precise calculations (like generating an account statement mid-month) should use REPEATABLE READ or SERIALIZABLE. Higher isolation = more locking = lower throughput.

For keys: a PRIMARY KEY uniquely identifies a row and cannot be NULL. A FOREIGN KEY enforces referential integrity — you cannot add an order for a customer_id that doesn't exist in the customers table, and (depending on your ON DELETE rule) you can't delete a customer who still has orders.

-- Demonstrating Dirty Read Protection
-- Package: io.thecodeforge.db.concurrency

-- Transaction A: Updating stock (Uncommitted)
BEGIN TRANSACTION;
    UPDATE io_thecodeforge.inventory 
    SET stock_count = 0 
    WHERE product_id = 99;

-- Transaction B: Reading under READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT stock_count FROM io_thecodeforge.inventory WHERE product_id = 99;
    -- Result: 50 (Ignores Transaction A's uncommitted change)
COMMIT;

-- Transaction A fails/rolls back
ROLLBACK;

-- If Transaction B used READ UNCOMMITTED, it would have seen '0',
-- causing a 'Dirty Read' and potentially incorrect business logic.

Query Optimization & Execution Plans — Reading the Database's Mind

You can write the perfect query, but if the database optimizer decides to scan 10 million rows, your app will crawl. Understanding how to read an execution plan is what transforms a good developer into a senior one. Interviewers love asking you to optimize a slow query — they want to see your thought process, not a magic bullet.

An execution plan shows the steps the database takes to run your query: which indexes it uses, which join algorithms (hash join, nested loop, merge join), and the estimated row counts. The most common red flag is a 'Seq Scan' on a large table — that means you're missing an index or your WHERE clause isn't SARGable (Search ARGument Able).

SARGability is key: wrapping an indexed column in a function like LOWER(email) = 'alice@example.com' makes the index unusable because the database has to evaluate the function for every row. Instead, use LOWER(email) in a functional index, or store the lowercase value.

Other optimizations: avoid SELECT *, use covering indexes, and analyze statistics regularly. A query that ran fine on 1000 rows can blow up on 1 million if the optimizer chooses a different plan due to stale statistics.

-- Problem: Slow query with WHERE LOWER(email) = ?
-- Even if email is indexed, index won't be used (non-SARGable)

-- Before: Full table scan because of function on indexed column
EXPLAIN ANALYZE
SELECT * FROM io_thecodeforge.users
WHERE LOWER(email) = 'dev@thecodeforge.io';
-- Expect: Seq Scan on users (cost=1000..10000)

-- Fix: Create a functional index on LOWER(email)
CREATE INDEX idx_users_lower_email 
    ON io_thecodeforge.users (LOWER(email));

-- After: Index scan now works
EXPLAIN ANALYZE
SELECT * FROM io_thecodeforge.users
WHERE LOWER(email) = 'dev@thecodeforge.io';
-- Expect: Bitmap Index Scan on idx_users_lower_email

-- Alternative fix: Store lowercase email in a separate column and index that

What Is a DBMS? — The Answer That Separates Junior From Senior

Interviewers don't ask "What is a DBMS?" to hear a dictionary definition. They're testing if you understand why your application isn't a database. A DBMS is middleware that sits between raw storage and your code. It handles concurrent reads without corruption, enforces schemas so your ORM doesn't silently write garbage, and recovers your data when the power dies mid-write.

MySQL, PostgreSQL, SQLite — they're all DBMSs. But the important part isn't what they are. It's what they stop you from having to build. No shared filesystem locking. No manual crash recovery. No hand-rolled permission checks. When a junior says "a DBMS stores data," a senior says "a DBMS enforces the contract between your application and your data."

Here's the trap: if you don't understand the DBMS as a separate process, you'll start treating your database as a file cabinet. Next thing you know, you're writing stored procedures to do business logic because the ORM is too slow. That's when everything breaks.

// io.thecodeforge — cs-fundamentals tutorial

# A DBMS vs just writing to a file
# This naive approach is what you do without a DBMS
import json

user_data = {"id": 1, "email": "dev@example.com", "balance": 100}

# Without DBMS: manual file handling, no concurrency control
with open("/tmp/user.json", "w") as f:
    f.write(json.dumps(user_data))

# With DBMS: the system handles atomicity
# conn.execute("UPDATE users SET balance = balance - 10 WHERE id = 1")
# This either completes fully or rolls back completely

print("DBMS handles what raw files cannot: atomicity, isolation, crash recovery")

DBMS vs RDBMS — The Interview Question That Exposes Your SQL Lies

Every junior parrots this: "RDBMS stores data in tables with relationships." That's the textbook answer. The senior answer is: "RDBMS guarantees referential integrity using foreign keys; a plain DBMS doesn't."

Here's the difference that matters: a DBMS (like MongoDB or XML databases) lets you store whatever you want, wherever you want. No constraints. Fast writes, but you're on the hook for consistency. An RDBMS (PostgreSQL, MySQL) enforces relationships at the engine level. That foreign key you defined? It blocks you from deleting a customer with active orders. That unique constraint? It stops two users from registering the same email.

The interviewer is really asking: "Do you understand when to use which?" If you pick an RDBMS for a document store use case, you'll drown in schema migrations. If you pick a plain DBMS for financial transactions, you'll lose money. The correct answer: RDBMS when data integrity is non-negotiable (finance, orders, user profiles). Plain DBMS when speed and schema flexibility beat strictness (logs, analytics, session stores).

// io.thecodeforge — cs-fundamentals tutorial

# Simulating the RDBMS guarantee vs plain DBMS
# Without referential integrity, this succeeds silently
users = [{"id": 1, "name": "Alice"}]
orders = [{"user_id": 2, "total": 50}]  # user_id 2 doesn't exist
print("Plain DBMS: Order saved for non-existent user. No error.")

# With RDBMS foreign key constraint
# This SQL would fail:
# INSERT INTO orders (user_id, total) VALUES (2, 50);
# ERROR: insert or update on table "orders" violates foreign key constraint

print("RDBMS: Blocks the write. Data stays consistent.")

Triggers & Transactions — When the Database Fires Back

A trigger is a stored procedure that automatically executes in response to certain events on a table, like INSERT, UPDATE, or DELETE. The classic interview question asks you to write a trigger that updates the Emp table when Dep is updated: increment salary by a fixed amount for all employees in the affected department. Why is this dangerous in production? Because triggers are invisible side effects—they execute within the same transaction as the triggering statement. A poorly written trigger can lock rows, cascade unexpectedly, or cause a single update to bring down the entire database. The correct approach is to use AFTER UPDATE on Dep, join on dept_id, and apply the raise using UPDATE Emp SET salary = salary + increment WHERE dept_id IN (SELECT dept_id FROM inserted). Never forget the inserted pseudo-table: it holds the new values after the update, not the old ones. The key takeaway: triggers are tempting but they turn your schema into a black box—use them only when application-level logic cannot enforce the rule.

// io.thecodeforge — cs-fundamentals tutorial
// PostgreSQL-style trigger on Dep update
CREATE OR REPLACE FUNCTION raise_emp_salary()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Emp
    SET salary = salary + 5000
    WHERE dept_id = NEW.dept_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_dep_update
AFTER UPDATE ON Dep
FOR EACH ROW
EXECUTE FUNCTION raise_emp_salary();

Above-Average Students — The Subquery That Separates Insight from Noise

Finding students whose marks exceed the class average is a deceptively simple subquery problem. The naive approach—SELECT * FROM Students WHERE Marks > AVG(Marks)—fails because aggregate functions cannot appear directly in WHERE clauses. Why? Because WHERE filters rows one at a time, while AVG() requires a full scan first. The correct solution uses a subquery: SELECT Student, Marks FROM Students WHERE Marks > (SELECT AVG(Marks) FROM Students). That inner SELECT runs once, computes the global average, and the outer query compares each row against that single value. Interviewers love this question because it tests whether you understand the order of operations in SQL: WHERE runs after FROM but before GROUP BY and aggregates. A correlated subquery would be needless here—it would recompute the average for every row, killing performance. The key insight: always ask yourself whether the subquery is static (runs once) or correlated (runs per row). For above-average students, the answer is static.

// io.thecodeforge — cs-fundamentals tutorial
-- Find students above class average
SELECT Student, Marks
FROM Students
WHERE Marks > (
    SELECT AVG(Marks) FROM Students
);

Third-Highest Salary — The Nested Query That Exposes Your Rank

Finding the employee with the third-highest salary tests your ability to think in sets, not loops. The most intuitive approach uses a subquery with DISTINCT and LIMIT/OFFSET: SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 2 returns the third-highest unique salary. Then the outer query fetches the employee: SELECT FROM Employee WHERE salary = (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 2). But this breaks if multiple employees share that salary—you get all of them, which might be correct or not depending on the interview twist. A more robust method uses a correlated subquery: SELECT FROM Employee e1 WHERE 2 = (SELECT COUNT(DISTINCT e2.salary) FROM Employee e2 WHERE e2.salary > e1.salary). Why does this work? It counts how many distinct salaries are strictly greater than the current row's salary; if exactly two, you're third. The key takeaway: always clarify whether ties should be included or skipped. The industry standard for 'Nth highest' is DENSE_RANK() or ROW_NUMBER(), but interviewers often want to see you build it from subqueries first.

// io.thecodeforge — cs-fundamentals tutorial
-- Using correlated subquery for third-highest salary
SELECT Employee_Name, Salary
FROM Employee e1
WHERE 2 = (
    SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee e2
    WHERE e2.Salary > e1.Salary
);

Distributed Database Interview Questions: CAP, Consistency, Sharding

Distributed databases are a hot topic in DBMS interviews, especially for senior roles. The CAP theorem states that a distributed system can only guarantee two of three properties: Consistency, Availability, and Partition Tolerance. In practice, partition tolerance is non-negotiable, so you choose between CP (e.g., HBase) and AP (e.g., Cassandra). Consistency models range from strong (linearizability) to eventual (e.g., DNS). Sharding splits data across nodes; common strategies include range-based (e.g., by user ID range) and hash-based (e.g., consistent hashing). A typical question: 'How would you shard a user table to handle 100 million users?' Answer: Use a hash on user_id modulo N shards, but rebalancing is costly. Better: consistent hashing with virtual nodes. Another trap: 'What is the difference between vertical and horizontal scaling?' Vertical = bigger machine; horizontal = more machines. For sharding, horizontal scaling is key. Example: A social media app shards posts by user_id hash. When a user posts, the write goes to the shard responsible for that user. Reads also hit that shard. But if a user becomes viral, that shard may overload. Solution: re-shard or use read replicas. Interviewers also ask about distributed transactions: Two-Phase Commit (2PC) ensures atomicity but blocks on coordinator failure; Three-Phase Commit (3PC) reduces blocking but is more complex. Practical tip: In microservices, prefer eventual consistency with Saga patterns over 2PC for scalability.

# Consistent hashing example
import hashlib

class ConsistentHash:
    def __init__(self, nodes=None, replicas=3):
        self.replicas = replicas
        self.ring = {}
        self.sorted_keys = []
        if nodes:
            for node in nodes:
                self.add_node(node)

    def add_node(self, node):
        for i in range(self.replicas):
            key = self._hash(f"{node}:{i}")
            self.ring[key] = node
            self.sorted_keys.append(key)
        self.sorted_keys.sort()

    def remove_node(self, node):
        for i in range(self.replicas):
            key = self._hash(f"{node}:{i}")
            del self.ring[key]
            self.sorted_keys.remove(key)

    def get_node(self, key):
        if not self.ring:
            return None
        hash_key = self._hash(key)
        for k in self.sorted_keys:
            if hash_key <= k:
                return self.ring[k]
        return self.ring[self.sorted_keys[0]]

    @staticmethod
    def _hash(key):
        return int(hashlib.md5(key.encode()).hexdigest(), 16)

# Usage
ch = ConsistentHash(['node1', 'node2', 'node3'])
print(ch.get_node('user123'))  # Returns a node

System Design Database Questions: Schema Design, Indexing Strategy

System design interviews often include database schema design and indexing strategy. For example, design a database for a ride-sharing app like Uber. Key entities: Users, Drivers, Rides, Payments. Schema: Users (user_id, name, phone, rating), Drivers (driver_id, name, phone, vehicle, rating, location), Rides (ride_id, user_id, driver_id, pickup, dropoff, status, fare, timestamp). Indexing strategy: For frequent queries like 'find nearby drivers', use a spatial index (e.g., PostGIS GiST index on location). For 'get ride history by user', index on user_id. For 'active rides by driver', index on driver_id and status. Avoid over-indexing: each index slows writes. Use composite indexes for multi-column filters (e.g., (status, timestamp) for 'pending rides ordered by time'). Denormalization: store driver's current location in a separate table updated via GPS, or use Redis for real-time location. Another common question: 'How would you design a URL shortener like bit.ly?' Schema: urls (id, long_url, short_code, created_at). Index on short_code for fast lookup. Use hash function (e.g., MD5) to generate short code, handle collisions with retry. For high read throughput, add caching layer (Redis) with TTL. For write-heavy systems, consider sharding by short_code hash. Also, discuss trade-offs: normalization vs denormalization. In a news feed, denormalize user names into posts to avoid joins, but at cost of data redundancy. Interviewers look for practical reasoning: 'When would you use a NoSQL database?' Answer: when schema is flexible, need horizontal scaling, or require high write throughput (e.g., Cassandra for time-series data).

-- Ride-sharing schema
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20) UNIQUE,
    rating DECIMAL(2,1) DEFAULT 5.0
);

CREATE TABLE Drivers (
    driver_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20) UNIQUE,
    vehicle VARCHAR(50),
    rating DECIMAL(2,1) DEFAULT 5.0,
    location GEOGRAPHY(Point)  -- PostGIS spatial type
);

CREATE TABLE Rides (
    ride_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    driver_id INT REFERENCES Drivers(driver_id),
    pickup GEOGRAPHY(Point),
    dropoff GEOGRAPHY(Point),
    status VARCHAR(20) CHECK (status IN ('requested','accepted','in_progress','completed','cancelled')),
    fare DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_rides_user_id ON Rides(user_id);
CREATE INDEX idx_rides_driver_status ON Rides(driver_id, status);
CREATE INDEX idx_drivers_location ON Drivers USING GIST (location);  -- spatial index

Performance Tuning Interview Scenarios

Performance tuning is a common interview topic. Scenario: 'A query that joins three tables is running slowly. How do you debug?' Steps: 1) Use EXPLAIN to get the query plan. Look for sequential scans, high row estimates, or nested loops. 2) Check if indexes exist on join columns and WHERE filters. 3) Consider rewriting the query: use subqueries or CTEs to reduce data volume. 4) Check for missing statistics (run ANALYZE). 5) If the plan shows a bad join order, try setting join_collapse_limit. Example: SELECT * FROM orders JOIN customers ON orders.cust_id = customers.id WHERE customers.region = 'US'; If orders has 10M rows and customers 1M, a nested loop join would be slow. Ensure index on customers.region and orders.cust_id. Another scenario: 'A write-heavy table has many indexes causing slow inserts.' Solution: drop unused indexes, batch inserts, or use partitioning. For example, partition by date: CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); This reduces index size per partition. Also, consider using UNLOGGED tables for temporary data. Another common issue: 'Deadlocks in high-concurrency systems.' Debug by enabling log_lock_waits and using pg_locks. Solution: ensure consistent lock ordering in transactions. For example, always update accounts in the same order (e.g., by account_id). Use SELECT ... FOR UPDATE NOWAIT to avoid waiting. Performance tuning also involves hardware: SSD vs HDD, memory allocation for shared_buffers, and connection pooling (e.g., PgBouncer). Interviewers may ask: 'How would you tune PostgreSQL for a reporting workload?' Answer: increase work_mem for sorts, use parallel query, and materialized views for pre-aggregated data.

-- Step 1: Get query plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders o
JOIN customers c ON o.cust_id = c.id
WHERE c.region = 'US';

-- Step 2: Check for missing indexes
CREATE INDEX idx_customers_region ON customers(region);
CREATE INDEX idx_orders_cust_id ON orders(cust_id);

-- Step 3: Rewrite query to reduce rows early
WITH us_customers AS (
    SELECT id FROM customers WHERE region = 'US'
)
SELECT * FROM orders o
JOIN us_customers c ON o.cust_id = c.id;

-- Step 4: Analyze tables
ANALYZE customers, orders;

-- Step 5: Check for deadlocks
SELECT * FROM pg_locks WHERE NOT granted;
-- Enable deadlock logging in postgresql.conf: log_lock_waits = on