Databases

33 guides

Transactions, isolation, indexing and the storage trade-offs interviewers probe.

ACID transactionsACID describes atomicity, consistency, isolation, and durability: a transaction is a boundary around state changes, not a magic guarantee that every business workflow is safe.Data consistency3 min read Transaction isolation levelsAn isolation level defines which effects concurrent transactions may observe, trading anomalies against blocking, aborts, and database overhead.Data consistency3 min read Read committed vs repeatable readRead committed gives each statement a current committed view, while repeatable read keeps a transaction-level snapshot or equivalent guarantee; neither automatically fixes every write race.Data consistency3 min read Serializable isolationSerializable isolation makes concurrent execution equivalent to some serial order, using locks, validation, or aborts to prevent anomalies such as write skew.Data consistency3 min read MVCCMulti-version concurrency control lets readers use a consistent snapshot while writers create newer row versions, reducing read–write blocking at the cost of version cleanup.Data consistency3 min read Write-ahead loggingWrite-ahead logging records a change in durable log storage before the corresponding data page is flushed, allowing crash recovery to replay or undo work.Data consistency3 min read Two-phase commitTwo-phase commit coordinates several resource managers through prepare and commit phases, preserving atomic decision at the cost of blocking and coordinator dependence.Data consistency3 min read Saga patternA saga splits a distributed business transaction into local commits and compensating actions, making progress without holding a global lock.Data consistency3 min read Transactional outboxA transactional outbox stores the intended message beside the business write, then publishes it asynchronously so a crash cannot lose the intent between two systems.Data consistency3 min readWith code example Lost update problemA lost update occurs when two writers read the same old value and the later write silently overwrites the earlier decision.Data consistency3 min readWith code example Phantom readA phantom read occurs when a repeated range query sees rows appear or disappear because another transaction inserted, deleted, or changed rows matching the predicate.Data consistency3 min read Dirty readA dirty read observes another transaction’s uncommitted change, which may later roll back and was never a valid committed state.Data consistency3 min read Database indexingAn index is a separate ordered structure that lets the database find rows without scanning the table — paid for with storage and slower writes.Databases8 min readWith code example Composite index designA multi-column index is sorted by its first column, then the second within that, and so on — which is why column order determines which queries it can serve.Databases7 min readWith code example Query execution plansAn execution plan is the tree of operations the database chose; reading it well means comparing what it expected against what actually happened.Databases7 min readWith code example The N+1 query problemOne query fetches N rows, then one more query runs per row — so the endpoint issues N+1 queries where one or two would do.Databases7 min readWith code example Database normalizationNormalization removes redundancy so that every fact is stored exactly once — which is what makes update, insert and delete anomalies impossible.Databases7 min readWith code example DenormalizationDenormalization deliberately duplicates or precomputes data to make reads cheaper, and the price is an ongoing obligation to keep the copies in agreement.Databases7 min readWith code example Database constraintsA constraint is an invariant the database enforces on every write, from every client, including the ones that bypass your application.Databases7 min readWith code example Foreign key designA foreign key guarantees the referenced row exists; the design decisions are what happens on delete, and what it locks along the way.Databases7 min readWith code example Table partitioningPartitioning splits one logical table into physical pieces within a single database, so queries can skip pieces and maintenance can work on one at a time.Databases7 min readWith code example Database shardingSharding splits data across independent databases to scale writes and storage, at the cost of cross-shard joins, transactions and operational simplicity.Databases7 min readWith code example Vacuum and bloatMVCC makes updates create new row versions rather than overwriting, and vacuum is what reclaims the old ones once no transaction can still see them.Databases7 min readWith code example Keyset paginationKeyset pagination asks for rows after a known position rather than skipping a count, so page cost is constant and results stay stable while data changes.Databases7 min readWith code example Connection poolingA pool reuses a fixed set of open connections so requests skip the handshake — and the fixed size is a deliberate capacity limit, not a constraint to raise away.Databases7 min readWith code example Strong consistencyStrong consistency means every read observes the most recent completed write, so the system behaves as if there were a single copy of the data.Data consistency7 min readWith code example Eventual consistencyEventual consistency guarantees replicas converge to the same value once writes stop — with no promise about when, or what you read before then.Data consistency7 min readWith code example Optimistic locking vs Pessimistic lockingOptimistic locking wins under low contention and loses badly under high contention. The contention threshold that decides it, what each costs, and how to implement both correctly.Data consistency6 min readWith code example SQL databases vs NoSQL databasesSQL is the default for relational invariants and joins; NoSQL wins for a known access pattern and elastic scale. Compare the decision axes before choosing.Databases3 min read PostgreSQL vs MySQLPostgreSQL is a strong default for rich SQL and correctness; MySQL is a strong operational choice for familiar web workloads. Compare transactions, indexing, and ecosystem fit.Databases3 min read Offset pagination vs Keyset paginationOffset pagination is convenient for small, stable result sets; keyset pagination stays fast and consistent deep into a changing dataset. Compare cursors and UX trade-offs.Databases3 min read Normalization vs DenormalizationNormalize authoritative relational data by default; denormalize measured read paths when latency or scale justifies duplicate state and repair work.Databases3 min read Index scan vs Full table scanIndexes win for selective lookups; full scans win when a large share of rows is needed. Read the query plan and account for maintenance and cache locality.Databases3 min read