Проєктування схеми PostgreSQL для вебзастосунків

Коротка відповідь

Почніть схему з власності, інваріантів і потрібних запитів. Обов’язковість, унікальність, зв’язки й прості перевірки задайте в БД, не лише UI. Індекси вибирайте за фільтрами й сортуванням. Схема є контрактом цілісності й доступу, не лише ORM-моделями.

Умовний tracker ілюструє користувачів, сесії з часом і список останніх записів. Опис WatersportGPS говорить про активності пристроїв, але не розкриває БД. Схема навчальна, не реалізація проєкту.

Практичні кроки реалізації

Запишіть інваріанти до колонок

Визначте існування без власника, публічні ID та видалення. Foreign key не допускає висячих зв’язків. Cascade застосовуйте для свідомого видалення залежних; інакше обмежте чи змоделюйте зберігання. Зручний cascade може знищити потрібні записи.

Виберіть типи за змістом

Timestamptz підходить миттєвостям між часовими поясами. Він не зберігає початкову назву зони; для локальних розкладів потрібне окреме поле. Date — для календаря. Null визначайте свідомо: незавершена сесія без кінця доречна, без власника зазвичай ні.

Індексуйте запит, не всі поля

Для списку користувача B-tree із user_id, created_at та додатковим ID може підтримати фільтр і порядок. Перевірте planner на даних. Індекси коштують запису й місця. Foreign key не створює автоматично індекс referencing-колонки; додайте за потреби join чи змін батька.

Плануйте сумісні міграції

Розділіть додавання, backfill, перемикання читання й запису та посилення constraints. Оцініть locks і вартість. Успіх порожньої БД мало говорить про production й старі інстанси. Визначте rollback, а за ризику втрати нових даних обирайте forward repair.

Приклад реалізації

SQL показано в bash для ізольованої БД через psql. Він створює умовну схему з нуля, не є production-міграцією.

psql "$DATABASE_URL" <<'SQL'
CREATE TABLE app_user (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE activity_session (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES app_user(id) ON DELETE RESTRICT,
  started_at timestamptz NOT NULL,
  ended_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (ended_at IS NULL OR ended_at >= started_at)
);
CREATE INDEX activity_session_recent
  ON activity_session (user_id, created_at DESC, id DESC);
SQL

Check перевіряє час рядка, NOT NULL — відомий початок. Зв’язок не дозволяє читати чужі сесії; потрібні scoped-запити чи свідома row security. Пов’язані атомарні записи виконуйте транзакцією.

Компроміси та альтернативи

Нормалізуйте сталі зв’язки для цілісності й запитів. JSONB підходить змінним атрибутам, але не скасовує валідації чи індексів. Integer identity і UUID мають власні вимоги; вибирайте за контрактом. Приватні поля не мають входити в типові API-проєкції.

Поширені помилки

• Інваріанти лише в ORM-hook.
• Timestamp без значення зони.
• Cascade без рішення зберігання.
• Припущення автоматичного FK-індексу.
• Індекси без planner і вартості запису.

Контрольний список перед випуском

• ☐ Власник і ID явні.
• ☐ Null, unique, checks і FK відповідають домену.
• ☐ Час розрізняє миті й календар.
• ☐ Планери мають реальні дані.
• ☐ Locks і сумісність переглянуто.
• ☐ Доступ окремий від цілісності.

Поширені запитання

Чи всі поля мають бути JSONB?

Ні. Стабільний зміст, зв’язки й обмеження потребують колонок. JSONB корисний для реальної мінливості з контрактом.

Чи foreign key перевіряє права?

Він перевіряє зв’язок рядків, не права автентифікованого користувача. Scoped-авторизація потрібна кожному читанню й запису.

Висновок

Спершу інваріанти, потім запити й свідомі міграції. Схема запобігає хибним зв’язкам, а вхід, доступ і сумісність API лишаються явними обов’язками застосунку.

Первинні джерела

• PostgreSQL: обмеження
• PostgreSQL: багатоколонкові індекси
• PostgreSQL: часові типи

Пов’язані посібники та приклади

• Тематичний розділ
• Проєктування REST API для продуктових команд
• GraphQL чи REST: вибір за навантаженням
• Як створити розширення Chrome з Manifest V3
• Доменні приклади продуктів портфоліо