Developing a web application (Django, python 3, PostgreSQL). Due to certain requirements for the application, it is necessary for the database to be on another server.

The difference in query execution time (the same page):

  • 34.67 ms (29 requests) - DB and Django on the same server
  • 2093.07 ms (30 requests) - DB and Django on different servers

Django-debug-toolbar SQL data

Question: Is there a way to increase the speed of data transfer?

Obviously, the main problem is the remote connection. PostgreSQL 9.3, Ubuntu 14.04 (on both servers). PostgreSQL settings are currently standard, remote connection is allowed by login password.

  • Dial-up 300 baud connection? Something hurt fast. - Sergey
  • Ping between servers what? Are the servers on the same network? - FeroxTL
  • Servers in different networks. Django - in Kiev, DB in Amsterdam. Ping result: 64 bytes from <...>: icmp_seq = 1 ttl = 56 time = 38.5 ms - artbataev
  • You may be saved by database replication. Or maybe not. Django works as before with the local database. But the local database is periodically synchronized with the remote one. - Sergey
  • IMHO, with such a ping and such a scheme will save you only caching. You can certainly try to raise the VPN, but most likely the channel is narrow between the servers. - Shamanis

1 answer 1

Try forwarding the port from one server to another. From the documentation :

ssh -L 63333:localhost:5432 joe@foo.com where joe is the username on the remote server foo.com and then check on the application host:

psql -h localhost -p 63333 postgres

If you cannot forward the port, try to at least disable logging of host names from which you connect to the database.

log_hostname = off in postgresql.conf .

  • What are the reasons to believe that ssh will increase the speed of postgres? - Sergey
  • I think the problem is setting up the connection. Well, and besides, I have already encountered a similar problem, at the time of development it was necessary to go to the remote server pg. - Dmitry V.
  • All this is strange. Unless, of course, a shaper that deliberately slows down all traffic except for the “standard” is not configured somewhere in the way. And to heighten the acceleration, you can also try to enable ssh compression. - Sergey
  • Unfortunately, not really helped. Approximately the same indicators, can only slightly faster. - artbataev
  • Although no, it helped - time decreased by 1.5 times. Instead of 2 seconds 1.3 now. But still long enough. - artbataev 4:44