4 ms·

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. > usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation. [0]: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html [1]: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html

SigmundA 3y ago
>You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.
- sgarland 3y ago
  > That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. Fair point. The speed increase is still decent [0], although if you don't need most of the tuples (and ignoring TOAST-ed columns), you could achieve similar results with a simple REINDEX. > Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc. Agreed. I keep trying to convince Postgres fanboys that it matters, to relatively little success. I think the tide is slowly turning as "thought leaders" have begun publishing blog posts on this. [0]: https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c2269f2b3eb9 https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c...

SigmundA 3y ago

>You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.

sgarland 3y ago
> That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. Fair point. The speed increase is still decent [0], although if you don't need most of the tuples (and ignoring TOAST-ed columns), you could achieve similar results with a simple REINDEX. > Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc. Agreed. I keep trying to convince Postgres fanboys that it matters, to relatively little success. I think the tide is slowly turning as "thought leaders" have begun publishing blog posts on this. [0]: https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c2269f2b3eb9 https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c...