DELETEd Lots of Rows from pg_largeobject, pg_freespacemap Curiosity

Question

postgres noobie, PG 9.4 (please don't say I need to update PG, thx)

Database has huge pg_largeobject (PGLOB) table (~450GB, 169.5M rows, lots of DML leading to ~95GB/~22% free space - all per pgstattuple. The PG server is running out of disk space.

Follow on to this post: Free Space in PGLOB Not Being Reused

I identified 25794 loids in PGLOB, totaling 3.4M rows, that could be DELETEd. I did so, using pg_unlink(loid). I then ran VACUUM ANALYZE, to make the DELETEd space on the millions of pages of the PGLOB table free for new data to be place into via INSERTs.

Here is a sampling of the output from this query, mid-way through the DELETE process:

SELECT avail, count(*) 
  FROM pg_freespace ('pg_largeobject') 
 GROUP BY avail 
 ORDER BY 1;

AVAIL	1stCOUNT		2ndCount	CountDIFF
0	15008		14157	-851
32	18137		17540	-597
.	.	many	similar	rows
1824	7212		6999	-213
1856	49009213		49008593	-620
1888	6399073		5561506	-837567
1920	9210		8976	-234
.	.	many	similar	rows
3904	5		10	5
3968	38956		48582	9626
4000	3		12	9
.	.	many	similar	rows
5984	1		13	12
6048	341		10117	9776
6080	19		88	69
TOTALs	5.62M		5.54M	-837567

To explain, AVAIL is the rough approx (1/256th of block size) bytes available on a page, the 1stCOUNT is the count of pages part wat through the DELETE process. The 2ndCount is the count of pages with each free space after all DELETEs were completed. The CountDIFF is the difference between 1st and 2nd COUNT.

What is VERY confusing to me is that I ABSOLUTELY expected the DIFFERENCE between the two counts to be ZERO. For example, take the 1888 AVAIL value. By DELETing data from various pages, I produced 837567 FEWER pages that had that amount free. PRESUMABLY, those pages should now have MORE free space, and those 837567 pages SHOULD have showed up as INCREASES to pages down the list that now have MORE AVAIL space.

Even more odd is that the 1888 page difference was the EXACT number of pages that appear to no longer be part of the PGLOB table. So non-1888 AVAIL pages had from zero to some few thousands of rows added or removed from the counts. And THOSE counts DID net to zero.

My understanding of VACUUM is that it does NOT "release pages from the table", but rather it simply makes freed space available to be reused by the same table:

PG 9.4 VACUUM

"Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table."

QUESTION 1: What am I not understanding with respect to the above?

QUESTION 2: FAR more importantly - can I DEFINITIVELY tell my client that those 837K (actually well over 1M total pages for the entire DELETE process) pages WILL be reused by the PGLOB table (as best as possible given blob chunk sizing and 8K page limit) BEFORE new file space will need to be added to the database to make room for new blobs?? This will alleviate the fear of crashing the PG server by completely filling the volume it is on.

UPDATE 1 20220315 13:06

Is it POSSIBLE, from a postgresql internals standpoint, that pages that have been put in a state (due to DELETEs) that ALL data they contain is now "free/reusable by the table" simply no longer show up in the FSM (or are returned by the pg_freespacemap function)? I would be VERY happy to find that this is true, because it means that my DELETEs have made 1M+ 8K pages inside the existing file structures for the PGLOB table available for new rows to be stored.

I made it reasonably far down the internals documentation and source code to know that there are a BUNCH of STATE fields in bitmaps for each page. And there is necessarily a HUGE amount of logic that controls the storage engine's decision process related to "find a page where this data can be stored".

Thanks in advance for your help!

FINAL UPDATE 20220319

I am now at a week past the big DELETE effort. The 430th 1GB file chunk for the database has not grown a single byte. I have also been watching the number of various pages with AVAIL bytes remaining shift around as new data has been INSERTed, UPDATEd, DELETEd and VACUUMed. So far so good!

Answer 1

What you are "missing" is the '(in most cases)'. The case where space is returned to the OS is when a large contiguous chunk of space at the very end of the table has become completely unused. In that case, VACUUM will truncate it off the end and return that space to the OS, and that seems to be what happened here. If you look at the total size of the table, you should see that it has shrunk.

This is a bit odd. You indicated that the table was growing a lot recently, which means the data at the end would be recent data. But that must mean the data you deleted was largely the most recent data, which is not how things usually work.