Databases

Most Important Database Concepts Everyone Should Know

A complete guide to core, intermediate, and advanced database concepts — from normalization and sharding to CAP theorem, MVCC, and beyond.

King Technologies
6 min read
#databases #backend #scalability #engineering #sharding #cap-theorem

🧠 Most Important Database Concepts Everyone Should Know

Whether you’re a backend engineer, full-stack developer, or system architect, understanding how databases truly work is non-negotiable. In this guide, we’ll explore the crucial database concepts — from fundamentals to distributed systems thinking.

  • Mostly PostgresSQL Specific as it’s the oldest one JK it’s the best relation database and we have relation to maintain

Basic Ones (You Probably know but let’s recap)

  • Normalization is a process for reduces Redundancy & Maintains integrity (1NF -> 3NF -> BCNF), as structuring your table so there is not duplicated data.

    1NF - Ensure Each cell holds single value not arrays

2NF - 1NF + Every non-key column depends on the primary key

3NF - 2NF + No transitive dependency i.e. Non-key columns shouldn’t be depend on the other non-key columns BCNF - Enhanced 3NF - Every determinant must be a candidate key

When not to normalize - ready heavy applications, analytics, reporting systems, caching layers in these cases we use denormalization

  • Denormalization intentionally duplicates data to optimize ready-heavy queries 💡 Use normalization for OLTP systems and denormalization for analytical workloads or caching layers. Normalization optimizes for data integrity; denormalization optimizes for speed

why denormalize you may ask? when you app scales, normalized database become too slow for frequent or complex reads, by helping in reducing number of joins, faster reads, reporting queries Denormalization includes Duplicate Columns, Precomputed Aggregates, materialized view, JSON Columns etc. but it helps in faster reads fewer joins simpler queries and great for caching reporting.

Best Practice Normalize first, then denormalize where necessary i.e. design your schema cleanly, analyze and optimize only where queries are slow

  • Indexes Indexes speed up reads but slow down writes balance carefully It’s similar to a book’s index like you are storing page number to get to the data fast, It’s uses B-Tree (Balanced Tree) for creating a index.
CREATE INDEX idx_email on users(email);
  • Query Optimization
  1. use Explain and Explain Analyze to inspect executions plans
  2. Prefer composite indexes for multi-column filters
  3. Limit Select Field as don’t use SELECT *, select only needed Columns
  4. Batch Inserts * updates
  5. Use Pagination by using LIMIT & OFFSET
  • Transactions is a logical unit of work in a database - set of operations that executed together as one, Either everything succeeds (Committed) or nothing happens (Rolled back)

    uses BEGIN & COMMIT keywords for transaction. for example

BEGIN;
UPDATE accounts set balance = balance - 100 where id = 1; -- debit
UPDATE accounts set balance = balance + 100 where id = 2; -- credit
COMMIT;

& ACID Properties Atomicity All or nothing, partial transactions don’t exist Consistency Follow rules as Database moves from one valid state to another Isolation Each transaction runs independently, no interference between them Durability Once Committed Data persists, i.e. survive failures

Mastering isolation levels (e.g READ COMMITTED, REPEATABLE READ, SERIALIZABLE) prevents race conditions and dirty reads.

  • Joins Join tables to perform queries together to get it fast INNER, LEFT, RIGHT, FULL.
  • SubQueries can be expensive prefer CTEs (WITH clauses) for clarity and reuse.

Intermediate Concepts (You might know but not all i’m sure)

  • Connection Pooling Reusing database connection improve performance and reduces overhead.

  • use tools like pgbouncer, PrismaPool, or HikariCP.

  • Replication create read replicas to distribute load, use synchronous replication for strong consistency, asynchronous for speed.

  • Partitioning splits tables by range or hash for efficiency

  • Sharding splits datasets across multiple servers for horizontal scalability

    Bonus Tip: A good shard key is evenly distributed and query-friendly

Advance Concepts (You’ll know some may be but not all, not at all)

  • MVCC Multi version concurrency control, to handle concurrent transactions. Readers don’t block writers- multiple version or rows are maintained, requires vacuum to reclaim space.

  • Change streams / CDC real time event driven updates (Postgres LISTEN/NOTIFY);

  • Distributed Transactions (2PC/SAGA Pattern) 2PC is Two phase commit ensure atomic cross-node operations Saga Sequence of local transactions with compensating rollbacks - used for microservices

  • Event Sourcing Store all changes as events instead of just the current state, it is mainly used for trails, debugging etc.

  • CQRS (Command Query Responsibility Segregation) separate read and write models for better scalability, often paired with event

  • Views are precomputed results sets for complex or aggregate queries, periodically refreshed for near-real time analytics

  • Data Federation is query multiple data sources as one logical system like presto, trino, Bigquery

✍️ Author

Rohit Jain Full Stack Developer with 5+ years of experience in designing scalable backend systems, distributed architectures, and high-performance web applications.