Помогите с выбором архитектуры PostgreSQL

Question

У меня есть таблица Users, с данными о пользователях(id, имя и прочее) и таблица chats(id чата, ...) я хочу используя тип данных списки в PGSQL в chats хранить эти самые списки id пользователей и админов. Но есть еще вариант создать таблицу ChatUsers с полями Chat, User, isAdmin, и с помощью ее реализовывать связь "многие к многим"(Chat, User, isAdmin это Forgein внешние ключи). Помогите выбрать архитектуру

Answer 1

Преимущество варианта "многие ко многим" в том, что можно легко узнать в каких чатах состоит юзер и в каких является админом.Тогда как, по спискам придётся искать перебором.

Минус "многие ко многим" может быть в том что для получения списка юзеров чата потребуется дополнительный JOIN, но реляционные базы оптимизированы как-раз для таких вещей, поэтому проблема не большая.

Answer 2

Не храни внешние ключи в списках!

Скажу сразу - второй вариант с отдельной таблицей ChatUsers однозначно лучше. Я сам наступал на эти грабли, и вот почему:

Хранение списков ID в PostgreSQL (через массивы) кажется удобным поначалу, но потом создаёт кучу проблем:

Производительность запросов падает на больших объемах. Попробуй найти "все чаты, где пользователь Х является админом" - с массивами это превратится в медленный перебор.
Индексы. На отдельную таблицу с связями можно навесить нормальные индексы, а вот эффективно индексировать элементы внутри массива... удачи с этим.
Целостность данных. PostgreSQL не сможет автоматически поддерживать ссылочную целостность, если ID пользователя хранится внутри массива. А с внешними ключами в отдельной таблице всё работает как часы.
Нормализация. Таблица ChatUsers полностью соответствует нормализованной схеме. И это хорошо.

Вот пример SQL для создания правильной структуры:

   CREATE TABLE Users (
       user_id SERIAL PRIMARY KEY,
       username VARCHAR(100) NOT NULL,
       -- другие поля пользователя
   );
   
   CREATE TABLE Chats (
       chat_id SERIAL PRIMARY KEY,
       chat_name VARCHAR(100) NOT NULL,
       -- другие поля чата
   );
   
   CREATE TABLE ChatUsers (
       chat_id INTEGER REFERENCES Chats(chat_id) ON DELETE CASCADE,
       user_id INTEGER REFERENCES Users(user_id) ON DELETE CASCADE,
       is_admin BOOLEAN DEFAULT FALSE,
       PRIMARY KEY (chat_id, user_id)
   );
   
   -- Индексы для быстрого поиска
   CREATE INDEX idx_chatusers_user_id ON ChatUsers(user_id);

И тебе не придётся писать кривые запросы с array_contains и прочую магию. Просто:

   -- Найти всех пользователей чата
   SELECT u.* FROM Users u
   JOIN ChatUsers cu ON u.user_id = cu.user_id
   WHERE cu.chat_id = 123;
   
   -- Найти всех админов чата
   SELECT u.* FROM Users u
   JOIN ChatUsers cu ON u.user_id = cu.user_id
   WHERE cu.chat_id = 123 AND cu.is_admin = TRUE;

БЛОГ НА HUSL

Помогите с выбором архитектуры PostgreSQL

Ответы (2 шт):

Не храни внешние ключи в списках!