> In my case, it was the fact that indexes don't release the space of > indexes of deleted rows. So, if you have a table that has a lot of > inserts/deletes, your indexes will grow incredibly fast. > > The way to see what your biggest items are: > select * from pg_class order by relpages desc;
Yes, I already suspected this could be at least part of the reason, and your SQL query confirms it. However, dropping and re-creating my biggest indexes only reclaims about 500MB, this still leaves about 1GB unaccounted for and I can't see how my remaining (small) indexes can be responsible for this (btw: do you know how much diskspace one 'relpage' use?). Given that I have lots of deletes/inserts, is there anything besides the indexes which could use this much space? > The only way that I know to recover this space is to drop the indexes > and recreate them. Vacuum didn't touch them for me. This is not good as the database is in use 24/7, and without the indexes everything comes to a screeching halt. This means I probably will have to stop the programs using the database for the time it takes to re-create the indexes; this is better than having to dump/restore everything however :) Are there any plans to also vacuum the indexes in a future version of Postgre (otherwise an excellent piece of software!) ? Regards, Kristian ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
