Партицирование данных в PostgreSQL

partitioning.sql

----------------------------------------------------
-- Проверим, как работают внешние ключи с партициями
----------------------------------------------------

DROP TABLE IF EXISTS content_sentiments2016 CASCADE;
DROP TABLE IF EXISTS content_sentiments2018 CASCADE;
DROP TABLE IF EXISTS content_sentiments CASCADE;
DROP TABLE IF EXISTS content_keywords CASCADE;
DROP TABLE IF EXISTS content2016 CASCADE;
DROP TABLE IF EXISTS content2017 CASCADE;
DROP TABLE IF EXISTS content CASCADE;

------------------
-- Основные данные
------------------

-- Эта таблица будет партицироваться
CREATE TABLE content (
    id              int not null,
    published_at    timestamp(0) without time zone,
    text_data       text,
    CONSTRAINT report_pkey PRIMARY KEY (id)
);
CREATE INDEX content_published_at_index
    ON content USING btree (published_at);

CREATE TABLE content_sentiments (
  content_id integer NOT NULL,
  sentiments integer[] NOT NULL, -- Ключевые слова в материале
  CONSTRAINT content_sentiments_pkey PRIMARY KEY (content_id),
  CONSTRAINT content_sentiments_content_id_foreign FOREIGN KEY (content_id)
      REFERENCES content (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
CREATE INDEX content_sentiments_content_id_index
  ON content_sentiments
  USING btree (content_id);


-- Эта таблица партицироваться не будет
CREATE TABLE content_keywords (
  content_id integer NOT NULL,
  keyword_ids integer[] NOT NULL, -- Ключевые слова в материале
  CONSTRAINT content_keywords_pkey PRIMARY KEY (content_id),
  CONSTRAINT content_keywords_content_id_foreign FOREIGN KEY (content_id)
      REFERENCES content (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
CREATE INDEX content_keywords_content_id_index
  ON content_keywords
  USING btree (content_id);


-----------
-- Партиции
-----------

-- content
CREATE TABLE content2016 (
        CHECK (id >= 1 AND id < 5)
    )
    INHERITS (content);
CREATE INDEX content2016_published_at_index
    ON content2016 USING btree (published_at);

CREATE TABLE content2017 (
        CHECK (id >= 5 AND id < 10)
    )
    INHERITS (content);
CREATE INDEX content2017_published_at_index
    ON content2017 USING btree (published_at);

-- content_sentiments
CREATE TABLE content_sentiments2016 (
        CHECK (content_id >= 1 AND content_id < 5)
    )
    INHERITS (content_sentiments);
CREATE INDEX content_sentiments2016_content_id_index
    ON content_sentiments2016 USING btree (content_id);

CREATE TABLE content_sentiments2017 (
        CHECK (content_id >= 5 AND content_id < 10)
    )
    INHERITS (content_sentiments);
CREATE INDEX content_sentiments2017_content_id_index
    ON content_sentiments2017 USING btree (content_id);



-- Определяем правила добавления данных в content
CREATE RULE content_insert AS ON INSERT TO content DO INSTEAD NOTHING;
CREATE RULE content_update AS ON UPDATE TO content DO INSTEAD NOTHING;
CREATE RULE content_delete AS ON DELETE TO content DO INSTEAD NOTHING;

CREATE RULE content_insert_2016 AS ON INSERT TO content 
    WHERE (id >= 1 AND id < 5)
DO INSTEAD INSERT INTO content2016 VALUES (NEW.*);

CREATE RULE content_insert_2017 AS ON INSERT TO content 
    WHERE (id >= 5 AND id < 10)
DO INSTEAD INSERT INTO content2017 VALUES (NEW.*);

CREATE RULE content_sentiments_insert_2016 AS ON INSERT TO content_sentiments 
    WHERE (content_id >= 1 AND content_id < 5)
DO INSTEAD INSERT INTO content_sentiments2016 VALUES (NEW.*);

CREATE RULE content_sentiments_insert_2017 AS ON INSERT TO content_sentiments 
    WHERE (content_id >= 5 AND content_id < 10)
DO INSTEAD INSERT INTO content_sentiments2017 VALUES (NEW.*);



-- Все готово. Теперь пробуем добавлять данные в content

INSERT INTO content(id, published_at, text_data)
VALUES (1, '2016-02-02', 'text 1');

INSERT INTO content(id, published_at, text_data)
VALUES (2, '2017-02-02', 'text 2');

INSERT INTO content(id, published_at, text_data)
VALUES (3, '2016-02-03', 'text 3');

INSERT INTO content(id, published_at, text_data)
VALUES (4, '2016-02-03', 'text 4');

-- Все есть
SELECT * FROM content;

-- Причем на каждом шарде - свои данные
SELECT * FROM content2016;
SELECT * FROM content2017;


-- Связанные данные
INSERT INTO content_sentiments (content_id, sentiments)
VALUES (1, '{1, 2, 3}'::int[]);

INSERT INTO content_sentiments (content_id, sentiments)
VALUES (5, '{1, 2, 3}'::int[]);

SELECT * FROM content_sentiments;
SELECT * FROM content_sentiments2016;
SELECT * FROM content_sentiments2017;


-- Так возникает ошибка внешнего ключа. 
-- Поэтому свяазнные таблиы тоже надо партицировать
--INSERT INTO content_keywords (content_id, keyword_ids)
--VALUES (1, '{1, 2, 3}'::int[]);