PostgreSQL or MySQL: practical differences

docker run -d --rm --name kb-pg-pgvsmy -e POSTGRES_PASSWORD=test postgres:17-alpine
docker run -d --rm --name kb-mysql-pgvsmy -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=kb mysql:8.4
docker cp compare.sql kb-pg-pgvsmy:/tmp/

-- compare.sql — one script for PostgreSQL 17 and MySQL 8.4
CREATE TABLE customers (
  id     integer PRIMARY KEY,
  email  varchar(50) NOT NULL UNIQUE,
  name   varchar(10) NOT NULL,
  active boolean NOT NULL DEFAULT true
);

INSERT INTO customers (id, email, name) VALUES (1, 'aigerim@example.com', 'Aigerim');

-- 1. The same email in a different case
INSERT INTO customers (id, email, name) VALUES (2, 'Aigerim@Example.com', 'Aigerim');
SELECT 'Almaty' = 'ALMATY' AS same_text;

-- 2. A value longer than the column
INSERT INTO customers (id, email, name) VALUES (3, 'kon@example.com', 'Konstantin Ivanov');

-- 3. Integer division and the || operator
SELECT 7 / 2 AS division;
SELECT 'Inter' || 'LAB' AS joined;

-- 4. What a boolean looks like
SELECT id, active FROM customers ORDER BY id;

-- 5. Can you roll back CREATE TABLE?
BEGIN;
CREATE TABLE draft (id integer);
ROLLBACK;
SELECT count(*) AS draft_exists FROM information_schema.tables WHERE table_name = 'draft';

-- 6. Get the new row back in the same statement
INSERT INTO customers (id, email, name) VALUES (4, 'timur@example.com', 'Timur') RETURNING id, active;

$ docker exec kb-pg-pgvsmy psql -U postgres -f /tmp/compare.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
 same_text 
-----------
 f
(1 row)

psql:/tmp/compare.sql:16: ERROR:  value too long for type character varying(10)
 division 
----------
        3
(1 row)

  joined  
----------
 InterLAB
(1 row)

 id | active 
----+--------
  1 | t
  2 | t
(2 rows)

BEGIN
CREATE TABLE
ROLLBACK
 draft_exists 
--------------
            0
(1 row)

 id | active 
----+--------
  4 | t
(1 row)

INSERT 0 1

$ docker exec -i -e MYSQL_PWD=test kb-mysql-pgvsmy mysql -uroot --force --unbuffered --show-warnings -t kb < compare.sql
ERROR 1062 (23000) at line 12: Duplicate entry 'Aigerim@Example.com' for key 'customers.email'
+-----------+
| same_text |
+-----------+
|         1 |
+-----------+
ERROR 1406 (22001) at line 16: Data too long for column 'name' at row 1
+----------+
| division |
+----------+
|   3.5000 |
+----------+
+--------+
| joined |
+--------+
|      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: 'Inter'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'LAB'
+----+--------+
| id | active |
+----+--------+
|  1 |      1 |
+----+--------+
+--------------+
| draft_exists |
+--------------+
|            1 |
+--------------+
ERROR 1064 (42000) at line 32: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING id, active' at line 1

$ docker exec kb-pg-pgvsmy psql -U postgres -c 'SHOW transaction_isolation'
 transaction_isolation 
-----------------------
 read committed
(1 row)

$ docker exec -e MYSQL_PWD=test kb-mysql-pgvsmy mysql -uroot -t -e 'SELECT @@transaction_isolation'
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

#!/usr/bin/env bash
# isolation.sh — session A reads twice inside one transaction,
# session B inserts and commits a row between those reads.
# Usage: ./isolation.sh pg | ./isolation.sh mysql
set -eu

if [ "$1" = "pg" ]; then
  client() { docker exec -i kb-pg-pgvsmy psql -U postgres -qtA; }
else
  client() { docker exec -i -e MYSQL_PWD=test kb-mysql-pgvsmy mysql -uroot -N --unbuffered kb; }
fi

{
  echo "BEGIN;"
  echo "SELECT 'A, first read:', count(*) FROM customers WHERE id >= 5;"
  sleep 2   # session B commits during this pause
  echo "SELECT 'A, second read:', count(*) FROM customers WHERE id >= 5;"
  echo "COMMIT;"
} | client &

sleep 1
echo "INSERT INTO customers (id, email, name) VALUES (5, 'dana@example.com', 'Dana');" | client
echo "B: inserted id 5 and committed"
wait

