Início / dba / Perguntas / 274563
Accepted
Majid
Asked: 2020-08-29 08:43:04 +0800 CST

Usar BIGINT e o dobro do espaço de armazenamento ou INTEGER mas com operações extras?

  • 772

Suponha que eu tenha uma tabela nomeada agencycom algumas colunas:

  internal_id(integer, unique)
, external_id(bigint, unique)
, name, location, created_at, ...

internal_ide external_idcada um é único e candidato a ser a coluna de chave primária.

Existem algumas outras tabelas (digamos A, B, C, D, E) que fazem referência a esta tabela. Suponha que cada uma dessas tabelas contenha milhões ou bilhões de linhas.

Normalmente tenho external_idquando preciso filtrar os A, B, C, D, Edados das tabelas.

Qual dos cenários a seguir é o melhor caminho a seguir, considerando o desempenho e o espaço de armazenamento:

  1. Use internal_idcomo chave primária em agencye como chave estrangeira em outras tabelas. Como esse campo ocupa 4 bytes de espaço de armazenamento, podemos economizar bilhões de bytes. Porém como normalmente tenho o external_id, tenho que fazer um extra JOINpara cada consulta como penalidade:
    SELECT A.* FROM A
         INNER JOIN agency ON A.internal_id=agency.internal_id
    WHERE agency.external_id=5;
  1. Use internal_idcomo chave primária em agencye como chave estrangeira em outras tabelas. Mas para me livrar de um extra JOIN, no meu aplicativo eu poderia mapear primeiro external_idcom internal_iduma consulta simples ( SELECT internal_id FROM agency WHERE external_id=5) e depois usar o fetched internal_idpara outra consulta simples:
    SELECT * FROM A
    WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

Ele tem melhor desempenho do que JOINconsiderar uma viagem extra de ida e volta entre o aplicativo e o banco de dados?

  1. esquecendo internal_ide usando external_idcomo chave primária e chave estrangeira, com a penalidade de mais 4 bytes extras por registro em cada outra tabelas ( A, B, C, D, E) e custo de bilhões de mais espaço de armazenamento ou operações de banco de dados potencialmente ainda mais lentas (por causa de arquivos de banco de dados maiores):
    SELECT * FROM A
    WHERE external_id=5

Atualizar:

  • agencyA tabela pode conter dezenas de milhares ou no máximo alguns milhões de linhas.
  • internal_ide external_idnão mudará com o tempo, mas outras colunas sem identidade raramente podem mudar.
  • Existem cerca de 5 a 7 tabelas relacionadas ( A, B, C, D, E, ...) que algumas delas podem ficar muito grandes ao longo do tempo, digamos alguns milhões de linhas por dia (bilhões de linhas ao longo de um ano)
postgresql performance

2 respostas

  1. A terceira opção pelo motivo que você descreve: você não precisa consultar agencysempre. Não é que a junção/pesquisa seja particularmente cara para consultas que retornam uma pequena quantidade de linhas, mas:

    • Seu banco de dados é mais fácil de trabalhar
    • Seu código é mais conciso e fácil de depurar
    • É mais fácil otimizar consultas que retornam mais do que algumas linhas
    • Evita a sobrecarga do sem sentido internal_idna agencymesa.

    Isso certamente vale 4 bytes/linha. Não estamos mais armazenando dados em fita, não é uma consideração tão grande quanto costumava ser.

    Você está certo, mas e quando as tabelas relacionadas contêm trilhões de linhas. Portanto, para 1 trilhão de linhas, são necessários mais 4 TB de dados. Isso não afeta o desempenho geral do banco de dados?

    Se você estiver lendo a tabela inteira, sim. Mas na maioria das vezes procuramos algumas dezenas/cem linhas no máximo. E por que estender a "trilhões" de linhas? Se você estiver lidando com esse volume, o hardware necessário não quebrará devido a 4 TB extras no heap.

    • 2
  2. Best Answer

    Suposições

    • agencytem menos linhas do que os "milhões e bilhões" que você menciona para outras tabelas. Muito abaixo do intervalo de integer: -2147483648 a +2147483647. Caso contrário, precisamos bigintpara internal_idcomeçar.

    • Mas agencyainda é grande. Caso contrário, não se preocupe com as otimizações de índice abaixo.

    • Ambos internal_ide external_idquase nunca mudam.

    • Os valores de ID são distribuídos aproximadamente uniformemente. Não poucas agências extremamente comuns e muitas muito raras. (Isso pode favorecer a otimização da consulta sem tradução de chave.)

    Eu consideraria uma combinação do cenário 1 e 2 , usando este estilo de consulta:

    SELECT *
FROM   A
WHERE  internal_id = (SELECT internal_id FROM agency WHERE external_id=5);

    A subconsulta encapsula a tradução de chave e pode ser usada como substituto para fornecer um literal internal_id. Também torna o trabalho do planejador de consultas um pouco mais simples ao envolver muitas junções.

    A menos que você reutilize internal_idpara muitas consultas subsequentes, uma pesquisa separada adiciona custos desnecessariamente para uma viagem de ida e volta separada ao servidor.

    Você pode encapsular a tradução de chave em uma função SQL simples:

    CREATE FUNCTION public.f_ext2int(_external_id bigint)
 RETURNS int
 LANGUAGE sql STABLE PARALLEL SAFE AS
'(SELECT internal_id FROM public.agency WHERE external_id = _external_id)';

    Então a consulta acima se torna:

    SELECT * FROM A WHERE internal_id = f_ext2int(5);

    A função pode ser "embutida" pelo planejador de consulta. Ver:

    Sugiro esta definição de tabela :

    CREATE TABLE agency (
  internal_id integer          -- PK is NOT NULL implicitly
, external_id bigint NOT NULL  -- NOT NULL, right?
-- , name, location, created_at, ...
, PRIMARY KEY (internal_id) INCLUDE (external_id)
, UNIQUE      (external_id) INCLUDE (internal_id)
);

    Isso fornece os índices cruciais (internal_id, external_id)e reforça (external_id, internal_id) as restrições que você mencionou, sem índices redundantes.

    O segundo ( UNIQUE (external_id) INCLUDE (internal_id)) destina-se a pesquisas inversas. Parece provável que você também precise disso. Caso contrário, você pode pular a INCLUDEcláusula lá. Por que precisamos de ambos os índices? Ver:

    Faz uso pesado de índices de cobertura (Postgres 11 ou posterior). Ver:

    Entre outras coisas, os índices de cobertura negam o lastro de colunas adicionais agencypara fins de tradução de chave.

    Com esses índices em vigor, a tradução de chave se reduz a varreduras apenas de índice muito rápidas para tradução de chave. O custo será praticamente insignificante no contexto de consultas em suas tabelas enormes.

    Isso economiza "milhões e bilhões" vezes 4 bytes para cada tabela e índice adicional (o que pode importar muito mais). É verdade que o armazenamento está ficando cada vez mais barato, mas a RAM (e a memória cache rápida!) ainda é normalmente limitada. Tabelas e índices maiores significam que uma quantidade menor pode ficar no cache. E isso é crucial para o desempenho.

    Linhas mais largas sempre afetam o desempenho geral do banco de dados de forma mais ou menos negativa, mesmo com armazenamento barato. Discussão relacionada:

    E normalmente é muito mais fácil para o olho humano operar com integernúmeros menores nas muitas tabelas (e arquivos de log e depuração, ...). Pode até ser o benefício prático mais importante.

    • 2

relate perguntas