Чим postgresql краще mysql. Системи керування базами даних. Створення нового типу

SQL,

Розробка веб-сайтів

Переклад

Сьогодні давайте поговоримо про переваги Postgres перед іншими системами з відкритим кодом. Цю тему ми обов'язково розкриємо докладніше на PG Day"16 Russia, до якої залишилося всього два місяці.

Можливо, ви питаєте себе: «Чому PostgreSQL?» Адже є й інші варіанти реляційних баз даних із відкритим вихідним кодом(в рамках цієї статті ми розглядали MySQL, MariaDB і Firebird), то що ж Постгрес може запропонувати такого, чого немає у них? У слогані PostgreSQL заявляється, що це «Просунута база даних з відкритим вихідним кодом у світі». Ми наведемо кілька причин, чому Постгрес робить такі заяви.

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

Модель даних

PostgreSQL не просто реляційна, а об'єктно-реляційна СУБД. Це дає йому деякі переваги над іншими базами даних SQL з відкритим вихідним кодом, такими як MySQL, MariaDB і Firebird.

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

Структури та типи даних

Існує великий список типів даних, які підтримує постгрес. Крім числових, з плаваючою точкою, текстових, булевих та інших очікуваних типів даних (а також безлічі їх варіацій), PostgreSQL може похвалитися підтримкою uuid, грошового, перерахованого, геометричного, бінарного типів, мережевих адрес, бітових рядків, текстового пошуку, xml, json, масивів, композитних типів та діапазонів, а також деяких внутрішніх типівдля ідентифікації об'єктів та розташування логів. Заради справедливості варто сказати, що MySQL, MariaDB і Firebird теж мають деякі з цих типів даних, але тільки Постгрес підтримує їх усі.

Давайте розглянемо докладніше деякі з них:

Мережеві адреси

PostgreSQL забезпечує зберігання різних типівмережевих адрес. Тип даних CIDR (безкласова маршрутизація інтернет домену, Classless Internet Domain Routing) дотримується угоди для мережевих адрес IPv4 і IPv6. Ось кілька прикладів:

192.168.100.128/25
10.1.2.3/32
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
::ffff:1.2.3.0/128

Також для зберігання мережевих адрес доступний тип даних INET, що використовується для IPv4 і IPv6 хостів, де підмережі є необов'язковими. Тип даних MACADDR може використовуватися для зберігання MAC-адрес для ідентифікації обладнання, таких як 08-00-2b-01-02-03.

MySQL і MariaDB також мають INET функції для конвертації мережевих адрес, але вони не надають типи даних для внутрішнього зберіганнямережевих адрес. Firebird теж не має типів для зберігання мережевих адрес.

Багатовимірні масиви

Оскільки Постгрес - це об'єктно-реляційна база даних, масиви значень можуть зберігатися більшість існуючих типівданих. Зробити це можна шляхом додавання квадратних дужок до специфікації типу даних для стовпця або за допомогою виразу ARRAY. Розмір масиву може бути заданий, але це необов'язково. Давайте розглянемо меню святкового пікніка для демонстрації використання масивів:

Створюємо таблицю, у якої значення є масивами CREATE TABLE holiday_picnic (holiday varchar(50) - рядкове значення sandwich text, - масив side text багатовимірний масив dessert text ARRAY - масив beverage text ARRAY - масив з 4-х елементів); -- вставляємо значення масивів в таблицю INSERT INTO "), ("chips", "crackers") )", "("fruit cocktail", "berry pie", "ice cream")", "("soda", "juice", "beer", "water ")");
MySQL, MariaDB і Firebird так не вміють. Щоб зберігати такі масиви значень у традиційних реляційних базахданих, доведеться використовувати обхідний шлях та створювати окрему таблицю з рядками для кожного із значень масиву.

Геометричні дані

Геодані швидко стають основною вимогою для багатьох програм. PostgreSQL вже давно підтримує безліч геометричних типів даних, таких як точки, лінії, кола та багатокутники. Один з цих типів - PATH, він складається з безлічі послідовно розташованих точок і може бути відкритим (початкова та кінцева точки не пов'язані) або закритим (початкова та кінцева точки пов'язані). Давайте розглянемо як приклад туристичну стежку. В даному випадку туристична стежка - це петля, тому початкова та кінцева точки пов'язані, і, отже, мій шлях є закритим. Круглі дужки навколо набору координат свідчать про закритий шлях, а квадратні - на відкритий.

