Re: VACUUM FULL doesn't reduce table size

On 03/09/2015 07:37 AM, pinker wrote:
> Adrian Klaver-4 wrote
>> On 03/09/2015 07:08 AM, pinker wrote:
>>> I did: select pg_cancel_backend(pid) from pg_stat_activity where usename
>>> <>
>>> 'mine';
>>
>> What makes you think that queries from usename = 'mine' are not important?
>>
>> Because on production I don't have access to this table.
>>
>> Or to get back to the original request:
>>
>> What does select * from pg_stat_activity show?
>>
>> mainly idle connections and queries to tables in different schemas.
>> query select * from pg_stat_activity where query ilike '%my_table%' shows
>> nothing as well.

And there is no relation between this table and the tables or functions
being queried?

>>
>> Also did the queries actually get cancelled?
>> Yes, but not all.

So what do you see in pg_stat_activity where state ilike 'idle in
transaction%'?

>>
>>>
>>> and then tried again vacuum full:
>>>
>>> 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.
>>> CPU 1.07s/1.56u sec elapsed 3.24 sec.
>>> Query returned successfully with no result in 6436 ms.
>>>
>>>
>>> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2
>>> 20140120 (Red Hat 4.8.2-16), 64-bit
>>>
>>> It was flash copy snapshot
>>
>> So what does that mean?
>>
>> 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....

If  snapshot is what I think it means, you might want to point them at:

http://www.postgresql.org/docs/9.3/interactive/backup-file.html


>>
>>>
>>
>>
>> --
>> Adrian Klaver
>
>> adrian.klaver@
>
>>
>>
>> --
>> Sent via pgsql-general mailing list (
>
>> pgsql-general@
>
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
>
> --
> View this message in context:
http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com