PostgreSQL, MySQL, SQLite — and why to start here

The families lesson gave the landscape. This one is narrower and more immediately useful: which relational database, and why this course spends most of its length on PostgreSQL.

The full decision — including when not to use a relational database at all — is module 13. This is the version you need to get started.

The honest default

Start with PostgreSQL. For a new project, with no unusual constraint, it is the right answer, and the burden of proof is on anything else.

Three reasons, in order of how much they actually matter:

It is hard to outgrow. It does relational properly, and it also does JSON documents with indexes, full-text search, geospatial queries, time-series through TimescaleDB, and vector similarity through pgvector. The most common "we need a second database" conversation ends with "actually Postgres does that" — which is worth a great deal, because a second database is a second thing to operate, back up, monitor and keep in step.

It refuses to accept nonsense. Strict about types, strict about constraints, and it tells you when you are wrong rather than guessing. MySQL historically guessed — silently truncating a too-long string, turning an invalid date into zeroes — and although modern versions default to strict mode, the culture difference persists. A database that says no is doing you a favour.

It is free, and genuinely so. PostgreSQL's licence has no company behind it that can change its mind. That has happened to other databases, more than once, and it is a real risk to weigh.

The three you will actually meet

PostgreSQL

Use it for: almost everything. Web applications, APIs, analytics up to a substantial size, anything with real relationships.

Strengths: correctness; the richest type system of any relational database (arrays, JSONB, ranges, network addresses, full-text vectors); extensions that add whole capabilities; excellent concurrency through MVCC; a planner that handles complicated queries well.

Costs: heavier on memory than MySQL for small workloads; connection handling needs a pooler sooner than people expect, because each connection is a process; and the documentation is comprehensive rather than gentle.

MySQL and MariaDB

Use it for: an existing project that already uses it. WordPress and much of the PHP world. Hosting where it is the only option.

Strengths: ubiquitous; slightly simpler to operate; very fast for simple read-heavy workloads; every hosting provider supports it.

Costs: a weaker type system; historically lax about bad data; fewer advanced features; and a more complicated ownership story since the Oracle acquisition, which is why MariaDB exists as a fork.

It is not a bad database. If you join a team using it, use it well and do not campaign to migrate. The SQL you learn here transfers almost entirely.

SQLite

Use it for: mobile apps, desktop apps, small tools, tests, and anything embedded.

Strengths: no server, no installation, no configuration. One file you can copy. Built into Python. Genuinely the most deployed database in the world, because it is in every phone and every browser.

Costs: one writer at a time; no network access, so not for a multi-server web app; a deliberately loose type system; fewer concurrency controls.

This course uses PostgreSQL, and almost everything transfers to SQLite for the times it is the better fit.

The ones you will hear about

Briefly, so the names are not mysterious:

SQL Server — Microsoft's. Excellent, expensive, and everywhere in enterprises on the Microsoft stack. Oracle — the incumbent in large enterprises and banks; technically very capable, commercially notorious. CockroachDB, YugabyteDB, TiDB — distributed SQL: PostgreSQL or MySQL compatibility with horizontal write scaling, at the cost of latency and operational complexity. Amazon Aurora, Google Cloud SQL, Azure Database, Supabase, Neon — managed PostgreSQL or MySQL; the same database, operated for you.

That last group is worth a sentence of its own. For most projects, managed PostgreSQL is the right production answer. You are not paying for a different database, you are paying to not do backups, failover and upgrades yourself at three in the morning. Learn on a local install; deploy on a managed one.

Questions that actually change the answer

Most "which database" arguments are decided by one of these, and none of them is about benchmarks.

What does the team already know? A database nobody can debug at 2am is a bad choice regardless of its merits. This outranks almost everything else.

What does your hosting support? Sometimes the decision is already made.

Is there an existing system to integrate with? Matching it is usually cheaper than bridging.

