Local vs Heroku Postgres speed

Question

I have a local database with a single table which has roughly 5.5 million records. I created a new database (Basic Plan) on Heroku Postgres and restored the local dump into it. Firing up psql and doing some queries I noticed that the speed is significantly lower than locally. I then provisioned another database with the Crane plan and the numbers are similarly bad.

Here some numbers:

---

select count(*) from table;
Local: 1216.744 ms
Heroku (Basic): 4697.073 ms
Heroku (Crane): 2972.302 ms

---

select column from table where id = 123456;
Local: 0.249 ms
Heroku (Basic): 127.557 ms
Heroku (Crane): 137.617 ms

---

How are these huge differences possible? Could this be entirely related to hardware differences? Is there any easy way to increase the throughput?

Answer 1

The 120-130ms for the single-row select is likely indicative of network latency, as Lukos suggests, and you can reduce this by running your queries closer to the database server. However, the 2-3 seconds of latency is more likely to do with database speed -- specifically, I/O throughput. This is why Heroku emphasizes that difference in their database offerings has to do with cache size.

Heroku Postgres stores your data on Amazon EBS. (This is revealed in an incident report, and would incidentally explain the 1 TB limit too.) EBS performance is a bit like a rollercoaster; much more so than local disks. Some reads can be fast, and others can be slow. Sometimes the whole volume drops to 100 KB/s, sometimes it maxes out the interconnect.

In my experience hosting databases in EC2, I found that running RAID 10 over EBS smoothed out performance differences. I don't believe Heroku does this, as it would greatly increase costs above the database plan price points. AWS recently announced provisioned IOPS for EBS that would allow you to pay for dedicated capacity, further increasing predictability but also increasing costs.

Answer 2

So you are asking if it is possible for an online database service to be slower than a local database? Yes, of course it will be slower, it has all the network latency to deal with
Is there an easy way to speed it up? Apart from the obvious like faster network links and moving offices to be next door to Heroku's data centre - No