$ bash isolation.sh pg
A, first read:|0
B: inserted id 5 and committed
A, second read:|1

$ bash isolation.sh mysql
A, first read:	0
B: inserted id 5 and committed
A, second read:	0

-- json_pg.sql — PostgreSQL: jsonb, the containment operator and a GIN index
CREATE TABLE products (
  id    integer PRIMARY KEY,
  attrs jsonb NOT NULL
);

INSERT INTO products VALUES
  (1, '{"name": "Latte", "tags": ["coffee", "milk"], "volume_ml": 350}'),
  (2, '{"name": "Espresso", "tags": ["coffee"], "volume_ml": 40}'),
  (3, '{"name": "Matcha", "tags": ["tea", "milk"]}');

CREATE INDEX products_attrs_gin ON products USING GIN (attrs jsonb_path_ops);

SELECT id, attrs ->> 'name' AS name
FROM products
WHERE attrs @> '{"tags": ["milk"]}'
ORDER BY id;

SET enable_seqscan = off;  -- three rows: force the planner to show the index
EXPLAIN (COSTS OFF) SELECT id FROM products WHERE attrs @> '{"tags": ["milk"]}';

$ docker exec kb-pg-pgvsmy psql -U postgres -f /tmp/json_pg.sql
CREATE TABLE
INSERT 0 3
CREATE INDEX
 id |  name  
----+--------
  1 | Latte
  3 | Matcha
(2 rows)

SET
                         QUERY PLAN                         
------------------------------------------------------------
 Bitmap Heap Scan on products
   Recheck Cond: (attrs @> '{"tags": ["milk"]}'::jsonb)
   ->  Bitmap Index Scan on products_attrs_gin
         Index Cond: (attrs @> '{"tags": ["milk"]}'::jsonb)
(4 rows)

-- json_mysql.sql — MySQL: JSON, MEMBER OF and a multi-valued index
CREATE TABLE products (
  id    integer PRIMARY KEY,
  attrs json NOT NULL
);

INSERT INTO products VALUES
  (1, '{"name": "Latte", "tags": ["coffee", "milk"], "volume_ml": 350}'),
  (2, '{"name": "Espresso", "tags": ["coffee"], "volume_ml": 40}'),
  (3, '{"name": "Matcha", "tags": ["tea", "milk"]}');

-- A JSON column cannot be indexed directly...
CREATE INDEX products_attrs_idx ON products (attrs);

-- ...so index the array values instead (a multi-valued index)
CREATE INDEX products_tags_idx ON products ((CAST(attrs -> '$.tags' AS CHAR(20) ARRAY)));

SELECT id, attrs ->> '$.name' AS name
FROM products
WHERE 'milk' MEMBER OF (attrs -> '$.tags')
ORDER BY id;

EXPLAIN FORMAT=TREE SELECT id FROM products WHERE 'milk' MEMBER OF (attrs -> '$.tags');

$ docker exec -i -e MYSQL_PWD=test kb-mysql-pgvsmy mysql -uroot --force --unbuffered -r kb < json_mysql.sql
ERROR 3152 (42000) at line 13: JSON column 'attrs' supports indexing only via generated columns on a specified JSON path.
id	name
1	Latte
3	Matcha
EXPLAIN
-> Filter: json'"milk"' member of (cast(json_extract(attrs,_latin1'$.tags') as char(20) array))  (cost=0.7 rows=2)
    -> Index lookup on products using products_tags_idx (cast(json_extract(attrs,_latin1'$.tags') as char(20) array)=json'"milk"')  (cost=0.7 rows=2)

-- ext_pg.sql — extensions in the official postgres:17-alpine image
SELECT count(*) AS available FROM pg_available_extensions;

CREATE EXTENSION pg_trgm;                        -- trigram similarity, ships with PostgreSQL
SELECT similarity('Almaty', 'Almata') AS score;

CREATE EXTENSION vector;                         -- pgvector is a separate project

$ docker exec kb-pg-pgvsmy psql -U postgres -f /tmp/ext_pg.sql
 available 
-----------
        59
(1 row)

CREATE EXTENSION
   score   
-----------
 0.5555556
(1 row)

psql:/tmp/ext_pg.sql:7: ERROR:  extension "vector" is not available
DETAIL:  Could not open extension control file "/usr/local/share/postgresql/extension/vector.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.