Re: slow pgsql tables - need to vacuum?

On Monday 07 April 2008, Dan99 <power919(at)gmail(dot)com> wrote:
> Does TRUNCATE TABLE keep all necessary table
> information such as indexes, constraints, triggers, rules, and
> privileges?

Yes. It does require an exclusive lock on the table very briefly, though,
which DELETE does not.

> Currently a mass DELETE is being used to remove the data.

And that's why the table is bloating. Especially if you aren't VACUUMing it
before loading the new data.

> Since VACUUM has never been done on the tables before, should a VACUUM
> FULL be done first? If so, approximately how long does a VACUUM FULL
> take on a database with 25 tables each having anywhere form 1,000 to
> 50,000 rows?

Honestly, you'd be better off dumping and reloading the database. With that
little data, it would be pretty quick. Although, VACUUM is pretty fast on
tables with no indexes.

> The reason I ask is because this is a live website, and
> any down time is very inconvenient. Also, would it be sufficient
> (after the first VACUUM FULL) to simply use a VACUUM ANALYZE after the
> tables are repopulated (ie. every night)?

If you do a TRUNCATE and then a fresh load, a simple ANALYZE is sufficient.

You really should create some indexes though. Right now your queries are
looping through the whole table for every SELECT. The only reason you're
not dying is your tables are small enough to completely fit in memory, and
presumably your query load is fairly low.

--
Alan