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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The simple update is one I considered, but I think if I put it into a stored procedure it should run faster

On May 8, 2013, at 21:14, Tyson Maly <tvmaly@yahoo.com> wrote:

The simple update is one I considered, but I think if I put it into a stored procedure it should run faster

Well, you would partially circumvent the query planner, but you would also circumvent any optimisation said query planner would be able to do based on the statistics for the values being updated.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In some cases, it would be 2-10 times a second per id.

________________________________
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Tyson Maly <tvmaly@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Wednesday, May 8, 2013 10:10 PM
Subject: Re: [GENERAL] question on most efficient way to increment a column

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general