in reply to Re^5: Architecture design for full stack development.
in thread Architecture design for full stack development.

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

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

Replies are listed 'Best First'.
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]
      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]

        There is no such thing as a 'clustered index' in postgres.

        Consequently, postgres has neither a CREATE INDEX nor an ALTER INDEX nor any other ALTER command to produce a 'clustered index'.

        If you think I am mistaken please show how you produce a 'clustered index', or a link to where such a thing is documented. I am really, really interested to learn something new about postgres.

[reply]

In Section Seekers of Perl Wisdom