A database management system (DBMS) is the layer of software between your applications and the files where data lives. You send it a request, such as "give me order 42" or "move 10 units from this account to that one", and it works out how to do that quickly, safely and at the same time as thousands of other requests.
Every DBMS, whatever its data model, is built from the same few parts: something that accepts requests, a query processor, a storage engine, a transaction manager, and a recovery and replication layer. Knowing what each part does explains most of the behaviour you see in production, and makes choosing a DBMS much easier.
What a DBMS gives you that files do not
You could keep data in CSV or JSON files. A DBMS exists because that stops working once you need any of these:
- Data independence. Applications describe what they want, not where it is on disk. You can add an index, move to faster storage or reorganise a table without changing application code (physical independence). You can often add a column or a view without breaking existing queries (logical independence).
- Concurrent access. Many users read and write at once without corrupting each other's work.
- Integrity. Types, keys and constraints are enforced centrally, for every client.
- Crash safety. A power cut in the middle of a write does not leave half a record.
- Access control. Users and roles are limited to the data and operations they are granted.
- Backup and recovery. You can restore to a known state, often to a specific point in time.
Three groups of people use these features differently: application developers and analysts who query the data, designers who define the schema, and administrators (DBAs) who handle installation, security, backups, performance and upgrades. In managed cloud databases the provider takes over much of the last role, but schema design and query performance stay with you.
The parts of a DBMS
Connection and protocol layer
Clients connect over a network protocol specific to the DBMS and authenticate. Each connection costs memory on the server, and in some systems a whole process, which is why busy applications put a connection pool in front of the database instead of opening a connection per request.
Query processor
The query processor turns a statement into results in three steps:
- The parser checks syntax and resolves table and column names against the catalog.
- The optimizer considers different ways to run the statement, such as which index to use, which join order and which join algorithm, and estimates the cost of each from table statistics.
- The executor runs the cheapest plan it found.
You can watch this happen. On PostgreSQL, create a table with 100,000 rows:
CREATE TABLE events (
event_id bigint PRIMARY KEY,
user_id int NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO events
SELECT g, g % 1000, now() - g * interval '1 second'
FROM generate_series(1, 100000) AS g;
ANALYZE events;
EXPLAIN SELECT * FROM events WHERE event_id = 42;
Index Scan using events_pkey on events (cost=0.29..8.31 rows=1 width=20)
Index Cond: (event_id = 42)
The optimizer chose the primary key index. Filter on a column with no index and it has no choice but to read the whole table:
EXPLAIN SELECT * FROM events WHERE user_id = 7;
Seq Scan on events (cost=0.00..1887.00 rows=100 width=20)
Filter: (user_id = 7)
Your cost numbers may differ slightly. The shape of the plan is the point. After CREATE INDEX ON events (user_id); the same query switches to an index-based plan. The optimizer's choices are only as good as its statistics, which is why stale statistics are a classic cause of a query that suddenly becomes slow.
Storage engine
The storage engine decides how rows are laid out on disk and how they are found again. Two designs dominate:
| B-tree engines | LSM-tree engines | |
|---|---|---|
| How writes work | Update fixed-size pages in place | Append to memory, flush sorted files, merge them later (compaction) |
| Strength | Predictable reads and range scans | High write throughput |
| Cost | Random I/O on write-heavy workloads | Background compaction, reads may check several files |
| Examples | PostgreSQL, MySQL InnoDB, SQL Server, SQLite | Cassandra, RocksDB, and databases built on RocksDB |
Analytical systems add a third option, columnar storage, which stores each column together. Scanning and aggregating a few columns of a very wide table becomes fast, and single-row updates become expensive.
Disk is slow compared with memory, so every engine keeps a cache of recently used pages (the buffer pool in MySQL and SQL Server, shared buffers in PostgreSQL). Sizing that cache is usually the first tuning step on a new server.
Transaction manager
The transaction manager provides the ACID guarantees. It has two jobs.
Concurrency control keeps simultaneous transactions from interfering. Most current systems use multiversion concurrency control (MVCC): a writer creates a new version of a row instead of overwriting it, so readers keep seeing a consistent snapshot and do not block. Locks are still taken when two transactions write the same row. Old row versions have to be cleaned up later, which is what VACUUM does in PostgreSQL and the purge thread does in InnoDB.
Logging and recovery keep committed data safe. Before a change touches the data files it is appended to a write-ahead log (called WAL in PostgreSQL, the redo log in MySQL and Oracle, the transaction log in SQL Server). A commit is complete once its log record is on stable storage. After a crash the DBMS replays the log to redo committed work and undo anything unfinished.
Replication and backup
The same log is the basis for almost everything else: streaming it to another server gives you a replica, archiving it gives you point-in-time recovery, and decoding it gives you change data capture. Replication keeps you running when a server fails. It does not replace backups, because a mistaken DELETE replicates just as faithfully as a correct one.
Types of DBMS
| Type | Data model | Typical use | Examples |
|---|---|---|---|
| Relational | Tables, SQL, joins, transactions | Business applications, anything with related data | PostgreSQL, MySQL, SQL Server, Oracle Database, SQLite |
| Document | JSON-like documents in collections | Content, catalogs, nested objects that vary in shape | MongoDB, Couchbase, CouchDB |
| Key-value | A value looked up by one key | Caches, sessions, counters | Redis, Valkey, DynamoDB |
| Wide-column | Rows grouped by partition key, spread over many nodes | Very high write volume, time-ordered data | Cassandra, HBase, ScyllaDB |
| Graph | Nodes and edges | Fraud rings, recommendations, network analysis | Neo4j |
| Time-series | Timestamped measurements | Metrics, IoT sensors | InfluxDB, TimescaleDB |
| Analytical (OLAP) | Columnar tables | Reporting and aggregation over large history | ClickHouse, DuckDB, Snowflake, BigQuery |
| Vector | Embeddings with nearest-neighbour search | Semantic search, retrieval for AI applications | pgvector (a PostgreSQL extension), Milvus |
The categories overlap more than they used to. PostgreSQL and MySQL store and index JSON. Many document and wide-column databases now offer SQL-like query languages and some form of transactions. Several relational systems have vector types.
A second useful split is by workload. OLTP systems handle many small, short transactions (place an order). OLAP systems handle a few large queries that scan a lot of history (revenue by region for five years). One database rarely does both well at scale.
A third is by deployment: an embedded DBMS such as SQLite or DuckDB runs inside your application process, a client-server DBMS runs as its own service, and a distributed DBMS spreads data over several machines.
How to choose one
Work through these in order:
- What do the queries look like? If you need joins, ad hoc questions and multi-row transactions, start with a relational database. If every access is "fetch this one thing by its ID", a key-value or document store fits.
- What consistency do you need? Money, stock and bookings need ACID transactions. Counters and activity feeds usually tolerate eventual consistency.
- How much data, and how fast does it arrive? A single well-sized relational server handles far more than most applications will ever produce. Distributed databases solve a real problem, but they add operational work and restrict how you can query.
- Who will run it? Check that a managed service exists where you host, and that your team can back it up, restore it and upgrade it.
- What is the licence? "Source available" is not the same as open source. Read the terms before you build a product on it.
When nothing in that list points elsewhere, a general-purpose relational DBMS is the safe default. It is easier to move one awkward workload out to a specialised store later than to rebuild transactions and joins on top of a store that lacks them.
Common mistakes
- Choosing by scale you do not have. Picking a distributed NoSQL store for a few gigabytes gives up joins and constraints and buys nothing.
- Treating schemaless as no schema. The schema still exists; it has moved into application code, where nothing enforces it.
- Using an OLTP database as a warehouse. Long analytical queries compete with live traffic and, under MVCC, hold back cleanup of old row versions.
- Counting replicas as backups. Test an actual restore.
- Leaving defaults in place. Default memory settings are sized to start anywhere, not to perform well on your hardware.
Further reading
Get the weekly commit
New database deep dives every week.
