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

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.

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

Replies are listed 'Best First'.
Re^10: Architecture design for full stack development.
by anonymized user 468275 (Curate) on Jun 25, 2017 at 06:05 UTC
    OK I see the problem - the syntax in Postgres doesn't have a CLUSTERED attribute but uses a different syntax for that, namely CLUSTER ON in the create table/alter table syntax. Kind of makes sense in a way, because it affects the whole table persistingly to make an index clustered. The syntax for the current Postgres version for the case of ALTER TABLE can be found in https://www.postgresql.org/docs/9.6/static/sql-altertable.html

    In other words you make the table CLUSTER ON <index-name> rather than making the index clustered at index creating time like you do in most other DBMS's, whereas as I said before, the CLUSTER sql command doesn't do that at all - it only works per use without persistence.

    So yes, Postgres DOES have clustered indexes, it's just that the documentation isn't as clear as it could be and the syntax is a bit different from other popular DBMS's.

    Update: it doesn;t work as expected. The table will have to have CLUSTER applied periodically although it does have a clustered index in the sense of what happens with CLUSTER table (as opposed to CLUSTER index). So that is yet another problem for me to address after all. And you were right that PgAdmin led me to believe it had clustered indexes just like Sybase - it looked that way in terms of how the interface works.

    One world, one people

[reply]

      Update: Ah, didn't see your update in time. Good, we seem to (more or less) agree then :) /Update

      No, you are still mistaken (or, just possibly, do not put forward your point clearly enough).

      Let's read what's on the page that you mentioned:

      https://www.postgresql.org/docs/current/static/sql-altertable.html

      It says: 

      CLUSTER ON

    This form selects the default index for future CLUSTER operations.
+ It does not actually re-cluster the table.

      [download]

      So it says: 'future CLUSTER operations'. That means the next CLUSTER command. That is the CLUSTER ON command. On the html-page, that CLUSTER is actually a link into sql-cluster.html. This is the CLUSTER I was talking about all along (naturally, as there is no other CLUSTERing in postgres).

      So no, Postgres does not have clustered indexes, except in the sense of the CLUSTER ON command (which re-organises the underlying table on the basis of a /single/ index; and which is non-persistent (as you rightly say))

[reply]
[d/l]
        Yes and I am grateful to you for putting me right - I could have gone on thinking that the clustered index could be relied upon to persist rather than requiring scheduled cluster operations (e.g. a cron or autosys job) instead.

        One world, one people

[reply]

In Section Seekers of Perl Wisdom