CLUSTER(7)

💖 CLUSTER(7) — the PostgreSQL manual page, bestie edition 💖

okay so real talk: this is the CLUSTER page from the PostgreSQL 18.6 Documentation, and it's lowkey one of the most satisfying commands in the whole game. it literally reorganizes your table on disk. main character energy fr fr. let's get into it 🫶

---

✨ NAME ✨

CLUSTER — cluster a table according to an index

like... it takes your table and physically sorts it by an index. that's the whole vibe. 💅

---

✨ SYNOPSIS ✨

CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]

and option can be one of these:

VERBOSE [ boolean ]

---

🌸 DESCRIPTION 🌸

so basically, CLUSTER tells PostgreSQL to cluster the table you named in table_name, using the index you named in index_name. heads up: that index has to already exist on table_name — you can't just vibe your way into it, bestie. 🙈

when a table gets clustered, it gets physically reordered based on the index info. it's giving glow-up but for your disk pages ✨

now the important bit — clustering is a one-time operation. when the table gets updated later, those changes are not clustered. like, no attempt is made to store new or updated rows according to their index order. so if you want, you can periodically recluster by just issuing the command again. 🎀 also!! setting the table's fillfactor storage parameter to less than 100% can help preserve cluster ordering during updates, since updated rows stay on the same page if there's enough space there. iykyk.

when a table is clustered, PostgreSQL remembers which index it was clustered by. so the form CLUSTER table_name will recluster the table using the same index as before. iconic. 👑

you can also use the CLUSTER or SET WITHOUT CLUSTER forms of ALTER TABLE to set the index that'll be used for future cluster operations, or to clear any previous setting. your choice, your rules.

CLUSTER without a table_name reclusters all the previously-clustered tables in the current database that the calling user has privileges for. so like, all the ones you're allowed to touch. 🫶

⚠️ careful: this form of CLUSTER cannot be executed inside a transaction block. no cap.

and while a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. that means no other database operations — reads and writes — can operate on the table until the CLUSTER is finished. she's locked in. literally. 💀

---

🎀 PARAMETERS 🎀

• table_name — the name (possibly schema-qualified) of a table. yes bestie, you can drop the schema in there.
• index_name — the name of an index.
• VERBOSE — prints a progress report as each table is clustered, at INFO level. so you can watch her work. 🍓
• boolean — specifies whether the selected option should be turned on or off. you can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. and honestly? you can just omit the boolean value entirely, in which case TRUE is assumed. easy. ✨

---

💜 NOTES 💜

to cluster a table, you must have the MAINTAIN privilege on the table. that's the entry fee, bestie. 🎟️

so here's when you actually want this: in cases where you're accessing single rows randomly within a table, the actual order of the data in the table is unimportant — whatever, doesn't matter. however, if you tend to access some data more than others, and there's an index that groups them together, you will benefit from using CLUSTER. 🌈

and if you're requesting a range of indexed values from a table, or a single indexed value that has multiple rows matching, CLUSTER will help — because once the index identifies the table page for the first row that matches, all the other matching rows are probably already on the same table page. so you save disk accesses and speed up the query. ate. 💅

CLUSTER can re-sort the table using either:

• an index scan on the specified index, or
• if the index is a b-tree, a sequential scan followed by sorting.

it'll attempt to choose whichever method is faster, based on planner cost parameters and available statistical information. she's smart like that. 🧚

while CLUSTER is running, the search_path is temporarily changed to pg_catalog, pg_temp. noted. 📝

when an index scan is used, a temporary copy of the table is created containing 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. budget check!! 😭

when a sequential scan and sort is used, a temporary sort file is also created, so the peak temporary space requirement is as much as double the table size, plus the index sizes. this method is often faster than the index scan method, but if the disk space requirement is intolerable, you can disable this choice by temporarily setting enable_sort to off. 🫠

it is advisable to set maintenance_work_mem to a reasonably large value (but not more than the amount of RAM you can dedicate to the CLUSTER operation) before clustering. don't be greedy, bestie.

because the planner records statistics about the ordering of tables, it's advisable to run ANALYZE on the newly clustered table. otherwise the planner might make poor choices of query plans. give her the data she needs. 📊

and because CLUSTER remembers which indexes are clustered, one can cluster the tables one wants clustered manually the first time, then set up a periodic maintenance script that executes CLUSTER without any parameters, so that the desired tables are periodically reclustered. automation slay. 🔥

each backend running CLUSTER will report its progress in the pg_stat_progress_cluster view. see Section 27.4.2 for details. 👀

clustering a partitioned table clusters each of its partitions using the partition of the specified partitioned index. when clustering a partitioned table, the index may not be omitted. and CLUSTER on a partitioned table cannot be executed inside a transaction block. same rule as before, no exceptions. 🙅

---

🌟 EXAMPLES 🌟

cluster the table employees on the basis of its index employees_ind:

CLUSTER employees USING employees_ind;

cluster the employees table using the same index that was used before:

CLUSTER employees;

cluster all tables in the database that have previously been clustered:

CLUSTER;

---

🦋 COMPATIBILITY 🦋

there is no CLUSTER statement in the SQL standard. we're built different, that's all. 💁

the following syntax was used before PostgreSQL 17 and is still supported:

CLUSTER [ VERBOSE ] [ table_name [ USING index_name ] ]

the following syntax was used before PostgreSQL 8.3 and is still supported:

CLUSTER index_name ON table_name

---

⭐ SEE ALSO ⭐

clusterdb(1), Section 27.4.2

---

okay that's the whole page bestie, you're officially a CLUSTER expert, go reorder something 💖

PostgreSQL 18.6 · 2026 · CLUSTER(7) — mwah, thanks for reading, ily, go run ANALYZE afterward fr fr 🎀✨🦋