PostgreSQL чи MySQL. Як обрати оптимальну базу даних для вашого проєкту

Привіт! Я Костянтин Гобеляк, Node.js Team Lead у MOJAM. У своїй роботі я часто взаємодіяв з різними базами даних, і сьогодні поділюсь аналізом PostgreSQL та MySQL для полегшення вибору між ними. Обидві системи мають свої сильні сторони, проте є ситуації, коли одна з них буде ефективнішою за іншу. Розглянемо основні відмінності PostgreSQL та MySQL, їхні переваги та недоліки.

З чого все починалось

PostgreSQL почав свою історію в 1986 році як проєкт POSTGRES під керівництвом професора Майкла Стоунбрейкера з метою створити передову систему управління базами даних. Ключовим моментом стало впровадження підтримки мови SQL у 1994 році. Також тоді змінили назву на Postgres95, а в 1996 її перейменували на PostgreSQL.

За роки ця БД отримала безліч важливих функцій, як-то багатоверсійний контроль паралелізму (MVCC) і механізм логування змін WAL, що дає змогу відновлювати базу даних у випадку збою. Ці особливості зробили PostgreSQL вибором номер один для багатьох компаній, що потребують надійної та масштабованої системи.

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

MySQL з’явилася на ринку в 1995 році. Розроблена шведською компанією MySQL AB, спочатку вона була створена для особистого використання на основі mSQL і низькорівневого інтерфейсу ISAM, який розробники вважали занадто повільним і негнучким. І завдяки сумісності API з системою mSQL вони могли перейти на MySQL.

Приблизно в 1999-2000 роках компанія співпрацювала з Berkeley DB і це дало MySQL підтримку транзакцій. Трохи пізніше з’явилася реплікація, ISAM був вдосконалений і перейменований на MyISAM, інтегрували двигун InnoDB (розробник — Хейккі Туурі). MySQL активно використовується в різних сценаріях — від невеликих вебсайтів до складних корпоративних рішень. Та дає можливість будувати надійну інфраструктуру для хмарних застосунків. Після придбання MySQL компанією Oracle у 2010 році, частина розробників перейшла до проєкту MariaDB, який став відкритою альтернативою.

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

Популярність

На початку 1990-х дуже популярною була легка система керування БД — mSQL. Але після піку популярності в розвитку динамічних вебзастосунків 1994–1997 років, її почала витісняти багатофункціональна MySQL. У 1999 році MySQL випередила mSQL за популярністю і надовго закріпилася серед лідерів ринку.

Своєю чергою, PostgreSQL поступово набирала популярність і лише в останні роки змогла наблизитися до популярності MySQL. Попри те, що з’явилася майже на 10 років раніше. Нижче наведено таблицю популярності двох БД за останні 10 років (рейтинг DB-Engines):

Там добре видно, що популярність MySQL трохи впала, тоді як у PostgreSQL виросла більш ніж утричі.

У 2023 році, згідно з опитуваннями Stack Overflow, PostgreSQL випередив MySQL. А у 2024 роцi вiдрив ще трохи збiльшився. Професійні розробники з більшою ймовірністю будуть використовувати PostgreSQL, тоді як новачки — навпаки MySQL. Детальніше тут: опитування 2023 та опитування 2024.

Також на наступному графіку Google Trends видно зниження популярності MySQL, хоча за цим графіком (посилання працює для залогованих користувачiв в Google) популярність PostgreSQL також трохи просіла.

Такі гіганти, як Instagram, Apple, Spotify, Reddit, Twitch використовують PostgreSQL.

• Instagram — спочатку використовували MySQL, але перейшли на PostgreSQL для критичних частин системи.
• Apple — аналогічно, PostgreSQL замінив MySQL як вбудовану БД для macOS, починаючи з OS X Lion.

Разом з тим Facebook, Github, Netflix, YouTube та Uber використовують MySQL як основну БД. Uber свого часу замінив PostgreSQL на MySQL (навіть є стаття про це), а зараз використовує комбінацію MySQL і MyRocks — двигун зберігання, заснований на RocksDB.

Функціонал

Далі ми розглянемо ключовий функціонал обох БД:

Загальні можливості

