Disk space requirement for REINDEX in Postgresql

Question

There is a server with postres 13 running out of disk space.

It contains a table metrics that occupies 90% of the entire database. I have deleted old records in this table. Due to this table is used in production, I cannot run "VACUUM FULL metrics". Since I can't afford to block it.

After deleting old records I want to REINDEX CONCURRENTLY bloated indexes to free space. But first of all I would like to know how much space is needed to reindex them. If the server runs out of space Postgres will crash.

So, is it possible to calculate a space requirement for REINDEX in Postgresql?

Answer 1

Up to three times than the size of new index:

• index structure itself
• WAL records during create index
• temporary files while sorting index values

---

I have deleted old records in this table

It is possible to reclaim space using the pgcompacttable tool. pgcompacttable was designed specifically for compressing tables when there is not enough space for a copy of the table.

During an update or insert, postgresql will write the new version of the tuple to a free space at the beginning of the table. Therefore, if we somehow update all tuples at the end of the table, the subsequent vacuum will find only empty pages at the end of the table without actual (live) data and will return those pages to the file system. So the size of the table on disk will be reduced. pgcompacttable performs exacly such updates + vacuum.