Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

590
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda