O PostgreSQL aceita o particionamento básico de tabelas. Esta seção descreve por que e como implementar o particionamento como parte do projeto do banco de dados.
O particionamento se refere a dividir o que é logicamente uma grande tabela, em partes físicas menores. O particionamento pode fornecer vários benefícios:
O desempenho da consulta pode ser melhorado drasticamente em determinadas situações, principalmente quando grande parte das linhas mais acessadas da tabela está em uma única partição, ou em um pequeno número de partições. O particionamento substitui de fato os níveis superiores de índices da árvore, tornando mais provável que as partes mais usadas dos índices caibam na memória.
Quando consultas ou atualizações acessam uma grande porcentagem de uma única partição, o desempenho pode ser aprimorado usando a verificação sequencial dessa partição em vez de usar o índice, o que exigiria leituras de acesso aleatório espalhadas por toda a tabela.
Carregamentos e exclusões em massa podem ser realizados
adicionando ou removendo partições, se o padrão de uso for
considerado no projeto de particionamento. Remover uma partição
individual usando DROP TABLE, ou fazendo
ALTER TABLE DETACH PARTITION, é muito mais
rápido do que uma operação em massa. Esses comandos também
evitam inteiramente a sobrecarga do VACUUM
causada pelo DELETE em massa.
Dados raramente usados podem ser migrados para mídias de armazenamento mais baratas e lentas.
Esses benefícios normalmente somente valem a pena quando a tabela é muito grande. O ponto exato em que uma tabela vai se beneficiar do particionamento depende da aplicação, embora uma regra geral seja que o tamanho da tabela deve exceder a memória física do servidor de banco de dados.
O PostgreSQL oferece suporte nativo para as seguintes formas de particionamento:
A tabela é particionada em “intervalos” definidos
por uma coluna-chave, ou conjunto de colunas, sem sobreposição
entre os intervalos de valores atribuídos a diferentes partições.
Por exemplo, pode-se particionar por intervalos de datas, ou por
intervalos de identificadores, para objetos de negócio
específicos. Os limites de cada intervalo são entendidos como
inclusivos na extremidade inferior e exclusivos na extremidade
superior. Por exemplo, se o intervalo de uma partição for de
1 a 10, e o intervalo da
próxima for de 10 a 20,
então o valor 10 pertence à segunda partição
e não à primeira.
[37]
A tabela é particionada listando explicitamente quais valores de chave aparecem em cada partição.
A tabela é particionada especificando um módulo e um resto para cada partição. Cada partição conterá as linhas para as quais o valor de hash da chave de partição dividido pelo módulo especificado produzirá o resto especificado. [38]
Se a aplicação precisar usar outra forma de particionamento,
diferente das listadas acima, podem ser usados métodos alternativos
como herança e visões UNION ALL.
Esses métodos oferecem flexibilidade, mas não possuem alguns dos
benefícios de desempenho do particionamento declarativo nativo.
O PostgreSQL permite declarar que a tabela está dividida em partições. A tabela dividida é chamada de tabela particionada. A declaração inclui o método de particionamento, conforme descrito acima, além de uma lista de colunas ou expressões a serem usadas como a chave de partição.
A tabela particionada é ela mesma uma tabela “virtual” sem armazenamento próprio. Em vez disso, o armazenamento pertence às partições, que são tabelas comuns associadas à tabela particionada. Cada partição armazena um subconjunto de dados conforme definido por seus limites de partição. Todas as linhas inseridas em uma tabela particionada são dirigidas para a partição apropriada, com base nos valores da(s) coluna(s) da chave de partição. A atualização da chave de partição de uma linha fará com que a linha seja movida para uma partição diferente, se ela não atender mais aos limites de partição de sua partição corrente.
As próprias partições podem ser definidas como tabelas particionadas, resultando em subparticionamento. Embora todas as partições devam ter as mesmas colunas que sua mãe particionada, as partições podem ter seus próprios índices, restrições e valores padrão, distintos daqueles de outras partições. Veja CREATE TABLE para obter mais detalhes sobre como criar tabelas e partições particionadas.
Não é possível transformar uma tabela normal em uma tabela particionada,
ou vice-versa. No entanto, é possível adicionar uma tabela regular ou
particionada existente como partição de uma tabela particionada,
ou remover uma partição de uma tabela particionada transformando-a
em uma tabela independente; isso pode simplificar e acelerar muitos
processos de manutenção. Veja ALTER TABLE para
saber mais sobre os subcomandos ATTACH PARTITION
e DETACH PARTITION.
As partições também podem ser tabelas estrangeiras, embora seja necessário um cuidado considerável, porque é responsabilidade do usuário que o conteúdo da tabela estrangeira satisfaça a regra de particionamento. Existem algumas outras restrições também. Veja CREATE FOREIGN TABLE para obter mais informações.
Suponha que estamos construindo um banco de dados para uma grande empresa de sorvetes. A empresa registra os picos de temperatura de todos os dias, assim como as vendas de sorvetes em cada região. Conceitualmente, queremos uma tabela como:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
Sabemos que a maioria das consultas irão acessar apenas os dados da última semana, mês ou trimestre, porque o principal uso dessa tabela será a elaboração de relatórios online para gerenciamento. Para reduzir a quantidade de dados antigos que precisam ser armazenados, decidiu-se manter apenas os dados dos últimos 3 anos. No início de cada mês, serão removidos os dados do mês mais antigo. Nessa situação, podemos usar o particionamento para ajudar a atender a todos os diferentes requisitos para a tabela de medição.
Para usar o particionamento declarativo nesse caso, são realizadas as seguintes etapas:
Criação da tabela measurement como
tabela particionada especificando a cláusula
PARTITION BY, que inclui o método de
particionamento (RANGE nesse caso), e a lista
de coluna(s) usada(s) como chave de partição.
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
Criação das partições. A definição de cada partição deve especificar limites que correspondam ao método de particionamento e à chave de partição da tabela-mãe. Deve-se observar que especificar limites de forma que os valores da nova partição se sobreponham aos de uma ou mais partições existentes causará erro.
As partições assim criadas são, em todos os aspectos, tabelas normais do PostgreSQL (ou, possivelmente, tabelas estrangeiras). É possível especificar espaço de tabelas e parâmetros de armazenamento para cada partição em separado.
Para o nosso exemplo, cada partição deve conter os dados de um mês específico, para corresponder ao requisito de excluir os dados de um mês de cada vez. Assim, os comandos podem se parecer com:
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE measurement_y2007m12 PARTITION OF measurement
FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
TABLESPACE fasttablespace;
CREATE TABLE measurement_y2008m01 PARTITION OF measurement
FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
WITH (parallel_workers = 4)
TABLESPACE fasttablespace;
(Lembre-se de que partições adjacentes podem compartilhar o valor do limite, porque os limites superiores do intervalo são tratados como limites exclusivos.)
Se for desejado implementar subparticionamento, deve ser
especificada novamente a cláusula PARTITION BY
nos comandos usados para criar as partições individualmente, como,
por exemplo:
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
PARTITION BY RANGE (peaktemp);
Após criar as partições para
measurement_y2006m02, quaisquer dados
inseridos em measurement mapeados para
measurement_y2006m02 (ou dados inseridos
diretamente em measurement_y2006m02,
o que é permitido, desde que sua restrição de partição seja
respeitada) será redirecionado para uma de suas partições com
base na coluna peaktemp.
A chave de partição especificada pode se sobrepor à chave de
partição da mãe, embora deva ser tomado cuidado ao especificar
os limites de uma subpartição, de modo que o conjunto de dados
que ela aceita constitua um subconjunto do que os próprios
limites da partição permitem; o sistema não tenta verificar se
esse é realmente o caso.
A inserção de dados na tabela-mãe que não podem ser mapeados para uma das partições existentes causa erro; uma partição apropriada deve ser adicionada manualmente.
Não é necessário criar manualmente restrições de tabela que descrevam as condições de limite da partição para as partições. Tais restrições serão criadas automaticamente.
Criação de índice na(s) coluna(s) da chave, assim como quaisquer outros índices desejados na tabela particionada. (O índice de chave não é estritamente necessário, mas na maioria dos cenários é útil.) Isso cria, automaticamente, um índice correspondente em cada partição, e quaisquer partições que se criar ou anexar posteriormente também terão esse índice. Um índice ou restrição de unicidade declarada em uma tabela particionada é “virtual”, da mesma forma que a tabela particionada o é: os dados reais estão em índices filhos nas tabelas de partição individualmente.
CREATE INDEX ON measurement (logdate);
Certifique-se de que o parâmetro de configuração
enable_partition_pruning não esteja
desativado no arquivo postgresql.conf.
Se estiver, as consultas não serão otimizadas como desejado.
No exemplo acima, estará sendo criada uma nova partição a cada mês, portanto se recomenda escrever um script gerando o DDL necessário automaticamente.
Normalmente o conjunto de partições estabelecido ao definir inicialmente a tabela não se destina a permanecer estático. É comum querer remover partições que contêm dados antigos e adicionar periodicamente novas partições para novos dados. Uma das vantagens mais importantes do particionamento é precisamente permitir que esta tarefa dolorosa seja executada quase instantaneamente pelo tratamento da estrutura da partição, em vez de mover fisicamente grandes quantidades de dados.
A opção mais simples para remover dados antigos é excluir a partição que não é mais necessária:
DROP TABLE measurement_y2006m02;
Isso pode excluir milhões de registros muito rapidamente, porque
não precisa excluir cada registro individualmente.
No entanto, observe que o comando acima requer um bloqueio
ACCESS EXCLUSIVE na tabela-mãe.
Outra opção geralmente preferível é remover a partição da tabela particionada, mas manter o acesso à partição como sendo uma tabela autônoma. Existem duas formas possíveis:
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;
Esta opção permite que operações posteriores possam ser executadas
nos dados antes de serem descartados definitivamente. Por exemplo,
geralmente é um bom momento para fazer cópia de segurança dos dados
usando COPY, pg_dump,
ou ferramentas semelhantes.
Também pode ser um momento útil para agregar dados em formatos
menores, realizar outras manipulações de dados ou criar
relatórios. A primeira forma do comando requer um bloqueio
ACCESS EXCLUSIVE na tabela-mãe.
Adicionar o qualificador CONCURRENTLY, como foi
feito na segunda forma, permite que a operação de desconexão exija
apenas o bloqueio SHARE UPDATE EXCLUSIVE na
tabela-mãe, mas veja
DETACH PARTITION
para obter mais detalhes sobre as restrições.
partition_name [ CONCURRENTLY | FINALIZE ]
Da mesma forma, podemos adicionar uma nova partição para lidar com os novos dados. Podemos criar uma partição vazia na tabela particionada, de maneira análoga a como foram criadas as partições originais acima:
CREATE TABLE measurement_y2008m02 PARTITION OF measurement
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
TABLESPACE fasttablespace;
Como alternativa à criação de uma nova partição, às vezes é mais
conveniente criar uma nova tabela separada da estrutura de
partições e anexá-la como uma partição posteriormente.
Isto permite que novos dados sejam carregados, verificados e
transformados antes de aparecerem na tabela particionada.
Além disso, a operação ATTACH PARTITION requer
apenas um bloqueio SHARE UPDATE EXCLUSIVE na
tabela particionada em vez do bloqueio
ACCESS EXCLUSIVE requerido por
CREATE TABLE ... PARTITION OF, portanto é mais
amigável para operações concorrentes na tabela particionada;
veja ALTER TABLE ... ATTACH PARTITION
para obter mais detalhes.
A opção
CREATE TABLE ... LIKE
Pode ser útil para evitar a repetição tediosa da definição da
tabela principal; por exemplo:
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
TABLESPACE fasttablespace;
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
Observe que, ao executar o comando ATTACH PARTITION,
a tabela será varrida para validar a restrição de partição enquanto
mantém um bloqueio ACCESS EXCLUSIVE nesta partição.
Conforme mostrado acima, recomenda-se evitar esta varredura criando
uma restrição CHECK correspondendo à restrição
de partição esperada na tabela antes de anexá-la,
uma vez que ATTACH PARTITION esteja completo,
recomenda-se remover a restrição CHECK que agora
se tornou redundante.
Se a tabela que está sendo anexada for ela própria uma tabela
particionada, então cada uma de suas subpartições será bloqueada
e varrida recursivamente até que uma restrição
CHECK adequada seja encontrada ou as partições
folha sejam encontradas.
Da mesma forma, se a tabela particionada tiver uma partição
DEFAULT, é recomendável criar uma restrição
CHECK que exclua a restrição da partição a ser
anexada.
Se isto não for feito, então a partição DEFAULT
será verificada para ver se não contém registros que devem
estar localizados na partição que está sendo anexada.
Esta operação será executada mantendo um bloqueio
ACCESS EXCLUSIVE na partição
DEFAULT.
Se a partição DEFAULT for uma tabela
particionada, cada uma de suas partições será verificada
recursivamente da mesma forma que a tabela que está sendo anexada,
conforme mencionado acima.
Como mencionado anteriormente, é possível criar índices em tabelas
particionadas para que sejam aplicados automaticamente a toda
a hierarquia.
Isto pode ser muito conveniente, porque não apenas todas as
partições existentes serão indexadas, mas também quaisquer
partições futuras.
Entretanto, uma limitação ao criar novos índices em tabelas
particionadas é que não é possível usar o qualificador
CONCURRENTLY, o que pode levar a longos
períodos de bloqueio.
Para evitar isto, pode ser usado
CREATE INDEX ON ONLY na tabela particionada,
que cria o novo índice marcado como inválido, impedindo a aplicação
automática às partições existentes.
Em vez disso, os índices podem ser criados individualmente em cada
partição usando CONCURRENTLY e
anexados ao índice particionado no pai
usando ALTER INDEX ... ATTACH PARTITION.
Assim que os índices de todas as partições forem anexados ao
índice pai, o índice pai será marcado como válido automaticamente.
Exemplo:
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);
CREATE INDEX CONCURRENTLY measurement_usls_200602_idx
ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
ATTACH PARTITION measurement_usls_200602_idx;
...
Esta técnica pode ser usada com as restrições
UNIQUE e PRIMARY KEY também;
Os índices são criados implicitamente quando a restrição é criada.
Exemplo:
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...
As seguintes limitações aplicam-se às tabelas particionadas:
Para criar uma restrição de chave primária ou de unicidade em uma tabela particionada, as chaves de partição não devem incluir expressões ou chamadas de função, e as colunas da restrição devem incluir todas as colunas da chave da partição. Esta limitação existe porque os índices individuais que compõem a restrição só podem impor a unicidade diretamente dentro de suas próprias partições; portanto, a própria estrutura de particionamento deve garantir que não haja duplicatas em partições diferentes.
Da mesma forma, uma restrição de exclusão deve incluir todas
as colunas da chave da partição.
Além disso, a restrição deve comparar estas colunas para verificar
a igualdade (não, por exemplo, &&).
Novamente, esta limitação decorre da impossibilidade de impor
restrições entre partições.
A restrição pode incluir colunas adicionais que não fazem parte
da chave da partição e pode compará-las com quaisquer operadores
que se desejar.
Os gatilhos BEFORE ROW em
INSERT não podem alterar qual partição será o
destino final para uma nova linha.
Não é permitido misturar tabelas temporárias e permanentes na mesma árvore de partição. Portanto, se a tabela particionada for permanente, suas partições também devem ser permanentes e, da mesma forma, se a tabela particionada for temporária, suas partições também devem ser temporárias. Ao usar tabelas temporárias, todos os membros da árvore de partição têm que ser da mesma sessão.
As partições individuais são vinculadas à sua tabela particionada usando herança por trás da cena. No entanto, não é possível usar todos os recursos genéricos de herança com tabelas particionadas declarativamente ou suas partições, conforme discutido abaixo. Notadamente, uma partição não pode ter outras mães além da tabela particionada da qual é uma partição, nem pode herdar de uma tabela particionada junto com uma tabela regular, significando que as tabelas particionadas e suas partições nunca compartilham uma hierarquia de herança com tabelas regulares.
Como uma hierarquia de partição formada pela tabela particionada
e suas partições ainda é uma hierarquia de herança,
tableoid e todas as regras normais de
herança se aplicam conforme descrito na
Seção 5.11, mas com algumas exceções:
As partições não podem possuir colunas que não estejam presentes
na partição-mãe.
Não é possível especificar colunas ao criar partições com
CREATE TABLE, nem é possível adicionar colunas
a partições depois usando ALTER TABLE.
As tabelas podem ser adicionadas como partição usando
ALTER TABLE ... ATTACH PARTITION somente se
suas colunas corresponderem exatamente às da tabela-mãe.
As restrições CHECK e NOT NULL
de uma tabela particionada são sempre herdadas por todas as suas
partições;
Não é permitido criar restrições NO INHERIT
destes tipos.
Não é possível remover uma restrição destes tipos se a mesma
restrição estiver presente na tabela-mãe.
Usar ONLY para adicionar ou remover uma
restrição somente na tabela particionada tem suporte, desde
que não existam partições.
Se existirem partições, usar ONLY
resultará em um erro para quaisquer restrições que não sejam
UNIQUE ou PRIMARY KEY.
Em vez disto, podem ser adicionadas restrições às próprias
partições e (se elas não estiverem presentes na tabela principal)
removidas.
Como uma tabela particionada não possui dados próprios, as
tentativas de usar TRUNCATE e
ONLY em uma tabela particionada sempre
retornarão erro.
Embora o particionamento declarativo nativo seja adequado para os casos de uso mais comuns, há algumas circunstâncias em que uma abordagem mais flexível pode ser útil. O particionamento pode ser implementado usando herança de tabela, permitindo vários recursos não aceitos pelo particionamento declarativo, como:
No particionamento declarativo, as partições devem ter exatamente o mesmo conjunto de colunas que a tabela particionada, enquanto com herança de tabela, as tabelas-filhas podem ter colunas extras não presentes na mãe.
A herança de tabela permite herança múltipla.
O particionamento declarativo aceita apenas particionamento de intervalo, lista e hash, enquanto a herança de tabela permite que os dados sejam divididos da forma que o usuário escolher. (Note, no entanto, que se a restrição de exclusão não puder excluir tabelas-filhas de maneira eficaz, o desempenho da consulta poderá ser ruim.)
Esse exemplo cria uma estrutura de particionamento equivalente à do exemplo de particionamento declarativo acima. São efetuadas as seguintes etapas:
Criação da tabela “raiz”, da qual todas as tabelas
“filhas” herdarão. Esta tabela não conterá dados.
Não é definida nenhuma restrição de verificação nessa tabela,
a menos que se pretenda que elas sejam aplicadas igualmente a
todas as tabelas-filhas. Também não faz sentido definir quaisquer
índices ou restrições de unicidade. Para o nosso exemplo, a
tabela raiz é a tabela measurement,
conforme definida originalmente:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
Criação de várias tabelas “filhas” em que cada uma herda da tabela raiz. Normalmente, essas tabelas não adicionam nenhuma coluna ao conjunto herdado da tabela raiz. Assim como no particionamento declarativo, essas tabelas são, em todos os aspectos, tabelas normais do PostgreSQL (ou tabelas estrangeiras).
CREATE TABLE measurement_y2006m02 () INHERITS (measurement); CREATE TABLE measurement_y2006m03 () INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 () INHERITS (measurement); CREATE TABLE measurement_y2007m12 () INHERITS (measurement); CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
Adição de restrições de tabela não sobrepostas às tabelas filhas para definir os valores de chave permitidos em cada uma.
Exemplos típicos seriam:
CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 )
Deve-se certificar de que as restrições garantam que não haja sobreposição entre os valores de chave permitidos em diferentes tabelas-filhas. Um erro comum é configurar restrições de intervalo como:
CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
Isso está errado, porque não está claro a qual tabela-filha o valor da chave 200 pertence. Em vez disso, os intervalos devem ser definidos nesse estilo:
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
Para cada tabela-filha, deve ser criado um índice na(s) coluna(s)-chave, assim como quaisquer outros índices desejados.
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
Queremos que a aplicação consiga executar
INSERT INTO medição ..., e que os dados sejam
redirecionados para a tabela-filha apropriada. Podemos organizar
isso anexando uma função de gatilho adequada à tabela raiz.
Se os dados forem adicionados apenas à filha mais recente,
podemos usar uma função de gatilho muito simples:
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Depois de criar a função, é criado o gatilho que chama a função de gatilho:
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
A função de gatilho deve ser redefinida todo mês para que a linha seja sempre inserida na tabela-filha corrente. No entanto, a definição do gatilho não precisa ser atualizada.
Podemos desejar inserir dados, e fazer com que o servidor localize automaticamente a tabela-filha onde a linha deve ser adicionada. Poderíamos fazer isso com uma função de gatilho mais complexa, como, por exemplo:
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
NEW.logdate < DATE '2008-02-01' ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
A definição do gatilho é a mesma de antes. Note que cada teste
IF deve corresponder exatamente à restrição
CHECK de sua tabela-filha.
Embora esta função seja mais complexa do que o caso de um único mês, ela não precisa ser atualizada com tanta frequência, porque as ramificações podem ser adicionadas antes de serem necessárias.
Na prática, pode ser melhor verificar primeiro a filha mais nova, se a maioria das inserções entrar nesta filha. Para simplificar, mostramos os testes do gatilho na mesma ordem usada em outras partes deste exemplo.
Uma abordagem diferente para redirecionar inserções para a tabela-filha apropriada é configurando regras, em vez de gatilho, na tabela raiz. Por exemplo:
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
Uma regra produz uma sobrecarga muito maior do que um gatilho, mas a sobrecarga é paga uma vez por consulta em vez de uma vez por linha, portanto esse método pode ser vantajoso para situações de inserção em massa. No entanto, geralmente o método de disparo de gatilho oferece melhor desempenho.
Esteja ciente de que o comando COPY ignora
regras. Se for desejado usar o COPY para
inserir dados, será necessário copiar para a tabela-filha
correta, em vez de copiar diretamente para a raiz.
O comando COPY dispara gatilhos, então pode
ser usado normalmente se for usada a abordagem de gatilho.
Outra desvantagem da abordagem de regra é não haver uma maneira simples de forçar um erro se o conjunto de regras não abranger a data da inserção; os dados vão silenciosamente para a tabela raiz.
Certifique-se de que o parâmetro de configuração
constraint_exclusion não esteja
desativado no arquivo postgresql.conf;
caso contrário, as tabelas-filhas podem ser acessadas
desnecessariamente.
Como pode ser visto, uma hierarquia de tabelas complexa pode exigir uma quantidade substancial de DDL. No exemplo acima, estaríamos criando uma nova tabela-filha a cada mês, portanto seria recomendável escrever um script que gerasse o DDL necessário automaticamente.
Para remover dados antigos rapidamente, basta excluir a tabela filha que não é mais necessária:
DROP TABLE measurement_y2006m02;
Para remover a tabela-filha da hierarquia de herança, mas manter o acesso a ela como tabela normal:
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
Para adicionar uma nova tabela-filha para lidar com novos dados, deve ser criada uma tabela-filha vazia, como foi feito com as tabelas-filhas originais criadas acima:
CREATE TABLE measurement_y2008m02 (
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);
Como alternativa, pode-se querer criar e preencher a nova tabela filha antes de adicioná-la à hierarquia de tabela. Isso pode permitir que os dados sejam carregados, verificados e transformados antes de se tornarem visíveis para consultas na tabela-mãe.
CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- possibly some other data preparation work ALTER TABLE measurement_y2008m02 INHERIT measurement;
As seguintes advertências se aplicam ao particionamento implementado usando herança:
Não existe uma maneira automática para verificar se todas as
restrições CHECK são mutuamente exclusivas.
É mais seguro criar um código que gere tabelas-filhas e crie e/ou
modifique objetos associados do que escrever cada um manualmente.
Índices e restrições de chave estrangeira se aplicam a uma única tabela, não incluindo suas filhas de herança; portanto existem algumas advertências a serem observadas.
Os esquemas mostrados aqui assumem que os valores da(s) coluna(s)
chave de uma linha nunca mudam, ou pelo menos não mudam o
suficiente para exigir que ela seja movida para outra partição.
Um UPDATE que tentar fazer isso vai falhar
devido às restrições de CHECK.
Se for necessário lidar com esses casos, então poderão ser
colocados gatilhos de atualização adequados nas tabelas-filhas,
mas isso torna o gerenciamento da estrutura muito mais complicado.
Os comandos VACUUMe ANALYZE
processam automaticamente todas as tabelas-filhas de herança.
Se isto não for desejado, pode ser usada a palavra-chave
ONLY.
Um comando como:
ANALYZE ONLY measurement;
só irá processar a tabela raiz.
As instruções INSERT com cláusulas
ON CONFLICT provavelmente não vão funcionar
conforme o esperado, porque a ação ON CONFLICT
só é executada no caso de violações de unicidade na tabela alvo
especificada, não incluindo suas tabelas-filhas.
São necessários gatilhos ou regras para rotear as linhas para a tabela-filha desejada, a menos que a aplicação esteja explicitamente ciente do esquema de particionamento. Os gatilhos podem ser complicados de escrever e são muito mais lentos do que o roteamento de tuplas executado internamente pelo particionamento declarativo.
Remoção de partição é uma técnica de otimização de consulta que melhora o desempenho de tabelas particionadas declarativamente. Como exemplo:
SET enable_partition_pruning = on; -- o padrão
SELECT count(*)
FROM measurement
WHERE logdate >= DATE '2008-01-01';
Sem remoção de partição, a consulta acima examinaria cada uma das
partições da tabela measurement.
Com remoção de partição ativada, o planejador examinará a
definição de cada partição e provará que a partição não precisa ser
verificada, porque não pode conter nenhuma linha que atenda à
cláusula WHERE da consulta.
Quando o planejador pode provar isso, ele exclui
(prune) a partição do plano da consulta.
Usando o comando EXPLAIN e o parâmetro de configuração
enable_partition_pruning, é possível mostrar
a diferença entre um plano para o qual as partições foram removidas,
e um para o qual não foram removidas. Um plano não otimizado típico
para esse tipo de configuração de tabela é:
SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=188.76..188.77 rows=1 width=8)
-> Append (cost=0.00..181.05 rows=3085 width=0)
-> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
...
-> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
Algumas ou todas as partições podem usar varreduras de índice em vez de varreduras sequenciais de toda a tabela, mas o ponto aqui é não haver necessidade de varrer as partições mais antigas para responder a esta consulta. Quando habilitamos a remoção de partição, obtemos um plano muito mais econômico que fornece a mesma resposta:
SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=37.75..37.76 rows=1 width=8)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
Note que a remoção da partição é orientada apenas pelas restrições definidas implicitamente pelas chaves de partição, e não pela presença de índices, portanto, não é necessário definir índices nas colunas-chave. Se há necessidade de se criar um índice para uma determinada partição, depende se é esperado que as consultas que varrem a partição geralmente varram uma grande parte da partição, ou apenas uma pequena parte. Um índice será útil no último caso, mas não no primeiro.
A exclusão de partição pode ser realizada não apenas durante o
planejamento de uma determinada consulta, mas também durante a sua
execução. Isso é útil, porque pode permitir que mais partições sejam
removidas quando as cláusulas contêm expressões cujos valores não são
conhecidos no momento do planejamento da consulta, como, por exemplo,
parâmetros definidos em um comando PREPARE
usando o valor obtido de uma subconsulta, ou usando um valor
parametrizado no lado interno de uma junção de laço aninhada.
A exclusão de partição durante a execução pode ser realizada em
qualquer um dos seguintes momentos:
Durante a inicialização do plano de consulta. A exclusão de
partição pode ser realizada aqui para valores de parâmetro
conhecidos durante a fase de inicialização da execução.
As partições removidas durante esse estágio não aparecerão no
EXPLAIN ou no EXPLAIN ANALYZE
da consulta. É possível determinar o número de partições
removidas durante esta fase observando a propriedade
“Subplans Removed” na saída do EXPLAIN.
O planejador de consultas obtém bloqueios para todas as partições
que fazem parte do plano.
Entretanto, quando o executor usa um plano em
cache, os bloqueios são obtidos
apenas nas partições que permanecem após a remoção de partições
realizada durante a fase de inicialização da execução, ou seja,
as mostradas na saída do EXPLAIN e não as
referidas pela propriedade “Subplans Removed”.
Durante a execução real do plano de consulta. A exclusão de
partições também pode ser realizada aqui para remover partições
usando valores conhecidos apenas durante a execução real
da consulta. Isso inclui valores de subconsultas e valores de
parâmetros em tempo de execução, como aqueles de junções de laço
aninhadas parametrizadas. Como o valor desses parâmetros pode
mudar muitas vezes durante a execução da consulta, a remoção de
partição é executada sempre que um dos parâmetros de execução
que está sendo usado pela remoção de partição é alterado.
Determinar se as partições foram removidas durante esta fase
requer uma inspeção cuidadosa da propriedade
loops na saída do
EXPLAIN ANALYZE.
Os subplanos correspondentes a diferentes partições podem ter
valores diferentes, dependendo de quantas vezes cada um deles
foi excluído durante a execução. Alguns podem ser mostrados como
(never executed) se forem excluídos todas
as vezes.
A exclusão de partição pode ser desativada por meio do parâmetro de configuração enable_partition_pruning.
A exclusão de restrição é uma técnica de otimização de consulta semelhante à exclusão de partição. Embora seja usada principalmente para particionamento implementado usando o método de herança legado (antigo), pode ser usada para outros fins, inclusive com o particionamento declarativo.
A exclusão de restrição funciona de maneira muito semelhante à exclusão
de partição, exceto por usar as restrições CHECK de
cada tabela — o que lhe dá o nome — enquanto a exclusão de
partição usa os limites de partição da tabela, que existem apenas no
caso de particionamento declarativo.
Outra diferença é que a exclusão de restrição é aplicada apenas no
momento do planejamento; não há tentativa de excluir partições em
tempo de execução.
O fato de que a exclusão de restrição usa restrições
CHECK, o que a torna lenta em comparação com a
exclusão de partições, às vezes pode ser usado como vantagem:
como as restrições podem ser definidas mesmo em tabelas
particionadas declarativamente, em adição aos seus limites de
partição internos, a exclusão de restrição pode eliminar partições
adicionais do plano de consulta.
A configuração padrão (e recomendada) para
constraint_exclusion não é on
nem off, mas uma configuração intermediária chamada
partition, que faz com que a técnica seja aplicada
apenas nas consultas que provavelmente vão funcionar em tabelas
com particionamento usando herança.
A configuração on faz com que o planejador examine
as restrições CHECK em todas as consultas, mesmo
as simples, que provavelmente não serão beneficiadas.
As seguintes advertências se aplicam à exclusão de restrição:
A exclusão de restrição é aplicada apenas durante o planejamento da consulta, ao contrário da exclusão de partição, que também pode ser aplicada durante a execução da consulta.
A exclusão de restrição só funciona quando a cláusula
WHERE da consulta contém constantes
(ou parâmetros fornecidos externamente).
Por exemplo, uma comparação com uma função não imutável como
CURRENT_TIMESTAMP não pode ser otimizada,
porque o planejador não pode saber em qual tabela filha o valor
da função pode cair em tempo de execução.
Mantenha as restrições de particionamento simples, caso contrário o planejador pode não conseguir provar que as tabelas filhas podem prescindir de serem acessadas. Use condições de igualdade simples para particionamento de lista ou testes de intervalo simples para particionamento de intervalo, conforme mostrado nos exemplos anteriores. Uma boa regra geral é que as restrições de particionamento devem conter apenas comparações da(s) coluna(s) de particionamento com constantes usando operadores indexáveis de árvore-B, porque somente colunas indexáveis de árvore-B são permitidas na chave de partição.
Todas as restrições em todas as filhas da tabela-mãe são examinadas durante a exclusão de restrição, portanto um grande número de filhas provavelmente aumentará consideravelmente o tempo de planejamento da consulta. Com isso, o particionamento baseado no método de herança legado funcionará bem com até, talvez, uma centena de tabelas filhas; não tente usar com muitos milhares de tabelas filhas.
A escolha de como particionar uma tabela deve ser feita com cuidado, porque o desempenho do planejamento e execução da consulta pode ser afetado negativamente por um projeto ruim.
Uma das decisões de projeto mais críticas será a coluna, ou colunas,
pelas quais os dados vão ser particionados.
Frequentemente, a melhor escolha será particionar pela coluna, ou
conjunto de colunas, que mais comumente aparecem nas cláusulas
WHERE das consultas executadas na tabela
particionada.
As cláusulas WHERE, compatíveis com as
restrições de partição, podem ser usadas para remover partições
desnecessárias.
No entanto, pode ser necessário tomar outras decisões devido aos
requisitos para a restrição PRIMARY KEY ou
UNIQUE.
A remoção de dados indesejados também é um fator a ser considerado
ao planejar a estratégia de particionamento.
Uma partição inteira pode ser desanexada com bastante rapidez,
portanto pode ser benéfico projetar a estratégia de partição de
forma que todos os dados a serem removidos de uma só vez estejam
localizados em uma única partição.
Escolher o número alvo de partições em que a tabela deve ser dividida
também é uma decisão crítica a ser tomada.
Não ter partições suficientes pode significar que os índices
vão permanecer muito grandes ou que o local dos dados vai permanecer
ruim, o que pode resultar em baixas taxas de acertos de cache.
No entanto, dividir a tabela em muitas partições também pode causar
problemas.
Muitas partições podem significar tempos de planejamento de consulta
mais longos e maior consumo de memória durante o planejamento e a
execução da consulta, conforme descrito abaixo.
Ao escolher como particionar a tabela, também é importante considerar
quais mudanças podem ocorrer no futuro.
Por exemplo, se for escolhido ter uma partição por cliente, e
no momento houver um número reduzido de clientes grandes, considere
o que vai acontecer se daqui a vários anos houver um grande número
de clientes pequenos.
Nesse caso, pode ser melhor optar por particionar por
HASH e escolher um número razoável de partições
em vez de tentar particionar por LIST e esperar
que o número de clientes não o faça aumentar acima do que é prático
para particionar dados.
O subparticionamento pode ser útil para dividir ainda mais as partições que se espera que se tornem maiores do que outras partições. Outra opção é usar o particionamento de intervalo com várias colunas na chave de partição. Qualquer um desses pode facilmente levar a um número excessivo de partições, portanto se aconselha a tomar cuidado.
É importante considerar a sobrecarga do particionamento durante o planejamento e a execução da consulta. O planejador de consulta geralmente consegue lidar com hierarquias de partição com até alguns milhares de partições razoavelmente bem, desde que as consultas típicas permitam que o planejador de consulta remova todas as partições, exceto um pequeno número. Os tempos de planejamento se tornam mais longos e o consumo de memória se torna maior quando mais partições permanecem depois que o planejador executa a remoção de partições. Outra razão para se preocupar com um número grande de partições, é que o consumo de memória do servidor pode aumentar muito ao longo do tempo, principalmente se muitas sessões acessarem um grande número de partições. Isso se dá, porque cada partição requer que seus metadados sejam carregados na memória local de cada sessão que a acessa.
Com cargas de trabalho do tipo armazém de dados, pode fazer sentido usar um número maior de partições do que com uma carga de trabalho do tipo OLTP. Geralmente, em armazéns de dados o tempo de planejamento da consulta é menos preocupante, porque a maior parte do tempo de processamento é gasto durante a execução da consulta. Com qualquer um desses dois tipos de carga de trabalho, é importante tomar as decisões corretas antecipadamente, porque o reparticionamento de grandes quantidades de dados pode ser dolorosamente lento. As simulações da carga de trabalho pretendida são geralmente benéficas para otimizar a estratégia de particionamento. Nunca assuma que mais partições é melhor que menos partições, nem vice-versa.
[37] Intervalo: Em matemática, podemos representar conjuntos, subconjuntos e soluções de equações pela notação de intervalo. Intervalo significa que o conjunto possui cada número real entre dois extremos indicados, seja numérica ou geometricamente. Não é possível representar subconjuntos ou conjuntos que não sejam reais (ou contidos nos reais) pela notação de intervalo. (N. T.)
[38] Hashing: Esse capítulo usa um pequeno problema de contagem como pretexto para introduzir a estrutura de dados conhecida como tabela de dispersão ou hash table. Esta estrutura é responsável por acelerar muitos algoritmos que envolvem consultas, inserções e remoções de uma tabela de dados. (N. T.)