From: | Robert Klemme <shortcutter(at)googlemail(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Maximum number of sequences that can be created |
Date: | 2012-05-15 11:45:59 |
Message-ID: | CAM9pMnOQzkH+Wz-oC9AHg0SqosbYRyMLQ5_nV_v9Uh-PdE8jiQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Віталій Тимчишин | 2012-05-15 15:21:09 | SSD selection |
Previous Message | Andres Freund | 2012-05-15 10:57:53 | Re: Maximum number of sequences that can be created |