# pgFirstAid ranks your PostgreSQL problems and hands you a checklist

> pgFirstAid is a GPL-3.0 PostgreSQL function and view that inspects a running database and returns a severity-ranked list of stability and performance findings, each with a recommended action and a link to the official documentation. It is a single SQL definition with no external tooling, and it is read-only, so running it against production is the intended use.

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

- Repository: https://github.com/randoneering/pgFirstAid
- Website: https://pgfirstaid.com
- Stars: 538 · Forks: 7
- Language: PLpgSQL
- License: GPL-3.0
- Published: 2026-10-22 · Updated: 2026-10-22 · Language: en
- Canonical page: https://hysenlabs.com/projects/randoneering-pgfirstaid

## One function, no dependencies, no agent

pgFirstAid is an open source PostgreSQL function and view that provides a prioritized list of actions to take to improve stability and performance. The deployment model is the first thing to understand: it is a single SQL function, no external tools required, and the installation is to copy and paste the function or view definition into your database and then run it. That means there is no agent to install, no extension to build, no configuration file, and nothing running in the background between calls. The project is explicitly inspired by Brent Ozar's FirstResponderKit for SQL Server, and the design goal is stated as being for everyone to use rather than only for DBAs, which is a design brief about the shape of the output. The result is actionable health insights from a PostgreSQL database in seconds, and the site for the project is pgfirstaid.com.

## Running it for the first time

The quick start is deliberately short. After pasting the definition in, there are two ways to call it, a function that you execute and a view that you select from:

```sql
SELECT * FROM pg_firstAid();
```

```sql
SELECT * FROM v_pgfirstAid;
```

The README recommends deploying as a user with the highest possible privileges in your environment to avoid issues, and the permissions section spells out what that buys you. It requires read access to the system catalogs, works with standard user permissions for most checks, and some checks may return fewer results for non-superuser accounts. The two access paths matter more than they look: a function call takes the snapshot at the moment you call it, while the view re-evaluates as you query it, so a dashboard built on the view will keep moving and a one-off snapshot will not. The view_pgFirstAid_managed.sql file at the repository root suggests a third option for environments where the definition is managed centrally.

## Severity ranking and what a row actually contains

The result set is a table with a fixed shape, and the columns are the contract. There is severity, category, check_name, object_name, issue_description, current_value, recommended_action and documentation_link, and the example output in the README shows a CRITICAL row for a missing primary key on public.users with the current value reported as no primary key defined and the action as add a primary key or unique constraint, and a HIGH row for missing statistics on public.orders reporting the last analyze as Never and recommending an ANALYZE. The severity scale runs from CRITICAL through HIGH, MEDIUM and LOW to INFO, so the ranking is a first-class part of the output rather than something you have to compute from the rows. Each finding also carries a link to official PostgreSQL documentation, which means the report doubles as a reading list for someone who has to justify the fix.

## Filtering and counting the findings

Because the result is a normal relation, you filter it like any other. Selecting only critical issues from the function, or medium issues from the view, or a severity set from either:

```sql
SELECT * FROM pg_firstAid() WHERE severity IN ('CRITICAL', 'HIGH');
```

Filtering by category works the same way, for example to list everything under Table Health:

```sql
SELECT * FROM v_pgfirstAid WHERE category = 'Table Health';
```

And counting issues by severity needs a deliberate ordering, because a plain ORDER BY on the severity text would sort alphabetically and put CRITICAL in the wrong place. The documented approach is a CASE expression that maps each severity to a rank number, then groups and orders by the minimum of that rank:

```sql
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);
```

The categories are Table Health, Query Health, Replication Health, System Health and Database Health, and the full list of individual checks grouped by severity lives in docs/health-checks/README.md.

## pg_stat_statements changes what you see

The tool has one optional dependency, and it is the one that matters for query work. When pg_stat_statements is installed, additional query workload checks run, covering what the database has actually been asked to do rather than what its configuration looks like. Without it, pgFirstAid keeps running and reports a setup action, so the absence is visible rather than silent, which is the right failure mode. Enabling it is a different procedure per platform, and the README gives the exact steps for each. On self-hosted PostgreSQL you set shared_preload_libraries to 'pg_stat_statements', restart PostgreSQL, then run CREATE EXTENSION pg_stat_statements;. On AWS RDS for PostgreSQL you add it to the DB parameter group shared_preload_libraries, reboot, then run the same CREATE EXTENSION. On GCP Cloud SQL you enable cloudsql.enable_pg_stat_statements, restart if required, then create the extension. On Azure Database for PostgreSQL you add it to shared_preload_libraries, restart, then create the extension.

## Safe to run in production, with caveats

