Re: how delete/insert/update affects select performace?

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