Module: PgHero::Methods::Space

Included in:
Database
Defined in:
lib/pghero/methods/space.rb

Instance Method Summary collapse

Instance Method Details

#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

#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

#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

#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

#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

#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

#space_stats_enabled? ⇒ Boolean

Returns:

  • (Boolean)
[View source]

142
143
144
 
# File 'lib/pghero/methods/space.rb', line 142

def space_stats_enabled?
  table_exists?("pghero_space_stats")
end

#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