agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedVacuum Question
11+ messages / 10 participants
[nested] [flat]
* Vacuum Question
@ 2000-06-01 01:41 brianb <brianb-pgadmin@edsamail.com>
0 siblings, 0 replies; 11+ messages in thread
From: brianb @ 2000-06-01 01:41 UTC (permalink / raw)
To: pgsql-admin
I have a Postgres application that must run 24x7. If postgres needs to be
vacuumed periodically, must I take the application offline, or is it enough
to disallow write (INSERT/UPDATE) access while allowing read access?
I hope it is the latter, as I have a large data set and there are
transactions going on all the time.
Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/
AIM: bbaquiran
^ permalink raw reply [nested|flat] 11+ messages in thread
* Vacuum Question
@ 2002-12-16 15:52 Mike Baker <bakerlmike@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Mike Baker @ 2002-12-16 15:52 UTC (permalink / raw)
To: pgsql-admin
Hi.
I am running a weekly cron job to vacuum our
production database. Everything seems to look OK,
except for the report around the pg_largeobjects
table. I was wondering if there is some tunning that I
need to do to my database, or if it is normal to have
so many tuples and deletes for pg_largeobjects. Here
is the output:
VACUUM
--Relation pg_largeobject--
Pages 409852: Changed 6205, reaped 393786, Empty 0,
New 0; Tup 1287298: Vac 14028, Keep/VTL 0/0, Crash 0,
UnUsed 454755, MinLen 45, MaxLen 2092; Re-using:
Free/Avail. Space 716319960/716272248; EndEmpty/Avail.
Pages 0/391372. CPU 17.65s/1.09u sec.
Index pg_largeobject_loid_pn_index: Pages 13833;
Tuples 1287298: Deleted 14028. CPU 0.65s/1.99u sec.
Rel pg_largeobject: Pages: 409852 --> 407718; Tuple(s)
moved: 4278. CPU 64.96s/194.85u sec.
Index pg_largeobject_loid_pn_index: Pages 13833;
Tuples 1287298: Deleted 4278. CPU 0.45s/0.68u sec.
Analyzing..
Thanks
Mike Baker
SCM Labs, Inc.
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Vacuum Question
@ 2003-02-21 07:48 Pascal PEYRE <peyre@cir.fr>
0 siblings, 3 replies; 11+ messages in thread
From: Pascal PEYRE @ 2003-02-21 07:48 UTC (permalink / raw)
To: pgsql-admin
Hi,
I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686
I have some big postgreSQL databases (4/5 GB at start) on this server.
Every night I erase data and I import a lot of new data.
For optimize my database I operate a vacuum "all" every night.
The problem is that the size of database is growing every day whereas
the volume of data stay remains stable. After six month my postgreSQL
databases size is approximately (40/50 Gb). Yesterday I suppress the
database and I restore it by pg_dump. By this way my database size is
gone down again to 4/5 Gb.
Is there any solution to reduce the size of the database without restore
a SQL backup ?
Thanks to your help
Pascal PEYRE
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Vacuum Question
@ 2003-02-22 02:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pascal PEYRE <peyre@cir.fr>
2 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-02-22 02:41 UTC (permalink / raw)
To: Pascal PEYRE <peyre@cir.fr>; +Cc: pgsql-admin
"Pascal PEYRE" <peyre@cir.fr> writes:
> I have some big postgreSQL databases (4/5 GB at start) on this server.
> Every night I erase data and I import a lot of new data.
Exactly how do you erase the old data? If you're zapping the entire
contents of tables, TRUNCATE might be a good answer.
> For optimize my database I operate a vacuum "all" every night.
Plain vacuum, or vacuum full? If it's a plain vacuum, do you have the
free space map parameters (in postgresql.conf) set large enough to cover
your database?
> The problem is that the size of database is growing every day whereas
> the volume of data stay remains stable.
Exactly which tables are bloating? Look at the relpages column of
pg_class (just after a vacuum, so that the values are up to date).
Look to see which entries grow from day to day...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Vacuum Question
@ 2003-02-23 12:19 Daniel Schuchardt <daniel_schuchardt@web.de>
parent: Pascal PEYRE <peyre@cir.fr>
2 siblings, 0 replies; 11+ messages in thread
From: Daniel Schuchardt @ 2003-02-23 12:19 UTC (permalink / raw)
To: pgsql-admin
Do a VACUUM FULL on your database. This should be the solution.
Daniel
""Pascal PEYRE"" <peyre@cir.fr> schrieb im Newsbeitrag
news:000001c2d97d$b11814c0$e3026b83@intranet.cir.fr...
> Hi,
>
> I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686
>
> I have some big postgreSQL databases (4/5 GB at start) on this server.
> Every night I erase data and I import a lot of new data.
>
> For optimize my database I operate a vacuum "all" every night.
>
> The problem is that the size of database is growing every day whereas
> the volume of data stay remains stable. After six month my postgreSQL
> databases size is approximately (40/50 Gb). Yesterday I suppress the
> database and I restore it by pg_dump. By this way my database size is
> gone down again to 4/5 Gb.
>
> Is there any solution to reduce the size of the database without restore
> a SQL backup ?
>
> Thanks to your help
>
> Pascal PEYRE
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Vacuum Question
@ 2003-02-25 11:42 Oleg Samoylov <olleg@telecom.mipt.ru>
parent: Pascal PEYRE <peyre@cir.fr>
2 siblings, 0 replies; 11+ messages in thread
From: Oleg Samoylov @ 2003-02-25 11:42 UTC (permalink / raw)
To: pgsql-admin
Reindex database also. Look for documentation 7.3.1 , Rounting Reindex.
contrib/reindex can help you, even you have older version.
Pascal PEYRE wrote:
> Hi,
>
> I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686
>
> I have some big postgreSQL databases (4/5 GB at start) on this server.
> Every night I erase data and I import a lot of new data.
>
> For optimize my database I operate a vacuum "all" every night.
>
> The problem is that the size of database is growing every day whereas
> the volume of data stay remains stable. After six month my postgreSQL
> databases size is approximately (40/50 Gb). Yesterday I suppress the
> database and I restore it by pg_dump. By this way my database size is
> gone down again to 4/5 Gb.
>
> Is there any solution to reduce the size of the database without restore
> a SQL backup ?
>
> Thanks to your help
>
> Pascal PEYRE
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Olleg Samoylov
^ permalink raw reply [nested|flat] 11+ messages in thread
* vacuum question
@ 2009-01-22 14:45 Stefano Nichele <stefano.nichele@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Stefano Nichele @ 2009-01-22 14:45 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I would like to ask your help in understanding vacuum activities.
I have a heavily-updated table with this structure:
colA bigint not null
colB character varying(128) not null
colC character varying(200) not null
colD character varying(200) not null
colE character varying(20)
Indexes:
"pk_tableA" PRIMARY KEY, btree (colA, colB, colC, colD)
Additional info:
pg_relation_size= 3453 MB
pg_total_relation_size= 7596 MB
I ran vacuum full verbose and that was the output:
----------------------------------------------------------------------------------------------------------
INFO: vacuuming "public.tableA"
INFO: "tableA": found 1284708 removable, 28569007 nonremovable row
versions in 441967 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 59 to 361 bytes long.
There were 8024509 unused item pointers.
Total free space (including removable row versions) is 466596580 bytes.
21297 pages are or will become empty, including 0 at the end of the table.
326363 pages containing 463153220 free bytes are potential move
destinations.
CPU 6.35s/2.90u sec elapsed 78.09 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 530257 pages
DETAIL: 328056 index row versions were removed.
19516 index pages have been deleted, 19516 are currently reusable.
CPU 7.45s/12.99u sec elapsed 108.42 sec.
INFO: "tableA": moved 1739094 row versions, truncated 441967 to 383291
pages
DETAIL: CPU 38.12s/958.74u sec elapsed 2414.47 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 532924 pages
DETAIL: 1739094 index row versions were removed.
13949 index pages have been deleted, 13949 are currently reusable.
CPU 8.46s/4.97u sec elapsed 132.59 sec.
----------------------------------------------------------------------------------------------------------
Then I still ran vacuum full and it was the output:
----------------------------------------------------------------------------------------------------------
INFO: vacuuming "public.tableA"
INFO: "tableA": found 0 removable, 28569007 nonremovable row versions
in 383291 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 59 to 361 bytes long.
There were 3496277 unused item pointers.
Total free space (including removable row versions) is 10348068 bytes.
0 pages are or will become empty, including 0 at the end of the table.
28326 pages containing 1795632 free bytes are potential move destinations.
CPU 5.05s/1.99u sec elapsed 53.20 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 532924 pages
DETAIL: 0 index row versions were removed.
13949 index pages have been deleted, 13949 are currently reusable.
CPU 7.00s/10.82u sec elapsed 91.17 sec.
INFO: "tableA": moved 0 row versions, truncated 383291 to 383291 pages
DETAIL: CPU 7.50s/10.93u sec elapsed 198.59 sec.
----------------------------------------------------------------------------------------------------------
At the end:
pg_relation_size= 3010 MB
pg_total_relation_size= 7173 MB
I saw that the second time it says:
There were 3496277 unused item pointers
Is this correct ? Should it not be 0 the second time ?
Do you see anything strange in the output ? (of course I'm asking since
i have a lot of queries that go in timeout)
Do you know any useful documentation that could help me in understanding
all those numbers ?
Cheers and thanks in advance.
ste
^ permalink raw reply [nested|flat] 11+ messages in thread
* Vacuum Question
@ 2025-09-22 15:06 Murthy Nunna <mnunna@fnal.gov>
0 siblings, 2 replies; 11+ 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] 11+ messages in thread
* Re: Vacuum Question
@ 2025-09-22 15:38 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Murthy Nunna <mnunna@fnal.gov>
1 sibling, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2025-09-22 15:38 UTC (permalink / raw)
To: pgsql-admin; Murthy Nunna <mnunna@fnal.gov>
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@fnal.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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Vacuum Question
@ 2025-09-22 20:02 Murthy Nunna <mnunna@fnal.gov>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Murthy Nunna @ 2025-09-22 20:02 UTC (permalink / raw)
To: pgsql-admin
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Monday, September 22, 2025 10:39 AM
To: pgsql-admin <pgsql-admin@postgresql.org>; Murthy Nunna <mnunna@fnal.gov>
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] 11+ messages in thread
* Re: Vacuum Question
@ 2025-09-23 06:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Murthy Nunna <mnunna@fnal.gov>
1 sibling, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2025-09-23 06:38 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; 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] 11+ messages in thread
end of thread, other threads:[~2025-09-23 06:38 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-01 01:41 Vacuum Question brianb <brianb-pgadmin@edsamail.com>
2002-12-16 15:52 Vacuum Question Mike Baker <bakerlmike@yahoo.com>
2003-02-21 07:48 Vacuum Question Pascal PEYRE <peyre@cir.fr>
2003-02-22 02:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-23 12:19 ` Daniel Schuchardt <daniel_schuchardt@web.de>
2003-02-25 11:42 ` Oleg Samoylov <olleg@telecom.mipt.ru>
2009-01-22 14:45 vacuum question Stefano Nichele <stefano.nichele@gmail.com>
2025-09-22 15:06 Vacuum Question Murthy Nunna <mnunna@fnal.gov>
2025-09-22 15:38 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-09-22 20:02 ` Murthy Nunna <mnunna@fnal.gov>
2025-09-23 06:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox