Pghero Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation Add the bundle to your composer.json:

   composer require czogori/pghero-bundle
   

   Enable it in config/bundles.php:

   return [
       // ...
       Czogori\PgHeroBundle\PgHeroBundle::class => ['all' => true],
   ];
   

2. Database Configuration Ensure your config/packages/doctrine.yaml includes PostgreSQL connection details (required for PgHero to function):

   doctrine:
       dbal:
           connections:
               default:
                   url: '%env(DATABASE_URL)%'
                   driver: 'pdo_pgsql'
   

3. First Use Case Access the profiler tab via Symfony’s Profiler (/_profiler/). Navigate to the "PgHero" tab to inspect PostgreSQL metrics like:

   • Slow queries
   • Locks
   • Index usage
   • Database size

Implementation Patterns

Workflows

1. Debugging Slow Queries

   • Use the "Queries" tab to identify slow-running SQL statements.
   • Filter by execution time or query text to pinpoint bottlenecks.
   • Example: If a SELECT query takes >500ms, analyze its EXPLAIN ANALYZE plan in PgHero.

2. Monitoring Database Health

   • Check the "Locks" tab to detect blocking queries or long-running transactions.
   • Review "Connections" to ensure connection pooling is optimal (e.g., pgbouncer usage).

3. Schema Optimization

   • Use the "Indexes" tab to identify unused or redundant indexes.
   • Leverage "Tables" to analyze bloat (e.g., pg_repack candidates).

4. Integration with Custom Tools

   • Extend PgHero’s data via Symfony’s Event Listeners (e.g., log slow queries to Sentry):

     use Symfony\Component\HttpKernel\Event\RequestEvent;
     
     public function onKernelRequest(RequestEvent $event) {
         $query = $event->getRequest()->query->get('query');
         if (str_contains($query, 'slow_pattern')) {
             // Log to external service
         }
     }
     

Best Practices

• Cache Profiler Data: Disable PgHero in production by setting pg_hero.enabled: false in config/packages/czogori_pghero.yaml.
• Combine with Doctrine Profiler: Use Symfony’s built-in Doctrine Profiler (/_profiler/doctrine) alongside PgHero for ORM-level insights.
• Automate Alerts: Use PgHero’s metrics to trigger alerts (e.g., via symfony/monolog-bundle for critical thresholds).

Gotchas and Tips

Pitfalls

1. Performance Overhead

   • PgHero runs queries against PostgreSQL, which may impact performance on low-resource environments.
   • Fix: Disable in production or limit to specific environments:

     # config/packages/czogori_pghero.yaml
     pg_hero:
         enabled: '%kernel.debug%'  # Only in dev
     

2. Missing PostgreSQL Extensions

   • PgHero relies on PostgreSQL extensions like pg_stat_statements. Ensure they’re enabled:

     CREATE EXTENSION pg_stat_statements;
     

   • Debug: Check pg_extension table for missing extensions.

3. Permission Issues

   • The database user must have SELECT privileges on pg_stat_* views.
   • Fix: Grant permissions:

     GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;
     

4. Outdated Data

   • PgHero caches data by default. For real-time insights, adjust caching:

     pg_hero:
         cache_lifetime: 5  # Refresh every 5 seconds
     

Debugging Tips

• Verify Connection: Test if PgHero connects to the correct database by checking the "Connections" tab.
• Query PgHero Directly: Run raw SQL against PostgreSQL to validate metrics:

  SELECT * FROM pg_stat_activity WHERE state = 'active';
  

• Symfony Profiler Conflicts: If the PgHero tab doesn’t appear, ensure:
  • The bundle is enabled in bundles.php.
  • The profiler toolbar is active (%kernel.debug% = true).

Extension Points

1. Custom Metrics Add your own PostgreSQL queries to PgHero by extending the bundle’s DataCollector:

   use Czogori\PgHeroBundle\DataCollector\PgHeroDataCollector;
   
   class CustomPgHeroCollector extends PgHeroDataCollector {
       public function collect($data) {
           $data['custom_metric'] = $this->getCustomQueryResult();
           return $data;
       }
   }
   

   Register it in services.yaml:

   services:
       App\DataCollector\CustomPgHeroCollector:
           tags: ['data_collector']
   

2. Override Templates Customize PgHero’s Twig templates by overriding the bundle’s resources:

   # config/packages/twig.yaml
   twig:
       paths:
           '%kernel.project_dir%/templates/czogori_pghero': CzogoriPgHeroBundle