Postgresql: what does "Optimze" do? vaccumdb? reindexdb?

When clicking "Optimize" in Resolve with PostgreSQL, it asks for the bin/ directory. However, we don't use the local Postgres, but one on a server. That may not be the exact same version than the local Postgres install, so I would rather not run the local executables, but the ones on the server.

To "optimize" on the server itself, possibly through a cron job, I need to know what "optimize" is actually doing.

I guess it does "vacuumdb". Can someone confirm? And does it also add the "--analyze" switch?
Does it also run "reindexdb"?

Also, is there any reason to run vacuumdb by hand, when the server does autovacuum anyway?

Or does "optimize" do something completely different, or in a very special way?

(There was a very similar question here: Which specific flags for PostgreSQL client applications? but unfortunately it didn't get any reply)

Yup, I went back to Jathavan Sriram’s article and saw that it is indeed vacuumdb and reindexdb.

I would recommend that you don’t try to set up a cron job—on macOS launchd is preferred, and on Linux, systemd.

I have a tool that will let you easily set all this up for each individual database.

https://github.com/sethgoldin/davinci-r ... flow-tools


Sent from my iPhone using Tapatalk

Thanks. I had come across your code on github, but hadn't realized it was yours. And I had missed the link to Javathan's article.

Unfortunately, we still don't know for sure what exactly the original Resolve "Optimize" button does.

I see you run vacuumdb with --analyze, and also run reindexdb.

In your experience, does it really improve things over what autovacuum does already?

I just took Jathavan’s word for what “Optimize” was doing, but if you press the GUI button and see the dialogue window, it’ll say both “reindexing” and “vacuuming.” It’s fast, but it says both.

If you look at the documentation, it seems like these two functions are enough:

https://www.postgresql.org/docs/9.5/sta ... dexdb.html

https://www.postgresql.org/docs/9.5/sta ... uumdb.html

Though I have a bunch of relatively small databases, I’ve found that the two functions I put into the script have kept everything running very nicely, since I have them run automatically every 24 hours.

However, if you really wanted to go in-depth and see exactly what was happening, you could increase the logging level, hit the GUI button, and watch what happens: https://twitter.com/jathavansriram/stat ... 31681?s=21


Sent from my iPhone using Tapatalk

Doing a periodic vacuum analyze (preferably during non-peak usage times) makes sense, but reindexing the database adds busy work with no practical benefit under most conditions, so probably best to skip that one unless there is a specific need for it.

Doing a periodic vacuum analyze (preferably during non-peak usage times) makes sense, but reindexing the database adds busy work with no practical benefit under most conditions, so probably best to skip that one unless there is a specific need for it.

Frank, can you talk about the internals of what it does and why it’s not necessary? Why might BMD include the function in the GUI button?


Sent from my iPhone using Tapatalk