1С и Postgres: Партиции

Всем привет.

Замечали как быстро работает БД пока она мелкая? Думаю да. Но с ростом данных, поиск и запись становятся все медленней и медленней. Некоторые борятся с этим при помощи "супер" обработок "Свертка базы" и т.п., но настоящие "госу" либо комбинируют эти вещи, либо стараются разобраться с причиной тормозов.

Итак начнем. Партиция - это кусок таблицы, заполняемый по определенным правилам.

Партиция может создаваться: по диапазону значений (range) и по списку значений (list).

Для того чтобы вы все лучше поняли приведу пример из собственной 1С. Для примера возьмем регистр продаж (если не ошибаюсь :)) _accumreg4959

CREATE TABLE _accumreg4959
(
  _period timestamp without time zone NOT NULL,
  _recordertref bytea NOT NULL,
  _recorderrref bytea NOT NULL,
  _lineno numeric(9,0) NOT NULL,
  _active boolean NOT NULL,
  _fld4960rref bytea NOT NULL,
  _fld4961rref bytea NOT NULL,
  _fld4962_type bytea NOT NULL,
  _fld4962_rtref bytea NOT NULL,
  _fld4962_rrref bytea NOT NULL,
  _fld4963rref bytea NOT NULL,
  _fld4964_type bytea NOT NULL,
  _fld4964_rtref bytea NOT NULL,
  _fld4964_rrref bytea NOT NULL,
  _fld4965rref bytea NOT NULL,
  _fld4966rref bytea NOT NULL,
  _fld6031_type bytea NOT NULL,
  _fld6031_rtref bytea NOT NULL,
  _fld6031_rrref bytea NOT NULL,
  _fld4967 numeric(15,3) NOT NULL,
  _fld4968 numeric(15,2) NOT NULL,
  _fld4969 numeric(15,2) NOT NULL,
  _fld6032 numeric(15,2) NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE _accumreg4959 OWNER TO postgres;
ALTER TABLE _accumreg4959 ALTER COLUMN _recordertref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _recorderrref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4960rref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4961rref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4962_type SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4962_rtref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4962_rrref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4963rref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4964_type SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4964_rtref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4964_rrref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4965rref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld4966rref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld6031_type SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld6031_rtref SET STORAGE PLAIN;
ALTER TABLE _accumreg4959 ALTER COLUMN _fld6031_rrref SET STORAGE PLAIN;


-- Index: _accumr4959_bydims4970_rtrn

-- DROP INDEX _accumr4959_bydims4970_rtrn;

CREATE UNIQUE INDEX _accumr4959_bydims4970_rtrn
  ON _accumreg4959
  USING btree
  (_fld4960rref, _period, _recordertref, _recorderrref, _lineno);

-- Index: _accumr4959_bydims6035_rtrn

-- DROP INDEX _accumr4959_bydims6035_rtrn;

CREATE UNIQUE INDEX _accumr4959_bydims6035_rtrn
  ON _accumreg4959
  USING btree
  (_fld6031_type, _fld6031_rtref, _fld6031_rrref, _period, _recordertref, _recorderrref, _lineno);

-- Index: _accumr4959_byperiod_trn

-- DROP INDEX _accumr4959_byperiod_trn;

CREATE UNIQUE INDEX _accumr4959_byperiod_trn
  ON _accumreg4959
  USING btree
  (_period, _recordertref, _recorderrref, _lineno);

-- Index: _accumr4959_byrecorder_rn

-- DROP INDEX _accumr4959_byrecorder_rn;

CREATE UNIQUE INDEX _accumr4959_byrecorder_rn
  ON _accumreg4959
  USING btree
  (_recordertref, _recorderrref, _lineno);

-- Partitioning

CREATE TABLE _accumreg4959_2010m10 (
    CHECK ( _period >= DATE '2010-10-01' AND _period < DATE '2010-11-01' )
) INHERITS ( _accumreg4959 ) ;

CREATE INDEX _accumreg4959_2010m10__period ON _accumreg4959_2010m10 ( _period ) ;

CREATE OR REPLACE FUNCTION _accumreg4959_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW._period >= DATE '2010-10-01' AND NEW._period < DATE '2010-11-01' )
    THEN
    INSERT INTO _accumreg4959_2010m10 VALUES ( NEW.* );
    END IF ;
    RETURN NEW ;
END ;
$$
LANGUAGE plpgsql ;

CREATE TRIGGER insert__accumreg4959_2010m10_trigger
    BEFORE INSERT OR UPDATE ON _accumreg4959
    FOR EACH ROW EXECUTE PROCEDURE
    _accumreg4959_insert_trigger() ;

Теперь внимание. Я создал таблицу _accumreg4959_2010m10, которая наследует INHERITS базовую 

_accumreg4959 с проверкой вставляемых данных по полю _period.

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

Далее создаем индекс по полю _period.

Затем функцию вставки значениний _accumreg4959_insert_trigger, которая будет вызываться из триггера insert__accumreg4959_2010m10_trigger. Все.

Что в итоге. А в итоге 1С как работала с таблицей _accumreg4959, так и работает, для 1С ничего не изменилось. А вот Postgres ... :) Далее объяснять думаю не нужно, что так можно организовать партиции для каждого месяца на год вперед.

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

Будут вопросы пишите. Благодарю за внимание.