Don't Use Sequence for Incremental ID
I learn from work about the relational database design. For many of providers of relational db product, they offer the sequence
building block that uses SQL to call something like nextval
to generate next integer value in each call. It’s tempting to use the sequence generated value as the primary key of a table. However I was told not to do this for the main reason:
Sequence is not a proper DB object that could not be migrated when DB failover. That means the sequence is reset to the initial value so that you will lose the latest ID number if failover. Although you can write the SQL script to resolve the latest number from table then recreate the sequence. But imagine that in an urgent failover case, you would not hope to do so.
Somebody advices to use a table to replace sequence. The pseudocode looks like that.
An ID table:
type | max_id |
---|---|
type1 | 199 |
type2 | 200 |
…. |
And sequence can guarantee the atomicity, that’s why many people like to use that in microservices architecture but one single DB. And sequence can handle well the isolation where multiple services are getting ID from DB. If we won’t use sequence, then we need a function that can do transactionally like sequence’s nextval
FUNCTION nextid (type)
BEGIN TRAN
select max_id from ID_TABLE where type = type
update max_id + 1
COMMIT TRAN
Then we can use this function in code to replace nextval
INSERT INTO some_table where nextid(some type)
(Above are pseudocode that demonstrates the idea…)