Skip to content
Back to search

pgFirstAid

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

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today

Check it deeper on your machine

It has changed since its scan on 11 Nov 2026.

As a Claude Code plugin, install it and then ask Claude Code:

/plugin marketplace add keithmackay/mackayi/plugin install scanskillsafety@mackayiDo a deep scan of https://github.com/randoneering/pgFirstAid with scanskillsafety

Or from a terminal, clone it and run it:

git clone https://github.com/keithmackay/scanskillsafety-skill ~/.claude/skills/scanskillsafetynode ~/.claude/skills/scanskillsafety/cli.cjs --deep 'https://github.com/randoneering/pgFirstAid'

It checks the exact commit: every file, its packages against OSV (api.osv.dev), how it compares with this page's scan (findsafeskills.com receives the repository's address), and, if you agree, Claude reading every file with your own Claude subscription. It never runs the code.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/randoneering--pgfirstaid/badge.svg)](/listings/randoneering--pgfirstaid)

HTML

<a href="/listings/randoneering--pgfirstaid"><img src="/api/listings/randoneering--pgfirstaid/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

Installation

-- 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 privileges (in your environment) to avoid issues.

Example Output

| 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/... |

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 233 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. <p align="center">
  2. <img alt="pgFirstAid Logo" src="docs/images/logo-transparent.png" width="500">
  3. </p>
  4. # pgFirstAid
  5. 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](https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit) for SQL Server, **pgFirstAid** is designed for everyone to use - not just DBAs! Get actionable health insights from your PostgreSQL database in seconds.
  6. ## Quick Thank You To Our Sponsors
  7. This project is supported by:
  8. <a href="https://get.neon.com/5HwTAdh">
  9. <picture>
  10. <source media="(prefers-color-scheme: dark)" srcset="docs/images/neon-logo-dark-color.png">
  11. <source media="(prefers-color-scheme: light)" srcset="docs/images/neon-logo-light-color.png">
  12. <img alt="Neon Sponsorship" src="docs/images/neon-logo-light-color.png" width="200">
  13. </picture>
  14. </a>
  15. 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.
  16. <a href="https://osuosl.org/">
  17. <picture>
  18. <source media="(prefers-color-scheme: dark)" srcset="docs/images/osllogo-white_dark.png">
  19. <source media="(prefers-color-scheme: light)" srcset="docs/images/osllogo-web_light.png">
  20. <img alt="OSU Sponsorship" src="docs/images/osllogo-web_light.png" width="200">
  21. </picture>
  22. </a>
  23. 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.
  24. ## Features
  25. - **Zero Dependencies** - Single SQL function, no external tools required
  26. - **Detailed Checks** - built-in health checks covering critical performance and stability issues
  27. - **Prioritized Results** - Issues ranked by severity (CRITICAL → HIGH → MEDIUM → LOW → INFO)
  28. - **Actionable Recommendations** - Each issue includes specific remediation steps
  29. - **Copyable Agent Prompts** - Every non-INFO finding includes check-specific guidance with its actual objects and observed values
  30. - **Documentation Links** - Direct links to official PostgreSQL documentation for deeper learning
  31. - **Optional `pg_stat_statements` Checks** - Runs additional query workload checks when `pg_stat_statements` is installed
  32. ## Quick Start
  33. ### Install or upgrade
  34. Run the complete matching SQL file as the installation owner or a database administrator.
  35. Use the same file for new installations and upgrades; no manual `DROP` is needed.
  36. | Installation | SQL file | Query afterward |
  37. |---|---|---|
  38. | Function | [`pgFirstAid.sql`](pgFirstAid.sql) | `SELECT * FROM pg_firstAid();` |
  39. | Self-hosted view | [`view_pgFirstAid.sql`](view_pgFirstAid.sql) | `SELECT * FROM v_pgfirstaid;` |
  40. | Managed view | [`view_pgFirstAid_managed.sql`](view_pgFirstAid_managed.sql) | `SELECT * FROM v_pgfirstaid;` |
  41. In a SQL editor, execute the entire file as a script with stop-on-error enabled.
  42. For the function, the equivalent psql command is:
  43. ```bash
  44. psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 --single-transaction -f pgFirstAid.sql
  45. ```
  46. The installer preserves existing ownership, grants, and comments.
  47. Unsupported custom definitions or blocking dependencies stop safely instead of being discarded.
  48. See the [short upgrade guide](docs/health-checks/agent-prompts.md#upgrade-an-existing-installation) for view commands and troubleshooting.
  49. ### Example Output
  50. | severity | category | check_name | object_name | issue_description | current_value | recommended_action | documentation_link |
  51. |----------|----------|------------|-------------|-------------------|---------------|-------------------|-------------------|
  52. | 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/... |
  53. | HIGH | Table Health | Missing Statistics | public.orders | Table has never been analyzed... | Last analyze: Never | Run ANALYZE on this table... | https://www.postgresql.org/... |
  54. The `agent_prompt` column is omitted from this compact example.
  55. View results also include `severity_order` before `agent_prompt`.
  56. ## What Does It Check?
  57. See the full list of checks, grouped by severity, in [`docs/health-checks/README.md`](docs/health-checks/README.md).
  58. ## Copy a Remediation Prompt
  59. Each non-INFO result includes an `agent_prompt` tailored to that finding's affected objects and measured values.
  60. Copy the complete cell into your agent to start with read-only diagnosis and an approval-gated remediation plan.
  61. ```sql
  62. SELECT severity, check_name, object_name, agent_prompt
  63. FROM pg_firstAid()
  64. WHERE severity <> 'INFO';
  65. ```
  66. Use `FROM v_pgfirstaid` for either view installation.
  67. INFO rows return `NULL` for `agent_prompt`.
  68. Review prompts for sensitive object names or captured SQL before sharing them externally.
  69. See [agent prompts and upgrades](docs/health-checks/agent-prompts.md) for details.
  70. ## Usage Tips
  71. ### Filter by Severity
  72. ```sql
  73. -- Show only critical issues
  74. SELECT * FROM pg_firstAid() WHERE severity = 'CRITICAL';
  75. SELECT * FROM v_pgfirstAid WHERE severity = 'MEDIUM';
  76. -- Show critical and high priority issues
  77. SELECT * FROM pg_firstAid() WHERE severity IN ('CRITICAL', 'HIGH');
  78. SELECT * FROM v_pgfirstAid WHERE severity IN ('CRITICAL', 'HIGH');
  79. ```
  80. ### Filter by Category
  81. 1. Table Health
  82. 2. Query Health
  83. 3. Replication Health
  84. 4. System Health
  85. 5. Database Health
  86. ```sql
  87. -- Check table health
  88. SELECT * FROM v_pgfirstAid WHERE category = 'Table Health';
  89. ```
  90. ### Count Issues by Severity
  91. ```sql
  92. SELECT severity, COUNT(*) as issue_count
  93. FROM pg_firstAid()
  94. GROUP BY severity
  95. ORDER BY MIN(CASE severity
  96. WHEN 'CRITICAL' THEN 1
  97. WHEN 'HIGH' THEN 2
  98. WHEN 'MEDIUM' THEN 3
  99. WHEN 'LOW' THEN 4
  100. ELSE 5 END);
  101. ```
  102. ## When to Run
  103. - **Daily** - Quick health check as part of morning routine
  104. - **Before Deployment** - Catch potential issues before they impact production
  105. - **After Major Changes** - Verify database health after schema modifications or data migrations
  106. - **Performance Troubleshooting** - First step when investigating slow queries or system issues
  107. - **Capacity Planning** - Regular monitoring to track database growth trends
  108. ## Important Notes
  109. **Read Before Acting**
  110. - 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
  111. - Test in a non-production environment first
  112. - Some operations (like VACUUM FULL) require maintenance windows
  113. - Never drop an index without validating its usage patterns over time
  114. **Permissions**
  115. - Requires read access to system catalogs (`pg_catalog`)
  116. - Works with standard user permissions for most checks
  117. - Some checks may return fewer results for non-superuser accounts
  118. **Optional: Enable pg_stat_statements For Deeper Query Checks**
  119. - pgFirstAid keeps running without this extension and reports a setup action when it is missing.
  120. - Self-hosted PostgreSQL: set `shared_preload_libraries = 'pg_stat_statements'`, restart PostgreSQL, then run `CREATE EXTENSION pg_stat_statements;`
  121. - AWS RDS for PostgreSQL: add `pg_stat_statements` to the DB parameter group `shared_preload_libraries`, reboot, then run `CREATE EXTENSION pg_stat_statements;`
  122. - GCP Cloud SQL for PostgreSQL: enable `cloudsql.enable_pg_stat_statements`, restart if required, then run `CREATE EXTENSION pg_stat_statements;`
  123. - Azure Database for PostgreSQL: add `pg_stat_statements` to `shared_preload_libraries`, restart, then run `CREATE EXTENSION pg_stat_statements;`
  124. ## Performance Impact
  125. pgFirstAid is designed to be lightweight and safe to run on production systems:
  126. - Read-only operations (no modifications to your data or schema)
  127. - Uses system catalog views that are already cached
  128. - Typical execution time: <1 second on most databases
  129. - No locking or blocking of user queries
  130. ## CI/CD Integration
  131. Drop-in GitHub Actions workflows in [`workflows/`](workflows/) for integrating pgFirstAid into your pipeline:
  132. | Workflow | Purpose |
  133. |----------|---------|
  134. | [`pgfirstaid-pr-audit.yml`](workflows/pgfirstaid-pr-audit.yml) | Posts a health audit on every PR |
  135. | [`pre-post-migration-validate.yml`](workflows/pre-post-migration-validate.yml) | Gates deployments on migration safety |
  136. | [`neon-before-after-validate.yml`](workflows/neon-before-after-validate.yml) | Isolated before/after checks via Neon branching |
  137. | [`db-health-checks.yml`](workflows/db-health-checks.yml) | Scheduled daily health monitoring |
  138. | [`managed-db-validate.yml`](workflows/managed-db-validate.yml) | Cloud-specific compatibility validation |
  139. Copy any workflow file to `.github/workflows/` in your repo. See [`workflows/README.md`](workflows/README.md) for setup and configuration.
  140. ## Testing
  141. - Query and health-check coverage is validated with pgTAP assertions grouped by severity.
  142. - Integration tests cover live runtime behavior, function/view parity, and checks that need concurrent sessions or timing control.
  143. - A coverage guard ensures every `check_name` in `pgFirstAid.sql` is referenced by at least one pgTAP assertion.
  144. - The GitHub Actions validation workflows also run `testing/seed_and_validate.py --managed` against live database targets to confirm the seeded checks actually fire end-to-end.
  145. - Managed database validation is exercised through the reusable workflow in `.github/workflows/managed-db-validate.yml`.
  146. - Function-upgrade tests cover the normal SQL file on fresh and existing installations, including grant preservation, dependency protection, and rollback.
  147. > **Important:** We currently validate managed-database testing against AWS, but we do not have the funding or credits needed to keep Azure and GCP test environments running. If you have access to Azure Database for PostgreSQL or GCP Cloud SQL and want to help validate pgFirstAid there, we would be happy to have the help.
  148. ## Compatibility
  149. - **PostgreSQL 10+** - Supported, with active automated validation focused on PostgreSQL 15-18
  150. - **PostgreSQL 9.x** - Most features work (minor syntax adjustments may be needed)
  151. - Works with PostgreSQL-compatible databases, including Amazon RDS, Aurora, Azure Database for PostgreSQL, GCP Cloud SQL, and self-hosted PostgreSQL
  152. - Automated managed-database validation is active for AWS today. Azure and GCP support is best-effort until we can fund those test environments.
  153. ## Contributing
  154. Prompt and function/view migration helpers each have one maintained source under `data/`.
  155. Edit the canonical helper, then run `uv run python tools/sync_sql_helpers.py` to refresh the standalone installers.
  156. PR Safe Checks rejects embedded copies that differ from their canonical source.
  157. See [SQL helper generation](tools/README.md#edit-shared-sql-helpers) for details.
  158. 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.
  159. ## License
  160. GPLv3
  161. ## Credits
  162. Logo designed and created by: [@sheeryy.bsky.social](https://bsky.app/profile/sheeryy.bsky.social)
  163. Inspired by [Brent Ozar's FirstResponderKit](https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit)) for SQL Server. Thank you to the SQL Server community for pioneering accessible database health monitoring!
  164. Dave-IYKYK
  165. ---
  166. **Made with ☕ for the PostgreSQL and Open Source community**

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.