5

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?

Nick Ginanto
  • 31,090
  • 47
  • 134
  • 244
  • A [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier) is *not* a string, it is a [128-bit](https://en.wikipedia.org/wiki/128-bit) value often presented to humans as a [hex string](https://en.wikipedia.org/wiki/Hexadecimal) of [36 characters](https://en.wikipedia.org/wiki/Universally_unique_identifier#Format). The `uuid-ossp` is a plugin to Postgres for *generating* UUID values. See: [*Generating a UUID in Postgres for Insert statement?*](https://stackoverflow.com/q/12505158/642706) and [*Default value for UUID column in Postgres*](https://dba.stackexchange.com/q/122623/19079) – Basil Bourque Jul 02 '17 at 21:19

2 Answers2

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:

Helios de Guerra
  • 3,445
  • 18
  • 23
  • 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](https://en.wikipedia.org/wiki/Federated_database_system). Fortunately, memory and storage are cheap nowadays. – Basil Bourque Mar 21 '15 at 13:40
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.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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. – Basil Bourque Mar 21 '15 at 13:31