Пошук уроків, статей та іншого контенту
Розглянемо сервер, бази даних, схеми, таблиці та інші об’єкти, з яких складається PostgreSQL-проєкт.
PostgreSQL-проєкт складається з кількох рівнів:
Сервер PostgreSQL
└── Кластер баз даних
├── База даних app_db
│ ├── Схема public
│ │ ├── Таблиця users
│ │ ├── Таблиця orders
│ │ ├── Представлення active_users
│ │ └── Функції та інші об’єкти
│ └── Схема billing
└── База даних test_dbВажливо розрізняти:
сервер — процес або набір процесів PostgreSQL, які приймають з’єднання;
кластер — каталог даних, яким керує один екземпляр PostgreSQL;
база даних — окремий простір даних усередині кластера;
схема — простір імен усередині бази даних;
таблиця — об’єкт для зберігання рядків і стовпців;
інші об’єкти — індекси, обмеження, послідовності, представлення, функції, тригери та типи.
PostgreSQL працює як серверна система керування базами даних. Клієнтський застосунок підключається до сервера за такими параметрами:
адреса сервера, наприклад localhost;
порт, за замовчуванням 5432;
ім’я бази даних;
ім’я користувача;
пароль або інший спосіб автентифікації.
Один сервер PostgreSQL може обслуговувати багато клієнтів і багато баз даних одночасно.
У PostgreSQL термін кластер має спеціальне значення. Це не набір серверів, як у деяких інших системах, а каталог на диску, у якому зберігаються:
усі бази даних цього екземпляра PostgreSQL;
системні каталоги;
конфігурація;
журнали транзакцій;
інформація про ролі та права доступу.
Один запущений екземпляр PostgreSQL зазвичай працює з одним каталогом даних і обслуговує всі бази даних у цьому кластері.
Водночас на одному фізичному або віртуальному сервері можна запустити кілька незалежних екземплярів PostgreSQL. Вони матимуть різні каталоги даних і, як правило, різні порти.
База даних — це логічно відокремлений контейнер усередині кластера.
Наприклад, в одному кластері можуть бути бази:
app_db — база робочого застосунку;
test_db — база для тестів;
analytics_db — база для аналітики.
Під час підключення клієнт завжди вибирає конкретну базу даних. Запит до таблиці в іншій базі безпосередньо виконати не можна:
SELECT *
FROM another_database.public.users;Такий синтаксис не працює в PostgreSQL. Об’єкти з різних баз даних не адресуються через чотирикомпонентні імена.
Для обміну даними між базами застосовують окремі механізми, наприклад зовнішні таблиці або прикладний код. Це відрізняється від роботи зі схемами: об’єкти різних схем в одній базі даних можна використовувати в одному запиті.
За допомогою psql можна підключитися до бази даних так:
psql -h localhost -p 5432 -U app_user -d app_dbПараметри означають:
-h — адресу сервера;
-p — порт;
-U — роль користувача;
-d — базу даних.
Усередині psql поточне підключення можна перевірити командою:
\conninfoКоманди, які починаються з \, є командами самого клієнта psql, а не SQL-командами PostgreSQL.
Схема — це простір імен усередині бази даних. Вона групує таблиці та інші об’єкти й дає змогу використовувати однакові назви в різних частинах проєкту.
Наприклад, у базі app_db можна створити такі схеми:
auth — користувачі та автентифікація;
billing — платежі;
reporting — об’єкти для звітів.
app_db
├── auth.users
├── billing.invoices
└── reporting.monthly_salesПовне ім’я об’єкта складається з назви схеми та назви об’єкта:
SELECT *
FROM auth.users;publicУ новій базі даних зазвичай існує схема public. Якщо назву схеми не вказати, PostgreSQL шукає об’єкт у схемах із параметра search_path.
Наприклад:
SELECT *
FROM users;За типової конфігурації PostgreSQL спочатку шукає таблицю users у схемі "$user", а потім у public.
Явне зазначення схеми робить код зрозумілішим:
SELECT *
FROM public.users;CREATE SCHEMA billing;Після цього таблицю можна створити в цій схемі:
CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);Назви об’єктів у різних схемах можуть збігатися:
CREATE TABLE auth.users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);
CREATE TABLE reporting.users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
registered_at timestamptz NOT NULL
);У цьому випадку auth.users і reporting.users — різні таблиці.
Таблиця зберігає дані у вигляді рядків і стовпців.
Приклад таблиці користувачів:
CREATE TABLE app_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);Тут:
id — ідентифікатор рядка;
email — адреса електронної пошти;
display_name — ім’я, яке бачать користувачі;
is_active — ознака активності;
created_at — дата створення.
Якщо схема не вказана, PostgreSQL створює таблицю в першій доступній схемі з search_path, зазвичай у public.
Краще явно вказувати схему:
CREATE TABLE public.app_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);Таблицю можна записати у форматі:
schema.tableНаприклад:
SELECT id, email
FROM auth.users;Якщо назва містить великі літери, пробіли або зарезервоване слово, її потрібно взяти в подвійні лапки. Зазвичай у PostgreSQL використовують імена в нижньому регістрі, щоб не створювати зайвих проблем із лапками.
Обмеження описують правила, яким мають відповідати дані.
PRIMARY KEYПервинний ключ однозначно ідентифікує рядок:
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);У таблиці може бути лише один первинний ключ. Він може складатися з одного або кількох стовпців.
NOT NULLЗабороняє зберігати NULL у стовпці:
CREATE TABLE categories (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);UNIQUEЗабезпечує унікальність значень:
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);Два рядки не можуть мати однакове значення email.
CHECKПеревіряє довільну умову:
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL CHECK (amount > 0)
);FOREIGN KEYОписує зв’язок між таблицями:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES app_users(id),
total numeric(12, 2) NOT NULL CHECK (total >= 0)
);Значення orders.user_id має посилатися на існуючий рядок у app_users.id.
Індекс — це окремий об’єкт, який допомагає PostgreSQL швидше знаходити рядки. Індекс створюють для стовпців, які часто використовуються у фільтрах, сортуванні або з’єднанні таблиць.
CREATE INDEX orders_user_id_idx
ON orders (user_id);Унікальне обмеження часто автоматично створює унікальний індекс. Наприклад, для такого стовпця окремий індекс зазвичай не потрібен:
email text NOT NULL UNIQUEІндекси пришвидшують читання, але збільшують витрати на запис і займають місце. Тому не варто створювати індекс для кожного стовпця без аналізу запитів.
Для генерації числових ідентифікаторів PostgreSQL може використовувати стовпці ідентичності:
CREATE TABLE tasks (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);Якщо виконати вставку без id, PostgreSQL згенерує його автоматично:
INSERT INTO tasks (title)
VALUES ('Підготувати звіт')
RETURNING id;За сучасного синтаксису GENERATED ... AS IDENTITY керування генерацією значень пов’язане з таблицею. У старих схемах також можна зустріти тип serial, який використовує окрему послідовність.
Послідовність є окремим об’єктом PostgreSQL і може існувати незалежно від таблиці:
CREATE SEQUENCE invoice_number_seq;Представлення, або VIEW, — це збережений SQL-запит, до якого можна звертатися майже як до таблиці.
CREATE VIEW active_users AS
SELECT id, email, display_name
FROM app_users
WHERE is_active = true;Тепер можна виконати:
SELECT *
FROM active_users;Звичайне представлення не зберігає окрему копію результату. Під час звернення PostgreSQL виконує запит, на якому воно побудоване.
Повне ім’я представлення також містить схему:
CREATE VIEW reporting.active_users AS
SELECT id, email
FROM auth.users
WHERE is_active = true;Функції зберігають логіку на стороні бази даних і можуть повертати значення або набір рядків. Наприклад:
CREATE FUNCTION active_user_count()
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
SELECT count(*)
FROM app_users
WHERE is_active = true;
$$;Виклик функції:
SELECT active_user_count();Функції належать схемі. Якщо схему не вказати під час створення, використовується схема з search_path.
Функції, представлення й таблиці є різними типами об’єктів. Їхні назви можуть перетинатися лише в межах правил, які PostgreSQL застосовує до різних просторів імен, тому для однозначності в прикладному коді бажано використовувати зрозуміле іменування.
Тригер автоматично запускає функцію у відповідь на подію в таблиці, наприклад INSERT, UPDATE або DELETE.
Спочатку створюють тригерну функцію:
CREATE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;Потім під’єднують її до таблиці:
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER documents_set_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();Після зміни рядка значення updated_at автоматично оновлюватиметься.
PostgreSQL має вбудовані типи даних, зокрема:
integer, bigint — цілі числа;
numeric — точні десяткові числа;
text — текст;
boolean — логічні значення;
date — дата;
timestamp — дата й час без часового поясу;
timestamptz — дата й час із часовим поясом;
jsonb — двійково оптимізовані JSON-дані;
масиви та інші спеціалізовані типи.
Тип стовпця є частиною структури таблиці. Він обмежує допустимі значення та впливає на операції, які можна виконувати над ними.
Наприклад:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_name text NOT NULL,
payload jsonb NOT NULL DEFAULT '{}'::jsonb,
happened_at timestamptz NOT NULL DEFAULT now()
);У PostgreSQL користувачі представлені об’єктами типу роль. Роль може:
підключатися до бази даних;
володіти об’єктами;
створювати об’єкти;
отримувати дозволи на читання або зміну даних.
Приклад створення ролі:
CREATE ROLE app_user
LOGIN
PASSWORD 'change-this-password';Створення бази даних із власником:
CREATE DATABASE app_db OWNER app_user;Після підключення до app_db власник або адміністратор може надати доступ до схеми й таблиць:
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_user;Права на базу даних, схему, таблицю та інші об’єкти — це різні рівні доступу. Наявність права підключитися до бази не означає автоматичного доступу до всіх її таблиць.
Нижче наведено послідовність SQL-команд для невеликого застосунку. Команди виконуються після підключення до потрібної бази даних.
-- Створюємо схему для облікових записів.
CREATE SCHEMA IF NOT EXISTS auth;
-- Створюємо таблицю користувачів.
CREATE TABLE auth.users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Створюємо таблицю замовлень і зв'язок із користувачем.
CREATE TABLE auth.orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES auth.users(id),
total numeric(12, 2) NOT NULL CHECK (total >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
-- Додаємо індекс для пошуку замовлень конкретного користувача.
CREATE INDEX orders_user_id_idx
ON auth.orders (user_id);
-- Створюємо представлення активних користувачів.
CREATE VIEW auth.active_users AS
SELECT id, email, display_name
FROM auth.users
WHERE is_active = true;
-- Додаємо тестові дані.
INSERT INTO auth.users (email, display_name)
VALUES ('anna@example.com', 'Анна');
INSERT INTO auth.orders (user_id, total)
SELECT id, 1250.50
FROM auth.users
WHERE email = 'anna@example.com';
-- Читаємо дані через представлення та таблицю.
SELECT
u.email,
o.total,
o.created_at
FROM auth.active_users AS u
JOIN auth.orders AS o ON o.user_id = u.id;У цьому прикладі:
база даних є зовнішнім контейнером для всіх об’єктів;
схема auth групує об’єкти функціональної частини застосунку;
users і orders є таблицями;
первинні та зовнішні ключі описують зв’язки;
orders_user_id_idx є індексом;
active_users є представленням;
ідентифікатори створюються автоматично.
У psql для перегляду об’єктів використовують метакоманди:
\lПоказує список баз даних.
\dnПоказує список схем.
\dt auth.*Показує таблиці в схемі auth.
\d auth.usersПоказує структуру таблиці, її стовпці, індекси та обмеження.
\dv auth.*Показує представлення.
\df auth.*Показує функції у схемі auth.
Також структуру можна досліджувати через системний каталог PostgreSQL або стандартні подання information_schema. Але для щоденної роботи в psql метакоманди часто є найшвидшим способом отримати огляд.
Сервер може містити кластер, кластер — кілька баз даних, а база даних — кілька схем. Таблиця належить не серверу безпосередньо, а конкретній схемі конкретної бази даних.
Запит:
SELECT *
FROM users;залежить від search_path. Якщо в кількох схемах є таблиця users, можна випадково звернутися не до тієї таблиці.
У важливих запитах краще писати:
SELECT *
FROM auth.users;Схеми існують усередині однієї бази даних. Вони допомагають організувати об’єкти та права доступу, але не замінюють окремі бази даних.
Індекс не є безкоштовним: він займає місце та збільшує вартість операцій запису. Його слід створювати з огляду на реальні запити й плани виконання.
Перевірки у frontend або backend не замінюють обмеження бази даних. Якщо правило є важливим для цілісності даних, його варто закріпити через NOT NULL, UNIQUE, CHECK або FOREIGN KEY.
PostgreSQL-сервер обслуговує клієнтські підключення та бази даних.
Кластер PostgreSQL — це каталог даних, який містить один або кілька баз даних.
База даних є логічно окремим контейнером і не дає змоги напряму звертатися до таблиць іншої бази.
Схема групує об’єкти всередині бази даних і формує простір імен.
Таблиці зберігають дані, а обмеження описують правила їхньої цілісності.
Індекси пришвидшують пошук, але збільшують витрати на зміну даних.
Представлення зберігають запити як повторно використовувані об’єкти.
Функції та тригери дають змогу виконувати логіку на стороні PostgreSQL.
Ролі та дозволи визначають, хто може підключатися до бази й працювати з її об’єктами.
Явне зазначення схеми, наприклад auth.users, робить SQL передбачуванішим.