Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

591
Visualizações
How to decrease size of a large postgresql table

I have a postgresql table that is "frozen" i.e. no new data is coming into it. The table is strictly used for reading purposes. The table contains about 17M records. The table has 130 columns and can be queried multiple different ways. To make the queries faster, I created indices for all combinations for filters that can be used. So I have a total of about 265 indexes on the table. Each index is about 1.1 GB. This makes the total table size to be around 265 GB. I have vacuumed the table as well.

Question

  • Is there a way to further bring down the disk usage of this table?
  • Is there a better way to handle queries for "frozen" tables that never get any data entered into them?
over 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

If your table or indexes are bloated, then the VACUUM FULL tablename might shrink them. But if they are not swollen, this will not do any good. This is not a benign operation, it will lock the table for a period of time (you'll need to rebuild hundreds of indexes, probably a long period of time), and it will generate large amounts of IO and WAL, the latter of which will be especially problematic for replicas. So I'd try it on a non-production clone to see if it really slows things down and see how long of a maintenance window you'll need to declare.

Other than that, be more judicious in your choice of indices. How did you get the list of "all combinations of filters that can be used"? Was it inspecting your source code, or just tackling slow queries one by one until you ran out of slow queries? Maybe you can look at snapshots of pg_stat_user_indexes taken a few days apart to see if they are actually being used.

Are they mostly two column indexes?

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda