PostgreSQL Vocabulary: 30 Terms Every Developer Should Know

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

---

Як Postgres керує даними

MVCC (Multi-Version Concurrency Control) — багатоверсійний контроль одночасності

** MVCC ** — це спосіб, яким Postgres обробляє одночасне читання і запис без блокування. Замість блокування читачів під час запису рядка, Postgres зберігає декілька версій кожного рядка. Читачі бачать послідовний знімок без очікування на авторів.

«Postgres використовує MVCC, тому читачі ніколи не блокують авторів, а автори ніколи не блокують читачів» «Причина, чому VACUUM важлива, в тому, що MVCC залишає старі версії рядків позаду — вони накопичуються, якщо їх не очищати»

WAL (запис наперед)

** WAL ** — це журнал всіх змін, внесених до бази даних, записаний до того, як було змінено сторінки з даними. Це є основою відновлення після аварії і реплікації. Якщо Postgres завершить роботу, він повторить WAL, щоб відновити послідовний стан.

«Стримінг реплікації працює за допомогою відправки сегментів WAL з первинного до репліки.» «WAL архів також є тим, як ми робимо відновлення в точці часу»

Вакуум і автовакуум

** VACUUM ** — це процес, який відновлює місце, зайняте мертвими версіями рядків (створеними під час оновлення і вилучення за допомогою MVCC). Без нього таблиці розширюються, а швидкодія зменшується. ** Autovacuum ** — фонова служба, яка автоматично запускає VACUUM.

«Таблиця має значне розширення — VACUUM ANALYZE не працював деякий час. Перевірте параметри автопочистки» Запустити VACUUM ANALYZE вручну після великого масового видалення, щоб відновити місце і негайно оновити статистику

Transaction

** транзакція ** є одиницею роботи, яка або повністю зафіксована, або повністю відновлена. Postgres сумісний з ACID — транзакції є атомарними, послідовними, ізольованими і тривалими.

«Вкладіть пакет в транзакцію — якщо якийсь рядок зазнає невдачі, ми хочемо повернути всю партію»

---

Indexes

Індекс Б-дерево

Індекс ** B- tree ** є типовим типом індексу. Він підтримує запити рівності і діапазону (=, <, >, BETWEEN, LIKE 'prefix%'). Це правильний вибір для більшості стовпчиків.

«Запит робить послідовне сканування на мільйон-рядковій таблиці — додайте індекс B-дерева на стовпці created_at»

Індекс Гірша

** GiST (Generalised Search Tree) ** підтримує складні типи даних і нетипові оператори. Він використовується для повнотекстового пошуку, геометричних даних, діапазонів IP ( inet ) і багато іншого.

«Ми використовуємо індекс GiST для пошуку IP-діапазону — B-дерево не підтримує запиту на обмеження діапазону»

Індекс Гіннеса

** GIN (Generalised Inverted Index) ** оптимізовано для індексування складних значень, де кожен елемент може з’ являтися у багатьох рядках — масивах, jsonb і векторах повнотекстового пошуку. GIN швидше запитує, але повільніше оновлює, ніж GiST.

«Додати індекс GIN на стовпці масиву tags, щоб ми могли ефективно запитуємо рядки, що містять певний тег»

Брін Індекс

** BRIN (Block Range Index) ** зберігає резюме (мін./ макс. значення) для кожного діапазону фізичних блоків диска. Він надзвичайно малий і підходить для великих таблиць, де дані природно корелюють з фізичним порядком (наприклад, дані часових рядів записуються послідовно).

Для таблиці журналу аудиту — мільярди рядків, впорядкованих за часом — індекс BRIN є набагато більш просторово ефективним, ніж B-дерево

---

Виконання запиту

Пояснення і аналіз

** EXPLAIN ** показує план запиту — як Postgres * має намір * виконати запит. ** EXPLAIN ANALYZE ** фактично * запускає * запит і показує справжні часи виконання і кількість рядків поряд з оцінками.

Запит повільний — запустіть EXPLAIN ANALYZE і пошукайте великі розбіжності між оціненим і фактичним числом рядків “План показує послідовне сканування на великому столі. Нам потрібен індекс»

Планувальник запитів

** Планувальник запитів ** (також відомий як * оптимізатор *) обирає найкращий план виконання запиту на основі статистики таблиць, доступності індексів і оцінювання вартості.

«Планувальник вибирає послідовне сканування, навіть якщо індекс існує — статистика може бути застарілим. Запустити ANALYZE»

Послідовне сканування (Seq Scan)

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

«Вивід EXPLAIN показує Seq Scan — саме тому запит займає 10 секунд. Нам потрібен індекс»

Індексне сканування проти Сканування індексу бітмап

• ** Сканування індексу ** — слідує за індексом, щоб отримати рядки по одному за раз. Швидкий для малих наборів результатів.
• ** Сканування індексу растрової зображення ** — збирає всі відповідні розташування рядків з індексу, а потім отримує фактичні рядки у порядку стека. Ефективний для середніх наборів результатів або умов з декількома індексами у поєднанні з AND/ OR.

Statistics

