What a Database Is

A librarian with perfect memory

A database is software whose entire job is to remember structured information correctly — across crashes, restarts, hundreds of concurrent users, and the messy reality of production. Hand it data; it organises, indexes, and returns answers in milliseconds. Take it away; you are back to grepping CSVs and arguing about which spreadsheet is the real one.

What separates a real database from a glorified file cabinet is the four guarantees baked into the engine: structure (every column has a type), integrity (constraints reject bad data before it lands), concurrency (many writers without corruption), and durability (committed data survives a power cut a millisecond after COMMIT returned).

The relational vocabulary you'll use forever

Every PostgreSQL idea below sits on three nouns: tables hold data, rows are individual records, columns are typed fields. The mathematical vocabulary (relation, tuple, attribute) maps one-to-one onto this. SQL is the language you use to define and query these structures.

What this quest assumes

Zero PostgreSQL experience. Some comfort with a terminal helps but isn't required — every example is a copy-pasteable psql session. By the end, you'll be designing schemas, writing window functions, reading EXPLAIN output, and arguing about isolation levels with a straight face.

Code

Create a tiny library·sql

-- One table, three rows, one query — the entire arc of a database.
CREATE TABLE books (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    genre TEXT,
    year  INTEGER CHECK (year BETWEEN 1450 AND 2100)
);

INSERT INTO books (title, genre, year) VALUES
    ('The Pragmatic Programmer', 'tech', 2019),
    ('Designing Data-Intensive Applications', 'tech', 2017),
    ('The Three-Body Problem', 'sci-fi', 2008);

SELECT title, year
FROM   books
WHERE  genre = 'tech'
ORDER  BY year DESC;

Same data as a fragile CSV·text

# The 'database' your team had before adopting Postgres.
books.csv
id,title,genre,year
1,"The Pragmatic Programmer",tech,2019
2,"Designing Data-Intensive Applications",tech,2017
3,"Three-Body Problem",scifi,2008          <-- typo: should be sci-fi
4,"Foundation",sci-fi,                     <-- year missing
5,"Dune",sci-fi,1965,extra                 <-- bad shape

A librarian with perfect memory

The relational vocabulary you'll use forever

What this quest assumes

Code

External links

Exercise

Progress

Comments 0