Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

589
Views
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 answers
Answer question

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!