bobocopy's questions

Estamos projetando um sistema que é conhecido por ser de leitura pesada (da ordem de dezenas de milhares de leituras por minuto).

• Há uma tabela namesque serve como uma espécie de registro central. Cada linha tem um textcampo representatione um exclusivo keyque é um hash MD5 daquele representation. ¹ Esta tabela tem atualmente dezenas de milhões de registros e espera-se que cresça em bilhões ao longo da vida útil do aplicativo.
• Existem dezenas de outras tabelas (de esquemas e contagens de registros altamente variados) que fazem referência à namestabela. Qualquer registro em uma dessas tabelas tem a garantia de ter um name_key, que é funcionalmente uma chave estrangeira para a namestabela.

_{1: Aliás, como você pode esperar, os registros nesta tabela são imutáveis ​​uma vez gravados.}

Para qualquer tabela que não seja a namestabela, a consulta mais comum seguirá este padrão:

SELECT list, of, fields 
FROM table 
WHERE name_key IN (md5a, md5b, md5c...);

Eu gostaria de otimizar o desempenho de leitura. Suspeito que minha primeira parada deve ser minimizar o tamanho dos índices (embora eu não me importe de ser provado errado lá).

A pergunta:
Qual é/são os tipos de dados ideais para as colunas keye ? Existe uma razão para usar mais ? ou ?name_key
hex(32)bit(128)BTREEGIN

tenho uma tabela articles:

                                                       Table "articles"
     Column     |            Type             |                     Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('articles_id_seq'::regclass) | plain    |              |
 user_id        | integer                     |                                                    | plain    |              |
 title          | character varying(255)      |                                                    | extended |              |
 author         | character varying(255)      |                                                    | extended |              |
 body           | text                        | default '--- []                                   +| extended |              |
                |                             | '::text                                            |          |              |
 created_at     | timestamp without time zone |                                                    | plain    |              |
 updated_at     | timestamp without time zone |                                                    | plain    |              |
 published_date | timestamp without time zone |                                                    | plain    |              |

Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "index_articles_on_published_date" btree (published_date)
    "index_rents_on_user_id" btree (user_id)
    "index_articles_on_user_id_and_published_date" btree (user_id, published_date)

Estamos no Postgres 9.4.4. A máquina tem 3,5 GB de memória e 150 GB de espaço em disco em um SSD.

_{Nota: O 'published_date' é sempre arredondado, pela aplicação, para a data mais próxima. Todas as horas/minutos/segundos são sempre 00. Legacy. Necessidades corrigidas. etc.}

Esta tabela tem centenas de milhões de artigos. A tabela recebe uma grande quantidade de consultas de leitura de (até 16) processos simultâneos executando as seguintes consultas tão rapidamente quanto nosso sistema responderá:

• uma contagem do número total de artigos

  SELECT COUNT(*) FROM articles;
  

• uma seleção de todos os artigos publicados para um determinado usuário

  SELECT * FROM articles WHERE user_id = $1;
  

• uma seleção do artigo publicado mais recentemente para um determinado usuário

  SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;
  

Estou descobrindo que, com um grande número de trabalhadores, essas consultas são bastante lentas. (No pico de carga, o primeiro leva minutos para ser concluído; os outros dois são da ordem de 10 segundos.) Em particular, parece que as consultas estão sendo enfileiradas.

A questão

Em resumo, as tabelas com apenas valores de largura fixa executam consultas de leitura melhor do que aquelas com larguras variadas? (Finja que o espaço em disco não é um problema.) No meu caso, estou me perguntando se veria uma melhoria de desempenho se extraísse o campo de texto 'corpo' para uma tabela separada e transformasse os campos de variação de caracteres em largura fixa campos de caracteres.

Admito que a pergunta é um pouco culta à carga. Eu simplesmente não sei o suficiente sobre os componentes internos do mecanismo de banco de dados Postgres para construir uma hipótese informada. Pretendo realizar experimentos reais com diferentes esquemas e configurações, mas gostaria de ter um modelo mental sólido de como o Postgres realmente funciona antes de prosseguir.

Pergunta relacionada

Onde posso aprender mais sobre os componentes internos do mecanismo de banco de dados Postgres? Eu pesquisei variações da pergunta acima com pouco sucesso. Quais são os termos corretos a serem usados ​​para esta pesquisa? Esse nível de documentação existe apenas na fonte e nas mentes dos DBAs Postgres? Também convido humildemente a sugestão de bons livros sobre o tema.