Message-ID: <4F31397A.5090304@squeakycode.net>
Date: Tue, 07 Feb 2012 08:47:22 -0600
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:10.0) Gecko/20120129 Thunderbird/10.0
MIME-Version: 1.0
To: Ofer Israeli <oferi@checkpoint.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Olga Vingurt <olgavi@checkpoint.com>, Netta Kabala <nettak@checkpoint.com>
Subject: Re: Inserts or Updates
References: 
 <217DDBC2BB1E394CA9E7446337CBDEF20102C056BD4F@il-ex01.ad.checkpoint.com>
In-Reply-To: 
 <217DDBC2BB1E394CA9E7446337CBDEF20102C056BD4F@il-ex01.ad.checkpoint.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable

On 2/7/2012 4:18 AM, Ofer Israeli wrote:
> Hi all,
>
> We are currently =93stuck=94 with a performance bottleneck in our serve=
r
> using PG and we are thinking of two potential solutions which I would b=
e
> happy to hear your opinion about.
>
> Our system has a couple of tables that hold client generated
> information. The clients communicate *every* minute with the server and
> thus we perform an update on these two tables every minute. We are
> talking about ~50K clients (and therefore records).
>
> These constant updates have made the table sizes to grow drastically an=
d
> index bloating. So the two solutions that we are talking about are:
>

You dont give any table details, so I'll have to guess.  Maybe you have=20
too many indexes on your table?  Or, you dont have a good primary index,=20
which means your updates are changing the primary key?

If you only have a primary index, and you are not changing it, Pg should=20
be able to do HOT updates.

If you have lots of indexes, you should review them, you probably don't=20
need half of them.


And like Kevin said, try the simple one first.  Wont hurt anything, and=20
if it works, great!

-Andy