Створюємо таблицю для зберігання стежок CREATE TABLE trails (trail_name varchar(250), trail_path path); - вставляємо стежку в таблицю, - для якої маршрут визначається координатами у форматі широта-довгота. 37.1735,-122.2236), (37.175416666667,-122.223), (37.1758,-122.22378333333), (37.179466666667,-16122.2) 22675), (37.180783333333,-122.22466666667), (37.176116666667,-122.2222), (37.1753 ,-122.22293333333), (37.173116666667,-122.22281666667)));
Розширення PostGIS для PostgreSQL доповнює існуючі властивості геометричних даних допоміжними просторовими типами, функціями, операторами та індексами. Воно забезпечує підтримку розташування та підтримує як растрові, так і векторні дані. Воно також забезпечує сумісність з безліччю сторонніх геопросторових інструментів (захищених авторським правом та відкритим вихідним кодом) для відображення, відтворення та роботи з даними.

Зауважте, що в MySQL 5.7.8 і MariaDB, починаючи з версії 5.3.3, були додані розширення типів даних для підтримки стандарту географічної інформації OpenGIS. Ця версія MySQLта наступні версії MariaDBпропонують зберігання типів даних, аналогічне штатним геоданим постгресу. Тим не менш, у MySQL і MariaDB значення даних спочатку повинні бути сконвертовані в геометричний формат простими командамиперед тим, як буде вставлено в таблицю. Firebird зараз не підтримує геометричні типи даних.

Підтримка JSON

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

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

MySQL 5.7.8 і MariaDB 10.0.1 додали підтримку вбудованих об'єктів JSON. Але хоча існує безліч функцій і операторів для JSON, які тепер доступні в цих базах даних, вони не індексуються так, як JSONB в PostgreSQL. Firebird поки що не приєднався до тренду та підтримує об'єкти JSON лише у вигляді тексту.

Створення нового типу

Якщо раптом так станеться, що великого списку типів даних постгресу вам виявиться недостатньо, ви можете використовувати команду CREATE TYPE, щоб створити нові типи даних, такі як складовий, перерахований, діапазон і базовий. Розглянемо приклад створення та надсилання запитів нового складового типу:

