On Wed, Mar 14, 2012 at 8:24 PM, Aleksey Tsalolikhin <atsaloli.tech@xxxxxxxxx> wrote: > On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera <vivek@xxxxxxxxx> wrote: >> I'll bet what happened was postgres re-wrote your table for you, >> effectively doing a compaction. You can get similar effect by doing >> an alter table and "changing" an INTEGER field to be INTEGER. >> Postgres does not optimize that do a no-op, so you get the re-writing >> effect. > > How does table rewriting work? Does it happen a row at a time or all at once? When you do something like alter type or update without a where clause, it will have to make a new copy of every old copy in the table. > In other words, how much free disk space is needed on an 800 TB filesystem > to rewrite a 550 TB table? (Have I got enough space?) If you update the whole table at once (a la alter table alter column or update with no where) then it has to have the space for all the real data to fit. The size of the file system isn't important as much as how much free space is left. IFF it's 800TB with exactly 550TB used, then you have 250TB free. The good news is that if the table is bloated, it should be able to just write to the free space in the table that's already there. This requires proper vacuuming and on older versions free space map settings. With a really large table, older versions of pgsql (<=8.3) tended to blow out their free space map if you didn't crank them up. Newer versions just need proper regular vacuuming. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
