From: Andy Colson <andy@squeakycode.net>
To: Ofer Israeli <oferi@checkpoint.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Olga Vingurt <olgavi@checkpoint.com>
Cc: Netta Kabala <nettak@checkpoint.com>
Subject: Re: Inserts or Updates
Date: Tue, 07 Feb 2012 08:47:22 -0600
Message-ID: <4F31397A.5090304@squeakycode.net> (raw)
In-Reply-To: <217DDBC2BB1E394CA9E7446337CBDEF20102C056BD4F@il-ex01.ad.checkpoint.com>
References: <217DDBC2BB1E394CA9E7446337CBDEF20102C056BD4F@il-ex01.ad.checkpoint.com>
On 2/7/2012 4:18 AM, Ofer Israeli wrote:
> Hi all,
>
> We are currently “stuck” with a performance bottleneck in our server
> using PG and we are thinking of two potential solutions which I would be
> 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 and
> 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
too many indexes on your table? Or, you dont have a good primary index,
which means your updates are changing the primary key?
If you only have a primary index, and you are not changing it, Pg should
be able to do HOT updates.
If you have lots of indexes, you should review them, you probably don't
need half of them.
And like Kevin said, try the simple one first. Wont hurt anything, and
if it works, great!
-Andy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, oferi@checkpoint.com, olgavi@checkpoint.com, nettak@checkpoint.com
Subject: Re: Inserts or Updates
In-Reply-To: <4F31397A.5090304@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox