Re: Table Bloat still there after the Vacuum

*What I did was, I issued the following command *
*
*
*$vacuumdb -d prodDB -t orders -f -z -v
*
*
*
*
*
* "Orders": found 0 removable, 27164544 nonremovable row versions in 518971
pages*
*DETAIL: 27126176 dead row versions cannot be removed yet.*
*Nonremovable row versions range from 118 to 213 bytes long.*
*There were 10425 unused item pointers.*
*Total free space (including removable row versions) is 35613716 bytes.*
*0 pages are or will become empty, including 0 at the end of the table.*
*89274 pages containing 12011420 free bytes are potential move destinations.
*
*CPU 15.53s/16.55u sec elapsed 62.78 sec.*
*INFO: index "idx_orders_id" now contains 27164544 row versions in 95569
pages*
*DETAIL: 0 index row versions were removed.*
*0 index pages have been deleted, 0 are currently reusable.*
*CPU 3.18s/4.35u sec elapsed 20.52 sec.*
*INFO: "Orders": moved 6 row versions, truncated 518971 to 518971 pages*
*DETAIL: CPU 0.08s/0.08u sec elapsed 7.69 sec.*
*INFO: index " idx_orders_id" now contains 27164544 row versions in 95569
pages*
*DETAIL: 6 index row versions were removed.*
*0 index pages have been deleted, 0 are currently reusable.*
*CPU 2.25s/2.78u sec elapsed 14.97 sec.*
*INFO: vacuuming "pg_toast.pg_toast_1059337"*
*INFO: "pg_toast_1059337": found 0 removable, 0 nonremovable row versions
in 0 pages*
*DETAIL: 0 dead row versions cannot be removed yet.*
*Nonremovable row versions range from 0 to 0 bytes long.*
*There were 0 unused item pointers.*
*Total free space (including removable row versions) is 0 bytes.*
*0 pages are or will become empty, including 0 at the end of the table.*
*0 pages containing 0 free bytes are potential move destinations.*
*CPU 0.00s/0.00u sec elapsed 0.00 sec.*
*INFO: index "pg_toast_1059337_index" now contains 0 row versions in 1
pages*
*DETAIL: 0 index pages have been deleted, 0 are currently reusable.*
*CPU 0.00s/0.00u sec elapsed 0.00 sec.*
*INFO: analyzing "Orders"*

Regards

On Mon, Apr 26, 2010 at 10:55 AM, Bill Moran <wmoran(at)potentialtech(dot)com>wrote:

> In response to akp geek <akpgeek(at)gmail(dot)com>:
>
> > Hi All -
> >
> > I have a table bloated with following details
> > rows:29431 pages:516039 shouldbe:534 (966.4X) wasted size:4223016960 (3
> GB)
> > *
> >
> > I did a vacuum on the database and also I did vacuumdb
> > full on the table. Still there is no change. Can you please suggest if
> there
> > is any other operation that can be done to take care of the issue
>
> VACUUM doesn't guarantee that it will clean all the bloat out, it makes
> some effort to debloat, but that's not its primary function.
>
> VACUUM FULL will completely debloat a table, contingent on restrictions
> below. Is that what you're running? I'm a little confused by your
> comment "vacuumdb full on the table" which contradicts itself. Please
> provide the exact commands that your ran, along with the output that
> resulted.
>
> Neither type of VACUUM can debloat rows that are still in use by
> transactions. If the applications that connect to this database are
> keeping transactions open for long periods, it will adversely affect
> those commands' ability to clean up dead rows.
>
> There is much more here:
> http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>