Unreadable Sequences

This is likely due to missing privileges. Make sure your user has the SELECT privilege for each sequence.

High Replication Lag

<%= pluralize(@replication_lag.round, "second") %>

Inactive Replication Slots

Inactive replication slots can cause a lot of disk space to be consumed.

For each, run:

SELECT pg_drop_replication_slot('slot_name');

Long Running Queries

We recommend setting a statement timeout on all non-superusers with:

ALTER ROLE <user> SET statement_timeout TO '60s';

Low Cache Hit Rate

Index Hit Rate: <%= (@index_hit_rate * 100).round(1) %>%
Table Hit Rate: <%= (@table_hit_rate * 100).round(1) %>%

The cache hit rate <%= link_to "should be above 99%", "https://devcenter.heroku.com/articles/understanding-postgres-data-caching", target: "_blank" %> in most cases. You can often increase this by adding more memory.

High Number of Connections

<%= pluralize(@total_connections, "connection") %>

<%= link_to "Use connection pooling", "https://www.craigkerstiens.com/2014/05/22/postgres-and-connection-pooling/", target: "_blank" %> for better performance. <%= link_to "PgBouncer", "https://wiki.postgresql.org/wiki/PgBouncer", target: "_blank" %> is a solid option.

High Number of Connections Idle in Transaction

<%= pluralize(@idle_connections, "connection") %> idle in transaction

Avoid opening transactions and doing work outside the database.

<%= link_to "View queries", live_queries_path(state: "idle in transaction") %>

Vacuuming Needed

The database will shutdown when there are fewer than 1,000,000 transactions left. <%= link_to "Read more", "https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND", target: "_blank" %>.

Try <%= link_to "tuning autovacuum", "https://blog.2ndquadrant.com/autovacuum-tuning-basics/", target: "_blank" %> - specifically autovacuum_vacuum_cost_limit.

If that doesn’t work, for each table, run:

VACUUM FREEZE VERBOSE table;

Columns Near Overflow

Consider changing columns to bigint to support a larger range of values.

Invalid Indexes

These indexes exist, but can’t be used. You should recreate them.

DROP INDEX CONCURRENTLY <%= pghero_pretty_ident(index[:name], schema: index[:schema]) %>;
<%= index[:definition].sub("CREATE INDEX ", "CREATE INDEX CONCURRENTLY ") %>;

Invalid Constraints

These constraints are marked as NOT VALID. You should validate them.

ALTER TABLE <%= pghero_pretty_ident(constraint[:table], schema: constraint[:schema]) %> VALIDATE CONSTRAINT <%= pghero_pretty_ident(constraint[:name]) %>;

Duplicate Indexes

These indexes exist, but aren’t needed. Remove them <% if @show_migrations %> with a migration <% end %> for faster writes.

rails generate migration remove_unneeded_indexes

<% @duplicate_indexes.each do |query| %>
<%= pghero_remove_index(query[:unneeded_index]) %><% end %>

<%= unneeded_index[:name] %> (<%= unneeded_index[:columns].join(", ") %>)

<%= covering_index[:name] %> (<%= covering_index[:columns].join(", ") %>)

Suggested Indexes

Add indexes to speed up queries. <% if @show_migrations %> Here’s a migration to help. <% end %>

rails generate migration add_suggested_indexes

commit_db_transaction
<% @suggested_indexes.each do |index| %>
<% if index[:using] && index[:using] != "btree" %>
add_index <%= index[:table].to_sym.inspect %>, <%= index[:columns].first.inspect %>, using: <%= index[:using].inspect %>, algorithm: :concurrently
<% else %>
add_index <%= index[:table].to_sym.inspect %>, [<%= index[:columns].map(&:to_sym).map(&:inspect).join(", ") %>], algorithm: :concurrently<% end %>
<% end %>

to speed up

Query Stats

Query stats are available but not enabled. <%= button_to "Enable", enable_query_stats_path, class: "btn btn-info" %>

Make them available by adding the following lines to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart the server for the changes to take effect.

Slow Queries

Slow queries take <%= @database.slow_query_ms %> ms or more on average and have been called at least <%= @database.slow_query_calls %> times.

<%= link_to "Explain queries", explain_path %> to see where to add indexes.

Unused Indexes

Unused indexes cause unnecessary overhead. Remove them <% if @show_migrations %> with a migration <% end %> for faster writes.

rails generate migration remove_unused_indexes

<% @unused_indexes.each do |query| %>
<%= pghero_remove_index(query)%><% end %>