What a database system does for you

ConceptFoundation5 min read#fundamentals#storage

The idea in 30 seconds

A database management system is a program that owns your data files and does every read and write on your behalf. Because everything goes through it, it can keep data safe across crashes, let many clients work at once, enforce rules, group changes into all-or-nothing transactions, and find records without reading everything.

One program in charge of the data

With plain files, every program that touches the data opens the files itself. Nothing coordinates them, so nothing stops two of them from colliding.

A database management system (DBMS) changes that arrangement. It is a program that owns the data files. Your application never touches those files; it sends requests (“add this user”, “find orders over ₹5,000 from last week”) and the DBMS does the work and sends back the result.

PostgreSQL works this way. A server process manages the database files and performs actions on behalf of clients, such as your web application, a command-line tool or a reporting dashboard. Client and server can be on different machines and talk over the network. Each new connection gets its own server process.

What that buys you

Capability	What it means	What happens with plain files
Durability and recovery	Committed changes survive a crash, and the database repairs itself on restart	A crash mid-write leaves a half-written file
Concurrency	Many clients read and write at once without corrupting anything	Lost updates and torn reads, as in the two-writer experiment
Transactions	Several changes succeed or fail together	Money debited from one account but never credited to the other
Integrity rules	The data must satisfy rules you declare: unique emails, positive prices	Duplicates and nonsense values get saved
Queries	Ask for exactly the records you want; the database works out how to find them	Read and scan everything, every time
Access control	Decide who may read or change what	Anyone who can open the file can do anything

Each row is a whole subject later in this field. A few of the mechanisms behind them, in PostgreSQL:

Durability comes from a write-ahead log: before PostgreSQL changes a data file, it writes a description of the change to a log and makes sure that log reaches permanent storage. After a crash, it replays the log to redo anything that had not reached the data files yet.
Concurrency comes partly from keeping several versions of each row, so a reader sees a consistent snapshot while a writer is changing the same row. Reading never blocks writing, and writing never blocks reading.
Transactions bundle steps into one all-or-nothing operation. Other clients never see the half-done state in between, and if anything fails, none of the steps take effect.

PredictFinding one record

A table of one million users, about 74 MB as JSON. How much longer does it take to find one user by id in the JSON file than in a small database running inside the same program?

Tens of thousands of times longer. We measured both on a laptop, in Python:

Users	JSON file: find one	JSON file: update one	SQLite: find one	SQLite: update one
100	0.07 ms	0.44 ms	0.008 ms	0.011 ms
1,000,000	474 ms	3,057 ms	0.007 ms	0.010 ms

The file has to be read and parsed in full for every lookup, and rewritten in full for every update. The database keeps an index on the id, so its cost barely changes with size. You saw why in B-tree indexes.

A storage format is not a database

CSV, JSON and Parquet are formats: rules for how bytes are laid out in a file. A format says nothing about who may write the file, what happens if two writers collide, or how to find a record without reading everything. A database system is the running software that enforces all of that.

Databases also come in two physical shapes:

Client-server, like PostgreSQL and MySQL: a separate server program that many clients connect to over the network.
Embedded, like SQLite: a library that runs inside your own program and stores the whole database in a single file. No separate server, but still transactions, queries and crash safety. The measurements above used it.

Two very different jobs

Applications ask databases for two very different kinds of work:

Transaction processing (OLTP): many small, concurrent reads and writes. “Place this order.” “Show this user’s cart.” Speed of each request and correctness under concurrency matter most.
Analytics (OLAP): a few huge reads over lots of history. “Revenue by region by month for five years.” How fast the system can scan matters most.

Systems built for one are usually poor at the other. Database types and how to choose one covers why.

Check yourself

What is the single architectural difference between a DBMS and a set of shared files, and why does it matter?
Show answer
In a DBMS, one system owns the data and performs every read and write on behalf of clients. That lets it coordinate concurrent access, order writes for crash safety, and enforce rules, which separate programs editing files cannot do.
I could explain this without looking
How does PostgreSQL recover committed changes after a crash?
Show answer
It writes a description of each change to the write-ahead log and flushes the log to permanent storage before changing the data files. On restart it replays the log to redo any changes that had not reached the data files.
I could explain this without looking
Why did looking up one user in the JSON file get slower as the file grew, while SQLite stayed the same?
Show answer
The file has to be read and parsed in full for every lookup. SQLite keeps an index on the id, so a lookup reads only a few pages whatever the table size.
I could explain this without looking
Is a CSV file a database? Explain.
Show answer
No. CSV is a storage format: a layout for bytes. It provides no coordination of writers, no crash safety, no rules and no way to find a record without reading the file.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 1.2 Architectural Fundamentals

Primary· §1.2 Architectural Fundamentals

PostgreSQL uses a client/server model; a server process manages the database files and performs actions on behalf of clients; it starts a new process for each connection; clients can talk to it over TCP/IP.

PostgreSQL documentation: 28.3 Write-Ahead Logging (WAL)

Primary· §28.3 Write-Ahead Logging (WAL)

Changes are logged and flushed to permanent storage before the data files are changed, so a crash can be repaired by replaying the log.

PostgreSQL documentation: 13.1 Concurrency Control, Introduction

Primary· §13.1 Introduction

Under MVCC, reading never blocks writing and writing never blocks reading.

PostgreSQL documentation: 5.5 Constraints

Primary· §5.5 Constraints

Constraints limit the data a table accepts beyond what data types can express, such as positive prices and unique values.

PostgreSQL documentation: 3.4 Transactions

Primary· §3.4 Transactions

A transaction bundles several steps into one all-or-nothing operation whose intermediate states other transactions cannot see.

PostgreSQL documentation: Chapter 21 Database Roles

Primary· Chapter 21 Database Roles

PostgreSQL manages access permissions with roles that own objects and are granted privileges on them.

Go further

What to read next, and why it is worth the time.

PostgreSQL documentation: 1.2 Architectural Fundamentals

Primary· §1.2 Architectural Fundamentals

One short page that shows how a real database server is put together. Worth reading in full.

Designing Data-Intensive Applications

ReferenceMartin Kleppmann· Chapter 3: Storage and Retrieval· location not yet verified

Builds a database from a log file upward, which makes this page's comparison concrete.