Postgres vs MySQL vs SQLite

You'll spend most of your career with one of three open-source relational databases: PostgreSQL, MySQL, or SQLite. Knowing what each is good at saves a lot of decisions.

PostgreSQL

Often pronounced "post-gres" or "post-gres-Q-L." Born at Berkeley in the 1980s, modern Postgres is the heavy-duty choice — used by everything from Stripe to Apple to Reddit.

Strengths

• Strict SQL standard compliance
• Rich type system: arrays, ranges, intervals, network addresses, UUID, money, JSON/JSONB
• Powerful extensions: PostGIS (geospatial), pgvector (AI embeddings), TimescaleDB (time-series), pg_trgm (fuzzy text)
• Window functions, CTEs, materialized views, partial/expression indexes
• MVCC for high concurrency — readers never block writers
• Strong replication options (logical & physical)

Trade-offs

• Slightly higher operational complexity than MySQL — vacuum, autovacuum tuning
• Smaller talent pool than MySQL in some industries

Connecting

psql "postgres://user:password@host:5432/dbname"

MySQL (and MariaDB)

The world's most-deployed open-source database, especially behind PHP/WordPress sites. MariaDB is a community fork started after Oracle acquired MySQL — drop-in compatible.

Strengths

• Massive ecosystem, hosting providers, and tooling
• Simple replication (primary/replica with binlog) — easy to set up
• Excellent read performance with InnoDB engine
• Lower learning curve for operations
• Default for WordPress, Drupal, Magento — and managed offerings on every cloud

Trade-offs

• Historically loose with SQL standards (silent type coercion, weaker default isolation) — improved in 8.x
• Fewer advanced types and indexes than Postgres
• JSON support is good but less mature than JSONB in Postgres

Connecting

mysql -h host -u user -p dbname

SQLite

SQLite is unusual: it isn't a server. It's a C library you embed in your application; the database is a single file on disk. Despite that, it's the most-deployed database in the world (every iOS and Android app, every browser, most TVs).

Strengths

• Zero configuration — no server, no users, no ports
• Single-file database — easy to ship, copy, version
• Surprisingly fast for read-heavy workloads (faster than client/server DBs in some benchmarks)
• ACID transactions, full-text search (FTS5), JSON support
• Perfect for: dev/test, mobile apps, embedded systems, edge/serverless, on-disk caches

Trade-offs

• One writer at a time — not for high-concurrency write workloads
• No network access — designed for in-process usage
• Some SQL features missing (RIGHT JOIN before 3.39, no FULL OUTER JOIN historically, limited ALTER TABLE)

Using it

sqlite3 mydatabase.db
sqlite> .tables
sqlite> SELECT * FROM users LIMIT 10;
sqlite> .quit

Side-by-Side Comparison

How to Choose

Pick Postgres when

• You're starting greenfield and want headroom
• You'll need JSON, arrays, geospatial, or full-text search
• You want strict standards compliance and rich types
• Your team values consistency and powerful tooling

Pick MySQL when

• You're using a stack that defaults to it (WordPress, Magento, certain SaaS frameworks)
• Your team has deep MySQL ops experience
• You need simple, well-trodden replication
• Hosting/managed options on your platform are MySQL-first

Pick SQLite when

• It's a single-user app (mobile, desktop)
• You want a unit-test database with zero setup
• You're at the edge / serverless and want minimal moving parts
• Reads dominate writes and the dataset fits on one machine
• You want a file you can ship, copy, or commit

A Note on Drop-Ins and Compatibility

• MariaDB — community fork of MySQL; mostly drop-in.
• Aurora MySQL/Postgres — AWS reimplementations of MySQL/Postgres on a distributed storage layer; mostly compatible.
• CockroachDB, YugabyteDB — distributed SQL databases that speak the Postgres wire protocol.
• PlanetScale — managed MySQL-compatible (Vitess) with branching and zero-downtime schema changes.
• libSQL / Turso — fork of SQLite with replication and remote access.

Common Dialect Differences (Quick Reference)

The 80% you write is identical across all three. The 20% is what migrations and abstraction layers exist for.