re:re:actable
Урок 083 минуты чтения

Что такое схема базы данных

На этой странице
Кратко

У термина «схема» два значения. Логическая схема — структура данных целиком: таблицы, столбцы, типы, связи, ограничения, индексы (то, что рисуют на ER-диаграмме и описывают миграциями). Schema как объект PostgreSQL — пространство имён внутри базы (public, billing, analytics), которое группирует таблицы и другие объекты, позволяет иметь одинаковые имена в разных схемах и раздавать права на уровне группы объектов. Объект без явного префикса ищется по search_path (по умолчанию "$user", public). Иерархия в PostgreSQL: кластер → база данных → схема → таблица; запрос не может напрямую соединить таблицы из разных баз, но из разных схем — может.

Вопрос

Что такое схема базы данных и как используются схемы в PostgreSQL?

Логическая схема

users 1 ──── N orders 1 ──── N order_items N ──── 1 products
                                                      N
                                                      │
                                                      1
                                                  categories

Хорошая логическая схема: понятные имена, явные связи через FK, ограничения целостности, индексы под реальные запросы, документация (COMMENT ON).

Schema как пространство имён

CREATE SCHEMA billing;
 
CREATE TABLE billing.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES public.users (id),
  amount numeric(12, 2) NOT NULL
);
 
SELECT u.email, i.amount
FROM public.users u
JOIN billing.invoices i ON i.user_id = u.id;

search_path

SHOW search_path;                       -- "$user", public
SET search_path TO billing, public;     -- теперь invoices найдётся без префикса

В функциях с SECURITY DEFINER search_path фиксируют явно (SET search_path = ...), иначе пользователь может подменить объекты в своей схеме.

Зачем делить на схемы

ПрименениеПример
Модули доменаbilling, catalog, auth
Права доступаGRANT USAGE ON SCHEMA analytics TO analyst
Служебные объектыaudit, staging для импорта
Multi-tenancy (schema per tenant)tenant_42.orders — изоляция, но сложнее миграции при тысячах арендаторов
РасширенияCREATE EXTENSION pg_trgm SCHEMA extensions

Права

REVOKE CREATE ON SCHEMA public FROM PUBLIC;           -- в PG 15+ так по умолчанию
GRANT USAGE ON SCHEMA analytics TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO analyst;

Подробнее — роли, GRANT и RLS.

Изучение схемы

-- psql
\dn            -- схемы
\dt billing.*  -- таблицы схемы
\d+ orders     -- структура таблицы
 
-- стандартный каталог
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';

Частые ошибки

  • Хранить всё в public в большом проекте.
  • Полагаться на search_path в миграциях и функциях — лучше полные имена.
  • Schema per tenant для тысяч клиентов — миграции становятся очень долгими.
  • Путать базу данных и схему (между базами нельзя делать JOIN без расширений).

Что важно помнить

  • Логическая схема — структура данных, schema в PostgreSQL — пространство имён.
  • Кластер → база → схема → объект.
  • Схемы помогают организовать модули и права.

Мини-шпаргалка

  • CREATE SCHEMA name.
  • schema.table.
  • SET search_path TO ....
  • GRANT USAGE ON SCHEMA ....
  • information_schema, \d.

Документация

PostgreSQL: Schemas.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам