SQL20 interview questions

SQL Query Optimization for High-Volume BFSI Workloads

Indexing strategies, execution plan analysis, partitioning, and write-heavy optimization patterns for financial transaction tables with 100M+ rows.

SQLIndexingExecution PlansPartitioning

SQL Query Optimization for High-Volume BFSI Workloads

Understanding Execution Plans

Always start with EXPLAIN ANALYZE:

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT t.id, t.amount, a.account_number
FROM transactions t
JOIN accounts a ON t.account_id = a.id
WHERE t.created_at >= '2024-01-01'
  AND t.status = 'SETTLED';

Key nodes to watch: Seq Scan (bad on large tables), Hash Join vs Nested Loop, Sort operations.

Indexing Strategies for Transactions

sql

-- Composite index for common filter patterns
CREATE INDEX idx_txn_status_date
ON transactions(status, created_at DESC)
WHERE status IN ('PENDING', 'SETTLED');

-- Partial index for active records only
CREATE INDEX idx_txn_pending
ON transactions(account_id, created_at)
WHERE status = 'PENDING';

-- Include columns to enable index-only scans
CREATE INDEX idx_txn_account_covering
ON transactions(account_id)
INCLUDE (amount, currency, status);

Table Partitioning

sql

-- Range partition by month for transactions
CREATE TABLE transactions (
    id BIGSERIAL,
    account_id BIGINT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions_2024_01
PARTITION OF transactions
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Interview Questions

1.When does an index NOT get used by the query planner?
2.What is the difference between a clustered and non-clustered index?
3.How do you optimize a query that cannot be changed (legacy code)?
4.What is index bloat and how do you manage it?
5.How do covering indexes reduce I/O?