Horton

Re: CLUSTER on partitioned index

Laurenz Albe <laurenz.albe@cybertec.at>

View thread View on postgr.es
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alvaro Herrera <alvherre@2ndquadrant.com>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Michael Paquier <michael@paquier.xyz>, 李杰(慎追) <adger.lj@alibaba-inc.com>, pgsql-hackers@lists.postgresql.org, 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, Zhihong Yu <zyu@yugabyte.com>
Date: 2021-07-21T11:01:11Z
Lists: pgsql-hackers 
On Tue, 2021-07-20 at 20:27 -0400, Alvaro Herrera wrote:
> I have to wonder if there really *is* a use case for CLUSTER in the
> first place on regular tables, let alone on partitioned tables, which
> are likely to be large and thus take a lot of time.  What justifies
> spending so much time on this implementation?  My impression is that
> CLUSTER is pretty much a fringe command nowadays, because of the access
> exclusive lock required.
> 
> Does anybody actually use it?

I see is used in the field occasionally, as it can really drastically
improve performance.  But I admit is is not frequently used.

In a data warehouse, which is updated only occasionally, running
CLUSTER after an update can make a lot of sense.

I personally think that it is enough to be able to cluster the table
partiton by partition.

Yours,
Laurenz Albe

Commits

  1. Add some isolation tests for CLUSTER

  2. Have CLUSTER ignore partitions not owned by caller

  3. Remove "recheck" argument from check_index_is_clusterable()

  4. Allow CLUSTER on partitioned tables

  5. doc: Add backlinks to progress reporting documentation