title: Postgres Unique Sequences in one table based on owner/foreign key
body: <p>I am creating a web application that will store all user information in one database using permissions, roles, and FKs to restrict data access. One of the tables in this application tracks work orders created by each user (i.e. the work order table has an FK to the user table).</p>
  
  <p>I am wanting to ensure that each user has their own uninterrupted sequence of 'work order IDs' that are assigned when the work order is scheduled. That is, if user 1 creates his first work order, it will assign it #1, however, if user 2 creates his fifth work order, it will assign it #5. </p>
  
  <p>The work order table has a UUID primary key, so each record is distinguishable, and the user FK has a not-null constraint. </p>
  
  <p>Based on my research so far, it seems like Postgres Sequences would likely be my best answer. I would need to create a sequence for each user, and incorporate it into a trigger to stamp the work order record with the next appropriate ID. However, this seems like it would be very performance intensive, and creating a new sequence for every user would have its own set of challenges. </p>
  
  <p>A second approach could be to create a second table that tracks each user's latest sequence, query it, increment it, and update both the work order table and the number tracking table. However, in this scenario, I think it would be susceptible to race conditions if two users were to convert records at exactly the same time. </p>
  
  <p>I'm unsure what the best way to solve the problem would be. Is there another way that would provide better performance?</p>