Re: Unable to Vacuum Large Defragmented Table

po 8. 4. 2019 v 7:57 odesílatel Igal Sapir <igal(at)lucee(dot)org> napsal:

> David,
>
> On Sun, Apr 7, 2019 at 8:11 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> wrote:
>
>> On Mon, 8 Apr 2019 at 14:57, Igal Sapir <igal(at)lucee(dot)org> wrote:
>> > However, I have now deleted about 50,000 rows more and the table has
>> only 119,688 rows. The pg_relation_size() still shows 31MB and
>> pg_total_relation_size() still shows 84GB.
>> >
>> > It doesn't make sense that after deleting about 30% of the rows the
>> values here do not change.
>>
>> deleting rows does not make the table any smaller, it just creates
>> dead rows in the table. VACUUM tries to release the space used by
>> those dead rows and turns it back into free space. Normal vacuum (not
>> FULL) can only shrink the table if completely empty pages are found at
>> the end of the table.
>>
>
> ACK
>
>
>>
>> > Attempting to copy the data to a different table results in the out of
>> disk error as well, so that is in line with your assessment. But it
>> actually just shows the problem. The new table to which the data was
>> copied (though failed due to out of disk) shows 0 rows, but
>> pg_total_relation_size() for that table shows 27GB. So now I have an
>> "empty" table that takes 27GB of disk space.
>>
>> I think the best thing to do is perform a normal VACUUM on the table
>>
>
> Running VACUUM on the newly created table cleared the 27GB so that's good
> (I was planning to run normal VACUUM but ran FULL).
>

you can drop some indexes, then you can run vacuum full, and create dropped
indexes again.

>
>> then CREATE EXTENSION IF NOT EXISTS pgstattuple; then do; SELECT *
>> FROM pgstattuple('<tablename>); and the same again on the toast table.
>> If your table still contains many dead rows then perhaps an open
>> transaction is stopping rows from being turned into free space.
>
>
> I am not sure how to read the below. I see a lot of "free_space" but not
> "dead":
>
> -[ RECORD 1 ]------+------------
> ?column? | primary
> table_len | 32055296
> tuple_count | 120764
> tuple_len | 9470882
> tuple_percent | 29.55
> dead_tuple_count | 0
> dead_tuple_len | 0
> dead_tuple_percent | 0
> free_space | 20713580
> free_percent | 64.62
> -[ RECORD 2 ]------+------------
> ?column? | toast
> table_len | 88802156544
> tuple_count | 15126830
> tuple_len | 30658625743
> tuple_percent | 34.52
> dead_tuple_count | 0
> dead_tuple_len | 0
> dead_tuple_percent | 0
> free_space | 57653329312
> free_percent | 64.92
>

it say, so your table can be reduced about 60%

>
>
>> Once pgstattuples reports that "tuple_len" from the table, its toast table
>> and all its indexes has been reduced to an acceptable value then you
>> should try a VACUUM FULL. Remember that VACUUM FULL must also write
>> WAL, so if WAL is on the same volume, then you'll need to consider
>> space required for that when deciding how much data to remove from the
>> table.
>>
>
> WAL is on the same volume. The PGDATA directory is mounted in a Docker
> container.
>
> Isn't there any way to do an in-place VACUUM or pause the WAL at the risk
> of losing some data if recovery is required?
>
> There is a catch-22 here. I can't reclaim the disk space because that
> requires disk space. Surely I'm not the first one to have encountered that
> problem with Postgres.
>
>
>>
>> > This is mostly transient data, so I don't mind deleting rows, but if
>> some day this could happen in production then I have to know how to deal
>> with it without losing all of the data.
>>
>> For the future, it would be better to delete more often than waiting
>> until the table grows too large. A normal VACUUM will turn space used
>> by dead tuples back into free space, so if done often enough there
>> won't be a need to vacuum full.
>>
>
> ACK. This issue came up while implementing a retention policy that will
> be enforced regularly.
>
> Thank you for all of your help,
>
> Igal
>
>