Чи безкоштовна ця стаття?

Так. Усі статті на CoderSlingo, включно з цією, доступні безкоштовно без реєстрації.

Коли опубліковано цю статтю?

Цю статтю опубліковано у 2026 році.

PostgreSQL JSONB & Розширений словник запитів для розробників сервера

Q: Про що ця стаття "PostgreSQL JSONB & Розширений словник запитів для розробників сервера"?

Освоєння операторів JSONB PostgreSQL, індексів GIN, функцій вікна, CTE, бічних з’ єднань і словника планів запитів — зі справжніми прикладами SQL і фразами розробників.

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

JSONB проти JSON

JSON vs JSONB зберігання

PostgreSQL підтримує два типи зберігання даних JSON:

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

** Використовуйте jsonb майже для всього. ** json корисно лише у тому випадку, якщо вам потрібно зберегти точно початковий текст (з пробілами і дублікатами клавіш).

«Відповідно до цієї теорії, jsonb завжди використовується, якщо у вас немає конкретної причини для json. jsonb індексується і набагато швидше для читання.»

Оператори JSONB

`->` and `->>`

-> повертає JSON об’єкт/елемент масиву за ключем (результат jsonb )
->> повертає елемент об’єкта JSON як text

SELECT data -> 'address' FROM users;          -- returns jsonb
SELECT data ->> 'name' FROM users;            -- returns text

“Використовуйте ->>, коли вам потрібно порівняти значення як рядок. Використовуйте ->, коли ви ланцюгаєте далі у вкладений JSON»

`#>` and `#>>`

#> витягує вкладене значення на шляху (результат jsonb )
#>> витягує вкладене значення на шляху як text

SELECT data #> '{address, city}' FROM users;   -- returns jsonb
SELECT data #>> '{address, city}' FROM users;  -- returns text

`@>` — Contains

@> перевіряє, чи ліве значення jsonb ** містить ** правильне значення. Цей оператор прискорюється за допомогою індексів GIN.

SELECT * FROM products WHERE attributes @> '{"colour": "blue"}';

” @> — це ваш найкращий друг для фільтрування jsonb. Упевніться, що у вас є індекс GIN для цієї колонки, або кожен запит буде робити послідовне сканування

« < @ » — містить

<@ є оберненою до @> — вона перевіряє, чи ліве значення міститься в правому.

«? » — ключ існує

? перевіряє чи існує текстовий ключ в об’єкті jsonb.

SELECT * FROM events WHERE payload ? 'errorCode';

Індексування JSONB

Індекс GIN на JSONB

Індекс **GIN (Generalised Inverted Index) ** на стовпчику jsonb створює індекс для всіх ключів і значень всередині JSON документів. Це значно прискорює @>, ?, і ?| запити.

CREATE INDEX idx_attributes_gin ON products USING GIN (attributes);

“Без GIN-індексу, кожен @> запит робить повне послідовне сканування. Як тільки ви додаєте індекс GIN, ці запити падають з секунд до мілісекунд»

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

tsvector

** tsvector ** є впорядкованим списком нормалізованих лексем (слів), що представляють документ. Це тип даних, які ви індексуєте для повнотекстового пошуку.

SELECT to_tsvector('english', 'The quick brown fox') AS lexemes;

tsquery

** tsquery ** є розбірним текстовим пошуком — один або більше лексем, з’єднаних & (І), | (АБО), або ! (НЕ).

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'postgresql & index');

“Зберігати попередньо обчислену tsvector колонку і індексувати її з GIN. Виклик to_tsvector на кожному запиту без індексу є повільним»

Функції вікон

Функції Window виконують обчислення у рядках, пов’ язаних з поточним рядком, без згортання їх у один рядок виводу, як це робить GROUP BY.

ROW_NUMBER()

** ROW_NUMBER() ** призначає послідовне ціле число для кожного рядка у розділі.

SELECT user_id, order_date, 
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_num
FROM orders;

