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:

typemax_id
type1199
type2200
….

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…)