Re: VACUUM FULL doesn't reduce table size

Joshua D. Drake <jd(at)commandprompt(dot)com> 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:

>>>> 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."

Initially the OP was reporting this, too:

DETAIL: 2989421 dead row versions cannot be removed yet.

Now that number is zero.

>> 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

No, that thread was about the same thing that this thread *started*
with, which was that there were a large number of dead row versions
which could not be removed yet. On *this* thread that was
corrected by terminating long-running transactions. That number is
now *zero*. AFAICS we have not seen the results of "SELECT
count(*)" or any other information to show that there is still any
problem after that was done. Maybe there is, but it has not yet
been demonstrated.

pinker: You can probably get to a solution to this much faster if
you do two things:

(1) Send emails directly to the pgsql-general(at)postgresql(dot)org list,
rather than going through nabble. Tom showed at least one case
where nabble failed to pass along useful information to the list,
and I have no idea how much other useful information it has not
passed along.

(2) Try to send enough information about the current state of the
problem to allow diagnosis. It is best if you can create a
reproducible test case (where you demonstrate the problem starting
from an empty database, creating all the objects and loading all
the data needed to show the problem).

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company