pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlarge objects & space usage
5+ messages / 4 participants
[nested] [flat]
* large objects & space usage
@ 2008-01-13 20:01 Kevin Kempter <kevin@kevinkempterllc.com>
2008-01-13 20:12 ` Re: large objects & space usage Milen A. Radev <milen@radev.net>
2008-01-13 21:04 ` Re: large objects & space usage Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Kevin Kempter @ 2008-01-13 20:01 UTC (permalink / raw)
To: pgsql-admin
Hi List;
I'm engaging with a client who is trying to solve what sounds like a bloat
issue. However in further conversations they told me this:
1) we have a table that is an integer key and a blob column
2) the table was at 30Gig, we went in and deleted the last 2 months of data
and it's still at 30Gog
At this point I thought bloat and that vacuum full or a table rebuild would
fix the issue. However later they told me:
3) we did a pg_dump and a reload into a database on the test box and the table
is still 30Gig.
I haven't gotten access to their servers yet so I only have the above info
however I wonder is there special space considerations or storage
requirements, etc with blobs ?
Thanks in advance...
/Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: large objects & space usage
2008-01-13 20:01 large objects & space usage Kevin Kempter <kevin@kevinkempterllc.com>
@ 2008-01-13 20:12 ` Milen A. Radev <milen@radev.net>
1 sibling, 0 replies; 5+ messages in thread
From: Milen A. Radev @ 2008-01-13 20:12 UTC (permalink / raw)
To: pgsql-admin
Kevin Kempter написа:
> Hi List;
>
> I'm engaging with a client who is trying to solve what sounds like a bloat
> issue. However in further conversations they told me this:
>
> 1) we have a table that is an integer key and a blob column
> 2) the table was at 30Gig, we went in and deleted the last 2 months of data
> and it's still at 30Gog
>
> At this point I thought bloat and that vacuum full or a table rebuild would
> fix the issue. However later they told me:
>
> 3) we did a pg_dump and a reload into a database on the test box and the table
> is still 30Gig.
>
> I haven't gotten access to their servers yet so I only have the above info
> however I wonder is there special space considerations or storage
> requirements, etc with blobs ?
[...]
So what is it - large objects or BYTEA?
--
Milen A. Radev
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: large objects & space usage
2008-01-13 20:01 large objects & space usage Kevin Kempter <kevin@kevinkempterllc.com>
@ 2008-01-13 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-14 00:58 ` Re: large objects & space usage David Wall <d.wall@computer.org>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-01-13 21:04 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin
Kevin Kempter <kevin@kevinkempterllc.com> writes:
> 3) we did a pg_dump and a reload into a database on the test box and the table
> is still 30Gig.
You need to find out *exactly* what they did there.
The idea that comes to mind is that they dropped and reloaded the table
itself, but didn't do anything about cleaning out pg_largeobject, and
when they say "the table is still 30Gig" they actually mean the whole
database directory is still 30Gig.
If there are no other tables storing large objects in the database,
dropping and reloading the whole database is probably the simplest
solution. Otherwise they're going to need to make sure the unwanted
LOs have been cleaned out (see contrib/vacuumlo) and then do a VACUUM
FULL or similar on pg_largeobject.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: large objects & space usage
2008-01-13 20:01 large objects & space usage Kevin Kempter <kevin@kevinkempterllc.com>
2008-01-13 21:04 ` Re: large objects & space usage Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-14 00:58 ` David Wall <d.wall@computer.org>
2008-01-14 02:04 ` Re: large objects & space usage Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David Wall @ 2008-01-14 00:58 UTC (permalink / raw)
To: pgsql-admin
> If there are no other tables storing large objects in the database,
> dropping and reloading the whole database is probably the simplest
> solution. Otherwise they're going to need to make sure the unwanted
> LOs have been cleaned out (see contrib/vacuumlo) and then do a VACUUM
> FULL or similar on pg_largeobject.
>
Does the psql command 'vacuum;' by itself automatically vacuum
pg_largeobject along with all of tables in the database?
David
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: large objects & space usage
2008-01-13 20:01 large objects & space usage Kevin Kempter <kevin@kevinkempterllc.com>
2008-01-13 21:04 ` Re: large objects & space usage Tom Lane <tgl@sss.pgh.pa.us>
2008-01-14 00:58 ` Re: large objects & space usage David Wall <d.wall@computer.org>
@ 2008-01-14 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2008-01-14 02:04 UTC (permalink / raw)
To: d.wall@computer.org; +Cc: pgsql-admin
David Wall <d.wall@computer.org> writes:
> Does the psql command 'vacuum;' by itself automatically vacuum
> pg_largeobject along with all of tables in the database?
Yes, if it's issued by a superuser or the database owner.
However, that won't help much if pg_largeobject has become bloated by a
lot of no-longer-wanted BLOBs. After deleting those, you'll probably
need VACUUM FULL or CLUSTER or some such if you want to reclaim the
disk space. Plain VACUUM usually isn't very successful at shortening
a table file.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-01-14 02:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-13 20:01 large objects & space usage Kevin Kempter <kevin@kevinkempterllc.com>
2008-01-13 20:12 ` Milen A. Radev <milen@radev.net>
2008-01-13 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-14 00:58 ` David Wall <d.wall@computer.org>
2008-01-14 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
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