I'm considering using a UUID as a primary / foreign key for my schema,
to help ensure portability of data in a multi-master context. Does
anyone have experience with this?
There's a project on Gborg (pguuid) to create a "native" UUID type, but
it looks stagnant (and I'd prefer to use PostgreSQL out of the box, if I
can). So I'm considering three possible representations:
* VARCHAR(36) or CHAR(36) containing the standard textual encoding
* NUMERIC(40,0) containing the 128-bit binary version of the UUID,
considered as an integer
* A pair of BIGINT columns, containing the two 64-bit halves of the
binary UUID, handled together as a two-column key.
Would any of these give reasonable performance (for joins of large
tables), compared to int4 IDs? Is any of them clearly any better or
worse than the others?
I'd appreciate any advice!
Vance
* I would certainly consider using a standard int4 sequence ID to join
the tables, with a separate mapping from ID to UUID. However, this
would require some effort when transferring data between databases, to
replace the IDs with UUIDs for transmission. The performance would have
to be quite a bit better to make the extra effort worthwhile.