Slow Postgres performance?

What is the easiest approach to troubleshooting slow ActiveRecord queries within a fly.io app running Ruby on Rails?

In a postgres db with less that 100 records I am seeing 4-5s response times in the past few days.

2023-04-19T01:37:54.545 app[REDACTED] otp [info] I, [2023-04-19T01:37:54.545168 #235] INFO -- : [c80d0061-3480-4b44-bb13-c4c1536c1ffc] Completed 200 OK in 4611ms (Views: 66.0ms | ActiveRecord: 4391.4ms | Allocations: 45783)

Health checks: flyio/postgres:14.6 started 3/3 passing

Are your app servers running in the same region as your postgres database servers?

Yes - they are both in DFW.

It might be worth spinning up a simple fly app running the pghero image. GitHub - ankane/pghero: A performance dashboard for Postgres - this should be pretty simple to get up and running on fly as a separate app.

Also, are you sure your PG instance has enough RAM and its not maxing out on resources?

I will check out pghero.

The current amount of ram for the PG instance is 256mb. However none of the metrics (to my knowledge) indicate that it is a memory issue, no spikes or weird behavior is apparent.

Just curious, is this consistently slow? Or is it only certain requests? And if so, is it always slow for the same requests?

It is consistently slow. Even when the view related query is retrieving 10 total records from 2 tables. (1515ms)

I think I’ve resolved the issue…

I deleted a 3gb volume associated with the app that was not mounted.
(I was going to use it for file storage before going with Google)

Previous 4-5s ActiveRecord queries are now down to single digit milliseconds.
Even the view which was previously under 90ms is now at single digits as well.

Glad you resolved this on your own!

For future reference on what happened, there is a known edge-case in the platform where having an unmounted volume causes instances to be placed in a random region (because the unmounted volume no longer ‘anchors’ the instance to its host/region), so your app instance was actually no longer being deployed in DFW, causing the slow queries to your database. Hope this helps your understanding!