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 INTO big_table (source_table1_id,a,b,c) SELECT source_table1_id, 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.