Re: Performance implications of creating many, many sequences

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/