From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
To: Wasim Devale <wasimd60@gmail.com>
To: vrms <vrms@netcologne.de>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Maintenance
Date: Wed, 8 May 2024 15:13:57 +0000
Message-ID: <fe0abbb333ab4918af3b450468cc360a@evernorth.com> (raw)
In-Reply-To: <CAB5fag6e3gqnO6rtBByF5Aa3x28Af1e=zQq+fOmag0WChCrUww@mail.gmail.com>
References: <CAEQOu1Fu2i7EoimtG_T4FQkuN5z3L5pY6n4jHHk=xB+NWZMfMw@mail.gmail.com>
<8f36cff9-78aa-4e72-ab22-093a889a926d@gmx.net>
<CANzqJaBf=f_CLxJO8MqN45z+KTY1i6hiF3Zt1mNX6Pmz66-JdQ@mail.gmail.com>
<2323019d-ba9b-4eba-86e8-84b030bdd94a@netcologne.de>
<CAB5fag6e3gqnO6rtBByF5Aa3x28Af1e=zQq+fOmag0WChCrUww@mail.gmail.com>
Tuples are not deleted. They are zero’d out and the space becomes available as free tuple space.
I would research your vacuum stats and think to change any auto-vacuum settings per table via ALTER TABLE command AFTER you can vacuum the entire db without performance degradation.
I would not vacuum a 4TB at once. I would chunk it out over schemas, etc. once that is done, vacuum db regularly as needed or set up cron jobs to vacuum the heavy hitter tables.
Adjusting the autovacuum auto-scale too low, 3-4 places right of the decimal, can have performance degradation.
After your maintenance, to reclaim linux space (if wanted), you have to backup, DROP db, then CREATE db, reload backup.
If you are on an LVM, you may want to look at those settings too.
From: Wasim Devale <wasimd60@gmail.com>
Sent: Wednesday, May 8, 2024 7:31 AM
To: vrms <vrms@netcologne.de>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Maintenance
vrms you are correct.
On Wed, 8 May, 2024, 7:59 pm vrms, <vrms@netcologne.de<mailto:vrms@netcologne.de>> wrote:
On 5/8/24 3:10 PM, Ron Johnson wrote:
> Don't dead tuples have to be vacuumed away before the space can be reused?
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@evernorth.com, wasimd60@gmail.com, vrms@netcologne.de, pgsql-admin@lists.postgresql.org
Subject: Re: Maintenance
In-Reply-To: <fe0abbb333ab4918af3b450468cc360a@evernorth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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