in reply to Re^4: Architecture design for full stack development.
in thread 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]

      ... 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]
        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]

In Section Seekers of Perl Wisdom