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@sss.pgh.pa.us]
Sent: Monday, October 06, 2003 8:53 PM
To: cjwhite@cisco.com
Cc: 'Robert Treat'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question about DB VACUUM


"Chris White \(cjwhite\)" <cjwhite@cisco.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