6 ms·

The article doesn't mention it when talking about clustered indexes, but you can force a table to be reordered on disk to match an index using CLUSTER. http://

• SigmundA 11y ago
  Unfortunately this is not the same as a clustered index as seen in other db's like MS SQL server. With a true clustered index, the table IS the index, this saves space and time by not having to maintain a secondary index and then looking up the result in the table. This can be very important especially with large datasets. Really wish true clustered indexes where implemented in PG. Kind of odd the article doesn't mention this while starting out explaining clustered vs non-clustered.
  • dsp1234 11y ago
    Indeed the issue that trips up many of the people I've worked with is that CLUSTER will make a table seem like the clustered index of other systems (in as much as it physically orders the table), but without an appropriately large fillfactor (and sometimes not even then), new rows and updates are not stored in indexes physical ordering. So at first glance it's super-speedy for clustered index-like queries, but then becomes slower and slower over time as the data changes and consecutive index reads are no longer sequential on disk.