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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
