Битва титанів: що краще — PostgreSQL чи MySQL?

Сьогодні розберемося, у чому різниця між цими двома системами та що краще обрати для вашого конкретного завдання.

Що таке PostgreSQL?

PostgreSQL — потужна система керування об’єктно-реляційними базами даних з відкритим вихідним кодом (СКОРБД).

Проєкт активно розвивається вже понад 35 років та заслужив хорошу репутацію завдяки своїй надійності, відмовостійкості та високій продуктивності.

У рейтингу DB-Engines PostgreSQL посідає четверте місце.

Історія PostgreSQL

PostgreSQL розроблено на основі пакету POSTGRES, написаного в Каліфорнійському університеті в Берклі.

Реалізація POSTGRES розпочалася у 1986 році. Проєктом керував професор Майкл Стоунбрейкер (Michael Stonebraker). POSTGRES використовувалася для наукових та бізнес-проєктів, а також для навчальних програм в кількох університетах.

У 1993 році спільнота зовнішніх користувачів зросла майже вдвічі і підтримувати проєкт стало складно. Тому історія POSTGRES закінчилася на версії 4.2.

У 1994 році Ендрю Ю (Andrew Yu) та Джоллі Чен (Jolly Chen) додали в POSTGRES інтерпретатор мови SQL. Із новим ім’ям Postgres95 продукт був опублікований онлайн і став нащадком початкового POSTGRES з Берклі, але вже з відкритим кодом.

У 1996 році назва Postgres95 втратила актуальність, і продукт перейменували на PostgreSQL, щоб підкреслити зв’язок між початковою POSTGRES і новішими можливостями з підтримкою SQL.

Можливості PostgreSQL

• Підтримує більшість вимог стандарту SQLверсія 15 реалізує 170 із 179 обов'язкових можливостей SQL:2016 Core, водночас із цим надаючи багато сучасних функцій. Зокрема, можна писати складні запити, іменовані підзапити, зовнішні ключі, тригери, представлення, віконні функції, підтримку версій тощо.
• Підтримує розширення за рахунок створення типів даних, функцій та операторів, а також процедурних мов.
• Функції користувача можна писати на SQL і C. Також у дистрибутив включено чотири процедурні мови: PL/pgSQL, PL/Tcl, PL/Perl і PL/Python.

Можна використовувати інші мови зі сторонньою підтримкою, а також реалізувати свою процедурну мову.

• Таблиці в PostgreSQL представлені як об’єкти.
• PostgreSQL відповідає вимогам ACIDатомарність, узгодженість, ізольованість та довговічність.
• Зберігає дані в об’єктах, підтримує успадкування.
• Підтримує бази даних без обмеження розміру.
• Підтримує як реляційні запити та дані SQL, так й нереляційні запити та дані у форматі JSON.
• CTECommon Table Expression дає можливість використовувати іменовані підзапити у складі основного запиту, а також використовувати їх рекурсивно. Це допомагає створювати складні запити. Ця можливість відсутня в MySQL.
• Працює на багатьох платформах: Linux (усі нові дистрибутиви), Windows (XP й вище), FreeBSD, OpenBSD, NetBSD, macOS, AIX, HP/UX та Solaris. Інші лінуксоподібні системи можуть підійти, але вони не протестовані.

Характеристики й переваги PostgreSQL

Детальні характеристики PostgreSQL можна переглянути в матриці характеристик. Нижче коротко розглянемо основні характеристики.

У системі доступні такі типи даних:

Примітиви	Integer, Numeric, String, Boolean
Структуровані	Date/Time, Array, Range / Multirange, UUID
Документи	JSON/JSONB, XML, Key-value (Hstore)
Геометричні	Point, Line, Circle, Polygon
Користувацькі	складені, користувацькі типи

Цілісність даних у системі можна реалізувати, використовуючи:

• UNIQUE, NOT NULL;
• первинні ключі;
• зовнішні ключі;
• обмеження виключення;
• явні блокування, рекомендаційні блокування.

Також доступні функції, що реалізують паралелізм та підвищують продуктивність:

• індексування: Б-дерево, за кількома стовпцями, з використанням виразів, часткове;
• розширене індексування: GiST, SP-Gist, KNN Gist, GIN, BRIN, індекси, що охоплюють, фільтр Блума;
• складний планувальник/оптимізатор запитів, сканування лише за індексами, статистика з кількох стовпців;
• транзакції, у тому числі вкладені;
• керування паралельним доступом за допомогою багатоверсійності (Multi-Version concurrency Control, MVCC);
• паралелізація запитів на читання та створення індексів з використанням Б-дерева;
• сегментування таблиць;
• всі рівні ізоляції транзакцій, визначені у стандарті SQL, у тому числі Serializable;
• компіляція виразів як реального часу (Just-in-time, JIT).

