How a Database Actually Works

Most people meet SQL as a language and never ask what is on the other end of the connection. That gap causes a lot of confusion later, when a query is slow for no visible reason or a transaction behaves in a way the syntax does not explain.

So start with the machine, not the syntax.

Why not just use files?#

Imagine you keep customers in a CSV file and two scripts write to it at the same time. Script A reads the file, adds a row, writes it back. Script B does the same, a few milliseconds later. One of those rows is gone, and nothing told you.

Now add the rest of what a real system needs: finding one customer among ten million without reading every line, refusing a negative price, keeping a half-finished write from corrupting the file when the power dies, letting thirty people read while five people write.

A database is the software that already solved those problems. SQL is how you talk to it.

A database is a server process#

PostgreSQL, MySQL and SQL Server all run as a long-lived program that owns the data files. Your application, psql, or a GUI client never touches those files. They connect over a socket, send text, and receive results.

 +-----------+      SQL text       +----------------------+     +-------------+
 | your app  | ------------------> |  database server     | --> | data files  |
 | psql      | <------------------ |  (one process or      |     | WAL (log)   |
 | GUI tool  |     rows / errors   |   thread per client)  | <-- | on disk     |
 +-----------+                     +----------------------+     +-------------+

That boundary matters. Only the server decides what is allowed to happen to the data, which is why constraints defined in the database cannot be bypassed by a buggy script.

What is actually on disk#

PostgreSQL stores a table as a file of fixed-size pages, 8 KB each by default. Each page holds a number of rows. When you ask for one row, the server reads the whole page that contains it into memory (the buffer cache) and keeps it there for the next query.

This is the first thing to internalize: the unit of I/O is the page, not the row. A query that touches 50 rows packed into 3 pages is cheap. A query that touches 50 rows scattered over 50 pages is not.

The second thing on disk is the write-ahead log, the WAL. Before the server changes a data page, it appends a description of the change to the log and flushes that log to disk. Only then does it tell you COMMIT succeeded.

If the machine crashes, the server replays the log on restart and rebuilds whatever was in memory but never reached the data files. That is where the D in ACID, durability, comes from. It is also why a commit costs one log flush rather than a rewrite of every touched page.

The life of a query#

When you send SELECT * FROM customers WHERE country = 'India', the server runs it through a fixed pipeline.

  SQL text
     |
     v
  Parser     -> checks syntax, builds a parse tree
     |
     v
  Analyzer   -> resolves names: does "customers" exist? what type is "country"?
     |
     v
  Planner    -> chooses HOW to get the rows (scan, index, join order)
     |
     v
  Executor   -> runs the chosen plan, pulling pages through the buffer cache
     |
     v
  rows back to the client

Errors show up at different stages, and knowing which one tells you what went wrong. syntax error at or near "FORM" comes from the parser. relation "customer" does not exist comes from the analyzer. A query that is correct but takes nine seconds is a planner or data problem.

SQL says what, not how#

SQL is declarative. You describe the result you want. You do not write the loop, pick the index, or decide the order of the joins.

The planner does that. It estimates the cost of several candidate plans using statistics the server keeps about each table (row counts, value distributions) and runs the cheapest one.

The same query can therefore run completely differently on two databases, or on the same database a month apart. With 11 rows in products, the planner reads the whole table because that is cheapest. With 50 million rows and a useful index, it jumps straight to the match. You did not change the query. The data changed.

SQL the standard, SQL the dialect#

There is an ISO SQL standard, and every major database implements most of it and then adds its own extensions. SELECT, JOIN, GROUP BY and window functions behave almost the same everywhere. Row limiting, date functions, string concatenation, procedural code and upsert syntax do not.

This course uses PostgreSQL because it follows the standard closely, ships with strong tooling, and is free. When something differs in MySQL, SQL Server or SQLite, the lesson says so. The ideas transfer. Most of the syntax does too.

Common mistakes#

Treating the database as a dumb file store. Pulling a whole table into your application and filtering it in a loop throws away the planner, the indexes and the network savings. If you find yourself writing for row in all_rows: if ..., there is usually a WHERE that belongs in the query.

Reading SQL top to bottom as if it were a script. The clauses are not executed in the order you write them. You will see the real order in Module 2, and it explains most beginner errors.

Assuming rows come back in a stable order. They come back in whatever order the executor produced them. Without ORDER BY, order is an accident.

Believing a successful COMMIT and a flushed data file are the same thing. The log is durable; the data pages may still be in memory. You never need to care until you start tuning durability settings, and then you need to care a lot.

Practice#

1. A CSV file customers.csv is edited by two scripts at once. Write down three specific things that can go wrong, and for each one, name the database feature that prevents it (transactions, constraints, indexes, locks, the WAL).
2. Without running anything, explain in two sentences why the same SELECT could use a full table scan today and an index lookup after the table grows.
3. The error relation "orders" does not exist and the error syntax error at or near "FORM" come from different stages of the pipeline above. Name the stage for each.
4. Find the default page size of the database you plan to use (hint: PostgreSQL documents it as block_size). Why would very wide rows make a table slower to scan?