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
permalink #capture_space_stats ⇒ Object
[View source]
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 |
permalink #clean_space_stats(before: nil) ⇒ Object
[View source]
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 |
permalink #database_size ⇒ Object
[View source]
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 |
permalink #relation_sizes ⇒ Object
[View source]
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 |
permalink #relation_space_stats(relation, schema: "public") ⇒ Object
[View source]
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 |
permalink #space_growth(days: 7, relation_sizes: nil) ⇒ Object
[View source]
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 |
permalink #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 |
permalink #table_sizes ⇒ Object
[View source]
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 |