Re: Performance degradation of inserts when database size grows

On 05/17/2011 08:45 AM, Andrey Vorobiev wrote:
> 1. How does database size affect insert performance?

As indexes grow, it becomes slower to insert into them.  It has to 
navigate all of the indexes on the table to figure out where to add the 
new row into there, and that navigation time goes up when tables are 
larger.  Try using the queries at 
http://wiki.postgresql.org/wiki/Disk_Usage to quantify how big your 
indexes are.  Many people are absolutely shocked to see how large they 
become.  And some database designers throw indexes onto every possible 
column combination as if they were free.

> 2. Why does number of written buffers increase when database size grows?
>

As indexes grow, the changes needed to insert more rows get spread over 
more blocks too.

You can install pg_buffercache and analyze what's actually getting dirty 
in the buffer cache to directly measure what's changing here.  If you 
look at http://projects.2ndquadrant.com/talks and download the "Inside 
the PostgreSQL Buffer Cache" talk and its "Sample Queries" set, those 
will give you some examples of how to summarize everything.

> 3. How can I further analyze this problem?

This may not actually be a problem in that it's something you can 
resolve.  If you assume that you can insert into a giant table at the 
same speed you can insert into a trivial one, you'll have to adjust your 
thinking because that's never going to be true.  Removing some indexes 
may help; reducing the columns in the index is also good; and some 
people end up partitioning their data specifically to help with this 
situation.  It's also possible to regain some of the earlier performance 
using things like REINDEX and CLUSTER.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books