Message-ID: <a1d1b28341b1453b7ea85692cad35455.squirrel@sq.gransy.com>
In-Reply-To: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
Date: Thu, 8 Sep 2011 16:10:19 +0200
Subject: Re: how delete/insert/update affects select performace?
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Anibal David Acosta" <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

On 8 Září 2011, 14:51, Anibal David Acosta wrote:
> Hi!
>
>
>
> I have a table not too big but with aprox. 5 millions of rows, this table
> must have 300 to 400 select per second. But also must have 10~20
> delete/insert/update per second.
>
> So, I need to know if the insert/delete/update really affect the select
> performance and how to deal with it.

Yes, insert/update do affect query performance, because whenever a row is
modified a new copy is created. So the table might grow over time, and
bigger tables mean more data to read.

There are two ways to prevent this:

1) autovacuum - has to be configured properly (watch the table size and
number of rows, and if it grows then make it a bit more aggressive)

2) HOT

> The table structure is very simple:
>
> account_id integer (PK)
>
> service_id integer (PK)
>
> enabled char(1)
>
> The index created on this has the same 3 columns.
>
> Most of time the table has more insert or delete than update, when update
> occur the column changed is enabled;

So there's one index on all three columns? I'd remove the "enabled" from
the index, it's not going to help much I guess and it makes HOT possible
(the modified column must not be indexed). Plus there will be one less
index (the other two columns are already a PK, so there's a unique index).

Tomas