• Відповідність ACID присутня в обох БД, але в PostgreSQL вона повна, тоді як MySQL з двигуном MyISAM не підтримує ACID.
• Обидві БД підтримують мультиверсійність (MySQL з InnoDB), але працюють по-різному: PostgreSQL використовує MVCC, а MySQL має власну реалізацію.
• RBAC підтримується обома БД, але PostgreSQL підтримує додатковий рівень безпеки — Row Level Security.
• Реплікація є в обох БД: стандартна фізична реплікація PostgreSQL використовує WAL, тоді як у MySQL — логічна (binlog). Однак PostgreSQL також підтримує логічну реплікацію через режим PUB/SUB, додану нещодавно.
• Є підтримка резервного копіювання та відновлення.
• Підтримується партиціонування таблиць (типи Range, List, Hash).
• Ієрархія об’єктів майже однакова: instance, database, schema (тільки в PostgreSQL), table, column.
• Плагіни доступні в обох БД, але PostgreSQL більш розширюваний шляхом плагінів (наприклад, PostGIS).

SQL

• Є підтримка стандарту SQL в обох БД.
• Обидві БД підтримують набір стандартних SQL-типів, але в PostgreSQL можна створювати власні типи даних.
• Збережені процедури підтримуються обома БД, але PostgreSQL також дозволяє писати їх на інших мовах, крім SQL.
• Представлення (views), тригери (triggers), CTE (в MySQL з версії 8.0), а також віконні функції є в обох БД.

DDL

• Є підтримка всіх основних DDL-операцій: CREATE/ALTER/DROP TABLE/DATABASE/VIEW, а також TRUNCATE TABLE. У MySQL також є можливість RENAME TABLE, у PostgreSQL це робиться через ALTER TABLE table_name RENAME TO new_table_name.
• Також в обох БД є можливість миттєвого додавання нової колонки: в MySQL з версії 8.0 — ALGORITHM=INSTANT (з версії 8.4 цей алгоритм став стандартним), а в PostgreSQL з версії 11 з’явилася можливість проставляти DEFAULT значення на льоту. До цього воно оновлювало дані в кожному рядку.

ALTER TABLE table_name ADD COLUMN column_name bigint, ALGORITHM=INSTANT;

Statements

• Підтримуються такі стандартні запити: SELECT/INSERT/UPDATE/DELETE/UNION/WITH, а також підзапити й різноманітні специфічні операції (наприклад, REPLACE у MySQL і MERGE у PostgreSQL).
• Є повнотекстовий пошук, в обох БД є інвертований індекс (MySQL — FULLTEXT, PostgreSQL — GIN).
• Є можливість повернення змінених даних: у PostgreSQL це працює на всіх операціях (INSERT, UPDATE, DELETE), тоді як у MySQL це реалізовано частково (недоступно на UPDATE), але в MariaDB це також працює для REPLACE.

INSERT INTO table_name (id, column_name)
VALUES (1, 'value1')
RETURNING *; -- also can return specific columns

• Обидві СУБД підтримують вставку даних із заміною, але в MySQL є два види такої вставки: REPLACE INTO (якщо спрацьовує обмеження, видаляється запис, викликається ON DELETE CASCADE, а потім вставляється новий) і ON DUPLICATE KEY UPDATE (немає видалень, тому працює швидше). У PostgreSQL є аналогічний механізм ON CONFLICT DO UPDATE, а також є можливість не змінювати нічого у випадку конфлікту — DO NOTHING.

MySQL:

# MySQL
INSERT INTO table_name (id, new_column)
VALUES (1, 'value1')
ON DUPLICATE KEY UPDATE new_column = 'value1';

REPLACE INTO table_name (id, new_column)
SET id = 1, new_column = 'value1';

PostgreSQL:

-- PostgreSQL
INSERT INTO table_name (id, column_name)
VALUES (1, 'value1')
ON CONFLICT DO UPDATE SET column_name = 'value1';

INSERT INTO table_name (id, column_name)
VALUES (1, 'value1')
ON CONFLICT DO NOTHING;

• PostgreSQL має потужну підтримку регулярних виразів через оператори ~ (пошук відповідності) і ~* (пошук без урахування регістру). MySQL також підтримує регулярні вирази, але через функцію REGEXP і має спрощений синтаксис для роботи з регулярками.
• EXPLAIN підтримується обома СУБД, але візуальне відображення результатів різниться (це детально розглянемо в іншій статті).

Тепер розглянемо відмінності у функціоналі.

Загальне

• MySQL — це реляційна база даних, а PostgreSQL — об’єктно-реляційна.
• У PostgreSQL є тільки один двигун зберігання, тоді як у MySQL їх 15, зокрема, найбільш популярні InnoDB та MyISAM.
• Модель з’єднань різна: PostgreSQL використовує процес на одне з’єднання, а MySQL — потік (тред). При цьому підході в PostgreSQL краща ізоляція, оскільки доступ до невірної комірки пам’яті викликає збій тільки одного процесу, а не всього сервера БД. Однак цей підхід споживає більше ресурсів, тому рекомендується використовувати пулери з’єднань (наприклад, pgCat або pgBouncer).
• У баз даних різні ліцензії: MySQL — GNU GPL, PostgreSQL — власна ліцензія в стилі MIT (Open Source).

SQL

• Views є в обох СУБД, але PostgreSQL пропонує MATERIALIZED VIEWS (це такі ж представлення, але попередньо обчислені, що корисно для оптимізації складних запитів).

CREATE MATERIALIZED VIEW complex_view AS
SELECT *
FROM table_name
WHERE column_name = 'value'
WITH DATA;

• MySQL підтримує тригери BEFORE і AFTER для INSERT/UPDATE/DELETE, а PostgreSQL підтримує ще й тригер INSTEAD OF для views.

MySQL:

# MySQL
CREATE TRIGGER total_amount BEFORE INSERT ON table_name
FOR EACH ROW SET @sum = @sum + NEW.amount;

CREATE TRIGGER total_amount AFTER DELETE ON table_name
FOR EACH ROW SET @sum = @sum - OLD.amount;

PostgreSQL:

-- PostgreSQL
CREATE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION fn_trigger;

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

DDL

• MySQL підтримує команди SHOW statements, за допомогою яких можна переглянути системну інформацію (список БД, таблиць, колонок тощо), тоді як у PostgreSQL є псевдоніми для клієнта \l, \dt, \d+ table_name. Але якщо потрібно зробити це за допомогою SQL, то запити будуть трохи складнішими.

-- SHOW DATABASES;
SELECT datname FROM pg_database;

-- SHOW TABLES;
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- SHOW COLUMNS FROM table_name;
SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name';

• В операціях DROP TABLE і TRUNCATE TABLE у PostgreSQL є опція CASCADE, на відміну від MySQL.

TRUNCATE TABLE table_name CASCADE;
DROP TABLE table_name CASCADE;

• Є нюанс у роботі TRUNCATE TABLE у PostgreSQL: якщо виконати цю операцію в межах іншої транзакції, то при відкаті транзакції операція також відкотиться. У MySQL такої можливості немає.
• MySQL дозволяє при додаванні нової колонки вказати, після якої колонки її потрібно створити, у PostgreSQL такої можливості немає — нові колонки завжди додаються в кінець таблиці.

ALTER TABLE table_name ADD COLUMN new_column varchar(255) AFTER id;

• Є різниця в автоматичній генерації ID: у MySQL це AUTO_INCREMENT, який зберігається як атрибут таблиці, а в PostgreSQL це псевдотип SERIAL, що є спеціальним об’єктом БД, який фактично є набором команд DDL під капотом.

CREATE TABLE table_name(
id SERIAL
);

-- ||
-- ||
-- ||
-- \/

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;

• У MySQL операції DDL не є транзакційними (на відміну від PostgreSQL), тобто якщо відбувається помилка під час такої операції, вона не зможе відкотитися.

Statements

• У MySQL є невидимі колонки, це колонки, які невидимі під час запиту SELECT *, але їх можна отримати, якщо безпосередньо вказати їх у вибірці.

CREATE TABLE table_name (
id INT PRIMARY KEY,
new_column VARCHAR(255) INVISIBLE
);

