Thread: CLUSTER, VACUUM, and TABLESPACEs (oh my)
Hi, I would like to request a little clarification on the CLUSTER and VACUUM commands. My use case here (partially) is when mydisk runs out of space and I want to move a table to a newly created tablespace. These questions somewhat overlap. Let’ssay I am starting with a table that is not CLUSTERed on a given index, but I want it to be. * If I run “CLUSTER table USING idx” on a table, is VACUUM FULL required/useful afterwards, or should I assume that the clusteroperation did the equivalent of a VACUUM FULL? * If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the orderspecified in the previous CLUSTER? * If I want to move a table to a new tablespace, is it possible to CLUSTER+VACUUM in the same step since the whole tablewill be rewritten anyway? This would be very useful in low-disk space scenarios. I did find this answer, but it’s datedseveral years ago and was hoping for something better supported. https://dba.stackexchange.com/a/87457/121020 The documentation is somewhat silent on these details, so I thought I’d ask here. Right now I move a table to a new tablespace,cluster on an index, and then do a full vacuum which results in three full copies of the table being written,which seems less than optimal where one should only be needed as far as I understand things. Cheers, Demitri
Hi,
Le lun. 25 janv. 2021 à 01:38, Demitri Muna <postgresql@demitri.com> a écrit :
Hi,
I would like to request a little clarification on the CLUSTER and VACUUM commands. My use case here (partially) is when my disk runs out of space and I want to move a table to a newly created tablespace. These questions somewhat overlap. Let’s say I am starting with a table that is not CLUSTERed on a given index, but I want it to be.
* If I run “CLUSTER table USING idx” on a table, is VACUUM FULL required/useful afterwards, or should I assume that the cluster operation did the equivalent of a VACUUM FULL?
The cluster operation is a VACUUM FULL with a sort step, so you don't need a VACUUM FULL after a CLUSTER.
* If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the order specified in the previous CLUSTER?
No, you still need CLUSTER.
* If I want to move a table to a new tablespace, is it possible to CLUSTER+VACUUM in the same step since the whole table will be rewritten anyway? This would be very useful in low-disk space scenarios. I did find this answer, but it’s dated several years ago and was hoping for something better supported. https://dba.stackexchange.com/a/87457/121020
No, but this is something being worked on. See https://commitfest.postgresql.org/31/2269/ for details.
The documentation is somewhat silent on these details, so I thought I’d ask here. Right now I move a table to a new tablespace, cluster on an index, and then do a full vacuum which results in three full copies of the table being written, which seems less than optimal where one should only be needed as far as I understand things.
Cheers,
Demitri
On 1/24/21 7:50 PM, Guillaume Lelarge wrote:
VACUUM FULL unclusters the table??
[snip]
* If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the order specified in the previous CLUSTER?No, you still need CLUSTER.
VACUUM FULL unclusters the table??
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Le lun. 25 janv. 2021 à 02:54, Ron <ronljohnsonjr@gmail.com> a écrit :
On 1/24/21 7:50 PM, Guillaume Lelarge wrote:[snip]
* If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the order specified in the previous CLUSTER?No, you still need CLUSTER.
VACUUM FULL unclusters the table??
It will rebuild the table without sorting the data according to the index used with CLUSTER (without any sorting actually).
> On Jan 24, 2021, at 9:03 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > >> VACUUM FULL unclusters the table?? > > It will rebuild the table without sorting the data according to the index used with CLUSTER (without any sorting actually). Thank you for the clarification; that’s very helpful. For the case above. If I CLUSTER a table, add/delete no rows, thenVACUUM, will the existing order remain or be changed? Demitri
Le lun. 25 janv. 2021 à 03:27, Demitri Muna <postgresql@demitri.com> a écrit :
> On Jan 24, 2021, at 9:03 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
>
>> VACUUM FULL unclusters the table??
>
> It will rebuild the table without sorting the data according to the index used with CLUSTER (without any sorting actually).
Thank you for the clarification; that’s very helpful. For the case above. If I CLUSTER a table, add/delete no rows, then VACUUM, will the existing order remain or be changed?
As long as you don't insert or update any rows, the order should remain even after a VACUUM FULL.