pgFirstAid

randoneering/pgFirstAid: Easy to deploy, open source, postgresql function that provides a prioritized list of actions to take to improve stability and performance.

by

Easy to deploy, open source PostgreSQL function that provides a prioritized list of tasks to improve database stability and performance.

Inspired by Brent Ozer’s FirstResponderKit for SQL Server, PGFirstAid Designed for everyone to use—not just DBAs! Get actionable health information from your PostgreSQL database in seconds.

  • zero dependency – Single SQL function, no external tools required
  • thorough investigation – 12 (and growing!) built-in health checks that cover critical performance and stability issues
  • priority results – Sort issues by severity (Critical → High → Medium → Low → Information)
  • actionable recommendations – Each issue includes specific troubleshooting steps
  • Documentation link – Direct links to official PostgreSQL documentation for deeper learning
-- Copy and paste the function definition into your PostgreSQL database
-- Then run it:
SELECT * FROM pg_firstAid();

That’s it! No configuration required. To avoid problems, deploy as a user with the highest possible privileges (in your environment).

seriousness Social class check_name object_name Issue Description current value recommended Action documentation_link
Serious table structure primary key is missing public.user Table is missing primary key… no primary key defined Add primary key or unique constraint… https://www.postgresql.org/…
High figures missing statistics public.order The table was never analyzed… Final analysis: never Run ANALYZE on this table… https://www.postgresql.org/…

  1. missing primary keys – Tables without primary keys which can cause replication problems and poor performance
  2. unused large index – Indexes consume significant disk space but are never used (>10MB, 0 scans)
  1. table bloat – Tables with >20% bloat affecting performance (tables >100MB)
  2. missing statistics – Tables were never analyzed, leaving the query planner without statistics
  3. duplicate index – Multiple indexes with similar or overlapping column sets
  1. old data – Table statistics older than 7 days with significant modifications
  2. low index efficiency – Indices with poor selectivity (scan-to-tuple ratio >1000)
  3. highly sequential scan – Tables with high sequential scan activity that could benefit from indexes
  4. high connection count – More than 50 active connections potentially impacting performance
  1. missing foreign key index – Foreign key constraints without supporting indexes for efficient joins
  1. database size – Monitoring current database size and growth
  2. PostgreSQL version – Version information and configuration details
-- Show only critical issues
SELECT * FROM pg_firstAid() WHERE severity = 'CRITICAL';

-- Show critical and high priority issues
SELECT * FROM pg_firstAid() WHERE severity IN ('CRITICAL', 'HIGH');
-- Focus on index-related issues
SELECT * FROM pg_firstAid() WHERE category LIKE '%Index%';

-- Check table maintenance issues
SELECT * FROM pg_firstAid() WHERE category = 'Table Maintenance';
SELECT severity, COUNT(*) as issue_count
FROM pg_firstAid()
GROUP BY severity
ORDER BY MIN(CASE severity
    WHEN 'CRITICAL' THEN 1
    WHEN 'HIGH' THEN 2
    WHEN 'MEDIUM' THEN 3
    WHEN 'LOW' THEN 4
    ELSE 5 END);
  • daily – Quick health check as part of morning routine
  • before deployment – Catch potential issues before they impact production
  • after major changes – Verify database health after schema modification or data migration
  • display troubleshooting – First step when investigating slow queries or system problems
  • capacity planning – Regular monitoring to keep track of database growth trends

read before acting

  • Always review recommendations carefully before making changes. I’ve been supporting Postgres databases for nearly a decade, but I learn something new every day
  • Test in non-production environment first
  • Some operations (such as VACUUM FULL) require a maintenance window.
  • Never drop an index without validating its usage patterns over time

permissions

  • Requires read access to the system catalog (pg_catalog,
  • Works with standard user permissions for most tests
  • Some checks may yield fewer results for non-superuser accounts

pgFirstAid is designed to be lightweight and secure to run on production systems:

  • Read-only operations (no modifications to your data or schema)
  • Uses system catalog views that are already cached
  • Typical execution time: <1 second on most databases
  • No locking or blocking of user queries
  • PostgreSQL 10+ – Fully supported, but only tested on 15+. This will change as the version becomes obsolete
  • PostgreSQL 9.x – Most features work (may require minor syntax adjustments)
  • Works with all PostgreSQL-compatible databases (Amazon RDS, Aurora, Azure Database, etc.).

Found any bugs? Do you have an idea for a new health checkup? tell me! I want this to be a tool that is available to everyone, casual DBA or not.

gplv3

Inspired by Brent Ozar’s FirstResponderKit for SQL Server. Thank you to the SQL Server community for pioneering accessible database health monitoring!

Dave-IYKYK

Built with ☕ for PostgreSQL and the open source community



Leave a Comment