On Mon, Apr 7, 2008 at 9:51 AM, Dan99 <power919@gmail.com> wrote:
> Unfortunately, I did not design this database (or the website for that
> matter) and am only maintaining it. As a result of the inexperience
> of the website designer, there are no indexes in any of the tables and
> it would be rather difficult to put them in after the fact since this
> is a live website.
Indexes can be created online with no downtime. They do block some
operations. If you're running 8.2 or 8.3, you can use CREATE INDEX
CONCURRENTLY which takes longer but doesn't block normal operations.
Otherwise, pick a time when activity is minimal to do your CREATE
INDEX.
> Does TRUNCATE TABLE keep all necessary table
> information such as indexes, constraints, triggers, rules, and
> privileges? Currently a mass DELETE is being used to remove the data.
Read the docs. It may depend on your version of Postgres. See below
for docs location.
> 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? 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 have the extra disk space, CLUSTER is supposed to be better
than VACUUM FULL, but you need an index to cluster the table on.
If you use TRUNCATE, the VACUUM is not necessary but an ANALYZE would be useful.
I don't think you ever said what version you're running--that would be
helpful. "SELECT version();" at the psql prompt will tell you the
server version.
I highly recommend referring to the docs for your version of Postgres at:
http://www.postgresql.org/docs/
if you have any questions about the above commands.
-Doug
