I have a table that will contain some pre-computed data based off other tables. (Computing on the fly is too computationally expensive given the size of data I have to deal with.) I will be generating incrementally as source data is added. (I won't ever need to UPDATE it in normal usage; portions may be deleted and regenerated.) The table will be fairly large. It's currently about 50 million rows and will grow every year.

Most of the queries against this table will be filtered down by a foreign key ID column. As such, they perform better if all the rows for that ID are grouped into the same pages. I can guarantee this ordering on disk by creating an index and calling CLUSTER periodically, but this is obviously less than ideal as it would require some kind of scheduled task, coordinating against usage and other scheduled tasks, etc.

However, since I'm generating this data in chunks related to the foreign key I want to CLUSTER by, I can easily put an ORDER BY clause on the INSERT command:

INSERT INTO big_table (source_table1_id,a,b,c)
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

Will this affect the on disk storage order, grouping the rows into close to the minimum number of pages? And if it does, are there other processes that could mess up the on disk order later?

I am currently using PostgreSQL 9.3, but I'd like to know about newer versions as well for upgrades.

  • Not strictly on-topic, but for Oracle you would achieve this with a direct path (APPEND) insert having an "order by" clause. Good question, btw. Jan 23, 2017 at 10:26

4 Answers 4


Postgres physically writes tuples sequentially as INSERTed. If you do that to a new table or a table without dead tuples, you get just the same result you would achieve with CLUSTER on an index with the same sort order as your INSERT. The effect of CLUSTER deteriorates with later writes to the table in just the same way (and stays intact while you never DELETE or UPDATE - or INSERT breaking the desired order).

Some answers focus on those effects from later writes and miss the point of the question. The answer to your question is basically:

YES, inserting in order does have the same effect as clustering.

based on some conditions:

queries against this table will be filtered down by a foreign key ID column.

Meaning you access rows with the same FK ID at once, not a range of sequential IDs. Then all you need is rows clustered per ID, the physical order between IDs barely has any effect on these queries.


generating this data in chunks related to the foreign key

Meaning, "chunks" include all rows for the same FK ID in sequence. There are no other rows for the same FK ID inserted separately. Something like:

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT s1.source_table1_id, ... 
FROM   source_table1 s1
WHERE s1.source_table1_id BETWEEN 123 and 125  -- example
ORDER BY s1.source_table1_id


I won't ever need to UPDATE it in normal usage; portions may be deleted and regenerated.

The DELETE is the only mildly problematic part. If you never delete either, you'd be done here. If by "portions" you mean all rows with said ID at once, you are still good, mostly. While deleting and inserting in the same transaction, there is no fragmentation within IDs. (Deleted tuples are not "dead" yet and not overwritten in the same transaction.)

Dead tuples bloat the table, and later inserts can fill in physical holes, which is where fragmentation can start. The bloat from dead tuples has various accumulating adverse effects, but index access to rows with the same FK ID is mostly unaffected.

But all that is orthogonal* to your question, since the same considerations apply to CLUSTER.

Consider the community tools pg_repack or pg_squeeze. Either can replace CLUSTER, without exclusive lock on the table. See:



Rows will be processed in the guaranteed order, but it does not mean that after being inserted they will be located next to each other. This will only be possible if records are never deleted or updated in your table. Once you update or delete some rows, after vacuuming you will likely have free space in the middle of the table, where the next inserted record(s) will go.

Some extra details in this question.


No, it doesn't.

  • Clustering is done over an index.
  • Without an ORDER BY, insertion order can be totally random
  • With an ORDER BY an INSERT can still leave gaps because it doesn't rewrite the table.
  • Cluster forces a table rewrite.

Consider this..

SELECT random() AS bar, random() AS quz
FROM generate_series(1,100) AS t(x);

By implementation, this inserts the rows in the order they were generated. It's not guaranteed to, but it does currently. SQL guarantees nothing of the return order without an explicit ORDER BY. However, the data doesn't profess any useful order when generated like that, so that's merely trivial and not significant to query performance anyway. Now we can do this..

CREATE INDEX foo_bar_idx ON foo(bar);
CLUSTER foo USING foo_bar_idx;

Now the rows on foo are ordered by bar, this can make certain operations faster that use foo_bar_idx.

What happens if those rows already happen to be in that order. What happens if the index happens to line up with the row and clustering doesn't actually reorder anything? Then nothing happens. But, that's not a typical use case even without INSERT and DELETE. In PostGIS we insert data all the time, and we cluster complex tables of geometries by their bounding box. Bounding box comparisons are abstract, but it makes things that use them substantially faster.


If the table is never updated or deleted from, then the inserted rows will be physically ordered in their chronological insertion order. But if it does get deleted from or updated, then vacuuming the table will create free space holes in the table, and newly insert rows might be scattered where ever they fit into those holes. This would be less of a problem if deletions happen on large sets of data specified by a range on the same column you want ordered by. In that case, entire pages of data will be deleted together, freeing that space to be re-used together.

Your INSERT INTO...SELECT...ORDER BY is unlikely to be effective, because the ordering will only occur chunk-wise. Unless your chunks are very large, or the chunks themselves are processed in order as well being ordered within each chunk, chunk-wise ordering is unlikely to do you much good.

You could look at range partitioning your table on the sort key. That might solve the problem just by keeping similar values together. If not, it would at least make CLUSTERing each separate partition take a lot less time than CLUSTERing one giant table does, which might make it easier to schedule them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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