[boloust]

You're right to point at performance as the main motivator for this setup.

The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size.

UUID PKs are also more susceptible to fragmentation.

[tpetry]

Thats not how PostgreSQL works. The primary key is only included in every secondary key for MySQL. PostgreSQL secondary indexes directly point at the page and rowid.

[dspillett]

It works this way in SQL Server too, and some other DBs, if you have a clustered index (usually recommended). The clustering key is included in all non-clustered indexes on the table. Not that this doesn't mean NCIs inherit any extra fragmentation potential from the clustering key, as it is effectively INCLUDed and not considered by of the key of the supporting index.

Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.