From: | "Jeroen Habets" <Jeroen(at)twofoldmedia(dot)com> |
---|---|
To: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: JDBC delete not recovering space? |
Date: | 2003-04-23 15:50:59 |
Message-ID: | LHEKIEALPFANFIKAIAPJAEDKCMAA.Jeroen@twofoldmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Exactly what you expect!
See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=routine-vacu
uming.html
Excerpt:
8.2. Routine Vacuuming
PostgreSQL's VACUUM command must be run on a regular basis for several
reasons:
1) To recover disk space occupied by updated or deleted rows.
> -----Oorspronkelijk bericht-----
> Van: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]Namens John Guthrie
> Verzonden: Wednesday, April 23, 2003 17:45
> Aan: pgsql-jdbc(at)postgresql(dot)org
> Onderwerp: [JDBC] JDBC delete not recovering space?
>
>
> i am seeing some odd behavior and wonder if anyone can help me explain it
> and fix it...
>
> i've written a java task that runs in a thread and monitors disk space on
> the partition where i have my $PGDATA. if the space available
> drops below a
> certain level, i remove some old records ... and vacuum.
>
> the odd thing is that running via jdbc the space seems to not be
> recovered.
> example - if i start with 100,000 records of approximately 1K size and i
> remove 50,000, using 'df' in another shell i see no space recovery. from
> another shell, using psql, i run vacuum and again i see no gains.
>
> the really odd thing is that if i do this whole scenario in psql - create
> 100,000, delete the oldest 50,000, and vacuum - i *do* see the space
> recovered.
>
> even better (or worse), if i do the whole test in java, verify that little
> or no space is recovered, then run psql and delete a single records and
> vacuum, bam(!) i get a boatload of space back.
>
> note that in the java task i explicitly set auto-commit to true and i run
> queries from psql that show results that reflect the deletions, so i don't
> see this as a transaction problem. also, if i shut down
> postgresql i see the
> correct (reduced) record count but still no space recovery. i can restart
> it, run psql, and vacuum, but still no space recovery... but if i then
> delete a record and vacuum again, i get space, lots of space (i.e.
> megabytes).
>
> any ideas anyone? thanks
>
> john
>
> ---
> john guthrie
> psynapse technologies
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2003-04-23 15:54:40 | Re: JDBC delete not recovering space? |
Previous Message | John Guthrie | 2003-04-23 15:45:21 | JDBC delete not recovering space? |