Início / dba / Perguntas / 330013
Accepted
Andrey B. Panfilov
Asked: 2023-08-07 09:42:01 +0800 CST

Quaisquer armadilhas/benefícios de criar PK com include(..) no PostgreSQL?

  • 772

O aplicativo está armazenando dados em cache de forma agressiva na memória e, para oferecer suporte à consistência (evitando a persistência de dados obsoletos), está fazendo algo como:

-- typical table structure:
create table t1 (
    id            varchar(16) primary key,
    version_stamp int4,
    ....
)

-- typical update statement
update t1 set
   version_stamp = version_stamp + 1,
   col1 = ?,
   col2 = ?,
...
where id = ? and version_stamp = ?

Se a atualização mencionada acima informar que nenhuma linha foi atualizada, isso significa que o aplicativo tentou persistir dados obsoletos e uma exceção foi lançada, e a ideia principal é prevenir ou, pelo menos, minimizar tais casos. Para isso, a aplicação realiza as seguintes consultas (por requisição, transação ou chamada de método):

select version_stamp from t1
  where id = ?

Se nenhuma linha foi retornada, isso significa que a linha foi excluída, se retornada version_stampfor diferente da version_stampmantida na memória, isso significa que estamos lidando com dados obsoletos.

A questão é: vale a pena definir chaves primárias como:

create unique index on t1(id) include(version_stamp)

Ou não nesse caso. O RPS típico para essas consultas é de cerca de 10k por segundo.

postgresql

2 respostas

  1. Best Answer

    A SELECTconsulta que você mostra pode se beneficiar muito de uma verificação somente de índice após a inclusão version_stampno índice PK. (Ou adicionando um índice multicoluna adicional para cobrir isso.) Isso está assumindo que sua tabela é VACUUM'ed o suficiente para permitir varreduras somente de índice.

    Para começar, o tipo de dadosvarchar(16) é uma escolha infeliz, idpois ocupa 17 bytes no disco. O espaço é normalmente alocado em blocos de 8 bytes, o que resulta em 7 bytes de preenchimento de alinhamento para 24 bytes no índice PK que você tem agora. O pior caso. (E possivelmente na tabela também.) Veja:

    bigint(8 bytes) ou mesmo uuid(16 bytes) teria um desempenho muito melhor. varcharAlém disso, o manuseio é um pouco mais caro para processar. Ver:

    A vantagem: adicionar um integerao índice PK dificilmente aumentará seu tamanho, pois pode ocupar 4 bytes dos 7 atualmente perdidos para preenchimento.

    Isso reduzirá um pouco os benefícios da "desduplicação de índice" - dependendo dos padrões de gravação típicos e do nível de simultaneidade. (Múltiplas versões simultâneas da mesma entrada PK agora podem ter um diferente version_stampe não podem ser compactadas.) Mas isso é uma observação lateral.

    Uma desvantagem muito mais importante : até agora, nada do que você divulgou na pergunta impede as atualizações do HOT para o UPDATEcomando exibido (ou similar). Adicionar version_stampao índice PK exclui atualizações HOT quando essa coluna é atualizada. O índice agora também requer uma atualização, o que pode adicionar custos para gravar operações e criar mais tabela e índice volumosos. Ver:

    O melhor curso de ação depende muito do quadro completo:

    • As atualizações HOT são possíveis agora?
    • Qual é a proporção de leituras e gravações? E qual é mais importante para você?
    • Você consegue autovacuummanter o mapa de visibilidade atualizado e lidar com o inchaço do índice?
    • Você pode mudar idpara um tipo de dados mais favorável?

    Observação: é assim que você mudaria seu PK:

    ALTER TABLE t1
  DROP CONSTRAINT t1_pkey
, ADD  CONSTRAINT t1_pkey PRIMARY KEY (id) INCLUDE (version_stamp);

    Ou alguma variante mais sofisticada, CREATE INDEX CONCURRENTLYse você não puder pagar por um longo bloqueio exclusivo na mesa. Ver:

    create unique index ... como você exibe está relacionado, mas diferente.

    • 4

  2. Provavelmente não. Se você encontrar o mesmo version_stamp usando uma varredura somente de índice, precisará acessar imediatamente essa página da tabela de qualquer maneira para fazer a atualização dela. A única vez que você evita esse IO é se encontrar um version_stamp diferente e, assim, lançar um erro. Mas isso provavelmente será muito raro e não vale a pena otimizar. (Além disso, se o carimbo de versão for diferente, provavelmente foi atualizado muito recentemente e, portanto, o bit do mapa de visibilidade foi limpo e você teve que visitar a página de qualquer maneira.)

    • 1

relate perguntas