Summary: You can write clean APIs, elegant business logic and a flawless deployment pipeline, and still have a backend that collapses under real traffic — because the database is the foundation that decides whether the system stays fast, reliable and scalable. These ten concepts are the vocabulary of that foundation. Each one is a trade, not a free win, and most production database problems come from applying one of them without acknowledging what it costs.

This is the vault's reference page for the data layer. Several architecture arguments elsewhere — the shared-database question in Modular Monolith Instead of Microservices, the compensating transactions in What Senior Architects Know About SAGA That Juniors Don't, the soft-delete confusion in Why Modern APIs Don't Always Use PUT and DELETE — assume these mechanics.

1. Indexes — fast reads, slower writes

An index is the library catalogue. Without one, finding a user by email in a ten-million-row table may mean inspecting millions of rows; with one, the database jumps to the record almost instantly.

Indexes are not free. Every insert, update and delete must also update every index on that table, consuming write throughput and storage. Indexing every column speeds some reads and quietly degrades all writes.

Experienced developers do not add indexes by guesswork. They study how the application actually queries data, analyze the slow queries, and index where it measurably pays. Understanding indexes is often the fastest way to fix a slow application without touching business logic.

2. ACID — why databases can be trusted

Transfer ₹5,000 between accounts. The money leaves one account, and the database crashes before it reaches the other. That money cannot be allowed to vanish — which is what ACID exists to prevent.

  • Atomicity — a transaction is one unit. Everything completes, or nothing does. There is no half-finished transaction.
  • Consistency — every successful transaction leaves the database in a valid state, with integrity rules intact before and after.
  • Isolation — concurrent transactions do not interfere; each behaves as though executing alone.
  • Durability — once committed, the change survives an immediate server crash.

Banking, airline reservations, e-commerce, payment gateways and healthcare systems all depend on these guarantees. Most users never think about ACID; every successful online purchase relies on it.

3. Transactions — several operations as one

Very few real operations are a single query. Placing an order deducts inventory, writes an order row, records a payment and updates a total. A transaction groups these so they succeed or fail together, which is Atomicity in practice.

The distributed version of this problem is much harder, and is exactly why the SAGA pattern exists — see What Senior Architects Know About SAGA That Juniors Don't.

4. Isolation levels — concurrency versus correctness

One ticket remains. Two customers click Buy Now simultaneously. Without correct concurrency control, both may believe they bought it.

Isolation levels define how much one in-flight transaction can see of another. The four standard levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — are a sliding scale between throughput and consistency. Lower levels allow more concurrency and risk inconsistent reads; higher levels give stronger guarantees and require more coordination.

The skill is not picking the strongest level every time. It is knowing what the application actually needs: a social feed tolerates minor inconsistency nobody will notice; a payments ledger tolerates none. Recognizing that difference is one of the things that separates a backend engineer from someone who writes SQL.

5. Locks — and the transactions that hold them too long

When a transaction reads or modifies data, the database temporarily locks the affected rows — sometimes whole tables — so conflicting changes cannot interleave. Shared locks let several transactions read concurrently; exclusive locks let exactly one modify.

Modern engines handle this automatically, and developers rarely lock rows by hand. The failure mode is indirect: a transaction that stays open too long keeps holding its locks, and everything else waits. One slow query inside a long transaction can block hundreds of requests and degrade the whole application.

Hence the rule — keep transactions short. The sooner it commits, the sooner its locks release. Efficient locking is as much about responsiveness as about correctness, and it is the same atomicity-under-contention problem that Designing a Distributed API Rate Limiter solves with Lua scripts.

6. Normalization — structure that stays clean

As applications grow, the difficulty is not storing more data but keeping it organized. Poor schema design produces duplicated information, records that disagree with each other, and update logic that has to touch many places to stay correct. Normalization removes that redundancy by giving each fact one home.

7. Denormalization — knowing when to break the rule

Normalized schemas can make read paths expensive, chaining joins across many tables to answer one reporting question. Denormalization deliberately reintroduces duplication to make those reads fast.

It is a trade with a real cost: duplicated data must be kept in sync, and the write path absorbs that work. The mistake is denormalizing early — before any measured performance problem exists.

8. Replication — scaling reads

Replication keeps copies of the same data on multiple servers, so read-heavy traffic can be distributed across replicas instead of overloading the primary. It buys read throughput and a measure of redundancy, and it introduces replication lag — a replica may briefly serve slightly stale data, which the application must be designed to tolerate.

9. Partitioning — making large tables manageable

Partitioning splits one large table into smaller physical pieces within the same database, commonly by date. Queries that target a single partition scan far less data, and aging out old data becomes a partition drop instead of a mass delete.

10. Sharding — scaling past one server

Eventually no single server is big enough, and the dataset must be distributed across several. Unlike replication, where every server holds the same data, sharding divides the dataset — customers A–F on one server, G–M on another, N–Z on a third. The application routes each query to the shard that owns the data.

This gives near-unlimited horizontal scale, and it costs a great deal of complexity: cross-shard queries get hard, transactions spanning shards need careful coordination, and the architecture becomes materially more difficult to operate. Most applications never need it. Internet-scale platforms cannot avoid it.

How they combine

These are not independent techniques; production systems run most of them at once. A food-delivery application places an order inside a transaction, relying on ACID for consistency while locks stop two customers buying the same meal. Indexes find restaurants and menu items in milliseconds, and a normalized schema keeps users, orders, restaurants and payments coherent. As it grows, reporting dashboards read denormalized tables, read traffic spreads across replicas, historical orders are partitioned by date, and customer data is eventually sharded.

The common mistakes

  • Indexing as a cure-all. Poorly chosen indexes slow every write and consume storage. Index what the application queries, not what seems important.
  • Long-running transactions. The longer a transaction stays open, the longer it blocks others. One slow query becomes an application-wide bottleneck.
  • Normalizing without considering reads. Fully normalized schemas can produce report queries buried in expensive joins.
  • Denormalizing too early. Duplicated data before a measured problem is pure maintenance cost — the database equivalent of the premature-abstraction trap in Ten Clean Code Truths That Survive Review.
  • Treating optimization as post-deployment work. Schema design, indexing strategy and transaction boundaries are cheapest to get right before there is production data to migrate.