Maintenance

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(at)gmail(dot)com>
Sent: Wednesday, May 8, 2024 7:31 AM
To: vrms <vrms(at)netcologne(dot)de>
Cc: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: [EXTERNAL] Re: Maintenance

vrms you are correct.

On Wed, 8 May, 2024, 7:59 pm vrms, <vrms(at)netcologne(dot)de<mailto:vrms(at)netcologne(dot)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