Moving the data from transaction table to history table to increase insert performance, postgres

Question

I have 3 database tables, each contain 6 million rows and adding 3 million rows every year.

Following are the table information: Table 1: 20 fields with 50 characters average in each filed. Has 2 indexes both are on timestamp fields. Table 2: 5 fields, 2 byte array field and 1 xml field Table 3: 4 fields, 1 byte array field

Following is the usage: Insert 15 to 20 records per second in each table. A view is created by joining first 2 tables and the select is mostly based on the date field in the first table.

Right Now, insert one record each in all three table together takes about 100 milliseconds.

I'm planning to migrate from postgres 8.4 to 9.2. I would like to do some optimization for insert performance also.Also, I'm planning to create history tables and keep the old record into those tables. I have the following questions in this regard

1. Will create history tables and move older data to those tables help in increasing the insert performance?
2. If it helps, how often I need to move the old records into the history tables, daily? or weekly/monthly/yearly?
3. If i keep only one month (220,000) data instead of one year data (3 million) will it help in improving insert performance?

Thanks in advance, Sudheer

Answer 1

I'm sure someone better informed than I will show up and provide a better answer, but my impression is that:

• Insert performance is mostly a function of your indexing strategy and your hardware
• Performance, in general, is better under 9.0+ than 8.4, and this may rub off on insert performance, but I'm not certain of that.
• None of your ideas are going to directly affect insert performance

Now, that said, the cost of maintaining a small index is lower than a large one, so it may be that creating history tables and moving old data there will improve performance simply by reducing index pressure. But I would expect dropping one of your indexes to have a direct and greater effect. Perhaps you could have a history table with both indexes and just maintain one of them on the "today" table?

If I were in your shoes, I'd get a copy of production going on my machine running 8.4 with a similar configuration. Then upgrade to 9.2 and see if the insert performance changes. Then try out these ideas and benchmark them, see which ones improve the situation. It's absolutely essential that things be kept as similar to production as possible for this to yield useful information, but it will certainly be better information than any hypothetical answer you might get.

Now, 100ms seems pretty slow for inserting one row IMO. Better hardware would certainly improve this situation. The usual suggestion would be a big striped RAID array with a battery-backed cache. PostgreSQL 9.0 High Performance has more information on all of this.