Este artigo discutirá os recursos fornecidos pelo particionamento embutido ou declarativo na versão 12 do PostgreSQL. A demonstração foi preparada para a
palestra homônima na
conferência HighLoad ++ Siberia 2019 (atual: um
vídeo apareceu com a palestra).
Todos os exemplos são executados na versão beta exibida recentemente:
=> SELECT version();
version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)
Os exemplos usam as tabelas de reservas e tickets do banco de dados demo. A tabela de reservas contém entradas para três meses de junho a agosto de 2017 e tem a seguinte estrutura:
=> \d bookings
Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Uma reserva pode incluir vários ingressos. A estrutura da tabela com tickets:
=> \d tickets
Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Esta informação deve ser suficiente para entender exemplos nos quais tentaremos criar tabelas particionadas.
→ Saiba mais sobre a base de demonstração
aqui.Particionamento de Gama
Primeiro, tente fazer a tabela de reservas particionada por período. Nesse caso, a tabela seria criada assim:
=> CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);
Seções separadas para cada mês:
=> CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);
Para indicar os limites de uma seção, você pode usar não apenas constantes, mas também expressões, por exemplo, uma chamada de função. O valor da expressão é calculado no momento em que a seção é criada e armazenada no diretório do sistema:
=> CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));
Descrição da tabela:
=> \d+ bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')
Isso é o suficiente. Nenhum gatilho para inserir registros, sem restrições de verificação necessárias. O parâmetro CONSTRAINT_EXCLUSION também não é necessário, você pode até desativá-lo:
=> SET constraint_exclusion = OFF;
Preenchendo com layout automático em seções:
=> INSERT INTO bookings_range SELECT * FROM bookings;
INSERT 0 262788
A sintaxe declarativa ainda oculta as tabelas herdadas, para que você possa ver a distribuição das linhas nas seções por consulta:
=> SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;
tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)
Mas não há dados na tabela pai:
=> SELECT * FROM ONLY bookings_range;
book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)
Verifique a exclusão de seções no plano de consulta:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)
Digitalizando apenas uma seção, conforme o esperado.
O exemplo a seguir usa a função to_timestamp com a categoria de variabilidade STABLE em vez de uma constante:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)
O valor da função é calculado quando o plano de consulta é inicializado e parte das seções são excluídas da visualização (linha de Subplanos removidos).
Mas isso funciona apenas para SELECT. Ao alterar dados, a exclusão de seção com base nos valores das funções STABLE ainda não foi implementada:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)
Portanto, você deve usar constantes:
=> EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;
QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)
Classificação do índice
Para executar a consulta a seguir, é necessário classificar os resultados obtidos de diferentes seções. Portanto, no plano de consulta, vemos o nó SORT e o alto custo inicial do plano:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)
Crie um índice em book_date. Em vez de um único índice global, os índices são criados em cada seção:
=> CREATE INDEX book_date_idx ON bookings_range(book_date);
=> \di bookings_range*
List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)
A consulta anterior com classificação agora pode usar o índice na chave de partição e retornar o resultado de diferentes seções imediatamente na forma classificada. O nó SORT não é necessário e o custo mínimo é necessário para produzir a primeira linha do resultado:
=> EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)
Os índices particionados criados dessa maneira são suportados centralmente. Ao adicionar uma nova seção, um índice será criado automaticamente nela. E você não pode remover o índice de apenas uma seção:
=> DROP INDEX bookings_range_201706_book_date_idx;
ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.
Apenas no todo:
=> DROP INDEX book_date_idx;
DROP INDEX
CRIAR ÍNDICE ... CONCURRENTEMENTE
Ao criar um índice em uma tabela particionada, você não pode especificar CONCURRENTLY.
Mas você pode fazer o seguinte. Primeiro, criamos o índice apenas na tabela principal, ele receberá o status inválido:
=> CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ f (1 row)
Em seguida, crie índices em todas as seções com a opção CONCURRENTLY:
=> CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);
Agora, conectamos índices locais ao global:
=> ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;
Isso é semelhante à conexão de tabelas de partição, que veremos um pouco mais adiante. Assim que todas as seções do índice estiverem conectadas, o índice principal mudará seu status:
=> SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';
indisvalid ------------ t (1 row)
Conectar e desconectar seções
A criação automática de seções não é fornecida. Portanto, eles devem ser criados com antecedência, antes que os registros com novos valores da chave de particionamento sejam adicionados à tabela.
Criaremos uma nova seção enquanto outras transações estiverem trabalhando com a tabela, ao mesmo tempo veremos os bloqueios:
=> BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');
count ------- 5 (1 row)
=> SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)
O bloqueio do AccessShareLock é imposto na tabela principal, em todas as seções e índices no início da instrução. O cálculo da função to_timestamp e a exclusão de seções ocorrem posteriormente. Se uma constante fosse usada em vez de uma função, apenas a tabela principal e a seção bookings_range_201707 seriam bloqueadas. Portanto, se possível, especifique constantes na solicitação - isso deve ser feito, caso contrário, o número de linhas em pg_locks aumentará proporcionalmente ao número de seções, o que pode levar à necessidade de aumentar max_locks_per_transaction.
Sem concluir a transação anterior, crie a seguinte seção para setembro em uma nova sessão:
|| => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';
relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)
Ao criar uma nova seção, o bloqueio ShareUpdateExclusiveLock, compatível com o AccessShareLock, é imposto na tabela principal. Portanto, as operações de adição de partições não entram em conflito com as consultas em uma tabela particionada.
=> COMMIT;
|| => COMMIT;
O particionamento é feito com o comando ALTER TABLE ... DETACH PARTITION. A seção em si não é excluída, mas se torna uma tabela independente. Os dados podem ser baixados dele, excluídos e, se necessário, reconectados (ATTACH PARTITION).
Outra opção para desativar é excluir a seção com o comando DROP TABLE.
Infelizmente, as duas opções, DROP TABLE e DETACH PARTITION, usam o bloqueio AccessExclusiveLock na tabela principal.
Seção padrão
Se você tentar adicionar um registro para o qual uma seção ainda não foi criada, ocorrerá um erro. Se esse comportamento não for desejado, você pode criar uma seção padrão:
=> CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;
Suponha que, ao adicionar registros, eles misturassem a data sem especificar um milênio:
=> INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1
Observamos que a frase RETURNING retorna uma nova linha, que se enquadra na seção padrão.
Após definir a data atual (alterando a tecla de particionamento), o registro se move automaticamente para a seção desejada, não sendo necessários acionadores:
=> UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;
tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1
Seccionamento da lista de valores
No banco de dados de demonstração, a coluna book_ref deve ser a chave primária da tabela de reservas. No entanto, o esquema de particionamento selecionado não permite criar essa chave:
=> ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);
ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.
A chave de particionamento deve ser incluída na chave primária.
Para dividir por meses e ainda incluir book_ref na chave primária, vamos tentar outro esquema para particionar a tabela de reservas - de acordo com a lista de valores. Para fazer isso, adicione a coluna redundante book_month como a chave da partição:
=> CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);
Vamos formar seções dinamicamente com base nos dados da tabela de reservas:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
Aqui está o que aconteceu:
=> \d+ bookings_list
Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')
Preenchendo o layout nas seções:
=> INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;
INSERT 0 262788
Como um retiro. Para preencher automaticamente book_month, é tentador usar a nova funcionalidade das colunas da versão 12 - GERADAS SEMPRE. Infelizmente, eles não podem ser usados como chave de partição. Portanto, a tarefa de preencher o mês deve ser resolvida de outras maneiras.
Restrições de integridade como CHECK e NOT NULL podem ser criadas em uma tabela particionada. Como na herança, especificar INHERIT / NOINHERIT indica se a restrição deve ser herdada em todas as tabelas de partição. Padrão INHERIT:
=> ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;
=> \d bookings_range
Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)
=> \d bookings_range_201706
Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)
Uma restrição EXCLUDE só pode ser criada localmente em partições.
Uma pesquisa em book_ref será exibida em todas as seções, mas por índice, graças ao fato de book_ref ser listado primeiro:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';
QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)
Uma pesquisa em book_ref e um intervalo de seções deve procurar apenas no intervalo especificado:
=> EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)
O comando INSERT ... ON CONFLICT localiza corretamente a seção desejada e executa a atualização:
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1
=> INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;
tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1
Chaves estrangeiras
No banco de dados de demonstração, a tabela de tickets refere-se a reservas.
Para tornar a chave estrangeira possível, adicione a coluna book_month e, ao mesmo tempo, divida-a em seções por mês, como bookings_list.
=> CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);
A restrição de CHAVE ESTRANGEIRA vale uma olhada mais de perto. Por um lado, esta é a chave estrangeira
da tabela particionada (tickets_list) e, por outro lado, é a chave
da tabela particionada (bookings_list). Portanto, chaves estrangeiras para tabelas particionadas são suportadas em ambas as direções.
Crie seções:
=> WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec
CREATE TABLE CREATE TABLE CREATE TABLE
=> \d+ tickets_list
Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')
Nós preenchemos:
=> INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);
INSERT 0 366733
=> VACUUM ANALYZE tickets_list;
Distribuição de linhas em seções:
=> SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;
tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)
Solicitações de conexão e agregação
Junte duas tabelas particionadas da mesma maneira:
=> EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)
Antes de iniciar uma conexão, cada tabela primeiro combina as seções que se enquadram na condição de consulta.
Mas você pode primeiro combinar as seções mensais correspondentes de ambas as tabelas e depois combinar o resultado. Isso pode ser alcançado ativando o parâmetro enable_partitionwise_join:
=> SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);
QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)
Agora, primeiro, as seções correspondentes das duas tabelas são unidas e, em seguida, os resultados da junção são combinados.
Uma situação semelhante com agregação:
=> EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)
Observe que as verificações de seção podem ser realizadas em paralelo. Mas primeiro as seções se reúnem, somente então a agregação começa. Como alternativa, você pode executar a agregação em cada seção e combinar o resultado:
=> SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;
QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)
Esses recursos são especialmente importantes se parte das seções forem tabelas externas. Por padrão, ambos estão desabilitados porque parâmetros apropriados afetam o tempo do plano, mas nem sempre podem ser usados.
Particionamento de hash
Uma terceira maneira de particionar uma tabela é o particionamento de hash.
Criando uma tabela:
=> CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);
Nesta versão do book_ref, como uma chave de particionamento, você pode declará-la imediatamente como uma chave primária.
Divida em três seções:
=> CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);
Preenchendo com layout automático em seções:
=> INSERT INTO bookings_hash SELECT * FROM bookings;
INSERT 0 262788
A distribuição das linhas nas seções ocorre uniformemente:
=> SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;
partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)
Novo comando para visualizar objetos particionados:
=> \dP+
List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)
=> VACUUM ANALYZE bookings_hash;
Subconsultas e junções de loop aninhadas
A exclusão de seção no tempo de execução é possível com conexões de loop aninhadas.
Distribuição das 10 primeiras reservas em seções:
=> WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;
partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)
Vejamos o plano de execução da consulta com a junção da tabela bookings_hash e a subconsulta anterior: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)
A conexão é feita usando o método de loop aninhado. O loop externo de acordo com a expressão geral da tabela é executado 10 vezes. Mas preste atenção ao número de chamadas para as seções da tabela (loops). Para cada valor book_ref do loop externo, apenas a seção é varrida onde esse valor é armazenado na tabela bookings_hash.Compare com a exclusão de seção desativada: => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;
QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)
=> RESET enable_partition_pruning;
Se você reduzir a seleção para uma reserva, duas seções não estarão visíveis: => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;
QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)
Em vez de uma subconsulta, você pode usar a função retornando um conjunto com a categoria de variabilidade STABLE: => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;
=> EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;
QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)
Sumário
Resumindo, podemos dizer que o particionamento embutido ou declarativo no PostgreSQL 12 recebeu um rico conjunto de recursos e pode ser recomendado com segurança substituir o particionamento por herança.