Re: Data files became huge with no apparent reason

On Wed, 28 Aug 2002, Dario Fumagalli wrote:

> I have a main database (proj_store) that holds 34 objects (tables and
> sequences). Only one table has more than some 100s records (the products
> table, totalling 975 records). The backend is PostgreSQL 7.1.1 on a
> Caldera Linux OpenServer, compiled from sources.
>
> A full, uncompressed SQL dump (with full inserts) is about 3,4 MB.
>
> Each day, a cron-driven program I wrote one year ago updates the
> database from a MS-SQL 7.0 server (inserts and updates only, in the
> range of 2-5 each day).

> - How is it possible this exceptional growth (N.B. the database server
> has all the default values set in its configuration files - i.e. was not
> "optimized")?

Well, updates are effectively similar to delete/insert for space usage
and the deleted space isn't freed until vacuum. With 2-5 updates I'm
not sure why you'd get into that state, unless of course each was
say updating every row.

> - More urgently: why now it takes 30 seconds to perform a select
> count(*) on 900 records (no other clients connected), while in the past
> it was almost instantaneous? All database operations are now slow as
> dogs. And I have to live with this 52 MB until the technician comes with
> a new disk (1 week... he is in vacation now).

There are almost certainly a huge number of dead rows that it's checking
to see if you can see them.

> - Why do the backend crashed immediately if I try to VACUUM (ANALYZE)
> it? I think the backend claims additional disk space for this operation
> and fills the disk again, but I'm not sure.

Possibly making the logs, I'd guess, but I'm not sure.

> - And last, but not least... is it possible to restore the situation
> without loosing data (backup is 3 weeks old)? I'm able to start the

Either dump and restore, or once you can vacuum, vacuum.
I don't remember if 7.1.1 has vacuum verbose, but that'd give alot
of info on how many rows were erased and such.

If you decide to do a dump and restore, you might want to looking at
7.2.2 since you can do vacuums that do not block tables.