Блог

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

1. Таблица, содержащая одну строку для каждой записи журнала, содержащую идентификатор журнала, внешний ключ пользователя и содержимое журнала. Таким образом, у нас будет отдельная строка для каждого выполняемого действия.
2. Таблица, содержащая одну строку для активности каждого уникального пользователя(внешний ключ пользователя) и идентификатор журнала. У нас может быть столбец json типа для хранения журналов, связанных с каждым пользователем. Каждый раз, когда происходит действие, мы можем получить соответствующую запись в журнале и обновить ее столбец JSON, добавив в него новое действие.

Подход 1 обеспечивает простой способ добавления новых записей в журнал без необходимости обновлять старые. Но запрос такой таблицы для получения активности пользователя приведет к запросу всей таблицы.

Подход 2 усложняет добавление нового действия пользователя, так как нам пришлось бы извлекать и обновлять объект JSON, но запрос вернет только одну строку.

Мне нужна помощь, чтобы понять, может ли один подход быть явно выгодным по сравнению с другим.

Базы данных оптимизированы для хранения и извлечения небольших строк из большой таблицы. Так что идите к первому решению. Индексы делают соединения такими быстрыми.

Объединение всех данных пользователя в один объект JSON не сделает вас счастливыми: каждое обновление должно будет считывать, изменять и записывать весь JSON, что совсем не эффективно.

Если вы регистрируете много изменений с точки зрения свойств, я бы создал таблицу с:

log_id , user_id (fk) и log в формате json с каждой строкой как одним действием.

Это не будет проблемой с производительностью, если вы проиндексируете свою таблицу. В postgresql вы можете индексировать поля внутри столбца json.

Подход 2 будет обновляться медленнее после каждого обновления по мере увеличения размера столбца. Кроме того, запросы будут более сложными.

Также рассмотрите структуру ведения журнала, которая может анализировать полуструктурированные данные в столбцы базы данных, такие как Serilog.

В противном случае я бы также рекомендовал ваш вариант «1», по одной строке на журнал с индексом user_id, но предлагаю добавить метку времени в ваши столбцы, чтобы механизм запросов мог сортировать события в порядке, прежде чем анализировать сам json для метки времени:

 CREATE TABLE user_log
(
    log_id             bigint, -- (PRIMARY KEY),
    log_ts             timestamp NOT NULL DEFAULT(now()),
    user_id            int NOT NULL, --REFERENCES users(user_id),
    log_content        json
);
CREATE INDEX ON user_log(user_id);

SELECT user_id, log_ts, log_content => 'action' AS user_action FROM user_log WHERE user_id = ? ORDER BY log_ts;