Текстовые поля с конечным доменом: разложить на подстать или нет?

Question

Новая база данных предназначена для хранения данных журнала (серии) веб-серверов. Структура записей журнала была преобразована в эту "наивную" общую схему:

CREATE TABLE log (
    matchcode SERIAL PRIMARY KEY,
    stamp TIMESTAMP WITH TIME ZONE,
    ip INET,
    bytes NUMERIC,
    vhost TEXT,
    path TEXT,
    user_agent TEXT,
    -- and so on
);

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

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

CREATE TABLE vhost ( ident SERIAL PRIMARY KEY, vhost TEXT NOT NULL UNIQUE );
CREATE TABLE path ( ident SERIAL PRIMARY KEY, path TEXT NOT NULL UNIQUE );
CREATE TABLE user_agent ( ident SERIAL PRIMARY KEY, user_agent TEXT NOT NULL UNIQUE );

CREATE TABLE log (
    matchcode SERIAL PRIMARY KEY,
    stamp TIMESTAMP WITH TIME ZONE,
    ip INET,
    bytes NUMERIC,
    vhost INTEGER REFERENCES vhost ( ident ) ,
    path INTEGER REFERENCES path ( ident ),
    user_agent INTEGER REFERENCES user_agent ( ident ),
    -- and so on
);

Я попробовал оба подхода сейчас. Как и следовало ожидать, второй гораздо меньше, по три раза. Однако его запрос становится значительно медленнее из-за всех задействованных объединений. Разница примерно на порядок.

Из того, что я понимаю, таблица должна быть достаточно нормальной в обоих случаях. На более позднем этапе проекта могут быть добавлены дополнительные атрибуты к различным текстовым значениям (например, дополнительная информация о каждом vhost и т. Д.).

Практические соображения очевидны, это в основном компромисс между пространством и временем. В конечном счете, что считается лучшей практикой в ​​таком случае? Есть ли другие, возможно, более теоретические последствия для такого сценария, о которых я мог бы знать?

Answer 1

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

Есть несколько способов взглянуть на эту проблему. Независимо от того, как вы на это смотрите, уместным является нечеткое слово.

Ограничения

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

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

Сжатие данных с идентификационными номерами

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

---

В вашем случае, который связан с автоматически генерируемыми файлами журналов, я предпочитаю хранить их по мере их поступления с устройства (веб-сервер, датчик потока и т. Д.), Если нет веской причины не делать этого.

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

Возьмите то, что дает устройство, если нет веской причины не делать этого.