SQL vs NoSQL, Indexing & Query Tuning — Interview Questions

How to use this lesson

Database questions at senior level come down to one loop:

1. Understand the access patterns.
2. Model and index for them.
3. Verify with execution plans.
4. Measure.

Always name the database engine you have in mind: PostgreSQL and MySQL behave differently in important ways.

Q1. SQL or NoSQL: how do you decide? When would you use MongoDB?

Short answer:

• Relational (SQL):

  • ACID transactions across tables;
  • joins and ad-hoc queries;
  • strong schema and constraints (integrity enforced by the database);
  • mature tooling.

  The default for transactional business data (orders, payments, inventory). Scale it with read replicas, partitioning, and eventually sharding (Citus, Vitess) or distributed SQL (CockroachDB, YugabyteDB, Spanner).

• NoSQL, by family:

  • document (MongoDB): flexible, nested aggregates;
  • key-value (Redis, DynamoDB): simple lookups at massive scale;
  • wide-column (Cassandra): write-heavy, time-series or partitioned data;
  • search (Elasticsearch);
  • graph (Neo4j).

  They trade joins and (sometimes) multi-entity transactions for horizontal scale, flexible schemas, and access-pattern-specific performance.

• MongoDB fits when:

  • data is naturally document-shaped and read and written as an aggregate (a product catalogue with variable attributes, CMS content, user profiles, event payloads);
  • the schema evolves quickly;
  • you need horizontal sharding, with document-level atomicity (it also has multi-document transactions since 4.0, but design to avoid needing them).

• Avoid MongoDB for highly relational data with many cross-entity invariants and reporting joins.

Decide by: access patterns, consistency needs, query flexibility, scale, and team expertise. Many systems are polyglot: Postgres for transactions, Redis for caching, Elasticsearch for search.

Learn it in depth → SQL vs NoSQL Trade-offs

Q2. How do indexes improve performance? What is a composite (compound) index?

Short answer: An index is an auxiliary sorted structure (usually a B+ tree) mapping key values to row locations. Lookups, range scans and ordered reads become O(log n) plus the matching rows, instead of full table scans. The costs: extra storage, and slower writes (every insert or update maintains every index).

A composite index covers several columns in order, (customer_id, status, created_at):

• it serves queries that filter on a leftmost prefix (customer_id, or customer_id + status, …);
• it supports ordering by the next column (… order by created_at desc, without a sort step);
• a query on status alone can't use it efficiently (except with skip scans in some engines);
• column order matters: equality columns first, then the range or sort column. Highly selective columns are usually first, but access patterns come first.

A covering index includes every column the query needs (INCLUDE (total) in Postgres and SQL Server), which gives index-only scans, with no table access.

Learn it in depth → Indexes & Query Performance

Q3. What's a good indexing strategy?

Short answer:

• Index for real queries: derive the indexes from the WHERE, JOIN, ORDER BY and GROUP BY clauses of your top queries (by frequency × cost, from pg_stat_statements or the slow log).
• Primary and foreign keys: index FK columns (Postgres doesn't do it automatically), for joins and cascading deletes.
• Composite indexes over many single-column ones. Order the columns by equality → range or sort.
• Selective indexes, not low-cardinality columns alone (a boolean flag), unless it's a partial index (WHERE status = 'PENDING').
• Covering indexes for hot read paths. Expression indexes for functions (lower(email)).
• Avoid over-indexing on write-heavy tables. Drop unused indexes (pg_stat_user_indexes.idx_scan = 0).
• Build indexes concurrently on live systems (CREATE INDEX CONCURRENTLY), and monitor index bloat and fragmentation.
• Verify with EXPLAIN (ANALYZE, BUFFERS).

Q4. What is a query execution plan? How do you analyse and optimise a slow query?

Short answer: The execution plan is the optimiser's chosen strategy for a query:

• the access paths (sequential scan, index scan, index-only scan, bitmap scan);
• the join algorithms (nested loop, hash, merge) and the join order;
• sorts and aggregations;
• the estimated rows and costs.

EXPLAIN ANALYZE runs the query, and shows actual rows and timings per node.

Slow-query workflow:

1. Find it: the slow query log (log_min_duration_statement, MySQL's slow_query_log), pg_stat_statements, and APM traces.
2. Read the plan:
   • estimated vs actual rows (bad estimates point to stale statistics or correlated columns);
   • sequential scans on big tables;
   • nested loops over large inputs;
   • sorts spilling to disk (work_mem);
   • huge buffer reads.
3. Fix it:
   • add or adjust indexes (composite, covering, partial);
   • rewrite non-sargable predicates (WHERE date(created_at) = … becomes a range; avoid leading-wildcard LIKE, and implicit casts);
   • replace OR with UNION ALL, and correlated subqueries with joins or EXISTS;
   • select fewer columns;
   • keyset pagination instead of a deep OFFSET;
   • ANALYZE or update the statistics (and extended statistics);
   • denormalise or pre-aggregate (materialised views) for reporting;
   • move reports to replicas or an OLAP store.
4. Verify with plans and timings on production-like data, and add a regression test or monitor.

Learn it in depth → Query Execution Plans

Q5. What is denormalisation, and when should you denormalise?

Short answer: Denormalisation deliberately duplicates or pre-computes data (redundant columns, summary tables, embedded documents), so reads avoid expensive joins or aggregations, at the cost of more storage, and harder writes (keeping the copies consistent). Denormalise when:

• read performance of a critical, frequent query dominates, and indexes aren't enough;
• you need reporting or dashboards (pre-aggregated daily totals);
• in NoSQL or microservices, where joins across services or partitions are impossible (read models, and CQRS);
• historical snapshots must be kept (for example, the order line's price at purchase time, which is actually correct modelling, not just denormalisation).

Keep the copies consistent with transactions, triggers, CDC or events, and document the source of truth.

Learn it in depth → Denormalization & Schema Trade-offs

Q6. What are the ACID properties?

Short answer:

• Atomicity: all of a transaction's operations happen, or none do (implemented through undo logs, or the WAL).
• Consistency: a transaction takes the database from one valid state to another, respecting constraints, FKs and invariants.
• Isolation: concurrent transactions don't see each other's intermediate states, to the degree defined by the isolation level (implemented through MVCC and locks).
• Durability: once committed, the changes survive crashes (the write-ahead log, fsync, replication).

In distributed systems, you often relax these to BASE (Basically Available, Soft state, Eventually consistent), with sagas and idempotency.

Learn it in depth → ACID Properties

Q7. How do you prevent dirty reads?

Short answer: A dirty read is reading another transaction's uncommitted changes, which may later be rolled back. You prevent it with an isolation level of READ COMMITTED or higher, which is the default in Postgres, Oracle and SQL Server (MySQL defaults to REPEATABLE READ). MVCC databases serve each query a snapshot of committed data, so readers never see uncommitted rows, and don't block writers. Never use READ UNCOMMITTED (or NOLOCK hints in SQL Server) for business logic. Related anomalies need higher levels or explicit locks:

• non-repeatable reads and phantoms: REPEATABLE READ or SERIALIZABLE;
• lost updates: @Version, or SELECT … FOR UPDATE.

Q8. Full-text search vs SQL LIKE: what's the difference? What is an inverted index?

Short answer:

• LIKE '%term%': substring matching, with no linguistic understanding. A leading wildcard can't use a B-tree index, so it's a full scan. There's no relevance ranking, no stemming, and no typo tolerance.
• Full-text search (Postgres tsvector/GIN, MySQL FULLTEXT, Elasticsearch/OpenSearch, Lucene):
  • text is tokenised and normalised (lowercasing, stemming, stop words, synonyms);
  • it's indexed in an inverted index;
  • queries return relevance-ranked results (BM25), with phrases, fuzzy matching, highlighting and facets.
• An inverted index maps each term → the list of documents (and positions) containing it, like a book's index. A query intersects or unions the posting lists, which is very fast, however large the corpus. It's the core of search engines. Trigram indexes (pg_trgm) can speed up LIKE/ILIKE and fuzzy matching in Postgres.

Q9. B-tree vs LSM tree?

Short answer:

• B-tree / B+ tree (Postgres, MySQL InnoDB, Oracle):
  • updates happen in place, in fixed-size pages;
  • reads are fast and predictable (O(log n), shallow trees);
  • writes cause random I/O and page splits, and write amplification from page rewrites plus the WAL.
  • Best for read-heavy, mixed OLTP workloads, and range queries.
• LSM tree (Log-Structured Merge; Cassandra, RocksDB, ScyllaDB, HBase, LevelDB):
  • writes go to an in-memory memtable, plus a sequential log, and are flushed as immutable sorted files (SSTables), which are later compacted;
  • very high write throughput (sequential I/O);
  • reads may check several files (read amplification, mitigated by Bloom filters and compaction);
  • compaction causes background write amplification, and space amplification.
  • Best for write-heavy workloads: time-series, logs, events, IoT.

Q10. What is index fragmentation, and what is partition pruning?

Short answer:

• Index fragmentation (bloat):
  • B-tree pages become partially empty, or out of order, after many updates or deletes and page splits. Postgres MVCC leaves dead tuples until vacuum.
  • The consequences: more pages to read, a larger index, and worse cache efficiency.
  • Monitor it: pgstattuple/bloat queries, and sys.dm_db_index_physical_stats in SQL Server.
  • Fix it: Postgres autovacuum tuning, REINDEX CONCURRENTLY, pg_repack; SQL Server ALTER INDEX REORGANIZE/REBUILD; OPTIMIZE TABLE in MySQL.
  • Avoid it: use sequential or time-ordered keys (UUIDv7 or ULID instead of random UUIDv4, which cause random page splits), and an appropriate fillfactor.
• Partition pruning: with a partitioned table (for example by month, on created_at), the planner skips partitions that can't match the WHERE clause (created_at >= '2026-09-01' scans only September). It needs the partition key in the predicate, and literal or parameter values (runtime pruning handles parameters). It makes queries on huge tables fast, and makes retention trivial (drop old partitions).

Q11. What is a materialised view?

Short answer: A query result stored physically as a table (CREATE MATERIALIZED VIEW daily_sales AS SELECT …). Reads are fast (and can be indexed), because the expensive join or aggregation is precomputed. It must be refreshed:

• Postgres: REFRESH MATERIALIZED VIEW [CONCURRENTLY], which is manual or scheduled, and recomputes everything;
• Oracle: fast refresh, with materialised view logs;
• SQL Server: indexed views, maintained automatically.

Uses: dashboards, reporting, and expensive aggregations tolerant of staleness. The alternatives are summary tables maintained by triggers, events or CDC, or an OLAP store.

Q12. OLTP vs OLAP?

Short answer:

• OLTP (online transaction processing): many small, concurrent transactions (placing orders, updating balances), normalised schemas, row-oriented storage, index lookups, strong consistency, and low latency. PostgreSQL, MySQL, Oracle.
• OLAP (online analytical processing): few large queries scanning and aggregating millions or billions of rows (reports, BI, analytics), denormalised star or snowflake schemas, columnar storage (compression, vectorised execution), batch or streaming loads. Snowflake, BigQuery, Redshift, ClickHouse, Druid, DuckDB.
• Scaling each: OLTP through vertical scaling, replicas, partitioning and sharding. OLAP through MPP, columnar engines, separated storage and compute, and pre-aggregation.
• Never run heavy analytics on the OLTP primary. Ship the data (CDC or ETL/ELT) to the warehouse. HTAP systems (TiDB, SingleStore, AlloyDB) try to do both.

Q13. How do you prevent database bottlenecks?

Short answer:

• Queries: index for the top queries, eliminate N+1, use projections, keyset pagination, and batch writes (JDBC batching).
• Connections: right-size the pools (small pools are often faster). Keep the total connections across all instances below the database limits. Use a proxy (PgBouncer, RDS Proxy). Keep transactions short, with no remote calls inside them.
• Load distribution: read replicas for reads that tolerate lag, caching (Redis, CDN), and moving analytics off the primary (to a warehouse).
• Write scaling: asynchronous processing through queues, partitioning, then sharding or distributed SQL when a single node is maxed out.
• Contention: avoid hot rows (counters, which you can shard, or aggregate asynchronously), lock ordering, and optimistic concurrency.
• Maintenance: vacuum, statistics, bloat control, and archiving old data.
• Observability: slow logs, pg_stat_statements, pool metrics, lock waits, and replication lag.

Q14. What is the slow query log? What is slow-query analysis?

Short answer: The slow query log records statements that exceed a duration threshold (MySQL slow_query_log plus long_query_time; Postgres log_min_duration_statement, and auto_explain to log the plans too). Slow-query analysis means:

1. aggregating the statements by normalised fingerprint (pt-query-digest, pg_stat_statements), and ranking them by total time (frequency × latency), not just the slowest single query;
2. explaining the top offenders with EXPLAIN ANALYZE;
3. fixing them (indexes, rewrites, caching);
4. tracking the regressions after deployments.

Combine it with APM to connect queries to endpoints.

Q15. What is query optimisation, in general terms?

Short answer: Making queries return the same results with less work:

• Reduce the data touched: selective predicates, indexes, partition pruning, fewer columns.
• Help the optimiser: fresh statistics, sargable predicates, avoiding functions on indexed columns, correct data types (no implicit casts), and parameterised queries (a stable plan cache; watch for parameter-sniffing issues in SQL Server).
• Choose better shapes: EXISTS vs IN vs joins, pre-aggregation, CTEs or lateral joins where they help, and avoiding SELECT *.
• Reduce round trips: batching, and set-based operations, instead of row-by-row loops.
• Architecture: caching, materialisation, replicas, and specialised stores (search, OLAP).

Follow-up questions this topic invites — and their answers

Q: Why might a database not use an index you created? A: The predicate isn't sargable (a function, a leading wildcard, a type mismatch), the column order doesn't match (not a leftmost prefix), low selectivity makes a sequential scan cheaper, statistics are stale, the table is small, or parameter types differ from the column type.

Q: UUID vs auto-increment primary keys? A: Auto-increment IDs are compact and sequential (index-friendly), but guessable, and centralised. Random UUIDv4 values fragment B-trees and bloat indexes. UUIDv7/ULID are time-ordered, globally unique and index-friendly, which makes them a good default for distributed systems.

Q: What is MVCC? A: Multi-Version Concurrency Control. Writers create new row versions, and readers see a consistent snapshot of committed versions without blocking. Old versions are cleaned later (vacuum in Postgres, undo in InnoDB and Oracle). It gives high concurrency for mixed workloads.

Q: When is a partial index useful? A: When queries target a small subset (WHERE status = 'PENDING', or deleted_at IS NULL). The index stays small and hot. It's also useful for unique constraints on a subset (for example, one active subscription per user).