PostgreSQL and MySQL are the two most widely deployed open-source relational databases, and “which should I use?” comes up in almost every new project. Both are mature, battle-tested, and more than capable for most workloads. The differences that matter are in the details: standards compliance, data types, JSON support, replication, and the edge cases that surface at scale. Let’s go through them concretely.

A Quick History

MySQL (1995) was built for speed, particularly web applications where fast reads mattered most. It was the “M” in the LAMP stack and the database behind early Facebook, Twitter, and Wikipedia.

PostgreSQL (1996, with roots in the 1980s) was designed from the start as a feature-complete, standards-compliant database. It moves more slowly and prioritizes correctness over raw speed.

Both are excellent. The cliché holds: PostgreSQL is the database for people who care about data integrity; MySQL is the database for people who care about getting something shipped. In practice, modern MySQL (8.0+) has narrowed the gap significantly.

Data Types

PostgreSQL has a much richer type system:

-- PostgreSQL-only types
uuid          -- native UUID, not varchar
jsonb         -- binary JSON with indexing
json          -- text JSON
cidr / inet   -- IP addresses
tsvector      -- full-text search
point, polygon, circle  -- geometric types
int4range, tsrange      -- range types
hstore        -- key-value store
ARRAY         -- any type as an array

MySQL supports JSON (since 5.7), but its implementation is less mature — no GIN indexes, no jsonb-style operators.

-- MySQL JSON
SELECT data->>'$.name' FROM products WHERE data->>'$.price' > 100;

-- PostgreSQL JSONB (richer operators, GIN-indexable)
SELECT data->>'name' FROM products WHERE (data->>'price')::numeric > 100;
CREATE INDEX idx_products_data ON products USING gin (data);
SELECT * FROM products WHERE data @> '{"category": "electronics"}';

Transactions and MVCC

Both use MVCC (Multi-Version Concurrency Control) to allow concurrent reads and writes without locking. The implementations differ:

PostgreSQL — full MVCC across all operations. Readers never block writers and vice versa. VACUUM cleans up dead tuple versions periodically.

MySQL (InnoDB) — also uses MVCC, but only for SELECT. DDL statements (ALTER TABLE, ADD COLUMN) in MySQL can still take table locks, which matters for zero-downtime migrations on large tables. MySQL 8.0+ improved this with online DDL, but PostgreSQL’s pg_repack and ALTER TABLE ... CONCURRENTLY patterns are generally more flexible.

Both databases support READ COMMITTED, REPEATABLE READ, and SERIALIZABLE isolation levels. PostgreSQL adds READ UNCOMMITTED as an alias for READ COMMITTED (refusing dirty reads even when asked).

Standards Compliance

PostgreSQL is closer to the SQL standard and enforces it more strictly. MySQL has historically been more permissive, which is either helpful or a footgun depending on your perspective:

-- MySQL (older defaults) allows this; PostgreSQL does not
SELECT id, name FROM users GROUP BY id;  -- name not in GROUP BY and not aggregated

-- MySQL: returns a random value for name (silently wrong)
-- PostgreSQL: error — column must appear in GROUP BY or be aggregated

MySQL’s sql_mode=STRICT_ALL_TABLES (now the default in 8.0) closes many of these gaps.

Full-Text Search

PostgreSQL has a capable built-in full-text search engine:

-- Create a tsvector column and index
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_articles_fts ON articles USING gin (search_vector);

-- Search
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'python & tutorial')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'python & tutorial')) DESC;

MySQL also has full-text search via FULLTEXT indexes, but PostgreSQL’s implementation supports more languages, custom dictionaries, and rank-based ordering out of the box.

For serious search workloads, both databases often hand off to Elasticsearch anyway — but for “search as a feature” on moderate data volumes, PostgreSQL holds up well without an additional service.

Replication

MySQL has had mature replication since the early 2000s. Its binary log replication is simple to set up and widely understood. MySQL Group Replication and InnoDB Cluster provide multi-primary setups.

PostgreSQL uses streaming replication (physical) or logical replication (table-level, across versions). Logical replication is powerful for zero-downtime major version upgrades and selective replication to a read replica. Tools like Patroni and repmgr manage failover.

-- PostgreSQL: create a logical replication slot
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');

-- Check replication lag on a standby
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Performance

For simple point reads and high-throughput inserts, MySQL can edge out PostgreSQL in benchmarks. For complex analytical queries, JOINs over many tables, and queries that benefit from PostgreSQL’s richer query planner, PostgreSQL often wins.

The practical difference: on most web application workloads, neither database is the bottleneck. Query design, indexing, connection pooling, and caching matter far more than which database you chose.

Extensions

PostgreSQL has a rich extension ecosystem:

CREATE EXTENSION IF NOT EXISTS postgis;        -- geospatial
CREATE EXTENSION IF NOT EXISTS pg_trgm;        -- trigram similarity search
CREATE EXTENSION IF NOT EXISTS uuid-ossp;      -- UUID generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;       -- encryption functions
CREATE EXTENSION IF NOT EXISTS timescaledb;    -- time-series (third-party)
CREATE EXTENSION IF NOT EXISTS pgvector;       -- vector similarity search (AI/ML)

MySQL has plugins but the ecosystem is narrower. pgvector in particular has made PostgreSQL the default choice for AI applications that need vector similarity search alongside structured data.

Choosing Between Them

If you need… Consider
Maximum ecosystem compatibility (shared hosting, cPanel) MySQL
JSONB + GIN indexing for document-style queries PostgreSQL
Complex queries, CTEs, window functions PostgreSQL
Existing team expertise with MySQL MySQL
Geospatial queries PostgreSQL + PostGIS
Vector similarity search PostgreSQL + pgvector
Simple web app, standard CRUD Either — both are fine
Managed cloud database with least configuration Either (RDS, Cloud SQL, Aurora, Supabase all support both)

Conclusion

If you’re starting a new project with no constraints, PostgreSQL is the safer long-term choice: better standards compliance, richer types, more powerful extensions, and a query planner that handles complex queries well. MySQL remains a solid choice where team familiarity or ecosystem requirements point to it, and modern MySQL 8.0 has addressed many of the historical gaps. The decision rarely matters as much as the schema design, indexing strategy, and query patterns you choose — both databases will serve you well if you treat them with care.