> > We do regularly drop an rebuild indexes on messages, but it looks like > >we do not on messageblks. There is one index on messageblks(message_idnr) > >and the primary key, both of which are bigint's (8 bytes). So with > >max(messageblk_idnr) at < 45million, that should account for about 720MB, > >which is quite a bit, but no-where near 10GB. I believe all the other > >indexes (on users, aliases, pbsp, etc.) are both much smaller and will > >reuse the keys, so shouldn't need to be recreated. Unfortunately, it's > >too late right now to drop all indexes and see just how much space that > >did free up. :( > > > > > Let me correct my statement a little, it's not just the big tables that > will get cause problems. It has more to do with the number of updates > than it has do do with the number of rows in the table. The user table > for example is updated every time you login, so it's indexes could get > fairly big... etc...
I was under the belief (from reading this page: http://www.postgresql.com/docs/7.3/static/routine-reindex.html ) that indexes that reused keys, or at least had dispersed key values, wouldn't have the growth problem. Eg. the users table indexes are on user_idnr and userid, so even though you update the table on each login, the key values remain the same, so the index doesn't grow. But on messages, the index on message_idnr keeps growing sequentially, and as old messages get deleted from the table, the index keys are never reused, so it just keeps growing. In that case, just messages and messageblks would need it regularly for most setups, I think. Yes/No? -- Jesse Norell [EMAIL PROTECTED] is not my email address; change "administrator" to my first name. --
