Re: Maximum number of sequences that can be created

Hi,

On Tue, May 15, 2012 at 12:57 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> I would rather suggest going with a suming table if you need to do something
> like that:
>
> sequence_id | value
> 1 | 3434334
> 1 | 1
> 1 | -1
> 1 | 1
> 1 | 1
> ...
>
> You then can get the current value with SELECT SUM(value) WHERE sequence_id =
> 1. For garbage collection you can delete those values and insert the newly
> summed up value again.
> That solution won't ever block if done right.

I was going to suggest another variant which would not need GC but
would also increase concurrency:

sequence_id | hash | value
1 | 0 | 3
1 | 1 | 9
1 | 2 | 0
1 | 3 | 2
...

with PK = (sequence_id, hash) and hash in a fixed range (say 0..15).

Value would be obtained the same way, i.e. via
SELECT SUM(value) FROM T WHERE sequence_id = 1

The hash value would have to be calculated

- at session start time (cheap but might reduce concurrency due to
small number of changes) or
- at TX start time (more expensive but probably better concurrency
due to higher change rate)

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/