«Використовуйте ROW_NUMBER(), щоб знайти найновіший порядок за користувачем — розділ за user_id, порядок за order_date, зменшуючись, і фільтр за order_num = 1»

Функції RANK () і DENSE_ RANK ()

** RANK() ** присвоює однаковий ранг рівним рядкам, але пропускає наступні рядки. ** DENSE_RANK() ** не пропускає.

Функції LAG () і LEAD ()

** LAG() ** отримує значення стовпчика з попереднього рядка. ** LEAD() ** отримує значення з наступного рядка. Корисно для обчислення різниці між послідовними рядками.

SELECT date, revenue,
       LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY date) AS revenue_change
FROM daily_stats;

“LAG і LEAD дозволяють порівнювати рядок з його сусідами без самоз’єднання. Чистіше»

CTE і підзапити

CTE (Common Table Expression) — спільний таблицьний вираз

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

WITH active_users AS (
  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT count(*) FROM orders WHERE user_id IN (SELECT id FROM active_users);

Список мов Sub-Saharan Africa

У старих версіях Postgres (до 12), CTE були оптимізаційними огорожами — планувальник запитів не міг вставляти в них предикати. З Postgres 12, типово, нерекурсивні CTE вставляються у рядок. Підзапити часто є більш ефективними для простих фільтрів; CTE відрізняються читабельністю і рекурсією.

“У Postgres 14, цей CTE вбудований і планувальник оптимізує його так само, як підзапит. Використовуйте той, який читає більш чітко»

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

Бочне з’єднання

** Бокове з’ єднання ** надає змогу використовувати кожен рядок лівої частини у підзапиті правої частини. Це як цикл for в SQL — для кожного рядка, оцінити підзапит.

SELECT u.id, recent.title
FROM users u
JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3
) recent ON TRUE;

«Бічні з’єднання ідеальні для запитів «верхня N за групою». Набагато чистіше, ніж корелювати підзапит»

Функція set-return

Функція повернення множини є функцією, яка може повертати декілька рядків. generate_series(), unnest(), і jsonb_array_elements() є поширеними прикладами.

«Використовуйте jsonb_array_elements() для розгніздування масиву JSONB на рядки — тоді ви можете приєднувати, фільтрувати і агрегувати кожен елемент, якби це був рядок таблиці»

Запитайте про плани

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

Зрозуміти, як Postgres отримує доступ до ваших даних, є ключовим для роботи з швидкодією.

** Послідовне сканування (Seq Scan) ** — читання кожного рядка у таблиці. Придатний для малих таблиць або коли збігається велика частина рядків. Це дорого на великих столах.
** Сканування індексу ** — слідує за індексом, щоб знайти відповідні рядки один за одним. Добре, коли кілька рядків збігаються. Випадковий введення/ виведення може бути повільним, якщо багато рядків розкидані по багатьох сторінках.
** Сканування бітів ** — читання індексу для створення бітів відповідних розташувань сторінок, а потім отримання цих сторінок у порядку. Цей варіант є оптимальним, якщо багато рядків збігаються, але повне послідовне сканування було б марним.

Запит робить послідовне сканування на 50 мільйонів рядків таблиці — додайте індекс на status і планувальник переключиться на бітове сканування

Частковий індекс

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

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

«Ми додали частковий індекс для status = 'active', тому що 95% запитів цікавляться тільки активними записами. Індекс є однією десятою частиною розміру повного індексу.»

Звичайні фрази PostgreSQL

Phrase	Meaning
”Run EXPLAIN ANALYSE”	Show the actual query execution plan and timings
”The planner chose a seq scan”	PostgreSQL decided to read the whole table
”Add a GIN index”	Index a `jsonb` or `tsvector` column for fast containment queries
”The CTE is a fence”	In older Postgres, the query planner can’t optimise across a CTE boundary
”Partition by user”	Group window function calculations per user
”Unnest the array”	Expand a PostgreSQL array or jsonb array into individual rows
”Write a partial index”	Index only the rows that match a specific condition