doc.prototypes.ru: Проекты: PostgreSQL: Кластерные решения: Распределение данных с помощью dblink: Структура кластера: Индексная DB

Равномерное распределение с помощью dblink

Структура кластера

Индексная DB

Таблица позиции в общем рейтинге

Эта таблица - небольшой бонус, с помощью которого мы сможем определять позицию любого фото в рейтинге. Так так у нас рейтинг имеет жесткие рамки от 1 до 5 с точностью 0,0001, то можно сделать небольшую таблицу к которой будем просто указывать количество фото для каждого рейтинга, максимум строк будет 4000, и работать с ней будет гораздо проще.

SQL код (1)

CREATE TABLE "public"."index_rating_foto" (
    "rating"        NUMERIC(5,4)  NOT NULL, 
    "counter"       INTEGER       NOT NULL DEFAULT 0,
    CONSTRAINT "index_rating_foto_pkey" PRIMARY KEY("rating")
) WITHOUT OIDS;

CREATE INDEX "index_rating_foto_idx" ON "public"."index_rating_foto"
  USING btree ("rating" DESC, "counter");

Да, такая вот маленькая таблица, в первой колонке рейтинг, а во второй - количество фото с таким рейтингом, соответсвенно, что бы определить позицию любого фото, достаточно сделать запрос:

SQL код (2)

SELECT SUM(counter) + 1 AS place FROM index_rating_foto WHERE rating > [ рейтинг фото ];

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

Таблица ТОП Фото

В этой таблице хранятся топовые фото нашего проекта, структура её проста: ID фото, хеш пользователя фото по которую определяем хранилище , собственно, рейтинг, и старый рейтинг, что бы мы могли легко определить его изменение.

SQL код (3)

CREATE TABLE "public"."index_top_foto" (
    "foto_id"       INTEGER         NOT NULL, 
    "user_hash"     CHAR(4)         NOT NULL, 
    "rating"        NUMERIC(5,4)    NOT NULL, 
    "old_rating"    NUMERIC(5,4)    NOT NULL DEFAULT 0, 
    CONSTRAINT "index_top_foto_idx" PRIMARY KEY("foto_id", "user_hash")
) WITHOUT OIDS;

CREATE INDEX "index_top_foto_idx1" ON "public"."index_top_foto"
    USING btree ("rating" DESC);

CREATE INDEX "index_top_foto_idx2" ON "public"."index_top_foto"
    USING btree ("rating");

Я принципиально не включаю проверку на ограничение количества записей таблицы, потому что:

Запрос COUNT(*) довольно дорогой, что бы его делать каждый раз во время вставки;
При резком понижении рейтинга фото, когда по идее оно должно выпасть из ТОПа, оно будет сваливаться только лишь в самый низ списка, а фото у которых рейтинг выше, в него так и не попадут;
При удалении фото, таблица рейтига у нас уменьшится и список будет не полным;

Поэтому создадим небольшую хранимую процедуру очистки таблицы, для периодического запуска по cron:

SQL код (4)

CREATE OR REPLACE FUNCTION "public"."index_top_foto_clean" () RETURNS boolean AS
$body$
DECLARE
-- Лучше лимит сделать больше чем нам реально нужно
    max_limit_top   INTEGER     := 20000;
    row_tmp         index_top_foto;
BEGIN
    SELECT * INTO row_tmp 
        FROM index_top_foto 
        ORDER BY rating DESC 
        LIMIT 1 
        OFFSET max_limit_top;
    IF row_tmp.rating IS NOT NULL THEN
        DELETE FROM index_top_foto
            WHERE rating < row_tmp.rating;
    END IF;
    RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql';

Остальные операции изменения определим позднее.

Таблица последних Фото

В этой таблице хранятся последние фото нашего проекта, структура её так же проста: ID фото, хеш пользователя фото по которую определяем хранилище и дата загрузки.

SQL код (5)

CREATE TABLE "public"."index_last_foto" (
    "foto_id"       INTEGER     NOT NULL, 
    "user_hash"     CHAR(4)     NOT NULL, 
    "added"         TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL, 
    CONSTRAINT "index_last_foto_idx" PRIMARY KEY("foto_id", "user_hash")
) WITHOUT OIDS;

CREATE INDEX "index_last_foto_idx1" ON "public"."index_last_foto"
  USING btree ("added" DESC);

Триггеры тоже не нужны. А вот хранимая процедура для чистки таблицы - да:

SQL код (6)

CREATE OR REPLACE FUNCTION "public"."index_last_foto_clean" () RETURNS boolean AS
$body$
DECLARE
    max_limit_last  INTEGER     := 1000;
    row_tmp         index_last_foto;
BEGIN
    SELECT * INTO row_tmp 
        FROM index_last_foto 
        ORDER BY added DESC 
        LIMIT 1 
        OFFSET max_limit_last;
    IF row_tmp.added IS NOT NULL THEN
        DELETE FROM index_last_foto
            WHERE added < row_tmp.added;
    END IF;
    RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql';

Один в один, как и для предыдущей таблицы.

Таблица тегов

Прямая плоская таблица без изысков.

SQL код (7)

