What is uuid ossp in postgres

Question

I've seen this in a migration

enable_extension 'uuid-ossp'

as far as I know uuid is a long unique string based on some RFCs, and this enable the db (in this case pg) to have a column type as a uuid

my question is - Why is this type of column needed and not just a string column? is it to replace the regular integer id column and to have a uuid as the id instead?

is there any advantage to use a uuid as the id instead of just having a string type column contain a uuid?

A UUID is not a string, it is a 128-bit value often presented to humans as a hex string of 36 characters. The uuid-ossp is a plugin to Postgres for generating UUID values. See: Generating a UUID in Postgres for Insert statement? and Default value for UUID column in Postgres — Basil Bourque
– Basil Bourque, Commented Jul 2, 2017 at 21:19

Helios de Guerra · Accepted Answer · 2014-02-11 20:35:44Z

5

I was hoping to see some more people chime in here, but I think the idea of the uuid is to replace the id column for a more unique id which is useful especially when you've got a distributed database or are dealing with replication.

Pros:

Easier to merge data
Better scaling when/if you have to move to a distributed system
Avoids Postgres sequence problems which often occur when merging or copying data
You can generate them from other platforms (other than just the database, if you need)
If you're wanting to obfuscate your records (e.g. rather than accessing users/1 (the id) which might prompt a curious user to try users/2 to see if he could access someone else's information since its obvious the sequential nature of the parameter). Obviously there are other ways of dealing with this particular issue however

Cons:

Requires larger key length that typical id
Is usually non-sequential (which can lead to strange behavior if you're ordering on it, which you probably shouldn't be doing generally anyhow)
Harder to reference when troubleshooting (finding by a long UUID rather than an simple integer id)

Here are some more resources which I found valuable:

Peter van Hardenberg's (of Heroku) argument for UUIDs (among other things, this is an amazing presentation and you should watch all of it)... Here's the part on using UUID's rather than ids: http://vimeo.com/61044807#t=15m04s
Jeff Atwood's (formerly of StackOverflow) argument for GUIDs: http://www.codinghorror.com/blog/2007/03/primary-keys-ids-versus-guids.html
http://rny.io/rails/postgresql/2013/07/27/use-uuids-in-rails-4-with-postgresql.html
http://blog.crowdint.com/2013/10/09/using-postgres-uuids-as-primary-keys-on-rails.html

answered Feb 11, 2014 at 20:35

Helios de Guerra

3,48520 silver badges23 bronze badges

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

1 Comment

Basil Bourque Over a year ago

As for data length, a typical ID field uses a 32-bit or 64-bit integer. A UUID is 128-bit value, stored as such by Postgres. So two or four times larger. Well worth it if you need the benefits such as federated data. Fortunately, memory and storage are cheap nowadays.

Clodoaldo Neto · Accepted Answer · 2014-02-11 20:45:19Z

3

It is not necessary to install that extension to use the uuid type. The advantages of using the UUID type in instead of a text type are two. The first is the automatic constraint

select 'a'::uuid;
ERROR:  invalid input syntax for uuid: "a"

Second is storage space. UUID only uses 16 bytes while the hex representation takes 33:

select
    pg_column_size('0123456789abcdef0123456789abcdef'),
    pg_column_size('0123456789abcdef0123456789abcdef'::uuid)
;
 pg_column_size | pg_column_size 
----------------+----------------
             33 |             16

The uuid-ossp extension just adds functions to generate UUID.

answered Feb 11, 2014 at 20:45

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

Basil Bourque Over a year ago

To summarize: The data type of UUID is already built into Postgres. No need for the extension to store and retrieve UUID values. If you want to generate UUID values from within Postgres, then you need the extension. The common use-case: Assigning a UUID as a default value to a new row.

Collectives™ on Stack Overflow

What is uuid ossp in postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related