Персональный сайт Александра Литовченко

номер32
Поиграть в преферанс в онлайне
18 мая 2008 08:24

Денормализация через триггеры в PostgreSQL

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

Предположим у нас есть таблицы vendor, и связанная с ней через foreign key таблица product со следующим DDL:

CREATE TABLE "product" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL,
    "vendor_id" integer NOT NULL
);
CREATE TABLE "vendor" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(40) NOT NULL
);
ALTER TABLE product ADD CONSTRAINT fk_product_vendor 
    FOREIGN KEY (vendor_id) REFERENCES vendor(id);

В клиентских приложениях нам нужно сортировать производителей по количеству производимых товаров. В нормализованной базе это делается примерно так:

SELECT v.name, count(p.id) as products FROM vendor v
    LEFT JOIN product p ON v.id=p.vendor_id 
    GROUP BY v.name
    ORDER BY products;

Очевидно, что при больших объемах данных и более сложных OLAP запросах такой подход крайне неэффективен. На помощь приходит денормализация в виде добавления целочисленного поля products в таблицу vendor.

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

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

Для серверного варианта PostgreSQL позволяет через систему триггеров назначить специализированные pl/pgSQL функции на операции добавления, изменения и удаления записей.

CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE FUNCTION ins_product() RETURNS "trigger"
    AS $$
begin
    update vendor set products=products+1 where id=new.vendor_id;
    return new;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_ins_product AFTER INSERT
    ON product FOR EACH ROW EXECUTE PROCEDURE ins_product();

CREATE FUNCTION del_product() RETURNS "trigger"
    AS $$
begin
    update vendor set products=products-1 where id=old.vendor_id;
    return old;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_del_product AFTER DELETE
    ON product FOR EACH ROW EXECUTE PROCEDURE del_product();

CREATE FUNCTION upd_product() RETURNS "trigger"
    AS $$
begin
    if old.vendor_id<>new.vendor_id then
        update vendor set products=products+1 where id=new.vendor_id;
        update vendor set products=products-1 where id=old.vendor_id;
    end if;
    return new
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_upd_product AFTER UPDATE 
    ON product FOR EACH ROW EXECUTE PROCEDURE upd_product();

Теперь, какие бы мы не делали манипуляции с таблицей product, поле products в таблице vendor всегда будет в актуальном состоянии. Изменения можно производить через любой интерфейс к базе и счетчик продуктов будет автоматически изменяться, не требуя никаких усилий от клиентского кода.

SELECT name, products FROM vendor ORDER BY products;

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

Комментарии

1 21 июня 2008 10:29, Дима

В связке Django+MySQL (все таблицы innodb) работало неправильно, почему не установлено, но неправильно работало именно с generic моделями, когда вставлялись данные в несколько таблиц.

Заработало только тогда когда +1/-1 в триггерах заменили на SELECT COUNT(*) FROM product WHERE product.vendor_id=NEW.vendor_id

2 28 июля 2008 04:58, KOHb

Это говорит только о сырости MySQL имхо.

Хотя вообще очень странно.

Добавить комментарий

только текст. HTML теги вырезаются:

Пожалуйста будьте вежливы при общении