1

Let's say I want to CLUSTER a table. There's no way to know how long it will take so I just run it. After a couple hours, I need the data so I cancel the CLUSTER command. Does CLUSTERing work with temp tables/files such that if I cancel before it's finished, all of that is erased and the hours it ran are wasted or does it make changes "live" such that canceling after a few hours isn't a full waste of time?

3

If you called CLUSTER without parameters you cannot ROLLBACK at all since that is only applicable to transactions. Quoting the manual:

This form of CLUSTER cannot be executed inside a transaction block.

But you talk about "CLUSTER a table", so assuming you mean the form:

CLUSTER table_name [ USING index_name ]

Which is transactional. Cancelling this command should not do any harm. Postgres builds the new table (and indexes) in the background and only updates references to physical files when done. The manual again:

When an index scan is used, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes.

When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. [...]

Bold emphasis mine. But the work that went into the temp tables is lost when you cancel the command before it can finish.

CLUSTER does not even update table statistics, which is why you should probably run ANALYZE on the table after CLUSTER.

Concerning:

I need the data so I cancel the CLUSTER command.

You might be interested in pg_repack, which can do the same as CLUSTER without exclusive locks, so you can use it without painting yourself into a corner - or even when you cannot afford downtime at all.

The extension is not shipped with Postgres, though. You have to install it yourself. It's not available on many cloud services.

Related:

0

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.