PostgreSQL

A 20-year-old vault that is still cooler than most new stuff.

▶ the lesson · narrated sketch video · tap for sound

the intro

PostgreSQL is an open-source relational database: your data lives in tables, and you ask questions with SQL. It handles joins, indexes, and transactions with decades of hardening behind it. If you learn one database deeply, make it this one.

the sample

sql you will actually write

SELECT meme, votes
FROM memes
WHERE funny = true
ORDER BY votes DESC
LIMIT 10;

fun fact ✦

Postgres keeps winning “most loved database” polls. The Swiss Army knife of data.

the docs

the deep dive

the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid

Postgres has been in continuous development since 1986 — it predates Linux — and it keeps absorbing whatever the industry invents: JSON, full-text search, geospatial, even vectors for AI. The running joke in engineering is that you start with Postgres and end up building your whole backend inside it. The joke keeps coming true.

just use Postgres

your SQLthe plannertables on disk
your SQL descends through three layers before touching data

key terms

relation

Postgres-speak for table. The docs and error messages say relation; you say table.

\dt lists your relations

transaction

A group of statements that commits or rolls back as one unit.

BEGIN; UPDATE ...; COMMIT;

jsonb

A binary JSON column you can index and query with SQL operators. Schemaless data living inside a strict schema.

data->>'theme' = 'dark'

psql

The terminal client. Half of Postgres wisdom hides in its backslash shortcuts.

\d users describes a table

how it works

  1. 1
    Install and connect

    One package install and the server runs as a background service. Connect with psql and you are in.

  2. 2
    Create a database and role

    CREATE DATABASE keeps projects separate; CREATE ROLE with LOGIN gives your app its own credentials.

  3. 3
    Model, seed, and query

    Define a few tables, load sample rows, and drill SELECTs until writing them feels like typing your thoughts.

  4. 4
    Measure before optimizing

    EXPLAIN ANALYZE any slow query. The plan usually points at a missing index, not a slow database.

the code, explained

two things Postgres does beautifully: returning writes and grouping

1INSERT INTO users (email, name)
2VALUES ('[email protected]', 'Ada')
3RETURNING id, created_at;
the new id comes back in the same round trip
4
5SELECT date_trunc('day', created_at) AS day,
bucket rows by day for the report
6 count(*) AS signups
7FROM users
8GROUP BY day;
collapse each day into one row

side by side

PostgreSQL

  • strict on bad data by default
  • rich types: jsonb, arrays, ranges
  • the top pick for new projects
  • one engine, endless extensions
VS

MySQL

  • forgiving defaults can hide bugs
  • fast simple reads, huge install base
  • powers legacy stacks everywhere
  • dialect differs between forks
🪤 gotcha

Postgres does not shrink a table when you DELETE — the dead rows sit there until autovacuum reclaims the space for reuse. Bulk-delete ten million rows and disk usage will not budge. That is normal, not a bug.

quick answers

Why Postgres over MySQL?

Stricter defaults, richer types (jsonb, arrays, ranges), and one sane engine. MySQL is great too; Postgres is the safer default for new work.

How much data can it handle?

Far more than you think. Single Postgres instances serve terabytes; Instagram famously ran on sharded Postgres for years.

Is Postgres actually free?

Yes, the PostgreSQL License is permissive. Managed hosting (RDS, Supabase, Neon) charges for the operations, never the software.

quick hits about PostgreSQL

short answers · interviews, tips, trivia & hidden gems

🧠 trivia

SQL was called SEQUELwatch ↗

💎 deep

INSERT that hands the row backwatch ↗

🔧 useful

Ask the database why it is slowwatch ↗

🔧 useful

Navigate psql like a prowatch ↗

🎤 interview

GROUP BY vs ORDER BY?watch ↗

💎 deep

Readable queries with WITHwatch ↗

💎 deep

The hidden superpower functionwatch ↗