How can you optimize Heroku Postgres database schema design?

Using the most appropriate data types for your columns can reduce the amount of space your data consumes and increase performance. For example, if you're storing small numbers, use INT2 or INT4 instead of INT8.

The article explicitly called out about "Heroku Postgres" which is nothing but the fully managed postgresql service by Heroku. So ideally the responses in this article should be only focused and tuned to if there is anything different someone would do to optimize postgresql on Heroku Vs self manage postgresql. Choosing the right data type is important for any database design. "storage capacity on disk" may not be concerning but what is important is when accessed, these data will also churn through memory, buffer, cache, etc.. so still affects indirect compute cost/performance. With any managed database service, you should double-check if there are any restrictions in places compared to open source or self managed versions of the same.

Normalization involves organizing your database to reduce redundancy and improve data integrity. This can help in maintaining consistency throughout your database and can make updates faster and more efficient.

The article explicitly called out about "Heroku Postgres" which is nothing but the fully managed postgresql service by Heroku. So ideally the responses in this article should be only focused and tuned to if there is anything different someone would do to optimize postgresql on Heroku Vs self manage postgresql. Heroku, as a managed database service should not affect the table normalization process. But as we normalize the design number of tables increases and relationships among them become complex. Hence when retrieving data we will need to join one or more together. So it will be good to check if there is any limit on the number of tables that can be joined / used in a single query.

Indexes can significantly speed up data retrieval operations. Create indexes on columns that you frequently query against, but be mindful that each index can slow down write operations (INSERT, UPDATE, DELETE) because the index needs to be updated as well.

The article explicitly called out about "Heroku Postgres" which is nothing but the fully managed postgresql service by Heroku. So ideally the responses in this article should be only focused and tuned to if there is anything different someone would do to optimize postgresql on Heroku Vs self manage postgresql. With managed database service, Refer to the Heroku documentation how they internally maintain the indexes, and keep it updated. Check if the index maintenance is fully online even for tables with TB size. Check if there is any custom limit on type or max size of the index enforced by Heroku. See if there are custom optimisations or other types of indexes provided for specific workloads if so and fits your case then leverage it.

Table partitioning can help manage large tables by splitting them into smaller, more manageable pieces. This can improve query performance and make maintenance tasks easier.

Regular testing and monitoring can help you identify performance bottlenecks and areas for improvement. Use Heroku's performance monitoring tools to track your database's performance and make adjustments as needed.

Use connection pooling: It can help in managing database connections more efficiently, reducing the overhead of establishing and tearing down database connections. Consider read replicas: If your application has a lot of read operations, using read replicas can help distribute the load and improve performance. Stay up-to-date with Postgres versions: Newer versions of PostgreSQL often include performance improvements, new features, and security patches. Heroku makes it easy to upgrade to newer versions, so take advantage of this. Regularly clean up unused data: Periodically removing old or unused data can help keep your database size manageable, which can contribute to better performance.