Re: VACUUM FULL doesn't reduce table size

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.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., "Tyler Riddle"