Створюємо новий складовий тип"wine" CREATE TYPE wine AS (wine_vineyard varchar(50), wine_type varchar(50), wine_year int); - Створюємо таблицю, яка використовує складовий тип "wine" CREATE TABLE pairings (menu_entree varchar(50), wine_pairing wine); -- вставляємо дані в таблицю за допомогою виразу ROW INSERT INTO pairings VALUES ("Lobster Tail", ROW ("Stag" "s Leap", "Chardonnay", 2012)), ("Elk Medallions", ROW ("Rombauer", "Cabernet Sauvignon", 2012)); /* вибірка з таблиці з використанням імені колонки (використовуйте дужки, що відокремлюються точкою від імені поля у складовому типі) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = "Elk Medallions";
Оскільки вони не є об'єктно-реляційними, MySQL, MariaDB та Firebird не надають такої потужної функціональності.

Розміри даних

PostgreSQL може обробляти багато даних. Поточні опубліковані обмеження наведені нижче:

У Compose [прим. пер.: організація, в якій працює автор оригінальної статті], ми автоматично масштабуємо вашу інсталяцію, щоб вам не доводилося хвилюватися про зростання кількості даних. Але, як відомо будь-якому адміністратору баз даних, варто з побоюванням ставитися до надто великих та необмежених можливостей. Ми радимо керуватися здоровим глуздомпри створенні таблиць та додаванні індексів.

Для порівняння, MySQL і MariaDB сумно відомі обмеженням розміру рядків 65 535 байт. Firebird також пропонує всього лише 64Кб як максимальний розмір рядка. Зазвичай обсяг даних обмежується максимальним розміромфайлів операційної системи. Оскільки PostgreSQL вміє зберігати табличні дані у безлічі файлів меншого розміру, може обійти це обмеження. Але варто зазначити, що занадто велика кількістьфайлів може негативно позначитися на продуктивності. MySQL та MariaDB підтримують Велика кількістьстовпців у таблиці (до 4,096 залежно від типу даних) і більші індивідуальні розміри таблиці, ніж PostgreSQL, але необхідність перевищити існуючі обмеженняПостгресу виникає лише у вкрай поодиноких випадках.

Цілісність даних

Постгрес прагне відповідати стандарту ANSI-SQL:2008, відповідає вимогам ACID (атомарність, узгодженість, ізольованість та надійність) та відомий своєю посилальною та транзакційною цілісністю. Первинні ключі, що обмежують та каскадні зовнішні ключі, унікальні обмеження, обмеження NOT NULL, перевірочні обмеження та інші функції забезпечення цілісності даних дають впевненість, що лише коректні дані буде збережено.

MySQL і MariaDB більше працюють на те, щоб відповідати стандарту SQL з двигунами таблиць InnoDB/XtraDB. Тепер вони пропонують опцію STRICT з використанням режимів SQL, яка встановлює перевірки коректності даних, що використовуються. Незважаючи на це, залежно від того, який режим ви використовуєте, недостовірні та навіть урізані без вашого відома дані можуть бути вставлені або створені під час оновлення. Жодна з цих баз даних зараз не підтримує обмеження CHECK. Крім того, у них існує безліч особливостей щодо обмежень цілісності посилання за зовнішніми ключами. На додаток до вищесказаного, цілісність даних може суттєво постраждати в залежності від обраного двигуна зберігання. MySQL (і fork MariaDB) не роблять секрету з того, що проміняли цілісність та відповідність стандартам на швидкість та ефективність.

Підбиваючи підсумки

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

Якщо вам здається, що PostgreSQL не відповідає вашим потребам, або ви віддаєте перевагу "стріляти від стегна", тоді вам варто звернути увагу на NoSQL бази даних, які ми пропонуємо в Compose, або подумати про інші SQL бази даних, які ми згадували. Кожна з них має свої переваги. Compose твердо впевнений, що дуже важливо вибрати правильну базу даних для конкретного завдання…іноді це означає, що потрібно вибрати кілька баз даних!

Бажаєте більше Постгресу?

Бази даних – це логічно змодельовані сховища будь-яких типів даних. Кожна база даних, яка не є безсхемною, слідує моделі, яка задає певну структуру обробки даних. СУБД – це додатки (або бібліотеки), що керують базами даних різних форм, розмірів та типів.

Щоб краще розібратися в СУБД, ознайомтеся з .

Реляційні системи управління базами даних

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

СУБД такого типу використовують структури (таблиці) для зберігання та роботи з даними. Кожен стовпець містить свій тип інформації. Кожен запис у базі даних, що має унікальний ключ, передається в рядок таблиці, і її атрибути відображаються в стовпцях таблиці.

Відносини та типи даних

Відносини можна визначити як математичні множини, що містять набори атрибутів, що відображають інформацію, що зберігається.

Кожен елемент, який формує запис, повинен задовольняти певному типуданих (ціле число, дата і т.д.). Різні РСУБД використовують різні типи даних, які завжди взаємозамінні.

Такі обмеження звичайні для реляційних баз даних. Фактично вони і формують суть відносин.

SQLite

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

Коли програма використовує SQLite, їх зв'язок здійснюється за допомогою функціональних і прямих викликів файлів, що містять дані (наприклад, баз даних SQL ite), а не якогось інтерфейсу, що підвищує швидкість та продуктивність операцій.

Типи даних, що підтримуються

NULL: NULL-значення.

INTEGER:ціле зі знаком, що зберігається в 1, 2, 3, 4, 6 або 8 байтах.

REAL:число з плаваючою комою, що зберігається у 8-байтовому форматі IEEE.

TEXT:текстовий рядок з кодуванням UTF-8, UTF-16BE або UTF-16LE.

BLOB:тип даних, що зберігається точно в такому вигляді, в якому і був отриманий.

Note:для отримання більш детальної інформаціїознайомтеся з документацією.

Переваги

Файлова:вся база даних зберігається у одному файлі, що полегшує переміщення.

Стандартизована: SQLite використовує SQL; деякі функції опущені (RIGHT OUTER JOIN або FOR EACH STATEMENT), однак є й деякі нові.

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

Недоліки

Відсутність управління користувача:просунуті БД надають користувачам можливість керувати зв'язками в таблицях відповідно до привілеїв, але SQLite такої функції немає.

Неможливість додаткового налаштування:Знов-таки, SQLite не можна зробити більш продуктивною, поколупавшись в налаштуваннях - так вже вона влаштована.

Коли варто використовувати SQLite

Вбудовані програми:всі портовані не призначені для масштабування програми - наприклад, локальні однокористувацькі програми, мобільні програми або ігри.

Система доступу до дискової пам'яті:У більшості випадків програми, які часто виконують прямі операції читання/запису на диск, можна перевести на SQLite для підвищення продуктивності.

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

Коли не варто використовувати SQLite

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

Програми, що записують великі обсяги даних:Одним із обмежень SQLite є операції запису. Ця РСУБД допускає одноразове виконання лише однієї операції запису.

MySQL

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

Типи даних, що підтримуються

TINYINT:дуже мале ціле.

SMALLINT:невелике ціле.

MEDIUMINT:ціле середнього розміру.

INT або INTEGER:ціле нормального розміру.

BIGINT:велике ціле.

FLOAT:знакове число з плаваючою комою одинарної точності.

DOUBLE, DOUBLE PRECISION, REAL:знакове число з плаваючою комою подвійної точності.

DECIMAL, NUMERIC:знакове число з плаваючою комою.

DATE:Дата.

DATETIME:комбінація дати та часу.

TIMESTAMP:позначка часу.

TIME:час.

YEAR:рік у форматі YY чи YYYY.

CHAR:рядок фіксованого розміру, що доповнюється праворуч пробілами до максимальної довжини.

VARCHAR:рядок змінної довжини.

TINYBLOB, TINYTEXT: BLOB- або TEXT-стовпець довжиною максимум 255 (2^8 - 1) символів.

BLOB, TEXT: BLOB- або TEXT-стовпець довжиною максимум 65535 (2^16 - 1) символів.

MEDIUMBLOB, MEDIUMTEXT: BLOB- або TEXT-стовпець довжиною максимум 16777215 (2^24 - 1) символів.

LONGBLOB, LONGTEXT: BLOB- або TEXT-стовпець довжиною максимум 4294967295 (2^32 - 1) символів.

ENUM:Перерахування.

SET:множини.

Переваги

Простота: MySQL легко встановлюється. Існує багато сторонніх інструментів, включаючи візуальні, що полегшують початок роботи з БД.

Багато функцій: MySQL підтримує більшу частину функціоналу SQL.

Безпека: MySQL має багато функцій безпеки.

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

Швидкість:зневага деяких стандартів дозволяє MySQL працювати продуктивніше, місцями зрізаючи на поворотах.

Недоліки

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

Питання надійності:деякі операції реалізовані менш надійно, ніж у інших РСУБД.

Застій у розробці:хоча MySQL і є open-source продуктом, робота над нею сильно загальмована. Тим не менш, існує кілька баз даних, повністю заснованих на MySQL (наприклад, MariaDB). До речі, докладніше про спорідненість MariaDB та MySQL можна з нашого із творцем обох РСУБД – Джеймсом Боттомлі.

Коли варто використовувати MySQL

Розподілені операції:коли вам потрібен більший функціонал, ніж може надати SQLite, варто використовувати MySQL.

Висока безпека:функції безпеки MySQL надають надійний захистдоступу та використання даних.

Веб-сайти та програми:Більшість веб-ресурсів цілком може працювати з MySQL, незважаючи на обмеження. Цей інструмент дуже гнучкий і простий у користуванні, що тільки на руку в тривалій перспективі.

Кастомні рішення:якщо ви працюєте над дуже специфічним продуктом, MySQL підлаштовується під ваші потреби завдяки широкому спектру налаштувань та режимів роботи.

Коли не варто використовувати MySQL

SQL-сумісність:оскільки MySQL не намагається повністю реалізувати стандарти SQL, вона є повністю сумісною з SQL. Через це можуть виникнути проблеми під час інтеграції з іншими РСУБД.

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

Нестача функцій:в залежності від вибору двигуна MySQL може не вистачати деяких функцій.

PostgreSQL

PostgreSQL - це просунута РСУБД, що орієнтується в першу чергу на повну відповідність стандартам і розширюваність. PostgreSQL, або Postgres, намагається повністю відповідати SQL стандартам ANSI/ISO.

PostgreSQL відрізняється від інших РСУБД тим, що має об'єктно-орієнтований функціонал, у тому числі повною підтримкоюконцепту ACID (Atomicity, Consistency, Isolation, Durability).

Будучи заснованим на потужній технології Postgres, відмінно справляється з одночасною обробкою кількох завдань. Підтримка конкурентності реалізована за допомогою MVCC (Multiversion Concurrency Control), що також забезпечує сумісність з ACID.

Хоча ця РСУБД не така популярна, як MySQL, існує багато сторонніх інструментів і бібліотек для полегшення роботи з PostgreSQL.

Типи даних, що підтримуються

bigint:знакове 8-байтне ціле.

bigserial: 8-бітове ціле, що автоматично інкрементується.

bit [(n)]:бітовий рядок фіксованої довжини.

bit varying [(n)]:бітовий рядок змінної довжини.

boolean:булівська величина.

box:прямокутник на площині.

bytea:бінарні дані.

character varying [(n)]:рядок символів фіксованої довжини.

character [(n)]:

cidr:мережна адреса IPv4 або IPv6.

circle:коло на площині.

date:календарної дати.

double precision:число з плаваючою комою подвійної точності.

inet:адреса хоста IPv4 чи IPv6.

integer:знакове 4-байтне ціле.

interval [(p)]:проміжок часу.

line:нескінченна пряма на площині.

lseg:відрізок на площині.

macaddr: MAC-адреса.

money:Фінансова величина.

path:геометричні шлях на площині.

point:геометричні точки на площині.

polygon:багатокутник на площині.

real:число з плаваючою комою одинарної точності.

smallint:знакове 2-байтне ціле.

serial: 4-бітове ціле, що автоматично інкрементується.

text:рядок символів змінної довжини.

time [(p)] :час доби (без часового поясу).

time [(p)] with time zone:час доби (з часовим поясом).

timestamp [(p)] :дата і час (без часового поясу).

timestamp [(p)] with time zone:дата та час (з часовим поясом).

tsquery:запит текстового пошуку.

tsvector:Текстовий пошук.

txid_snapshot:снэпшот ID користувача транзакції.

uuid:унікальний ідентифікатор.

xml: XML-дані.

Переваги

Повна SQL-сумісність.

Спільнота: PostgreSQL підтримується досвідченою спільнотою 24/7.

Підтримка сторонніми організаціями:незважаючи на дуже сучасні функції, PostgreSQL використовується в багатьох інструментах, пов'язаних з РСУБД.

Розширюваність: PostgreSQL можна програмно розширити за рахунок процедур, що зберігаються.

Об'єктно-орієнтованість: PostgreSQL – не лише реляційна, а й об'єктно-орієнтована СУБД.

Недоліки

Продуктивність:У простих операціяхЧитання PostgreSQL може поступатися своїм суперникам.

Популярність:через свою складність інструмент не дуже популярний.

Хостинг:через перерахованих вище факторів проблематично знайти відповідного провайдера.

Коли варто використовувати PostgreSQL

Цілісність даних:якщо пріоритет стоїть на надійність та цілісність даних, PostgreSQL – найкращий вибір.

Складні процедури:якщо ваша БД повинна виконувати складні процедури, варто вибрати PostgreSQL через її розширюваність.

Інтеграція:якщо в майбутньому вам доведеться переміщати всю базу на інше рішення, найменше проблем виникне з PostgreSQL.

Коли не варто використовувати PostgreSQL

Швидкість:якщо все, що потрібно – це швидкі операції читання, не варто використовувати PostgreSQL.

Прості ситуації:якщо вам не потрібна підвищена надійність, підтримка ACID і таке інше, використання PostgreSQL - це стрілянина з гармати по мухам.

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

MySQL існує досить давно і зарекомендувала себе як відмінне рішення, Postgresql прийшла на ринок приблизно в той же час, але надає досить багато цікавих функційі можливостей, завдяки чому стрімко набирає популярності. У цій статті ми спробуємо виконати порівняння MySQL vs Postgresql, порівняємо основні відмінності цих систем, з'ясуємо як вони працюють і спробуємо зрозуміти яка система буде кращою для вашого проекту.

Системи управління базами даних

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

Реляційні системи управління базами даних дозволяють розміщувати дані в таблицях, зв'язуючи рядки з різних таблиць і, таким чином, пов'язуючи різні, логічно об'єднані дані. Перед тим, як ви зможете зберігати дані, необхідно створити таблиці певного розміру та вказати тип даних кожного стовпця. Стовпи представляють поля даних, а самі дані розміщені у рядках. Обидві системи управління базами даних і MySQL vs Postgresql належать до реляційних. Далі ми розглянемо докладніше, ніж відрізняються обидві програми. А тепер перейдемо до детальнішого розгляду.

коротка історія

MySQL

Розробка MySQL розпочалася ще у 90-х роках. Перший внутрішній випуск бази даних відбувся 1995 року. За цей час розробкою програми займалося кілька компаній. Розробку було розпочато шведською компанією MySQL AB, яку придбала Sun Microsystems, яка, власне, перейшла у власність Oracle. На даний момент, починаючи з 2010 року, розробкою займається Oracle.

Postgresql

Розробка Postrgresql розпочалася далекого 1986 року у стінах Каліфорнійського університету Берклі. Розробка тривала майже вісім років, потім проект розділився на дві частини комерційної бази даних IIlustra і повністю вільний проект Postrgesql, який розробляється ентузіастами.

Збереження даних

MySQL

MySQL - це реляційна база даних, для зберігання даних у таблицях використовуються різні двигуни, але робота з двигунами захована в самій системі. На синтаксис запитів та їх виконання двигун не впливає. Підтримуються такі основні двигуни MyISAM, InnoDB, MEMORY, Berkeley DB. Вони відрізняються між собою способом записування даних на диск, а також методами зчитування.

Postgresql

Postgresql являє собою об'єктно-реляційну базу даних, яка працює тільки на одному движку - storage engine. Усі таблиці представлені як об'єктів, можуть успадковуватися, проте дії з таблицями виконуються з допомогою об'єктивно орієнтованих функцій. Як і в MySQL, всі дані зберігаються на диску, у спеціально відсортованих файлах, але структура цих файлів і записів у них дуже відрізняється.

Стандарт SQL

Незалежно від системи управління базами даних, що використовується, SQL - це стандартизована мова виконання запитів. І він підтримується всіма рішеннями, навіть MySQL чи Postgresql. Стандарт SQL був розроблений у 1986 році і за цей час вже вийшло декілька версій.

MySQL

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

Postgresql

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

Можливості обробки

З попереднього пункту випливають інші відмінності postgresql від mysql, це можливості обробки даних і обмеження. Природно, відповідність новим стандартам дає нові можливості.

MySQL

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

Postgresql

Postgresql підтримує використання курсорів для переміщення за отриманими даними. Ви отримуєте лише покажчик, вся відповідь зберігається у пам'яті сервера баз даних. Цей вказівник можна зберігати між сеансами. Тут підтримується побудова індексів відразу кількох стовпців таблиці. Крім того, індекси можуть бути різних типів, крім hash та b-tree доступні GiST та SP-GiST для роботи з містами, GIN для пошуку за текстом, BRIN та Bloom.

Postgresql підтримує Регулярні виразиу запитах, рекурсивних запитівта успадкування таблиць. Але тут є кілька обмежень, наприклад, ви можете додати нове поле лише до кінця таблиці.

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

Бази даних повинні бути оптимізовані для оточення, в якому ви будете працювати. Історично так склалося, що MySQL орієнтувалася на максимальну продуктивність, а Postgresql розроблялася як база даних із великою кількістю налаштувань та максимально відповідну стандарту. Але згодом Postgresql отримав багато поліпшень та оптимізації.

MySQL

У більшості випадків для організації роботи з базою даних MySQL використовується таблиця InnoDB, ця таблиця являє собою B-дерево з індексами. Індекси дозволяють дуже швидко отримати дані з диска, і для цього потрібно менше дискових операцій. Але сканування дерева потребує знаходження двох індексів, а це вже повільно. Все це означає що MySQL буде швидше за Postgresql тільки при використанні первинного ключа.

Postgresql

Вся заголовна інформація таблиць Postgresqlзнаходиться в оперативної пам'яті. Ви не можете створити таблицю, яка не буде в пам'яті. Записи таблиці сортуються за індексом, тому ви можете їх дуже швидко витягти. Для більшої зручності можна застосовувати кілька індексів до однієї таблиці.

Загалом PostgreSQL працює швидше, за винятком використання первинних ключів. Давайте розглянемо кілька тестів із різними операціями:

Типи даних

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

MySQL

MySQL підтримує такі типи даних:

TINYINT: дуже маленьке ціле.;
SMALLINT:маленьке ціле;
MEDIUMINT:ціле середнього розміру;
INT:ціле нормального розміру;
BIGINT:велике ціле;
FLOAT:знакове число з плаваючою комою одинарної точності;
DOUBLE, DOUBLE PRECISION, REAL:знакове число з плаваючою комою подвійної точності
DECIMAL, NUMERIC:знакове число з плаваючою комою;
DATE:дата;
DATETIME:комбінація дати та часу;
TIMESTAMP:позначка часу;
TIME:час;
YEAR:рік у форматі YY чи YYYY;
CHAR: рядок фіксованого розміру, що доповнюється праворуч пробілами до максимальної довжини;
VARCHAR:рядок змінної довжини;
TINYBLOB, TINYTEXT:двійкові або текстові дані максимальною довжиною 255 символів;
BLOB, TEXT: двійкові або текстові дані максимальною довжиною 65535 символів;
MEDIUMBLOB, MEDIUMTEXT:текст чи двійкові дані;
LONGBLOB, LONGTEXT:текст або двійкові максимальні дані довжиною 4294967295 символів;
ENUM:перерахування;
SET:множини.

Postgresql

Типи полів, що підтримуються, в Postgresql досить сильно відрізняються, але дозволяють записувати точно ті ж дані:

bigint:знакове 8-байтове ціле;
bigserial: 8-байтове ціле, що автоматично збільшується;
bit:двійковий рядок фіксованої довжини;
bit varying:двійковий рядок змінної довжини;
boolean:прапор;
box:прямокутник на площині;
byte: бінарні дані;
character varying:рядок символів фіксованої довжини;
character:
cidr:мережеву адресу IPv4 або IPv6;
circle:коло на площині;
date: дата в календарі;
double precision:число з плаваючою комою подвійної точності;
inet:адреса інтернету IPv4 або IPv6;
integer: знакове 4-байтне ціле число;
interval:проміжок часу;
line:нескінченна пряма на площині;
lseg:відрізок на площині;
macaddr: MAC-адреса;
money:грошова величина;
path:геометричний шлях на площині;
point:геометрична точка на площині;
polygon:багатокутник на площині;
real:число з плаваючою точкою одинарної точності;
smallint:двобайтове ціле число;
serial:автоматично збільшується чотирибітне ціле число;
text:рядок символів змінної довжини;
time:час доби;
timestamp:дата та час;
tsquery: запит текстового пошуку;
tsvector:документ текстового пошуку;
uuid: унікальний ідентифікатор;
xml: XML-дані.

Як бачите, типів даних у Postgresql більше і вони різноманітніші, є свої типи полів для певних видівданих, яких немає MySQL. Відмінність MySQLвід Postgresql очевидно.

Розробка

Обидва проекти мають відкритий вихідний код, але розвиваються по-різному. Розвиток MySQL подобається далеко не всім. І це порівняння mysql і postgresql дає багато відмінностей.

MySQL

База даних MySQLрозробляється компанією Oracleі ходять чутки, що компанія має намір гальмувати розвиток двигуна. Було створено багато форків проекту, у тому числі форк MariaDB від розробника оригінальної MySQL. Але все ж таки розвиток залишається повільним.

Postgresql

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

Переклад

Сьогодні поговоримо про переваги Postgres перед іншими системами з відкритим кодом. Цю тему ми обов'язково розкриємо докладніше на PG Day"16 Russia, до якої залишилося всього два місяці.

Можливо, ви питаєте себе: «Чому PostgreSQL?» Адже є й інші варіанти реляційних баз даних з відкритим вихідним кодом (у рамках цієї статті ми розглядали MySQL, MariaDB та Firebird), то що ж Постгрес може запропонувати такого, чого немає у них? У слогані PostgreSQL заявляється, що це «Просунута база даних з відкритим вихідним кодом у світі». Ми наведемо кілька причин, чому Постгрес робить такі заяви.

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

Модель даних

Структури та типи даних

Існує великий список типів даних, які підтримує постгрес. Крім числових, з плаваючою точкою, текстових, булевих та інших очікуваних типів даних (а також безлічі їх варіацій), PostgreSQL може похвалитися підтримкою uuid, грошового, перерахованого, геометричного, бінарного типів, мережевих адрес, бітових рядків, текстового пошуку, xml, , масивів, композитних типів та діапазонів, а також деяких внутрішніх типів для ідентифікації об'єктів та розташування логів. Заради справедливості варто сказати, що MySQL, MariaDB і Firebird теж мають деякі з цих типів даних, але тільки Постгрес підтримує їх усі.

Давайте розглянемо докладніше деякі з них:

Мережеві адреси

PostgreSQL забезпечує зберігання різних типів мережевих адрес. Тип даних CIDR (безкласова маршрутизація інтернет домену, Classless Internet Domain Routing) дотримується угоди для мережевих адрес IPv4 і IPv6. Ось кілька прикладів:

192.168.100.128/25
10.1.2.3/32
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
::ffff:1.2.3.0/128

MySQL і MariaDB також мають INET функції для конвертації мережевих адрес, але вони не надають типи даних для внутрішнього зберігання мережевих адрес. Firebird теж не має типів для зберігання мережевих адрес.

Багатовимірні масиви

Оскільки Постгрес - це об'єктно-реляційна база даних, масиви значень можуть зберігатися більшість існуючих типів даних. Зробити це можна шляхом додавання квадратних дужок до специфікації типу даних для стовпця або за допомогою виразу ARRAY. Розмір масиву може бути заданий, але це необов'язково. Давайте розглянемо меню святкового пікніка для демонстрації використання масивів:

Створюємо таблицю, у якої значення є масивами CREATE TABLE х елементів); -- вставляємо значення масивів в таблицю INSERT INTO "), ("chips", "crackers") )", "("fruit cocktail", "berry pie", "ice cream")", "("soda", "juice", "beer", "water ")");
MySQL, MariaDB і Firebird так не вміють. Щоб зберігати такі масиви значень у традиційних реляційних базах даних, доведеться використовувати обхідний шлях та створювати окрему таблицю з рядками для кожного з значень масиву.

Геометричні дані

Зауважте, що у MySQL 5.7.8 та MariaDB, починаючи з версії 5.3.3, були додані розширення типів даних для підтримки стандарту географічної інформації OpenGIS. Ця версія MySQL та наступні версії MariaDB пропонують зберігання типів даних, аналогічне штатним геоданим постгресу. Тим не менш, у MySQL і MariaDB значення даних спочатку повинні бути сконвертовані в геометричний формат простими командами, перш ніж будуть вставлені в таблицю. Firebird зараз не підтримує геометричні типи даних.

Підтримка JSON

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

Створення нового типу

Створюємо новий складовий тип "wine" CREATE TYPE wine AS (wine_vineyard varchar(50), wine_type varchar(50), wine_year int); - Створюємо таблицю, яка використовує складовий тип "wine" CREATE TABLE pairings (menu_entree varchar(50), wine_pairing wine); -- вставляємо дані в таблицю за допомогою виразу ROW INSERT INTO pairings VALUES ("Lobster Tail", ROW ("Stag" "s Leap", "Chardonnay", 2012)), ("Elk Medallions", ROW ("Rombauer", "Cabernet Sauvignon", 2012)); /* вибірка з таблиці з використанням імені колонки (використовуйте дужки, що відокремлюються точкою від імені поля у складовому типі) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = "Elk Medallions";
Оскільки вони не є об'єктно-реляційними, MySQL, MariaDB та Firebird не надають такої потужної функціональності.

Розміри даних

PostgreSQL може обробляти багато даних. Поточні опубліковані обмеження наведені нижче:

У Compose [прим. пер.: організація, в якій працює автор оригінальної статті], ми автоматично масштабуємо вашу інсталяцію, щоб вам не доводилося хвилюватися про зростання кількості даних. Але, як відомо будь-якому адміністратору баз даних, варто з побоюванням ставитися до надто великих та необмежених можливостей. Ми радимо керуватися здоровим глуздом під час створення таблиць та додавання індексів.

Для порівняння, MySQL і MariaDB сумно відомі обмеженням розміру рядків 65 535 байт. Firebird також пропонує всього лише 64Кб як максимальний розмір рядка. Зазвичай обсяг даних обмежується максимальним розміром файлів операційної системи. Оскільки PostgreSQL вміє зберігати табличні дані у безлічі менших файлів, він може обійти це обмеження. Але варто відзначити, що дуже велика кількість файлів може негативно позначитися на продуктивності. MySQL і MariaDB підтримують більшу кількість стовпців у таблиці (до 4,096 залежно від типу даних) і більші індивідуальні розміри таблиці, ніж PostgreSQL, але необхідність перевищити існуючі обмеження постгресу виникає лише в дуже рідкісних випадках.

Цілісність даних

Підбиваючи підсумки

Якщо вам здається, що PostgreSQL не відповідає вашим потребам, або ви віддаєте перевагу "стріляти від стегна", тоді вам варто звернути увагу на NoSQL бази даних, які ми пропонуємо в Compose, або подумати про інші SQL бази даних, які ми згадували. Кожна з них має свої переваги. Compose твердо впевнений, що дуже важливо вибрати правильну базу даних для конкретного завдання… іноді це означає, що потрібно вибрати декілька баз даних!

Бажаєте більше Постгресу?

Мене часто запитують, «Чому ви надаєте перевагу, PostgreSQL або MySQL?» Моя відповідь завжди та сама: «Це – питання переваги». Ви можете поставити безліч інших розробників те саме питання, і їх відповіді будуть дуже різного штибу.

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

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

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

Список особливостей та можливостей

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

З таблиці ми бачимо, що PostgreSQL пропонує повні особливостіі можливості традиційних додатків баз даних, тоді як MySQL зосереджується більш швидкому виконанні (роботі) для веб додатків. Розвиток індустрії «відкритих вихідних джерел» принесе більшу кількість особливостей та можливостей у наступних версіях обох баз даних.

Таблиця A: порівняння MySQL та PostgreSQL

Особливості	PostgreSQL	MySQL
ANSI SQL сумісність	Близька до стандарту ANSI SQL	Дотримуйтесь деяких стандартів ANSI SQL
Швидкість роботи	Повільніше	Швидше
Вкладені селективи	Так	Ні
Транзакації	Так	Так, однак, повинен використовуватися тип таблиці InnoDB
Відповідь бази даних	Так	Так
Підтримка зовнішніх ключів	Так	Ні
Уявлення	Так	Ні
Збережені процедури	Так	Ні
Тригери	Так	Ні
Unions	Так	Ні
Повні Joins	Так	Ні
Обмежувачі цілісності	Так	Ні
Підтримка Windows	Так	Так
Вакуум (очищення)	Так	Ні
ODBC	Так	Так
JDBC	Так	Так
Різні типи таблиць	Ні	Так

Коли використовувати MySQL

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

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

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

MySQL щодо швидше PostgreSQL.
Дизайн та планування бази даних дещо простіше.
Можна створити простий веб-сайт з використанням бази.
Відповіді на запити MySQLбули добре протестовані.
Немає потреби використовувати методи очищення (вакуум).

Коли використовувати PostgreSQL

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

Існує чимало розробників, які віддають перевагу багатим функціональним можливості SQLкоманд PostgreSQL. Одна з найбільш відчутних відмінностей між MySQL та PostgreSQL – неможливість створення вкладених підзапитів (селектів) у MySQL. PostgreSQL відповідає багатьма SQL стандартам ANSI, таким чином дозволяючи створення складних команд SQL.

Декілька причин використовувати PostgreSQL:

Складний дизайн бази даних.
Переїзд із Oracle, Sybase або MSSQL.
Складні набори правил.
Використання процедурних мов на сервері.
Транзакації
Використання процедур, що зберігаються.
Використання географічних даних.
R-Trees (наприклад, використання індексів).

Висновок

Ви повинні будете вибрати, зваживши всі плюси та мінуси, яка база даних є «досконалою» для вашої програми або сайту. А може бути й таке, що ви захочете використати обидві бази (бувають і такі випадки). Мій висновок – одна база не обов'язково краща за іншу, і кожна з них займає свою певну нішу у світі баз даних з відкритим вихідним кодом.