Easy-to-deploy, open source PostgreSQL function (and view!) that provides a prioritized list of actions to improve database stability and performance.Inspired by Brent Ozar's FirstResponderKit for SQL Server, pgFirstAid is designed for everyone to use—not just DBAs! Get actionable health insights from your PostgreSQL database in seconds.
This project is supported by:
DigitalOcean is the cloud provider that doesn't make you wade through a thousand services to spin up a VM or database. You get straightforward VMs (they call them Droplets), managed Postgres/MySQL/Redis, Kubernetes if you need it, and pricing you can actually understand. All with an interface that doesn't require a PhD to navigate. Click the badge above to start your journey with DigitalOcean.
Neon gives you serverless PostgreSQL that scales to zero when you're not using it. You get Git-like branching for your database; fork it for testing, preview environments, whatever. Plus, it's just Postgres. All your tools and extensions just work. This project uses Neon for testing pgFirstAid for compatibility, which means you can use it too! Click the Neon image to get started building your next project.
Thank you OSUOSL for providing hosting for our testing infrastructure and CI/CD pipelines! OSU Open Source Lab (osuosl.org) is a nonprofit at Oregon State University that supports the open source community by providing hosting, infrastructure, and systems engineering services for open source projects.
- Zero Dependencies - Single SQL function, no external tools required
- Detailed Checks - built-in health checks covering critical performance and stability issues
- Prioritized Results - Issues ranked by severity (CRITICAL → HIGH → MEDIUM → LOW → INFO)
- Actionable Recommendations - Each issue includes specific remediation steps
- Documentation Links - Direct links to official PostgreSQL documentation for deeper learning
-- Copy and paste the function or view definition into your PostgreSQL database
-- Then run it:
--- function
SELECT * FROM pg_firstAid();
--- view
SELECT * FROM v_pgfirstAid;That's it! No configuration needed. Deploy as a user with the highest possible priviledges (in your environment) to avoid issues.
| severity | category | check_name | object_name | issue_description | current_value | recommended_action | documentation_link |
|---|---|---|---|---|---|---|---|
| CRITICAL | Table Health | Missing Primary Key | public.users | Table missing a primary key... | No primary key defined | Add a primary key or unique constraint... | https://www.postgresql.org/... |
| HIGH | Table Health | Missing Statistics | public.orders | Table has never been analyzed... | Last analyze: Never | Run ANALYZE on this table... | https://www.postgresql.org/... |
- Missing Primary Keys - Tables without primary keys that can cause replication issues and poor performance
- Unused Large Indexes - Indexes consuming significant disk space but never used (>10MB, 0 scans)
- Table Bloat - Tables with >20% bloat affecting performance (tables >100MB)
- Missing Statistics - Tables never analyzed, leaving the query planner without statistics
- Duplicate Indexes - Multiple indexes with identical or overlapping column sets
- Inactive Replication Slots - Identifies replication slots that are inactive and can be removed if no longer needed
- Tables Larger Than 100GB - Identifies tables that are larger than 100GB
- Tables With More Than 200 Columns - List tables with more than 200 columns. You should probably look into those...
- Outdated Statistics - Table statistics older than 7 days with significant modifications
- Low Index Efficiency - Indexes with poor selectivity (scan-to-tuple ratio >1000)
- Excessive Sequential Scans - Tables with high sequential scan activity that may benefit from indexes
- High Connection Count - More than 50 active connections potentially impacting performance
- Replication Slots With High WAL Retention - Replication slots that have 90% of max wal setting
- Long Running Queries - Queries that have been running for 5 minutes or more
- Blocked and Blocking Queries - Queries that are currently blocked or blocking other queries at the time you run pg_firstAid
- Tables With More Than 50 Columns - List tables with more than 50 columns (but less than 200)
- Tables Larger Than 50GB - Identifies tables larger than 50GB (but less than 100GB)
- Missing Foreign Key Indexes - Foreign key constraints without supporting indexes for efficient joins
- Idle Connections For More Than 1 Hour - Grabs connections that have been open and idle for more than 1 hour
- Tables With Zero or Only One Column - Identifies tables with one or zero columns
- True Empty Table(s) in Database - Searches for truly empty tables in the database. Checks if there are rows present and the last time vacuum and analyze was ran against the identified table
- Tables With No Recent Activity - Checks for zero activity since the last stats reset. This check works for all versions of Postgres. In 16+, we could use
last_*_timestampcolumns which could tell you WHEN the last activity was as well. However, this would break compatibility for anything older than 16. - Indexes With Low Usage - Flags indxes with 1MB with 1-99 scans. Zero scans are already caught by the CRITICAL unused indexes check.
- Roles That Have Never Logged In - Excludes system role and managed services roles. This includes users with
LOGINrights.
- Database Size - Current database size and growth monitoring
- PostgreSQL Version - Version information and configuration details
- Installed Extensions - Lists installed extensions on the Server
- Server Uptime - Server uptime since last restart
- Log Directory - Location of Log File(s). Results will vary for managed services like AWS RDS. (note: need access to AWS/Azure/GCP environments where I can test against!)
- Log File Sizes - The size of the log files. Again, this will vary for managed services.
-- Show only critical issues
SELECT * FROM pg_firstAid() WHERE severity = 'CRITICAL';
SELECT * FROM v_pgfirstAid WHERE severity = 'MEDIUM';
-- Show critical and high priority issues
SELECT * FROM pg_firstAid() WHERE severity IN ('CRITICAL', 'HIGH');
SELECT * FROM v_pgfirstAid WHERE severity IN ('CRITICAL', 'HIGH');- Table Health
- Query Health
- Replication Health
- System Health
- Database Health
-- Check table health
SELECT * FROM v_pgfirstAid WHERE category = 'Table Health';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 modifications or data migrations
- Performance Troubleshooting - First step when investigating slow queries or system issues
- Capacity Planning - Regular monitoring to track database growth trends
Read Before Acting
- Always review recommendations carefully before making changes. I have been supporting Postgres databases for close to a decade, but I learn something new each day
- Test in a non-production environment first
- Some operations (like VACUUM FULL) require maintenance windows
- Never drop an index without validating its usage patterns over time
Permissions
- Requires read access to system catalogs (
pg_catalog) - Works with standard user permissions for most checks
- Some checks may return fewer results for non-superuser accounts
pgFirstAid is designed to be lightweight and safe 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 testing on 15+. This will change as versions are deprecated
- PostgreSQL 9.x - Most features work (minor syntax adjustments may be needed)
- Works with all PostgreSQL-compatible databases (Amazon RDS, Aurora, Azure Database, etc.)
Found a bug? Have an idea for a new health check? Let me know! I want this to grow to be a tool that is available for all, accidental 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
Made with ☕ for the PostgreSQL and Open Source community
