5

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?

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Sergei
  • 151
  • 2

1 Answers1

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.

Craig Ringer
  • 57,821
  • 6
  • 162
  • 193