Summarize table size and index usage for a postgres database

db_size.rb

#!/usr/bin/env ruby

require_relative '../config/environment'

class TableStats 
  include ActionView::Helpers::NumberHelper

  OUTPUT_HEADER = sprintf "%-32s %10s %10s %10s %10s\n", "Table name", "Tuples", "Size", "#Seq. Scans", "#Index Scans"
  def initialize( attrs = {} )
    @attributes = attrs
  end

  def to_s
    sprintf "%-32s %10d %10s %10d %10d\n", 
            @attributes[:name],
            @attributes[:tuples],
            number_to_human_size( @attributes[:size_in_bytes], :precision => 2 ),
            @attributes[:seq_scans],
            @attributes[:idx_scans]
  end
end

class IndexStats
  include ActionView::Helpers::NumberHelper

  OUTPUT_HEADER = sprintf "%-64s %-24s %1s %1s %10s %10s %10s %10s %10s\n", "Index name", "Table Name", "P", "U", "Tuples", "Size", "#scans", "#tup read", "#tup fetched"
  def initialize( attrs = {} )
    @attributes = attrs
  end

  def to_s
    sprintf "%-64s %-24s %1s %1s %10d %10s %10d %10d %10d\n",
            @attributes[:name],
            @attributes[:table_name],
            @attributes[:is_primary] ? "X" : " ",
            @attributes[:is_unique]  ? "X" : " ",
            @attributes[:tuples],
            number_to_human_size( @attributes[:size_in_bytes], :precision => 2 ),
            @attributes[:scanned],
            @attributes[:tuples_read],
            @attributes[:tuples_fetched]
  end
end

INDEX_QUERY=<<-SQL
        SELECT idx.relname as table,
               idx.indexrelname as index,
               pg_index.indisunique as unique,
               pg_index.indisprimary as primary,
               pg_relation_size( idx.indexrelname::text ) as bytes,
               cls.relpages as pages,
               cls.reltuples as tuples,
               idx.idx_scan as scanned,
               idx.idx_tup_read as read,
               idx.idx_tup_fetch as fetched
          FROM pg_stat_user_indexes idx
    INNER JOIN pg_class cls ON (cls.relname = idx.relname )
    INNER JOIN pg_index ON ( idx.indexrelid = pg_index.indexrelid )
      ORDER BY idx.relname,
               idx.indexrelname;
SQL

TABLE_QUERY=<<-SQL
    SELECT tbl.relname as table,
           tbl.seq_scan as seq_scan,
           tbl.idx_scan as idx_scan,
           tbl.n_live_tup as tuples,
           pg_relation_size( tbl.relname::text ) as bytes
      FROM pg_stat_user_tables tbl
  ORDER BY tbl.relname;
SQL

total_size_in_bytes = 0
table_results = ActiveRecord::Base.connection.query( TABLE_QUERY )

@table_stats = table_results.each_with_object( {} ) do |result, table_stats|
                 name, seq_scans, idx_scans, tuples, size = result
                 attributes = { :name          => name,
                                :seq_scans     => seq_scans,
                                :idx_scans     => idx_scans,
                                :tuples        => tuples,
                                :size_in_bytes => size }
                 table_stats[name] = TableStats.new( attributes )

                 total_size_in_bytes += size.to_i
               end

puts TableStats::OUTPUT_HEADER
@table_stats.each_value { |t| puts t.to_s }

index_results = ActiveRecord::Base.connection.query( INDEX_QUERY )
@index_stats = index_results.each_with_object( {} ) do |result, index_stats|
                 table_name, index_name, is_unique, is_primary, size, _, tuples, scanned, tup_read, tup_fetch = result 
                 attributes = { :table_name     => table_name,
                                :name           => index_name,
                                :is_unique      => is_unique == 't',
                                :is_primary     => is_primary == 't',
                                :size_in_bytes  => size,
                                :tuples         => tuples,
                                :scanned        => scanned,
                                :tuples_read    => tup_read,
                                :tuples_fetched => tup_fetch }
                 index_stats[index_name] = IndexStats.new( attributes )
                 total_size_in_bytes += size.to_i
               end
puts IndexStats::OUTPUT_HEADER
@index_stats.each_value { |t| puts t.to_s }

include ActionView::Helpers::NumberHelper
puts "Total size: #{number_to_human_size( total_size_in_bytes, :precision => 2 ) }"