The performance section is the part a sceptical operator wants. The operations are read-only, with no modifications to your data or schema. It uses system catalog views that are already cached, so typical execution time is under one second on most databases, and there is no locking or blocking of user queries. The important notes are the ones the author writes in the first person, and they are worth reading as guidance rather than boilerplate. Always review recommendations carefully before making changes, with the note that the author has been supporting Postgres databases for close to a decade and still learns 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. The scheduling suggestions are Daily for a morning health check, before deployment, after major schema changes or data migrations, as a first step when investigating slow queries, and for capacity planning to track growth trends.

## CI/CD workflows and the surrounding repository

The project ships drop-in GitHub Actions workflows in a workflows/ directory, and two are named in the README table. pgfirstaid-pr-audit.yml posts a health audit on every pull request, and pre-post-migration-validate.yml is the companion for migrations, though the README truncates its description. The repository is broader than the SQL file. There is a data/ directory, a docs/ tree with the health-checks documentation, a roadmap/ directory, a skills/ directory, a testing/ directory, tools/, a pyproject.toml, a uv.lock and a .github directory. The Python side is only the test harness, with the project named pgfirstaid-testing at version 0.1.0, requiring Python 3.11 or newer, and depending on psycopg2-binary, pytest and pyyaml, with pytest configured to look in the testing directory. Recent releases are v2.2.1, v2.2.2 and v2.2.3 in July, August and September 2026, and the last push was on 2026-10-04, so the function is actively being extended.

## Where pgFirstAid is the wrong tool

Three cases. The first is a managed cloud instance where most findings are not actionable, since a missing primary key or a stale statistic on a table you do not own is a report you cannot act on, and a tool that returns hundreds of unfixable CRITICAL rows trains people to ignore it. The second is a team that needs diagnosis rather than a checklist, because the output is a list of findings with recommended actions, and a finding such as a table that has never been analyzed does not tell you which query is slow. The third is continuous monitoring, because the tool is a snapshot function or a live view, and there is no history, no trend and no alerting in the repository. Against running the standard statistics and catalog queries by hand, the difference in approach is that pgFirstAid encodes a curated set of checks with severity ranking and remediation text, which is what makes it usable by someone who is not a DBA, and what also means the checks are only as good as the person who wrote them.

## Conclusion

pgFirstAid is a good fit for a developer who needs a quick, opinionated read on a PostgreSQL instance they did not build, for a team that wants a health audit in CI, and for anyone who wants the checks to be a single read-only function they can run against production without installing an agent. It is a poor fit for a place where the recommendations are the problem rather than the diagnosis, since a table without a primary key may be a deliberate design choice, and it is a poor fit for a managed cloud instance where you cannot act on most of the findings. Check three things before you trust the output: that you run it as a user with the highest possible privileges in your environment, since the README says this avoids issues and some checks return fewer results for non-superuser accounts, that you read the recommendations before applying them, and that you enable pg_stat_statements if you want the query workload checks, since the tool runs without it and only reports a setup action when it is missing.

## FAQ

### How do I install and run pgFirstAid?

Copy and paste the function or view definition into your PostgreSQL database, then call it with SELECT * FROM pg_firstAid(); or select from the view with SELECT * FROM v_pgfirstAid;. No configuration is needed, and the README recommends deploying as a user with the highest possible privileges in your environment to avoid issues.

### Is pgFirstAid safe to run against a production database?

The operations are read-only with no modifications to your data or schema, it uses system catalog views that are already cached, typical execution time is under one second on most databases, and it does not lock or block user queries. The README still advises testing in a non-production environment first and reviewing each recommendation before acting.

### What does pgFirstAid need from pg_stat_statements?

It is optional, and it enables the additional query workload checks. Without the extension pgFirstAid keeps running and reports a setup action. Enabling it means adding pg_stat_statements to shared_preload_libraries, restarting PostgreSQL, and running CREATE EXTENSION pg_stat_statements;, with different steps for AWS RDS, GCP Cloud SQL and Azure Database for PostgreSQL.

### What columns does the pgFirstAid report have?

The report has severity, category, check_name, object_name, issue_description, current_value, recommended_action and documentation_link, with severity ranked from CRITICAL through HIGH, MEDIUM and LOW to INFO, and categories covering Table Health, Query Health, Replication Health, System Health and Database Health.

### Can I use pgFirstAid in a CI pipeline?

The repository ships drop-in GitHub Actions workflows in the workflows/ directory, including pgfirstaid-pr-audit.yml, which posts a health audit on every pull request, and pre-post-migration-validate.yml for migrations.

## Sources

- [License: GPL-3.0](https://github.com/randoneering/pgFirstAid/blob/main/LICENSE)
- [Project website](https://pgfirstaid.com)
- [randoneering/pgFirstAid on GitHub](https://github.com/randoneering/pgFirstAid)
- [README](https://github.com/randoneering/pgFirstAid/blob/main/README.md)
- [Releases](https://github.com/randoneering/pgFirstAid/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/randoneering-pgfirstaid