Início / dba / Perguntas / 25129
Accepted
davetapley
Asked: 2012-09-29 14:43:00 +0800 CST

Como devemos tratar as linhas que não serão consultadas quando forem antigas no PostgreSQL?

  • 772

Temos uma tabela em um banco de dados PostgreSQL que cresce na ordem de milhões de linhas por dia.

Cada linha consiste em:

ID
Foreign user ID
Date and time
Other data

A data e a hora não são estritamente monotônicas com ID, mas são próximas.

Ao consultar esta tabela, estamos interessados ​​apenas em obter linhas para um determinado ID de usuário estrangeiro, com data e hora nas últimas duas semanas. Linhas com mais de duas semanas nunca serão consultadas, mas gostaríamos de mantê-las para fins de arquivamento.

Dado este caso de uso especial:

  1. Devemos ter um índice na coluna de data e hora?
  2. Existe alguma dica que podemos dar de que a data e a hora estão (quase) aumentando monotonicamente com o ID.
  3. Devemos tentar remover linhas com mais de duas semanas da tabela, isso provavelmente melhorará o desempenho?
postgresql performance

1 respostas

  1. Best Answer
    1. Particionar e/ou usar índices parciais, para que você tenha apenas um índice para a parte que lhe interessa
    2. Não, AFAIK, ainda não há dicas de correlação na página. Pena.
    3. Depende muito do tipo de consulta, mas pode valer a pena fazer, principalmente se você puder fazer por particionamento.

    partição

    Este é um uso clássico para particionamento de tabelas. Particione a tabela em duas (ou mais) partes, uma com os dados quentes e outra com os dados de arquivamento antigos. Indexe tudo o que você precisa na mesa quente e use menos índices para economizar espaço e inserttempo na mesa fria.

    Com a exclusão de restrição ativada, o Pg verificará automaticamente apenas new_tablequando suas consultas corresponderem à restrição de particionamento.

    Se suas consultas não usarem todas a mesma restrição, você poderá adicionar um whereteste que use a restrição ou apenas consultar a new_tablepartição diretamente.

    índices parciais

    Como alternativa, você pode criar índices parciais que cobrem apenas os intervalos nos quais você está interessado. Novamente, eles só funcionarão se suas consultas usarem restrições que Pg pode reconhecer como correspondentes ao índice parcial. No seu caso, você teria que criá-los e soltá-los ao longo do tempo para manter seu intervalo de tempo relevante, já que você não pode indexar em uma expressão dinâmica como WHERE (some_field > current_timestamp - INTERVAL '2' week).

    • 6

relate perguntas