Optimize your pg optimize

[albertlast]

I would like to notice that your pg table optimize( https://github.com/elkarte/Elkarte/blob/development/sources/database/DbTable-postgresql.php#L786 ) only refresh the planer stats but doesn't shrink the database/tables. For such like this you should look in the implmentation of smf: https://github.com/SimpleMachines/SMF2.1/blob/fb199859d60ea25820e6fea3d14e6edba4655b90/Sources/DbExtra-postgresql.php#L89 In the newer version of this file i droped the support for < 9.1 pg this is the reason why i ref this old version of this file.

In this coding are two different on the one side a full vacuum or the alternative version for very old pg version and some queries around this to get the size before and after the vacuum.

[Spuds]

Thanks for the links.

Do you have any examples of how much disk space is reclaimed in a real life forum? I'm just curious how inefficient the disk file becomes over time, especially since this function (VACUUM ANALYZE) runs weekly? (I think)

To me what is in place seems OK for a general maintenance function as it "releases" space within the existing disk file for new data, and my assumption is that the new data is going to be added, therefore the file will grow anyway so why go through the overhead of shrinking if with a "full"? Then again if we are talking about 50% of wasted space or something ...

[albertlast]

VACUUM and ANALYZE both run when the threshod is reached: https://www.postgresql.org/docs/9.5/static/routine-vacuuming.html#AUTOVACUUM From this point is no need to run by his self this commands (you should change the config of pg if there a issue).

An normal Vacuum reclaim only the storage back to pg, so that pg is able again to reuse this place. When you want to give the space back to the os you need to run a full 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. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

https://www.postgresql.org/docs/current/static/sql-vacuum.html

I got no infos how much space is given back when you use full vacuum on a populate board, but this form operation is use less and a full vacuum should not be done on a weekly basis.