On 23/10/2010 3:18 AM, Michael Gardner wrote:
Consider the following table: CREATE TABLE invoice ( account_id integer NOT NULL REFERENCES account, invoice_number integer NOT NULL, UNIQUE (account_id,invoice_number)); I would like to do the equivalent of making invoice_number a serial type, but on a per-account basis. Would it be a reasonable approach to create a separate sequence for each individual account? Are there performance implications I should know about, given that there will be hundreds of thousands of accounts? Is there another approach I should be looking at instead?
I'd favour "another approach". The purpose of sequences is to improve concurrency in the face of rapid inserts. You're probably not going to have a bunch of transactions all wanting to grab new invoice numbers for the same customer at the same time, so this isn't going to be a problem; you're already partitioning the concurrency limitation out to be per-customer, which should be good enough.
Also, sequences are not gapless. If a transaction grabs an entry then rolls back instead of committing, that ID is never used. Your customers would probably not like that for invoice numbers.
Instead, maintain a counter, either in the main customer record or in an associated (customer_id, counter) side table if you want to reduce potential lock contention. Write a simple SQL function that uses an UPDATE ... RETURNING statement to grab a new ID from the counter and increment it. Use that function instead of 'nextval(seqname)' when you want an ID. The UPDATE will take a lock out on the customer row (or side-table row if you did it that way) that'll prevent anyone else updating it until the transaction commits or rolls back.
-- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
