On Thu, Nov 11, 2010 at 1:25 PM, Kyriacos Kyriacou
<kyriacosk@prime-tel.com> wrote:
> This is my first post in this mailing list and I would like to raise an
> issue that in my opinion is causing performance issues of PostgreSQL
> especially in a transaction processing environment. In my company we are
> using PostgreSQL for the last 8 year for our in-house developed billing
> system (telecom). The last few months we started considering moving to
> another RDBMS just because of this issue.
>
> After all these years, I believe that the biggest improvement that could
> be done and will boost overall performance especially for enterprise
> application will be to improve Multiversion Concurrency Control (MVCC)
> mechanism. In theory this seems to be improving performance for SELECT
> queries but on tables with very intensive and frequent updates, even
> that is not fully true because of the fragmentation of data caused by
> MVCC. I saw cases were a SELECT COUNT(*) on an empty (!!!) table (used
> as a buffer) took more than 40min to return a result! VACUUM is not a
> solution in my opinion even though after the introduction of autovacuum
> daemon situation got much better.
There are probably a number of ways that the behavior you're seeing
could be improved without switching databases or rewriting PostgreSQL,
but you haven't provided enough information here for anyone to help
you in a meaningful way - such as the version of PostgreSQL you're
running. One obvious suggestion would be to empty your table using
TRUNCATE rather than DELETE, which will avoid the particular problem
you're describing here.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company