Rails PgHero: Postgres Health Dashboard voor Trage Queries, Ontbrekende Indexen en Ruimtebewaking

Vorig kwartaal deed ik een database-audit voor een Series B SaaS op Rails 7.2 en Postgres 15. Hun engineeringteam was ervan overtuigd dat ze een schaalprobleem hadden. Wat ze hadden waren vijf queries die 62% van de database-CPU opaten, drie tabellen met 8 GB aan dode tuples, en elf ongebruikte indexen die 14 GB schijfruimte verspilden. Totale tijd om dat allemaal te vinden: ongeveer twaalf minuten met Rails PgHero gemount op /pghero.

Ze hadden geen grotere database nodig. Ze hadden een dokter nodig. Rails PgHero is die dokter, en het is honderd regels Ruby, een mountable engine, en een stethoscoop voor je Postgres-cluster.

Na negentien jaar Rails en waarschijnlijk vierhonderd database-audits is PgHero nog steeds het eerste wat ik installeer op een clientsysteem als ik moet begrijpen waar de pijn vandaan komt. Niet Datadog, niet New Relic, niet pganalyze. Gewoon PgHero. Het beantwoordt de vragen die ik daadwerkelijk heb in de eerste dertig minuten: welke queries doen pijn, welke indexen ontbreken, welke indexen liggen ongebruikt in de weg, en welke tabellen zijn stilletjes aan het rotten.

Wat Rails PgHero Eigenlijk Doet

Rails PgHero is een mountable Rails-engine die Postgres system views (pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes, pg_stat_activity) bevraagt en de resultaten als dashboard rendert. Meer niet. Geen agents, geen externe dienst, geen data die je infrastructuur verlaat.

Het dashboard laat acht dingen zien die ertoe doen:

• Trage queries — de topovertreders op totale tijd, gerangschikt op total_time / calls.
• Lang lopende queries — alles wat langer actief is dan een drempel (standaard 60s).
• Ontbrekende indexen — sequential scans op grote tabellen die baat zouden hebben bij een index.
• Ongebruikte indexen — indexen die Postgres in het gevolgde venster nooit heeft aangeraakt.
• Ongeldige indexen — mislukte CREATE INDEX CONCURRENTLY-operaties die je bent vergeten.
• Tabelruimte en bloat — opgeblazen tabellen die VACUUM FULL of pg_repack nodig hebben.
• Dubbele indexen — twee indexen die dezelfde kolommen dekken.
• Actieve connecties — wie is verbonden, vanaf waar, en welke query draait er.

Het is geen volledige APM. Het tekent geen flame graph en volgt geen query over servicegrenzen heen. Het is het tienstuivergereedschap dat de vragen beantwoordt die in elk productie-incident opkomen dat ik ooit heb gedraaid.

Rails PgHero Installeren

De installatie is saai op de goede manier. Voeg de gem toe en mount de engine.

# Gemfile
gem "pghero"

# config/routes.rb
Rails.application.routes.draw do
  authenticate :user, ->(u) { u.admin? } do
    mount PgHero::Engine, at: "pghero"
  end
end

Dat authenticate-blok is niet optioneel. PgHero laat echte productie-querytekst, connectie-informatie en rolnamen zien. Als je app admin-users heeft, wikkel het daarin. Zo niet, wikkel het in HTTP basic auth via Rack::Auth::Basic, of nog beter, zet het achter een Tailscale-only route op je infrastructuur.

Zet nu pg_stat_statements aan. Dit is de Postgres-extensie waarmee PgHero per-query timing kan zien. Zonder deze extensie krijg je connecties en ruimte; met deze extensie krijg je de lijst met trage queries die je middag daadwerkelijk gaat veranderen.

-- als superuser, eenmalig per cluster
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Op managed Postgres (RDS, Aurora, Google Cloud SQL, Supabase, Neon) heb je pg_stat_statements ook nodig in shared_preload_libraries. Op RDS is dat een parameter group-wijziging en een reboot. Op Google Cloud SQL is het een flag. Doe dit voordat je op zoek gaat naar trage queries, anders lacht PgHero je uitdrukkingsloos toe.

