Search code examples
databasepostgresqlyiiheroku-postgresphp-5.6

Remote Postgres Database Heroku Connection is slow from Digital Ocean Instance

I am using Apache2 and php 5.6.,12. I decided to host my database remotely at Heroku(Using postgresql 9.4) and keep my server at Digital ocean.

In my yii 1 framework, the connection string that I have added is the following:

'db'=>array(
                'connectionString' => 
               'pgsql:host=ec2-XX-XX-XX-XX.compute-1.amazonaws.com;port=6372;dbname=dddqXXXXX;sslmode=require',
                'emulatePrepare' => true,
                'username' => 'XXXX4dcXXXX', 
                'password' => 'XXXXXXXXXc34XXXXXXX123',
                 'charset' => 'utf8',
),

The connection is successful but remote access is making it slow for even simple query in my server at digital ocean. I read from Heroku that for remote access, ssl mode has to be enabled. So I did and and I am still unable to figured out why the database connection is slow. It can be slow up to even 5 seconds. I tried with a locally installed postgresql database server and everything is running as expected. I am not sure how can I solve this else I will have to move away from Herokku and do it in the traditional way which is going to be very depressing. I hope that someone can help me.

Here is my php info og pgsql:

enter image description here

Is there some settings that need to be done to speed up remote heroku database access in apache2 or php?

Solution

  • I was unable to ping Postgres Heroku Server as advised by Richard (Heroku has prevented pinging) . It was very obvious that connection between digital ocean server and Heroku Postgres server is slow. Thus I emailed Heroku directly to ask for their advice.

    Heroku's Solution:

    They claimed that applications which are connecting from long distance outside the Heroku platform will have initial connection latency and this latency is a big problem.

    Thus, Application has to establish a TCP connection which Postgres protocol will upgrade that to an SSL connection. This takes quite a few packets and introduces a lot of latency, particularly if the app is creating a new connection for each query or page load.

    Heroku recommended me to configure the app to use something like heroku-pgbouncer connection pool. That uses pgbouncer and stunnel to provide a configurable connection pool for the app endpoints.

    The recommendation sound too expensive and highly challenging for me to deal with.

    My Solution :: Use Database Labs

    I found out another postgres as a service provider called Database Labs . They allow users to select data center region for better performance.Database Labs has easy backend managing platform and friendly support team. The backend had minimum backend functionality and I do understand as they started in year 2014.

    However, after migrating to their service, the performance of my web page improved remarkably. The connection was like any standard connection without the need for SSL. I am inputing my solution for the benefit of others who could face similar problem like me.

    Heroku is definitely a good provider if we host our application in Heroku and use their database service. However If you are a Digital Ocean user, I recommend that you use Use Database Labs . This saves a lot of time