Conditional segmented auto increment in Postgres

Question

class Ticket{

    int id;
    int caseId;
    int orderNumber;

}

Lets imagine I can't change data structure. The order is always have to be without jumps (1,2,4 not good) in range of one caseId, every caseId will have to start from 1. Currently, records is being written in synchronized method, this is not scalable solution and there is a performance is actual issue here.

How can I use by Postgres DB to just throw the Ticket entities to it being certain that order number is always properly incremented on the db layer?

You always have to serialize operations if you want a gap-less sequence. — Laurenz Albe, Dec 16 '22 at 14:29

score 0 · Answer 1 · answered Dec 16 '22 at 23:23

You can have either scalability or a gap-less sequence but not both. However, you can get the appearance of both. Go ahead and generate orderNumber allowing gaps come as they may. Then create a view on your table that generates a new order number from the relative values.

create view  tickets_vw as 
   select id 
        , case_id
        , row_number() over(partition by case_id order by order_number) order_number 
     from tickets;

See demo here. I interpreted range of one caseId as meaning you wanted the sequence to restart for each Case_id. In addition to showing gap-less relative value the view implements restart as well.

Alternative: As long as the id is always increasing (as demo would) you could use ID in the row_number() function and eliminate the oredeNumber from the table. I however tend to use a generated primary key for nothing but PK and referencing FK.

Conditional segmented auto increment in Postgres

1 Answers1