How does pgcompacttable work in Postgresql?

Question

I would like to know how it reduces spaces. Certain forums mentions it does this by:

• Moving dead tuples to the front of the table. Essentially reusing dead tuples.
• Rebuilds the table and indices. Dead tuples are moved to the end of the table. A second step vaccum is required to reclaim the space. (using normal vaccum would just allow reclaimed spaces to be reusable within the table itself, not other tables).

The above two points may work together or contradict each other. This seems to be a popular solution but not well documented. Prefer not to read the perl code directly.

References:

• https://dataegret.com/2018/03/postgresql-bloatbusters/
• https://onesignal.com/blog/lessons-learned-from-5-years-of-scaling-postgresql/#database-upgrades

Answer 1

When updating a row, postgresql creates a new version of that row (mvcc). This new version of the row is written to the beginning of the table if there is free space somewhere.

If there are empty pages at the end of the table that do not contain live rows, the vacuum process can truncate those empty pages and return used space to the file system.

Knowing about these two features of postgresql, we wrote pgcompacttable. (I am a contributor):

• pgcompacttable runs normal update commands that don't change anything on the real data. But due to the implementation of mvcc, a new version of the row is created somewhere at the beginning of the table
• then we run vacuum, which cleans up the old versions of the rows, notices that there are empty pages at the end of the table, and returns them to the file system