Thread: using TEMP with the VACUUM function

using TEMP with the VACUUM function

From
Wing Kin Chong
Date:
Hi Team,

The VACUUM function currently doesn't use the system Environment Variables "TEMP" or "TMP"
So, if the database size is over 100 GB,
We have to make sure, there is 250 GB of free disk space,
You need 2.5 times free disk space on the database disk to run the VACUUM function.

Can your team look into this,
Allow the VACUUM function using the system Environment Variables "TEMP" or "TMP" please.

Regards

Wing CHONG
Senior Developer | Buildsoft 
p: 02 4626 4909  |  w: buildsoft.com.au
Connect with us via LinkedIn
  
Check out our instructional videos on Youtube
  
Connect with us on Facebook


MiTek Inc. and its subsidiaries are a global operating company that serves its residential and commercial customers in North America, Europe, Middle East, Africa, and Asia Pacific. Additional information about our corporate organization is available at www.mii.com/corporate-structure/. MiTek Holdings, Inc.. All Rights Reserved ---- This communication (including any attachments) contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying, or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. If the disclaimer can't be applied, take no action.

Re: using TEMP with the VACUUM function

From
"David G. Johnston"
Date:
On Sunday, June 30, 2024, Wing Kin Chong <Wing.Chong@mii.com> wrote:
Hi Team,

The VACUUM function currently doesn't use the system Environment Variables "TEMP" or "TMP"
So, if the database size is over 100 GB,
We have to make sure, there is 250 GB of free disk space,
You need 2.5 times free disk space on the database disk to run the VACUUM function.

Can your team look into this,
Allow the VACUUM function using the system Environment Variables "TEMP" or "TMP" please.

The vacuum full command is what you are describing, right?

Not going to happen - the added space is not for temporary data, it’s for the real new table being built.  We’d have to move the data at the end of the command if it was anywhere besides the data directory.  Though the additional space needed is whatever the size of the live tuples are, not 2.5 times the existing space that includes dead tuples.

David J.

Re: using TEMP with the VACUUM function

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, June 30, 2024, Wing Kin Chong <Wing.Chong@mii.com> wrote:
>> Allow the VACUUM function using the system Environment Variables "TEMP" or
>> "TMP" please.

> The vacuum full command is what you are describing, right?

> Not going to happen - the added space is not for temporary data, it’s for
> the real new table being built.  We’d have to move the data at the end of
> the command if it was anywhere besides the data directory.

Also, this would do exactly nothing to reduce the peak disk space
usage.  The fundamental problem for VACUUM FULL is that it can't
safely unlink the old table/index files until the new ones are fully
built and fsync'd.  Having a temporary copy somewhere else doesn't
alleviate that.  (In fact, if the "somewhere else" isn't on a
physically different file system, the third copy would make things
very much worse.)

            regards, tom lane



Re: using TEMP with the VACUUM function

From
Wing Kin Chong
Date:
Hi David and Tom,

Thanks for your assistance,

Yes, it's the vacuum full command on a Table,
which some client's databases are over 100 GB,
after the client deleted the info,
the database size is not Reduced until we run the vacuum full command,

Is there any way to find out the disk space we need to run the vacuum full command on the database (or a table)?

Regards

Wing CHONG
Senior Developer | Buildsoft 
p: 02 4626 4909  |  w: buildsoft.com.au
Connect with us via LinkedIn
  
Check out our instructional videos on Youtube
  
Connect with us on Facebook



From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, July 2, 2024 12:32 AM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Wing Kin Chong <Wing.Chong@mii.com>; pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Subject: Re: using TEMP with the VACUUM function
 
"David G. Johnston" <david. g. johnston@ gmail. com> writes: > On Sunday, June 30, 2024, Wing Kin Chong <Wing. Chong@ mii. com> wrote: >> Allow the VACUUM function using the system Environment Variables "TEMP" or >> "TMP"
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, June 30, 2024, Wing Kin Chong <Wing.Chong@mii.com> wrote:
>> Allow the VACUUM function using the system Environment Variables "TEMP" or
>> "TMP" please.

> The vacuum full command is what you are describing, right?

> Not going to happen - the added space is not for temporary data, it’s for
> the real new table being built.  We’d have to move the data at the end of
> the command if it was anywhere besides the data directory.

Also, this would do exactly nothing to reduce the peak disk space
usage.  The fundamental problem for VACUUM FULL is that it can't
safely unlink the old table/index files until the new ones are fully
built and fsync'd.  Having a temporary copy somewhere else doesn't
alleviate that.  (In fact, if the "somewhere else" isn't on a
physically different file system, the third copy would make things
very much worse.)
			regards, tom lane
MiTek Inc. and its subsidiaries are a global operating company that serves its residential and commercial customers in North America, Europe, Middle East, Africa, and Asia Pacific. Additional information about our corporate organization is available at www.mii.com/corporate-structure/. MiTek Holdings, Inc.. All Rights Reserved ---- This communication (including any attachments) contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying, or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. If the disclaimer can't be applied, take no action.