> Let me ask a question, you have this hundred million row table. OK, how > much of that table is "read/write?" Would it be posible to divide the > table into two (or more) tables where one is basically static, only > infrequent inserts and deletes, and the other is highly updated?
Well, all of it is read write... some of the data might be updated less frequently, but there's no way I would know which part of the data is that. Logically is just the same type of data... so unless I find a way to continuously move back and forth the data between an archive table and the live table, based on how active the groups are, I can't imagine any other way of partitioning it. And that would also mean some quite big load given the pretty high dynamics of the groups. > The "big" thing in performance is the amount of disk I/O, if you have a > smaller active table with only a single index, then you may be able to cut > your disk I/O time really down. The smaller the row size, the more rows > fit into a block. The fewer blocks the less dissk I/O. The less disk I/O > the bbetter the performance. I agree, but it is quite hard to achieve that when the data set is both big AND the partitioning criteria is highly dynamic. Not to mention that deleting from that table is also a PITA performance-wise, so I wonder how well the continuous back and forth between the active and inactive table would do. > Also, and anyone listening correct me if I'm wrong, you NEED to vacuum > frequently because the indexes grow and vacuuming them doesnt remove > everything, sometimes a REINDEX or a drop/recreate is the only way to get > performance back. So if you wait too long between vacuums, your indexes > grow and spread across more disk blocks than they should and thus use > more disk I/O to search and/or shared memory to cache. This is nice in theory, but kills performance. I vacuum the big tables only overnight, otherwise the server is sluggish. Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend