Re: Vacuum Question

I think you asked the same question 11 years ago. 😀

- Seriously, though, 200GB is less than 1% of 22TB. There are bigger
problems if you're running that razor-thin on disk space.
- Is the transaction rate on the active tables sooo high that there's a
real chance of wrap-around?
- According to my interpretation of the docs, if you VACUUM FREEZE the
big static tables, then you won't need to vacuum them again, nor worry
about wrap-around problems.

https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

> PostgreSQL reserves a special XID, FrozenTransactionId, which *does not
> follow the normal XID comparison rules* and is *always considered older
> than every normal XID*. Frozen row versions are treated as if the
> inserting XID were FrozenTransactionId, so that they *will appear to be **“in
> the past”** to all normal transactions regardless of wraparound issues*,
> and so such row versions will be valid until deleted, no matter how long
> that is.

On Mon, Sep 22, 2025 at 11:06 AM Murthy Nunna <mnunna(at)fnal(dot)gov> wrote:

> Version 14.13
>
>
>
> I have a large database 22 TB, and it has lot of tables. Most of the
> tables do not change (static). But the age(relfrozenxid) of those tables
> keep increasing because there are some other tables in the database that
> are updated. The size of these large static tables are about 200 GB on an
> average. And to prevent transaction ID wrap around, I have been doing
> manual vacuum table by table (couple of tables a day due to limited WAL
> disk space). Each table generates WAL size of 90% of the tablesize approx.
>
> e.g
>
> Tablesize = 200 GB. Time takes to run vacuum = 1 hour 45 minutes. WAL
> generated 182 GB
>
>
>
> I tried VACUUM FREEZE also, but the WAL generated and time it takes is no
> significantly different.
>
>
>
> Following is an example output of a table vacuum:
>
>
>
> vacuumdb: vacuuming database "large_db"
>
> INFO: aggressively vacuuming "public.tab_111"
>
> INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
>
> INFO: table "tab_111": found 0 removable, 527846215 nonremovable row
> versions in 15396753 out of 15396753 pages
>
> DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 954951860
>
> Skipped 0 pages due to buffer pins, 0 frozen pages.
>
> CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111.88 s.
>
> INFO: aggressively vacuuming "pg_toast.pg_toast_17386"
>
> INFO: table "pg_toast_17386": found 0 removable, 32180684 nonremovable
> row versions in 7981550 out of 7981550 pages
>
> DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 955034530
>
> Skipped 0 pages due to buffer pins, 0 frozen pages.
>
> CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.04 s.
>
>
>
> Is there a way I can minimize WAL generation? My issue is amount of WAL
> rather than time it takes to run. Since it is not locking the table I do
> not mind long run time.
>
> I know one way is to pgdump/restore but it takes a long time and further
> to that I have to rebuild replicas.
>
>
>
> Please note, I have autovacuum turned on and it is doing what it is
> supposed to do on tables that change. So, there is no issue there.
>
>
>
> I very much appreciate any help/advice you can provide.
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!