3 ms·

Did you try Clustering with PG (aka index organized table in Oracle)

• SigmundA 13y ago
  My understanding is PostgreSQL does not have true indexed organized tables (Clustered Index in MSSQL). You can create the table as clustered on an index and it will do it one time, if new data is inserted it is not clustered, you have to manually recluster exclusively locking it. Also this does not help performance nearly as much in PG because the index used to cluster is still a normal index and used normally with indirection. The only performance benefit comes from the fact that the data being searched for might be on the same page saving some disk I/O. A true clustered index the table IS the index, the table is seeked directly. This also saves disk space and some I/O since a separate index is not built redundantly storing the data. That being said an array should be even faster than a lookup to a secondary table with real clustered index since the array data would be in the row data of the main table incurring only the offset lookup to retrieve. There are definitely cases where hierarchical data structures are superior in performance to normalized ones. You gotta watch out though for hierarchical structures that store keys inline as the add their own space and I/O overhead (See shortening key names in Mongo DB). Arrays are a good choice though, they allow multi-value data inline without key overhead.