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

"Anibal David Acosta"  wrote:
 
> 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.
 
In addition to the advice from Tomas (which was all good) you should
be aware that depending on the version of PostgreSQL (which you
didn't mention), your hardware (which you didn't describe), and your
configuration (which you didn't show) the data modification can make
you vulnerable to a phenomenon where a checkpoint can cause a
blockage of all disk I/O for a matter of minutes, causing even
simple SELECT statements which normally run in under a millisecond
to run for minutes.  This is more likely to occur in a system which
has been aggressively tuned for maximum throughput -- you may need
to balance throughput needs against response time needs.
 
Every one of the last several major releases of PostgreSQL has
gotten better at preventing this problem, so your best protection
from it is to use a recent version.
 
There's a good chance that you won't run into this, but if you do,
you can generally correct it by reducing your shared_buffers setting
or making your background writer more aggressive.
 
-Kevin