max_column_size.rb

query = "SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'jsonb';"
result = ActiveRecord::Base.connection.execute(query)

jsonb_aggregate = result.values.to_h.map do |table, column|
  jsonb_result = ActiveRecord::Base.connection.execute("SELECT MAX(pg_column_size(#{column})) FROM #{table};").values 

  if jsonb_result.first
    puts "done #{table}, column: #{column}"
    { size: jsonb_result.first, table: table, column: column }
  end
end.compact






query = "SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'json';"
result = ActiveRecord::Base.connection.execute(query)


json_aggregate = result.values.to_h.map do |table, column|
  json_result = ActiveRecord::Base.connection.execute("SELECT MAX(pg_column_size(#{column})) FROM #{table};").values 

  if json_result.first
    puts "done #{table}, column: #{column}"
    { size: json_result.first, table: table, column: column }
  end
end.compact



query = "SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'text';"
result = ActiveRecord::Base.connection.execute(query)


text_aggregate = result.values.to_h.map do |table, column|
  next if table.start_with?("pg_")
  text_result = ActiveRecord::Base.connection.execute("SELECT MAX(pg_column_size(#{column})) FROM #{table};").values 

  if text_result.first
    puts "done #{table}, column: #{column}"
    { size: text_result.first, table: table, column: column }
  end
end.compact


jsonb_aggregate.max_by { |h| h[:size].first.to_i }[:size].first
json_aggregate.max_by { |h| h[:size].first.to_i }[:size].first
text_aggregate.max_by { |h| h[:size].first.to_i }[:size].first