Question 393700 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to find the size of a table in bytes in Ruby on Rails?

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"
Tags:

2

Answers


  1. 0

    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.

    1. When an Essential database reaches 90% of a usage limit, the owner receives a warning email.
    2. When the database exceeds a plan limit, the owner receives an additional notification. At this point, the database receives a 7-day grace period to comply with the limit or migrate to another plan. If your database is over 200% of the plan limit at any time, we skip the grace period and immediately revoke access.

    You can ask Heroku to set additional alerts.

    Set alerts based on your plans limit to give you some advanced notice if you start approaching it. We recommend setting a warning alert when your database gets to 80% of the allotted size for your plan and a critical alert when it gets to 90% of the allotted size.

    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, and pg: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 and pg: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.

    Login or Signup to reply.
  2. 0

    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 is table_name method

    There 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

    DB_SIZE_CACHE_EXPIRY = 1.hour

def self.total_db_size
  query = <<~SQL.squish
    SELECT pg_size_pretty(
      pg_database_size('#{connection_db_config.database}')
    ) AS size
  SQL

  Rails.cache.fetch('total_db_size', expires_in: DB_SIZE_CACHE_EXPIRY) do
    connection.select_value(query)
  end
end

def self.total_relation_size
  return if table_name.nil?

  query = <<~SQL.squish
    SELECT pg_size_pretty(
      pg_total_relation_size('#{table_name}')
    ) AS size
  SQL

  Rails.cache.fetch("table_#{table_name}_size", expires_in: DB_SIZE_CACHE_EXPIRY) do
    connection.select_value(query)
  end
end
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search