Saturday, July 28, 2007

Native UUIDs in PostgreSQL

Stefan Arentz posted an entry on his blog, about a hack he's done for PostgreSQL. He's added a native UUID to the database. This is pretty slick. Most of your users will never notice but it can help on databases that need it. He lists some advantages on the google code page:

Database Size - Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records. The native UUID type uses 16 bytes of storage whereas a ascii uuid column uses 38 bytes.

Consistency - The native UUID type only accepts UUIDs that are well formatted. Of course you can also do this with a constraint check, but having a native type with a fixed format is more elegant in my opinion.

Transparent - Nobody is going to notice the difference. Because of the implicit typecasting you can use UUIDs as if they are ascii values.