Versions for My Environment:
- Ruby 3.2.2
- Rails 7.1.2
- PSQL 15 – (the gem is pg 1.1)
Preface:
I did not find a direct answer after some searching on the Internet, so I am posting this to show what I came up with. Essentially, I wanted to display an estimated size of each table in my database on an admin dashboard so I could have an idea of when I would need to upgrade my Heroku PSQL Add-on (you pay based on rows and size).
Question:
This is a personal project, but I am curious if this is aligned with industry best-practice. Would a commercial application just use some other means outside the app to know the size of their database, the average rate of change, and when they are expecting to scale up? Or is it typical to track this from within the application?
My solution — I put the following in the base ApplicationRecord class from which all models inherit. Note, you can call total_db_size
from ApplicationRecord and any of its descendant models and they will all return the same result, but you will get an error if you try to call total_relation_size
from ApplicationRecord. See example usage.
class ApplicationRecord < ActiveRecord::Base
primary_abstract_class
def self.total_db_size
query = <<-SQL.squish
SELECT pg_size_pretty(
pg_database_size(#{"'#{ActiveRecord::Base.connection_db_config.database}'"})
) AS size
SQL
connection.execute(query).first["size"]
end
def self.total_relation_size
query = <<-SQL.squish
SELECT pg_size_pretty(
pg_total_relation_size(#{"'#{name.underscore.pluralize}'"})
) AS size
SQL
find_by_sql(query).first.size
end
end
Example Usage:
Database Size
irb(main):001> ApplicationRecord.total_db_size
(1.4ms) SELECT pg_size_pretty( pg_database_size('famifin_development') ) AS size
=> "8269 kB"
Table Size – must be called from a model
irb(main):002> ApplicationRecord.total_relation_size
ApplicationRecord Load (1.4ms) SELECT pg_size_pretty( pg_total_relation_size('application_records') ) AS size
...PG::UndefinedTable: ERROR: relation "application_records" does not exist (ActiveRecord::StatementInvalid)...
irb(main):003> User.total_relation_size
User Load (0.7ms) SELECT pg_size_pretty( pg_total_relation_size('users') ) AS size
=> "48 kB"
2
Answers
There’s little benefit to putting it in the application which is using the database, it just complicates the application. Usually this information would be monitored by a tool outside the application.
Heroku already provides metrics for PostgreSQL and will alert you when the database is getting too large and gives you a 7 day grace period.
You can ask Heroku to set additional alerts.
Your Heroku application logs already contain database metrics, including the database size.
For more fine-grained information, start with the heroku-pg-extras extension to the Heroku CLI. From there you can get information about how your database is growing, and suggestions about where you could trim some bloat.
pg:index-usage
,pg:unused-indexes
, andpg:index-size
can help find large, unused indexes which could be dropped to save space and time.pg:table-size
can monitor your table growth.pg:bloat
andpg:vacuum-stats
can tell you if your auto-vacuum needs tuning.And there are other techniques for controlling database growth: periodically dropping old data (made efficient using table partitions); moving data not needed for runtime into a data warehouse, for example business intelligence and other statistical data.
See Heroku Postgres Monitoring Best Practices and Heroku Monitoring and Metrics for more.
Some tips for your current solution to monitor that metrics inside the application using model layer. Let’s assume you have single database
Table name can be different than
name.underscore.pluralize
. There istable_name
methodThere are useful built-in methods for execute query, for example
select_value
Probably you can store query results in a cache store (like Redis) and refresh them periodically
To create indented content along with an indented closing tag, you can use a squiggly heredoc (
~
vs-
)You can modify your methods such way