We use varchar for UUID (on postgres) which surprisingly hasn't been that terribly performance wise. And yes we do use varchar(36) although on postgres it doesn't really matter because I think almost all varchars are text.

I would love to switch to native UUID someday though.

we did the same and then switched to the native UUID type. it eliminates the need for a unique index and we saw a drop in storage space by 1/2. it's totally worth converting UUID to the uuid field.

ALTER TABLE my_table ALTER COLUMN my_uuid TYPE uuid USING uuid::uuid;

And you want as much of the index as possible in RAM. When the index is 10 times larger than necessary and do not fit in your RAM, you get a very expensive performance penalty!

Same, but we use raw binary columns (i.e. "BINARY(16)" for MySQL and "BYTEA(16)" for PostgreSQL).

Conversion between the database-native UUID type and the application-layer UUID type is often so flaky (particularly with Java and JPA).

All the `CHAR`s in Postgres are `TEXT`, from the docs:

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs and slower sorting. In most situations text or character varying should be used instead.

Yeah, VARCHAR and TEXT are backed by the same datatype, but VARCHAR(N) gives you a limit check (that can be a pain to change if you ever need to.) There shouldn't be any noticable performance difference. For UUIDs, the native type is way better.