0

According to v12 docs

Cluster with index scan requires

free space on disk at least equal to the sum of the table size and the index sizes

and cluster with sequential scan and sort requires

as much as double the table size, plus the index sizes

Is there any way out if I do not have that much free space? i.e. can it be run in portions somehow?

If I run CLUSTER command and it fails because of insufficient free disk space will the performed work be entirely rolled back (when run with or without any parameter if it matters)?

1

yes postgresql would roll the transaction back.

Almost all commands in Postgresql are run in transaction so if something goes wrong the command will be rolled back.

If the Postgresql system tables and WAL are on the same disk do not run this command if disk space is this limited. Postgresql would panic and shutdown until space is freed up.

A Few things to keep in mind,

  1. Postgresql will not keep the table in that order so if the table is heavily updated. The physical sort order will be lost very quickly due to MVCC

  2. Have to run the cluster command on a regular bases to keep the table's physical order which recreates the limited disk space problem.

  3. The advantage for clustering is for read only queries that select record set in the order of the cluster.

If disk space is limited and the table is updated/inserted regularly in a short period of time the table and indexes will grow to a point the cluster can not be rerun and vacuum will not be able run

looks like its time to start planning on adding disk space and moving these tables to new TableSpaces

|improve this answer|||||
  • All commands run inside a transaction. – Laurenz Albe Feb 24 at 9:50
  • there are a couple of commands that are not such as init_db and utilities that are not protected. So i do not like telling people everything is protected by transactions because I had some one misconstrue that statement before. – zsheep Feb 24 at 12:24
  • Well, initdb is not an SQL statement. But it is good to err on the side of caution. – Laurenz Albe Feb 24 at 12:46
  • The docs say that CLUSTER without any parameter cannot be executed inside a transaction block. Would it still manage to roll back if it runs out of free disk space? – yaugenka Feb 24 at 16:11
  • you missing a big part here "reclusters all the previously-clustered tables in the current database that the calling user owns", What it is doing behind the scenes is it starts a new transactions for each table that would be recluster. Postgresql does not support NESTING transactions. So this is a block to keep from creating Nested Transactions. – zsheep Feb 24 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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