12

We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We want to rule out it being "a terrible idea for an obvious reason" before we moved to the phase of actually trying it and testing it at load.

Our peak load is of the order of 1000 inserts a second, across a couple of tables.

Our research thus far indicates that - sequence generation speed shouldn't be an issue - sequence fragmentation (gaps) will happen, but shouldn't be an issue - id exhaustion won't be an issue

We're not sure if we're missing other big things. We'd be grateful for people's opinions, especially from people who have tried it before and had either positive or negative experiences.

For context - we have two main motivations for doing this.

One motivation to do this is so that we can define a bunch of dictionaries (we call them scopes) and have human readable words assigned to those ids, so we want to make sure that ids in different tables never overlap. So, in one scope, id 12345 might be assigned the value "Green" and in another it might be assigned "Verde". (Actually, we don't use it for internationalisation, but we might one day).

The other motivation is to make it easy to have several deployments in the field and know (by uniquely setting each deployment's sequence couple of most significant digits) that our deployments won't overlap primary keys. (Like a GUID lite).

  • How confident will you be that 12345 will never be used in more than one table (ie because an id is manually updated for some reason in the future)? I would much rather have the security of knowing the database is enforcing that constraint, that the burden of having to enforce it myself. – Jack Douglas Jan 2 '18 at 17:29
  • 1
6

Three possible issues that spring to mind are:

  1. With any shared resource you are creating a potential bottleneck. My gut says that for your peak load this should not be an issue but I strongly suggest benchmarking any such solution in a production-like production-sized environment to be sure.

  2. You are essentially assigning meaning to surrogate keys which defeats part of their purpose in RDB theory. A surrogate key by its nature should not have meaning beyond being a key for identifying tuples in that relation. If the entities might have meaning together and so need collision free keys, is it correct that they are being modelled separately or has something been missed in the requirements and/or data model design?

  3. You are introducing a potential point of failure. What if a deployment doesn't get its initial sequence starting point set? You then either have a deployment blocking error or deployments start from the same place "breaking" your feature. Also, what will you do if somewhere down the line someone thinks it is a good idea to branch a deployment (in production perhaps a tenant company divests part of itself and needs to separate out the data). What if the seed somehow gets reset by a bad upgrade deployment or other migration?[0]

If none of those issues concerns you then go ahead, the idea isn't going to break anything IMO. Of course there may be better ways even if this one isn't wrong in itself.


When you say "UUID-lite" you imply that you have already considered and discounted UUIDs. Is that the case, and if so are there particular reasons for deciding they are not suitable for this project?

One possible reason for not using UUIDs is index fragmentation though the significance of that is often greatly over-stated[1]. SQL Server's answer to this is the "sequential GUID" which is pretty much equivalent to what you are suggesting if we discount assigning meaning to key values - perhaps postgres has an equivalent to that? Of course always increasing indexes can have their own performance issues (last-page contention, index stats growing stale) in some very specific high volume workloads[2].

Another common argument against UUIDs is key length: why use 16 bytes per value when 4 or 8 will suffice? If the uniqueness is truly a useful property then this will usually trump key-size concerns significantly. If key-size is a concern but you are happy to use a 64-bit INT rather than needing to keep inside 32-bits you could use your technique without adding a potential shared-resource contention issue by doing your seeded-integer-key idea per table[3] using a normal INT IDENTITY(<start>, 1)[4] column definition, though again this is adding deployment complexity (a small amount, but certainly not zero).

Human readability is sometimes cited as a problem, but that goes back to assigning meaning to surrogate keys.

Compressibility is a less common concern but one you might come across. To just about any compression algorithm UUIDs are likely to look like random (therefor uncompressible) data unless you are using something like SQL server's sequential UUIDs. This might be a concern for a very large set of links (or other block of data) that contains many entity IDs served to an application over a slow network, or if needing to use something like SQL Server's index compression features, though both these matters are essentially just restating the key size concern in a slightly different way and sequential UUIDs may help here too.


[0] this could happen for normal identity columns too of course, but as you are using a less common feature you are increasing the chance of a less experienced DBA after you missing the problem if it happens once you are off doing something new and exciting elsewhere!

[1] I'm a SQL Server guy, I suspect the potential issue is the same in postgres but for all I know it may have a different index layout that can mitigate the effect.

[2] Though again these may be SQL Server specific especially the latter of the two examples I listed

[3] Top two bytes: vary by database, next two: vary by table, remaining four: the incrementing bits

[4] That is MS SQL Server syntax, postgres syntax may vary but you shoudl see what I mean and be able to translate


tl;dr: if you find yourself reinventing the wheel, make sure that all of the existing designs really aren't suitable before starting to considering why a new one might or might not be.

| improve this answer | |
  • 1
    About [1]: Since (from what I heard) the issue in SQL Server is mainly with fragmentation of the CI when the UUID is used as the CI, that can't happen in Postgres, where all tables are heaps. Non clustered, btree indexes should behave the same in both platforms. I suppose fragmentation is considered less of a problem there. – ypercubeᵀᴹ Dec 22 '17 at 17:45
  • Postgres has also several other types of indexes (hash, trigram, gin, gist, brin, ...). Not sure how they might be affected or even if they might be useful for a UUID column. – ypercubeᵀᴹ Dec 22 '17 at 17:46
  • Non-clustered indexes (in SQL Server, and usually elsewhere) are still b-tree based so fragment due to page splitting in the presence of data arriving in random order. The link between UUIDs and CIs in many discussions about SQL Server indexing strategies is due to the key choice question (real data / always incrementing surrogate / effectively random surrogate) and the fact that clustering by PK is often peoples' default arrangement. As you say, indexes based on significantly different structures such as hashes have completely different considerations. – David Spillett Dec 23 '17 at 9:57
  • 1
    Ah, the old "existing external dependency beyond the scope of this project to change" problem! That makes sense as a reason to decide against UUIDs. – David Spillett Jan 2 '18 at 16:06
  • 1
    That makes sense, thanks — I should perhaps have taken issue with @yper-trollᵀᴹ's original comment. I don't think 'fragmentation' is the right word for the issue with a UUID CI. The problem is that every insert is virtually guaranteed to be to a different leaf block so insert performance is diabolical. There is also the fill factor issue but that is minor by comparison — a <25% hit on space versus up to several orders of magnitude performance hit. – Jack Douglas Jan 3 '18 at 8:17
3

We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We want to rule out it being "a terrible idea for an obvious reason" before we moved to the phase of actually trying it and testing it at load.

That's a horrible idea: rule it out. Just use a GUID/UUID. Why did you rule out that idea? In PostgreSQL we use uuid-ossp,

uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.

Like this,

CREATE EXTENSION uuid-ossp;
CREATE TABLE f ( f_id uuid DEFAULT uuid_generate_v4() );

You make a lot of assumptions in your answer in order for it to be valid,

  • speed "shouldn't be an issue"
  • gaps "shouldn't be an issue"
  • id exhaustion won't happen

You don't have to assume any of that. What if you get a DOS on the ID creating a massive a gap and pushing rollover on one shard? Why not just use the industry solution for this problem? It's not clear that there is a single drawback. It's likely all win. Except for a few bytes of storage.

| improve this answer | |
  • 1
    +1 for quoting "It's a horrible idea" back at me :) – Burleigh Bear Jan 2 '18 at 4:13
1

One motivation to do this is so that we can define a bunch of dictionaries (we call them scopes) and have human readable words assigned to those ids, so we want to make sure that ids in different tables never overlap. So, in one scope, id 12345 might be assigned the value "Green" and in another it might be assigned "Verde". (Actually, we don't use it for internationalisation, but we might one day).

On it's own I wouldn't let this be the reason for choosing a quirky and fragile design. If you go the route there will be no way to take advantage of the database features for ensuring referential integrity, for example. A traditional normalised way of achieving the same thing would have benefits beyond RI:

create table tab1(tab1_id serial primary key);
create table tab2(tab2_id serial primary key);
create table scope(scope_id serial primary key, scope_name text);
create table scope_tab1(scope_id integer references scope, tab1_id integer references tab1, val text, primary key(scope_id,tab1_id));
insert into scope(scope_name) values ('English'),('French');
insert into tab1(tab1_id) select generate_series(1,5);
insert into tab2(tab2_id) select generate_series(1,5);
insert into scope_tab1(scope_id,tab1_id,val) values (1,1,'Green'),(2,1,'Verde');
select tab1_id
     , (select val from scope_tab1 where scope_id=1 and tab1_id=tab1.tab1_id) val_s1
     , (select val from scope_tab1 where scope_id=2 and tab1_id=tab1.tab1_id) val_s2
from tab1;
tab1_id | val_s1 | val_s2
------: | :----- | :-----
      1 | Green  | Verde 
      2 | null   | null  
      3 | null   | null  
      4 | null   | null  
      5 | null   | null  

dbfiddle here

The other motivation is to make it easy to have several deployments in the field and know (by uniquely setting each deployment's sequence couple of most significant digits) that our deployments won't overlap primary keys. (Like a GUID lite).

I would suggest, as others have done, that using UUID is much better (ie much less error-prone) than inventing a new UUID-lite.

I still don't think it is your best bet however — you aren't sharding so there is no actual need to have non-overlapping IDs between deployments that I can see from the information you have provided. Presumably you have other ways of identifying a deployment in a database than looking at IDs in these tables.

| improve this answer | |
0

I've used the pattern you proposed with an additional central id table that all other ids foreign key to. It worked in a major production system totally fine.

I think the real reason for doing this is if your ids have a scope beyond your database. For example, in my example, these ids enumerated unique financial securities and companies. You might ask, why not create a set if ids for companies, and a second set for securities, as autoincrment primary keys on each table? Because we wanted other time series records to refer to either securities or companies. So, the time series table foreign keyed to the central id table.

Given the above, a GUID / UUID would also work fine. However, these formats are often 128 bit in size which can have an impact as there are used in almost every index, primary key and foreign key in the database, and mitigating their non-sequential placement in the total id range can be tricky, leading to sub-optimal select performance. Our database was very geared towards to select performance.

GUIDs / UUIDs do have one advantage, which is that they are much easier to create federated generation processes. That is to say, you can have multiple id generation / assignment processes in your enterprise without coordination, by just assuming that they will never clash. If your only id generation processes is inside your database, then that's less of a concern, but its worth mentioning.

Note that UUID generation is dependent on getting your MAC addresses unique, so you will have to pay attention to that in a virtual / container environment.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.