Heroku Postgres Database Tuning
Last updated 04 October 2016
Table of Contents
The Heroku Data team continuously ensures that Heroku Postgres databases are configured optimally for web application workloads. You can further improve the performance of your Heroku Postgres database by understanding your specific workload and by doing performance tuning.
Choose the right Heroku Postgres plan
Choosing the right plan depends on the unique usage characteristics of your app as well as your organization’s availability and uptime expectations. For more information, see Choosing the Right Heroku Postgres Plan.
Hobby Tier databases are not suitable for production use because, by design, they have variable performance characteristics. If your application requires predictable performance and availability, upgrade your database plan.
The first step in understanding the performance of your database is to track various vital metrics, which offer an overall view of your system’s performance. We offer a number of system metrics which are output on your application logs. These should be tracked and alerted on appropriately. One easy way to do that is by installing the Librato addon to track this data over time.
Beware of connections
With metrics in place, keep a close eye on connection count. One common issue arises due to leaking database connections, which in turn degrades the performance of your database. Some frameworks have ways to control connections (for example, ActiveRecord), but if your connection count is steadily approaching your plan limit, also consider using pgbouncer.
Identify and fix expensive queries
The biggest cause of database performance issues comes from under-optimized queries which apply significant pressure on the database system. To identify these queries, look at the Expensive Queries view for your database. Furthermore, we have configured Postgres to log queries taking longer than 2 seconds, so keep an eye on your application logs as well. Once expensive queries are identified, they can often be optimized by adding an appropriate index, although sometimes the database schema might have to be adjusted to achieve optimal queries. Always use EXPLAIN to understand how individual queries are executed and which indexes to add or remove.
Long running queries can also have an impact on database performance because they may hold locks or otherwise overconsume server resources. You can automatically abort long running queries at the session level by running
SET statement_timeout = '20s' on each connection, or for all statements database wide by running
ALTER DATABASE your-database-name SET statement_timeout='20s'. Note that this will affect all connections, including those from analytics tools such as dataclips.
Watch out for locks
Concurrent writes to the same rows will cause Postgres to lock one transaction while the other completes. A large number of locks gives the impression that the database response times degrade, but in fact the locked transactions aren’t doing work at all while they wait for the appropriate lock to be granted. In some cases, a database migration with ALTER TABLE statements will seem to take a very long time, but it might just be waiting for its turn to run.
To identify locks, run the
heroku pg:locks command available from the pg-extras plugin. Additionally, the number of locks is logged on your log stream, and it’s a good idea to keep an eye on it with the Librato addon.
Manage database bloat
Postgres uses MVCC to enable concurrent access to data. This can cause dead tuples or bloat, which requires the database system to be VACUUMed in order to perform optimally. Postgres includes an autovacuum daemon, but it might be the case that more aggressive vacuum settings must be configured. Alternatively, the command
VACUUM ANALYZE can be run frequently during times of least traffic to clean up bloat manually.
Consider introducing separate caching
Postgres is very good at caching your data, but some applications benefit from caching in a separate in-memory system such as Heroku Redis. Consider this approach if certain queries are run very frequently and exhibit variable response time, which is indicative of caching effects.
heroku pg:diagnose command flags issues that Heroku Postgres users commonly run into. Running this command is a good way to determine if you are running into any common operational issues with your Heroku Postgres database.