Поблажливий і суворий
MySQL виріс як швидка база для вебу і довго був поблажливим: обрізав задовгі рядки, приймав '1abc' як число 1, мовчки ігнорував CHECK. З версії 8.0 строгий режим увімкнений за замовчуванням, і більшість старих пасток закрита, але порівняння рядка з числом досі проходить з попередженням. PostgreSQL від початку суворий до типів: некоректне значення це помилка, а не попередження.
MariaDB відгалузилась від MySQL у 2009 році й довго була сумісною заміною. Тепер вони розходяться: у MariaDB є послідовності й INSERT … RETURNING, а JSON у ній це LONGTEXT з перевіркою json_valid. Laravel з версії 11 має для неї окремий драйвер mariadb.
Для PHP-розробника важать не бенчмарки, а поведінка за замовчуванням: , , як видаються ключі через , що вміє JSON-колонка і чим розширюється база. PostgreSQL додає можливості , MySQL покладається на те, що є в сервері.
Виберіть дві-три бази
| MySQL 8.0 | MariaDB 10.11 | PostgreSQL 16 | SQLite 3.45 | |
|---|---|---|---|---|
| Ізоляція за замовчуванням | REPEATABLE READ | REPEATABLE READ | READ COMMITTED | SERIALIZABLE |
| DDL у транзакції | ✗ неявний COMMIT | ✗ неявний COMMIT | ✓ | ✓ |
| JSON | ✓ бінарний JSON, багатозначний індекс | ✗ LONGTEXT з json_valid | ✓ JSONB і GIN | ✓ JSON-функції, текст |
| Повнотекстовий пошук | ✓ FULLTEXT | ✓ FULLTEXT | ✓ tsvector, без українського словника | ✓ FTS5 |
| CHECK | ✓ з 8.0.16 | ✓ | ✓ | ✓ |
| Ключі | AUTO_INCREMENT | AUTO_INCREMENT і SEQUENCE | SEQUENCE, IDENTITY | rowid |
| INSERT … RETURNING | ✗ LAST_INSERT_ID() | ✓ | ✓ | ✓ з 3.35 |
| Upsert | ON DUPLICATE KEY UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT | ON CONFLICT |
| Віконні функції й CTE | ✓ з 8.0 | ✓ | ✓ | ✓ з 3.25 |
| Розширення | ✗ плагіни сервера | ✗ плагіни сервера | ✓ CREATE EXTENSION | ✓ завантажувані модулі |
| Реплікація | binlog, Group Replication | binlog, Galera | потокова й логічна | ✗ |
| Коли брати | Звичний хостинг, велика спільнота, прості запити на великому навантаженні. Строгий режим не вимикайте. | Там, де вона вже стоїть. Не вважайте її MySQL: JSON, оптимізатор і функції відрізняються, у Laravel окремий драйвер. | Складні запити, JSONB з індексами, суворі типи, міграції, що відкочуються цілком. На ньому працює цей сайт. | Тести, CLI, вбудовані застосунки. Окрема тема про неї поруч. |
Кожне значення для MySQL 8.0.46, MariaDB 10.11.14 і PostgreSQL 16.15 перевірено запитом: ізоляція, ROLLBACK після CREATE TABLE, CHECK, CREATE SEQUENCE, INSERT … RETURNING, ROW_NUMBER(). Версії, з яких зʼявилась можливість, взято з документації.
Що ламається при переході з MySQL на PostgreSQL
Query Builder ховає більшість різниці: whereJsonContains, upsert і insertOrIgnore збирають потрібний SQL для кожної бази. Ламається те, що Builder не контролює: регістр, порядок NULL, типи в параметрах, сирий SQL і стан послідовностей.
where('name', 'like', '%kvitka%')
регістр
MySQL з колацією _ci знаходить і Kvitka Soft, і kvitka labs. PostgreSQL порівнює з урахуванням регістру й повертає лише другу. whereLike() без caseSensitive у PostgreSQL збирає ilike.
Vacancy::find('1abc')
500 замість 404
MySQL приводить '1abc' до 1 і повертає вакансію 1. PostgreSQL відповідає invalid input syntax for type bigint, і користувач бачить помилку сервера. Обмежте параметр маршруту: whereNumber('job').
orderBy('city')
інший порядок
Рядки з NULL у MySQL ідуть першими при ASC, у PostgreSQL останніми. Якщо порядок важливий, пишіть його явно: orderByRaw('city NULLS FIRST') або сортуйте за двома виразами.
upsert($rows, ['source', 'source_id'])
потрібен індекс
MySQL ігнорує другий аргумент і спрацьовує на будь-якому унікальному ключі. PostgreSQL вимагає унікальний індекс рівно на цих колонках, інакше there is no unique or exclusion constraint matching the ON CONFLICT specification.
імпорт рядків з готовими id
дубль ключа
AUTO_INCREMENT сам перескакує за найбільший id. Послідовність PostgreSQL про вставлені вручну id не знає, і наступний create() падає з duplicate key value violates unique constraint. Після імпорту потрібен setval().
DB::raw("IFNULL(`city`, '-')")
синтаксис
Зворотні лапки, IFNULL і GROUP_CONCAT у PostgreSQL дають помилку. Відповідники: подвійні лапки або без лапок, COALESCE, string_agg(name, ', ').
міграція, що впала посередині
краще
На PostgreSQL Laravel загортає міграцію в транзакцію, і вона відкочується цілком. На MySQL лишається половина змін, і міграцію доводиться доробляти руками.
whereJsonContains('tags', 'laravel')
працює
Builder збирає json_contains() для MySQL і оператор @> для PostgreSQL. Різниця в індексі: GIN у PostgreSQL проти багатозначного індексу в MySQL, нижче в EXPLAIN.
Кожен рядок перевірено на MySQL 8.0.46 і PostgreSQL 16.15, параметри передано через PDO, як це робить Laravel.
Міграція, що впала посередині
Міграція створює таблицю й додає колонку, а третій крок падає. Що лишиться в базі, вирішує транзакційний DDL. Нижче те саме вручну: BEGIN, дві DDL-команди, ROLLBACK.
Tables_in_lab (invoices) invoices Field Type Null salary_from int YES
Кожна DDL-команда спершу комітить відкриту транзакцію, тож ROLLBACK уже нічого не відкочує. Laravel на MySQL міграції в транзакцію не загортає.
Tables_in_lab (invoices) invoices
Так само, як у MySQL: неявний COMMIT.
Did not find any relation named "invoices". column_name ------------- id title
Відкотилось усе. Виняток: CREATE INDEX CONCURRENTLY і ще кілька команд у транзакції не працюють, таку міграцію виносять окремо з $withinTransaction = false.
vacancies
SQLite теж відкочує DDL. Тому тести на SQLite не покажуть, як міграція поведеться на MySQL.
Синхронізація вакансії з дошки
Синхронізація приносить вакансію з джерела: якщо пари (source, source_id) ще немає, рядок вставляється, якщо є, оновлюється. Метод upsert() у Laravel збирає потрібний синтаксис сам, але відповіді баз відрізняються, і це видно, коли треба знати, що саме сталося.
Query OK, 1 row affected -- вставка Query OK, 2 rows affected -- оновлення Query OK, 0 rows affected -- ті самі значення -- наступна нова вакансія отримала id = 4, а не 2
Кількість рядків каже, що сталося: 1 вставка, 2 оновлення, 0 нічого не змінилось. Кожна спроба вставки бере значення AUTO_INCREMENT, тож у id лишаються дірки.
id | inserted ----+---------- 1 | t -- перший запуск 1 | f -- другий запуск
RETURNING одразу повертає id, а xmax = 0 відрізняє вставку від оновлення. Конфлікт названо колонками, і для них потрібен унікальний індекс: ON CONFLICT (title) без нього дає помилку.
1|Senior PHP Developer
Синтаксис як у PostgreSQL. RETURNING SQLite знає з версії 3.35.
Що база робить з некоректним значенням
Зарплата «від 5000 до 3000», рядок замість числа, порівняння тексту з нулем. PostgreSQL на все відповідає помилкою. MySQL у строгому режимі відмовляє в записі, але в порівнянні й арифметиці досі приводить рядок до числа і лише попереджає.
ERROR 3819 (HY000): Check constraint 'vacancies_chk_1' is violated. ERROR 1366 (HY000): Incorrect integer value: 'пʼять' for column 'salary_from' at row 1 sum eq id_match 8 1 1 Warning 1292: Truncated incorrect DOUBLE value: '5 років'
CHECK працює з MySQL 8.0.16, до того його розбирали й ігнорували. Строгий режим (STRICT_TRANS_TABLES) захищає запис, а вираз '1abc' = 1 досі істинний.
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `lab`.`vacancies` id_match 1
CHECK перевіряється, але повідомлення інше, ніж у MySQL. Порівняння рядка з числом таке саме поблажливе.
ERROR: new row for relation "vacancies" violates check constraint "vacancies_check" DETAIL: Failing row contains (1, 5000, 3000). ERROR: invalid input syntax for type integer: "5 років" ERROR: invalid input syntax for type integer: "abc"
Те саме відбувається з параметром з PHP: id = '1abc' через PDO дає SQLSTATE[22P02]. Тому маршрут /jobs/1abc на PostgreSQL треба обмежити whereNumber(), інакше це 500.
Повнотекстовий пошук і українська мова
Обидві бази вміють повнотекстовий індекс, але жодна не знає української морфології. FULLTEXT у MySQL ділить текст на слова без стемінгу. У PostgreSQL є конфігурації для двох десятків мов, а української серед них немає, тож лишається simple, яка лише переводить у нижній регістр.
Через це «розробник» не знаходить «розробника». Префіксний пошук рятує частково, а повноцінний пошук українською з морфологією й помилками дає окремий пошуковий рушій.
id: 2 id: 1, 2, 3
Без префікса знайдено лише точне слово. Слова коротші за innodb_ft_min_token_size (3 символи) в індекс не потрапляють.
id: 2 id: 1, 2, 3 arabic armenian basque catalan danish dutch english … … russian serbian simple spanish swedish tamil turkish yiddish
Конфігурації ukrainian у стандартному PostgreSQL немає. russian стемить частину українських слів випадково («розробника» стає «розробник», «шукаємо» стає «шукаєм»), але покладатися на це не можна.
Пошук вакансій за тегом
Теги вакансії лежать у JSON-масиві, і запит шукає ті, де є clickhouse. whereJsonContains у Laravel перетворюється на JSON_CONTAINS у MySQL і на @> у PostgreSQL. Без індексу обидві бази перебирають усі 200 001 рядок. PostgreSQL індексує весь jsonb одним GIN-індексом, MySQL 8.0.17+ вміє багатозначний індекс на масив, оголошений через CAST … ARRAY.
Тип json у PostgreSQL зберігає текст як є, з пробілами й дубльованими ключами, і GIN-індекс на нього не створити. Беріть jsonb. MySQL зберігає JSON у бінарному вигляді завжди, а в MariaDB JSON це LONGTEXT з перевіркою json_valid, і індексують його через згенеровану колонку.
SELECT id FROM vacancies WHERE JSON_CONTAINS(tags, '"clickhouse"');| type | key | rows | Extra |
|---|---|---|---|
| ALL | NULL | 199129 | Using where |
SELECT id FROM vacancies WHERE JSON_CONTAINS(tags, '"clickhouse"');| type | key | rows | Extra |
|---|---|---|---|
| range | vacancies_tags_mvi | 1 | Using where |
SELECT id FROM vacancies WHERE 'clickhouse' MEMBER OF (tags);| type | key | rows | Extra |
|---|---|---|---|
| ref | vacancies_tags_mvi | 1 | Using where |
MySQL 8.0.46, 200 001 вакансія, одна з тегом clickhouse. Вивід знято зі справжньої бази, частину колонок прибрано.
SELECT id FROM vacancies WHERE tags @> '["clickhouse"]';Seq Scan on vacancies (cost=0.00..4562.01 rows=1 width=8) (actual time=44.494..44.496 rows=1 loops=1) Filter: (tags @> '["clickhouse"]'::jsonb) Rows Removed by Filter: 200000 Execution Time: 44.512 ms
SELECT id FROM vacancies WHERE tags @> '["clickhouse"]';Bitmap Heap Scan on vacancies (cost=37.39..41.41 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=1)
Recheck Cond: (tags @> '["clickhouse"]'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on vacancies_tags_gin (cost=0.00..37.39 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (tags @> '["clickhouse"]'::jsonb)
Execution Time: 0.051 ms PostgreSQL 16.15, 200 001 вакансія, індекс CREATE INDEX … USING gin (tags). Рядки Planning Time прибрано.
- CAST … ARRAY
- Індекс оголошують так: CREATE INDEX vacancies_tags_mvi ON vacancies ((CAST(tags AS CHAR(32) ARRAY))). Кожен елемент масиву стає окремим записом індексу.
- JSON_CONTAINS
- Саме його збирає whereJsonContains у Laravel для MySQL, і багатозначний індекс його підхоплює. Так само працюють MEMBER OF і JSON_OVERLAPS.
- type
- ALL це перебір таблиці, range і ref пошук по індексу.
- @>
- Оператор «містить». Його збирає whereJsonContains для PostgreSQL, і GIN-індекс на jsonb його підтримує.
- Bitmap Index Scan
- GIN знаходить адреси рядків, де є значення, а Bitmap Heap Scan читає лише ці сторінки таблиці.
- Recheck Cond
- Повторна перевірка умови на самих рядках. GIN може повернути зайве, тож база перевіряє ще раз.
Що змінити в коді
Підключення змінюється одним рядком у .env, а решту різниці закривають кілька звичок у коді.
Чому сайт на PostgreSQL
Окремого документа з мотивацією вибору в репозиторії немає, тож тут лише те, що видно в коді. Прод це контейнер postgres:17-alpine, а образ застосунку збирається тільки з pdo_pgsql. Схема спирається на те, чого MySQL не має в такому вигляді: колонки jsonb з GIN-індексами для міст і тегів вакансії, згенерована колонка tsvector і міграції, які відкочуються цілком. Ці частини міграції виконуються лише на pgsql, бо тести ганяються на SQLite. Сам пошук сайту при цьому працює на LOWER(...) LIKE: українського словника для повнотекстового пошуку в PostgreSQL немає, а документів сотні, а не мільйони.
src/Hiring/Infrastructure/Catalog/EloquentJobCatalog.phpФільтр за містом і тегом це whereJsonContains, тобто @> під GIN-індексом. Лічильник міст розгортає масив через jsonb_array_elements_text на PostgreSQL і через json_each на SQLite.src/Search/Infrastructure/EloquentSearchEngine.phpПошук сайту: LOWER(колонка) LIKE ? з ESCAPE для кожного слова. Коментар у класі пояснює, чому не tsvector: у стандартному PostgreSQL немає українського словника.docker-compose.ymlСервіс db на образі postgres:17-alpine, застосунок і планувальник чекають на його healthcheck через pg_isready.Dockerfileinstall-php-extensions ставить pdo_pgsql і pgsql, а pdo_mysql в образ не потрапляє.На чому спотикаються при виборі й переїзді
Вибирати базу за бенчмарком
Синтетичний тест показує тисячі запитів на секунду в обох. Сайт упирається не в це, а в індекси, N+1 і блокування, які однаково можливі в будь-якій базі.
Натомість: Вибирайте за можливостями, які знадобляться схемі (JSONB, розширення, транзакційні міграції), і за тим, що вміє підтримувати команда й хостинг.
Сирий SQL під одну базу
DB::raw з зворотними лапками, IFNULL, GROUP_CONCAT чи DATE_FORMAT працює лише в MySQL. При переїзді це помилки в найнесподіваніших місцях.
Натомість: Користуйтесь Builder там, де можна. Де не можна, розгалужуйте за getDriverName(), як EloquentJobCatalog на цьому сайті, і покривайте обидві гілки тестом.
Імпорт без setval
Дані перенесли разом з id, сайт працює, а перша ж реєстрація падає з duplicate key value violates unique constraint "users_pkey".
Натомість: Після імпорту для кожної таблиці виконайте setval(pg_get_serial_sequence(…), max(id)). Інструменти на кшталт pgloader роблять це самі.
Покладатися на REPEATABLE READ
Код, що на MySQL читав той самий знімок двічі в транзакції, на PostgreSQL з READ COMMITTED бачить між читаннями чужі коміти.
Натомість: Не розраховуйте на рівень ізоляції за замовчуванням. Де важлива узгодженість, блокуйте явно: lockForUpdate() або перевірка версії рядка.
Вважати MariaDB тим самим MySQL
Міграція з колонкою JSON і багатозначним індексом, функції JSON_TABLE чи поведінка оптимізатора відрізняються, і код, перевірений на MySQL 8, падає на MariaDB.
Натомість: У Laravel вказуйте драйвер mariadb і тестуйте на тій версії, що стоїть на проді.
- Рівень ізоляції
- Що одна транзакція бачить зі змін інших. MySQL і MariaDB за замовчуванням працюють на REPEATABLE READ, PostgreSQL на READ COMMITTED. Одна й та сама послідовність запитів може дати різний результат.
- Транзакційний DDL
- CREATE TABLE, ALTER TABLE і DROP усередині транзакції, які відкочує ROLLBACK. У PostgreSQL і SQLite так і є. MySQL і MariaDB виконують неявний COMMIT перед кожною DDL-командою.
- AUTO_INCREMENT і послідовність
- AUTO_INCREMENT це лічильник самої таблиці MySQL. Послідовність у PostgreSQL окремий обʼєкт: bigserial і GENERATED AS IDENTITY беруть з неї nextval(). Обидва не відкочуються, тож дірки в id нормальні.
- Розширення
- Модуль, який додає в PostgreSQL типи, функції й види індексів: CREATE EXTENSION pg_trgm для пошуку за підрядком, PostGIS для геоданих, pgvector для векторів.
Питання зі співбесід
PostgreSQL підтримує транзакційний DDL, і Laravel загортає міграцію в транзакцію: помилка відкочує всі кроки. MySQL перед кожною DDL-командою виконує неявний COMMIT, тож ROLLBACK відкочувати нічого. Тому на MySQL міграції роблять дрібними, щоб невдала лишала мало роботи руками.
Колація utf8mb4_unicode_ci в MySQL порівнює без урахування регістру, а LIKE у PostgreSQL враховує регістр. Виправлення: whereLike() у Laravel, який для PostgreSQL збирає ilike, або LOWER(email) LIKE з нижнім регістром значення. Для частого пошуку за email краще нормалізувати його при записі й зберігати в нижньому регістрі.
json зберігає вихідний текст: пробіли, порядок і дублікати ключів. jsonb розбирає документ у бінарний вигляд, прибирає дублікати й підтримує GIN-індекс та оператори @>, ?, ?|. Для тегів, за якими фільтрують, потрібен jsonb з GIN: тоді whereJsonContains іде індексом. json має сенс лише тоді, коли важливо зберегти документ байт у байт.
Що тести ганяються саме на PostgreSQL, а не на SQLite. Сирий SQL (зворотні лапки, IFNULL, GROUP_CONCAT, функції дат). Пошук без урахування регістру. Порядок NULL у сортуваннях. Параметри маршрутів з числовими id. upsert() з унікальним індексом рівно на колонках uniqueBy. Послідовності після імпорту даних. Код, що покладався на REPEATABLE READ. І план запитів на реальних обсягах: індекси на зовнішні ключі PostgreSQL сам не створює.