Postgres vs MySQL vs SQLite: Choosing a Database

The three databases most new developers meet are PostgreSQL, MySQL, and SQLite. They all speak SQL, they all store relational data, and yet they are very different tools. This post lays out where each one shines, where each one frustrates, and the dialect differences that catch you out when you move code between them.

If you’re starting a new project, this post is the one-sitting overview you need.

The 30-second answer

• SQLite — single-file embedded database. Best for desktop apps, mobile apps, CLIs, tests, and small websites.
• MySQL — battle-tested server database with massive deployment history. Pragmatic, fast on simple workloads, ubiquitous in shared hosting.
• PostgreSQL — feature-rich server database with the strongest standard SQL conformance. The default modern choice for new server projects.

If none of those tugs at you, pick Postgres and stop reading. It’s the safest default for almost any new application.

SQLite

SQLite isn’t a server. It’s a C library that reads and writes a single file. Your application opens the file, runs SQL against it, and closes it. There’s no daemon, no network port, no CREATE USER. The file is the database.

Strengths

• Zero setup. Add a library, point it at a path, you’re done. No service to configure, no port to expose.
• Embedded everywhere. Built into iOS, Android, macOS, Windows 10+, every browser, every major language’s standard library or stdlib-equivalent.
• Astonishingly reliable. SQLite is one of the most-tested pieces of software in existence. Aircraft systems and the Linux kernel ship it.
• Fast for the common case. Single-process read-heavy workloads with modest write rates absolutely fly.
• Great for tests. An in-memory SQLite database (:memory:) lets you spin up a fresh schema in a millisecond.

Weaknesses

• Single-writer. Only one transaction can write at a time. Fine for one app, terrible for high-concurrency servers.
• No real network access. It’s not a server, so you can’t have ten machines talking to one SQLite file safely.
• Limited ALTER TABLE. Many schema changes require the rebuild-and-rename dance.
• Loose typing. SQLite’s “type affinity” lets you put a string in an integer column. This catches you exactly once.

When to pick SQLite

• A desktop or mobile app needing local storage.
• A small website with one process and modest traffic.
• A CLI tool or background job that needs to remember things between runs.
• Your test suite (almost always).

MySQL

MySQL has powered enormous chunks of the web since the 2000s. It’s pragmatic, fast, and everywhere — most shared-hosting providers offer it by default, and forks like MariaDB keep the ecosystem healthy.

Strengths

• Ubiquity. Every hosting provider, every framework, every ORM speaks it. Documentation, blog posts, Stack Overflow answers are endless.
• Fast on simple workloads. Read-heavy applications, especially ones with hot indexed lookups, run very well.
• Replication is mature. Source/replica setups are well understood, with tooling that’s been refined for two decades.
• Multi-storage-engine architecture. InnoDB is the default and what you want; the historical MyISAM engine is mostly relevant for legacy systems.

Weaknesses

• SQL conformance has historically been loose. Strict mode is the default now, but you still meet legacy databases where invalid dates silently became 0000-00-00.
• JSON support arrived late. It works, but Postgres’s JSONB is still the gold standard.
• Window functions and CTEs took a long time to land. Modern MySQL (8+) has them; older deployments often don’t.
• Licensing and governance moved under Oracle in 2010. The community response (MariaDB) is the result.

When to pick MySQL

• You’re working with an existing MySQL system. There’s no compelling reason to migrate.
• Your hosting environment offers MySQL by default and Postgres is awkward to provision.
• You want a known-good database for an application built on a CMS or framework that integrates more tightly with MySQL (WordPress, classic LAMP stacks).

PostgreSQL

Postgres is the “kitchen-sink” of relational databases — the strongest standard SQL conformance, the richest type system, the deepest extension ecosystem. It’s the modern default for new server-side projects.

Strengths

• Standards conformance. Postgres usually does what the SQL standard says it should. Code you write here ports more cleanly than code written for MySQL.
• Type system. First-class arrays, ranges, JSONB, UUID, network addresses, geometric types. Custom types if you need them.
• JSONB. A binary JSON type that’s indexable and queryable. The best way to store semi-structured data in a relational database.
• Extensions. PostGIS for geo data. pgvector for embeddings. pg_trgm for fuzzy text search. Full-text search built in.
• Concurrency model. MVCC (multi-version concurrency control) means readers never block writers and vice versa.
• Window functions, CTEs, recursive CTEs have been first-class for years.

Weaknesses

• Operational overhead. Connection pooling, vacuuming, replication setup are all things you have to think about at scale.
• Defaults aren’t tuned for production. Out of the box, Postgres assumes a small machine. Production deployments need tweaking.
• Some hosting environments don’t offer it. Less common on basic shared hosting; trivial on any modern PaaS.

When to pick Postgres

• New server-side application of any kind.
• You expect to use JSON columns, geo data, full-text search, or vector embeddings.
• You want the closest thing to “standard SQL” so your skills and your code are portable.
• Your team values correctness over raw write throughput.

A quick comparison table

Concern	SQLite	MySQL	Postgres
Single-file embedded	Yes	No	No
Server / network	No	Yes	Yes
Concurrent writers	One	Many	Many (MVCC)
JSON	Limited	Decent (JSON)	Best (JSONB)
Full-text search	FTS5 extension	Built in (InnoDB)	Built in (tsvector)
Standard SQL conformance	Reasonable	Improving	Strongest
Extensions	Few	Few	Many
Default for new server apps	No	Sometimes	Yes

