Index Bloat

<% if @index_bloat.any? %>

Indexes can become <%= link_to "bloated over time", "https://www.compose.com/articles/postgresql-bloat-origins-monitoring-and-managing/", target: "_blank" %>. Recreate them to remove bloat.

For each index, run:

CREATE INDEX CONCURRENTLY new_index ...;
ANALYZE table;
DROP INDEX CONCURRENTLY index;
ANALYZE table;
ALTER INDEX new_index RENAME TO index;

<% if @show_sql %> <%= link_to "Hide SQL", {} %> <% else %> <%= link_to "Show SQL", {sql: "t"} %> <% end %> for each index

<% @index_bloat.each do |index| %> <% if @show_sql && !index[:primary] %> <% end %> <% end %>
Index Bloat Size
<%= index[:index] %> <% if index[:primary] %> PRIMARY <% end %> <%= PgHero.pretty_size(index[:bloat_bytes]) %> <%= PgHero.pretty_size(index[:index_bytes]) %>
<% new_index = "new_#{index[:index]}".first(63) %> 
<%= index[:definition].sub(" INDEX ", " INDEX CONCURRENTLY \n    ").sub(index[:index], new_index) %>;

ANALYZE <%= pghero_pretty_ident(index[:table], schema: index[:schema]) %>;

DROP INDEX CONCURRENTLY
    <%= pghero_pretty_ident(index[:index]) %>;

ANALYZE <%= pghero_pretty_ident(index[:table], schema: index[:schema]) %>;

ALTER INDEX <%= pghero_pretty_ident(new_index) %>
    RENAME TO <%= pghero_pretty_ident(index[:index]) %>;
<% else %>

No significant index bloat!

<% end %>