#Two philosophies
AddedMySQL and PostgreSQL both speak SQL, both store rows in B-tree-indexed tables with MVCC and write-ahead logs, and both run most of the web. They grew from different priorities, and those priorities still show in everyday behaviour:
- MySQL started (1995) as a fast, simple database for web applications. It favoured speed and ease of use, was lenient by default (bad values became warnings, not errors), and put replication first. Its architecture separates the SQL server from pluggable storage engines — today almost always InnoDB.
- PostgreSQL grew out of the Berkeley POSTGRES research project (1986) and aims for correctness, standards and extensibility: strict typing, transactional everything (including DDL), and a type and index system you can extend with your own types, operators, index methods and languages.
A rough mental model: MySQL is a well-tuned appliance — it does a known set of things very fast and is easy to operate; PostgreSQL is a workshop — more tools, stricter rules, and you can build new tools into it. Since MySQL 5.7/8.0 turned strict mode on by default and added CTEs, window functions and JSON, the gap in everyday SQL is much smaller than old blog posts suggest, but it hasn't closed.
The quickest way to feel the difference is to give both the same slightly sloppy SQL:
MySQL 9.7
SELECT 7 / 2 AS division;
SELECT '5' + 3 AS text_plus_number;
SELECT 'abc' + 1 AS junk_plus_number;
SELECT 'a' || 'b' AS pipes;
SELECT 1 / 0 AS by_zero;
SELECT 'Rohit' = 'rohit' AS same_name;+----------+
| division |
+----------+
| 3.5000 |
+----------+
+------------------+
| text_plus_number |
+------------------+
| 8 |
+------------------+
+------------------+
| junk_plus_number |
+------------------+
| 1 |
+------------------+
Warning (Code 1292): Truncated incorrect DOUBLE value: 'abc'
+-------+
| pipes |
+-------+
| 0 |
+-------+
Warning (Code 1287): '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
Warning (Code 1292): Truncated incorrect DOUBLE value: 'a'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'b'
+---------+
| by_zero |
+---------+
| NULL |
+---------+
Warning (Code 1365): Division by 0
+-----------+
| same_name |
+-----------+
| 1 |
+-----------+PostgreSQL 18
SELECT 7 / 2 AS division;
SELECT '5' + 3 AS text_plus_number;
SELECT 'abc' + 1 AS junk_plus_number;
SELECT 'a' || 'b' AS pipes;
SELECT 1 / 0 AS by_zero;
SELECT 'Rohit' = 'rohit' AS same_name; division
----------
3
(1 row)
text_plus_number
------------------
8
(1 row)
ERROR: invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc' + 1 AS junk_plus_number;
^
pipes
-------
ab
(1 row)
ERROR: division by zero
same_name
-----------
f
(1 row)What’s happening
7 / 2: MySQL's/always returns a decimal,3.5000. PostgreSQL follows the operand types — integer divided by integer is integer division — so3. Write7.0 / 2(or7::numeric / 2) in PostgreSQL when you want the fraction.'5' + 3is8in both. In PostgreSQL that's because'5'is an untyped literal that gets resolved as an integer; a realtextcolumn plus a number would be an error.'abc' + 1: MySQL converts'abc'to a number, finds no digits, uses0, returns1and leaves a warning most applications never read. PostgreSQL refuses:invalid input syntax for type integer. This is the philosophy in one line — MySQL tries to produce an answer, PostgreSQL refuses to guess.'a' || 'b': standard SQL string concatenation, so PostgreSQL returnsab. In MySQL||is logical OR (unlesssql_modehasPIPES_AS_CONCAT): both strings become0, the result is0, and MySQL now warns that this meaning is deprecated. UseCONCAT()for portable code.1 / 0:NULLplus a warning in a MySQLSELECT(an error only inINSERT/UPDATEunder strict mode); always an error in PostgreSQL.'Rohit' = 'rohit': true in MySQL because the default collationutf8mb4_0900_ai_ciis case-insensitive; false in PostgreSQL, whose default collations are case-sensitive. This one changes query results, not just error handling.
AdvancedArchitecture: threads vs processes
MySQL serves each connection with a thread inside one mysqld process; PostgreSQL forks a separate operating-system process per connection, plus background processes:
MySQL 9.7
SELECT @@max_connections, @@thread_handling;
SELECT TYPE, COUNT(*) AS threads FROM performance_schema.threads GROUP BY TYPE;
SELECT THREAD_OS_ID, PROCESSLIST_ID, NAME FROM performance_schema.threads
WHERE PROCESSLIST_ID = CONNECTION_ID();+-------------------+---------------------------+
| @@max_connections | @@thread_handling |
+-------------------+---------------------------+
| 151 | one-thread-per-connection |
+-------------------+---------------------------+
+------------+---------+
| TYPE | threads |
+------------+---------+
| BACKGROUND | 41 |
| FOREGROUND | 5 |
+------------+---------+
+--------------+----------------+---------------------------+
| THREAD_OS_ID | PROCESSLIST_ID | NAME |
+--------------+----------------+---------------------------+
| 2328 | 1096 | thread/sql/one_connection |
+--------------+----------------+---------------------------+PostgreSQL 18
SHOW max_connections;
SELECT backend_type, count(*) FROM pg_stat_activity
GROUP BY backend_type ORDER BY backend_type;
SELECT pid, backend_type FROM pg_stat_activity WHERE pid = pg_backend_pid(); max_connections
-----------------
100
(1 row)
backend_type | count
------------------------------+-------
autovacuum launcher | 1
background writer | 1
checkpointer | 1
client backend | 1
io worker | 3
logical replication launcher | 1
walwriter | 1
(7 rows)
pid | backend_type
-----+----------------
751 | client backend
(1 row)What’s happening
- MySQL:
one-thread-per-connection, and this connection is the threadthread/sql/one_connectionwith OS thread id 2328, inside the singlemysqldprocess. Background work (purge, page cleaners, I/O) is 41 more threads. - PostgreSQL: this connection is a process with its own pid (
751), a "client backend". The others are separate processes too — checkpointer, background writer, WAL writer, autovacuum launcher, and PostgreSQL 18's new io workers for asynchronous I/O (io_method = workeris the default in 18). - A process per connection costs more memory and setup time than a thread, which is why PostgreSQL's default
max_connectionsis 100 vs MySQL's 151, and why PostgreSQL deployments almost always put a pooler (PgBouncer, or the cloud provider's) in front. MySQL can also exhaust connections, but each one is cheaper. See Connection pooling. - Processes give each session its own private memory and a simple, well-understood model that extensions rely on; moving PostgreSQL to threads has been discussed in the community for years but isn't part of 18. The cost is per-connection overhead, which the pooler absorbs.
AdvancedOld MySQL: the lenient defaults behind the reputation
Much of MySQL's "it accepts garbage" reputation comes from versions before 5.7, whose default sql_mode wasn't strict. Turn strict mode off on 9.7 and the old behaviour returns (see Numeric types: INT, DECIMAL and FLOAT for the clamped values it produced). Today's MySQL default is strict, has CTEs and window functions (8.0+), enforced CHECK constraints (8.0.16+), and rejects zero dates. When someone compares the two databases, ask which version they're thinking of.