Re: Performance implications of creating many, many sequences

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?