I have spent most of this year asking what happens while software is running.
Processes execute programs.
Processes communicate through networks.
HTTP gives applications a protocol for requests and responses.
DNS helps them find things.
Eventually, though, an application usually needs to remember something.
Users.
Orders.
Products.
Payments.
Configuration.
Events.
Some of that information needs to survive long after the process that created it has disappeared.
That brings me to databases.
My first mental model of a relational database was essentially:
a collection of spreadsheets that I can query with SQL.
It is not completely useless.
Tables certainly look spreadsheet-ish when displayed in a terminal.
But it misses most of what makes a relational database interesting.
So I want a better model.
For now I am concentrating on three pieces:
tables
keys
indexes
They answer three surprisingly different questions:
tables
│
└── how is the data structured?
keys
│
└── how do I identify and relate data?
indexes
│
└── how can the database find data efficiently?
Those distinctions already make the database feel less like a very ambitious CSV file.
Start with a table
Suppose I am building a tiny shop.
I might need to store products:
products
+------------+----------------------+-------+
| product_id | name | price |
+------------+----------------------+-------+
| 101 | Gopher mug | 18.00 |
| 102 | Algorithm cushion | 32.00 |
| 103 | Linux penguin print | 24.00 |
+------------+----------------------+-------+
In SQL I could define something like:
CREATE TABLE products (
product_id integer,
name text,
price numeric
);
A table has named columns describing attributes of the data.
It contains rows representing individual records that conform to that structure.
So:
TABLE
│
├── columns
│ ├── product_id
│ ├── name
│ └── price
│
└── rows
├── product 101
├── product 102
└── product 103
Already this is doing something a plain text file does not necessarily do.
The database knows something about the structure of the data.
It can also enforce rules about what is allowed into that structure.
Columns have types
My product_id is an integer.
My name is text.
My price is numeric.
Those choices matter.
I am not merely storing:
101,Gopher mug,18.00
and hoping every future program remembers what each position means.
The table definition gives the data a schema:
product_id integer
name text
price numeric
The database can use that schema when validating, storing and operating on values.
Different database systems have different type systems and exact behaviours, so the details are not perfectly portable.
But the broader idea is straightforward:
The schema describes the shape of the data the database is expected to hold.
Tables can enforce constraints
Types are only one part of the structure.
Suppose a product without a name makes no sense.
I can express that rule:
CREATE TABLE products (
product_id integer,
name text NOT NULL,
price numeric
);
Now:
name = NULL
is not valid for that column.
I might also decide product IDs must be unique.
Or that prices must satisfy some condition.
Relational databases give me constraints for expressing rules about valid data.
Common SQL constraints include:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
This is an important shift in my thinking.
I could enforce every rule only in application code:
application
│
└── "please don't insert nonsense"
But a database constraint places a rule closer to the data itself:
application
│
▼
database
│
├── does this satisfy the schema?
└── does this satisfy the constraints?
That means a second application, script or administrator cannot casually bypass a rule merely because they forgot the validation implemented by the first application.
The database becomes an active participant in maintaining valid state.
Rows need identity
Now I hit a more interesting problem.
Suppose my table contains:
+------------+----------------------+-------+
| product_id | name | price |
+------------+----------------------+-------+
| 101 | Gopher mug | 18.00 |
| 102 | Gopher mug | 21.00 |
+------------+----------------------+-------+
The names are identical.
Perhaps they are different sizes.
Perhaps one is a limited edition.
Perhaps someone simply created two products with the same name.
If I say:
Update the Gopher mug.
which row do I mean?
Names are not necessarily identity.
I need some value that reliably distinguishes one product from another.
Enter the primary key.
The primary key says: this identifies the row
I can define:
CREATE TABLE products (
product_id integer PRIMARY KEY,
name text NOT NULL,
price numeric
);
Now:
product_id
is the table’s primary key.
A primary key identifies rows uniquely and cannot contain NULL.
So:
product_id = 101
refers to one particular row.
That gives me:
PRODUCT
identity
│
▼
product_id = 101
attributes
│
├── name = Gopher mug
└── price = 18.00
This seems trivial until I realise how many other database operations depend on reliable identity.
If I want to:
update one product
delete one product
refer to one product from another table
I need a stable way to say which row I mean.
A primary key can contain more than one column
A primary key does not have to be a single column.
A database can use a combination of columns:
PRIMARY KEY (order_id, product_id)
if the combination is what uniquely identifies a row.
For example:
order_items
+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
| 5001 | 101 | 2 |
| 5001 | 103 | 1 |
| 5002 | 101 | 1 |
+----------+------------+----------+
Perhaps the business rule says one product can appear at most once within each order.
Then neither:
order_id
nor:
product_id
is unique by itself.
But the pair:
(order_id, product_id)
can be.
So row identity can be:
one column
or:
a combination of columns
depending on the model.
Natural keys and invented keys
This immediately raises another design question.
Sometimes the data already contains something that looks unique.
Perhaps:
email address
ISBN
vehicle registration
country code
Could one of those become the primary key?
Maybe.
That would be a natural key: identity derived from meaningful domain data.
Another approach is to create an identifier whose main job is simply to identify the row:
product_id
customer_id
order_id
That is commonly called a surrogate key.
Conceptually:
natural key
│
└── meaningful business value
also used as identity
surrogate key
│
└── database/application identifier
created specifically as identity
Neither choice is automatically correct.
A natural value that appears permanent can later turn out to change.
A surrogate identifier can avoid coupling database identity to mutable business information.
But adding a surrogate key does not magically remove the need to enforce genuinely unique business rules.
For example, if the system requires email addresses to be unique, this:
customer_id integer PRIMARY KEY
does not enforce that rule.
I may still need:
email text UNIQUE
Identity and business uniqueness are related, but they are not always the same question.
UNIQUE is not the same thing as “the primary key”
Suppose:
CREATE TABLE users (
user_id integer PRIMARY KEY,
email text UNIQUE NOT NULL
);
Both columns have uniqueness rules.
But they have different roles in the design.
user_id
│
└── primary identity of this row
email
│
└── business rule:
duplicates are not allowed
A table can have multiple unique constraints.
It has one declared primary key.
That primary key communicates which column or column set represents the table’s principal row identifier.
This feels like useful schema documentation as well as enforcement.
Foreign keys let tables refer to one another
Now suppose I add orders.
orders
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 5001 | 72 |
| 5002 | 84 |
+----------+-------------+
And customers:
customers
+-------------+---------+
| customer_id | name |
+-------------+---------+
| 72 | Ada |
| 84 | Grace |
+-------------+---------+
The value:
orders.customer_id = 72
is referring to:
customers.customer_id = 72
This is where a foreign key enters.
Foreign keys express relationships
I could define:
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL
REFERENCES customers(customer_id)
);
The customer_id in orders is now a foreign key referencing customers.
Conceptually:
CUSTOMERS
customer_id
72
84
▲
│
│ references
│
ORDERS
customer_id
72
84
The database can now enforce an important rule:
A non-null foreign-key value cannot refer to a customer that does not exist.
And because this schema also says NOT NULL, every order must actually have a customer.
Without NOT NULL, PostgreSQL would also allow:
customer_id = NULL
which means the row is not claiming to reference any customer at all.
Without the foreign key, this might be possible:
orders
order_id customer_id
5003 999999
even if customer 999999 exists nowhere.
With the constraint, the relationship itself becomes part of the database’s integrity rules.
This is called referential integrity.
That phrase suddenly feels considerably less mysterious.
Why not put everything in one table?
At this point a tempting alternative appears.
Why not store:
order_id
customer_id
customer_name
customer_email
product_id
product_name
product_price
quantity
...
all together?
Then I would not need all these references.
The problem is repetition.
Imagine one customer makes one hundred orders.
If their name and email are copied into every order row, I now have one hundred versions of information describing the same customer.
What happens if their email address changes?
I could end up with:
99 rows with new@example.com
1 row with old@example.com
Now which value describes the customer?
Separating data according to what it represents can reduce this duplication.
Instead:
customers
│
└── customer information stored once
orders
│
└── references customer identity
This is heading towards the larger subject of normalisation.
I am deliberately stopping before that rabbit hole eats November.
For now, the useful idea is simply:
Relationships let one row refer to another row without copying all of its attributes.
Many-to-many relationships need somewhere to live
Suppose:
one order has many products
and:
one product can appear in many orders
That is a many-to-many relationship.
Rather than putting a list of product IDs into one column, I can model the relationship as another table:
orders
│
│
▼
order_items
▲
│
│
products
For example:
CREATE TABLE order_items (
order_id integer REFERENCES orders(order_id),
product_id integer REFERENCES products(product_id),
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
Now:
+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
| 5001 | 101 | 2 |
| 5001 | 103 | 1 |
| 5002 | 101 | 1 |
+----------+------------+----------+
Each row represents a relationship:
this product belongs to this order in this quantity.
That is quite elegant.
The relationship itself can even have attributes such as:
quantity
price_at_purchase
discount
It has become data in its own right.
So where do indexes fit?
This is where I had been mixing two different concepts.
I knew databases had:
primary keys
indexes
and had vaguely assumed an index was another kind of identifier.
It isn’t.
An index primarily gives the database an additional access path that can make some query plans cheaper.
Suppose my products table eventually contains:
10 rows
Finding:
SELECT *
FROM products
WHERE product_id = 101;
is not particularly intimidating.
Suppose it contains:
10,000,000 rows
Now repeatedly examining rows looking for the correct value can become expensive.
An index gives the database another data structure it can use to locate relevant rows more efficiently.
Conceptually:
WITHOUT USEFUL INDEX
table
│
├── row
├── row
├── row
├── row
├── row
├── ...
└── target?
WITH USEFUL INDEX
index
│
└── 101 ─────────────► relevant table row
That diagram is intentionally simplistic.
Different database systems and index types work differently.
But the purpose is what I want to remember:
An index gives the database an additional access path to data.
The book-index analogy is actually useful
For once, the obvious analogy behaves reasonably well.
Suppose I have a 900-page book and want information about:
B-trees
Without an index, I could begin at page one and read until I find the relevant material.
With an index:
B-trees .......... 317, 442
I gain a shortcut to likely locations.
The index itself takes space.
It also needs updating when the book’s contents change.
Database indexes have a similar trade-off.
They can make some reads dramatically more efficient.
But they are not free.
An index has to be maintained
Suppose I create:
CREATE INDEX products_name_idx
ON products(name);
The database now has additional structure associated with product names.
That may help queries such as:
SELECT *
FROM products
WHERE name = 'Gopher mug';
But when I:
INSERT
UPDATE
DELETE
rows, relevant indexes may also need to be maintained.
So:
more indexes
does not automatically mean:
faster database
Instead there is a trade-off:
index
│
├── potentially faster reads
│
├── additional storage
│
└── additional write/maintenance work
This is why “index every column” is not a sensible strategy.
The useful index depends on how the data is actually queried.
Primary keys and indexes are related, but not identical
Here is a subtle point.
A primary key is a constraint and schema concept.
An index is an access structure.
They solve different conceptual problems:
PRIMARY KEY
"Are these values a valid unique identity?"
INDEX
"How can I locate relevant rows efficiently?"
A database implementation may use an index to enforce a primary key efficiently.
For example, PostgreSQL automatically creates a unique B-tree index for a primary key.
But that implementation relationship should not make me merge the concepts.
The constraint expresses the rule.
The index provides machinery that can help enforce or search it.
That distinction feels important.
Foreign keys and indexes are another interesting pair
A foreign key says:
This value must refer to a valid row over there.
Again, that is an integrity rule.
It does not necessarily mean the referencing column automatically receives an index.
For PostgreSQL, declaring a foreign key does not automatically create an index on the referencing columns.
That distinction is easier to remember if I separate the two sides:
CUSTOMERS.customer_id
referenced side
→ already backed by a primary key / unique constraint
ORDERS.customer_id
referencing side
→ not automatically indexed
PostgreSQL can therefore check that the referenced customer exists efficiently, while an index on orders.customer_id is a separate design choice that may help when the database needs to find matching child rows.
Whether such an index is useful depends on how the table is queried and modified.
So:
FOREIGN KEY
│
└── relationship/integrity
INDEX
│
└── data access/performance
Another pair of things that often appear together without being the same thing.
Indexes can cover more than one column
Suppose I frequently query:
SELECT *
FROM products
WHERE category_id = 12
AND created_at >= '2020-01-01';
A database can support indexes containing multiple columns.
Conceptually:
CREATE INDEX products_category_created_idx
ON products(category_id, created_at);
For PostgreSQL B-tree indexes, conditions on the leading, leftmost columns are especially important in determining how much of the index must be scanned.
But this does not mean column order is irrelevant.
For a B-tree multicolumn index, the order of indexed columns affects which query conditions can use the index most efficiently.
So even:
Which columns should be indexed?
isn’t quite enough.
I may also need to ask:
In which combination and order?
Database performance is beginning to look suspiciously like another deep subject.
I shall pretend not to notice for the remainder of this note.
An index doesn’t guarantee the database will use it
Another assumption worth dropping:
index exists
│
▼
query uses index
Not necessarily.
The database has a query planner.
It can consider possible ways of executing a query and choose what it estimates to be an appropriate plan.
Sometimes scanning the table may actually be cheaper.
If a table contains ten rows, using an index to find nine of them could be more machinery than simply reading the table.
So an index gives the database another option.
It does not force every relevant-looking query to take that route.
This makes the database feel much more active than I first imagined.
I send SQL describing what result I want.
The database works out a strategy for obtaining it.
SQL is declarative
That leads to a bigger point.
When I write:
SELECT name, price
FROM products
WHERE product_id = 101;
I am mostly describing:
what data I want
rather than writing an algorithm such as:
open file
start at row 1
compare product_id
move to next row
compare product_id
...
The database engine chooses an execution strategy based on things such as:
table structure
available indexes
statistics
query conditions
That is a powerful division of responsibility.
My application expresses the request.
The database decides how to execute it.
Tables aren’t ordered lists
One final mental correction.
When a database client prints:
+------------+----------------------+
| product_id | name |
+------------+----------------------+
| 101 | Gopher mug |
| 102 | Algorithm cushion |
| 103 | Linux penguin print |
+------------+----------------------+
it is extremely tempting to think:
101 is first
102 is second
103 is third
as though the table itself has a permanent row order.
That is not something I should rely upon.
If ordering matters to the result, I should ask for it explicitly:
SELECT product_id, name
FROM products
ORDER BY product_id;
The visual presentation of rows should not quietly become part of my application logic.
Another spreadsheet instinct to unlearn.
Putting the model together
My imaginary shop now looks something like this:
CUSTOMERS
─────────────────
customer_id PK
name
email UNIQUE
│
│
│ FK
▼
ORDERS
─────────────────
order_id PK
customer_id
created_at
│
│
│
▼
ORDER_ITEMS
─────────────────
order_id PK/FK
product_id PK/FK
quantity
▲
│
│
PRODUCTS
─────────────────
product_id PK
name
price
And surrounding those tables I may have indexes supporting common access patterns:
customers.email
│
└── index / uniqueness machinery
orders.customer_id
│
└── perhaps indexed for customer-order lookups
products.name
│
└── perhaps indexed if name searches justify it
The important thing is not the precise schema.
It is that I can now see different responsibilities.
The mental model I’m keeping
Tables structure data
TABLE
│
├── columns describe attributes
├── rows contain data
└── constraints restrict valid state
Primary keys establish identity
PRIMARY KEY
│
└── which value or values uniquely identify this row?
Foreign keys establish relationships
FOREIGN KEY
│
└── which valid row elsewhere does this value refer to?
Unique constraints express uniqueness rules
UNIQUE
│
└── which values must not be duplicated?
Indexes provide access paths
INDEX
│
└── how might the database find relevant rows efficiently?
And those ideas combine into something considerably richer than:
database = spreadsheet with SQL.
A relational database is storing structured data while also maintaining rules about identity, relationships and validity.
Then it builds additional structures such as indexes to make useful access patterns efficient.
The pieces overlap.
But they are not interchangeable.
That may be the most useful thing I have learned here:
schema design
│
└── what does the data mean?
constraints
│
└── what states are valid?
keys
│
└── how is data identified and related?
indexes
│
└── how can data be accessed efficiently?
Four questions.
Four different jobs.
And I suspect databases become much easier to reason about once I stop asking one feature to answer all four.
References and further reading
These PostgreSQL 12 references are retained because they match the version current when this note was written. PostgreSQL 12 is now an unsupported release.
PostgreSQL table definitions
PostgreSQL 12: Data Definition An overview of PostgreSQL’s table-definition features, including columns, defaults, constraints, schemas and related SQL structures.
Database constraints
PostgreSQL 12: Constraints
Documents CHECK, NOT NULL, UNIQUE, primary-key and foreign-key constraints. Particularly useful for the distinction between row identity, uniqueness and referential integrity.
CREATE TABLE
PostgreSQL 12: CREATE TABLE The PostgreSQL 12 reference for defining tables, columns and constraints. It also documents PostgreSQL’s creation of a unique B-tree index when a primary-key constraint is declared.
Indexes
PostgreSQL 12: Indexes Introduces indexes as a mechanism for improving database retrieval performance while noting the additional system overhead that indexes introduce.
Creating indexes
PostgreSQL 12: CREATE INDEX Documents explicit index creation, including indexes over individual columns, multiple columns and expressions.
Multicolumn indexes
PostgreSQL 12: Multicolumn Indexes Explains PostgreSQL indexes covering more than one column and why column order matters for efficient use of multicolumn B-tree indexes.
SQLite table and constraint model
SQLite: CREATE TABLE
A useful second implementation reference covering table columns, declared types, primary keys and NOT NULL, UNIQUE, CHECK and foreign-key constraints.
PostgreSQL row ordering
PostgreSQL 12: Sorting Rows (ORDER BY)
Documents that query result order is unspecified unless an explicit ORDER BY is used.