Particionando tabelas no PostgreSQL usando herança

No PostgreSQL existe como particionar tabelas usando herança de classe.

Esse sistema funciona um pouco como o MySQL, que usa particionamento padrão. Mas também é mais complexo.

Primeiro criamos uma tabela-base contendo os campos. Ela é abstrata, ou seja: ela não terá registros, apenas suas descendentes:

create table all_logs (
id bigserial,
date timestamp default now(),
id_user int,
action varchar(20),

check(false) no inherit
);

A constraint check(false) no inherit só funcionará se o banco for versão 9.2 ou superior. Se ainda não suporta, omita.

Observe que esta tabela não possui chave primária nem chaves estrangeiras. Eles devem existir apenas nas tabelas-filhas desta.

Estas são as tabelas filhas, que armazenam os registros. Veja que elas "herdam"os campos da tabela all_logs como em orientação a objetos:

create table logs_2015_01 (

primary key(id),
check(date between '2015-01-01' and '2015-02-01')
) inherits (all_logs);

create index on logs_2015_01(date);

create table logs_2015_02 (

primary key(id),
check(date between '2015-02-01' and '2015-03-01')
) inherits (all_logs);

create index on logs_2015_02(date);

1. A cláusula inherits faz ela herdar da tabela all_logs. Mas é preciso definir os campos chaves primária e estrangeiras, bem como os índices em cada tabela filha. Definir na tabela-base, as filhas não vão herdar essas constraints.
2. A cláusula check é importante para performance: uma pesquisa pela tabela all_logs usando o campo date fará excluir, das tabelas-filhas, todos os meses que não correspondem às datas no check, sem ler seus dados.

Inserindo registros.
Para inserir na tabela all_logs, é necessário criar uma trigger que indique para qual tabela-filha redirecionar o registro.

CREATE OR REPLACE FUNCTION insert_logs()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.date between '2015-01-01' AND '2015-02-01' ) THEN
        INSERT INTO logs_2015_01 VALUES (NEW.*);
    ELSIF ( NEW.date between '2015-02-01' AND '2015-03-01' ) THEN
        INSERT INTO logs_2015_02 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date fora de alcance. Corrija a função insert_logs()';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_logs
    BEFORE INSERT ON all_logs
    FOR EACH ROW EXECUTE PROCEDURE insert_logs();

Então você pode inserir registros usando INSERT na tabela all_logs. A trigger redirecionará para a tabela filha adequada.

Fazendo pesquisas:
No exemplo nós definimos a  partição pelo campo date. uma instrução de SELECT por este campo fará o banco escolher as partições corretas por causa da cláusula check.

SELECT * from all_logs where date between '2015-01-05' and '2015-01-10'
Isto, por exemplo, fará com que o banco utilize apenas a tabela logs_2015_01, porque a 2015_02 usa um check(date between '2015-02-01' and '2015-03-01').

Excluindo partições:






Basta dropar a tabela que você deseja excluir.
drop table 2015_01