Re: Performance implications of creating many, many sequences

On 10/24/2010 12:42 AM, Michael Gardner wrote:
> On Oct 22, 2010, at 11:03 PM, Craig Ringer wrote:
>
>> 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.
>
> Thanks for the suggestion. It seems like there should be a safe way to use max() instead of a separate counter though, as long as I can guarantee that invoice numbers never change and invoices are never deleted. Right?

True. You'll then have to provide your own locking (say, SELECT ... FOR
UPDATE on the customer record) to ensure that no two invoices are
allocated the same number, though. If you use UPDATE ... RETURNING on a
counter field the locking is done for you.

You'll have a UNIQUE(customer_id,order_id) constraint in place anyway,
of course, so you won't have the risk of genuinely duplicate IDs, just
the need to retry a transaction that fails due to a duplicate key error
if two invoice creations on a customer happen concurrently.

Perhaps it's too unlikely to care about, but I just dislike using
max(x)+1 on principle, as it's just a generally unsafe sql programming
idiom.

--
Craig Ringer