Small Change, Big Win

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
with us.

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.