pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM freeze
7+ messages / 7 participants
[nested] [flat]
* VACUUM freeze
@ 2020-04-18 18:25 Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` RE: VACUUM freeze Dave Bolt <dave@davebolt.co.uk>
2020-04-20 08:55 ` Re: VACUUM freeze Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Arnav @ 2020-04-18 18:25 UTC (permalink / raw)
To: pgsql-admin
hi
what is VACUUM freeze , does it not allow autovacuum to happen ever.
if yes till how long?
--
Regards
Arnav
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
@ 2020-04-18 22:35 ` Dave Bolt <dave@davebolt.co.uk>
2020-04-19 07:15 ` Re: VACUUM freeze Ankush Chawla <ankushchawla03@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Dave Bolt @ 2020-04-18 22:35 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@gmail.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` RE: VACUUM freeze Dave Bolt <dave@davebolt.co.uk>
@ 2020-04-19 07:15 ` Ankush Chawla <ankushchawla03@gmail.com>
2020-04-19 12:09 ` Re: VACUUM freeze Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-19 13:24 ` Re: VACUUM freeze Olivier Gautherot <ogautherot@gautherot.net>
0 siblings, 2 replies; 7+ messages in thread
From: Ankush Chawla @ 2020-04-19 07:15 UTC (permalink / raw)
To: Dave Bolt <dave@davebolt.co.uk>; pgsql-admin
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.
On Sun, Apr 19, 2020 at 4:05 AM Dave Bolt <dave@davebolt.co.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@gmail.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` RE: VACUUM freeze Dave Bolt <dave@davebolt.co.uk>
2020-04-19 07:15 ` Re: VACUUM freeze Ankush Chawla <ankushchawla03@gmail.com>
@ 2020-04-19 12:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2020-04-19 12:09 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: Dave Bolt <dave@davebolt.co.uk>; pgsql-admin
> On Apr 19, 2020, at 1:15 AM, Ankush Chawla <ankushchawla03@gmail.com> wrote:
>
> This will enable for older version to stay.
Counting on not vacuuming tables in order to keep old row versions around forever is a really bad idea. What are you trying to accomplish?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` RE: VACUUM freeze Dave Bolt <dave@davebolt.co.uk>
2020-04-19 07:15 ` Re: VACUUM freeze Ankush Chawla <ankushchawla03@gmail.com>
@ 2020-04-19 13:24 ` Olivier Gautherot <ogautherot@gautherot.net>
2020-04-19 18:26 ` Re: VACUUM freeze Keith <keith@keithf4.com>
1 sibling, 1 reply; 7+ messages in thread
From: Olivier Gautherot @ 2020-04-19 13:24 UTC (permalink / raw)
To: Ankush Chawla <ankushchawla03@gmail.com>; +Cc: Dave Bolt <dave@davebolt.co.uk>; pgsql-admin
Hi Ankush,
On Sun, Apr 19, 2020 at 9:16 AM Ankush Chawla <ankushchawla03@gmail.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@davebolt.co.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@gmail.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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` RE: VACUUM freeze Dave Bolt <dave@davebolt.co.uk>
2020-04-19 07:15 ` Re: VACUUM freeze Ankush Chawla <ankushchawla03@gmail.com>
2020-04-19 13:24 ` Re: VACUUM freeze Olivier Gautherot <ogautherot@gautherot.net>
@ 2020-04-19 18:26 ` Keith <keith@keithf4.com>
0 siblings, 0 replies; 7+ messages in thread
From: Keith @ 2020-04-19 18:26 UTC (permalink / raw)
To: Olivier Gautherot <ogautherot@gautherot.net>; +Cc: Ankush Chawla <ankushchawla03@gmail.com>; Dave Bolt <dave@davebolt.co.uk>; pgsql-admin
On Sun, Apr 19, 2020 at 9:25 AM Olivier Gautherot <ogautherot@gautherot.net>
wrote:
> Hi Ankush,
>
> On Sun, Apr 19, 2020 at 9:16 AM Ankush Chawla <ankushchawla03@gmail.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.
>
>
VACUUM FREEZE does not lock the table. You're thinking of a VACUUM FULL,
which completely rewrites the table. You can continue using a table while
VACUUM FREEZE is running, but how effective it is is all dependent on what
other transactions are running at the time the vacuum runs. But that's true
for any vacuum run, not just freeze. A freeze forces vacuum to inspect
every page and mark each tuple frozen, even if it had already been marked
frozen in the past. It's a more aggressive action that isn't actually
needed much anymore in modern postgres versions unless you're cleaning up a
database that hadn't been having vacuum run properly in the past. But once
you get things tuned properly, you should almost never need to run a forced
FREEZE vacuum.
> 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@davebolt.co.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
>>>
>>>
>>>
>>
If you are on at least version 9.6 of PostgreSQL, when a page has all of
its tuples marked as FROZEN, autovacuum will automatically be able to skip
over those pages. So if you have old tables that get no updates/deletes
anymore, autovacuum should be able to go over them very quickly the next
time it comes around to them to get things in order to prevent transaction
id exhaustion.
I've written a bit more about managing txid exhaustion here if that is your
major concern -
https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
You can also tune autovacuum on a per-table basis if needed. I would not
recommend ever turning off autovacuum unless you've worked out the exact
schedule of a manual vacuums needed for your environment. You simply cannot
stop vacuuming all-together on a database that has active writes happening
- https://www.keithf4.com/per-table-autovacuum-tuning/
Keith Fiske
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: VACUUM freeze
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
@ 2020-04-20 08:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2020-04-20 08:55 UTC (permalink / raw)
To: Arnav <justdba03@gmail.com>; pgsql-admin
On Sat, 2020-04-18 at 23:55 +0530, Arnav wrote:
> what is VACUUM freeze , does it not allow autovacuum to happen ever.
> if yes till how long?
VACUUM (FREEZE) marks all rows that are visible to everybody as
unconditionally visible.
All rows that live long have to get frozen at some point, so that they
can survive transaction wraparound without data corruption, because the
internal transaction ID that created them is stored in the row metadata.
This can be useful for insert-only tables, since autovacuum won't have
to touch frozen rows ever again unless they get updated or deleted.
The negative aspect of freezing all rows is that all rows that are frozen
get modified, which means that the block containing them becomes dirty
and has to be written to disk again. Now if any such row is modified or
deleted later, freezing the row has generated unnecessary I/O.
This is why by default VACUUM will freeze only rows whose creating
transaction is more then 50 million transactions in the past, in the
hope that such rows have less chance to be modified again than more
recent rows.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-04-20 08:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-18 18:25 VACUUM freeze Arnav <justdba03@gmail.com>
2020-04-18 22:35 ` Dave Bolt <dave@davebolt.co.uk>
2020-04-19 07:15 ` Ankush Chawla <ankushchawla03@gmail.com>
2020-04-19 12:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-04-19 13:24 ` Olivier Gautherot <ogautherot@gautherot.net>
2020-04-19 18:26 ` Keith <keith@keithf4.com>
2020-04-20 08:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox