Re^7: Architecture design for full stack development.

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

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

Replies are listed 'Best First'.
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
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]
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]
Re^11: Architecture design for full stack development. by erix (Prior) on Jun 25, 2017 at 06:30 UTC
Re^12: Architecture design for full stack development. by anonymized user 468275 (Curate) on Jun 25, 2017 at 06:55 UTC
Some notes below your chosen depth have not been shown here


Just another Perl shrine
	PerlMonks