Re: Growing Database Size - Mailing list pgsql-general

On Sat, 14 Jun 2003, Kenneth Godee wrote:

> >
> > You probably want to use a reindex. If you have an index on a column
> > that only increases or decreases over time, the index won't be able
> > to use reuse pages that are no longer in use. This is fixed in 7.4.
> >
>
> I'm kinda having the same type of problem, I'm updating a entire
> database nightly with.....
>
> BEGIN
> DELETE (deleting all rows from a table)
> DELETE (deleting all rows from a table)
> COPY (populating with new rows)
> COPY (populating with new rows)
> COMMIT
> VACUUM (analyze)
>
> Since I don't fully understand the whole "FSM" settings and
> can find "NO" doc's or "ANY" info on how to tweak these settings
> besides an email now and then saying to up them. Not only that but
> (as of a few days ago) there seems to be no way to search prior posts
> on this topic. The archives seem to be off line, last time I tried it seems
> my IP address wasn't in the "pg_hba.conf" file and google SITE: searchs don't work
> either, "The page can not be found" errors.
> So my database just grows and grows and added to my monthly list of todos, I take the
> database off line into single user mode and reindex and vacuum full.
>
> Am I missing something? Does anyone know where to find any doc's on how to
> adjust the FSM settings?
> I usally try to do my home work before asking these questions, but.....

Well is that a vacuum or a vacuum analyse you're doing after that nightly
delete/copy sequence? You need to run vacuum to stand any chance of your fsm
settings actually helping you since the fsm is for tracking freed space in the
file store and vacuum is what does that scan. At least I don't think vacuum
analyse does but I may well be mistaken on that (pretty basic and I really
should look at those details some time).

The copy's are just going to be sticking every tuple after the space for all
the delete tuples without that. Also, why not use a delete, vacuum full, copy
sequence? I see you're doing this in a transaction so you probably don't want
the alternative of truncate, copy which I think, but don't know, will truncate
the file store.

Also, is there some reason you're not wanting to run vacuum full more
frequently?


>
> Any help in understanding this process would be great, I think it would help others too,

FSM stands for Free Space Map. The space taken by deleted tuples is
automatically reused unless that space is recorded in the fsm. Vacuum fills in
the fsm and vacuum full moves undeleted tuples into space taken by deleted
tuples (without using the fsm).

> Have you checked the size of your database lately?
>
> Or is all this stuff updated in the newer versions, I'm running 7.2.1.

Upgrade to 7.2.4, it should be painless and it's highly recommended.

Well if you're not reusing the same range of indexed values for each new load
of data I would expect you'll find the disk usage increasing for those
indexes. There isn't a way to avoid the periodic reindex if you want to
maintain reasonable disk usage in that case. I'm not sure if that's different
for 7.3 or 7.4.


--
Nigel J. Andrews