De Slow Queries-tab: Die Zichzelf Terugverdient

Open /pghero in productie en ga direct naar “Query Stats.” Je ziet iets als:

Rank | Total Time | Calls   | Avg Time  | Query
1    | 4:12:33    | 892.451 | 16.9 ms   | SELECT * FROM orders WHERE user_id = $1
2    | 2:41:17    | 41      | 3928 s    | SELECT COUNT(*) FROM events WHERE created_at > $1
3    | 1:58:04    | 12.441  | 571 ms    | SELECT ... FROM users LEFT JOIN ... ORDER BY updated_at

Drie dingen om in die lijst naar te zoeken, op volgorde:

1. De topquery op totale tijd. Dit is degene die je de meeste CPU per dag kost. Hij kan snel per aanroep zijn (zoals #1 hierboven, 17 ms) maar loopt zo vaak dat het oplossen — meestal met een betere index of een counter cache — je lucht geeft.
2. De query met de gigantische avg_time. #2 hierboven draait meer dan een uur per aanroep. Dat is een rapport dat iemand vanuit een trage adminpagina start, en het houdt al die tijd een snapshot open. Herschrijf hem met een materialized view. Zie Rails materialized views met Scenic als dat een terugkerend patroon is voor je.
3. Elke query die opduikt die je niet zelf hebt geschreven. Soms is het een gem die zichzelf instrumenteert. Soms is het een gek admin-dashboard. Soms is het Devise die bij elke request een full-table pg_stat_statements-check doet omdat iemand de extensie verkeerd geconfigureerd heeft.

De afgekapte querytekst kan verlengd worden door pg_stat_statements.max en pg_stat_statements.track_utility in je Postgres-config aan te passen. PgHero toont je de genormaliseerde vorm ($1, $2) — de echte waarden worden niet bijgehouden, en dat is met opzet. Als je geparametriseerde queryvoorbeelden nodig hebt, is dat waar een echte APM als Sentry Performance of auto_explain voor is.

Van een Slow-Query-Bevinding naar een Fix

PgHero laat je de query zien. Er een fix van maken is waar een Rails-ontwikkelaar zijn geld verdient. Mijn spiekbriefje:

• WHERE column = $1 op een grote tabel zonder index. Voeg de index toe. EXPLAIN bevestigt.
• ORDER BY column DESC LIMIT n met een filter. Je wilt een composite index die eerst de WHERE-kolommen dekt, dan de ORDER BY-kolom.
• COUNT(*) met WHERE created_at > $1. Benader hem met pg_class.reltuples voor een schatting op volledige tabel, of voeg een partial index toe. Of gebruik een counter cache — zie Rails counter cache om N+1 count-queries te elimineren.
• SELECT * FROM ... LIMIT 25 OFFSET 10000. Stap over op keyset-paginatie. Klassiek op dit punt, maar nog steeds verrassend gebruikelijk — zie Rails cursor-paginatie vs offset.

Ontbrekende Indexen: De Snelste Winst

De “Space”- en “Missing Indexes”-tabs zijn het equivalent van een dokter die een gebroken bot ontdekt op een röntgenfoto. PgHero vlaggen elke tabel waar Postgres sequential scans doet op meer dan 10.000 rijen en geen index helpt.

# config/initializers/pghero.rb
PgHero.long_running_query_sec = 60
PgHero.slow_query_ms = 20      # alles gemiddeld > 20ms is "slow"
PgHero.slow_query_calls = 100  # ...alleen als hij minstens 100 keer draait in het venster

Ik zet slow_query_ms laag (20 ms) op elke app die met echt verkeer draait. De standaard (100 ms) verbergt queries die alleen snel lijken omdat ze tegen een warme buffer cache lopen. Twintig milliseconden is ongeveer het punt waarop een index zou winnen.

Voor ontbrekende indexen kan PgHero voorstellen wat je moet toevoegen:

PgHero.suggested_indexes
# => [
#      { table: "orders", columns: ["user_id"], using: "btree" },
#      { table: "events", columns: ["team_id", "created_at"], using: "btree" }
#    ]

Dit zijn suggesties, geen commando’s. Voeg ze in productie altijd toe met CREATE INDEX CONCURRENTLY en gebruik add_index :orders, :user_id, algorithm: :concurrently in migraties. Zelfs dan, draai het achter Strong Migrations zodat de migratie wordt geblokkeerd als je het vergeet.

Ongebruikte Indexen: Waar Niemand Over Praat

Ontbrekende indexen krijgen de aandacht. Ongebruikte indexen zijn degene die je stilletjes geld kosten. Elke index voegt write amplification toe bij INSERT, UPDATE en DELETE. Op een hete write-tabel met vijftien indexen zijn dat vijftien B-tree-updates per rijwijziging. Als er elf nooit gelezen worden, verbrand je CPU en schijf voor niets.

PgHero vlagt ze:

Index                          | Size    | Scans
orders_shipped_at_idx          | 2,1 GB  | 0
orders_promo_code_idx          | 1,4 GB  | 0
users_signup_source_idx        | 890 MB  | 0

Nul scans sinds de laatste stats-reset betekent dat Postgres deze index nooit heeft gebruikt om een query te beantwoorden. Soms komt dat doordat de query planner voor een ander plan kiest (onwaarschijnlijk als de index goed van vorm is). Vaker is het een overblijfsel van een feature die niemand meer gebruikt, of van een query die is herschreven en waarvan de index nooit verwijderd is.

Drop ze niet dezelfde middag dat PgHero ze vlagt. Stats-resets gebeuren — na een major version upgrade, na een pg_stat_statements_reset(), na een failover. Kijk een week of twee naar een “zero scans”-vlag voordat je de index verwijdert. Verwijder hem dan met DROP INDEX CONCURRENTLY.

Lang Lopende Queries: De Pager Om 3 Uur ‘s Nachts

De “Long Running Queries”-tab laat alles zien dat langer actief is dan de drempel die je hebt ingesteld (standaard 60 seconden). Dit is waar je het ding vangt dat je on-call om 3 uur ‘s nachts gaat pageren als je niet ingrijpt.

PgHero.long_running_query_sec = 60

De twee smaken die ik constant zie:

• Idle in transaction — een background job heeft een transactie geopend, wat werk gedaan en toen een HTTP-call gedaan die nog steeds hangt. De transactie houdt rijlocks vast. Alles wat op die rijen wacht staat in de rij. Zie Rails pessimistic locking met SELECT FOR UPDATE voor hoe deze ontstaan.
• Een traag rapport — iemand heeft een admin-export gestart. Die houdt nu een snapshot open, en autovacuum kan geen dode tuples opruimen op tafels die hij aanraakt. Table bloat begint te klimmen.

Kill ze direct vanuit PgHero. Naast elke rij staat een “Kill”-knop. Die roept pg_terminate_backend(pid) aan. Doe het tijdens het incident, ga daarna uitzoeken hoe ze zijn ontstaan. Voeg een statement timeout toe om herhaling te voorkomen:

# config/database.yml
production:
  variables:
    statement_timeout: 30000   # 30 seconden voor web

Voor lang lopende background jobs stel je de timeout per connectie in in plaats van globaal.

Tabelruimte en Bloat

De “Space”-tab toont tabel- en indexgroottes gerangschikt op schijfgebruik. Het is de tab die ik als eerste open als een klant zegt “de database vult sneller dan we verwachtten.” Het verhaal is bijna altijd een van deze:

• Een log_entries- of webhook_events-tabel zonder retentiebeleid. Voeg een deleted_at-kolom toe, draai een nachtelijke DELETE ... WHERE created_at < 90.days.ago, en overweeg Postgres partitioning met pg_partman als het meer is dan 100 GB.
• Een JSONB-kolom die de volledige API-response opslaat voor auditdoeleinden. Verplaats hem naar S3 met een foreign key naar het object, of splits hem uit naar een logs-tabel die je kunt prunen.
• Bloat door tabellen met veel churn. PgHero toont een schatting — als een tabel 10 GB toegewezen heeft maar 3 GB aan levende rijen, is dat 70% bloat. Autovacuum zou het moeten opruimen, maar op zeer high-churn tabellen kan het achterlopen. pg_repack is de operationele fix. Zie de bloat-metric in PgHero’s Space-tab.

Query Stats-geschiedenis met Meerdere Databases

PgHero kan querystats persisteren naar zijn eigen tabel zodat je trends over tijd ziet, niet alleen de huidige pg_stat_statements-snapshot.

# config/initializers/pghero.rb
PgHero.databases = {
  primary: { url: ENV["DATABASE_URL"] },
  analytics: { url: ENV["ANALYTICS_DATABASE_URL"] }
}

# db/migrate/xxx_create_pghero_query_stats.rb
class CreatePgheroQueryStats < ActiveRecord::Migration[7.1]
  def change
    create_table :pghero_query_stats do |t|
      t.text :database
      t.text :user
      t.text :query
      t.integer :query_hash, limit: 8
      t.float :total_time
      t.integer :calls
      t.timestamp :captured_at
    end
    add_index :pghero_query_stats, [:database, :captured_at]
  end
end

Zet dan een recurring job op — Solid Queue recurring jobs is hier perfect voor — om elke vijf minuten stats vast te leggen:

# app/jobs/pghero_query_stats_job.rb
class PgheroQueryStatsJob < ApplicationJob
  queue_as :low

  def perform
    PgHero.capture_query_stats
    PgHero.clean_query_stats(before: 14.days.ago)
  end
end

# config/recurring.yml
production:
  pghero_query_stats:
    class: PgheroQueryStatsJob
    schedule: every 5 minutes

Nu heeft de Query Stats-tab een tijdbereikkiezer. Je kunt “wat was traag op dinsdag om 14 uur” vergelijken met “wat is nu traag”, wat onbetaalbaar is voor het vangen van regressies uit een deploy.

Space-Stats Geschiedenis en Alerts

Dezelfde behandeling werkt voor tabelruimte over tijd:

PgHero.capture_space_stats

En PgHero heeft ingebouwde alerting hooks. Verbind hem met je notifier:

# config/initializers/pghero.rb
PgHero.methods_added_to_notifier = true

# app/notifiers/pghero_notifier.rb
class PgheroNotifier
  def self.slow_query(query, options)
    return unless options[:total_time] > 3600  # alleen alerten > 1u totale tijd
    SlackNotifier.ping("Slow query in #{options[:database]}: #{query[0..200]}")
  end
end

Ik zet dit meestal pas aan als een app een paar weken zonder alerts PgHero heeft gedraaid. Anders krijg je het eerste uur 200 “slow query”-Slack-berichten en leert iedereen het kanaal te dempen.

Productiebeveiliging Checklist

PgHero toont echte productiedata. Behandel het als toegang tot een Rails-console.

• Authenticeer de mount. Devise authenticate :user, ->(u) { u.admin? }, HTTP basic, of een firewall op infra-niveau. Laat /pghero nooit open.
• Gebruik een read-only Postgres-rol voor de PgHero-connectie. Die heeft alleen SELECT nodig op pg_stat_statements, pg_stat_activity, pg_stat_user_tables, pg_stat_user_indexes, en de pg_terminate_backend-functie als je de Kill-knop wilt.
• Exposeer het niet aan Cloudflare Access zonder SSO. De Kill-knop is echt. Een gescreenshotte URL vanaf de laptop van een stagiair kan een outage veroorzaken.

De read-only rol setup:

CREATE ROLE pghero LOGIN PASSWORD 'sterk-wachtwoord';
GRANT pg_monitor TO pghero;
GRANT EXECUTE ON FUNCTION pg_terminate_backend(integer) TO pghero;

Dan in je pghero.rb-initializer:

PgHero.databases = {
  primary: { url: ENV["PGHERO_DATABASE_URL"] }  # gebruikt de pghero-rol
}

Wat PgHero Niet Doet

Twee jaar geleden zou ik gezegd hebben “je hebt ook pganalyze nodig.” Nu zeg ik “je hebt pganalyze nodig als je voorbij 500 GB data zit of als je engineeringteam eigenaar is van hun Postgres.” Daaronder is PgHero + de rauwe output van EXPLAIN (ANALYZE, BUFFERS) op de queries die het vlagt, genoeg.

Wat PgHero mist:

• Geen queryplannen. Voor elke query die PgHero vlagt, draai zelf EXPLAIN (ANALYZE, BUFFERS) in een psql-console. Pipe de output door explain.dalibo.com om hem te visualiseren.
• Geen indexdiepte of B-tree-fragmentatie. Die tellen op schaal. De pgstattuple-extensie geeft je de rauwe cijfers.
• Geen lock-trees. Voor deadlock-onderzoek wil je pg_locks gejoined tegen pg_stat_activity, wat PgHero niet toont. Datadog Database Monitoring of een custom script.
• Geen historische pg_stat_statements op rijniveau. PgHero aggregeert het in buckets. Als je rauwe historische query-events nodig hebt, is pganalyze of een custom pipeline het antwoord.

Voor 95% van de Rails-apps doet niks daarvan ertoe. Installeer PgHero, hang de query stats-history job er aan, zet het achter admin-auth, en check het elke maandagochtend. Die enkele gewoonte doet meer voor je database-gezondheid dan welke APM ook.

Veelgestelde Vragen

Heb ik pg_stat_statements nodig om Rails PgHero nuttig te maken?

Niet voor alles. Zonder pg_stat_statements krijg je nog steeds connecties, lang lopende queries, tabelruimte, ongebruikte indexen en dubbele indexen — wat al waardevol is. Maar de Slow Queries-tab is leeg. Op managed Postgres schakel je het in via een parameter group-wijziging en een reboot; op self-hosted voeg je pg_stat_statements toe aan shared_preload_libraries in postgresql.conf en herstart je de server. Draai daarna CREATE EXTENSION pg_stat_statements in je database. Schakel het in voordat je PgHero installeert, dan hebben de stats al wat geschiedenis wanneer je voor het eerst inlogt.

Rails PgHero vs pganalyze vs Datadog Database Monitoring — welke moet ik gebruiken?

Ze zijn niet hetzelfde product. Rails PgHero is het gratis, self-hosted “eerste tool dat je installeert” dat 90% van de database-health-vragen in één dashboard beantwoordt. pganalyze is een betaalde SaaS die queryplan-geschiedenis toevoegt, indexadviezen met kostenschattingen, en vacuum-tracking — de moeite waard boven 500 GB of als Postgres je carrière is. Datadog Database Monitoring is de moeite waard als je toch al Datadog betaalt en databasetraces gecorreleerd met APM-spans over services heen wilt. Onder 500 GB en buiten enterprise Datadog-contracten, begin met PgHero. Je groeit er niet zo snel overheen als je denkt.

Hoe beveilig ik de /pghero-mount in productie?

Laat het nooit open staan. Wikkel het in een Devise authenticate-blok dat op admin controleert, of gebruik Rack::Auth::Basic met een credential opgeslagen in Rails credentials. Beperk het daarbovenop op netwerkniveau — zet het achter een Tailscale-only route, Cloudflare Access met SSO, of een VPN. De Kill-knop op de Long Running Queries-tab beëindigt daadwerkelijk connecties, dus behandel toegang tot /pghero met dezelfde ernst als toegang tot de Rails-console. Gebruik een read-only Postgres-rol voor de PgHero-databaseconnectie, alleen pg_monitor en EXECUTE op pg_terminate_backend toegekend.

Kan ik Rails PgHero draaien op meerdere databases zoals read replicas en analytics?

Ja. Configureer PgHero.databases in config/initializers/pghero.rb met één entry per database-URL. Het dashboard toont een dropdown om ertussen te wisselen. Op read replicas moet je weten dat pg_stat_statements-counters op de primary leven — replicas laten een subset van stats zien. Gebruik PgHero op de primary voor query-analyse en op replicas voornamelijk voor connectie-gezondheid en detectie van lang lopende queries. Meerdere databases zijn ook van belang als je Rails read replicas met automatisch switchen gebruikt — je wilt zowel writer- als reader-load op één plek zien.

Hulp nodig om je Postgres uit de gevarenzone te halen? TTB Software doet database-audits, index- en query-tuning en productie-Postgres-reviews voor Rails-teams. We shippen Rails op Postgres al negentien jaar.