#postgresql #database-design #query-performance
#postgresql #database-design #запрос-производительность
Вопрос:
Название не очень конкретное, поэтому я уточню.
Я работаю над системой баз данных, в которой пользователи могут добавлять данные в базу данных postgres через разбавленный API.
Пока что все данные пользователя скомпилированы в одну таблицу, структурированную примерно так:
CREATE TABLE UserData (
userId int NOT NULL,
dataId int NOT NULL PRIMARY KEY,
key varchar(255) NOT NULL,
data json not NOT NULL,
);
Однако я думаю, что может быть более эффективным (и более быстрым запросом) вместо этого предоставить каждому идентификатору пользователя собственную таблицу:
CREATE TABLE UserData_{userId} (
dataId int NOT NULL PRIMARY KEY,
key varchar(255) NOT NULL,
data json not NOT NULL,
);
CREATE TABLE UserData_{anotherUserId} ();
etc...
Однако я обеспокоен тем, что это приведет к засорению базы данных.
Каковы плюсы и минусы для каждой из них? При каких требованиях к нагрузке / скорости каждая из них будет хорошо работать? И какой из них, по вашему мнению, будет лучше для сценария с высокой нагрузкой и высокой скоростью?
Комментарии:
1. В общем, разумно иметь много «идентичных» таблиц. Это проблема обслуживания и т. Д.
2. @RickJames Я предполагаю, что в вашем комментарии есть опечатка, и вы имели в виду «это неразумно».
3. Я бы сохранил таблицы без разделения. Я бы не стал разделять данные таким образом, если для этого нет веской причины. Выполнение такого разделения может стать кошмаром. Это довольно подвержено ошибкам и дорого для кода и отладки.
4. TL / TR: сохраните свой текущий дизайн
Ответ №1:
То, что вы предлагаете, по сути, является разделением, поэтому я предлагаю прочитать документы об этом. В основном это выгодно, когда каждая ваша операция охватывает большую часть одного раздела (т. Е. Выбирает Все данные для одного пользователя или Удаляет все данные для одного пользователя).
Однако в большинстве случаев использования лучше использовать одну правильно проиндексированную таблицу. Это гораздо более простая структура, и она может быть очень производительной. Если все ваши запросы предназначены для одного пользователя, то вы захотите, чтобы все индексы начинались со userId
столбца, и postgres будет использовать их для эффективного доступа только к соответствующим строкам. И если придет день, когда вы захотите запросить данные у нескольких пользователей, сделать это будет намного проще.
Однако я советую вам не верить мне на слово. Создайте обе структуры, сгенерируйте поддельные данные, чтобы заполнить их, и посмотрите, как они себя ведут!
Комментарии:
1. Правильно. Разбиение на разделы не ускорит сканирование индекса, только последовательное сканирование, ограниченное несколькими разделами.
Ответ №2:
Рассмотрим:
- В итоге у вас может получиться x таблиц, если у вас есть по одной на пользователя. Сколько «пользователей» вы ожидаете?
json
Данные не связаны и могут расти по мере роста вашего решения / приложения. Как вы будете обрабатывать недостающие ключи / значения?Users
Таблица будет расти по горизонтали (больше столбцов), где вы всегда должны стремиться расти по вертикали (больше строк)
Лучшим решением было бы хранить ваши данные в таблицах, связанных с таблицей user_id. ie. a «keys», которая содержит ключ, date_added, активный и внешний ключ (user_id)
Это также решит проблему сохранения ваших данных как a json
, которые, в вашем примере, будет сложно поддерживать. Скорее откройте этот json в таблицу, где вы сможете воспользоваться индексами и кластеризацией.
Если вы ссылаетесь на свои user_id
в отдельных таблицах как на внешний ключ, вы можете разбить или кластеризировать эти таблицы по этому ключу, чтобы значительно увеличить скорость и компенсировать рост. Это означает, что у вас есть одна таблица для users
(id, name, active, created_at, …) и множество таблиц, связанных с этим пользователем, например. subscriptions
(id, user_id, …), items
(id, user_id, …), things
(id, user_id, …)