Re: VACUUM freeze

Hi Ankush,

On Sun, Apr 19, 2020 at 9:16 AM Ankush Chawla <ankushchawla03(at)gmail(dot)com>
wrote:

> thanks for reply
>
> Then what would be way if we want some tables to be skipped for autovacuum.
> What do you think, what should we do?
> This will enable for older version to stay.
>

VACUUM FREEZE means that you will put a lock on the table and release it
after the job is completed. It is the most effective option but requires a
kind of down time for all queries on the table.

If you refer to "older version to stay", you may intend to access deleted
data (i.e. when you run an UPDATE, you actually run an INSERT and a
DELETE). Deleted data is not accessible. If you need to have access to
historical data, I'd suggest creating a history table keeping track of all
INSERT, UPDATE and DELETE.

If you plan to reduce the impact of the VACUUM in terms of performance and
lock-time, I would recommend, on the contrary, to run it often, so that it
has a minimal amount of work to do. Disabling vacuum will increase your
probability of filling your tables with unnecessary bloat. We evaluated the
option of running a vacuum daily on a table with millions of rows and
quickly came to the conclusion that it was a really bad idea.

>
> On Sun, Apr 19, 2020 at 4:05 AM Dave Bolt <dave(at)davebolt(dot)co(dot)uk> wrote:
>
>> Hi Arnav,
>>
>> Could be wrong here, in which case someone will jump in.
>>
>> My understanding is that freeze only applies while doing the vacuum, and
>> then only when doing a manual vacuum.
>>
>>
>>
>> I looked at the documentation at
>> https://www.postgresql.org/docs/current/sql-vacuum.html and
>> https://www.postgresql.org/docs/current/routine-vacuuming.html to try to
>> confirm, but did feel a little confused.
>>
>> Hope this helps a bit.
>>
>> Dave
>>
>>
>>
>> *From:* Arnav [mailto:justdba03(at)gmail(dot)com]
>> *Sent:* 18 April 2020 19:26
>> *To:* pgsql-admin
>> *Subject:* VACUUM freeze
>>
>>
>>
>> hi
>>
>>
>>
>> what is VACUUM freeze , does it not allow autovacuum to happen ever.
>>
>> if yes till how long?
>>
>> --
>>
>> Regards
>>
>> Arnav
>>
> --
> Best Regards,
> Ankush Chawla
>

Hope it helps
Olivier
--
Olivier Gautherot
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/