Re: VACUUM FULL doesn't reduce table size

On 03/09/2015 09:19 AM, Joshua D. Drake wrote:
>
> On 03/09/2015 08:57 AM, Adrian Klaver wrote:
>>
>> On 03/09/2015 08:49 AM, Kevin Grittner wrote:
>>> pinker <pinker(at)onet(dot)eu> wrote:
>>>
>>>> INFO: vacuuming "my_table"
>>>> INFO: "my_table": found 0 removable, 3043947 nonremovable row
>>>> versions in 37580 pages
>>>> DETAIL: 0 dead row versions cannot be removed yet.
>>>
>>> So there are no longer any dead rows being left behind, right?
>>>
>>> Why are we still discussing this? Do you have some other question?
>>
>> Well from the original post:
>>
>> "I have deleted a large number of records from my_table, which
>> originally had 288 MB. Then I ran vacuum full to make the table size
>> smaller. After this operation size of the table remains the same,
>> despite of the fact that table contains now only 241 rows and after
>> rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM
>> old_table - new_table size is 24kB. "
>>
>> So I think the question remains how is 241 rows = 3043947 nonremovable
>> row versions? And that number is an increase from the original number
>> which was 2989662 nonremovable row versions.
>
> TGL has answered this before:
>
> http://www.postgresql.org/message-id/14512.1282137722@sss.pgh.pa.us
>
> There are a number of things that can cause this but they are all about
> making sure that all versions of the tuple are completely and utterly of
> no use before vacuum will remove them.

And that is what this thread is trying to determine. As others and I
suspect, there is good reason to believe there is some sort of data
corruption at work. This awaits a clearer understanding of what 'It was
flash copy snapshot' means. Also per Kevin Grittner and Tom Lane there
is a Nabble issue at work where the list here is not seeing all the
information. Example:

On this list I saw:

"In other words detail the steps you took to get the snapshot.

I would like to know as well. Sysadmin team manage it, I'll ask them, but
as far I know it's matrix feature...."

on Nabble I see:

"> In other words detail the steps you took to get the snapshot.
First, they call pg_start_backup, then flash copy is done for blocks,
that were changed. Flash copy is made on another filesystem.

If this matters - xmaxes for all rows are 0.
"

Also per Kevin Grittner we are looking at a moving target, so some sort
of information about current state would be helpful.

>
> JD
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com