Database Reference
In-Depth Information
Data caching
Generally, for most applications, only one piece of data is frequently accessed. Postgres
monitors the accessed data and places it in a cache to improve the performance of your
queries. If your application is well designed, it is possible that 99 percent of the queries are
cached.
You can find the cache rate of your database with the following SQL query:
$ heroku pg:psql --app your-app-name
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) +
sum(heap_blks_read)) as percentage, sum(heap_blks_hit) as
quantity_hit, sum(heap_blks_read) as quantity_read FROM
pg_statio_user_tables;
percentage | quantity_hit | quantity_read
-----------------------+-----------------+---------------
0.99999826000612665185 | 4024719812 | 7003
(1 row)
In this example, the application and the database are optimized with 99.99 percent of
cache. If your percentage is below that, you should consider optimizing your application or
change your database plan to a higher one that offers more RAM memory.
For example, if you are using the Premium 2 plan that offers 3.5 GB of RAM memory, a
small portion will be used by the operating system kernel, another part will be used for oth-
er programs, including Postgres, and the rest between 80 percent and 95 percent of RAM
memory is used to cache data by the operating system. Postgres manages a Shared Buffer
Cache, which is allocated and used to hold data and indexes in memory. Usually, it is alloc-
ated 25 percent of the total operating system memory.
Some operations in the database can affect the amount of cache memory temporarily. If, for
example, you are running
VACUUM
,
DDL
operations, or creating indexes, these operations
tend to consume the available cache memory.
If there is an interruption of database services, you may receive a message saying that you
have a "cold cache". This happens for a period until the database comes back online after a
service failure.
