Блог

Главная - Вопросы по программированию - Хранение повторяющихся данных в виде столбца в Postgres?

Хранение повторяющихся данных в виде столбца в Postgres?

#postgresql

#postgresql

Вопрос:

В каком-то проекте базы данных у меня есть users таблица, которая каким-то образом имеет вычисленное значение avg_service_rating . И есть еще одна вызываемая таблица services со всеми службами, связанными с пользователем, и рейтингами для этой службы. Есть ли простой в вычислительном отношении способ, с помощью которого я могу поддерживать рейтинг, не обновляя его каждый раз, когда выполняется вставка в таблицу служб? avg_service_rating Возможно, как столбец generate, но вместо этого с вызовом функции? Любой прямой совет или ссылка на ресурсы также будут высоко оценены!

 CREATE TABLE users (
    username VARCHAR PRIMARY KEY,
    avg_service_ratings NUMERIC -- is it possible to store some function call for this column?,
    ...
);

CREATE TABLE service (
    username VARCHAR NOT NULL REFERENCE users (username);
    service_date DATE NOT NULL,
    rating INTEGER,
    PRIMARY KEY (username, service_date),
);

Комментарии:

1. Рассматривали ли вы возможность использования представления?

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

Ответ №1:

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

Я вижу два решения:

  1. в services таблице есть триггер, который обновляет users таблицу всякий раз, когда добавляется или изменяется рейтинг. Это замедляет изменения данных, но не ваши запросы.

  2. Превратиться users в представление. Исходная users таблица будет переименована, и она потеряет avg_service_rating столбец, который вычисляется на лету с помощью представления.

    Чтобы сделать иллюзию идеальной, создайте INSTEAD OF INSERT OR UPDATE OR DELETE триггер в представлении, который изменяет базовую таблицу. Тогда ваше приложение не нужно менять.

    С помощью этого решения вы платите определенную цену как за SELECT изменение, так и за изменение данных, но последняя цена будет ниже, поскольку вам не нужно изменять две таблицы (и users может получить меньше изменений, чем services ). Дополнительным преимуществом является то, что вы избегаете дублирования данных.

Ответ №2:

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

В противном случае ваши варианты — это представление (где вы можете вызвать функцию или вычислить значение с помощью подзапроса) или AFTER UPDATE OR INSERT триггер в service таблице, который обновляется users.avg_service_ratings . С помощью триггера, если вы получаете много обновлений в service таблице, вам нужно будет рассмотреть возможные проблемы параллелизма, но это будет означать, что цифру не нужно вычислять каждый раз при обращении к строке в users таблице.