Re^5: Architecture design for full stack development.

CLUSTER [1] rewrites a table in the order of an index (to wit, the index you pass to the CLUSTER command). Access of singular values does not change; but access of values that are now (after CLUSTER) 'near' each other can become faster (because there is a higher chance they are already read). It has to do with /physical/ nearness (or proximity) of the table data, not of the values.

At least, that is my theory :)

Please don't ask me to submit test results -- I have a system to build!

Fair enough :) But I wouldn't undertake any work on the basis of your theory - I don't think it is valid.

I'll try to devise a test myself, and see if one of our theories can be falsified.

[1] CLUSTER command (PostgreSQL docs)

Comment on Re^5: Architecture design for full stack development.

Replies are listed 'Best First'.
Re^6: Architecture design for full stack development. by anonymized user 468275 (Curate) on Jun 23, 2017 at 18:15 UTC
I am not sure about a cluster command, I was referring to the 'CLUSTERED' attribute available when creating or altering an index. A clustered index is one which maintains organisation of the underlying data in index order. Postgres allows more than one index to be clustered; I first learned about clustered indexes on Sybase where only one index per table could be clustered for obvious reasons, but how Postgres manages more than one clustered index on the same table is a separate mystery. One world, one people	[reply]
Re^7: Architecture design for full stack development. by erix (Prior) on Jun 23, 2017 at 19:34 UTC
... to the 'CLUSTERED' attribute available when creating or altering an index. Where did you find that clustered attribute? Are you perhaps using some GUI that implements the CLUSTER with the INDEX display? There /is/ a clusterable attribute for indexes, but that's obviously not the same thing (the clusterable attribute just flags whether an index can possibly be used (as the /only/ index!) with a CLUSTER command). ... clustered indexes on Sybase where only one index per table could be clustered It's no different in PostgreSQL: CLUSTER accepts only a single index as parameter.	[reply]
Re^8: Architecture design for full stack development. by anonymized user 468275 (Curate) on Jun 25, 2017 at 05:15 UTC
I suggest you research more on clustered indexes which have been around for more than twenty years, whereas the Postgres 'cluster' command is not core knowledge in the field of DBMSs and I already said I am not talking about that so please stop pushing the cluster command on me - it isn't something I would use because I believe the index should be clustered or not from DDL rather than having to be clustered ad hoc with such an SQL command. Update: Put simply: - a clustered index is necessary to ensure the underlying data is maintained in index order (popular analogy is a phone book). - the cluster command does not create a clustered index but organises the data into index order one time per use. - you cannot use the cluster command to make the index clustered - you have to use an ALTER command to do that. One world, one people	[reply]
Re^9: Architecture design for full stack development. by erix (Prior) on Jun 25, 2017 at 05:47 UTC
Re^10: Architecture design for full stack development. by anonymized user 468275 (Curate) on Jun 25, 2017 at 06:05 UTC
Some notes below your chosen depth have not been shown here


Syntactic Confectionery Delight
	PerlMonks