Dialect differences that bite

This is where porting code gets interesting. Same SQL, different answers.

LIMIT vs TOP vs FETCH

-- Postgres, MySQL, SQLite
SELECT * FROM users ORDER BY id LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM users ORDER BY id;

-- Standard SQL (Postgres also supports this)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

The good news: Postgres, MySQL, and SQLite all use LIMIT. SQL Server and Oracle have their own variations.

Auto-incrementing primary keys

-- SQLite
id INTEGER PRIMARY KEY              -- auto-increments

-- MySQL
id INT PRIMARY KEY AUTO_INCREMENT

-- Postgres (modern)
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- Postgres (legacy, still common)
id SERIAL PRIMARY KEY

Same effect, three different syntaxes.

String concatenation

-- Standard, Postgres, SQLite
SELECT first_name || ' ' || last_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;

|| is the standard operator; MySQL repurposes it for logical OR unless you change settings.

Booleans

-- Postgres, MySQL
WHERE is_active = TRUE

-- SQLite (no real boolean type; uses 0/1)
WHERE is_active = 1

SQLite accepts TRUE/FALSE keywords but stores 1/0.

Current timestamp

-- All three
CURRENT_TIMESTAMP
NOW()        -- not in SQLite for backward compatibility, but available in Postgres/MySQL

For portability, CURRENT_TIMESTAMP is the safest.

Case sensitivity

• Identifiers: Postgres folds unquoted identifiers to lowercase. MySQL depends on the OS filesystem. SQLite is case-insensitive on identifiers.
• String comparisons: Postgres is case-sensitive. MySQL’s default collation is case-insensitive. SQLite depends on the column’s collation.

SELECT * FROM users WHERE name = 'ada' returns Ada on MySQL and nothing on Postgres. This is a constant source of bug reports when teams move between the two.

JSON

-- Postgres JSONB
SELECT data->>'email' FROM events WHERE data->>'type' = 'signup';
CREATE INDEX idx_events_type ON events ((data->>'type'));

-- MySQL JSON
SELECT JSON_EXTRACT(data, '$.email') FROM events WHERE JSON_EXTRACT(data, '$.type') = 'signup';

-- SQLite (1.x JSON extension)
SELECT json_extract(data, '$.email') FROM events WHERE json_extract(data, '$.type') = 'signup';

Postgres has the cleanest syntax and the only one that indexes nested fields out of the box.

Full-text search

• Postgres: built-in tsvector / tsquery with ranking.
• MySQL: FULLTEXT index on InnoDB.
• SQLite: FTS5 virtual-table extension.

All three handle “search blog posts for ‘sql tutorials’” quite well. None of them replaces a dedicated search engine for large or relevance-sensitive workloads.

Hosting and operational concerns

The technical comparison is rarely the whole story. Where will the database run?

• Managed services. AWS RDS, Google Cloud SQL, Supabase, Neon, PlanetScale, Turso. Most support Postgres and MySQL; SQLite has a small but growing edge-managed ecosystem.
• Self-hosted. Postgres and MySQL need a real machine, backups, monitoring, and someone who understands them. SQLite has none of this overhead.
• Edge. SQLite-shaped systems (Turso/libSQL, Cloudflare D1) put the database next to the request, which can be transformative for latency-sensitive apps.
• CI/CD. SQLite makes test suites trivially fast. Postgres in Docker is the next-best thing and now standard.

Backups

• SQLite: copy the file. Use the online backup API for live databases.
• MySQL / Postgres: logical backups (mysqldump, pg_dump) and physical backups (replication snapshots). Managed services do both automatically.

Connection pooling

Server databases under load need a connection pool. The application talks to the pool; the pool maintains a small number of live database connections. PgBouncer for Postgres and ProxySQL for MySQL are the canonical tools. Many managed services include them now.

A decision flow

If I were sketching this on a whiteboard:

1. Is this an embedded, single-machine workload? → SQLite.
2. Are you required to use a specific database by hosting, employer, or framework? → Use what’s required.
3. Is the application going to grow, use JSON, geo data, or vectors? → Postgres.
4. Is the team deeply experienced with MySQL and the app fits the sweet spot? → MySQL.
5. Otherwise? → Postgres.

That covers 95% of new projects.

Common beginner mistakes

• Choosing MySQL out of inertia for a brand new project when Postgres would serve better.
• Choosing SQLite for a multi-process web app, then discovering write contention.
• Assuming JSON support is identical across databases.
• Relying on MySQL’s case-insensitive defaults and then breaking when you deploy to Postgres.
• Treating “auto-increment primary key” as a single concept and getting bitten by dialect differences.

Recap

You now know:

• SQLite is embedded, single-file, perfect for apps and tests
• MySQL is ubiquitous, fast, and battle-tested
• Postgres is the modern default with the richest feature set
• Dialect differences (LIMIT/TOP, autoincrement, JSON syntax, case sensitivity) matter when porting
• Hosting and operations often decide before the technical merits do

There’s no single “best” database. There’s a database that fits your situation — and most often these days, that’s Postgres.

Next steps

With a database chosen, the next layer up is connecting to it from application code: drivers, query builders, ORMs, migrations. Each ecosystem has its conventions, but the SQL underneath is what you’ve already learned.

Related reading: What Is SQL?, SQL Indexes and Performance, SQL CREATE TABLE and INSERT.

Questions or feedback? Email codeloomdevv@gmail.com.