Easy to Overlook PostgreSQL Performance Issues in Rails Apps
Performance issues in the PostgreSQL layer usually start to manifest only when your app reaches a certain scale. In this blog post, I’ll discuss easy-to-miss database config tweaks and common problems that I’ve encountered in multiple Rails projects.
random_page_cost is infamously misconfigured in the default PostgreSQL settings. This value represents how expensive it is to fetch a row using a database index. A related seq_page_cost represents the cost to fetch a row using an in-memory sequential scan. The default value of 4 for random_page_cost compared to 1 for seq_page_cost indicates that it’s significantly more expensive to fetch data using indexes (stored on disk). Unfortunately, this value comes from ancient times when databases still used HDD disks.
If your database uses an SSD disk (hint: it does), these default settings will likely harm your app’s performance. As a result your database is less likely to choose an index instead of a sequential scan. Seq scans are both CPU and memory-intensive. To find matching rows, the database engine has to well scan each row individually. It’s like doing a for loop over a large array instead of fetching a value based on its hash key.
