We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 to 10 seconds in query execution. May i know why it happens?. Postgresql config setting almost same except archive_mode which is on external postgresql, while in embedded postgresql it is off.
closed as too broad by mustaccio, Marco, dezso, John Eisbrener, RDFozz Aug 28 '17 at 15:53
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
For me it looks like an issue with the VM and/or network and not Postgres, if it is true that nothing else has changed, but only the "dedication" to another machine.
We had a bad VM configuration - my VM was on the same rack as our Scientists run their stuff. So each time they did anything - all resources went there.
Look at your VM, how much RAM and Processing power does it really have? If you can afford it - give your your postgres more RAM and config it to cache most of the DB (or all). And if you can afford it - buy a dedicated server for Postgres. You'll have one for below $1k (with couple of SSDs and 32GB RAM).
For the rest: listen to a_horse_with_no_name, and give us additional info (and hell, if you use pg91 - upgrade to pg96, if you use anything below pg96 - upgrade!)
-- should be a comment, but my reputation is not enough for it.