PostgreSQL краще інших SQL баз даних з відкритим вихідним кодом. Частина 2

Друзі, представляємо вашій увазі другу частину перекладу «Чим PostgreSQL краще?». Сподіваємося, вона викличе таке ж гаряче обговорення в коментарях, як і перша частина. А також з радістю продовжимо з вами дискусію особисто PG Day'16 Russia, до якої залишилося зовсім трохи!

У слогані PostgreSQL заявляється, що це «Сама передова база даних з відкритим вихідним кодом у світі». першої частини цієї серії ми розглянули зберігання даних — модель, структури, типи і обмеження за розміром, — щоб дати вам кілька причин, чому Постгрес підтверджує свої слова справою. У другій частині ми поговоримо про маніпуляції з даними і пошуку, включаючи індексування, віртуальних таблиць і можливості запитів. У цій серії ми з'ясовуємо, що вигідно відрізняє PostgreSQL від інших баз даних з відкритим вихідним кодом, а саме — від MySQL, MariaDB і Firebird.



Індексування
Постгрес пропонує можливості індексування, яких немає в інших БД з відкритим вихідним кодом. Крім стандартних індексів, він підтримує часткові індекси, функціональні індекси, GiST і GIN індекси. Давайте розглянемо деякі з них докладніше.

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

-- створення індексу тільки для платних користувачів
CREATE INDEX paying_accounts_idx ON accounts (account_id) 
WHERE account_type <> 'test';

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

Функціональні індекси
Функціональні індекси (або індекси на основі виразів) можуть бути створені за допомогою будь-якої функції, щоб попередньо обчислити стовпець для індексування. Нові значення індексуються і розглядаються як константи для виконання запитів, а не обчислюються всякий раз, коли запускається запит. Наприклад, якщо у вас є веб-журнал кліків, який збирає кліки URL в якому б форматі вони не надходили, ви можете захотіти створити індекс, що приводить заслання до нижнього регістра для нормалізації даних PostgreSQL чутливий до регістру: compose.io Compose.io будуть вважатися різними результатами):

-- створення індексу для URL-у нижньому регістрі
CREATE INDEX webhits_lower_urls_idx ON webhits (lower(url)); 

GIST і GIN (а також і BRIN!)
GiST (Generalized Search Tree, Узагальнене Дерево Пошуку) дозволяє об'єднувати B-дерево, R-дерево і визначені користувачем типи індексів для створення індивідуального індексу з розширеними можливостями запитів. GiST використовується в PostGIS (який ми зробили стандартним для всіх інсталяцій PostgreSQL з січня) і OpenFTS (повнотекстової пошуковий движок з відкритим вихідним кодом). Постгрес також підтримує SP-GiST, який дозволяє створювати партицированные пошукові індекси для неймовірно швидкого пошуку.

GIN (Generalized Inverted Index, Узагальнений Інвертований Індекс) дозволяє індексувати складові типи даних, які дають можливість об'єднувати інші типи даних різними способами для створення чогось повністю індивідуального. Докладний опис складових типів даних ви можете знайти на першої частини цієї серії.

Синтаксис для створення GIST і GIN індексів буде наступним: CREATE INDEX… ON… USING GIST|GIN.... Дуже просто!

В PostgreSQL 9.5 був представлений BRIN (Block Range Index), який дозволяє розбивати великі таблиці діапазони на підставі стовпця для індексування. Це означає, що планувальник запитів може сканувати тільки діапазон, зазначений у запиті. Також, при індексуванні діапазонів необхідне для індексування місце на диску буде істотно менше, ніж при стандартному B-Tree індексі.

