Postgres – Introduction

PostgreSQL is an open-source relational database that has been shipping since 1996 and is owned by nobody — no single company can change its licence or its direction. That matters more than it sounds: it is the reason it turns up equally in a weekend project and in a bank.

Most introductions stop at the feature list. This one covers the three mechanisms that explain almost every surprising thing Postgres does later in this track: one process per connection, the write-ahead log, and MVCC.

One process per connection

When your application opens a connection, the supervisor process forks a new operating-system process to serve it. Not a thread — a process, with its own memory.

SELECT pid, backend_type, state, query
FROM   pg_stat_activity
WHERE  backend_type = 'client backend';

Each of those rows is a real process you could see in ps. A connection costs a few megabytes before it has done any work, and the default ceiling is deliberately low:

SHOW max_connections;   -- 100 out of the box

This is the single most common way a Postgres application falls over in production. A web app with 40 instances, each holding a pool of 10 connections, wants 400 backends from a database willing to give 100. The answer is not a bigger number — it is a connection pooler, and it gets a section of its own in the production post at the end of the track.

Cluster, database, schema, table

Four words that get used loosely everywhere else and precisely in the Postgres documentation. Getting them straight now saves an argument with an error message later.

Cluster            one running Postgres server, one data directory, one port
 |
 +-- Database       stayhub, stayhub_lab, postgres, template1
      |             a connection is to ONE database and cannot see across
      |
      +-- Schema    public, or one per tenant/component
           |
           +-- Table, view, index, sequence, function

A "cluster" here has nothing to do with clustering for high availability. It is one server process managing one directory of files, serving several databases on one port.

The important consequence is on the second line: a connection belongs to one database. There is no USE otherdb. If you come from MySQL, where a "database" is closer to what Postgres calls a schema, this is the first thing that will trip you — a query joining two Postgres databases does not work, and the answer is usually that they should have been two schemas.

SELECT current_database(), current_schema, current_user;

Every table you create without saying otherwise lands in the public schema, and that default is worth revisiting before you go to production — the roles post covers why.

The write-ahead log

Postgres never writes your change straight to the table file. It writes a description of the change to the write-ahead log (WAL) first, flushes that to disk, and only then reports the commit as successful. The table file itself is updated later, in the background.

Everything else follows from that ordering:

• Crash recovery — on restart, Postgres replays the WAL and rebuilds everything the crash caught in memory.
• Replication — a replica is a server applying the primary's WAL stream.
• Point-in-time recovery — keep a base backup plus the WAL that followed it and you can restore the database to any second in between.
• Commit latency — a COMMIT waits for a disk flush. This is why inserting 10,000 rows in 10,000 transactions is so much slower than in one.

MVCC, and why VACUUM has to exist

Postgres uses multiversion concurrency control. An UPDATE does not overwrite a row. It writes a new version of the row and marks the old one as no longer visible to transactions that start after it.

You can watch it happen. ctid is the physical location of a row version — block number and offset within the block:

CREATE TEMP TABLE t (id int, note text);
INSERT INTO t VALUES (1, 'first');
SELECT ctid, xmin, id, note FROM t;

UPDATE t SET note = 'second' WHERE id = 1;
SELECT ctid, xmin, id, note FROM t;

 ctid  | xmin | id | note
-------+------+----+-------
 (0,1) | 4667 |  1 | first

 ctid  | xmin | id |  note
-------+------+----+--------
 (0,2) | 4667 |  1 | second

Same row, new address. The version at (0,1) is still on disk, invisible and taking up space. Two consequences run through the whole track:

• A reader never blocks a writer, and a writer never blocks a reader. A long report reads the versions that were current when it started, while writes carry on beside it. No read locks, and no WITH (NOLOCK) folklore.
• Dead row versions accumulate, and something has to reclaim them. That is VACUUM, and the autovacuum daemon that runs it for you. A table that is updated heavily and never vacuumed grows without its row count growing — the condition everyone calls bloat. Autovacuum is on by default and mostly does the right thing; the two occasions it does not are covered in the production post.

Which version to run

One major release a year, supported for five years from release. The version number is a single integer: 15, 16, 17. There is no meaningful reason to start a new project on anything but the newest major your managed provider offers, and every example in this track was run on:

psql -U stayhub -d stayhub -c 'SHOW server_version;'
# 16.15

Minor releases — 16.15 to 16.16 — are bug and security fixes only, and upgrading is a restart. Major upgrades rewrite the on-disk format and need pg_upgrade and a maintenance window, which is the argument for starting current rather than inheriting the problem.

Postgres or MySQL

Both are excellent and either will serve a normal application for years. The honest differences that have held for years and still hold:

Choosing on benchmark numbers you read somewhere is choosing on noise — both are fast, and your schema will matter more than either. Three questions actually decide it:

1. Is the shape of the data interesting? Geospatial, time series, documents sitting beside relations, vectors for search. If yes, Postgres, and it is not close — that is what the extension ecosystem is for.
2. Do you want the database to refuse bad data, or the application to prevent it? If the database, Postgres gives you more to work with, up to and including "no two bookings may overlap at the same property" as a constraint the database enforces.
3. Who runs it at 3am? A team with ten years of MySQL operational habits will keep a MySQL cluster up better than a Postgres one, whatever the feature table says.

What this track covers

Eighteen posts, in order, each meant to be read in under ten minutes. Install and psql first, then roles, types and constraints, then the SQL you write daily, then jsonb, indexes and EXPLAIN, then transactions, migrations and the production checklist.

Every query in the track is executed before it ships, against a copy of a real booking application's schema holding 400,000 bookings. When you see a query plan here, it is a plan Postgres actually chose.