Фото Кевіна Ку на Unsplash
Якщо ви пишете програмне забезпечення та використовуєте PostgreSQL, вітаємо — ви обрали еквівалент витриманого віскі серед баз даних. Це надійно, потужно і не боїться, якщо ви трохи перевантажите його. Але якщо ви не використовуєте правильні інструменти та методи, то це як пити це віскі з пластикового стаканчика. Тож давайте це виправимо.
Ось 10 порад, які зроблять ваш досвід роботи з PostgreSQL більш плавним, швидким і менш нервовим.
1. Індекси — ваші найкращі друзі (поки вони не перестануть бути ними)
Індекси прискорюють запити. Поки не перестануть. Використовуйте B-tree індекси для пошуку рівності, GIN індекси для повнотекстового пошуку та BRIN індекси для величезних таблиць. Але не переборщіть з індексами — надмірна кількість може уповільнити записи. Завжди використовуйте EXPLAIN ANALYZE перед тим, як щось зробити (як у стосунках).
Приклад:
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = '[email protected]';
2. Використовуйте пулінг з’єднань (якщо не любите повільні додатки)
Кожне нове підключення до бази даних — це як відкриття нової вкладки у браузері — ви можете це робити, але з часом все сповільнюється. Використовуйте PgBouncer або вбудований пулінг з’єднань PostgreSQL, щоб усе працювало швидко.
Приклад:
Налаштуйте PgBouncer з pool_mode = transaction, щоб забезпечити ефективне управління з’єднаннями.
3. Каскадні видалення — це пастка
Зовнішні ключі з ON DELETE CASCADE звучать чудово, поки ви випадково не зітрете пів своєї виробничої бази даних. Натомість обробляйте видалення вручну, використовуючи м’яке видалення або фонові завдання, якщо ви дійсно знаєте, що робите.
Приклад:
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP;
UPDATE orders SET active=false, deleted_at = NOW() WHERE user_id = 123;
-- ви також можете встановити deleted_at за замовчуванням в об'єкті Date у вашому коді
4. Знайте свій JSONB (але не зловживайте ним)
JSONB в PostgreSQL — це магія: гнучкий, швидкий і чудово підходить для напівструктурованих даних. Але якщо ви пишете запити, що схожі на археологічні розкопки (data->’user’->’profile’->’settings’->’theme’), краще просто використовувати відповідні стовпці. І, будь ласка, не зберігайте масиви в стовпцях JSONB.
Уникайте зберігання масивів у JSONB
Хоча JSONB підтримує масиви, зберігання великих масивів у стовпцях JSONB може призвести до неефективних запитів. Оскільки PostgreSQL має спеціалізований тип масив, часто краще зберігати масиви окремо і використовувати JOIN замість того, щоб вкладати їх у JSONB.
Приклад (погана практика — зберігання масиву в JSONB):
-- ПОГАНА ПРАКТИКА! НЕ КОПІЮЙТЕ ЦЕ
CREATE TABLE users (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO users (data) VALUES ('{"tags": ["admin", "editor", "viewer"]}');
SELECT data->'tags' FROM users;
Приклад (краща практика — використання стовпця масиву):
CREATE TABLE users (
id SERIAL PRIMARY KEY,
tags TEXT[]
);
INSERT INTO users (tags) VALUES (ARRAY['admin', 'editor', 'viewer']);
SELECT * FROM users WHERE 'admin' = ANY(tags);
Використання типу масиву PostgreSQL дозволяє індексувати та оптимізувати продуктивність запитів, що робить фільтрацію і пошук значно швидшими, ніж витягування елементів масиву з JSONB.
Приклад:
SELECT data->>'name' FROM users WHERE data->'address'->>'city' = 'New York';
5. VACUUM і ANALYZE, як ніби ваше життя залежить від цього
PostgreSQL не прибирає за собою — воно просто залишає "мертві кортежі", як ваші брудні тарілки, що залишаються після вас.
Запустіть VACUUM і ANALYZE регулярно, щоб тримати ваші запити швидкими, а місце на диску під контролем.
Приклад:
VACUUM ANALYZE users;
6. Використовуйте CTE (Загальні Табличні Вирази) для Зрозумілості, Але Остерігайтесь Проблем з Продуктивністю
Загальні табличні вирази (WITH запити) роблять ваш SQL більш читабельним, але за замовчуванням вони матеріалізують результати, що може уповільнити роботу. Використовуйте WITH ... MATERIALIZED для одноразових обчислень та WITH ... NOT MATERIALIZED, якщо потрібно, щоб планувальник оптимізував їх.
Приклад:
WITH user_orders AS (
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id
)
SELECT * FROM user_orders WHERE order_count > 5;
7. Не Покладайтесь на Автоматичні Ідентифікатори для Масштабування
SERIAL та BIGSERIAL підходять для невеликих додатків, але в масштабі вони стають вузьким місцем. Якщо ви працюєте з розподіленими системами, розгляньте використання UUID або ulid для унікальних, сортувальних ідентифікаторів, які не стануть вам на заваді.
Приклад:
CREATE TABLE users (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT
);
8. Розділяйте Великі Таблиці, Поки Вони Вас Не Побили
Маєте таблицю з сотнями мільйонів рядків? Ви, мабуть, спостерігаєте, як ваші запити повзуть. Використовуйте партиціонування таблиць, щоб розділити їх на менші, більш керовані частини і насолоджуйтесь прискоренням.
Партиціонування за діапазоном
Якщо у вас є набір даних на основі часу, партиціонування за діапазоном часто є найкращим підходом, розбиваючи таблицю на сегменти за датами.
Приклад:
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
Партиціонування за хешем
Якщо ваша таблиця не має природного поділу на основі дати, партиціонування за хешем буде корисним для рівномірного розподілу рядків по партиціях.
Приклад:
CREATE TABLE users (
id SERIAL,
name TEXT NOT NULL
) PARTITION BY HASH (id);
CREATE TABLE users_part1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_part2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_part3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_part4 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);
За допомогою партиціонування за хешем, PostgreSQL автоматично розподіляє рядки по партиціях на основі хешу стовпця id, забезпечуючи рівномірний розподіл навантаження та покращену продуктивність запитів.
9. Логи — Ваше Кришталеве Сферо
Якщо щось працює повільно, не гадайте — перевірте логи. Увімкніть pgstatstatements для аналізу повільних запитів і активуйте logminduration_statement, щоб виявляти проблеми з продуктивністю до того, як вони стануть справжнім відключенням.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
Резервні Копії: Тому що «Ой» Буває
Якщо у вас немає автоматизованих резервних копій, ви живете на межі (і не в хорошому сенсі). Використовуйте pg_dump для логічних резервних копій, pg_basebackup для фізичних резервних копій і WAL архівування для відновлення точок у часі. Бо одного дня вам це буде потрібно.
pg_dump -U postgres -F c -f backup.dump mydatabase
10. Бонус Порада: COUNT(*) і Його Прихована Складність
Використовувати COUNT(*) здається простим, але на великих таблицях це може бути неймовірно повільно. PostgreSQL оцінює COUNT(*) за допомогою послідовного сканування або індексного сканування, залежно від того, чи можна використовувати індекс.
Приклад:
SELECT COUNT(*) FROM orders;
Як PostgreSQL Обчислює COUNT(*)
- Послідовне сканування: Якщо немає індексу, що допомагає, PostgreSQL читає кожен рядок.
2.
Індексне Сканування Лише за Індексом: Якщо індекс покриває запит, PostgreSQL може підрахувати кількість рядків за допомогою індексу, пропускаючи непотрібні перевірки видимості.
Оцінка Кількості Рядків
Замість точного підрахунку ви можете отримати швидку оцінку за допомогою pg_class:
Приклад:
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';
Для великих таблиць це може бути в порядки швидше ніж повний COUNT(*).
Підсумки
PostgreSQL є однією з найкращих баз даних, але як і будь-який потужний інструмент, його легко використовувати неправильно. Дотримуючись цих 10+1 порад, ви уникнете більшості типових помилок. І якщо коли-небудь відчуєте себе загубленими, пам'ятайте: EXPLAIN ANALYZE — ваш друг, а Google завжди нагадуватиме вам те, що ви забули.
Тепер вперед і пишіть міцний SQL.
Якщо Вам Сподобався Цей Пост, Поставте Лайк і Поділіться!
Якщо ви знайшли цей пост корисним, поставте йому лайк (або п'ять) і поділіться з тим другом, який постійно каже вам, що MongoDB — краща база даних. Вони, ймовірно, потребують просвітлення, а PostgreSQL завжди радий прийняти їх назад, коли вони зрозуміють, що відповідність ACID дійсно важлива. 🚀
Postgresql!
Перекладено з: 10 Things Every Software Engineer Using PostgreSQL Should Know