Skip site navigation (1) Skip section navigation (2)

Re: Maximum number of sequences that can be created

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 (view raw or flat)
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/

In response to

pgsql-performance by date

Next:From: Віталій ТимчишинDate: 2012-05-15 15:21:09
Subject: SSD selection
Previous:From: Andres FreundDate: 2012-05-15 10:57:53
Subject: Re: Maximum number of sequences that can be created

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group