Re: slow pgsql tables - need to vacuum?

On Apr 7, 11:14 am, ahodg(dot)(dot)(dot)(at)simkin(dot)ca (Alan Hodgson) wrote:
> On Monday 07 April 2008, Dan99 <power(dot)(dot)(dot)(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
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

A new website and hence a new database is planed for the near future,
so It is good that I am learning all this now. How do indexes work
and what columns should I put them on in a given table? Technically,
what is the difference between a VACUUM and VACUUM FULL? I know I can
probably get all this information from the docs, but I hope you guys
can bear with me just a little bit longer :)

Thanks,
Daniel