Для порівняння
Інші розглянуті нами SQL бази даних скорочують розрив, коли мова заходить про функціональних індексах. В MySQL 5.7.6 були представлені генеруються стовпці, які можна використовувати як функціональні індекси. У MariaDB віртуальні (також відомі як «генеруються» або «обчислювані») стовпці з'явилися у версії 5.2, але підтримують тільки використання вбудованих функцій для створення стовпців (визначені користувачем функції відсутні). У версії 2.0 Firebird було представлено індексування виразів з допомогою обчислюваних стовпців. Тим не менш, жодна з цих баз даних не підтримує часткові, GiST або GIN індекси. Крім того, ми згадували у першій частині, що нативні типи даних JSON не можуть бути проіндексовані в цих базах даних.

Коли ви налаштуєте всі індекси і захочете проаналізувати їх продуктивність, не забудьте прочитати статтю Метта Барра з mySidewalk «Проста перевірка індексів в PostgreSQL».

Функції віртуальних таблиць
Віртуальні таблиці необхідні для багатьох запитів. Всі порівнювані нами SQL бази даних пропонують якусь функціональність віртуальних таблиць. PostgreSQL може дати вам більше.

CTEs і рекурсія
Постгрес підтримує Common Table Expressions (CTE) з використанням виразу WITH. Ми демонстрували цю функцію в статті PostgreSQL — Series, Random and With. CTE дозволяють створювати віртуальні таблиці прямо у вашому запиті, виражаючи логічну послідовність операцій. Таким чином, їх набагато простіше читати і тестувати, ніж віртуальні таблиці, створені з допомогою вкладених запитів десь в іншій частині запиту. CTE в PostgreSQL також можуть бути використані рекурсивно. Ця зручна функція дозволяє пройти наскрізь через ієрархію з багаторазово посилається на самого себе запитом, поки не залишиться більше рівнів даних, які можна повернути. Ось приклад рекурсивного CTE, яке ідентифікує рівні, теми і батьківські відносини в систематиці теми:

-- запит з рекурсією
CTE WITH RECURSIVE topic_taxonomy_recursive 
(level, parent_topic_name, topic_name)
AS ( 
SELECT 1, tt.parent_topic_name, tt.topic_name
FROM topic_taxonomy tt
WHERE tt.parent_topic_name = 'All Topics'
UNION ALL
SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name
FROM topic_taxonomy_recursive ttr, topic_taxonomy tt
WHERE ttr.topic_name = tt.parent_topic_name
) SELECT level, parent_topic_name, topic_name
FROM topic_taxonomy_recursive;

MySQL і MariaDB не використовують умова WITH і, отже, формально не підтримують CTE. Хоча ви і можете за допомогою вкладених запитів створити в цих базах даних похідні таблиці, вони не дозволяють робити рекурсію. Крім того, незважаючи на те, що оптимізатор запитів в MySQL був вдосконалений з часів релізу версії 5.6, вкладені запити в базі даних відомі своєю проблемністю і можуть істотно вплинути на продуктивність. Firebird в цьому питанні випереджає MySQL і MariaDB і збігається за функціональністю з Постгресом: підтримує CTE з допомогою WITH і надає можливість рекурсії.

Матеріалізовані уявлення
Матеріалізовані уявлення (Materialized views) — це ще одна зручна функція віртуальних таблиць, підтримувана PostgreSQL. Вони, як і звичайні views, представляють результат запиту, який ви будете часто використовувати, але різниця в тому, що результат зберігається на диску, як звичайна таблиця. Матеріалізовані уявлення можуть бути проіндексовані. Крім того, на відміну від звичайних уявлень, які пересоздаются кожен раз, коли їх викликають, подання з збереженим результатом фіксуються у часі. Вони не оновлюються, якщо не робити це навмисно. Це може істотно збільшити швидкість, з якою здійснюються запити, що використовують materialized views. Замість використання звичайних уявлень або необхідності здійснювати складні об'єднання таблиць або виконувати групувальні функції в запиті, використовуйте materialized views, де всі необхідні дані вже підготовлені і чекають на диску. Коли вам знадобиться оновити дані в матеріалізованому поданні із збереженим результатом, це можна буде зробити на вимогу за допомогою команди REFRESH. Наведемо приклад materialized view, яке видає зведені дані про доходи:

