From: Tomas Vondra <tv@fuzzy.cz>
To: Anibal David Acosta <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: how delete/insert/update affects select performace?
Date: Thu, 8 Sep 2011 16:10:19 +0200
Message-ID: <a1d1b28341b1453b7ea85692cad35455.squirrel@sq.gransy.com> (raw)
In-Reply-To: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
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
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: tv@fuzzy.cz, aa@devshock.com
Subject: Re: how delete/insert/update affects select performace?
In-Reply-To: <a1d1b28341b1453b7ea85692cad35455.squirrel@sq.gransy.com>
* 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