Re: Question about DB VACUUM

But as you could see from the prior query \lo_list showed no large
objects, this was done just prior to the vacuum.

aesop=# \lo_list
Large objects
ID | Description
----+-------------
(0 rows)

aesop=# vacuum verbose pg_largeobject;
NOTICE: --Relation pg_largeobject--
NOTICE: Index pg_largeobject_loid_pn_index: Pages 2819; Tuples 460:
Deleted 84.

CPU 0.21s/0.02u sec elapsed 0.23 sec.
NOTICE: Removed 84 tuples in 19 pages.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
NOTICE: Pages 19: Changed 0, Empty 0; Tup 460: Vac 84, Keep 460, UnUsed
2.
Total CPU 0.22s/0.03u sec elapsed 0.25 sec.
VACUUM

I am using the JDBC LargeObject.delete() method to remove large objects
from the pg_largeobject table. Could you suggest a better mechanism to
use from java?

Chris

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, October 06, 2003 8:53 PM
To: cjwhite(at)cisco(dot)com
Cc: 'Robert Treat'; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Question about DB VACUUM

"Chris White \(cjwhite\)" <cjwhite(at)cisco(dot)com> writes:
> Why aren't there any unused tuples?

The "unused" number isn't especially interesting, it's just the number
of line pointer slots that were once used and aren't at the moment. At 4
bytes apiece, they aren't costing you anything worth noticing.

> Why is the pg_largeobject_loid_pn_index table so big (2818 pages)?

This looks like a standard "index bloat" problem (see the archives for
details). "REINDEX pg_largeobject" would make the bloat go away for
awhile. 7.4 should largely solve this problem, but in earlier releases
you need to figure on periodic reindexing.

> Why has table grown by 4 pages.

Probably because there are now 460 live tuples instead of 227. I don't
think you've entirely fixed your problem of not removing all unused
large objects...

regards, tom lane