Apenas para lembrá-lo, já discutimos questões relacionadas ao isolamento , fizemos uma digressão em relação à estrutura de dados de baixo nível e depois exploramos as versões de linha e observamos como os instantâneos de dados são obtidos a partir das versões de linha.

Agora, procederemos a dois problemas intimamente conectados: vácuo na página e atualizações HOT . Ambas as técnicas podem ser referidas a otimizações; eles são importantes, mas praticamente não são abordados na documentação.

Vácuo in-page durante atualizações regulares

Ao acessar uma página para atualização ou leitura, se o PostgreSQL entender que a página está ficando sem espaço, ele poderá executar um rápido vácuo na página. Isso acontece em qualquer um dos casos:

1. Uma atualização anterior nesta página não encontrou espaço suficiente para alocar uma nova versão de linha na mesma página. Essa situação é lembrada no cabeçalho da página e da próxima vez que a página for aspirada.
2. A página está mais do que a fillfactor do fillfactor de fillfactor cheia. Nesse caso, o vácuo é realizado imediatamente, sem adiar para o próximo.

fillfactor é um parâmetro de armazenamento que pode ser definido para uma tabela (e para um índice). O PostgresSQL insere uma nova linha em uma página apenas se a página estiver com menos do que o fillfactor de fillfactor de fillfactor cheio. O espaço restante é reservado para novas tuplas criadas como resultado de atualizações. O valor padrão para tabelas é 100, ou seja, nenhum espaço é reservado (e o valor padrão para índices é 90).

O vácuo na página exclui tuplas que não são visíveis em nenhuma captura instantânea (aquelas que estão além do horizonte de transações do banco de dados, discutido na última vez ), mas fazem isso estritamente em uma página da tabela. Ponteiros para tuplas aspiradas não são liberados, pois podem ser referenciados a partir de índices e um índice está em outra página. O vácuo na página nunca ultrapassa uma página da tabela, mas funciona muito rapidamente.

Pelas mesmas razões, o mapa de espaço livre não é atualizado; isso também reserva espaço extra para atualizações e não para inserções. O mapa de visibilidade também não é atualizado.

O fato de uma página poder ser aspirada durante as leituras significa que uma consulta SELECT pode implicar na alteração de páginas. Este é mais um caso como esse, além de uma alteração adiada dos bits de dica, discutida anteriormente.

Vamos considerar um exemplo de como isso funciona. Vamos criar uma tabela e índices nas duas colunas.

 => CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75); => CREATE INDEX hot_id ON hot(id); => CREATE INDEX hot_s ON hot(s); 

Se a coluna s armazenar apenas caracteres latinos, cada versão de linha ocupará 2004 bytes mais 24 bytes de um cabeçalho. Definimos o parâmetro de armazenamento do fillfactor como 75%, o que reserva espaço suficiente para três linhas.

Para examinar convenientemente o conteúdo da página da tabela, vamos recriar uma função já familiar adicionando mais dois campos à saída:

 => CREATE FUNCTION heap_page(relname text, pageno integer) RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid) AS $$ SELECT (pageno,lp)::text::tid AS ctid, CASE lp_flags WHEN 0 THEN 'unused' WHEN 1 THEN 'normal' WHEN 2 THEN 'redirect to '||lp_off WHEN 3 THEN 'dead' END AS state, t_xmin || CASE WHEN (t_infomask & 256) > 0 THEN ' (c)' WHEN (t_infomask & 512) > 0 THEN ' (a)' ELSE '' END AS xmin, t_xmax || CASE WHEN (t_infomask & 1024) > 0 THEN ' (c)' WHEN (t_infomask & 2048) > 0 THEN ' (a)' ELSE '' END AS xmax, CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu, CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot, t_ctid FROM heap_page_items(get_raw_page(relname,pageno)) ORDER BY lp; $$ LANGUAGE SQL; 

Vamos também criar uma função para examinar a página de índice:

 => CREATE FUNCTION index_page(relname text, pageno integer) RETURNS TABLE(itemoffset smallint, ctid tid) AS $$ SELECT itemoffset, ctid FROM bt_page_items(relname,pageno); $$ LANGUAGE SQL; 

Vamos verificar como funciona o vácuo na página. Para fazer isso, inserimos uma linha e a alteramos várias vezes:

 => INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; 

Existem quatro tuplas na página agora:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3979 (c) | 3980 (c) | | | (0,2) (0,2) | normal | 3980 (c) | 3981 (c) | | | (0,3) (0,3) | normal | 3981 (c) | 3982 | | | (0,4) (0,4) | normal | 3982 | 0 (a) | | | (0,4) (4 rows) 

Como esperado, acabamos de exceder o limite do fillfactor . Isso fica claro pela diferença entre o tamanho da página e upper valores upper : excede o limite igual a 75% do tamanho da página, o que gera 6144 bytes.

 => SELECT lower, upper, pagesize FROM page_header(get_raw_page('hot',0)); 

  lower | upper | pagesize -------+-------+---------- 40 | 64 | 8192 (1 row) 