-- створення матеріалізованого подання, що містить зведені дані про доходи
CREATE MATERIALIZED VIEW aggregatedMonthlyRevenue 
(year, month, total_revenue)
AS ( 
SELECT date_part('year', date) AS year,
date_part('month', date) AS month,
SUM(revenue) AS total_revenue
Revenue FROM
WHERE date >= '2014-01-01'
GROUP BY date_part('year', date),
date_part('month', date)
ORDER BY date_part('year', date),
date_part('month', date)
);

-- оновлення подання, при необхідності
REFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue; 

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

Можливості запитів
Можливості запитів в Постгресе великі.

Ми вже трохи говорили про WITH в попередньому розділі. Давайте розглянемо ще кілька додаткових функцій, які можуть бути використані для запитів SELECT.

Об'єднання запитів
PostgreSQL надає умови UNION, INTERSECT і EXCEPT для взаємодії між запитами SELECT. UNION додасть результати другого запиту SELECT до результатів першого. INTERSECT повертає тільки ті рядки, які відповідають обом запитів SELECT. EXCEPT повертає тільки ті рядки першого запиту SELECT, які не збігаються зі строками з другого запиту SELECT. Давайте розглянемо приклад з використанням EXCEPT, де ми хочемо повернути контактну інформацію користувачів, за винятком тих випадків, коли користувач отримав email протягом минулого тижня і відповів на нього.

/*
запит для вибірки інфо
про користувачів, які не отримували
листів за останній тиждень
*/
SELECT c.lastName, c.firstName, c.email 
FROM customers c 
EXCEPT 
SELECT e.lastName, e.firstName, e.email 
FROM email_log e 
WHERE e.email_date > current_date - interval '7 days' 
AND e.email_action_date > current_date - interval '7 days'
AND email_action_type = 'response';

Хоча MySQL, MariaDB, і Firebird підтримують UNION, жодна з них не підтримує ні INTERSECT, ні EXCEPT. Тим не менш, використовуючи в запиті об'єднання і умова EXISTS, можна отримати той же результат, що і в PostgreSQL. Але запит при цьому вийде більш складним.

Віконні функції
Віконні функції, які являють собою агрегатні функції поверх деяких рядків результату (надаючи «вікно» домен), можуть бути надзвичайно корисними. По суті, вони дозволяють зробити ітерацію по рядках в секції, які відносяться до поточної рядку, щоб виконати функцію. Стандартні функції включають ROW_NUMBER(), RANK(), DENSE_RANK() і PERCENT_RANK(). Ключове слово OVER, опціонально використовується з PARTITION BY and ORDER BY, показує, що використовується віконна функція. В якості прикладу в параграфі «Функції і не тільки» нижче ми використовували віконну функцію з ROW_NUMBER() OVER..., щоб визначити медіану у серії числових значень. Зауважте, що умова WINDOW в запитах з віконними функціями не є обов'язковим, але дозволяє створювати і іменувати вікна для збереження порядку.

Firebird, MySQL і MariaDB на даний момент не підтримують віконні функції, хоча вони і анонсувалися кілька років тому при плануванні Firebird 3.

Латеральні вкладені запити
Ключове слово LATERAL може бути застосоване до вкладених запитів в умови FROM, щоб додати перехресні посилання між вкладеним запитом та іншими таблицями або віртуальними таблицями, які були створені до нього. Таким способом можна писати більш прості запити. Це працює таким чином, що кожна рядок оцінюється у порівнянні з таблицею, на яку веде перехресне посилання, що може означати покращення показників швидкості в процесі виконання запиту. Наведемо приклад, в якому ми хочемо отримати список студентів та інформацію про те, чи читали вони останнім часом що-небудь на тему технологій:

-- запит з використанням LATERAL у вкладеному запиті
SELECT s.firstName, s.LastName, x.topic_name 
FROM students s 
JOIN content_log c ON c.student_id = s.id 
LEFT OUTER JOIN LATERAL ( 
SELECT t.topic_name
FROM content_topics t
WHERE t.parent_topic_name = 'Technology'
AND t.id = c.topic_id
AND c.date > current_date - interval '30 days'
) x ON true;

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

І ще дещо на замітку: MySQL і MariaDB не підтримують FULL OUTER JOIN, але можна використовувати обхідний шлях з використанням UNION ALL, щоб об'єднати всі рядки двох таблиць.

Функції і не тільки
PostgreSQL надає надійні вбудовані оператори та функції, у тому числі такі, які підтримують спеціалізовані типи даних, докладно розглянуті в першій частині цієї серії [посилання на Частину 1]. Крім того, він дозволяє вам створювати власні оператори та функції (включаючи агрегати), а також збережені процедури і тригери. Ми не зможемо детально розглянути їх всі, оскільки тема дуже обширна (!), але давайте розберемо кілька простих прикладів функцій.

Постгрес підтримує 4 види користувацьких функцій: мова запиту, процедурний мова, мова C і внутрішній. Кожен вид може брати і повертати як базові, так і складені типи даних. Зауважте, що в PostgreSQL команда CREATE FUNCTION використовується не тільки для створення функцій, але і збережених процедур.

Давайте розглянемо приклад створення функції, яка повертає складовою тип даних:

-- створюємо новий складовою тип під назвою "datetext"
CREATE TYPE datetext AS ( 
date date,
date_as_text text
);

/*
створюємо функцію, яка приймає значення дати
і повертає дату і відповідний їй datetext
*/
CREATE FUNCTION show_date_as_text(date) 
RETURNS datetext -- this is our composite type 
AS 
$$ SELECT $1, to_char($1, 'FMMonth FMDD, YYYY') $$
LANGUAGE SQL;

-- запит із застосуванням функції
SELECT show_date_as_text('2015-01-01');

-- Повертає: (2015-01-01,"January 1, 2015")

А ось приклад реальної функції для знаходження медіани в серії числових даних:

-- створюємо функцію, яка знаходить медіану у серії числових даних
CREATE FUNCTION median(numeric[]) 
RETURNS numeric 
AS 
$$ SELECT AVG(x.result)
FROM (
SELECT result, 
ROW_NUMBER() OVER (ORDER BY val) as ra,
ROW_NUMBER() OVER (ORDER BY val DESC) as rd
FROM unnest($1) result -- notice the use of array "unnest"
) AS x
WHERE x.ra BETWEEN x.rd - 1 AND x.rd + 1;
$$
LANGUAGE SQL;

-- запит із застосуванням функції
SELECT median(ARRAY[1,2,3,4,5,6,7]);

-- Повертає: 4

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

Можливості індивідуалізації Постгреса і не мають рівних серед MySQL, MariaDB і Firebird.

Мовні розширення
Для PostgreSQL є безліч мовних розширень. Деякі з них є частиною дистрибутива, а безліч інших доступні через сторонніх розробників.

У Compose ми підтримуємо тільки доверяемые (trusted) розширення мови для PostgreSQL, щоб забезпечити безпеку ваших інсталяцій. Ми додали підтримку PL/Perl в лютому і PL/v8 (процедурний мову на основі JavaScript) — у серпні. Ці мовні розширення, які мають більше вбудованих функцій, ніж вбудований мова PL/pgSQL на основі SQL (також доступний в інсталяціях Compose), дозволяють створювати вишукані скрипти для маніпуляцій та обробки даних на сервері.

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

Незважаючи на те, що ми розповіли лише про невеликому безліч можливостей, які виділяють Постгрес на тлі інших SQL рішень з відкритим вихідним кодом, насправді їх набагато більше (і ще більше з'явилося у версії 9.5!). Ми сподіваємося, що ця серія з двох статей забезпечила переконливий огляд причин, по яких вам варто вибрати PostgreSQL.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.