|
|
|
|
|
|
by da_chicken
2950 days ago
|
|
|
You're correct that PostgreSQL has had clustered indexes for quite a long time. The only difference here is that PostgreSQL allows a HEAP table. That said, you do have to re-cluster a PostgreSQL and that requires an exclusive lock on the table, which is obviously not idea for a massive table. MySQL will always cluster on the PRIMARY KEY or (if there isn't one) the first UNIQUE key[0], but as far as I can tell it always clusters on write. I don't see any way to configure the padding of the clustered index like you can on SQL Server, so I'm not sure how this is accomplished. MySQL only supports SQL in procedures[1]. There is no PL/pgSQL[2] equivalent, and except for custom UDFs written in C/C++, there's no support for external procedures, either. PostgreSQL[3] supports PL/pgSQL, PL/Python, PL/Tcl, and PL/Perl in base, plus there's external modules for PL/Java, PL/Lua, PL/R, PL/sh, and PL/v8. [0]: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.h... [1]: https://dev.mysql.com/doc/refman/8.0/en/adding-functions.htm... [2]: https://www.postgresql.org/docs/current/static/plpgsql-overv... [3]: https://www.postgresql.org/docs/current/static/xplang.html |
|
|