0

Our postgresql db has a no-usage window of 2am to 6am.

one of the daily cron jobs already does a VACUUM FULL during this period. i am seeing no real performance hit with the ~200 odd users who use the web site. but the db is what i would classify as 'light' at this time.

however, there is forecast of data surge in the upcoming months due to some process changes in the org. my specific question:

is there a performance gain to be expected if I dump the entire db to a text file (already happens as part of db backup), drop the database, recreate it and reload the dump back. if the answer is 'yes', how significant is the gain?

or will VACUUM FULL do the job and no action is needed?

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
deepakkt
  • 82
  • 1
  • 8
  • what gave you such idea? what do you think to gain/avoid with it?.. – Vao Tsun Jan 24 '18 at 08:14
  • i am assuming that disjoint pages of tables which had data added over time will become contiguous when i load them back in one swoop. perhaps something else related on those lines – deepakkt Jan 24 '18 at 08:30
  • ah, understood now. vacuum full does it - copies all rows over to a new table and drops the old one, leaving all references and such. you really don't have to backup/restore – Vao Tsun Jan 24 '18 at 08:38

1 Answers1

0

vacuum full would fo it for you. no need to manually reload data

https://www.postgresql.org/docs/current/static/sql-vacuum.html

VACUUM FULL

can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132