I'm switching right away. The notation doesn't really do anything for me, but that's fine. I've been using bit(128), but always suspected that of being unoptimal (for no particular reason).
I think bit(128) is quite efficient (OCTET_LENGTH() function shows me it's using 16 bytes).
Btw, here are the data types and format I've tried/considered to store GUID in:
- BYTEA (storing the raw bytes; storage = 4+16 = 20 bytes; attlen = -1)
- CHAR/VARCHAR(18) (i'm using "base192" with character set containing ASCII 64-255. storage = 4+18 = 22 bytes?; attlen = -1)
- CHAR/VARCHAR(22) (using base64, storage = 4+22 = 26 bytes?; attlen = -1)
- INET/CIDR (storage = 24 bytes?; attlen = -1)
- BIT(128) (storage = 16 bytes?; attlen = -1)
PostgreSQL hasn't included a datatype with attlen of exactly 16 bytes, so all of the above are "variable-length field". My considerations in choosing the appropriate type for storing GUID are as follow (sorted from most important to least important):
1. The ease/naturalness of inserting. INET/CIDR is the slight winner here. For VARCHAR(18)/VARCHAR(22) I have to create a guidhex_to_base192()/guidhex_to_base64() function, which is not a big deal. Of course, I can always create/represent GUID as base192/base64 from the start, in which case using VARCHAR(18)/VARCHAR(22) is very easy too. For BYTEA you have to use "\\000" escape codes in psql. I'm still having difficulty on how to insert BIT fields using DBD::Pg and bind_param().
2. "Ease to the eye", that is, they way PostgreSQL displays the data. For me, INET/CIDR wins here, though VARCHAR(22) looks equally nice too. VARCHAR(18) and BYTEA makes the display looks weird due to high ASCII characters and/or control characters. BIT(128) is just too long (and silly me, I can't seem to find an easy way to display BIT(128) columns as hex or normal strings).
Of course, we can use ENCODE(col, 'base64') to display BYTEA GUID column, but it's kind of annoying to having to write that all the time.
3. The compactness/efficiency of storage. Well, none of the above are the most efficient anyway. We'll have to wait until PostgreSQL officially supports INT16/INT128/BIGBIGINT/GUID/fixed BYTEA. So either one is ok to me. 16 vs 22-24 bytes are not that big a deal either. Also, disk space is getting cheaper every day.
4. Ease of incremental searching. Suppose we're creating a GUI app to let user type in an item by its ID. VARCHAR(22) is a winner here since it allows users to type in normal characters in the keyboard and still lets Pg uses index for searching using "WHERE col LIKE '...%'".
However, most "sane" database design would use another unique code for most entities that need to be typed in. 128bit (22 characters as base64) are just too long anyway.
5. The ease of migrating to future "real GUID" datatype. I think using INET/CIDR will be easiest, as I can just use some simple combination of builtin Pg string function. But this is a very minor issue since if we're using a "real GUID" in the future, we most probably can't use our old GUID anymore, due to different creation algorithm.
So in short, for GUID I now tend to use BYTEA or INET/CIDR. Storing as base192/base64 feels a little wasteful for me, since I can use ENCODE(...) to display binary data as base64 anyway. I find BIT(n) awkward to work with/not properly supported in most languages.
Howver, using INET/CIDR prevents me to use LIKE or ~. So I guess it's back to BYTEA for me.
-- dave
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
