We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
In short
A database is an organized collection of data stored on disk or in memory, managed by software (a database management system, or DBMS) that lets applications write, read, update, and delete that data reliably and concurrently. It is the system of record almost every application depends on to remember anything between requests.
In short
A database is an organized collection of data stored on disk or in memory, managed by software (a database management system, or DBMS) that lets applications write, read, update, and delete that data reliably and concurrently. It is the system of record almost every application depends on to remember anything between requests.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Database across a run of lessons, not one page. These 4 alone are about 75 minutes of step-by-step reading, every one with a quiz.
Your database already cached the disk, so what a query cache saves is CPU, and that varies by 430 times between queries.
PgBouncer, HikariCP, and why opening a new database connection per request is a recipe for disaster.
Databases built for timestamped data, metrics, IoT sensors, financial ticks, and observability.
Databases that store relationships as native citizens, powering social networks, fraud detection, and recommendation engines.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Query Cachingpay once,
yours for life
A database is two things working together: the data itself, and the software that manages it. The software is the database management system (DBMS), for example PostgreSQL, MySQL, MongoDB, or Redis. When people say "the database", they usually mean the running DBMS plus the data it owns.
The job of the DBMS is to take messy raw bytes and turn them into something you can query safely while many users hit it at once. It enforces structure, handles concurrent reads and writes without corrupting data, survives crashes, and answers questions like "give me every order placed by user 4821 in the last 30 days" in milliseconds instead of you scanning a giant file by hand.
Databases split into broad families. Relational databases (PostgreSQL, MySQL) store data in tables with rows and columns and connect them with joins. NoSQL databases drop the rigid table model: document stores (MongoDB) keep JSON-like records, key-value stores (Redis, DynamoDB) map a key straight to a value, and graph databases (Neo4j) store relationships as first-class data.
At the bottom, a database stores data in fixed-size blocks called pages, usually 8KB in PostgreSQL or 16KB in MySQL InnoDB. Reading from disk is slow, so the DBMS keeps hot pages in a memory area called the buffer pool and only touches disk when it has to.
Finding one row out of ten million without scanning the whole table is the job of an index, most often a B-tree. A B-tree keeps keys sorted in a shallow, wide tree so a lookup takes a handful of page reads instead of millions. This is why a query on an indexed column stays fast as the table grows, and why an unindexed query gets slower every day.
Reliability comes from a write-ahead log (WAL). Before the database changes the actual data pages, it first appends a record of the change to an append-only log and flushes that to disk. If the server loses power mid-write, the database replays the log on restart and recovers to a consistent state. This same log is what powers transactions and the ACID guarantees, and it is also what gets shipped to replicas to keep copies in sync.
Reach for a relational database by default. PostgreSQL or MySQL handle the vast majority of applications, give you transactions and joins, and are battle-tested for decades. You only need something else when you hit a real limit they cannot meet.
Pick a key-value store like Redis when you need sub-millisecond reads on simple lookups, such as a session cache or a leaderboard, and you can accept that the data lives mostly in memory. Pick a document store like MongoDB when your records are deeply nested and your schema changes often. Pick a wide-column or distributed store like Cassandra or DynamoDB when a single machine cannot hold your write volume and you are willing to give up joins and strong consistency for horizontal scale.
The core trade-off is consistency versus scale. A single relational node gives you clean transactions but caps out at what one machine can do. Distributed databases scale across many machines but force you to confront eventual consistency, where a write may take a moment to show up everywhere. There is no free lunch, which is what the CAP theorem formalizes.
Picture an e-commerce checkout. The user clicks "Place Order", and the application opens a transaction against PostgreSQL. Inside that transaction it inserts a row into the orders table, decrements stock in the products table, and inserts payment details, all as one atomic unit.
If the payment step fails, the database rolls back everything: the order disappears and the stock count returns to what it was, as if the click never happened. This is atomicity, and it is the reason you do not end up with an order that was never paid for or stock that vanished into nothing.
Meanwhile, the product catalog pages that thousands of shoppers are browsing get served from a read replica or from a Redis cache so they never touch the busy primary. The primary database is reserved for the writes that must be correct, while cheaper read paths absorb the traffic that can tolerate being a second stale. That division of labor is the everyday shape of how real systems use a database.
PostgreSQL
Open-source relational database that powers Apple, Instagram, and Reddit as their primary system of record.
Amazon DynamoDB
Fully managed key-value and document store that handles Amazon's own shopping cart and order traffic at massive scale.
Redis
In-memory key-value store used by Twitter, GitHub, and Stack Overflow for caching, sessions, and rate limiting.
MongoDB
Document database used by companies like Adobe and eBay to store flexible, nested JSON-style records.
The database is the actual collection of stored data. The DBMS (database management system) is the software that creates, reads, updates, and protects that data, such as PostgreSQL or MySQL. In casual use, people say "database" to mean both together.
SQL (relational) databases store data in tables with fixed schemas and connect them with joins, giving strong transactions. NoSQL databases drop the table model for documents, key-value pairs, or graphs, trading joins and strict consistency for flexible schemas and easier horizontal scaling.
An index, usually a B-tree, lets the database find a specific row in a few reads instead of scanning the whole table. Without an index, a lookup gets slower as the table grows. With one, it stays fast even at millions of rows. The cost is slightly slower writes and extra storage.
ACID means transactions are Atomic (all steps succeed or none do), Consistent (the data stays valid), Isolated (concurrent transactions do not corrupt each other), and Durable (committed data survives a crash). Most relational databases like PostgreSQL guarantee this.
It uses a write-ahead log. Every change is appended to a log on disk before the actual data is modified. If the server crashes, the database replays the log on restart to recover to a consistent state, so committed transactions are not lost.
Database Query Caching
Your database already cached the disk, so what a query cache saves is CPU, and that varies by 430 times between queries
Database Connection Pooling
PgBouncer, HikariCP, and why opening a new database connection per request is a recipe for disaster
Time-Series Databases
Databases built for timestamped data, metrics, IoT sensors, financial ticks, and observability
Graph Databases
Databases that store relationships as native citizens, powering social networks, fraud detection, and recommendation engines
In-Memory Databases
Databases that live entirely in RAM, trading durability for speed measured in microseconds
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
BASE
An alternative to ACID for distributed systems: Basically Available, Soft state, Eventually consistent. Trades strong consistency for availability.
Sharding
Splitting a database into smaller pieces (shards) distributed across multiple servers. Each shard holds a subset of the data.
Replication
Keeping copies of the same data on multiple servers. Improves read performance and provides fault tolerance if one server goes down.
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Database in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