Portanto, quando a página for acessada na próxima vez, o vácuo na página deve ocorrer. Vamos verificar isso.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | dead | | | | | (0,2) | dead | | | | | (0,3) | dead | | | | | (0,4) | normal | 3982 (c) | 3983 | | | (0,5) (0,5) | normal | 3983 | 0 (a) | | | (0,5) (5 rows) 

Todas as tuplas mortas (0,1), (0,2) e (0,3) são aspiradas; depois disso, uma nova tupla (0,5) é adicionada no espaço livre.

As tuplas que sobreviveram à aspiração são movidas fisicamente para endereços altos da página, de modo que todo o espaço livre seja representado por uma área contínua. Os valores dos ponteiros são alterados de acordo. Graças a isso, não há problemas com a fragmentação do espaço livre em uma página.

Os ponteiros para tuplas aspiradas não podem ser liberados, pois são referenciados na página de índice. Vamos olhar para a primeira página do índice hot_s (porque a página zero é ocupada por metainformações):

 => SELECT * FROM index_page('hot_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) 4 | (0,4) 5 | (0,5) (5 rows) 

Também vemos a mesma imagem no outro índice:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,5) 2 | (0,4) 3 | (0,3) 4 | (0,2) 5 | (0,1) (5 rows) 

Você pode observar que os ponteiros para as linhas da tabela seguem aqui em ordem inversa, mas isso não faz diferença, pois todas as tuplas têm o mesmo valor: id = 1. Mas no índice anterior, os ponteiros são ordenados pelos valores de s , e isso é essencial.

Com o acesso ao índice, o PostgreSQL pode obter (0,1), (0,2) ou (0,3) como identificadores de tupla. Ele tentará obter a versão da linha apropriada na página da tabela, mas devido ao status "morto" do ponteiro, o PostgreSQL descobrirá que essa versão não existe mais e a ignorará. (Na verdade, depois de descobrir que a versão de uma linha da tabela não está disponível, o PostgreSQL alterará o status do ponteiro na página de índice para não acessar mais a página da tabela.)

É essencial que o vácuo na página funcione apenas em uma página da tabela e não aspire as páginas de índice.

Atualizações HOT

Por que não é bom armazenar referências a todas as versões de linha no índice?

Primeiro, para qualquer alteração da linha, todos os índices criados para a tabela precisam ser atualizados: depois que uma nova versão é criada, ela precisa ser referenciada. E precisamos fazer isso em qualquer caso, mesmo se os campos forem alterados e não indexados. Obviamente, isso não é muito eficiente.

Segundo, os índices acumulam referências a tuplas históricas, que precisam ser aspiradas juntamente com as próprias tuplas (discutiremos um pouco mais adiante como isso é feito).

Além disso, a árvore B no PostgreSQL possui os detalhes de implementação. Se uma página de índice não tiver espaço suficiente para inserir uma nova linha, a página será dividida em duas e todos os dados serão distribuídos entre elas. Isso é chamado de divisão de uma página. No entanto, quando as linhas são excluídas, as duas páginas de índice não são mescladas em uma. Por isso, o tamanho do índice pode falhar na redução, mesmo que uma parte significativa dos dados seja excluída.

Naturalmente, quanto mais índices são criados em uma tabela, mais complexidades são encontradas.

No entanto, se um valor for alterado em uma coluna que não seja indexada, não faz sentido criar uma linha extra da árvore B que contenha o mesmo valor da chave. É exatamente assim que a otimização chamada atualização HOT ( atualização de tupla somente na pilha) funciona.

Durante essa atualização, a página de índice contém apenas uma linha, que faz referência à primeira versão da linha na página da tabela. E já está dentro da página da tabela que uma cadeia de tuplas é organizada:

• As linhas atualizadas que estão na cadeia são rotuladas com o bit Heap Hot Updated.
• Linhas que não são referenciadas no índice são rotuladas com bit de tupla somente heap.
• Como de costume, as versões de linha são vinculadas através do campo ctid .

Se durante a varredura de índice, o PostgreSQL acessa uma página de tabela e encontra uma tupla rotulada como Heap Hot Updated, entende que não deve parar, mas deve seguir a cadeia HOT, considerando cada tupla nela. Certamente, para todas as tuplas obtidas dessa maneira, a visibilidade é verificada antes de devolvê-las ao cliente.

Para observar como uma atualização HOT funciona, vamos excluir um índice e limpar a tabela.

 => DROP INDEX hot_s; => TRUNCATE TABLE hot; 

Agora refazemos a inserção e atualização de uma linha.

 => INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; 

E é isso que vemos na página da tabela:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 | t | | (0,2) (0,2) | normal | 3987 | 0 (a) | | t | (0,2) (2 rows) 

Há uma cadeia de alterações na página:

• O sinalizador Heap Hot Updated indica que a cadeia ctid deve ser seguida.
• O sinalizador Heap Only Tuple indica que essa tupla não é referenciada nos índices.

