lib/extensions/ar_adapter/ar_dba.rb
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
def database_size(name)
select_value("SELECT pg_database_size(#{quote(name)})").to_i
end
def database_version_details
select_value("SELECT version()")
end
def spid
select_value("SELECT pg_backend_pid()").to_i
end
def xlog_location
select_value("SELECT pg_current_wal_insert_lsn()::varchar")
end
def xlog_location_diff(lsn1, lsn2)
select_value("SELECT pg_wal_lsn_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i
end
def client_connections
select(<<-SQL, "Client Connections").to_a
SELECT client_addr AS client_address
, datname AS database
, pid AS spid
, wait_event_type
, wait_event
, query
FROM pg_stat_activity
ORDER BY 1, 2
SQL
end
# Taken from: https://github.com/bucardo/check_postgres/blob/2.19.0/check_postgres.pl#L3492
# and referenced here: http://wiki.postgresql.org/wiki/Show_database_bloat
# check_postgres is Copyright (C) 2007-2012, Greg Sabino Mullane
#
# Changes applied:
# Removed schemaname and totalwastedbytes columns from the original to fit our requirements.
# Reformatted the SQL and renamed some columns to make them more easier to id.
# Removed some CASE... logic statements as not needed for our requirements.
def table_bloat
data = select(<<-SQL, "Table Bloat")
SELECT tablename AS table_name
, reltuples::bigint AS rows
, relpages::bigint AS pages
, otta
, ROUND(CASE WHEN otta = 0 OR sml.relpages = 0 OR sml.relpages = otta THEN 0.0
ELSE sml.relpages / otta::numeric END, 1) AS percent_bloat
, CASE WHEN relpages < otta THEN 0
ELSE relpages::bigint - otta END AS wasted_pages
, CASE WHEN relpages < otta THEN 0
ELSE (blocksize * (relpages - otta))::bigint END AS wasted_size
, CASE WHEN relpages < otta THEN 0
ELSE blocksize * (sml.relpages - otta)::bigint END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr%pagesize = 0 THEN pagesize
ELSE datahdr%pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - (CASE WHEN hdr%pagesize = 0 THEN pagesize
ELSE hdr%pagesize END)))::numeric
AS datahdr
, (maxfracsum * (nullhdr + pagesize - (CASE WHEN nullhdr%pagesize = 0 THEN pagesize
ELSE nullhdr%pagesize END)))
AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
( SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2)
FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8
ELSE 4 END AS pagesize
FROM ( SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
) AS sml
WHERE schemaname = 'public'
ORDER BY 1
SQL
integer_columns = %w[
otta
pages
pagesize
rows
wasted_bytes
wasted_pages
wasted_size
]
float_columns = %w[
percent_bloat
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
float_columns.each { |c| datum[c] = datum[c].to_f }
end
data.to_a
end
# Taken from: https://github.com/bucardo/check_postgres/blob/2.19.0/check_postgres.pl#L3492
# and referenced here: http://wiki.postgresql.org/wiki/Show_database_bloat
# check_postgres is Copyright (C) 2007-2012, Greg Sabino Mullane
#
# Changes applied:
# Removed schemaname and totalwastedbytes columns from the original to fit our requirements.
# Reformatted the SQL and renamed some columns to make them more easier to id.
def index_bloat
data = select(<<-SQL, "Index Bloat")
SELECT tablename AS table_name
, iname AS index_name
, ituples::bigint AS rows
, ipages::bigint AS pages
, iotta AS otta
, ROUND(CASE WHEN iotta = 0 OR ipages = 0 OR ipages = iotta THEN 0.0 ELSE ipages / iotta::numeric END, 1) AS percent_bloat
, CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wasted_pages
, CASE WHEN ipages < iotta THEN 0 ELSE (blocksize * (ipages - iotta))::bigint END AS wasted_size
, CASE WHEN ipages < iotta THEN 0 ELSE blocksize * (ipages - iotta) END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr%pagesize = 0 THEN pagesize
ELSE datahdr%pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples, 0) AS ituples, COALESCE(c2.relpages, 0) AS ipages
, COALESCE(CEIL((c2.reltuples * (datahdr - 12)) / (blocksize - 20::float)), 0) AS iotta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - (case when hdr%pagesize = 0 THEN pagesize ELSE hdr%pagesize END)))::numeric AS datahdr
, (maxfracsum * (nullhdr + pagesize - (case when nullhdr%pagesize = 0 THEN pagesize ELSE nullhdr%pagesize END))) AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
(SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS pagesize
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i
ON indrelid = cc.oid
LEFT JOIN pg_class c2
ON c2.oid = i.indexrelid
) AS sml
WHERE schemaname = 'public'
ORDER BY 1, 2
SQL
integer_columns = %w[
otta
pages
pagesize
rows
wasted_bytes
wasted_pages
wasted_size
]
float_columns = %w[
percent_bloat
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
float_columns.each { |c| datum[c] = datum[c].to_f }
end
data.to_a
end
# Taken from: https://github.com/bucardo/check_postgres/blob/2.19.0/check_postgres.pl#L3492
# and referenced here: http://wiki.postgresql.org/wiki/Show_database_bloat
# check_postgres is Copyright (C) 2007-2012, Greg Sabino Mullane
#
# Changes applied:
# Removed schemaname and totalwastedbytes columns from the original to fit our requirements.
# Reformatted the SQL and renamed some columns to make them more easier to id.
# Changed to a UNION so that it is easier to read the output and to separate table stats from idx stats.
def database_bloat
data = select(<<-SQL, "Database Bloat")
SELECT tablename AS table_name
, ' ' AS index_name
, reltuples::bigint AS rows
, relpages::bigint AS pages
, otta
, ROUND(CASE WHEN otta = 0 OR sml.relpages = 0 OR sml.relpages = otta THEN 0.0 ELSE sml.relpages / otta::numeric END, 1) AS percent_bloat
, CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wasted_pages
, CASE WHEN relpages < otta THEN 0 ELSE (blocksize * (relpages - otta))::bigint END AS wasted_size
, CASE WHEN relpages < otta THEN 0 ELSE blocksize * (sml.relpages - otta)::bigint END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr % pagesize = 0 THEN pagesize
ELSE datahdr % pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - (CASE WHEN hdr%pagesize = 0 THEN pagesize
ELSE hdr%pagesize END)))::numeric AS datahdr
, (maxfracsum * (nullhdr + pagesize - (CASE WHEN nullhdr % pagesize = 0 THEN pagesize
ELSE nullhdr % pagesize END))) AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
( SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS pagesize
FROM ( SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname
AND nn.nspname <> 'information_schema'
) AS sml
WHERE schemaname = 'public'
UNION
SELECT tablename AS table_name
, iname AS index_name
, ituples::bigint AS rows
, ipages::bigint AS pages
, iotta AS otta
, ROUND(CASE WHEN iotta = 0 OR ipages = 0 OR ipages = iotta THEN 0.0 ELSE ipages / iotta::numeric END, 1) AS percent_bloat
, CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wasted_pages
, CASE WHEN ipages < iotta THEN 0 ELSE (blocksize * (ipages - iotta))::bigint END AS wasted_size
, CASE WHEN ipages < iotta THEN 0 ELSE blocksize * (ipages - iotta) END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr % pagesize = 0 THEN pagesize
ELSE datahdr % pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples, 0) AS ituples, COALESCE(c2.relpages, 0) AS ipages
, COALESCE(CEIL((c2.reltuples * (datahdr - 12)) / (blocksize - 20::float)), 0) AS iotta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - ( CASE WHEN hdr%pagesize = 0 THEN pagesize
ELSE hdr%pagesize END)))::numeric AS datahdr
, (maxfracsum * (nullhdr + pagesize - ( CASE WHEN nullhdr % pagesize = 0 THEN pagesize
ELSE nullhdr % pagesize END))) AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
( SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS pagesize
FROM ( SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname
AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i
ON indrelid = cc.oid
LEFT JOIN pg_class c2
ON c2.oid = i.indexrelid
) AS sml
WHERE schemaname = 'public'
ORDER BY 1, 2
SQL
integer_columns = %w[
otta
pages
pagesize
rows
wasted_bytes
wasted_pages
wasted_size
]
float_columns = %w[
percent_bloat
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
float_columns.each { |c| datum[c] = datum[c].to_f }
end
data.to_a
end
def table_statistics
data = select(<<-SQL, "Table Statistics")
SELECT relname AS table_name
, seq_scan AS table_scans
, seq_tup_read AS sequential_rows_read
, idx_scan AS index_scans
, idx_tup_fetch AS index_rows_fetched
, n_tup_ins AS rows_inserted
, n_tup_upd AS rows_updated
, n_tup_del AS rows_deleted
, n_tup_hot_upd AS rows_hot_updated
, n_live_tup AS rows_live
, n_dead_tup AS rows_dead
, last_vacuum AS last_vacuum_date
, last_autovacuum AS last_autovacuum_date
, last_analyze AS last_analyze_date
, last_autoanalyze AS last_autoanalyze_date
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY relname ASC ;
SQL
integer_columns = %w[
table_scans
sequential_rows_read
index_scans
index_rows_fetched
rows_inserted
rows_updated
rows_deleted
rows_hot_updated
rows_live
rows_dead
]
timestamp_columns = %w[
last_vacuum_date
last_autovacuum_date
last_analyze_date
last_autoanalyze_date
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
timestamp_columns.each { |c| datum[c] = ActiveRecord::Type::Time.new.deserialize(datum[c]) }
end
data.to_a
end
# Provide the database statistics for all tables and indexes
def statistics
stats = select(<<-SQL, "Statistics")
SELECT relname AS name,
reltuples AS rows,
relpages AS pages
FROM pg_class
ORDER BY reltuples DESC, relpages DESC
SQL
stats.each do |s|
s["rows"] = s["rows"].to_f.to_i
s["pages"] = s["pages"].to_f.to_i
s["size"] = s["pages"] * 8 * 1024
s["average_row_size"] = s["pages"].to_f / (s["rows"] + 1) * 8 * 1024
end
stats.to_a
end
def table_size
stats = select(<<-SQL, "Table Size")
SELECT relname AS table_name
, reltuples AS rows
, relpages AS pages
FROM pg_class
WHERE reltuples > 1
AND relname NOT LIKE 'pg_%'
ORDER BY reltuples DESC
, relpages DESC ;
SQL
stats.each do |s|
s["rows"] = s["rows"].to_f.to_i
s["pages"] = s["pages"].to_f.to_i
s["size"] = s["pages"] * 8 * 1024
s["average_row_size"] = s["pages"].to_f / (s["rows"] + 1) * 8 * 1024
end
stats.to_a
end
def table_total_size(table)
select_value("SELECT pg_total_relation_size('#{table}')").to_i
end
# @return [Hash<String,String>] a hash of {table_name => text_table_name}
def text_table_names
data = select_rows(<<-SQL, "Text Table Names")
SELECT t.relname AS table_name, tt.relname AS text_table_name
FROM pg_class t
JOIN pg_class tt ON t.reltoastrelid = tt.oid
SQL
data.to_h
end
# @return [Hash<String,Array<String>>] a hash of {table_name => [index_names]}
def index_names
data = select_rows(<<-SQL, "Index Names")
SELECT DISTINCT t.relname AS table_name, i.relname AS index_name
FROM pg_class t
JOIN pg_index d ON t.oid = d.indrelid
JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
SQL
result = Hash.new { |h, k| h[k] = [] }
data.each_with_object(result) { |(k, v), h| h[k] << v }
end
def primary_key?(table_name)
select_value(<<-SQL)
SELECT EXISTS(
SELECT 1
FROM pg_index
WHERE indrelid = '#{table_name}'::regclass AND indisprimary = true
)
SQL
end
def table_metrics_bloat(table_name)
data = select(<<-SQL, "Table Metrics Bloat Analysis")
SELECT tablename AS table_name
, reltuples::bigint AS rows
, relpages::bigint AS pages
, otta
, ROUND(CASE WHEN otta = 0 OR sml.relpages = 0 OR sml.relpages = otta THEN 0.0
ELSE sml.relpages / otta::numeric END, 1) AS percent_bloat
, CASE WHEN relpages < otta THEN 0
ELSE relpages::bigint - otta END AS wasted_pages
, CASE WHEN relpages < otta THEN 0
ELSE (blocksize * (relpages - otta))::bigint END AS wasted_size
, CASE WHEN relpages < otta THEN 0
ELSE blocksize * (sml.relpages - otta)::bigint END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr%pagesize = 0 THEN pagesize
ELSE datahdr%pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - (CASE WHEN hdr%pagesize = 0 THEN pagesize
ELSE hdr%pagesize END)))::numeric
AS datahdr
, (maxfracsum * (nullhdr + pagesize - (CASE WHEN nullhdr%pagesize = 0 THEN pagesize
ELSE nullhdr%pagesize END)))
AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
( SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2)
FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8
ELSE 4 END AS pagesize
FROM ( SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
) AS sml
WHERE schemaname = 'public'
AND tablename = '#{table_name}'
ORDER BY 1
SQL
integer_columns = %w[
otta
pages
rows
wasted_bytes
wasted_pages
wasted_size
]
float_columns = %w[
percent_bloat
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
float_columns.each { |c| datum[c] = datum[c].to_f }
end
data.to_a
end
def table_metrics_analysis(table_name)
data = select(<<-SQL, "Table Metrics Stats Analysis")
SELECT seq_scan AS table_scans
, seq_tup_read AS sequential_rows_read
, idx_scan AS index_scans
, idx_tup_fetch AS index_rows_fetched
, n_tup_ins AS rows_inserted
, n_tup_upd AS rows_updated
, n_tup_del AS rows_deleted
, n_tup_hot_upd AS rows_hot_updated
, n_live_tup AS rows_live
, n_dead_tup AS rows_dead
, last_vacuum AS last_vacuum_date
, last_autovacuum AS last_autovacuum_date
, last_analyze AS last_analyze_date
, last_autoanalyze AS last_autoanalyze_date
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND relname = '#{table_name}'
ORDER BY relname ASC ;
SQL
integer_columns = %w[
table_scans
sequential_rows_read
index_scans
index_rows_fetched
rows_inserted
rows_updated
rows_deleted
rows_hot_updated
rows_live
rows_dead
]
timestamp_columns = %w[
last_vacuum_date
last_autovacuum_date
last_analyze_date
last_autoanalyze_date
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
timestamp_columns.each { |c| datum[c] = ActiveRecord::Type::Time.new.deserialize(datum[c]) }
end
data.to_a
end
def table_metrics_total_size(table_name)
select_value(<<-SQL, "Table Metrics Total Size").to_i
SELECT pg_total_relation_size('#{table_name}'::regclass) AS total_table_size;
SQL
end
def number_of_db_connections
select_value(<<-SQL, "DB Client Connections").to_i
SELECT count(*) as active_connections
FROM pg_stat_activity
SQL
end
def index_metrics_bloat(index_name)
data = select(<<-SQL, "Index Metrics Bloat Analysis")
SELECT tablename AS table_name
, iname AS index_name
, ituples::bigint AS rows
, ipages::bigint AS pages
, iotta AS otta
, ROUND(CASE WHEN iotta = 0 OR ipages = 0 OR ipages = iotta THEN 0.0 ELSE ipages / iotta::numeric END, 1) AS percent_bloat
, CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wasted_pages
, CASE WHEN ipages < iotta THEN 0 ELSE (blocksize * (ipages - iotta))::bigint END AS wasted_size
, CASE WHEN ipages < iotta THEN 0 ELSE blocksize * (ipages - iotta) END AS wasted_bytes
FROM ( SELECT schemaname
, tablename
, cc.reltuples
, cc.relpages
, blocksize
, CEIL((cc.reltuples * ((datahdr + pagesize - (CASE WHEN datahdr%pagesize = 0 THEN pagesize
ELSE datahdr%pagesize END)) + nullhdr2 + 4)) / (blocksize - 20::float)
) AS otta
, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples, 0) AS ituples, COALESCE(c2.relpages, 0) AS ipages
, COALESCE(CEIL((c2.reltuples * (datahdr - 12)) / (blocksize - 20::float)), 0) AS iotta
FROM ( SELECT pagesize
, blocksize
, schemaname
, tablename
, (datawidth + (hdr + pagesize - (case when hdr%pagesize = 0 THEN pagesize ELSE hdr%pagesize END)))::numeric AS datahdr
, (maxfracsum * (nullhdr + pagesize - (case when nullhdr%pagesize = 0 THEN pagesize ELSE nullhdr%pagesize END))) AS nullhdr2
FROM ( SELECT schemaname
, tablename
, hdr
, pagesize
, blocksize
, SUM((1 - null_frac) * avg_width) AS datawidth
, MAX(null_frac) AS maxfracsum
, hdr + ( SELECT 1 + count(*) / 8
FROM pg_stats s2
WHERE null_frac <> 0
AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s
, ( SELECT
(SELECT current_setting('block_size')::numeric) AS blocksize
, CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
, CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS pagesize
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1, 2, 3, 4, 5
) AS foo
) AS rs
JOIN pg_class cc
ON cc.relname = rs.tablename
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i
ON indrelid = cc.oid
LEFT JOIN pg_class c2
ON c2.oid = i.indexrelid
) AS sml
WHERE iname = '#{index_name}'
ORDER BY 1, 2
SQL
integer_columns = %w[
otta
pages
pagesize
rows
wasted_bytes
wasted_pages
wasted_size
]
float_columns = %w[
percent_bloat
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
float_columns.each { |c| datum[c] = datum[c].to_f }
end
data.to_a
end
def index_metrics_analysis(index_name)
data = select(<<-SQL, "Index Metrics Stats Analysis")
SELECT relid AS table_id
, indexrelid AS index_id
, schemaname
, relname AS table_name
, indexrelname AS index_name
, idx_scan AS index_scans
, idx_tup_read AS index_rows_read
, idx_tup_fetch AS index_rows_fetched
FROM pg_stat_user_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND indexrelname = '#{index_name}' ;
SQL
integer_columns = %w[
table_id
index_id
index_scans
index_rows_read
index_rows_fetched
]
data.each do |datum|
integer_columns.each { |c| datum[c] = datum[c].to_i }
end
data.to_a
end
def index_metrics_total_size(_index_name)
select_value(<<-SQL, "Index Metrics - Size").to_i
SELECT pg_total_relation_size('#{table_name}'::regclass) - pg_relation_size('#{table_name}') AS index_size;
SQL
end
# DBA operations
#
# Fetch data directory
def data_directory
select_value(<<-SQL, "Select data directory")
SELECT setting AS path
FROM pg_settings
WHERE name = 'data_directory'
SQL
end
# Fetch PostgreSQL last start date/time
def last_start_time
start_time = select_value(<<-SQL, "Select last start date/time")
SELECT pg_postmaster_start_time()
SQL
ActiveRecord::Type::DateTime.new.deserialize(start_time)
end
def analyze_table(table)
execute("ANALYZE #{quote_table_name(table)}")
end
def reindex_table(table)
execute("REINDEX TABLE #{quote_table_name(table)}")
end
def vacuum_analyze_table(table)
execute("VACUUM ANALYZE #{quote_table_name(table)}")
end
def vacuum_full_analyze_table(table)
execute("VACUUM FULL ANALYZE #{quote_table_name(table)}")
end
end