Для надійності роботи системи, включаючи аварійне відновлення, у систему вбудовано:

• журнал передзапису (Write-ahead Logging, WAL);
• реплікація: асинхронна, синхронна, логічна;
• відновлення на момент часу (Point-in-time-recovery, PITR), активний режим очікування;
• табличний простір.

Також з метою безпеки в PostgreSQL передббачено аутентифікацію за допомогою GSSAPI, SSPI, LDAP, SCRAM-SHA-256 або сертифіката, надійну систему контролю доступу, безпеку на рівні стовпців та рядків і багатофакторну аутентифікацію з використанням сертифікатів та додаткового методу.

Розширюваність системи реалізована за допомогою:

• Збережених функцій та процедур.
• Процедурних мов: PL/pgSQL, Perl, Python і Tcl. Розширення дають можливість використовувати інші мови, зокрема Java, JavaScript (V8), R, Lua та Rust.
• Виразів шляху SQL/JSON.
• Обгортки сторонніх даних: підключення до інших баз або потоків даних із використанням стандартного інтерфейсу SQL.
• Налаштовуваного інтерфейсу зберігання для таблиць.
• Багатьом інших розширенням, які надають додаткову функціональність, зокрема PostGIS.

У PostgreSQL також зручно працювати з різними даними завдяки підтримці міжнародних наборів символів, зіставленню без урахування регістру й діакритичних знаків та повнотекстового пошуку.

Навіщо використовувати PostgreSQL?

Підсумуємо: PostgreSQL надає безліч можливостей, які допомагають розробникам створювати програми, адміністраторам — захищати цілісність даних та створювати середовища, стійкі до збоїв, а вам — керувати даними, незалежно від обсягу набору даних.

СКБД PostgreSQL відкрита для розширення. Ви можете визначити власні типи даних, створити функції користувача й навіть писати код різними мовами програмування — і вам не доведеться перекомпілювати свою базу даних.

Можливості PostgreSQL дозволяють обслуговувати великі та складні бази даних.

Якщо ви розраховуєте на розширення свого проєкту, якщо прогнозується великий обсяг даних, використовуйте PostgreSQL.

Недоліки використання PostgreSQL

Насамкінець розповімо, які проблеми можуть виникнути при використанні системи:

• встановлення та налаштування можуть виявитися складними для новачків;
• детальна документація доступна лише англійською;
• порівняно низька швидкість читання;
• багато програм з відкритим вихідним кодом використовують MySQL, але не підтримують PostgreSQL;
• для поновлення потрібно подвоїти обсяг сховища;
• пакетне завантаження може уповільнювати роботу;
• для підвищення швидкості потрібні додаткові витрати;
• виникають проблеми з аварійним відновленням.

Перейдемо до суперника PostgreSQL — MySQL.

Що таке MySQL?

MySQL — одна з найпопулярніших у світі систем керування реляційними базами даних (СКРБД) з відкритим вихідним кодом.

До неї включено безліч функцій, розроблених за понад 25 років. У рейтингу DB-Engines вона посідає друге місце, поступаючись лише Oracle Database.

Історія MySQL

Авторам MySQL була необхідна СКБД для керування таблицями даних. Для цього вони вже розробили низькорівневі програми, що дозволяли працювати з таблицями за допомогою СКБД mSQL. Але в ході тестування з’ясувалося, що mSQL не настільки швидка та гнучка, наскільки потрібно.

В результаті було розроблено новий SQL-інтерфейс для бази даних майже з таким самим API, як і в mSQL. Цей API мав спростити портування коду, написаного для mSQL, для використання з MySQL.

MySQL названо на честь дочки співзасновника MySQL AB Мікаела «Монті» Віденіуса (Michael Widenius) — Мю.

У травні 1996 було випущено версію MySQL 1.0 для обмеженого кола користувачів, а публічний реліз версії 3.33.1 відбувся в жовтні 1996 року. До нього було включено лише бінарний дистрибутив для Solaris. Місяцем пізніше було випущено код та двійковий реліз для Linux.

Протягом наступних років створювалися версії MySQL для дедалі більшої кількості операційних систем, зростала кількість можливостей цієї СКРБД та її популярність.

У 2008 році компанія Sun Microsystems придбала MySQL AB. Через два роки, у 2010 році компанію Sun Microsystems придбала Oracle, яка володіє MySQL досі.

