Пошук уроків, статей та іншого контенту
Розглянемо призначення PostgreSQL, його архітектуру, ключові можливості та типові сценарії використання.
PostgreSQL — це об’єктно-реляційна система керування базами даних (СУБД) із відкритим кодом.
Вона дає змогу:
зберігати структуровані дані;
виконувати SQL-запити;
додавати, змінювати й видаляти записи;
гарантувати цілісність даних;
працювати з кількома користувачами одночасно;
обробляти великі обсяги інформації.
PostgreSQL часто використовують як основну базу даних для вебзастосунків, мобільних застосунків, внутрішніх систем компаній і аналітичних сервісів.
PostgreSQL зберігає дані переважно в таблицях. Таблиця складається з:
рядків — окремих записів;
стовпців — властивостей записів;
типів даних — правил для значень у стовпцях.
Наприклад, таблиця users може містити користувачів:
| id | name | email | |---:|---|---| | 1 | Анна | anna@example.com | | 2 | Богдан | bohdan@example.com |
У реляційних базах таблиці можна пов’язувати між собою. Наприклад, таблиця orders може містити замовлення, пов’язані з користувачами через user_id.
Для взаємодії з PostgreSQL використовується мова
Сервер PostgreSQL — це запущений процес або група процесів, які приймають підключення та виконують запити клієнтів.
Клієнтом може бути:
командний рядок psql;
сервер вебзастосунку;
графічний клієнт;
інша програма, яка підтримує PostgreSQL.
У PostgreSQL термін кластер означає набір баз даних, якими керує один екземпляр сервера PostgreSQL.
Один сервер PostgreSQL може обслуговувати кілька баз даних. Вони використовують спільні системні процеси, але дані між базами логічно розділені.
База даних містить об’єкти певного застосунку або системи:
таблиці;
індекси;
подання;
функції;
типи даних;
обмеження.
Наприклад, в одному кластері можуть існувати бази даних shop і analytics.
Схема — це логічний простір імен усередині бази даних. Вона групує таблиці та інші об’єкти.
За замовчуванням PostgreSQL використовує схему public. Таблицю з цієї схеми можна записати так:
SELECT * FROM public.users;Схеми допомагають розділяти об’єкти різних частин застосунку або керувати доступом до них.
Типовий процес роботи має такий вигляд:
Застосунок підключається до сервера PostgreSQL.
Сервер перевіряє користувача та його права.
Застосунок надсилає SQL-запит.
PostgreSQL аналізує запит і вибирає план виконання.
Сервер читає або змінює дані.
Результат повертається застосунку.
PostgreSQL використовує клієнт-серверну архітектуру. Це означає, що програма, яка надсилає запит, і база даних можуть працювати як на одному комп’ютері, так і на різних серверах.
Для кожного клієнтського підключення PostgreSQL створює окремий серверний процес, який обробляє запити цього клієнта.
Окрім процесів підключень, PostgreSQL має фонові процеси. Вони виконують системні завдання, зокрема:
записують зміни на диск;
очищують застарілі версії рядків;
допомагають підтримувати стабільну роботу сервера.
Користувачу застосунку не потрібно керувати цими процесами вручну. Вони запускаються та працюють разом із сервером PostgreSQL.
Транзакція — це група операцій, яка виконується як єдине ціле.
Наприклад, переказ грошей може складатися з двох дій:
зменшити баланс одного рахунку;
збільшити баланс іншого рахунку.
Якщо друга дія не виконалася, першу також потрібно скасувати. PostgreSQL дає змогу зробити це за допомогою транзакції.
Основні команди для керування транзакцією:
BEGIN — почати транзакцію;
COMMIT — підтвердити зміни;
ROLLBACK — скасувати зміни.
PostgreSQL підтримує властивості ACID:
Atomicity — усі операції транзакції виконуються разом або не виконуються зовсім;
Consistency — дані залишаються узгодженими;
Isolation — паралельні транзакції не повинні некоректно впливати одна на одну;
Durability — підтверджені зміни зберігаються навіть після збою.
Нижче наведено SQL-скрипт, який створює таблицю товарів, додає записи та отримує товари дорожчі за 1000.
Скрипт можна виконати в базі даних PostgreSQL через psql або інший SQL-клієнт.
-- Створюємо таблицю товарів
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0),
in_stock boolean NOT NULL DEFAULT true
);
-- Додаємо товари
INSERT INTO products (name, price)
VALUES
('Механічна клавіатура', 2499.00),
('Миша', 899.00),
('USB-кабель', 299.00);
-- Отримуємо товари дорожчі за 1000
SELECT id, name, price
FROM products
WHERE price > 1000
ORDER BY price DESC;У цьому прикладі:
id — унікальний ідентифікатор товару;
GENERATED ALWAYS AS IDENTITY автоматично генерує значення id;
PRIMARY KEY визначає первинний ключ;
text використовується для тексту;
numeric(10, 2) зберігає число з двома цифрами після крапки;
NOT NULL забороняє пропускати значення;
CHECK (price >= 0) не дозволяє створити товар із від’ємною ціною;
DEFAULT true встановлює значення in_stock, якщо його не передали явно.
PostgreSQL підтримує стандартні SQL-операції:
SELECT — читання даних;
INSERT — додавання даних;
UPDATE — оновлення даних;
DELETE — видалення даних;
CREATE — створення об’єктів;
ALTER — зміна об’єктів.
Також PostgreSQL має власні розширення SQL, які допомагають розв’язувати практичні задачі.
Обмеження не дозволяють зберігати некоректні дані. Найпоширеніші з них:
PRIMARY KEY — унікально ідентифікує рядок;
FOREIGN KEY — створює зв’язок між таблицями;
UNIQUE — забороняє дублікати;
NOT NULL — забороняє пропущені значення;
CHECK — перевіряє умову для значення.
Наприклад, електронна адреса користувача може бути унікальною:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL
);Індекс — це структура даних, яка допомагає швидше знаходити рядки в таблиці.
Наприклад:
CREATE INDEX products_name_idx
ON products (name);Індекс може пришвидшити пошук за name, але він також займає місце та потребує оновлення під час зміни даних. Тому індекси потрібно створювати для справді потрібних запитів, а не для кожного стовпця.
PostgreSQL підтримує багато типів даних:
числа;
текст;
дати й час;
логічні значення;
масиви;
JSON і JSONB;
UUID;
геометричні типи;
бінарні дані.
Для кожного стовпця варто вибирати тип, який відповідає його призначенню. Наприклад, для ціни краще використовувати numeric, а не текстовий тип.
PostgreSQL може зберігати структуровані дані у форматі JSON. Тип jsonb зберігає JSON у форматі, зручному для обробки та пошуку.
Це корисно, коли структура частини даних може змінюватися або містити додаткові властивості:
CREATE TABLE events (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_type text NOT NULL,
payload jsonb NOT NULL
);
INSERT INTO events (event_type, payload)
VALUES (
'user_registered',
'{"user_id": 42, "source": "mobile"}'
);
SELECT payload->>'source' AS source
FROM events
WHERE event_type = 'user_registered';Реляційні таблиці залишаються основним способом зберігання даних, а jsonb доречно використовувати для окремих гнучких структур.
PostgreSQL має систему розширень, яка дає змогу додавати можливості до конкретної бази даних.
Розширення можуть додавати:
нові типи даних;
функції;
оператори;
індекси;
спеціалізовані можливості.
Набір доступних розширень залежить від встановлення та налаштування PostgreSQL.
PostgreSQL має систему ролей. Роль може бути:
користувачем, який підключається до бази;
групою для об’єднання дозволів;
власником об’єкта бази даних.
Дозволи визначають, які дії може виконувати роль. Наприклад, одна роль може лише читати таблицю, а інша — також додавати та змінювати записи.
Такий підхід допомагає:
не надавати застосунку зайвих прав;
розділяти доступ розробників і служб;
обмежувати доступ до окремих таблиць;
зменшувати наслідки помилок або компрометації облікових даних.
PostgreSQL часто використовують у таких випадках:
вебзастосунки з користувачами, товарами та замовленнями;
системи керування контентом;
фінансові й облікові системи;
CRM та ERP-системи;
сервіси з географічними даними;
платформи з аналітичними запитами;
системи, де важливі транзакції та цілісність даних;
застосунки, які поєднують реляційні дані з окремими JSON-структурами.
PostgreSQL особливо добре підходить для застосунків, де дані мають чіткі зв’язки, а помилки під час запису можуть бути критичними.
У типовій архітектурі вебзастосунку PostgreSQL є окремим шаром зберігання даних:
Клієнт надсилає HTTP-запит.
Сервер застосунку перевіряє запит і бізнес-правила.
Сервер застосунку надсилає SQL-запит до PostgreSQL.
PostgreSQL повертає результат.
Сервер застосунку формує відповідь клієнту.
Застосунок не повинен покладатися лише на перевірки у своєму коді. Важливі правила варто також закріплювати обмеженнями бази даних, наприклад NOT NULL, UNIQUE і FOREIGN KEY.
До основних переваг PostgreSQL належать:
відкритий код;
підтримка стандартного SQL;
транзакції та властивості ACID;
надійність і цілісність даних;
потужна система типів;
підтримка складних запитів;
розширюваність;
робота з реляційними та JSON-даними;
розвинена система ролей і дозволів;
придатність як для невеликих, так і для великих систем.
PostgreSQL не усуває необхідність правильно проєктувати базу даних. Розробник повинен:
продумувати структуру таблиць;
вибирати відповідні типи даних;
визначати зв’язки та обмеження;
аналізувати запити;
налаштовувати резервне копіювання;
контролювати права доступу.
Швидкість і надійність залежать не лише від самої СУБД, а й від схеми даних, SQL-запитів, індексів, конфігурації та способу використання.
PostgreSQL — це серверна СУБД. psql — лише один із клієнтів для підключення до неї.
Якщо дату, ціну або логічне значення зберігати як text, перевірки та сортування стають складнішими. Потрібно використовувати відповідні типи даних.
Перевірки лише в коді застосунку можуть бути недостатніми. Інший скрипт або сервіс може записати некоректні дані. Важливі правила варто закріплювати на рівні бази.
Індекс пришвидшує окремі запити, але збільшує витрати на зберігання та зміну даних. Його слід додавати з урахуванням реальних запитів.
Якщо кілька змін мають виконатися разом, їх потрібно об’єднати в транзакцію. Інакше помилка посередині може залишити дані в неповному стані.
PostgreSQL — об’єктно-реляційна СУБД із відкритим кодом.
Дані зазвичай зберігаються в таблицях і пов’язуються між собою.
PostgreSQL працює за клієнт-серверною архітектурою.
Кластер може містити кілька баз даних, а база даних — кілька схем.
Транзакції забезпечують надійне виконання пов’язаних операцій.
Обмеження допомагають зберігати цілісність даних.
Індекси пришвидшують пошук, але потребують додаткових ресурсів.
PostgreSQL підтримує SQL, JSONB, різні типи даних, ролі та розширення.
СУБД підходить для вебзастосунків, фінансових систем, CRM, аналітики та інших проєктів, де важливі надійність і узгодженість даних.