Re: GUIDs

On Saturday, November 15, 2003, at 05:26  PM, Doug McNaught wrote:

> I think if I were in your place, I'd store them as TEXT fields using
> either the base64 or hex representation.

That was my original plan. But because the hex and base64
representations are 32 bytes instead of 16, it ends up being a lot less
efficient. Josh Berkus explains it to me like this:

<agliodbs> I mean, think of, for example, many-to-many join tables, or
tree tables, with millions of rows but just to id columns
<agliodbs> going from 32 bytes per row to 64 bytes will almost double
the size of the table and all of its indexes
<agliodbs> for example, let's take the join table/index example:
1000000 rows, with 2 ID colmuns
<agliodbs> now, usling a 16byte number, you have about 40bytes per row
(16+16+overhead)
<agliodbs> that's 40mb to load the table/index into memory
<agliodbs> but if you go with 32chars, that's about 76mb ... or abut
120mb for unicode
<agliodbs> hopefully you're not loading the whole thing into memory
often, but sometimes seq scans are necessary, and as much as 1/3 of the
table could end up in memory
<agliodbs> in addition to the memory load, it takes longer to get 40 mb
off disk than it does to take 13mb
<agliodbs> and longer to sync it to disk, and longer to vacuum it

So I'm inclined, I think, to use BYTEA as Peter originally suggested (I
can't get pguuid to compile for PostgreSQL 7.3.3 or 7.4RC2) and convert
it to other representations as needed on the API.

Regards,

David

--
David Wheeler                                     AIM: dwTheory
david@kineticode.com                              ICQ: 15726394
http://www.kineticode.com/                     Yahoo!: dew7e
                                                Jabber: Theory@jabber.org
Kineticode. Setting knowledge in motion.[sm]