A cadeia crescerá (dentro da página) com outras alterações:

 => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 (c) | t | | (0,2) (0,2) | normal | 3987 (c) | 3988 (c) | t | t | (0,3) (0,3) | normal | 3988 (c) | 3989 | t | t | (0,4) (0,4) | normal | 3989 | 0 (a) | | t | (0,4) (4 rows) 

Mas há apenas uma referência ao chefe da cadeia no índice:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) (1 row) 

Para enfatizar, as atualizações HOT funcionam no caso em que os campos a serem atualizados não são indexados. Caso contrário, algum índice conteria uma referência diretamente a uma nova versão de linha, e isso é incompatível com o conceito dessa otimização.

A otimização funciona apenas em uma página e, portanto, uma caminhada adicional pela cadeia não precisa de acesso a outras páginas e não afeta o desempenho.

Vácuo in-page durante atualizações HOT

Aspirar durante as atualizações HOT é um caso especial, mas importante, de aspiração na página.

Como antes, já ultrapassamos o limite do fillfactor , portanto, a próxima atualização deve causar um vácuo na página. Mas desta vez há uma cadeia de atualizações na página. A cabeça desta cadeia HOT deve sempre permanecer onde está, pois é referenciada pelo índice, enquanto o restante dos ponteiros pode ser liberado: eles são conhecidos por não terem referências externas.

Para não tocar no ponteiro da cabeça, é utilizado o endereçamento indireto: o ponteiro referenciado pelo índice - (0,1) neste caso - adquire o status "redirecionar", que redireciona para a tupla apropriada.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | normal | 3989 (c) | 3990 | t | t | (0,2) (4 rows) 

Note que:

• As tuplas (0,1), (0,2) e (0,3) foram aspiradas.
• O ponteiro da cabeça (0,1) permanece, mas adquiriu o status "redirecionar".
• A nova versão da linha substituiu (0,2), pois não havia referências a essa tupla, com certeza, e o ponteiro foi liberado (status "não utilizado").

Vamos fazer uma atualização várias vezes mais:

 => UPDATE hot SET s = 'F'; => UPDATE hot SET s = 'G'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 (c) | 3991 (c) | t | t | (0,3) (0,3) | normal | 3991 (c) | 3992 | t | t | (0,5) (0,4) | normal | 3989 (c) | 3990 (c) | t | t | (0,2) (0,5) | normal | 3992 | 0 (a) | | t | (0,5) (5 rows) 

A próxima atualização causa aspiração in-page novamente:

 => UPDATE hot SET s = 'H'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 5 | | | | | (0,2) | normal | 3993 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | unused | | | | | (0,5) | normal | 3992 (c) | 3993 | t | t | (0,2) (5 rows) 

Novamente, algumas das tuplas são aspiradas e o ponteiro para a cabeça da corrente é movido de acordo.

Conclusão: se as colunas que não são indexadas são atualizadas com frequência, pode fazer sentido reduzir o parâmetro fillfactor para reservar algum espaço na página para atualizações. No entanto, devemos levar em consideração que, quanto menos o fillfactor , mais espaço livre resta em uma página, para aumentar o tamanho físico da tabela.

Quebra de uma corrente QUENTE

Se a página não tiver espaço livre para alocar uma nova tupla, a cadeia será interrompida. E teremos que fazer uma referência separada do índice para a versão da linha localizada em uma página diferente.

Para reproduzir essa situação, vamos iniciar uma transação simultânea e criar o instantâneo de dados nela.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT count(*) FROM hot; 

 | count | ------- | 1 | (1 row) 

O instantâneo não permitirá aspirar as tuplas na página. Agora vamos fazer uma atualização na primeira sessão:

 => UPDATE hot SET s = 'I'; => UPDATE hot SET s = 'J'; => UPDATE hot SET s = 'K'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 | t | t | (0,5) (0,5) | normal | 3996 | 0 (a) | | t | (0,5) (5 rows) 

Na próxima atualização, a página não terá espaço suficiente, mas o vácuo na página não poderá aspirar nada:

 => UPDATE hot SET s = 'L'; 

 | => COMMIT; -- snapshot no longer needed 

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 (c) | t | t | (0,5) (0,5) | normal | 3996 (c) | 3997 | | t | (1,1) (5 rows) 

Na tupla (0,5), há uma referência a (1,1), que está na página 1.

 => SELECT * FROM heap_page('hot',1); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+------+-------+-----+-----+-------- (1,1) | normal | 3997 | 0 (a) | | | (1,1) (1 row) 

Agora, existem duas linhas no índice, cada uma das quais aponta para o início de sua cadeia HOT:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (1,1) 2 | (0,1) (2 rows) 

Infelizmente, a documentação praticamente não possui informações sobre o vácuo na página e as atualizações HOT, e você deve procurar respostas no código-fonte. Eu aconselho você a começar com README.HOT .

Continue lendo .