Date: Wed, 1 Dec 2004 13:09:40 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Found Large Files.. what objects are they?
Message-ID: <20041201200940.GA24852@winnie.fuhr.org>
References: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>

On Wed, Dec 01, 2004 at 12:07:20PM -0600, Yudie wrote:

> I found bunch of large files (more than 1 gb) in one of database directory.
> The files looks like this:
> 69233123
> 69233123.1
> 69233123.2
> 69233123.3
> 69233123.4
> ...and so on.
> 
> These large files very delay the dumping process. 
> Anyone know what it could be & how to delete the object related?

When a file exceeds 1GB, PostgreSQL divides it into segments named
N, N.1, N.2, etc.

> How to find a table by oid?

contrib/oid2name should be helpful.  You could also query the system
catalogs: the files' parent directory should be the database's OID,
so for a file named .../12345/69233123 you could identify the
database with the following query:

SELECT datname FROM pg_database WHERE oid = 12345;

Connect to that database and find out which table or index uses the
files:

SELECT relname FROM pg_class WHERE relfilenode = 69233123;

Once you've done that, figure out why the files are so large:

Could it simply be that the table stores a lot of data?

Have you been running VACUUM?  If not, and if you've made a lot of
updates or deletes, then you'll have a lot of dead tuples;
contrib/pgstattuple can be useful for checking on that.  If that's
the case, then VACUUM FULL and/or REINDEX should recover the dead
space, but be aware that they'll acquire exclusive locks on the
objects they're working with and they might take a long time (hours)
to run.  Also, after a REINDEX a table's indexes will probably be
stored in different files; you can find out the new file names by
querying pg_class and looking at the relfilenode field.

Another possibility would be to dump, drop, recreate, and restore
the table and see if the file sizes shrink.  Again, the file names
will probably change, so query pg_class to see what the new ones
are.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/