4

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III.

I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. Moreover, I need to update database statistics via ANALYSIS so query executor could select the best plan and I also need to REINDEX to regenerate indexes.

The windows I use for that purposes is here: http://www.pgadmin.org/docs/dev/maintenance.html

Some questions regarding the behavior and options:

  1. if I run VACUUM without selecting any options below (FULL, FREEZE, ANALYZE) what PostgreSQL will do? Will it only free some space and that's it?

  2. If I run VACUUM with Analyze option is it the same as running VACUUM without Analyze and then running ANALYZE separately. What is working faster?

  3. Is it worth to do REINDEX after doing VACUUM and ANALYZE? I can see in verbose messages that indexes are affected somehow by VACUUM without options. Should I run REINDEX to make my indexes works faster?

3

I don't know what resources you're getting this from. Not just that PgAdmin page given some of what you're saying. The information you're relying on is either outdated or incomplete; all this is pretty much unnecessary.

Make sure that autovacuum is keeping up with the database workload and you're pretty much done. These days you should not generally need to run a manual vacuum or analyze, though it's handy after bulk loads or deletes. Manually reindexing is certainly not required as a routine operation.

See autovacuum in the docs.

  • I check my database settings for autovacuum. I can see that "autovacuum = on". Where can I find logs of this daemon so I could check that it is really working? I am using Windows, so I am not 100% sure that everything is fine – Sergei Aug 10 '13 at 14:09
  • @Sergei See the manual for logging settings related to autovacuum. – Craig Ringer Aug 10 '13 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.