I have setup PostgreSQL on a VPS I own - the software that accesses the database is a program called PokerTracker.
PokerTracker logs all your hands and statistics whilst playing online poker.
I wanted this accessible from several different computers so decided to installed it on my VPS and after a few hiccups I managed to get it connecting without errors.
However, the performance is dreadful. I have done tons of research on 'remote postgresql slow' etc and am yet to find an answer so am hoping someone is able to help.
Things to note:
The query I am trying to execute is very small. Whilst connecting locally on the VPS, the query runs instantly.
While running it remotely, it takes about 1 minute and 30 seconds to run the query.
The VPS is running 100MBPS and then computer I'm connecting to it from is on an 8MB line.
The network communication between the two is almost instant, I am able to remotely connect fine with no lag whatsoever and am hosting several websites running MSSQL and all the queries run instantly, whether connected remotely or locally so it seems specific to PostgreSQL.
I'm running their newest version of the software and the newest compatible version of PostgreSQL with their software.
The database is a new database, containing hardly any data and I've ran vacuum/analyze etc all to no avail, I see no improvements.
I don't understand how MSSQL can query almost instantly yet PostgreSQL struggles so much.
I am able to telnet to the port 5432 on the VPS IP with no problems, and as I say the query does execute it just takes an extremely long time.
What I do notice is on the router when the query is running that hardly any bandwidth is being used - but then again I wouldn't expect it to for a simple query but am not sure if this is the issue. I've tried connecting remotely on 3 different networks now (including different routers) but the problem remain