** Статистика ** Postgres — це дані щодо розподілу значень у кожному стовпчику. Планувальник запитів використовує їх для оцінювання кількості рядків, які відповідатимуть умові. Вони оновлюються за допомогою ANALYZE або AUTOVACUUM.

«Планувальник оцінив 100 рядків, але отримав 50 000 — статистика застаріла. Запустити ANALYZE на таблиці.”

---

Розширені функції SQL

CTE (Common Table Expression / WITH)

CTE визначає названий підзапит у верхній частині інструкції за допомогою WITH. Це робить складні запити більш зрозумілими. У Postgres 12+ типово CTE вставляються у рядок планувальником (якщо ви не додали MATERIALIZED ).

«Використовуйте CTE, щоб розбити запит на логічні кроки — це набагато легше читати, ніж вкладені підзапити»

Функція вікна

Функція ** window ** виконує обчислення на наборі рядків, пов’ язаних з поточним рядком, без згортання їх у єдину групу. Загальні функції вікна: ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(PARTITION BY ...).

«Використовуйте ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) для отримання останнього запису на користувача» «Вінкові функції є однією з найпотужніших можливостей Postgres — вивчіть їх і ви написаєте набагато менше підзапитів»

jsonb

** jsonb ** — двійковий тип стовпчика JSON Postgres. Він зберігає JSON в обробленому, двійковому форматі — швидше запитувати, ніж json (текст). Ви можете індексувати поля jsonb за допомогою індексів GIN і запитувати їх за допомогою операторів -> і ->>.

«Ми зберігаємо метадані продукції як jsonb, тому нам не потрібно додавати колонку для кожного можливого атрибута» Додати GIN індекс на metadata jsonb колонці, щоб ви могли ефективно шукати в JSON

---

Операції та адміністрування

pg_stat_activity

** pg_stat_activity ** — це системний перегляд, у якому показано поточні запущені запиту, їх стан (активний, неактивний, неактивний у транзакції) і час їх запущеності.

«Запустити SELECT * FROM pg_stat_activity WHERE state = 'active', щоб побачити, що зараз працює» «Є сеанси, що застрягли в idle in transaction — вони можуть триматися за замками. Вбийте їх, якщо вони були там занадто довго»

Об’ єднання з’ єднань

Postgres має надлишок на з’єднання (зазвичай 5-10 МБ пам’яті). Connection pooling повторно використовує з’єднання через багато потоків програми. PgBouncer є стандартним інструментом.

“Ми досягаємо межі підключення під навантаженням. Налаштувати PgBouncer в транзакційному режимі для об’єднання з’єднань.” «З PgBouncer, ми пішли з 500 прямих з’єднань до 50 — база даних набагато щасливіша»

Replication

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

«Ми маємо дві потокові репліки для масштабування читання і одну як гарячу резервну копію для відключення» «Ми використовували логічну реплікацію для міграції до нової версії Postgres з нульовим часом простою.»

psql

** psql ** — це інтерактивний клієнт командного рядка для PostgreSQL. Це стандартний засіб для з’ єднання з базами даних, виконання запитів і перевірки схем.

«Поєднайтеся з psql -h host -U user -d database і запустіть \dt, щоб переглянути всі таблиці» «Використовуйте \x в psql, щоб переключитися на розширений режим виводу — набагато легше читати широкі рядки»

Extension

** Розширення ** Postgres додає функціональність до бази даних. Відомі розширення: pg_stat_statements (статистика запитів), PostGIS (геопросторові), uuid-ossp (генерація UUID), pgcrypto (шифрування).

«Install pg_stat_statements — it’s essential for identifying slow queries.» (англійською) «Ми використовуємо розширення PostGIS для геопросторових запитів — знаходження всіх користувачів в межах 10 км від місця»

Tablespace

** tablespace ** — це розташування на диску, де Postgres зберігає файли даних. Ви можете скористатися таблицями для розміщення певних таблиць або індексів на швидкому або більшому носії.

«Пересунути велику таблицю архіву в tablespace на дешевше повільне зберігання — це рідко запитується.»

Консультативний замок

** Порадницький блокування ** — це механізм блокування на рівні програми, який надає Postgres. На відміну від блокувань рядків, блокування за порадою не прив’ язано до жодних певних даних — їх значення визначає ваша програма.

«Ми використовуємо Postgres консультативний замок, щоб запобігти двом працівникам обробляти одне і те ж завдання одночасно»

---

Як використовувати цю функцію в прикладі

В ході дослідження:

“Запустити EXPLAIN ANALYZE на повільному запиту і поділитись виведенням. Я хочу побачити, чи планувальник вибирає правильний індекс.»

** В перегляді коду: **

“Цей запит не використовує індекс на user_id через виклик функції LOWER(). Створити функціональний індекс замість цього»

** У обговоренні архітектури: **

«Ми потребуємо масштабування читання — давайте додамо потокову репліку і маршрутизуємо завдання з читанням до неї»

** Під час пояснення поведінки Postgres: **

“MVCC означає, що читання не блокує запис, але створює мертві кортежі. AUTOVACUUM очищає їх — якщо він не може триматися в ногу, ви побачите набряк столу»

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