Можливості MySQL

• Подвійне ліцензування. MySQL можна використовувати як продукт з відкритим вихідним кодом згідно з умовами ліцензії GNU General Public License або придбати у Oracle стандартну комерційну ліцензію.
• Протестовано з широким спектром різних компіляторів.
• Підтримується більшістю CMS та веб-фреймворків (навіть без додаткових модулів).
• Дуже швидко виконує операції читання.
• Досить просто освоюється, встановлюється та налаштовується.
• Проста в обслуговуванні. Ви можете обслуговувати її без адміністратора.

Характеристики й переваги MySQL

MySQL написана на C та C++ і працює на багатьох платформах, включаючи Oracle Linux/Red Hat/CentOS, Oracle Solaris, Ubuntu, SUSE, Debian та інші Linux-дистрибутиви, Microsoft Windows Server, Microsoft Windows, macOS. Для портативності система налаштовується за допомогою CMake.

В останню версію проєкту включено:

• багаторівневу структуру сервера з незалежними модулями;
• повну багатопоточність з використанням потоків ядра, щоб використовувати кілька процесорів, якщо вони доступні;
• транзакційні та нетранзакційні механізми зберігання;
• дуже швидкі механізми роботи з дисковими накопичувачами (Б-дерева) зі стисненням індексу;
• дуже швидку систему розподілу пам’яті з урахуванням потоків;
• дуже швидке підключення завдяки оптимізованому об’єднанню зі вкладеним циклом;
• хеш-таблиці в пам’яті, які використовуються як тимчасові таблиці;
• сервер у вигляді окремої програми для використання в мережному середовищі клієнт-сервер.

На додаток, MySQL полегшує додавання інших механізмів зберігання. Це корисно, якщо ви хочете надати SQL інтерфейс для внутрішньої бази даних.

У роботі з системою вам доступна безліч типів даних, включаючи цілі зі знаком/без знака довжиною 1, 2, 3, 4 та 8 байт, FLOAT, DOUBLE, CHAR, VARCHAR, BINARY, VARBINARY, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM та просторові типи OpenGIS.

Крім того, ви можете працювати з рядками фіксованої та змінної довжини.

Список операторів та функцій, які підтримуються MySQL:

• оператори та функції у списку SELECT та речення WHERE;
• GROUP BY та ORDER BY, а також групові функції COUNT(), AVG() та інші;
• LEFT OUTER JOIN та RIGHT OUTER JOIN зі стандартним синтаксисом SQL та ODBC;
• псевдоніми для таблиць та стовпців, як цього вимагає стандартний SQL;
• DELETE, INSERT, REPLACE та UPDATE для повернення кількості рядків, які було змінено, або для повернення кількості рядків, що збігаються, за допомогою встановлення прапора при підключенні до сервера;
• специфічні для MySQL оператори SHOW, які отримують інформацію про бази даних, механізми зберігання, таблиці та індекси;
• INFORMATION_SCHEMA, реалізована за стандартом SQL;
• оператор EXPLAIN, який показує, як оптимізатор інтерпретує запит.

Зверніть увагу! Імена MySQL функцій не залежать від імен таблиць або стовпців.

Наприклад, ABS є допустимим іменем стовпця. Єдине обмеження полягає в тому, що для виклику функції не допускаються пробіли між ім’ям функції та символом (, який слідує за ним.

Також MySQL дозволяє посилатися на таблиці різних баз даних в одному операторі.

Загалом у MySQL дуже гнучка та безпечна система прав доступу та паролів, яка забезпечує підтвердження на основі хоста. Захист паролів забезпечено повним шифруванням трафіку паролів під час підключення до сервера.

Система підтримує великі бази даних, але має обмеження кількості індексів на таблицю — до 64. Також є такі обмеження:

• кожен індекс може складатися з 1–16 стовпців або частин стовпців;
• максимальна ширина індексу для таблиць InnoDB становить 767 або 3072 байти;
• максимальна ширина індексу для MyISAM таблиць становить 1000 байт.

До MySQL можна підключитися з використанням кількох протоколів:

• За допомогою сокетів TCP/IP на будь-якій платформі.
• У Windows можна підключитися за допомогою іменованих каналів, якщо сервер запущено з увімкненою змінною named_pipe. Також можливі підключення з пам’яттю, що використовується спільно, якщо включена системну змінну shared_memory. Таке підключення виконується за допомогою опції --protocol=memory.
• У системах Unix клієнти можуть підключатись за допомогою файлів сокетів доменів.

Клієнтські програми MySQL можна писати багатьма мовами. Клієнтська бібліотека, написана на C, доступна для клієнтів, написаних C, C++ або будь-якою іншою мовою, що забезпечує прив’язки до C.

Також система має API для C, C++, Eiffel, Java, Perl, PHP, Python, Ruby та Tcl, а інтерфейс Connector/ODBC (MyODBC) забезпечує підтримку MySQL для клієнтів, які використовують з’єднання ODBC (Open Database Connectivity).

Інтерфейс Connector/J забезпечує підтримку MySQL для клієнтських програм Java, які використовують з’єднання JDBC. Клієнти можна запускати на Windows та Unix. MySQL Connector/NET дозволяє розробникам легко створювати програми .NET з підключенням до MySQL.

MySQL має кілька клієнтських та службових програм. Це програми для командного рядка, зокрема mysqldump та mysqladmin, і програми з графічним інтерфейсом, такі як MySQL Workbench.

Для чого використовувати MySQL?

Багато з найбільших компаній у всьому світі, зокрема Facebook, Twitter, Booking.com та Verizon, користуються MySQL. Ця СКБД забезпечує значну економію часу та грошей для їхніх сайтів.

Більшість веброзробників надають перевагу MySQL завдяки простоті бази даних та навчання роботі з нею.

Підсумуємо: MySQL добре підходить для малого бізнесу або організацій без досвідченої та великої команди фахівців із роботи з даними. Вона відрізняється невисокою вартістю, простотою налаштування та обслуговування. Також її підтримує велика спільнота.

Недоліки використання MySQL

Головний недолік системи — неефективність роботи з великими базами даних. Інші мінуси досить специфічні, наприклад:

• вимоги ACID підтримуються лише при використанні з InnoDB та NDB Cluster Storage;
• складно налагоджувати збережені процедури через відсутність хороших інструментів розробки та налагодження, які є в інших СКБД;
• версії нижче 5.0 не підтримують ROLE, COMMIT та процедури, що зберігаються;
• слабкі збережені процедури;
• функція GROUP BY не завжди працює так, як очікується.

До того ж, MySQL не підтримує SQL у повному обсязі. Тому розробникам буває складно навчитися використовувати синтаксис MySQL. Наприклад, ця СКБД не підтримує обмеження CHECK.

Різниця між MySQL та PostgreSQL

Розглянемо основні відмінності між цими двома системами.

Відповідність стандартам

PostgreSQL підтримує більшість вимог стандарту SQL.

MySQL Server розходиться зі стандартами ANSI SQL та ODBC SQL у деяких випадках.

Наприклад, права доступу до таблиці MySQL не скасовуються автоматично після її видалення. Потрібно явно викликати REVOKE. Функція CAST() не підтримує перетворення на REAL та BIGINT.

Адміністрування

Адмініструвати PostgreSQL набагато складніше, ніж MySQL, тому що у PostgreSQL набагато більше можливостей.

MySQL підійде для невеликих проєктів і у випадку, якщо ви не можете собі дозволити адміністратора, адже для керування складними таблицями в PostgreSQL знадобиться кваліфікований персонал.

Продуктивність

Продуктивність MySQL нижча, ніж у PostgreSQL. До того ж, часто доводиться робити вручну те, що має бути реалізовано в БД. Це призводить до недоцільних витрат зусиль та часу.

Документація

Документація PostgreSQL набагато повніша, ніж документація MySQL. Остання часто складна для розуміння, і доводиться шукати пояснення в сторонніх джерелах.

Що краще: MySQL чи PostgreSQL?

Відповідь це питання залежить від характеристик проєкту, термінів його виконання та досвіду команди.

Якщо колектив багато років працює з MySQL та потрібно терміново запускати проєкт, то логічно не витрачатиме час і використовувати MySQL. Якщо ж час дозволяє, то є сенс випробувати PostgreSQL.

Для проєктів із малим навантаженням немає різниці, яку з цих СКБД обрати. При цьому в PostgreSQL реалізовано більше можливостей для перевірки, а під час роботи з MySQL перевірки доводиться реалізувати самостійно.

З огляду на те, що обидві СКБД відомі як найшвидші, MySQL швидше справляється з командами, призначеними тільки для читання, за рахунок паралелізму, а PostgreSQL краще працює з операціями читання-запису, великими наборами даних та складними запитами.

Якщо у вас невеликий проєкт, який не масштабуватиметься, і є досвід роботи з MySQL, вибирайте MySQL. А якщо у вас великий проєкт зі складними даними та передбачається, що він розширюватиметься, то скористайтеся PostgreSQL.