On Apr 7, 11:14 am, ahodg...@xxxxxxxxx (Alan Hodgson) wrote: > On Monday 07 April 2008, Dan99 <power...@xxxxxxxxx> 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...@xxxxxxxxxxxxxx) > 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
