Как оптимально выстроить хранение данных?

Question

[Смотритель]

Приветствую.
Не добившись от монго вменяемого поведения, было принято решение все-таки мигрировать на постгре.
Суть проекта - сбор информации с разных источников, частично парсинг, частично сами пользователи, частично с помощью человеческих ресурсов. Объем "записей" на начальном уровне ожидается ~50млн. В каждой группе данных от 5 до 10 полей.

Отсюда вопрос, как лучше выстроить структуру хранения данных, пока в голове только два варианта.

1. Все хранить в одной большой таблице вида:

ID / уникальное_поле / группа_полей_1 / группа_полей_2 / группа_полей_3

Соответственно все выборки делать по конкретным значениям полей, все данные в "исходном виде" и получаются проходом только по одной таблице, но большой (количество полей более 30) и с большой частью NULL-данных.

2. Разбить на 4 таблицы. главная из которых по сути только как полный список + доп.поля для наиболее активных выборок, остальные данные по группам:

- ID / уникальное_поле / данные_для_выборок
- ID / уникальное_поле / группа_полей_1
- ID / уникальное_поле / группа_полей_2
- ID / уникальное_поле / группа_полей_3

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

Например для:

- уникальное_поле = aaaa
- группа_1_поле_A = xxx
- группа_2_поле_A = yyy
- группа_3 - нет записи вообще

и по итогу выдать результат нужно по значению
- уникальное_поле = aaa, группа_2_поле_A = yyy

3. Мутированный вариант из №2, когда в главной таблице вести "итоговые рассчитанные поля", которые изменять при изменении данных в "дочерних таблицах":

- ID / уникальное_поле / итоговая_группа_полей
- ID / уникальное_поле / группа_полей_1
- ID / уникальное_поле / группа_полей_2
- ID / уникальное_поле / группа_полей_3

Большая получается "главная таблица" с полями с итоговыми данными, но их будет опять же не больше 15 против более 30 в первом варианте. И для обычных выборок не требуется обращаться к другим таблицам и производить склейку/выбор.

ЗЫ. Уточнение ID - это поле идентификатора записи для первичного ключа. Уникальный индекс по сути сквозной единый, он же ключ связи - строка, в число не переделать

Comments

[sim3x]

Что хранится?
Зачем в БД?

[Смотритель]

sim3x,

Что хранится - данные всякие в цифирках и строчках (имена/пароли/явки)
Зачем в БД - ну наверное, потому что по файлам делать выборки сложнее (тем более разными сервисами обработки на разных железяках по сети)

Не в обиду, но вопрос странный... может уточните, что имеете ввиду?

[sim3x]

Артем,
Я имею ввиду, что на ваш вопрос без деталей никто не ответит
Деталей вы не написали совсем

Вопрос требуется конкретизировать

[Смотритель]

sim3x, деталей и не предоставлю. Но не понимаю, каких таких данных не хватает?
Ну давайте отталкиваться от ситуации:

вариант 1:
- одна таблица: id / uid / str1 / str2 / str3 / in1 / int2 / int 3

вариант 2:
- четыре таблицы:
- id / uid
- id / uid / str1 / int1
- id / uid / str2 / int2
- id / uid / str3 / int3

результат должен быть по правилу, выводить поле "справа" (т.е. если заполнено 3, то 1 и 2 неважно), т.е. каждый запрос формирует итог по результату из всех 4-х

вариант 3:
- четыре таблицы с реализацией логики:
- id / uid / str0 / int0
- id / uid / str1 / int1
- id / uid / str2 / int2
- id / uid / str3 / int3

выборка только из главной, а данные там обновляем при изменении записей в "дочерних"

Вопрос соответственно звучит так - какой из вариантов будет наиболее оптимальным как с точки зрения выборок (основная часть) - которые должны быть быстрыми, так и с точки зрения изменения (их меньше, но тоже хватает) - которые тоже желательно делать не по 10-15-20 секунд

[fogree]

Мне кажется, очевидно, что в одной таблице будет работать быстрее (по крайней мере выборки), но больше места на диске занимать.
Попробуйте сгенерировать данные для обоих вариантов и сами посмо́трите как будет работать. И не забудьте сюда написать результаты, возможно кому-то тоже будет интересно.

[Смотритель]

fogree, да там не только место, там еще и с индексами получается "застрелиться"...

[fogree]

Артем, и индексов в PostgreSQL много разных. Я бы не занимался теорией, сгенерируйте 50 млн максимально приближенных к вашим данных и потестите. Я бы посмотрел на результаты :)

Я недавно с Redis похожий тест провёл и когда он отъел 40 ГБ памяти, я понял, что для этой задачи нужно что-то другое. А провёл бы я этот тест чуть раньше, сэкономил бы себе пару дней.

Answer 1

[Смотритель]

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

В принципе получилось оптимально и по работе с данными, и по ресурсоемкости... Вопрос считаю закрытым =)