2

Consider this table used to store object metadata

CREATE TABLE meta (
  "obj_uuid"  uuid,
  "type"      int,
  "value"     text
);

Objects are identified using uuids as primary keys, the metadata row references that uuid. Now I am concerned this will blow up the DB size exponentially.

Does Postgres optimize this on itself, or would it be better to create an intermediate table that maps the uuid to a serial and reference this serial in the metadata table instead?

Improve this question
3
  • If a serial is adequate then why are you using uuid? Commented Aug 22, 2013 at 12:43
  • @ClodoaldoNeto because the UUID comes from CouchDB Commented Aug 22, 2013 at 12:47
  • This question on DBA.StackExchange might help you out. dba.stackexchange.com/questions/322/… Commented Aug 22, 2013 at 12:50

1 Answer 1

Reset to default
4

Now I am concerned this will blow up the DB size exponentially

A uuid is only 128 bits, as compared to a 64-bit bigint or 32-bit integer. So your worst case overhead is 4x the space for the key.

If your key/value pairings are very small and there are a great deal of them this might be a concern. In that case I'd use an integer or biginteger key based on a sequence, then just use the uuid as a unique secondary key that doesn't appear in referential integrity checks.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Actually, using UUID might save space in this case. Currently it takes 64b (main table) + 64b (PK index) + 64b (ref table). In the case you suggested it will take 32 + 64 (main table uuid + serial) 32 + 64 (serial and uniq indexes) + 32 (ref table).
@IgorRomanchenko UUIDs are 128 bits, not 64. Yes, if it was a 1:1 master to ref table relationship there'd be no benefit, but if there are many meta rows per main row it'll start making a big difference in a hurry. With 128 + n*128 vs (128+32) + n*(32), the latter wins even with n=1, though the complexity wouldn't be worth it until you start having several meta rows per main row. There's a lot less benefit if you need bigint keys for your main rows, to the point where I doubt I'd bother unless I had tens or hundreds of meta rows per main row; you need nearly n=10 to make 2:1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.