News for hackers. Stuff that matters.

A comment on How PostgreSQL stores rows.

Re:How PostgreSQL stores rows by Izkata on Wednesday February 09, 2022 @08:44PM (#30281468)
> What if we run VACCUM ?

> VACCUM as expected defragmented page by moving the tuples around.

I wasn't paying full attention to the code blocks initially so these two statements confused me until I went back. VACUUM FULL is what was actually run - VACUUM would not have moved tuples around, but if VACUUM was run before the INSERT, then that empty spot from the DELETE could have been reused.

[ Parent | on HN ]
Re:How PostgreSQL stores rows by ants_a on Thursday February 10, 2022 @04:14AM (#30283955)
A normal vacuum will also defragment page contents, see PageRepairFragmentation()[1], which is called during normal vacuum from lazy_vacuum_page().

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

[ Parent | on HN ]
Re:How PostgreSQL stores rows by pgaddict on Thursday February 10, 2022 @06:40AM (#30284864)
Yeah, VACUUM only shuffles rows within a page, to maximize the amount of free space available for new data.

VACUUM FULL essentially rebuilds the segment files - it creates new files and shovels all rows from the old ones. In the past it was implemented differently, by actually moving rows (a bit like defrag tools in Windows) but that was very expensive and inefficient.

[ Parent | on HN ]