Do you need something specific? Geospatial — PostGIS, so PostgreSQL. Embedded — SQLite. Vast write volume across machines — see module 12.

How much data, honestly? Not how much you hope. PostgreSQL on one reasonable machine handles hundreds of gigabytes and tens of thousands of transactions a second. Most applications never approach the point where the choice is forced, and designing for a scale you do not have is how projects acquire complexity they never needed.

What does not decide it

Worth saying plainly, because these arguments come up constantly.

Benchmarks. They measure someone else's workload on someone else's hardware. The difference between databases is almost always smaller than the difference between a good schema and a bad one, or an index present and absent.

"It scales better." Usually true of a scale you do not have, and paid for in complexity you do have. Have the problem first.

Fashion. Databases have fashions like anything else. The one being written about this year is not necessarily the one you should be operating.

Which one a big company uses. Their constraints are not yours. A company with 4,000 engineers and a dedicated database team can run things you cannot.

What transfers

Reassurance, because this is a real worry when picking a first one to learn.

Almost all of it. SELECT, WHERE, JOIN, GROUP BY, indexes, transactions, normalisation, reading a query plan — these are the same everywhere, and they are what this course is mostly about. The differences are at the edges: type names, upsert syntax, date functions, the exact EXPLAIN format.

What you are actually learning is how to think about data, and that transfers to MongoDB and Redis too, which is why the later modules are shorter than they would otherwise need to be.

For this course

Modules 2–9    PostgreSQL 16
Module 10      MongoDB
Module 11      Redis
Module 12      a look at Cassandra, Neo4j, Elasticsearch, TimescaleDB, pgvector
Module 14      the capstone, using PostgreSQL and Redis together

All free, all runnable on your own machine, all with Docker as the easy path. The next lesson installs PostgreSQL.

Check your work

The default, and why. PostgreSQL: hard to outgrow, strict about bad data, and genuinely free.

What "hard to outgrow" means concretely. JSONB, full-text search, geospatial, time-series and vector are all available without a second database.

Why strictness is a feature. A database that says no is telling you something is wrong now rather than later.

When MySQL is right. When it is already there. Do not campaign to migrate.

When SQLite is right. Mobile, desktop, tools, tests — anything embedded with one writer.

What managed PostgreSQL buys. Not a different database; not doing backups and failover yourself.

The question that outranks the others. What the team can debug at 2am.

Three things that do not decide it. Benchmarks, scaling claims for scale you do not have, and what a big company does.

How much transfers between relational databases. Almost all of it — the differences are at the edges.

Practice

1. Write down the database used by three projects you have worked on or seen, and why.
2. Install PostgreSQL and SQLite. Note how long each took.
3. Create the same table in both. Note the differences in type names.
4. Insert a 300-character string into a varchar(100) in both and compare what happens.
5. Insert '2026-02-30' as a date in both and compare.
6. Look up PostGIS, TimescaleDB and pgvector. Write one sentence on what each adds.
7. Find the pricing for managed PostgreSQL from two providers at the smallest tier.
8. Find one benchmark comparing PostgreSQL and MySQL and identify the workload it measured. Decide whether it resembles yours.
9. Estimate the row count of the largest table in a project you have built. Compare it with what one PostgreSQL machine handles.
10. Look up when PostgreSQL, MySQL and SQLite were first released.
11. Find out what happened to MySQL's ownership and why MariaDB exists.

Official documentation

• PostgreSQL — About — Feature list and limits, including the maximum table size, which is larger than you expect.
• PostgreSQL — Licence — Short enough to read in full; worth doing once.
• SQLite — Appropriate uses — The clearest statement of when it is and is not the right answer.
• MySQL — Differences from standard SQL — Where it departs, which is where your SQL will not transfer.
• PostgreSQL — Server administration — What a managed provider is doing on your behalf.
• DB-Engines ranking — Relative popularity over time, which is a reasonable proxy for how easy it is to hire for.

Next: installing it, and connecting.