Local vs. Heroku Postgres Speed

Question

I have a local database with one table that has approximately 5.5 million records. I created a new database (basic plan) on Heroku Postgres and restored a local dump to it. Having dismissed psql and doing some queries, I noticed that the speed is much lower than locally. Then I provided another database with a crane plan, and the numbers are bad too.

Here are a few 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 due to differences in equipment? Is there an easy way to increase throughput?

Answer 1

So you ask, is it possible for the online database service to be slower than the local database? Yes, of course, it will be slower, it has all network latency to deal with
Is there an easy way to speed it up? Besides the obvious ones, such as faster network links and mobile offices, which are located next to Heroku data center - No

Answer 2

120-130 ms for selecting one row most likely indicates a network delay, as Lukos suggests, and you can reduce this by completing your queries closer to the database server. However, 2-3 seconds of latency will most likely have a database speed - in particular, I / O bandwidth. This is why Heroku emphasizes that the difference in database offerings is related to cache size.

Heroku Postgres stores your data on Amazon EBS . (This is revealed in the incident report and, incidentally, will also explain the 1 TB limit.) EBS performance is a bit like a roller coaster; much more than local drives. Some readings may be fast, while others may be slow. Sometimes the whole volume drops to 100 KB / s, sometimes it gives off an interconnect.

In my experience of hosting databases in EC2, I found that running RAID 10 on EBS smooths out differences in performance. I do not think Heroku is doing this, as it will significantly increase costs that exceed database plans. AWS recently announced IOPS for EBS , which will allow you to pay for the allocated capacity, further increasing predictability, but also increasing costs.

Answer 3

I created a new database (basic plan) on Heroku Postgres and restored a local dump in it.

It looks like you played simultaneously with another database.

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

I highly suspect that your Heroku application and Heroku DB will not be hosted in the same region. Both must be either in the US or in the EU.