I can't say about the web side of your question. But 9 uuids are great for n-tier applications. PK 8 generation can be decentralized: each client 7 generates it's own pk without risk of collision. And 6 the speed difference is generally small.

Make 5 sure your database supports an efficient 4 storage datatype (16 bytes, 128 bits). At 3 the very least you can encode the uuid string 2 in base64 and use char(22).

I've used them 1 extensively with Firebird and do recommend.

For what it's worth, I've seen a long running 7 stored procedure (9+ seconds) drop to just 6 a few hundred milliseconds of run time simply 5 by switching from GUID primary keys to integers. That's 4 not to say displaying a GUID is a bad idea, but as 3 others have pointed out, joining on them, and 2 indexing them, by definition, is not going 1 to be anywhere near as fast as with integers.

I can answer you that in SQL server if you 14 use a uniqueidentifier (GUID) datatype and 13 use the NEWID() function to create values 12 you will get horrible fragmentation because 11 of page splits. The reason is that when 10 using NEWID() the value generated is not 9 sequential. SQL 2005 added the NEWSEQUANTIAL() function 8 to remedy that

One way to still use GUID 7 and int is to have a guid and an int in 6 a table so that the guid maps to the int. the 5 guid is used externally but the int internally 4 in the DB

for example

457180FB-C2EA-48DF-8BEF-458573DA1C10    1
9A70FF3C-B7DA-4593-93AE-4A8945943C8A    2

1 and 2 will be used 3 in joins and the guids in the web app. This 2 table will be pretty narrow and should be 1 pretty fast to query

Why couple your primary key with your URI?

Why 18 not have your URI key be human readable 17 (or unguessable, depending on your needs), and 16 your primary index integer based, that way 15 you get the best of both worlds. A lot 14 of blog software does that, where the exposed 13 id of the entry is identified by a 'slug', and 12 the numeric id is hidden away inside of 11 the system.

The added benefit here is that 10 you now have a really nice URL structure, which 9 is good for SEO. Obviously for a transaction 8 this is not a good thing, but for something 7 like stackoverflow, it is important (see 6 URL up top...). Getting uniqueness isn't 5 that difficult. If you are really concerned, store 4 a hash of the slug inside a table somewhere, and 3 do a lookup before insertion.

edit: Stackoverflow 2 doesn't quite use the system I describe, see 1 Guy's comment below.

Rather than URLs like this:

http://example.com/user/783

Why not have:

http://example.com/user/yukondude

Which 2 is friendlier to humans and doesn't leak 1 that tiny bit of information?

You could use an integer which is related 13 to the row number but is not sequential. For 12 example, you could take the 32 bits of the 11 sequential ID and rearrange them with a 10 fixed scheme (for example, bit 1 becomes 9 bit 6, bit 2 becomes bit 15, etc..).
This 8 will be a bidirectional encryption, and 7 you will be sure that two different IDs 6 will always have different encryptions.
It 5 would obviously be easy to decode, if one 4 takes the time to generate enough IDs and 3 get the schema, but, if I understand correctly 2 your problem, you just want to not give 1 away information too easily.

We use GUIDs as primary keys for all our 4 tables as it doubles as the RowGUID for 3 MS SQL Server Replication. Makes it very 2 easy when the client suddenly opens an office 1 in another part of the world...

I don't think a GUID gives you many benefits. Users 8 hate long, incomprehensible URLs.

Create 7 a shorter ID that you can map to the URL, or 6 enforce a unique user name convention (http://example.com/user/brianly). The 5 guys at 37Signals would probably mock you for worrying 4 about something like this when it comes 3 to a web app.

Incidentally you can force 2 your database to start creating integer 1 IDs from a base value.

It also depends on what you care about for 11 your application. For n-tier apps GUIDs/UUIDs 10 are simpler to implement and are easier 9 to port between different databases. To 8 produce Integer keys some database support 7 a sequence object natively and some require 6 custom construction of a sequence table.

Integer 5 keys probably (I don't have numbers) provide 4 an advantage for query and indexing performance 3 as well as space usage. Direct DB querying 2 is also much easier using numeric keys, less 1 copy/paste as they are easier to remember.

I work with a student management system 9 which uses UUID's in the form of an integer. They 8 have a table which hold the next unique 7 ID.

Although this is probably a good idea 6 for an architectural point of view, it makes 5 working with on a daily basis difficult. Sometimes 4 there is a need to do bulk inserts and having 3 a UUID makes this very difficult, usually 2 requiring writing a cursor instead of a 1 simple SELECT INTO statement.

I've tried both in real web apps.

My opinion 10 is that it is preferable to use integers 9 and have short, comprehensible urls.

As a 8 developer, it feels a little bit awful seeing 7 sequential integers and knowing that some 6 information about total record count is 5 leaking out, but honestly - most people 4 probably don't care, and that information 3 has never really been critical to my businesses.

Having 2 long ugly UUID urls seems to me like much 1 more of a turn off to normal users.

I think using a GUID would be the better 2 choice in your situation. It takes up more 1 space but it's more secure.

I think that this is one of these issues 7 that cause quasi-religious debates, and 6 its almost futile to talk about. I would 5 just say use what you prefer. In 99% of 4 systems it will no matter which type of 3 key you use, so the benefits (stated in 2 the other posts) of using one sort over 1 the other will never be an issue.

Youtube uses 11 characters with base64 encoding 6 which offers 11^64 posibilities, and they 5 are usually pretty manageable to write. I 4 wonder if that would offer better performance 3 than a full on UUID. UUID converted to base 2 64 would be double the size I believe.

More 1 information can be found here: https://www.youtube.com/watch?v=gocwRvLhDf8

Pros and Cons of UUID

Note: uuid_v7 is time based uuid instead of random. So 20 you can use it to order by creation date 19 and solve some performance issues with db inserts if you do really many of them.

Pros:

• can be generated on api level (good for distributed systems)
• hides count information about entity
• doesn't have limit 2,147,483,647 as 32-bit int
• removes layer of errors related to passing one entity id userId: 25 to get another bookId: 25 accidently
• more friendly graphql usage as ID key

Cons:

• 128-bit instead 32-bit int (slightly bigger size in db and ~40% bigger index, around ~30MB for 1 million rows), should be a minor concern
• can't be sorted by creation (can be solved with uuid_v7)
• non-time-ordered UUID versions such as UUIDv4 have poor database index locality (can be solved with uuid_v7)

URL usage

Depending 18 on app you may care or not care about url. If 17 you don't care, just use uuid as is, it's fine.

If 16 you care, then you will need to decide on 15 url format.

Best case scenario is a use of 14 unique slug if you ok with never changing 13 it:

http://example.com/sale/super-duper-phone

If your url is generated from title and 12 you want to change slug on title change 11 there is a few options. Use it as is and 10 query by uuid (slug is just decoration):

http://example.com/book/035a46e0-6550-11dd-ad8b-0800200c9a66/new-title

Convert 9 it to base64url:

• you can get uuid back from AYEWXcsicACGA6PT7v_h3A
• AYEWXcsicACGA6PT7v_h3A - 22 characters
• 035a46e0-6550-11dd-ad8b-0800200c9a66 - 36 characters

http://example.com/book/AYEWXcsicACGA6PT7v_h3A/new-title

Generate a unique short 11 chars length 8 string just for slug usage:

http://example.com/book/icACEWXcsAY-new-title
http://example.com/book/icACEWXcsAY/new-title

If you don't 7 want uuid or short id in url and want only 6 slug, but do care about seo and user bookmarks, you 5 will need to redirect all request from

http://example.com/sale/phone-1-title

to

http://example.com/sale/phone-1-title-updated

this 4 will add additional complexity of managing 3 slug history, adding fallback to history 2 for all queries where slug is used and redirects 1 if slugs doesn't match