Skip Navigation
Show nav
Dev Center
  • Get Started
  • Documentation
  • Changelog
  • Search
  • Get Started
    • Node.js
    • Ruby on Rails
    • Ruby
    • Python
    • Java
    • PHP
    • Go
    • Scala
    • Clojure
    • .NET
  • Documentation
  • Changelog
  • More
    Additional Resources
    • Home
    • Elements
    • Products
    • Pricing
    • Careers
    • Help
    • Status
    • Events
    • Podcasts
    • Compliance Center
    Heroku Blog

    Heroku Blog

    Find out what's new with Heroku on our blog.

    Visit Blog
  • Log inorSign up
Hide categories

Categories

  • Heroku Architecture
    • Compute (Dynos)
      • Dyno Management
      • Dyno Concepts
      • Dyno Behavior
      • Dyno Reference
      • Dyno Troubleshooting
    • Stacks (operating system images)
    • Networking & DNS
    • Platform Policies
    • Platform Principles
  • Developer Tools
    • Command Line
    • Heroku VS Code Extension
  • Deployment
    • Deploying with Git
    • Deploying with Docker
    • Deployment Integrations
  • Continuous Delivery & Integration (Heroku Flow)
    • Continuous Integration
  • Language Support
    • Node.js
      • Troubleshooting Node.js Apps
      • Working with Node.js
      • Node.js Behavior in Heroku
    • Ruby
      • Rails Support
      • Working with Bundler
      • Working with Ruby
      • Ruby Behavior in Heroku
      • Troubleshooting Ruby Apps
    • Python
      • Working with Python
      • Background Jobs in Python
      • Python Behavior in Heroku
      • Working with Django
    • Java
      • Java Behavior in Heroku
      • Working with Java
      • Working with Maven
      • Working with Spring Boot
      • Troubleshooting Java Apps
    • PHP
      • Working with PHP
      • PHP Behavior in Heroku
    • Go
      • Go Dependency Management
    • Scala
    • Clojure
    • .NET
      • Working with .NET
  • Databases & Data Management
    • Heroku Postgres
      • Postgres Basics
      • Postgres Getting Started
      • Postgres Performance
      • Postgres Data Transfer & Preservation
      • Postgres Availability
      • Postgres Special Topics
      • Migrating to Heroku Postgres
    • Heroku Key-Value Store
    • Apache Kafka on Heroku
    • Other Data Stores
  • AI
    • Working with AI
    • Heroku Inference
      • Inference API
      • Quick Start Guides
      • Inference Essentials
      • AI Models
    • Vector Database
    • Model Context Protocol
  • Monitoring & Metrics
    • Logging
  • App Performance
  • Add-ons
    • All Add-ons
  • Collaboration
  • Security
    • App Security
    • Identities & Authentication
      • Single Sign-on (SSO)
    • Private Spaces
      • Infrastructure Networking
    • Compliance
  • Heroku Enterprise
    • Enterprise Accounts
    • Enterprise Teams
    • Heroku Connect (Salesforce sync)
      • Heroku Connect Administration
      • Heroku Connect Reference
      • Heroku Connect Troubleshooting
  • Patterns & Best Practices
  • Extending Heroku
    • Platform API
    • App Webhooks
    • Heroku Labs
    • Building Add-ons
      • Add-on Development Tasks
      • Add-on APIs
      • Add-on Guidelines & Requirements
    • Building CLI Plugins
    • Developing Buildpacks
    • Dev Center
  • Accounts & Billing
  • Troubleshooting & Support
  • Integrating with Salesforce
  • Databases & Data Management
  • Heroku Postgres
  • Postgres Performance
  • Heroku Postgres Database Tuning

Heroku Postgres Database Tuning

English — 日本語に切り替える

Last updated December 03, 2024

Table of Contents

  • Choose the Right Heroku Postgres Plan
  • Track Metrics
  • Beware of Connections
  • Identify and Fix Expensive Queries
  • Watch Out for Locks
  • Manage Database Bloat
  • Consider Separate Caching
  • Run Diagnostics

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.

Essential-tier databases have variable performance characteristics by design. If your application requires predictable performance and availability, upgrade your database plan.

Track Metrics

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 metrics are 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.

The PgBouncer buildpack is a classic buildpack. There currently is no Cloud Native Buildpack version.

Identify and Fix Expensive Queries

The biggest cause of database performance issues comes from under-optimized queries that apply significant pressure on the database system. To identify these queries, look at the Expensive Queries view for your database. Furthermore, we’ve configured Postgres to log queries taking longer than 2 seconds, so keep an eye on your application logs as well. After expensive queries are identified, they can often be optimized by adding an appropriate index, although sometimes the database schema must 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 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'. This affects all connections, including connections from analytics tools such as dataclips.

Watch Out for Locks

Concurrent writes to the same rows 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 seems to take a long time, but it’s just 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’s likely 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 Separate Caching

Postgres is good at caching your data, but some applications benefit from caching in a separate in-memory system such as Heroku Key-Value Store. Consider this approach if certain queries are run frequently and exhibit variable response time, which is indicative of caching effects.

Run Diagnostics

The heroku pg:diagnose command flags issues that Heroku Postgres users commonly run into. Running this command is a good way to determine if you’re running into any common operational issues with your Heroku Postgres database.

Keep reading

  • Postgres Performance

Feedback

Log in to submit feedback.

Understanding Heroku Postgres Data Caching Heroku Postgres Metrics Logs

Information & Support

  • Getting Started
  • Documentation
  • Changelog
  • Compliance Center
  • Training & Education
  • Blog
  • Support Channels
  • Status

Language Reference

  • Node.js
  • Ruby
  • Java
  • PHP
  • Python
  • Go
  • Scala
  • Clojure
  • .NET

Other Resources

  • Careers
  • Elements
  • Products
  • Pricing
  • RSS
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku Blog
    • Heroku News Blog
    • Heroku Engineering Blog
  • Twitter
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku
    • Heroku Status
  • Github
  • LinkedIn
  • © 2025 Salesforce, Inc. All rights reserved. Various trademarks held by their respective owners. Salesforce Tower, 415 Mission Street, 3rd Floor, San Francisco, CA 94105, United States
  • heroku.com
  • Legal
  • Terms of Service
  • Privacy Information
  • Responsible Disclosure
  • Trust
  • Contact
  • Cookie Preferences
  • Your Privacy Choices