pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAfter vacuum db size is increasing
3+ messages / 3 participants
[nested] [flat]
* After vacuum db size is increasing
@ 2021-08-16 16:20 srijith s <udbdoctor@gmail.com>
2021-08-16 18:50 ` Re: After vacuum db size is increasing Peter M. Groen <pgroen@osdev.nl>
2021-08-16 21:40 ` Re: After vacuum db size is increasing Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 2 replies; 3+ messages in thread
From: srijith s @ 2021-08-16 16:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Postgres Admins
Any help is much appreciated . When i am running vacuum full, the database size is getting increased a lot, my database was around 100gb and when i did vacuum full on the large table ( which consume 95% of data in the database) , database size become 200+ gb , then i did a full vacuum on the database itself and that made the db grow to 300+gb 🙄 and filled my data drive .
The large table size is showing only 80gb.
I am running Postgres on windows. Anybody know why its behaving like this
Thanks in advance
Regards
Sri
Sent from my iPhone
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: After vacuum db size is increasing
2021-08-16 16:20 After vacuum db size is increasing srijith s <udbdoctor@gmail.com>
@ 2021-08-16 18:50 ` Peter M. Groen <pgroen@osdev.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Peter M. Groen @ 2021-08-16 18:50 UTC (permalink / raw)
To: srijith s <udbdoctor@gmail.com>; pgsql-admin@lists.postgresql.org
It is a misconception that VACUUM will decrease allocated diskspace.
PG is allocating tablespace, used by data or not. If it needs more, it
will extend those. Downsize of this ( pun intended ) that tablespaces
normally *don't* shrink. You saying your database is only using 80Gb
but this is only 80Gb of *data*. That 80Gb is probably used within the
tablespace mentioned.
I don;t know the setup of your database but it sounds to me, your
tablespace is extended during the VACUUM operation for whatever
reason.. (Temporary space?) It seems odd to me that it "adds" the exact
amount of space as it was initially...
--
Open Systems Development B.V.
Peter M. Groen ( Managing Director )
Rooseveltstraat 18-P
2321 BM Leiden
Mob : +31 6 81 10 35 37
Email : peter@osdev.nl
Slype : peter_m_groen
On Mon, 2021-08-16 at 12:20 -0400, srijith s wrote:
> Hi Postgres Admins
>
> Any help is much appreciated . When i am running vacuum full, the
> database size is getting increased a lot, my database was around
> 100gb and when i did vacuum full on the large table ( which consume
> 95% of data in the database) , database size become 200+ gb , then i
> did a full vacuum on the database itself and that made the db grow to
> 300+gb 🙄 and filled my data drive .
> The large table size is showing only 80gb.
>
> I am running Postgres on windows. Anybody know why its behaving like
> this
>
> Thanks in advance
> Regards
> Sri
>
>
>
>
>
> Sent from my iPhone
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: After vacuum db size is increasing
2021-08-16 16:20 After vacuum db size is increasing srijith s <udbdoctor@gmail.com>
@ 2021-08-16 21:40 ` Keith Fiske <keith.fiske@crunchydata.com>
1 sibling, 0 replies; 3+ messages in thread
From: Keith Fiske @ 2021-08-16 21:40 UTC (permalink / raw)
To: srijith s <udbdoctor@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Aug 16, 2021 at 12:20 PM srijith s <udbdoctor@gmail.com> wrote:
> Hi Postgres Admins
>
> Any help is much appreciated . When i am running vacuum full, the database
> size is getting increased a lot, my database was around 100gb and when i
> did vacuum full on the large table ( which consume 95% of data in the
> database) , database size become 200+ gb , then i did a full vacuum on the
> database itself and that made the db grow to 300+gb 🙄 and filled my data
> drive .
> The large table size is showing only 80gb.
>
> I am running Postgres on windows. Anybody know why its behaving like this
>
> Thanks in advance
> Regards
> Sri
>
>
>
>
>
> Sent from my iPhone
>
>
Running a VACUUM FULL is essentially rewriting the entire table. While it
is running, it is making a secondary copy of the object given. Doing this
on a single table could use up to double the size of the existing table
while it is running. It is also generating WAL files in addition to this
which will take up even more space.
After it completes, then and only then, will you get your space back. And
it will all depend on how much space was actually bloat to begin with.
It is recommended to keep an eye on your database's disk space and never
let it grow above 75% usage for these very sorts of situations. Often to
reclaim space in PostgreSQL you must use additional space to reclaim it
back.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-08-16 21:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-16 16:20 After vacuum db size is increasing srijith s <udbdoctor@gmail.com>
2021-08-16 18:50 ` Peter M. Groen <pgroen@osdev.nl>
2021-08-16 21:40 ` Keith Fiske <keith.fiske@crunchydata.com>
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