ALTER TABLE table_name CHANGE COLUMN new_column new_column VARCHAR(255) INVISIBLE;
ALTER TABLE table_name MODIFY COLUMN new_column VARCHAR(255) INVISIBLE;
ALTER TABLE table_name ALTER COLUMN new_column SET VISIBLE;

• У PostgreSQL можна виконувати операції SELECT/UPDATE/INSERT/DELETE всередині CTE (Common Table Expressions) та в запитах після CTE. У MySQL всередині CTE можна лише SELECT, а в запитах після CTE — SELECT/UPDATE/DELETE.
• PostgreSQL підтримує стандартний SQL-підхід до обробки NULL. Для порівняння значень NULL використовується IS NULL або IS NOT NULL. У MySQL нестандартна поведінка під час використання оператора = із NULL. Наприклад, NULL = NULL повертає NULL, а не TRUE, що може призвести до неочікуваних результатів при некоректній обробці значень NULL. Тому при роботі з NULL завжди потрібно використовувати IS NULL або IS NOT NULL для коректного порівняння.
• У MySQL можна використовувати спрощений синтаксис для LIMIT із вказівкою зміщення та кількості рядків, чого немає в PostgreSQL.

MySQL:

# MySQL
SELECT * FROM table_name LIMIT 10, 5;

PostgreSQL:

-- PostgreSQL
SELECT * FROM table_name LIMIT 10 OFFSET 5;

• PostgreSQL використовує оператор || для об’єднання рядків, тоді як у MySQL для цього використовується функція CONCAT().
• PostgreSQL повністю підтримує оператор FULL OUTER JOIN, який повертає рядки, що є в одній або обох таблицях. MySQL не підтримує FULL OUTER JOIN напряму. Для цього необхідно використовувати комбінацію LEFT JOIN і RIGHT JOIN з об’єднанням результатів за допомогою UNION.

MySQL:

# MySQL
SELECT * FROM table_name t LEFT JOIN second_table_name s ON t.id = s.table_name_id
UNION
SELECT * FROM table_name t RIGHT JOIN second_table_name s ON t.id = s.table_name_id;

PostgreSQL:

-- PostgreSQL
SELECT * FROM table_name t FULL OUTER JOIN second_table_name s ON t.id = s.table_name_id;

Далі розглянемо основні типи даних.

У обох СУБД є такі типи даних:

• числові;
• дата і час;
• рядкові;
• просторові;
• JSON.

Числові:

• У MySQL є signed/unsigned integers різної довжини (1, 2, 3, 4 і 8 байтів), тоді як у PostgreSQL є лише signed integers довжиною 2, 4 і 8 байтів.
• Для зберігання точних числових значень можна використовувати тип NUMERIC. Цей тип реалізовано як DECIMAL. Однак операції з цим типом даних важчі для СУБД, ніж з integer.
• Числа з плавучою точкою представлені типами FLOAT і DOUBLE у MySQL, REAL і DOUBLE PRECISION у PostgreSQL.
• Є тип BIT, який потрібен для зберігання бітових значень.
• PostgreSQL має тип money із фіксованою дробовою точністю (налаштовується через опцію lc_monetary).
• Також у PostgreSQL є спеціальний тип для зберігання автоінкрементних колонок — SMALLSERIAL, SERIAL і BIGSERIAL.
• Обидві СУБД підтримують тип boolean, але у PostgreSQL він нативний, а у MySQL це псевдонім для tinyint(1) під капотом. У MySQL можна використовувати вирази TRUE і FALSE, які автоматично приведуться до 1 і 0 відповідно.

Дата і час:

• Обидві СУБД підтримують тип TIMESTAMP, але їхні діапазони різні: у MySQL це від 1970-01-01 00:00:01.000000 UTC до 2038-01-19 03:14:07.499999, а в PostgreSQL — від 4713 р. до н.е. до 294276 р. н.е. Також у MySQL цей тип вказує часовий пояс UTC, а в PostgreSQL є додатковий тип timestamptz, який повертає дату й час у вказаній зоні (timezone), але під капотом усе одно зберігає значення в UTC.
• Типи DATE і TIME присутні в обох СУБД (у PostgreSQL також є timetz).
• У MySQL є тип DATETIME з діапазоном від 1000-01-01 00:00:00.000000 до 9999-12-31 23:59:59.499999 і повертається у вигляді YYYY-MM-DD hh:mm.
• Також у MySQL є тип YEAR з діапазоном від 1901 до 2155 і 0000.
• У PostgreSQL є власний тип INTERVAL, який дозволяє зберігати й обробляти періоди в роках, місяцях, днях, годинах, хвилинах і секундах. Цей тип корисний для арифметики над датами й часом. У MySQL також є INTERVAL, але він є оператором.

Нижче наведено вибірку даних за останні 30 днів, за винятком останніх 2 — запит однаковий для обох СУБД.

SELECT *
FROM table_name
WHERE created_at BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '2 days';

Рядкові:

• Обидві СУБД мають рядки фіксованої довжини (CHAR) і змінної довжини з лімітом (VARCHAR).
• Для зберігання великого тексту використовується тип TEXT, але у MySQL він розділений на чотири типи: TINYTEXT, TEXT, MEDIUMTEXT і LONGTEXT.
• Для зберігання бінарних даних у MySQL використовується тип BLOB, представлений чотирма типами: TINYBLOB, BLOB, MEDIUMBLOB і LONGBLOB (також є для дрібних даних BINARY і VARBINARY, подібні до CHAR і VARCHAR, тільки в бінарному виконанні), а в PostgreSQL — BYTEA. Виведення бінарних рядків відбувається за допомогою шістнадцяткової нотації в MySQL, а в PostgreSQL за замовчуванням за допомогою послідовності символів ASCII, але є можливість також використовувати шістнадцяткову систему.
• Також в обох СУБД є тип перерахування ENUM, але використовується він трохи по-різному: у MySQL його можна вказувати одразу під час додавання колонки, а в PostgreSQL потрібно окремо створювати власний тип (про це поговоримо трохи пізніше).

MySQL:

# MySQL
ALTER TABLE table_name ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active';

PostgreSQL:

-- PostgreSQL
CREATE TYPE status_type AS ENUM ('active', 'inactive');
ALTER TABLE table_name ADD COLUMN status status_type DEFAULT 'active';

• У PostgreSQL неможливо видалити значення з уже створеного типу enum і змінити сортування. А в MySQL це можливо, але для видалення потрібно змінити всі видаляємі значення в колонках таблиці на інше доступне.
• У MySQL також є тип SET, який схожий на ENUM, але має трохи розширену функціональність. Якщо взяти за аналогію HTML-перемикачі, то ENUM — це radio button, а SET — checkbox.

Просторові:

Коли йдеться про просторові запити, обидві СУБД мають свої слабкі та сильні сторони.

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

JSON:

PostgreSQL підтримує два типи:

• JSON — зберігає дані як рядок, перевіряє лише коректність синтаксису JSON під час запису, але не перевіряє структуру або типи даних.
• JSONB — це двійковий формат зберігання JSON, де дані парсяться й індексуються, що робить його більш продуктивним для операцій пошуку. Для інтенсивних операцій читання й модифікації JSON краще використовувати тип JSONB, оскільки він швидший для пошуку й дозволяє створювати індекси. Тип JSON більше підходить для випадків, коли потрібно просто зберігати дані у форматі JSON без частих операцій пошуку.

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

• PostgreSQL має повну підтримку операцій із JSON: можливість витягнення даних, перетворення, фільтрації та навіть модифікації JSON-структур.
• У MySQL підтримується доступ до значень за ключами та створення вкладених запитів для фільтрації даних усередині JSON.

Однією з головних переваг PostgreSQL є ефективна індексація полів JSONB, що дозволяє створювати індекси за конкретними ключами або значеннями всередині JSON-структур за допомогою індексів GIN або B-tree.

У MySQL індексація всередині полів JSON безпосередньо не підтримується. Однак можна створювати віртуальні колонки на основі даних JSON та індексувати їх. Наприклад, можна витягти значення з JSON і створити індекс на віртуальній колонці.

CREATE TABLE table_name (
id INT AUTO_INCREMENT PRIMARY KEY,
new_column JSON,
col_data VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(new_column, '$.col_data'))) VIRTUAL
);

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

Нижче порівняння основних операцій:

Додаткові типи:

• У PostgreSQL також є тип UUID (Universally Unique Identifier), який зберігає унікальні ідентифікатори за стандартом RFC 4122. Це робить роботу з UUID більш зручною та ефективною, оскільки PostgreSQL підтримує цей тип даних на рівні ядра. У MySQL є вбудована функція UUID(), яка генерує унікальні ідентифікатори у форматі UUID. Однак MySQL не надає спеціального типу даних для зберігання UUID і зазвичай розробники використовують типи CHAR(36) або BINARY(16) для їх зберігання.
• Крім того, PostgreSQL має вбудовані типи даних для роботи з мережевими адресами (Network Address Types), які призначені для зберігання та обробки IP-адрес, масок підмереж та інших мережевих даних. Ці типи даних дозволяють виконувати ефективні операції порівняння, фільтрації та інших маніпуляцій з IP-адресами та масками мереж. Основні типи даних такі: INET — для зберігання IPv4 та IPv6 адрес, CIDR — для зберігання блоків мереж IPv4 і IPv6, MACADDR — для зберігання MAC-адрес, MACADDR — для зберігання 8-байтових MAC-адрес (використовується для розширених форматів MAC-адрес).

Користувацькі типи даних:

І нарешті, PostgreSQL має потужний функціонал для створення користувацьких типів даних, що дозволяє розробникам визначати власні структури даних, розширюючи можливості бази даних під конкретні завдання. Цей функціонал підтримує кілька видів користувацьких типів, включаючи складені типи, перерахування (ENUM), масиви, псевдоніми типів, доменні типи, а також складніші, такі як типізація на основі C-функцій (для інтеграції зовнішніх типів).

• Складений тип (Composite Types) даних — це тип, який являє собою набір полів (атрибутів), кожен із яких може мати свій тип. Це схоже на створення структур або об’єктів в інших мовах програмування.

CREATE TYPE full_name AS (
first_name VARCHAR(50),
last_name VARCHAR(50)
);

ALTER TABLE table_name ADD COLUMN name full_name;

INSERT INTO table_name (name) VALUES (ROW('John', 'Doe'));

SELECT (name).first_name, (name).last_name FROM table_name;

• Типи ENUM дозволяють створювати набір передвизначених значень, які являють собою рядкові дані. Це корисно для створення обмежених списків варіантів.
• Доменний тип (Domains) — це спосіб створення нового типу на основі наявного з додатковими обмеженнями та правилами. Це корисно для застосування бізнес-правил.

CREATE DOMAIN positive_integer AS INTEGER
CHECK (VALUE > 0);

• PostgreSQL дозволяє створювати масиви з будь-яких наявних типів даних, включаючи користувацькі типи, такі як ENUM або складені типи.

ALTER TABLE table_name ADD COLUMN grades INTEGER[];
INSERT INTO table_name (grades) VALUES ('{4,3,5}');
SELECT grades[1] FROM table_name;

• Псевдоніми типів (Type Aliases) дозволяють створювати зручніші назви для наявних типів даних. Це корисно, якщо потрібно використовувати довгі або незручні типи даних і ви хочете спростити код.

CREATE TYPE price AS NUMERIC(12, 2);

• PostgreSQL дозволяє створювати базові типи даних за допомогою зовнішніх бібліотек на мовах програмування — таких, як C. Це складніший варіант, який вимагає створення нових типів даних на рівні ядра PostgreSQL. Користувацькі базові типи можуть містити складні структури даних і поведінку, які не можна реалізувати за допомогою стандартних можливостей SQL.

Основні етапи створення користувацького базового типу:

1. Написання функції мовою C для реалізації нового типу.
2. Реєстрація нового типу в PostgreSQL за допомогою команди CREATE TYPE.
3. Налаштування операторів і функцій для роботи з типом (наприклад, як дані будуть серіалізуватися, десеріалізуватися, індексуватися тощо).

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

Висновки

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

Якщо підсумувати:

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

Вибір між цими двома СУБД має ґрунтуватися на вимогах проєкту та складності запитів.

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

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті