Module: PgHero::Methods::Space
- Included in:
- Database
- Defined in:
- lib/pghero/methods/space.rb
Instance Method Summary collapse
- #capture_space_stats ⇒ Object
- #clean_space_stats(before: nil) ⇒ Object
- #database_size ⇒ Object
- #relation_sizes ⇒ Object
- #relation_space_stats(relation, schema: "public") ⇒ Object
- #space_growth(days: 7, relation_sizes: nil) ⇒ Object
- #space_stats_enabled? ⇒ Boolean
- #table_sizes ⇒ Object
Instance Method Details
#capture_space_stats ⇒ Object
122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
# File 'lib/pghero/methods/space.rb', line 122 def capture_space_stats now = Time.now values = relation_sizes.map do |rs| { database: id, schema: rs[:schema], relation: rs[:relation], size: rs[:size_bytes].to_i, captured_at: now } end PgHero::SpaceStats.insert_all!(values) if values.any? end |
#clean_space_stats(before: nil) ⇒ Object
137 138 139 140 |
# File 'lib/pghero/methods/space.rb', line 137 def clean_space_stats(before: nil) before ||= 90.days.ago PgHero::SpaceStats.where(database: id).where("captured_at < ?", before).delete_all end |
#database_size ⇒ Object
4 5 6 |
# File 'lib/pghero/methods/space.rb', line 4 def database_size PgHero.pretty_size select_one("SELECT pg_database_size(current_database())") end |
#relation_sizes ⇒ Object
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# File 'lib/pghero/methods/space.rb', line 8 def relation_sizes select_all_size <<~SQL SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'm' then 'matview' ELSE 'index' END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND c.relkind IN ('r', 'm', 'i') ORDER BY pg_table_size(c.oid) DESC, 2 ASC SQL end |
#relation_space_stats(relation, schema: "public") ⇒ Object
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
# File 'lib/pghero/methods/space.rb', line 92 def relation_space_stats(relation, schema: "public") if space_stats_enabled? relation_sizes ||= self.relation_sizes sizes = relation_sizes.map { |r| [[r[:schema], r[:relation]], r[:size_bytes]] }.to_h start_at = 30.days.ago stats = select_all_stats <<~SQL SELECT captured_at, size AS size_bytes FROM pghero_space_stats WHERE database = #{quote(id)} AND captured_at >= #{quote(start_at)} AND schema = #{quote(schema)} AND relation = #{quote(relation)} ORDER BY 1 ASC SQL stats << { captured_at: Time.now, size_bytes: sizes[[schema, relation]].to_i } else raise NotEnabled, "Space stats not enabled" end end |
#space_growth(days: 7, relation_sizes: nil) ⇒ Object
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
# File 'lib/pghero/methods/space.rb', line 49 def space_growth(days: 7, relation_sizes: nil) if space_stats_enabled? relation_sizes ||= self.relation_sizes sizes = relation_sizes.to_h { |r| [[r[:schema], r[:relation]], r[:size_bytes]] } start_at = days.days.ago stats = select_all_stats <<~SQL WITH t AS ( SELECT schema, relation, array_agg(size ORDER BY captured_at) AS sizes FROM pghero_space_stats WHERE database = #{quote(id)} AND captured_at >= #{quote(start_at)} GROUP BY 1, 2 ) SELECT schema, relation, sizes[1] AS size_bytes FROM t ORDER BY 1, 2 SQL stats.each do |r| relation = [r[:schema], r[:relation]] if sizes[relation] r[:growth_bytes] = sizes[relation] - r[:size_bytes] end r.delete(:size_bytes) end stats else raise NotEnabled, "Space stats not enabled" end end |
#space_stats_enabled? ⇒ Boolean
142 143 144 |
# File 'lib/pghero/methods/space.rb', line 142 def space_stats_enabled? table_exists?("pghero_space_stats") end |
#table_sizes ⇒ Object
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
# File 'lib/pghero/methods/space.rb', line 29 def table_sizes select_all_size <<~SQL SELECT n.nspname AS schema, c.relname AS table, pg_total_relation_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND c.relkind = 'r' ORDER BY pg_total_relation_size(c.oid) DESC, 2 ASC SQL end |