CREATE TABLE "public"."index_tags" (
    "id"            SERIAL, 
    "tag"           TEXT        NOT NULL, 
    "tag_hash"      CHAR(4)     NOT NULL, 
    CONSTRAINT "index_tags_pkey" PRIMARY KEY("id"), 
    CONSTRAINT "index_tags_tag_key" UNIQUE("tag")
) WITHOUT OIDS;

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

Точки входа для изменений данных

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

У нас 2 таблицы с рейтингом, первая (index_rating_foto) не именованная, то есть, не имеет непосредтвенных связей с объектами, а вторая (index_top_foto), хоть и именованная, но не полная, то есть в ней не перечислены все связи, поэтому любую из этих таблиц нельзя использовать как точку входа. Вариант, когда мы будем с прокси сервера последовательно обращаться к таблицам рейтинга, я даже не рассматриваю, во первых - слишком много запросов по сети, во-вторых сложнее соблюдать целостность данных и использовать транзации. Для этого сделаем хранимую процедуру, как общую точку входа для таблиц с рейтингами:

SQL код (8)

-- Для кошерности создадим еще пару типов
-- Список возможных команд
CREATE TYPE "public"."command_type" AS ENUM
    ( 'INSERT', 'UPDATE', 'DELETE' );

-- Составной тип как входящие данные для точки входа
CREATE TYPE "public"."index_rating_type_command" AS (
    "command"       "public"."command_type",
    "foto_id"       INTEGER,
    "user_hash"     CHAR(4),
    "new_rating"    NUMERIC(5,4),
    "old_rating"    NUMERIC(5,4)
);

-- Хранимая процедура, она же точка входа
CREATE OR REPLACE FUNCTION "public"."index_foto_rating_command"
    (income "public"."index_rating_type_command") RETURNS boolean AS
$body$
BEGIN
-- Определяем все ли нам дошло:
    IF  income.command IS NULL OR
        income.foto_id IS NULL OR
        income.user_hash IS NULL OR
        (   income.new_rating IS NULL AND
            income.old_rating IS NULL ) THEN
        RAISE EXCEPTION 'fail_index_foto_rating_command';
        RETURN FALSE;
    END IF;
-- Определяем тип команды, и в соответсвии с нем действуем:
    IF income.command = 'DELETE' THEN
-- Если команда на удаление
    -- Удаляем из таблицы ТОПов
        DELETE FROM index_top_foto 
            WHERE   foto_id = income.foto_id AND
                    user_hash = income.user_hash;
    -- Удаляем из таблицы рейтинга
        IF income.old_rating IS NULL OR income.old_rating < 1 THEN
        -- Мало ли, вдруг не пришло.
            income.old_rating := income.new_rating;
        END IF;
        UPDATE index_rating_foto 
            SET counter = counter - 1
            WHERE rating = income.old_rating;
    ELSE
-- Если команда не на удаление, то нам, впрочем пофиг, вставка это или удаление,
-- все равно действуем по ситуации
    -- Обновляем таблицу ТОПов
        UPDATE index_top_foto 
            SET old_rating = rating,
                rating = income.new_rating 
            WHERE foto_id = income.foto_id 
                AND user_hash = income.user_hash;
        IF FOUND = FALSE THEN
        -- Фото в топе нет, значит его просто вставляем
            INSERT INTO index_top_foto
                (foto_id, user_hash, rating, old_rating)
                VALUES (
                            income.foto_id,
                            income.user_hash,
                            income.new_rating,
                            income.old_rating
                        );
        END IF;
    -- Обновляем таблицу рейтингов
        IF income.old_rating IS NULL OR income.old_rating < 1 THEN
        -- Раз нет старого рейтинга, то значит фото новое, просто добавляем счетчик
            UPDATE index_rating_foto
                SET counter = counter + 1
                WHERE rating = income.new_rating;
            IF FOUND = FALSE THEN
            -- Ага, обновить не удалось
                INSERT INTO index_rating_foto
                    (rating, counter)
                    VALUES (income.new_rating, 1);
            END IF;
        ELSE
        -- Обновляем счетчики
            UPDATE index_rating_foto
                SET counter = counter - 1
                WHERE rating = income.old_rating;
            UPDATE index_rating_foto
                SET counter = counter + 1
                WHERE rating = income.new_rating;
            IF FOUND = FALSE AND income.new_rating > 0 THEN
            -- Ага, обновить не удалось
                INSERT INTO index_rating_foto
                    (rating, counter)
                    VALUES (income.new_rating, 1);
            END IF;
        END IF;
    END IF;
    RETURN TRUE;
END;
$body$
LANGUAGE 'plpgsql';

Штатные вызовы будут такими:

SQL код (9)

SELECT index_foto_rating_command(('INSERT', 1, '0001', 5, 0));
SELECT index_foto_rating_command(('UPDATE', 1, '0001', 1, 5));
SELECT index_foto_rating_command(('DELETE', 1, '0001', NULL, 1));

На этом с индексной базой данных закончим. Берем разпределенную базу:

Распределенная DB

Сергей Томулевич aka Phoinix (01.12.2009 г.)

DOC.PROTOTYPES.RU