Коли я починав свою кар'єру розробника, моєю першою роботою стала DBA (адміністратор бази даних, АБД). У ті роки, ще до AWS RDS, Microsoft, Google Cloud та інших хмарних сервісів, існувало два типу АБД:
- АБД інфраструктури відповідали за налаштування бази даних, конфігурування сховища і турбувалися за резервні копії та реплікацію. Після налаштування БД інфраструктурний адміністратор час від часу «налаштовував екземпляри», наприклад, уточнював розміри кешей.
- АБД програм отримував від АБД інфраструктури чисту базу і відповідав за її архітектуру: створення таблиць, індексів, обмежень і налаштування SQL. АБД також реалізовував ETL-процеси та міграцію даних. Якщо команда використовували збережені процедури, то АБД також їх підтримував.
АБД програм зазвичай були частиною команд розробки. Вони володіли глибокими знаннями з конкретної теми, тому зазвичай працювали тільки над одним-двома проєктами. Інфраструктурні адміністратори баз даних зазвичай входили в ІТ-команду і могли одночасно працювати над кількома проєктами.
Я адмін бази даних програм
У мене ніколи не було бажання возитися з бэкапами або налаштовувати сховище (упевнений, це цікаво!). Донині мені подобається говорити, що я адмін БД, який знає, як розробляти програми, а не розробник, який розбирається в базах даних.
У цій статті я поділюся хитрощами про розробку баз даних, які дізнався за свою кар'єру.
Зміст:
- Оновлюйте лише те, що потрібно оновити
- При великих завантаженнях відключайте обмеження та індекси
- Для проміжних даних використовуйте UNLOGGED-таблиці
- Реалізуйте процеси за допомогою WITH і RETURNING
- В колонках з низькою вибірковістю уникайте індексів
- Використовуйте часткові індекси
- Завжди завантажуйте відсортовані дані
- Колонки з високою кореляцією індексуйте з допомогою BRIN
- Робіть індекси «невидимими»
- Не плануйте початок тривалих процесів на початок будь-якої години
- Висновок
Оновлюйте лише те, що потрібно оновити
Операція UPDATE споживає досить багато ресурсів. Для її прискорення найкраще оновлювати тільки те, що потрібно оновити.
Ось приклад запиту на нормалізацію колонки email:
db=# UPDATE users SET email = lower(email);
UPDATE 1010000
Time: 1583.935 ms (00:01.584)
Виглядає невинно, так? Запит оновлює адреси пошти для 1 010 000 користувачів. Але чи потрібно оновлювати всі рядки?
db=# UPDATE users SET email = lower(email)
db-# WHERE email != lower(email);
UPDATE 10000
Time: 299.470 ms
Потрібно було оновити 10 000 рядків. Зменшивши кількість оброблюваних даних, ми знизили тривалість виконання з 1,5 секунд до менш ніж 300 мс. Це також заощадить нам надалі сили на супровід бази даних.
Оновлюйте лише те, що потрібно оновити.
Такий тип великих оновлень дуже часто зустрічається в скриптах міграції даних. Коли наступного разу будете писати подібний скрипт, переконайтеся, що ви оновлюєте лише необхідне.
При великих завантаженнях відключайте обмеження та індекси
Обмеження — важлива частина реляційних баз даних: вони зберігають дані консистентними та надійними. Але у всього є своя ціна, і найчастіше доводиться розплачуватися при завантаженні або оновленні великої кількості рядків.
Давайте задамо схему невеликого сховища:
DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
id serial PRIMARY KEY,
name TEXT NOT NULL,
price INT NOT NULL
);
INSERT INTO product (name, price)
SELECT random()::text, (random() * 1000)::int
FROM generate_series(0, 10000);
DROP TABLE IF EXISTS customer CASCADE;
CREATE TABLE customer (
id serial PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO customer (name)
SELECT random()::text
FROM generate_series(0, 100000);
DROP TABLE IF EXISTS sale;
CREATE TABLE sale (
id serial PRIMARY KEY,
created timestamptz NOT NULL,
product_id int NOT NULL,
customer_id int NOT NULL
);
Тут визначаються різні типи обмежень, такі як «not null», а також обмеження на унікальність значень
Щоб встановити базову лінію, почнемо додавати в таблицю sale зовнішні ключі, після чого завантажемо туди дані
db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk db-# FOREIGN KEY (product_id) REFERENCES product(id); ALTER TABLE Time: 18.413 ms db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk db-# FOREIGN KEY (customer_id) REFERENCES customer(id); ALTER TABLE Time: 5.464 ms db=# CREATE INDEX sale_created_ix ON sale(created); CREATE INDEX Time: 12.605 ms db=# INSERT INTO SALE (created, product_id, customer_id) db-# SELECT db-# now() - interval '1 hour' * random() * 1000, db-# (random() * 10000)::int + 1, db-# (random() * 100000)::int + 1 db-# FROM generate_series(1, 1000000); INSERT 0 1000000 Time: 15410.234 ms (00:15.410)
Після визначення обмежень та індексів завантаження в таблицю мільйона рядків зайняла близько 15,4 с.
Тепер спочатку завантажимо дані в таблицю, і тільки потім додамо обмеження та індекси:
db=# INSERT INTO SALE (created, product_id, customer_id) db-# SELECT db-# now() - interval '1 hour' * random() * 1000, db-# (random() * 10000)::int + 1, db-# (random() * 100000)::int + 1 db-# FROM generate_series(1, 1000000); INSERT 0 1000000 Time: 2277.824 ms (00:02.278) db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk db-# FOREIGN KEY (product_id) REFERENCES product(id); ALTER TABLE Time: 169.193 ms db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk db-# FOREIGN KEY (customer_id) REFERENCES customer(id); ALTER TABLE Time: 185.633 ms db=# CREATE INDEX sale_created_ix ON sale(created); CREATE INDEX Time: 484.244 ms
Завантаження пройшло набагато швидше, 2,27 секунд замість 15,4. Індекси та обмеження створювалися після завантаження даних помітно довше, але весь процес виявився набагато швидше: 3,1 с. замість 15,4.
На жаль, в PostgreSQL з індексами так само вчинити не вийде, їх можна лише видаляти і перестворювати. В інших базах, наприклад, Oracle, можна відключати і включати індекси без переcтворення.
Для проміжних даних використовуйте UNLOGGED-таблиці
Коли ви міняєте дані в PostgreSQL, зміни записуються в журнал (write ahead log (WAL)). Він використовується для підтримки цілісності, швидкої переіндексації в ході відновлення і підтримки реплікації.
Запис в WAL часто потрібен, але є деякі обставини, при яких ви можете відмовитися від WAL заради прискорення процесів. Наприклад, у випадку з проміжними таблицями.
Проміжними називають одноразові таблиці, в яких зберігаються тимчасові дані, використовувані для реалізації якихось процесів. Приміром, у ETL-процесах дуже часто завантажують дані з CSV-файлів в проміжні таблиці, очищають інформацію, а потім вантажать її в таблицю. У такому сценарії проміжна таблиця — одноразова і не використовується в резервних копіях або репліках.
UNLOGGED-таблиця.
Проміжні таблиці, які не потрібно відновлювати у разі збою і які не потрібні в репліках, можна задати як UNLOGGED:
CREATE UNLOGGED TABLE staging_table ( /* table definition */ );
Увага: перш ніж використовувати UNLOGGED, переконайтеся, що ви повністю усвідомлюєте всі наслідки.
Реалізуйте процеси за допомогою WITH і RETURNING
Припустимо, у вас таблиця користувачів, і ви виявили, що в ній є дані що повторюються :
Table setup
db=# SELECT u.id u.email, o.id as order_id
FROM orders o JOIN users u ON o.user_id = u.id;
id | email | order_id
----+-------------------+----------
1 | foo@bar.baz | 1
1 | foo@bar.baz | 2
2 | me@hakibenita.com | 3
3 | ME@hakibenita.com | 4
3 | ME@hakibenita.com | 5
Користувач haki benita зареєстрований двічі, з поштою ME@hakibenita.com та me@hakibenita.com. Оскільки ми не нормалізуємо адреси електронної пошти при внесенні в таблицю, тепер нам доведеться розібратися з дублями.
Нам потрібно:
- Визначити дублі за адресами у верхньому регістрі, і зв'язати дубльованих користувачів один з одним.
- Оновити замовлення, щоб вони посилалися тільки на один з дублів.
- Прибрати дублі з таблиці.
Зв'язати дубльованих користувачів можна за допомогою проміжної таблиці:
db-# SELECT
db-# lower(email) AS normalized_email,
db-# min(id) AS convert_to_user,
db-# array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
db-# FROM
db-# users
db-# GROUP BY
db-# normalized_email
db-# HAVING
db-# count(*) > 1;
CREATE TABLE
db=# SELECT * FROM duplicate_users;
normalized_email | convert_to_user | convert_from_users
-------------------+-----------------+--------------------
me@hakibenita.com | 2 | {3}
У проміжної таблиці містяться звязок між дублями. Якщо користувач з нормалізованою адресою пошти з'являється більше одного разу, ми присвоюємо йому мінімальний ID користувача, у до якого звернемо всі дублі. Інші користувачі зберігаються в array column і всі посилання на них будуть оновлені.
З допомогою проміжної таблиці оновимо посилання на дублі у таблиці orders:
db=# UPDATE db-# orders o db-# SET db-# user_id = du.convert_to_user db-# FROM db-# duplicate_users du db-# WHERE db-# o.user_id = ANY(du.convert_from_users); UPDATE 2
Тепер можна безпечно видалити дублі з users:
db=# DELETE FROM db-# users db-# WHERE db-# id IN ( db(# SELECT unnest(convert_from_users) db(# FROM duplicate_users db(# ); DELETE 1
Зверніть увагу, що для «перетворення» масиву ми використовували функцію unnest, яка перетворює кожен елемент на рядок.
Результат:
db=# SELECT u.id, u.email, o.id as order_id db-# FROM orders o JOIN users u ON o.user_id = u.id; id | email | order_id ----+-------------------+---------- 1 | foo@bar.baz | 1 1 | foo@bar.baz | 2 2 | me@hakibenita.com | 3 2 | me@hakibenita.com | 4 2 | me@hakibenita.com | 5
Відмінно, всі появи користувача 3 (ME@hakibenita.com) перетворені в користувача 2 (me@hakibenita.com).
Можемо перевірити, що дублі видалені з таблиці users:
db=# SELECT * FROM users; id | email ----+------------------- 1 | foo@bar.baz 2 | me@hakibenita.com
Тепер можна позбутися від проміжної таблиці:
db=# DROP TABLE duplicate_users;
DROP TABLE
Все добре, але занадто довго і потрібна очистка! Чи є спосіб краще?
Узагальнені табличні вирази (CTE)
За допомогою узагальнених табличних виразів, також відомих як вираз WITH, ми можемо виконати всю процедуру з допомогою єдиного SQL-запиту:
WITH duplicate_users AS (
SELECT
min(id) AS convert_to_user,
array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
FROM
users
GROUP BY
lower(email)
HAVING
count(*) > 1
),
update_orders_of_duplicate_users AS (
UPDATE
orders o
SET
user_id = du.convert_to_user
FROM
duplicate_users du
WHERE
o.user_id = ANY(du.convert_from_users)
)
DELETE FROM
users
WHERE
id IN (
SELECT
unnest(convert_from_users)
FROM
duplicate_users
);
Замість проміжної таблиці ми створили узагальнений табличний вираз і багаторазово його використовували.
Повернення результатів з CTE
Одна з переваг виконання DML всередині виразу WITH полягає в тому, що ви можете повернути з нього дані за допомогою ключового слова RETURNING. Припустимо, нам потрібен звіт про кількість оновлених і видалених рядків:
WITH duplicate_users AS (
SELECT
min(id) AS convert_to_user,
array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
FROM
users
GROUP BY
lower(email)
HAVING
count(*) > 1
),
update_orders_of_duplicate_users AS (
UPDATE
orders o
SET
user_id = du.convert_to_user
FROM
duplicate_users du
WHERE
o.user_id = ANY(du.convert_from_users)
RETURNING o.id
),
delete_duplicate_user AS (
DELETE FROM
users
WHERE
id IN (
SELECT unnest(convert_from_users)
FROM duplicate_users
)
RETURNING id
)
SELECT
(SELECT count(*) FROM update_orders_of_duplicate_users) AS orders_updated,
(SELECT count(*) FROM delete_duplicate_user) AS users_deleted
;
Результат:
orders_updated | users_deleted
----------------+---------------
2 | 1
Привабливість підходу в тому, що весь процес виконується однією командою, тому немає необхідності управляти транзакціями або турбуватися про очищення проміжної таблиці в разі збою процесу.
Увага: Читач Reddit вказав мені на можливу непередбачувану поведінку виконання DML в узагальнених табличних виразах:
Суб-вирази вWITHвиконуються конкурентно один з одним і з основним запитом. Тому при використанні вWITHвиразів що модифікують дані, фактичний порядок оновлень буде непередбачуваним
Це означає, що ви не можете покладатися на порядок виконання незалежних суб-виразів. Виходить, що якщо між ними є залежність, як у прикладі вище, ви можете покладатися на виконання залежних підвиразів до їх використання.
В колонках з низькою вибірковістю уникайте індексів
Припустимо, у вас є процес реєстрації, при якому користувач входить за адресою пошти. Щоб активувати акаунт, потрібно верифікувати пошту. Таблиця може виглядати так:
db=# CREATE TABLE users (
db-# id serial,
db-# username text,
db-# activated boolean
db-#);
CREATE TABLE
Більшість ваших користувачів — громадяни свідомі, вони реєструються з коректною поштовою адресою і негайно активують обліковий запис. Давайте заповнимо таблицю даними користувача, і будемо вважати, що 90 % користувачів активовано:
db=# INSERT INTO users (username, activated) db-# SELECT db-# md5(random()::text) AS username, db-# random() < 0.9 AS activated db-# FROM db-# generate_series(1, 1000000); INSERT 0 1000000 db=# SELECT activated, count(*) FROM users GROUP BY activated; activated | count -----------+-------- f | 102567 t | 897433 db=# VACUUM ANALYZE users; VACUUM
Щоб вибрати кількість активованих і неактивованих користувачів, можна створити індекс по колонці activated:
db=# CREATE INDEX users_activated_ix ON users(activated);
CREATE INDEX
І якщо ви виберете кількість неактивованих користувачів, база скористається індексом:
db=# EXPLAIN SELECT * FROM users WHERE NOT activated;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on users (cost=1923.32..11282.99 rows=102567 width=38)
Filter: (NOT activated)
-> Bitmap Index Scan on users_activated_ix (cost=0.00..1897.68 rows=102567 width=0)
Index Cond: (activated = false)
База вирішила, що фільтр видасть 102 567 позицій, приблизно 10 % таблиці. Це узгоджується з завантаженими нами даними, так що таблиця добре впоралася.
Однак якщо ми вибиремо кількість активованих користувачів, то виявимо, що база вирішила не використовувати індекс:
db=# EXPLAIN SELECT * FROM users WHERE activated;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on users (cost=0.00..18334.00 rows=897433 width=38)
Filter: activated
Багатьох розробників це збиває з пантелику, коли база даних не використовує індекс. Пояснити, чому вона так робить, можна наступним чином: якби вам потрібно було прочитати всю таблицю, ви скористалися б індексом?
Ймовірно що ні, навіщо це потрібно? Читання з диска — операція дорога, тому ви захочете читати якомога менше. Наприклад, якщо таблиця розміром 10 Мб, а індекс розміром 1 Мб, то для зчитування всій таблиця доведеться зчитати з диска 10 Мб. А якщо додати індекс, то вийде 11 Мб. Це марнотратно.
Давайте тепер подивимося на статистику, яку PostgreSQL зібрав по таблиці:
db=# SELECT attname, n_distinct, most_common_vals, most_common_freqs
db-# FROM pg_stats
db-# WHERE tablename = 'users' AND attname='activated';
------------------+------------------------
attname | activated
n_distinct | 2
most_common_vals | {t,f}
most_common_freqs | {0.89743334,0.10256667}
Коли PostgreSQL проаналізував таблицю, він з'ясував, що у колонці activated є два різних значення. Значення t в колонці most_common_vals відповідає частоті 0.89743334 в колонці most_common_freqs, а значення f відповідає частоті 0.10256667. Після аналізу таблиці база даних визначила, що 89,74 % записів — це активовані користувачі, а решта 10,26 % — неактивовані.
На основі цієї статистики PostgreSQL вирішив, що краще сканувати всю таблицю, ніж припускати, що 90 % рядків задовольнять умові. Поріг, після якого база може вирішувати, чи використовувати індекс, залежить від багатьох факторів, і жодного емпіричного правила тут немає.
Індекс для колонок з низькою і високою вибірковістю.
Використовуйте часткові індекси
У попередній главі ми створили індекс для колонки з булевими значеннями, в якій близько 90 % записів були true (активовані користувачі).
Коли ми вибрали кількість активних користувачів, база не використовувала індекс. А коли запросили кількість неактивованих, база використовувала індекс.
Виникає питання: якщо база не збирається користуватися індексом для фільтрування активних користувачів, навіщо нам індексувати їх в першу чергу?
Перш ніж відповісти на це питання, давайте подивимося на вагу повного індексу по колонці activated:
db=# \di+ users_activated_ix Schema | Name | Type | Owner | Table | Size --------+--------------------+-------+-------+-------+------ public | users_activated_ix | index | haki | users | 21 MB
Індекс важить 21 Мб. Просто для довідки: таблиця з користувачами займає 65 Мб. Тобто вага індексу ~32 % ваги бази. При цьому ми знаємо, що ~90 % вмісту індексу навряд чи буде використовуватися.
В PostgreSQL можна створювати індекс тільки для частини таблиці — так званий частковий індекс:
db=# CREATE INDEX users_unactivated_partial_ix ON users(id)
db-# WHERE not activated;
CREATE INDEX
З допомогою виразу WHERE ми обмежуємо рядки які охоплюються індексом . Давайте перевіримо, чи спрацює:
db=# EXPLAIN SELECT * FROM users WHERE not activated;
QUERY PLAN
------------------------------------------------------------------------------------------------
Index Scan using users_unactivated_partial_ix on users (cost=0.29..3493.60 rows=102567 width=38)
Відмінно, база виявилася досить розумною і зрозуміла, що використаний нами в запиті логічний вираз може підійти для часткового індексу.
У такого підходу є ще одна перевага:
db=# \di+ users_unactivated_partial_ix
List of relations
Schema | Name | Type | Owner | Table | Size
--------+------------------------------+-------+-------+-------+---------
public | users_unactivated_partial_ix | index | haki | users | 2216 kB
Повний індекс по колонці важить 21 Мб, а частковий — всього 2,2 Мб. Тобто 10 %, що відповідає частці неактивованих користувачів в таблиці.
Завжди завантажуйте відсортовані дані
Це один з найчастіших моїх коментарів при розборі коду. Порада не настільки інтуїтивна, як інші, і може дуже вплинути на продуктивність.
Припустимо, у вас є величезна таблиця з конкретними продажами:
db=# CREATE TABLE sale_fact (id serial, username text, sold_at date);
CREATE TABLE
Кожну ніч в ході ETL-процесу ви завантажуєте дані в таблицю:
db=# INSERT INTO sale_fact (username, sold_at) db-# SELECT db-# md5(random()::text) AS username, db-# '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at db-# FROM db-# generate_series(1, 100000); INSERT 0 100000 db=# VACUUM ANALYZE sale_fact; VACUUM
Щоб зімітувати завантаження, скористаємося випадковими даними. Вставили 100 тис. рядків з випадковими іменами, а дати продаж в періоді з 1 січня 2020 року і на два роки вперед.
Здебільшого таблиця використовується для підсумкових звітів про продажі. Найчастіше фільтрують по даті, щоб подивитися продажі за певний період. Щоб прискорити сканування діапазону, створимо індекс sold_at:
db=# CREATE INDEX sale_fact_sold_at_ix ON sale_fact(sold_at);
CREATE INDEX
Поглянемо на план виконання запиту на вибірку всіх продажів в червні 2020:
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Bitmap Heap Scan on sale_fact (cost=108.30..1107.69 rows=4293 width=41)
Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Heap Blocks: exact=927
-> Bitmap Index Scan on sale_fact_sold_at_ix (cost=0.00..107.22 rows=4293 width=0)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Planning Time: 0.191 ms
Execution Time: 5.906 ms
Прогнавши запит кілька разів, щоб прогріти кеш, тривалість виконання стабілізувалася на рівні 6 мс.
Сканування за бітовій карті (Bitmap Scan)
У плані виконання ми бачимо, що база використовувала сканування за бітовій карті. Воно проходить у два етапи:
Побудова бітової карти (Bitmap Index Scan): база проходить по всьому індексуsale_fact_sold_at_ixі знаходить всі сторінки таблиці, що містять релевантні рядка.Сканування за бітовій карті (Bitmap Heap Scan): база зчитує сторінки, що містять релевантні рядки, і знаходить ті з них, що задовольняють умову.
Сторінки можуть містити багато рядків. На першому етапі індекс використовується для пошуку сторінки. На другому етапі шукаються рядки в сторінках, звідси випливає операція Recheck Cond в плані виконання.
На цьому моменті багато адміністраторів баз даних і розробників закруглятся і перейдуть до наступного запитом. Але є спосіб поліпшити цей запит.
Індексне сканування (Index Scan)
Внесемо невеликі зміна в завантаження даних.
db=# TRUNCATE sale_fact; TRUNCATE TABLE db=# INSERT INTO sale_fact (username, sold_at) db-# SELECT db-# md5(random()::text) AS username, db-# '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at db-# FROM db-# generate_series(1, 100000) db-# ORDER BY sold_at; INSERT 0 100000 db=# VACUUM ANALYZE sale_fact; VACUUM
На цей раз ми завантажили дані, відсортовані за sold_at.
Тепер план виконання того ж запиту виглядає так:
db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.29..184.73 rows=4272 width=41)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Planning Time: 0.145 ms
Execution Time: 2.294 ms
Після декількох прогонів тривалість виконання стабілізувалася на рівні 2,3 мс. Ми отримали стійку економію приблизно в 60 %.
Також ми бачимо, що в цей раз база не стала використовувати сканування по бітовій карті, а застосувала «звичайне» індексне сканування. Чому?
Кореляція
Коли база аналізує таблицю, вона збирає всю можливу статистику. Одним з параметрів є кореляція:
Статистична кореляція між фізичним порядком рядків і логічним порядком значень в колонках. Якщо значення близьке до -1 або +1, індексне сканування по колонці вважається вигіднішим, ніж коли значення кореляції близько 0, оскільки знижується кількість випадкових звернень до диску.
Як пояснюється в офіційній документації, кореляція є мірою того, як «відсортовані» значення в конкретній колонці на диску.
Кореляція = 1.
Якщо кореляція дорівнює 1 або близько того, це означає, що сторінки зберігаються на диску приблизно в тому ж порядку, що й рядок таблиці. Таке зустрічається дуже часто. Наприклад, у автоінкрементних ID кореляція зазвичай близька до 1. У колонок з датами і тимчасовими мітками, які показують, коли був створений рядок, кореляція теж близька до 1.
Якщо кореляція дорівнює -1, сторінки відсортовані в зворотному порядку щодо колонок.
Кореляція ~ 0.
Якщо кореляція близька до 0, це означає, що значення в колонці не корелюють або майже не корелюють з порядком сторінок в таблиці.
Повернемося до sale_fact. Коли ми завантажили дані в таблицю без попереднього сортування, кореляції були такими:
db=# SELECT tablename, attname, correlation db-# FROM pg_stats db=# WHERE tablename = 'sale_fact'; tablename | attname | correlation -----------+----------+-------------- sale | id | 1 sale | username | -0.005344716 sale | sold_at | -0.011389783
У автоматично згенерованого ID колонки кореляція дорівнює 1. У колонки sold_at кореляція дуже низька: послідовні значення розкидані по всій таблиці.
Коли ми завантажили відсортовані дані в таблицю, вона вирахувала кореляції:
tablename | attname | correlation -----------+----------+---------------- sale_fact | id | 1 sale_fact | username | -0.00041992788 sale_fact | sold_at | 1
Тепер кореляція sold_at дорівнює 1.
Так чому база використовувала сканування по бітовій карті, коли кореляція була низькою, а при високій кореляції застосувала індексне сканування?
- Коли кореляція була рівна 1, база визначила, що рядки потрібного діапазону, ймовірно, будуть у послідовних сторінках. Тоді для читання декількох сторінок краще використовувати індексне сканування.
- Коли кореляція була близька до 0, база визначила, що рядки потрібного діапазону, ймовірно, будуть розкидані по всій таблиці. Тоді доцільно використовувати сканування по бітовій карті тих сторінок, в яких є потрібні рядки, і тільки потім витягати їх із застосуванням умови.
Коли наступного разу будете завантажувати дані в таблицю, подумайте про те, скільки інформації буде вибиратися, та сортування так, щоб індекси могли швидко сканувати діапазони.
Команда CLUSTER
Інший спосіб «сортування таблиці на диску» по конкретному індексу полягає у використанні команди CLUSTER.
Наприклад:
db=# TRUNCATE sale_fact; TRUNCATE TABLE -- Insert rows without sorting db=# INSERT INTO sale_fact (username, sold_at) db-# SELECT db-# md5(random()::text) AS username, db-# '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at db-# FROM db-# generate_series(1, 100000) INSERT 0 100000 db=# ANALYZE sale_fact; ANALYZE db=# SELECT tablename, attname, correlation db-# FROM pg_stats db-# WHERE tablename = 'sale_fact'; tablename | attname | correlation -----------+-----------+---------------- sale_fact | sold_at | -5.9702674e-05 sale_fact | id | 1 sale_fact | username | 0.010033822
Ми завантажили дані в таблицю у випадковому порядку, тому кореляція sold_at близька до нуля.
Щоб «перекомпонувати» таблицю sold_at, використовуємо команду CLUSTER для сортування таблиці на диску у відповідності з індексом sale_fact_sold_at_ix:
db=# CLUSTER sale_fact USING sale_fact_sold_at_ix; CLUSTER db=# ANALYZE sale_fact; ANALYZE db=# SELECT tablename, attname, correlation db-# FROM pg_stats db-# WHERE tablename = 'sale_fact'; tablename | attname | correlation -----------+----------+-------------- sale_fact | sold_at | 1 sale_fact | id | -0.002239401 sale_fact | username | 0.013389298
Після кластеризації таблиці кореляція sold_at стала дорівнювати 1.
Команда CLUSTER.
Що потрібно відзначити:
- Кластеризація таблиці по конкретній колонці може вплинути на кореляцію іншої колонки. Наприклад, погляньте на кореляцію ID після кластеризації за
sold_at. CLUSTER— важка і блокуюча операція, тому не застосовуйте її до живої таблиці.
З цих причин краще вставляти вже відсортовані дані і не покладатися на CLUSTER.
Колонки з високою кореляцією індексуйте з допомогою BRIN
Коли мова заходить про індекси, багато розробників думають про В-дерева. Але PostgreSQL пропонує і інші типи індексів, наприклад, BRIN:
BRIN спроектований для роботи з дуже великими таблицями, в яких деякі колонки володіють природною кореляцією зі своїм фізичним місцем розташування всередині таблиці
BRIN означає Block Range Index. Згідно документації, BRIN найкраще працює з колонками, які мають високу кореляцію. Як ми вже бачили в попередніх главах, автоінкрементні ID і тимчасові мітки природним чином корелюють з фізичною структурою таблиці, тому для них вигідніше використовувати BRIN.
При певних умовах BRIN може забезпечити краще «співвідношення ціни та якості» з точки зору розміру та продуктивності в порівнянні з аналогічним індексом типу B-дерево.
BRIN.
BRIN діапазон значень в межах кількох сусідніх сторінок в таблиці. Припустимо, у нас в колонці є такі значення, кожне в окремій сторінці:
1, 2, 3, 4, 5, 6, 7, 8, 9
BRIN працює з діапазонами сусідніх сторінок. Якщо задати три суміжні сторінки, індекс розділить таблицю на діапазони:
[1,2,3], [4,5,6], [7,8,9]
Для кожного діапазону BRIN зберігає мінімальне і максимальне значення:
[1-3], [4-6], [7-9]
Давайте з допомогою цього індексу пошукаємо значення 5:
- [1-3] — тут його точно немає.
- [4-6] — може бути тут.
- [7-9] — тут його точно немає.
З допомогою BRIN ми обмежили зону пошуку блоком 4-6.
Візьмемо інший приклад. Нехай значення у колонці будуть мати кореляцію близькою до нуля, тобто вони не відсортовані:
[2,9,5], [1,4,7], [3,8,6]
Індексування трьох сусідніх блоків дасть нам такі діапазони:
[2-9], [1-7], [3-8]
Пошукаємо значення 5:
- [2-9] — може бути тут.
- [1-7] — може бути тут.
- [3-8] — може бути тут.
У цьому випадку індекс взагалі не звужує область пошуку, тому він марний.
Розбираємося з pages_per_range
Кількість суміжних сторінок визначається параметром pages_per_range. Кількість сторінок в діапазоні впливає на розмір і точність BRIN:
- Велике значення
pages_per_rangeдасть маленький і менш точний індекс. - Маленьке значення
pages_per_rangeдасть великий і більш точний індекс.
За замовчуванням значення pages_per_range дорівнює 128.
BRIN з більш низьким значенням pages_per_range.
Для ілюстрації створимо BRIN з діапазонами по дві сторінки і пошукаємо значення 5:
- [1-2] — тут його точно немає.
- [3-4] — тут його точно немає.
- [5-6] — може бути тут.
- [7-8] — тут його точно немає.
- [9] — тут його точно немає.
При діапазоні в дві сторінки ми можемо обмежити зону пошуку блоками 5 і 6. Якщо діапазон буде на три сторінки, індекс обмежить зону пошуку блоками 4, 5 і 6.
Іншою відмінністю між двома індексами є те, що коли діапазон дорівнював трьом сторінкам, нам потрібно було зберігати три діапазони, а при двох сторінках у діапазоні ми отримуємо вже п'ять діапазонів і індекс збільшується.
Створюємо BRIN
Візьмемо таблицю sales_fact і створимо BRIN по колонці sold_at:
db=# CREATE INDEX sale_fact_sold_at_bix ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 128);
CREATE INDEX
За замовчуванням значення pages_per_range = 128.
Тепер виберемо період дат продажів:
db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on sale_fact (cost=13.11..1135.61 rows=4319 width=41)
Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Rows Removed by Index Recheck: 23130
Heap Blocks: lossy=256
-> Bitmap Index Scan on sale_fact_sold_at_bix (cost=0.00..12.03 rows=12500 width=0)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Execution Time: 8.877 ms
База з допомогою BRIN отримала період дат, але в цьому нічого цікавого...
Оптимізуємо pages_per_range
Згідно з планом виконання, база прибрала зі сторінок 23 130 рядків, які знайшла за допомогою індексу. Це може говорити про те, що заданий нами для індексу діапазон занадто великий для цього запиту. Створимо індекс з удвічі меншою кількістю сторінок в діапазоні:
db=# CREATE INDEX sale_fact_sold_at_bix64 ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 64);
CREATE INDEX
db=# EXPLAIN (ANALYZE)
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on sale_fact (cost=13.10..1048.10 rows=4319 width=41)
Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Rows Removed by Index Recheck: 9434
Heap Blocks: lossy=128
-> Bitmap Index Scan on sale_fact_sold_at_bix64 (cost=0.00..12.02 rows=6667 width=0)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Execution Time: 5.491 ms
При 64 сторінках в діапазоні база видалила менше рядків, знайдених з допомогою індексу — 9 434. Значить, їй довелося робити менше операцій введення-виведення, а запит виконався трохи швидше, за ~5,5 мс замість ~8,9.
Протестуємо індекс з різними значеннями pages_per_range:
| pages_per_range | Прибрано рядків при повторній перевірці індексу |
| 128 | 23 130 |
| 64 | 9 434 |
| 8 | 874 |
| 4 | 446 |
| 2 | 446 |
При зменшенні pages_per_range індекс стає точнішим, а зі знайдених з його допомогою сторінок видаляється менше рядків.
Зверніть увагу, що ми оптимізували абсолютно конкретний запит. Для ілюстрації годиться, але в реальному житті краще використовувати значення, які відповідають потребам більшості запитів.
Оцінка розміру індексу
Іншою важливою перевагою BRIN є його розмір. У попередніх розділах ми для поля sold_at створили індекс на основі В-дерева. Його розмір був 2 224 Кб. А розмір BRIN з параметром pages_per_range=128 всього 48 Кб: в 46 разів менше.
Schema | Name | Type | Owner | Table | Size
--------+-----------------------+-------+-------+-----------+-------
public | sale_fact_sold_at_bix | index | haki | sale_fact | 48 kB
public | sale_fact_sold_at_ix | index | haki | sale_fact | 2224 kB
На розмір BRIN також впливає pages_per_range. Приміром, BRIN з pages_per_range=2 важить 56 Кб, трохи більше 48 Кб.
Робіть індекси «невидимими»
В PostgreSQL є класна фіча transactional DDL. За роки роботи з Oracle я звик в кінці транзакцій використовувати такі DDL-команди, як CREATE, DROP та ALTER. Але в PostgreSQL виконувати DDL-команди можна усередині транзакції, а зміни будуть застосовані тільки після коміта транзакції.
Нещодавно я виявив, що використання транзакційного DDL може зробити індекси невидимими! Це корисно, коли хочеться побачити план виконання без індексів.
Наприклад, в таблиці sale_fact ми створили індекс по колонці sold_at. План виконання запиту на вибірку липневих продажів виглядає так:
db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.42..182.80 rows=4319 width=41)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))P
Щоб побачити, як виглядав би план, якщо б індексу sale_fact_sold_at_ix не було, можна помістити індекс всередину транзакції і негайно відкотитися:
db=# BEGIN;
BEGIN
db=# DROP INDEX sale_fact_sold_at_ix;
DROP INDEX
db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on sale_fact (cost=0.00..2435.00 rows=4319 width=41)
Filter: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
db=# ROLLBACK;
ROLLBACK
Спочатку почнемо транзакцію за допомогою BEGIN. Потім видалемо індекс і згенеруємо план виконання. Зверніть увагу, що план тепер використовує повне сканування таблиці, немов би індексу не існує. У цей момент транзакція все ще виконується, тому індекс поки не видалений. Для завершення транзакції без видалення індексу відкатемо її за допомогою команди ROLLBACK.
Перевіримо, що індекс ще існує:
db=# \di+ sale_fact_sold_at_ix
List of relations
Schema | Name | Type | Owner | Table | Size
--------+----------------------+-------+-------+-----------+---------
public | sale_fact_sold_at_ix | index | haki | sale_fact | 2224 kB
Інші бази, які не підтримують транзакційний DDL, дозволяють досягти мети інакше. Наприклад, Oracle дозволяє вам позначити індекс як невидимий і тоді оптимізатор буде його ігнорувати.
Увага: якщо видалити індекс всередині транзакції, це призведе до блокування конкурентних операцій SELECT, INSERT, UPDATE та DELETE в таблиці, поки транзакція буде активна. Обережно застосовуйте в тестових середовищах і уникайте застосування в експлуатаційних базах.
Не плануйте початок тривалих процесів на початок будь-якої години
Інвестори знають, що можуть відбуватися дивні події, коли ціна акцій досягає гарних круглих значень, наприклад, 10$, 100$, 1000$. Ось що про це пишуть:
[...] ціна активів може непередбачувано змінюватися, перетинаючи круглі значення кшталт $50 або $100 за акцію. Багато недосвідчених трейдерів люблять купувати або продавати активи, коли ціна досягає круглих чисел, тому що їм здається, що це справедливі ціни.
З цієї точки зору розробники не надто відрізняються від інвесторів. Коли їм потрібно запланувати тривалий процес, вони зазвичай обирають якусь годину.
Типове нічне навантаження на систему.
Це може призвести до сплесків навантаження в ці години. Так що якщо вам потрібно запланувати тривалий процес, то більше шансів, що в інший час система буде простоювати.
Також рекомендується застосовувати в розкладах випадкові затримки, щоб не запускатися кожен раз в один і той же час. Тоді навіть якщо на цей час запланована інша задача, це не буде великою проблемою. Якщо ви застосовуєте таймер systemd, можете скористатися опцією RandomizedDelaySec.
Висновок
У статті наведені поради з різним ступенем очевидності на основі мого досвіду. Деякі з них легко запровадити, деякі вимагають глибокого розуміння принципів роботи баз даних. Бази — це каркас більшості сучасних систем, так що час, витрачений на вивчення їх роботи, буде хорошим вкладом для будь-якого розробника!
Джерело ENG: hakibenita.com
Коментарі (3)