Small Change, Big Win
Posted almost 4 years ago by Harold
At the Heroku Department of Data, we are always investigating ways to improve the reliability, security and performance of your database servers. We do this by monitoring the entire ecosystem around it; we monitor the reliability of the platform itself, as well as keeping a close eye on the hardware where your data is hosted on upstream servers. But this also includes listening to the community. We do that by staying involved with our users at developer meetups and hackfests, listening closely to support requests to find and resolve common patterns of pain, as well as any relevant mailing lists.
Whenever we spot a problem, we make it a priority to resolve it.
The last such occasion has a story behind it. Just a couple of days ago, we found a user on the PostgreSQL performance list who discovered an improvement to our database Global Unified Configuration Settings (GUCs) that, when modified, increased performance of their query significantly.
The GUC involved was
random_page_cost. This configuration parameter serves as
a hint that the query planner uses to determine how expensive it is to perform
random seeks from the underlying block device relative to the cost of a
sequential scan. It turns out that lowering this number on our platform helps
the planner make better decisions when it comes to choosing among using an
index, which has its own set of costs, or simply fetching data from disk
directly. The day we discovered this improvement, we deployed a change to our
database configuration settings that will apply to any new database provisioned
Because we prioritize stability of running systems, we chose not to deploy this change to all provisioned databases. However, if you'd like to apply this change yourself, you can do so by running:
ALTER DATABASE <your-db-name> SET random_page_cost = 2.0;
A great story of how one person's pain turned into a resolution for our entire customer base.