Most of the time, PostgreSQL stores rows on disk in whatever order they happened to be inserted, and it’s up to indexes to help find them quickly regardless of physical order. But sometimes physical order actually matters for performance — particularly when you’re frequently scanning ranges of data that correlate with a particular index. That’s the exact problem the CLUSTER command solves.
In this article I’ll explain what CLUSTER does, how it physically reorganizes a table, its full syntax, when it actually helps performance, and the trade-offs and maintenance considerations that come with using it.
What Is the CLUSTER Command?
CLUSTER physically reorders the rows in a table on disk to match the order of an index. Once you cluster a table on a given index, rows that are close together in index order end up close together on disk too. This can dramatically speed up queries that scan ranges of data using that index, because PostgreSQL can read fewer, more sequential disk pages instead of jumping around.
It’s important to understand that this reordering is a one-time physical operation — PostgreSQL does not keep the table clustered automatically as new rows come in. Newly inserted or updated rows go back to being appended wherever there’s free space, meaning the “clusteredness” degrades over time and needs to be periodically redone if you want to keep the benefit.
Basic Syntax
CLUSTER [VERBOSE] table_name [ USING index_name ];
You can also cluster every previously-clustered table in the database at once:
CLUSTER [VERBOSE];
And there’s a simpler way to just re-cluster a table using whatever index it was already clustered on previously:
CLUSTER table_name;
Let’s go through the pieces.
table_name
The table you want to physically reorganize.
USING index_name
Specifies which index’s order to use when rewriting the table. The index must already exist on the table before you can cluster using it.
CLUSTER orders USING orders_customer_id_idx;
Omitting USING index_name
If you leave this out on a table that’s been clustered before, PostgreSQL remembers the index from the last time and reuses it:
CLUSTER orders;
If the table has never been clustered and you omit the index, PostgreSQL will throw an error since it doesn’t know which index to use.
VERBOSE
Prints progress information as the operation runs, which is useful on large tables so you can see it’s actually making progress rather than appearing to hang.
CLUSTER VERBOSE orders USING orders_customer_id_idx;
Clustering Everything at Once
CLUSTER;
This re-clusters every table in the current database that has previously been clustered (i.e., has a marked clustering index), skipping tables that have never been clustered. This is a handy maintenance command if you have several tables you regularly re-cluster and want to script a single catch-all command.
How CLUSTER Actually Works Internally
Under the hood, CLUSTER doesn’t just shuffle rows in place. It essentially builds a brand-new copy of the table, writing rows out in index order, then swaps this new copy in for the old one and drops the old version. This has a few important implications:
- It requires roughly double the disk space temporarily, since both the old and new copies of the table exist briefly during the operation.
- It takes an
ACCESS EXCLUSIVElock on the table for the duration of the operation, meaning reads and writes are both blocked until it finishes. - All indexes on the table get rebuilt as part of the process, not just the one you clustered on.
Because of the exclusive lock, running CLUSTER on a large, actively-used production table can cause a noticeable outage window. This is one of the most important operational considerations when planning to use it.
Practical Examples
Example 1: Clustering a Table by a Frequently Range-Queried Column
Suppose you have an orders table that’s frequently queried by customer_id in ranges (e.g., “get all orders for this customer, sorted”):
CREATE INDEX orders_customer_id_idx ON orders(customer_id);
CLUSTER orders USING orders_customer_id_idx;
After this runs, rows belonging to the same customer will be physically stored near each other on disk, which can meaningfully speed up queries like:
SELECT * FROM orders WHERE customer_id = 42 ORDER BY order_date;
Example 2: Clustering a Time-Series Table by Timestamp
Time-series data is a classic use case for clustering, since queries frequently scan date ranges:
CREATE INDEX events_created_at_idx ON events(created_at);
CLUSTER events USING events_created_at_idx;
Queries like SELECT * FROM events WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'; benefit from reading fewer, more sequential disk blocks.
Example 3: Re-Clustering a Table That’s Drifted Over Time
Since new inserts and updates don’t respect the clustered order, you’ll typically want to periodically re-run CLUSTER on tables where this matters:
CLUSTER VERBOSE orders;
Because no USING clause is specified, this reuses the index PostgreSQL remembers from the last clustering operation.
Example 4: Clustering Every Previously-Clustered Table
CLUSTER VERBOSE;
Handy as a single maintenance command if you have several tables that benefit from periodic re-clustering — for instance, as part of a low-traffic maintenance window job.
Example 5: Checking Which Index a Table Is Clustered On
SELECT
t.relname AS table_name,
i.relname AS index_name
FROM pg_index x
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
WHERE x.indisclustered = true;
This tells you which tables currently have a “remembered” clustering index, which is useful before running a bare CLUSTER; command with no arguments.
Common Use Cases
- Range-scan-heavy reporting tables, where queries commonly filter or sort by a specific column across a large range of values.
- Time-series data, where queries typically scan a contiguous date or timestamp range.
- Data warehousing / analytics tables that are bulk-loaded periodically and then read heavily between loads, making a post-load cluster operation practical.
- Tables with a dominant access pattern where nearly all queries go through the same index, making the physical reordering broadly beneficial rather than helping one query pattern at the expense of another.
When CLUSTER Is Not a Good Fit
It’s worth being honest about where CLUSTER doesn’t help:
- Frequently updated tables — the benefit erodes quickly as new rows get appended out of order, meaning you’d need to re-cluster often, which isn’t practical given the exclusive lock requirement.
- Tables queried through multiple, very different access patterns — clustering optimizes for one index’s order; queries that primarily use a different index won’t see much benefit and might even see slightly worse performance on writes due to the reorganized layout.
- Very large, high-availability tables where downtime is unacceptable — the
ACCESS EXCLUSIVElock makes this risky without a proper maintenance window.
Troubleshooting Common Issues
“There is no previously clustered index for table” Error
This happens when you run CLUSTER table_name; without a USING clause on a table that’s never been clustered before. You need to specify the index explicitly the first time:
CLUSTER orders USING orders_customer_id_idx;
The Operation Is Taking a Long Time / Appears Stuck
CLUSTER rewrites the entire table and rebuilds all its indexes, so on large tables this can genuinely take a while. Use VERBOSE to get progress feedback, and check pg_stat_progress_cluster (available in modern PostgreSQL versions) for real-time progress:
SELECT * FROM pg_stat_progress_cluster;
Application Errors Due to Locking
If your application throws timeout or lock-related errors while CLUSTER is running, that’s expected — it holds an ACCESS EXCLUSIVE lock, blocking all reads and writes on that table for the duration. Schedule clustering operations during low-traffic maintenance windows, and communicate the expected downtime to your team.
Disk Space Errors During Clustering
Since CLUSTER builds a full new copy of the table before dropping the old one, you need roughly double the table’s current disk usage available as free space. Check available space beforehand:
SELECT pg_size_pretty(pg_total_relation_size('orders'));
Compare this against free space on the relevant tablespace/disk before attempting to cluster a very large table.
Clustering Order Isn’t Maintained After New Inserts
This is expected, not a bug. CLUSTER is a one-time physical reorganization, not an ongoing constraint. If you need consistently clustered data over time, you’ll need to schedule periodic re-clustering, or consider alternative approaches like partitioning by the relevant column instead, which maintains physical separation automatically as new data arrives.
Best Practices
- Schedule
CLUSTERduring maintenance windows, given the exclusive lock it requires — never run it on a busy production table without planning for the downtime. - Monitor disk space before clustering large tables, since the operation temporarily needs roughly double the table’s size in free space.
- Use
VERBOSEon large tables so you have visibility into progress rather than wondering if the operation has hung. - Consider table partitioning as an alternative if you need consistently ordered access over time without repeated manual maintenance — partitioning by range naturally keeps related data physically separated as new rows arrive.
- Re-cluster periodically, not constantly. Since new writes degrade the clustered order over time, pick a sensible cadence (e.g., after a nightly bulk load, or weekly for slower-changing tables) rather than trying to keep it perfectly clustered at all times.
- Pick the right index to cluster on. Base your choice on your dominant, most performance-sensitive query pattern — clustering on the wrong index provides little benefit and still costs you the full reorganization overhead.
- Run
ANALYZEafter clustering. WhileCLUSTERrebuilds indexes, it’s good practice to refresh planner statistics afterward so the query planner has accurate information about the new physical layout.
CLUSTER Versus pg_repack: An Alternative Worth Knowing
Given that CLUSTER‘s biggest operational downside is its ACCESS EXCLUSIVE lock, it’s worth knowing about pg_repack, a popular third-party extension that accomplishes a similar physical reorganization (and can also cluster a table by an index) with dramatically less locking impact. It works by creating a new table, copying rows over while tracking ongoing changes via triggers, and then performing a much shorter locked swap at the very end, rather than locking the table for the entire rewrite.
pg_repack isn’t part of core PostgreSQL and needs to be installed separately, and it comes with its own set of trade-offs (extra disk I/O during the copy phase, additional trigger overhead on the source table while it runs). But for large, high-availability tables where even a planned maintenance window is difficult to arrange, it’s a widely used alternative worth evaluating before committing to a plain CLUSTER operation.
Monitoring Clustered Tables Over Time
Since clustering degrades as new rows are written, it’s useful to have a way of estimating how “out of order” a table has become relative to its clustering index, so you know when a re-cluster is actually worth the maintenance window. While PostgreSQL doesn’t expose a simple built-in “clustering correlation percentage,” the pg_stats view includes a correlation statistic for each column, which reflects how closely the physical row order matches the logical order of that column’s values:
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';
A correlation value close to 1 (or -1) means the physical order closely tracks that column’s sorted order — a good sign your clustering is still effective. A value drifting toward 0 suggests physical order has become essentially random relative to that column, meaning a re-cluster would likely restore meaningful performance benefit for range scans on it.
Clustering and Table Partitioning Together
If you’re working with a partitioned table (using PostgreSQL’s native declarative partitioning), it’s worth knowing that CLUSTER operates per-partition, not across the parent table as a single logical unit. You’d need to cluster each partition individually:
CLUSTER orders_2026_01 USING orders_2026_01_customer_id_idx;
CLUSTER orders_2026_02 USING orders_2026_02_customer_id_idx;
For many time-series use cases, partitioning by date range already achieves much of what clustering by a timestamp column would achieve — since each partition is a physically separate table already grouped by the ranges that matter for typical queries — which is one of the reasons partitioning is often recommended as a longer-term structural alternative to relying on periodic manual clustering.
Marking a Clustering Index Without Immediately Reorganizing
There’s a useful but often-overlooked variant of this workflow: you can tell PostgreSQL which index should be considered “the” clustering index for a table without actually performing the reorganization right away, using ALTER TABLE:
ALTER TABLE orders CLUSTER ON orders_customer_id_idx;
This doesn’t touch the physical row order at all — it just records the association, so that a later bare CLUSTER orders; (without a USING clause) knows which index to use. This is handy for setting up the intended clustering index ahead of a scheduled maintenance window, separating the (harmless, instant) declaration step from the (locking, potentially slow) actual reorganization step.
You can undo this association without affecting the table’s current physical order:
ALTER TABLE orders SET WITHOUT CLUSTER;
This just tells PostgreSQL to stop remembering a clustering index for this table — future bare CLUSTER table_name; calls will fail until you either specify USING explicitly again or re-mark it with ALTER TABLE ... CLUSTER ON.
How CLUSTER Interacts With Vacuum and Table Statistics
Since CLUSTER essentially rebuilds the table from scratch, it has a useful side effect: all dead tuples get discarded in the process, since only live, visible rows get written into the new physical copy. In that sense, CLUSTER acts a bit like VACUUM FULL combined with a physical reordering — you get both a fully compacted table and a clustered physical layout in a single operation.
That said, CLUSTER does not automatically update the query planner’s statistics the way ANALYZE would. It’s good practice to run ANALYZE immediately afterward:
CLUSTER orders USING orders_customer_id_idx;
ANALYZE orders;
Skipping this step means the planner continues operating on statistics that may not accurately reflect the table’s new physical layout and correlation characteristics, which is particularly relevant since clustering directly changes the correlation statistic for the clustered column, and the planner uses that statistic when deciding how much an index scan is likely to benefit from sequential disk access patterns.
Real-World Scenario: Clustering a Reporting Table After a Nightly ETL Load
A common practical pattern looks like this: a table is bulk-loaded once per night from an ETL pipeline, and then queried heavily throughout the following day by BI dashboards that almost always filter or sort by a report_date column. Since the table is essentially static during the day and completely rebuilt each night, clustering fits naturally into the maintenance window right after the load completes:
-- Runs as part of the nightly ETL job, after the bulk load finishes
TRUNCATE staging_reports;
INSERT INTO staging_reports SELECT * FROM source_reports;
CLUSTER staging_reports USING staging_reports_report_date_idx;
ANALYZE staging_reports;
Because the table isn’t being actively written to by users during this window, the exclusive lock CLUSTER requires has essentially no practical downside here, while the resulting physical layout meaningfully speeds up the day’s worth of read-heavy dashboard queries that follow.
Wrapping Up
CLUSTER is a niche but genuinely powerful tool for a specific problem: making range scans over an index faster by aligning physical row order with that index’s order. It’s not something you’ll use on every table, and it comes with real operational costs — an exclusive lock and temporary extra disk usage — but for the right table with the right access pattern, particularly time-series or reporting tables with a dominant index, it can produce a noticeable performance improvement.
Just remember it’s a one-time operation, not an ongoing guarantee, so factor periodic re-clustering into your maintenance routine if the performance gain matters enough to justify it.