MySQL Vs PostgreSQL
MySQL and PostgreSQL are the two most popular open-source relational databases. Both are mature, fast and reliable, and both run the core SQL you learn on this site. Their differences lie in philosophy, data types, some SQL behaviour, extensibility and operations. This page compares them with examples.
1. Overview
| MySQL | PostgreSQL | |
|---|---|---|
| First release | 1995 | 1996 (from the Berkeley POSTGRES project, 1986) |
| Owner / governance | Oracle Corporation | PostgreSQL Global Development Group (community) |
| Licence | GPL v2 (+ commercial) | PostgreSQL Licence (permissive, BSD-like) |
| Type | Relational (RDBMS) | Object-relational (ORDBMS) |
| Default port | 3306 | 5432 |
| Reputation | Simple, fast, easy to run, web-friendly | Standards-compliant, feature-rich, extensible |
2. Architecture
- Storage: MySQL has pluggable engines (InnoDB by default) with a clustered primary key. PostgreSQL has one engine that stores rows in heap files, with all indexes pointing to them.
- MVCC: both keep old row versions so readers don't block writers. InnoDB keeps old versions in undo logs; PostgreSQL keeps them in the table and cleans up with
VACUUM(autovacuum). - Schemas: in MySQL, database = schema. In PostgreSQL, a database contains several schemas (
publicby default).
3. SQL Behaviour Differences
| Behaviour | MySQL | PostgreSQL |
|---|---|---|
| String comparison | Case-insensitive by default (utf8mb4_0900_ai_ci) | Case-sensitive; use ILIKE or citext |
| Insert-or-update | INSERT … ON DUPLICATE KEY UPDATE | INSERT … ON CONFLICT … DO UPDATE, MERGE |
| Return changed rows | Not supported; run a SELECT after | INSERT/UPDATE/DELETE … RETURNING |
| Auto-increment | AUTO_INCREMENT | GENERATED AS IDENTITY / SERIAL |
String concat with || | Logical OR (unless PIPES_AS_CONCAT mode) | Concatenation |
| Quoting identifiers | `name` | "name" |
| DDL inside a transaction | Causes an implicit commit | Transactional: CREATE TABLE can be rolled back |
| Default isolation | REPEATABLE READ | READ COMMITTED |
Case-insensitive comparison in MySQL, shown live:
CREATE DATABASE pgcompare;
USE pgcompare;
CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(60) UNIQUE, visits INT DEFAULT 0);
INSERT INTO users VALUES (1, 'Asha@Example.com', 3);
SELECT id, email FROM users WHERE email = 'asha@example.com';Result: 1 row
| id | |
|---|---|
| 1 | Asha@Example.com |
In PostgreSQL the same query returns no rows unless you compare with lower(email) or use citext. MySQL's case-insensitive collation also makes the UNIQUE index treat 'ASHA@EXAMPLE.COM' as a duplicate:
INSERT INTO users VALUES (2, 'ASHA@EXAMPLE.COM', 0);ERROR 1062 (23000): Duplicate entry 'ASHA@EXAMPLE.COM' for key 'users.email'Upsert syntax side by side. MySQL (run here):
INSERT INTO users (id, email, visits) VALUES (1, 'Asha@Example.com', 1)
ON DUPLICATE KEY UPDATE visits = visits + 1;
SELECT id, visits FROM users;Result: 1 row
| id | visits |
|---|---|
| 1 | 4 |
INSERT INTO users (id, email, visits) VALUES (1, 'Asha@Example.com', 1)
ON CONFLICT (id) DO UPDATE SET visits = users.visits + 1
RETURNING id, visits;4. Data Types
| Area | MySQL | PostgreSQL |
|---|---|---|
| JSON | JSON (binary, indexed via generated columns or multi-valued indexes) | json and jsonb (binary, GIN-indexable) |
| Arrays | No (use JSON arrays) | Native arrays: int[], text[] |
| Ranges | No | daterange, int4range with overlap operators |
| UUID | BINARY(16) + UUID_TO_BIN() | Native uuid |
| Network types | No | inet, cidr, macaddr |
| Custom types | No | CREATE TYPE, domains, enums |
| Unsigned integers | Yes (INT UNSIGNED) | No |
| Spatial | Built-in GIS types | PostGIS extension (very powerful) |
5. Indexes and Extensions
- MySQL: B-tree (most), hash (MEMORY), full-text, spatial (R-tree), functional and multi-valued (JSON array) indexes; invisible indexes for safe testing.
- PostgreSQL: B-tree, hash, GIN, GiST, SP-GiST and BRIN, plus partial indexes (
WHERE active) and expression indexes. - Extensions: PostgreSQL's extension system is a major strength: PostGIS, TimescaleDB, pgvector, pg_stat_statements. MySQL extends through components and plugins, but the ecosystem is smaller.
6. Replication, Scaling and Operations
| Topic | MySQL | PostgreSQL |
|---|---|---|
| Built-in replication | Binary-log replication (row-based), GTIDs; Group Replication | Streaming (physical) and logical replication |
| HA packages | InnoDB Cluster (Router + Shell) | Patroni, repmgr, cloud services |
| Connection scaling | Thousands of threads are fine | Use a pooler (PgBouncer) for many connections |
| Maintenance | Little routine maintenance | Autovacuum must keep up on busy tables |
| Online schema change | Instant ADD COLUMN; gh-ost / pt-online-schema-change tools | Many changes are fast; transactional DDL |
7. Which Should You Choose?
| MySQL is a great fit when… | PostgreSQL is a great fit when… |
|---|---|
| You want simplicity and easy operations | You need advanced SQL and data types (arrays, ranges, jsonb) |
| Workloads are high-volume, simple reads and writes | You run complex analytical queries |
| You use common stacks: WordPress, LAMP, many SaaS tools | You need GIS (PostGIS) or vector search (pgvector) |
| You want case-insensitive text matching by default | You need strict standard compliance and transactional DDL |
For learning SQL either is fine: the SELECT, JOIN, GROUP BY and transaction skills on this site transfer directly.
8. Summary
- Both are excellent open-source relational databases; MySQL favours simplicity and speed, PostgreSQL favours features and extensibility.
- MySQL compares strings case-insensitively by default; PostgreSQL is case-sensitive.
- Upserts:
ON DUPLICATE KEY UPDATE(MySQL) vsON CONFLICT(PostgreSQL); PostgreSQL also hasRETURNING. - PostgreSQL has richer types and index kinds; MySQL handles many connections with lighter threads.
- Pick by team skills, workload and the features you actually need.
Was this tutorial helpful?