На днях мне пришлось столкнуться со следующей проблемой при проектировании базы данных: имеется таблица сущности, к которой необходимо иметь таблицу расширения с некими количественными характеристиками. В моем конкретном примере это был ресурс в рамках проектного управления и его количественная оценка. При этом количественная оценка делилась на типы: плановая, фактическая и прогнозная.
При рассмотрении подобной задачи в лоб было два решения. Первое решение заключалось в создании широкой таблицы, которая была бы связана с ресурсом отношением один-к-одному. Данное решение казалось оптимальным с точки зрения простоты реализации, но при необходимости настройки расширения данного решения, все оказывалось не так просто. В этом случае пришлось бы изменять таблицу, добавляя новые поля, например, для поля данные бухгалтерского учета.
С другой стороны, была и вторая альтернатива решения, а именно, создать узкой таблицы, которая бы содержала тип значения и само значение. Данное решение представляет возможность простого масштабирования решения, так как не требует переработки структуры базы данных. В противовес этому мы получали более сложные запросы, по крайней мере по написанию.
В процессе размышления я задумался на тему производительности данного решения. Попытки найти ответа на просторах интернета не убедили меня в правоте того или иного способа. И я решил провести эксперимент с производительностью.
Эксперимент производился на двух СУБД:Oracle и Postgres. Выбор был продиктован тем, что разрабатываемая система должна была работать именно под управлением этих двух СУБД.
Были созданы структуры данных, как описано выше и сгенерирован контрольный пример, с 1 млн. строк в основной таблице ресурсов. Следовательно в широкой таблице получился тот же миллион записей, а в узкой исходя из трех типов — 3 миллиона.
Далее попытался провести анализ в меру своих знаний и возможностей (времени и ресурсов). При построение планов запросов было замечено, что при использовании select к узкой таблице происходит больший объем чтения, чем при работе с широкой. В Postgres был и замечено значительное увеличение работы процессора при использовании узкой таблицы. Так же при выборке данных значительно быстрее работало получение всех записей их широкой таблице, а из узкой заметно притормаживало, когда курсор не был еще закеширован в памяти.
Так же при работе с узкой таблице возник еще один момент. Для нее был создан суррогатный ключ id, так как в противном случае надо было бы делать составной первичный ключ с типом и ссылкой на основную таблицу, да и работать без уникального простого числового ключа я уже отвык. Так вот, в данному случае массовое добавление записей было значительно медленнее из-за необходимости генерации этого ключа, что в широкой таблице не требуется.
В итоге этих экспериментов я пришел для себя к следующим выводам:
1. Узкая таблица. Плюс: Узкая таблица данных позволяет просто масштабировать системы, добавляя новые типы значений буквально на лету. Минут: запросы получаются более громоздкие, так как приходится несколько раз подключать одну и туже таблицу, либо применять разворот данных (pivot и его аналоги). На больших объемах данных будет возникать интенсивный ввод-вывод на дисковой подсистеме, что не самое лучшее решение.
2. Широкая таблица. Плюс: Показывает хорошие показатели быстродействия как при выборке данных, так и при добавлении значительных объемов информации. Минус: Сложно расширить логику, так как требуется перестройка структуры базы данных.
В итоге я принял для себя решение, что узкая таблица предназначена прежде всего для работы в транзакционных системах, где главное возможность адаптации к требованиям бизнеса, а вот широкие — к системам анализа и отчетности, где как раз важна скорость наполнения данными и скорость их извлечения.
О таких выводах я читал в некоторых статьях, но теперь я сам убедился в этом решении.
P.S. Если у кого-то есть другие мысли, то готов обсудить.