On Fri, Feb 13, 2009 at 3:34 PM, Tino Schwarze <postgresql@xxxxxxx> wrote: > On Fri, Feb 13, 2009 at 03:13:28PM -0700, Scott Marlowe wrote: > > [...] > >> Yeah, that's pretty bad. ~2 Million live rows and ~18 Million dead >> ones is a pretty badly bloated table. >> >> Vacuum full is one way to reclaim that lost space. You can also dump >> and restore that one table, inside a drop / create restraints in a >> transaction during maintenance if you can. A Vacuum full is quite >> intrusive, so avoid it during normal working hours. Dumping and >> reloading the whole db may be faster than either a vacuum full or a >> cluster. A common trick is to do something like: >> >> begin; >> select * into ordermydata from bigbloatedtable order by some_field; >> delete * from bigbloatedtable; >> insert into bigbloatedtable select * from ordermydata; >> commit; >> >> This will both put your table in some order which might help, and >> remove the bloat. > > Really? Wouldn't that add even more bloat? How does that work? (I'd > expect a drop table/create table instead of the delete...) > Note: I suppose that you know a lot more about PG than I do, so I'm just > curious. Whoops, meant truncate bigbloatedtable; sheesh, long week. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
