Question 1

What is the difference between SQL and NoSQL databases?

Accepted Answer

SQL databases (PostgreSQL, MySQL) store rows in fixed schemas, support multi-row ACID transactions, and use SQL with joins. NoSQL is an umbrella for document stores (MongoDB), key-value stores (Redis, DynamoDB), wide-column stores (Cassandra), and graph databases (Neo4j). NoSQL trades joins and strict schemas for horizontal scalability, flexible documents, or specialized access patterns.

Question 2

How do database indexes work?

Accepted Answer

An index is a separate data structure that maps column values to row locations so the database can find rows without scanning the whole table. B-tree indexes (the default) keep keys sorted and support equality and range queries in O(log n). Hash indexes support only equality lookups but in O(1). LSM trees (used by Cassandra, RocksDB) optimize writes by buffering in memory and flushing sorted files to disk.

Question 3

What are database isolation levels?

Accepted Answer

Isolation levels define what concurrent transactions can see of each other. Read Uncommitted allows dirty reads; Read Committed blocks dirty reads; Repeatable Read blocks non-repeatable reads; Serializable blocks phantom reads and behaves as if transactions ran one at a time. PostgreSQL defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read, and most distributed databases offer Snapshot Isolation.

Question 4

When should I shard a database?

Accepted Answer

Shard when a single primary node cannot handle the write throughput, when the dataset is too large to fit on one disk, or when you need data residency in specific regions. Before sharding, exhaust simpler options: read replicas, vertical scaling, partitioning within a single node, archiving cold data, and a cache. Sharding adds operational complexity around resharding, cross-shard queries, and distributed transactions.

Question 5

PostgreSQL vs MongoDB: which should I use?

Accepted Answer

Use PostgreSQL when you need joins, multi-row ACID transactions, a strict schema, rich SQL, and proven OLTP performance. Use MongoDB when your data is naturally hierarchical (documents), the schema evolves often, you need horizontal sharding out of the box, or your access pattern is dominated by single-document reads and writes. Modern PostgreSQL also supports JSONB, narrowing the gap considerably.

Question 6

What is a write-ahead log in a database?

Accepted Answer

A write-ahead log (WAL) is an append-only log that records every change before it is applied to the data files. On crash, the database replays the WAL to recover committed transactions and roll back incomplete ones. PostgreSQL, MySQL InnoDB (redo log), and SQLite all use WAL, and replication is typically built by streaming the WAL to followers.

Database Engineering

Debezium and the Outbox Pattern: The Real Impact on Your Postgres Database

PostgreSQL Internals: How Queries Actually Execute

How Database Locks Work: A Complete Guide

MongoDB Cheat Sheet: 100+ Commands with Real Examples

Redis vs DragonflyDB vs KeyDB: Best Redis Alternative in 2026?

When to Use PostgreSQL vs MongoDB vs DynamoDB (2026 Guide)

PostgreSQL Cheat Sheet: 100+ Commands with Real Examples

How Databases Actually Store Data Internally

Django PostgreSQL Setup: From Zero to Production

How Database Indexing Works

Postgres 18: The Release That Makes Databases Fast Again

Subscribe via RSS Feed