public inbox for [email protected]help / color / mirror / Atom feed
Vacuum Question 4+ messages / 3 participants [nested] [flat]
* Vacuum Question @ 2025-09-22 15:06 Murthy Nunna <[email protected]> 2025-09-22 15:38 ` Re: Vacuum Question Ron Johnson <[email protected]> 2025-09-23 06:38 ` Re: Vacuum Question Laurenz Albe <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Murthy Nunna @ 2025-09-22 15:06 UTC (permalink / raw) To: pgsql-admin 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Vacuum Question 2025-09-22 15:06 Vacuum Question Murthy Nunna <[email protected]> @ 2025-09-22 15:38 ` Ron Johnson <[email protected]> 2025-09-22 20:02 ` RE: Vacuum Question Murthy Nunna <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Ron Johnson @ 2025-09-22 15:38 UTC (permalink / raw) To: pgsql-admin; Murthy Nunna <[email protected]> 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 <[email protected]> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Vacuum Question 2025-09-22 15:06 Vacuum Question Murthy Nunna <[email protected]> 2025-09-22 15:38 ` Re: Vacuum Question Ron Johnson <[email protected]> @ 2025-09-22 20:02 ` Murthy Nunna <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Murthy Nunna @ 2025-09-22 20:02 UTC (permalink / raw) To: pgsql-admin From: Ron Johnson <[email protected]> Sent: Monday, September 22, 2025 10:39 AM To: pgsql-admin <[email protected]>; Murthy Nunna <[email protected]> Subject: Re: Vacuum Question [EXTERNAL] – This message is from an external sender 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. * I have 10 TB disk for WALs which is separate from /pgdata disk. But with the amount of WALs vacuum is generating, even 10 TB could fill up. * Is the transaction rate on the active tables sooo high that there's a real chance of wrap-around? * Given the xid numbers, I would say it is high. It went from 786089 to 965376038 in 12 months * 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. * About a year ago, I did pgdump/restore. But I did not perform vacuum though. So, may be I should complete my table by table vacuum at least once then. https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND<https://urldefens...; 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Vacuum Question 2025-09-22 15:06 Vacuum Question Murthy Nunna <[email protected]> @ 2025-09-23 06:38 ` Laurenz Albe <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Laurenz Albe @ 2025-09-23 06:38 UTC (permalink / raw) To: Murthy Nunna <[email protected]>; pgsql-admin On Mon, 2025-09-22 at 15:06 +0000, Murthy Nunna 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? If the data won't change any more, run a VACUUM (FREEZE) on the table. That should freeze all rows, and any subsequent VACUUM will finish very quickly and produce no WAL. > About a year ago, I did pgdump/restore. But I did not perform vacuum though. So, may be I > should complete my table by table vacuum at least once then. If you restore a table from a dump, all the rows will be unfrozen. It will take another VACUUM (FREEZE) to freeze the rows. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-23 06:38 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-22 15:06 Vacuum Question Murthy Nunna <[email protected]> 2025-09-22 15:38 ` Ron Johnson <[email protected]> 2025-09-22 20:02 ` Murthy Nunna <[email protected]> 2025-09-23 06:38 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox