Thread: question on most efficient way to increment a column

On Wed, May 8, 2013 at 8:45 AM, Tyson Maly <tvmaly@yahoo.com> wrote:
> If I have a simple table with an id as a primary key that is a serial column
> and a column to keep track of a total_count for a particular id, what method
> would provide the fastest way to increment the total_count in the shortest
> amount of time and minimize any locking?
>
> id  serial
> total_count integer
>


uh,
update foo set total_count = total_count + 1 where id = x;
?

merlin

Tyson Maly wrote:
> If I have a simple table with an id as a primary key that is a serial column and a column to keep
> track of a total_count for a particular id, what method would provide the fastest way to increment the
> total_count in the shortest amount of time and minimize any locking?
>
> id  serial
> total_count integer

UPDATE tablename SET total_count=total_count+1 WHERE id=42;

Yours,
Laurenz Albe

How often are these updated? Once an hour, once a minute, once a
second, a thousand times a second?

If it's not more than once a second I would look at eager materialized
views as a possibility for handing this.

http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views#Eager_Materialized_View

On Wed, May 8, 2013 at 7:45 AM, Tyson Maly <tvmaly@yahoo.com> wrote:
> If I have a simple table with an id as a primary key that is a serial column
> and a column to keep track of a total_count for a particular id, what method
> would provide the fastest way to increment the total_count in the shortest
> amount of time and minimize any locking?
>
> id  serial
> total_count integer
>
> Best regards,
>
> Ty
>



--
To understand recursion, one must first understand recursion.