Re: Zero dead tuples, when significant apparent bloat

On Tue, Dec 10, 2013 at 5:57 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

> If you willing to install the pgstattuple[1] extension, what does the
> output say? Note, there is some overhead on larger tables (disk I/O
> primarily)
>

Yeah, this is a prod database that sees nontrivial traffic, so I'm not yet
ready to install pgstattuple.

> Also, check the output from bloat query at
> https://wiki.postgresql.org/wiki/Show_database_bloat
>

Interestingly, that does return results that include the tables which are
exhibiting the zero dead tuple behavior. Thanks.

On Tue, Dec 10, 2013 at 6:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dead tuples and empty space are not the same thing.
>

Can you elaborate on that?

What I'm trying to do is efficiently choose tables which are displaying
significant space bloat to schedule them for a manual vacuum full.

My understanding was that percentage of dead tuples would be a reasonable
metric, since dead tuples in the table would also be reflected in the
indexes, and since vacuum and autovacuum attempt to return pages which are
entirely empty of live tuples. Actually, I assumed that dead tuples were
also the triggering metric for autovacuum and autoanalyze.

If that method is not feasible, can you suggest a different method for
determining candidate tables to reclaim space from?

-john

--
John Melesky | Sr Database Administrator
503.284.7581 x204 | john(dot)melesky(at)rentrak(dot)com <vincent(dot)lau(at)rentrakmail(dot)com>
RENTRAK | www.rentrak.com | NASDAQ: RENT

Notice: This message is confidential and is intended only for the
recipient(s) named above. If you have received this message in error,
or are not the named recipient(s), please immediately notify the
sender and delete this message.