# Understanding Postgres Performance

DevFeed: [Understanding Postgres Performance](<https://devfeed.tech/articles/understanding-postgres-performance-41118.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2012/10/01/Understanding-Postgres-Performance/>)

Author: Map

Published: 2012-10-01T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Caching](<https://devfeed.tech/topics/caching.md>), [Database](<https://devfeed.tech/topics/database.md>), [Heroku Postgres](<https://devfeed.tech/topics/heroku-postgres.md>), [Amazon EC2](<https://devfeed.tech/topics/amazon-ec2.md>)

Tags: [cache](<https://devfeed.tech/tags/cache.md>), [database](<https://devfeed.tech/tags/database.md>), [database-performance](<https://devfeed.tech/tags/database-performance.md>), [ec2](<https://devfeed.tech/tags/ec2.md>), [heroku](<https://devfeed.tech/tags/heroku.md>), [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [index](<https://devfeed.tech/tags/index.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgres-performance](<https://devfeed.tech/tags/postgres-performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>)

## AI overview

A practical guide to assessing PostgreSQL performance for application developers. It explains how to check cache hit rates, when to increase available database cache, and how index usage and table size can indicate opportunities for improvement.

## Source excerpt

Update theres a more recent post that expands further on where to start optimizing specific queries, and of course if you want to dig into optimizing your infrastructure High Performance PostgreSQL is still a great read For many application developers their database is a black box. Data goes in, comes back out and in between there developers hope its a pretty short time span. Without becoming a DBA there's a few pieces of data that most application developers can easily grok which will help them understand if their database is performing adequately. This post will provide some quick tips that allow you to determine whether your database performance is slowing down your app, and if so what you can do about it. Understanding your Cache and its Hit Rate The typical rule for most applications is that only a fraction of its data is regularly accessed. As with many other things data can tend to follow the 80/20 rule with 20% of your data accounting for 80% of the reads and often times its higher than this. Postgres itself actually tracks access patterns of your data and will on its own keep frequently accessed data in cache. Generally you want your database to have a cache hit rate of about 99%. You can find your cache hit rate with: SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables; We can see in this dataclip that the cache rate for Heroku Postgres is 99.99%. If you find yourself with a ratio significantly lower than 99% then you likely want to consider increasing the cache available to your database, you can do this on Heroku Postgres by performing a fast database changeover or on something like EC2 by performing a dump/restore to a larger instance size. Understanding Index Usage The other primary piece for improving performance is indexes. Several frameworks will add indexes on your primary keys, though if you're searching on other fields or joini