Introduction: Why Databases Exist | Databases Tutorial

This chapter explains what databases actually are, why the relational model won, and how to get PostgreSQL running locally so you can follow along.

What a Database Actually Is

A database is a program that stores data durably and answers questions about it quickly. Those two words do a lot of work.

Durably means the data is still there after a crash, a power cut, or your laptop getting kicked. That sounds obvious. It is not. Writing a file and calling fsync at the right moment is surprisingly hard, and a database does this for you hundreds of times a second.

Quickly means you can ask "how many users signed up last week?" without reading every row. The database has structures (indexes, statistics, query plans) that turn "look at everything" into "look at the five rows that matter."

If all you need is to save some config between runs, a file is fine. The moment two processes want to touch the same data, or one of them wants to count things, or you want to guarantee that a purchase and an inventory decrement happen together, you want a database.

Why Relational Won

Edgar Codd published "A Relational Model of Data for Large Shared Data Banks" in 1970. The idea: represent data as unordered sets of tuples, and describe operations on those sets mathematically. Then let the computer figure out how to execute them.

Before Codd, you navigated through data structurally: "follow this pointer to that record." After Codd, you declared what you wanted and let the engine plan the path.

That shift is why SQL has survived 50 years. The NoSQL wave in the 2000s argued that relational was too rigid for the web. Most of those databases either added SQL back (Cassandra, DynamoDB), quietly became relational-ish (MongoDB with aggregations), or found their niche alongside a relational store rather than replacing it.

Use relational by default. Reach for something else when you have a real reason.

Installing PostgreSQL

Pick the path that matches your machine.

macOS via Homebrew:

brew install postgresql@16
brew services start postgresql@16

Linux (Debian/Ubuntu):

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql

Docker (works everywhere):

docker run --name pg -e POSTGRES_PASSWORD=postgres \
    -p 5432:5432 -d postgres:16

Windows: use the EnterpriseDB installer at postgresql.org/download.

Confirm it worked:

psql --version
# psql (PostgreSQL) 16.2

Your First Database

Create a new database called learning and connect to it.

createdb learning
psql learning

You are now at a learning=# prompt. This is psql, the command-line client. Type a SQL statement, end it with a semicolon, press enter.

SELECT version();

Output looks something like:

PostgreSQL 16.2 on aarch64-apple-darwin23.2.0, compiled by Apple clang 15.0.0

Now create a table and put something in it:

CREATE TABLE greetings (
    id      SERIAL PRIMARY KEY,
    message TEXT NOT NULL
);

INSERT INTO greetings (message) VALUES ('hello from postgres');

SELECT * FROM greetings;

You should see:

 id |       message       
----+---------------------
  1 | hello from postgres

That's a full round trip: create storage, write a row, read it back. Everything else in this tutorial is variations on that theme.

The psql Essentials

A handful of backslash commands get you through most sessions:

\l              list databases
\c dbname       connect to a database
\dt             list tables in current database
\d tablename    describe a table's columns and constraints
\di             list indexes
\q              quit
\?              show all backslash commands
\h SELECT       show help for a SQL command

You can also edit your last query in $EDITOR:

\e

That opens your editor. Save, quit, and psql runs the query. Much nicer than retyping a 30-line SELECT.

GUI Clients

psql is great, but a GUI helps when you're exploring an unfamiliar schema. Pick one:

TablePlus (macOS/Windows): polished, paid after a trial
DBeaver (cross-platform, free): heavier, supports many databases
pgAdmin (cross-platform, free): ships with PostgreSQL, functional if dated

You do not need one to learn. This tutorial uses psql throughout because it is the environment guaranteed to exist on every box.

What Ships with PostgreSQL

When you install PostgreSQL you get a handful of binaries. The ones you'll use:

postgres      the server itself
psql          interactive SQL client
createdb      shortcut for CREATE DATABASE
dropdb        shortcut for DROP DATABASE
pg_dump       logical backup (SQL or custom format)
pg_restore    restore from pg_dump custom format
pg_ctl        start/stop/restart a cluster
initdb        initialize a new data directory

You don't need to master these today. You need to know they exist so you can man pg_dump when the time comes.

Sample Data for the Rest of the Tutorial

Later chapters use a small e-commerce schema: users, products, orders, order_items. Chapter 3 builds it properly. For now, if you want something to poke at:

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      TEXT UNIQUE NOT NULL,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO users (email, name) VALUES
    ('ada@example.com',       'Ada Lovelace'),
    ('grace@example.com',     'Grace Hopper'),
    ('alan@example.com',      'Alan Turing'),
    ('margaret@example.com',  'Margaret Hamilton'),
    ('katherine@example.com', 'Katherine Johnson');

SELECT id, name FROM users ORDER BY name;

Keep that window open. You'll use it in the next chapter.

Next Steps

Continue to 02-sql-basics.md to learn how to read data properly.