0

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!

1 Answer 1

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.

1
  • You are correct on all cases. I cannot imagine that the data that was DELETEd was contiguous at all, much less contiguous at the end of the physical table storage structure. I could use the LOID values I DELETEd to compare to the values in the table where the vast majority of the blobs came from to validate that 100%, but I know that isn't necessary.
    – TheSQLGuru
    Mar 15, 2022 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.