PostgreSQL – About UUID and Generators

postgresqluuid

I'm thinking to use UUID as PK for all tables into my new PostgreSQL database, for such a goal I will be using uuid_generate_v5 and uuid_ns_dns or uuid_ns_url functions, but I have some doubts:

is there any problems using UUIDs only in a database (i.e. performance)?
is there any pros and cons respect to use uuid_generate_v4?
When to use uuid_ns_dns or uuid_ns_url? (I haven't found info about it)
can name parameter for uuid_generate_v5 function be any string (even an empty string) or it depends on namespace parameter? Examples, please.

Best Answer

Answers to your questions:

There are no problems, but the performance will be somewhat worse than using a sequence generated bigint primary key, because adding one to an integer number has to perform better than anything more complicated. Also, a uuid takes twice the storage space (16 bytes).

You should benchmark if that makes a noticeable difference for you.
Apart from the performance impact, it makes no difference.
It does not matter, they just return different constants.
The name parameter can be any string. That function is IMMUTABLE, so you have to make sure that you use different names if you want different UUIDs.

Use UUIDs if you need to generate artificial primary keys in a decentralized fashion, for example in the application code or in different database if you use sharding. If the primary keys are generated in a single database, use a sequence.

Related Solutions

PostgreSQL – How to Index a UUID

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.

PostgreSQL Table Inheritance Explained

There is nothing in your question that would speak against inheritance.

One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.

nba_players, nfl_players etc. could conveniently inherit from a general parent table players (possibly in a separate schema for empty parent tables). ALTER TABLE on the parent cascades to all children (except for ALTER TABLE ... RENAME). This makes it easy to run queries on all player tables together by targeting the parent.

If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);

Best Answer

Related Solutions

PostgreSQL – How to Index a UUID

PostgreSQL Table Inheritance Explained

Related Question