Server-side flight recorder for PostgreSQL performance monitoring

# pgfr_record

Core flight recorder extension for PostgreSQL. Continuously samples database state in the background so you can answer "what was happening in my database?" after the fact.

## What it does

pgfr_record installs a set of tables, views, and pg_cron jobs that continuously capture PostgreSQL system state. It uses UNLOGGED ring buffers for high-frequency sampling of wait events, active sessions, and locks, and durable snapshot tables for periodic capture of WAL activity, checkpoints, I/O, table and index stats, query stats, replication state, and configuration. Data flows from ring buffers into archives and aggregates for longer retention.

## Key features

- **Continuous background sampling** via pg_cron -- no external agents or sidecars
- **Ring buffers** (UNLOGGED) for real-time wait events, active sessions, and lock contention
- **Durable snapshots** every minute: WAL, checkpoints, I/O, tables, indexes, statements, replication, configuration
- **Aggregates and archives** for longer retention (7 days for archives, 30 days for snapshots)
- **Safety mechanisms**: circuit breaker, load shedding
- **Collection modes**: normal, light, emergency, kill
- **Configurable profiles**: default, production_safe, development, troubleshooting, minimal_overhead
- **Delta views**: snapshot-over-snapshot changes for trend analysis

## Requirements

- PostgreSQL 15, 16, or 17
- `pg_cron` extension
- Superuser privileges for installation
- Optional: `pg_stat_statements` for query-level analysis

## Install

```sql
\i _record/install.sql
SELECT pgfr_record.enable();
```

Or from the command line:

```bash
psql --single-transaction -f _record/install.sql
psql -c "SELECT pgfr_record.enable();"
```

## Quick start

```sql
-- Check health
SELECT * FROM pgfr_record.health_check();

-- View recent wait events
SELECT * FROM pgfr_record.recent_waits;

-- View recent active sessions
SELECT * FROM pgfr_record.recent_activity;

-- View recent lock contention
SELECT * FROM pgfr_record.recent_locks;

-- Snapshot-over-snapshot deltas
SELECT * FROM pgfr_record.deltas;
```

## Key views

| View                              | Description                      |
|-----------------------------------|----------------------------------|
| `pgfr_record.deltas`                     | Snapshot-over-snapshot changes   |
| `pgfr_record.recent_waits`               | Wait events from ring buffer     |
| `pgfr_record.recent_activity`            | Active sessions from ring buffer |
| `pgfr_record.recent_locks`               | Lock contention from ring buffer |
| `pgfr_record.recent_idle_in_transaction` | Idle-in-transaction sessions     |
| `pgfr_record.recent_replication`         | Replication status               |
| `pgfr_record.recent_vacuum_progress`     | Vacuum operations in progress    |
| `pgfr_record.archiver_status`            | WAL archiving status             |

## Key functions

| Function                        | Description                   |
|---------------------------------|-------------------------------|
| `pgfr_record.enable()`                 | Start collection jobs         |
| `pgfr_record.disable()`                | Stop collection jobs          |
| `pgfr_record.health_check()`           | System health status          |
| `pgfr_record.set_mode(mode)`           | Set collection mode           |
| `pgfr_record.apply_profile(name)`      | Apply a configuration profile |
| `pgfr_record.list_profiles()`          | List available profiles       |
| `pgfr_record.ring_buffer_health()`     | Ring buffer status            |
| `pgfr_record.cleanup()`                | Manual retention cleanup      |

## Profiles

| Profile            | Sample Interval | Use Case                               |
|--------------------|-----------------|----------------------------------------|
| `default`          | 60s             | General purpose monitoring             |
| `production_safe`  | 300s            | Production with maximum safety margins |
| `development`      | 60s             | Staging and development                |
| `troubleshooting`  | 60s             | Active incident response               |
| `minimal_overhead` | 300s            | Resource-constrained systems           |

## Related extensions

- [pgfr_analyze](https://database.dev/dventimi/pgfr_analyze) -- reporting, anomaly detection, time-travel forensics
- [pgfr_control](https://database.dev/dventimi/pgfr_control) -- vacuum diagnostics, scale factor tuning, bloat analysis

See the [top-level README](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/README.md) and [REFERENCE.md](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/REFERENCE.md) for full documentation.


# pg_flight_recorder

Server-side flight recorder for PostgreSQL. Answers "what was happening in my database?"

## Install

Requires PostgreSQL 15+ with pg_cron.

Download from [GitHub Releases](https://github.com/dventimisupabase/pg-flight-recorder/releases/latest) or clone the repo, then:

```bash
# Core (tables, collection, scheduling)
psql --single-transaction -f install.sql
```

```bash
# Control (optional — vacuum diagnostics, scale factor tuning, bloat analysis)
psql --single-transaction -f autovacuum_control.sql
```

```bash
# Reporting & analysis (optional)
psql --single-transaction -f reporting.sql
```

## Use

```sql
SELECT flight_recorder_reporting.report('1 hour');
```

That's it. It runs automatically. The report tells you what happened.

## Uninstall

```bash
# Remove everything (destructive)
psql --single-transaction -f uninstall.sql
```

```bash
# Remove only control functions (keeps core + data)
psql --single-transaction -f uninstall_autovacuum_control.sql
```

```bash
# Remove only reporting functions (keeps core + data)
psql --single-transaction -f uninstall_reporting.sql
```

## Export

With default retention: ~2.5GB uncompressed, ~150MB compressed.

```bash
# Without compression
pg_dump -d your_database -n flight_recorder --data-only -f flight_recorder_data.sql
```

```bash
# With compression (PostgreSQL 16+)
pg_dump -d your_database -n flight_recorder --data-only --compress=gzip:9 -f flight_recorder_data.sql.gz
```

```bash
# With compression (PostgreSQL 15)
pg_dump -d your_database -n flight_recorder --data-only | gzip > flight_recorder_data.sql.gz
```

## Reference

See [REFERENCE.md](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/REFERENCE.md) for configuration, functions, and details.


Reporting and analysis functions for pgfr_record

# pgfr_analyze

Reporting and analysis extension for [pgfr_record](https://database.dev/dventimi/pgfr_record). Turns raw flight recorder data into anomaly reports, incident forensics, and capacity planning.

## What it does

pgfr_analyze reads the snapshot and ring buffer data collected by pgfr_record and provides functions for anomaly detection, performance regression analysis, time-travel forensics, blast radius analysis, capacity planning, and configuration change tracking. It never writes to the core schema -- it only reads and computes.

## Key features

- **Anomaly detection**: checkpoint anomalies, buffer pressure, temp file spills, lock contention, XID wraparound risk
- **Query storm and regression detection**: find abnormal query patterns and performance regressions with severity classification
- **Time-travel forensics**: `what_happened_at()` for point-in-time analysis, `incident_timeline()` for event reconstruction
- **Blast radius analysis**: measure the impact of high-cost queries on system resources
- **Capacity planning**: `capacity_summary()`, `quarterly_review()`, and the `capacity_dashboard` view
- **Configuration tracking**: detect PostgreSQL config changes, view config at a point in time, health check recommendations
- **Comprehensive reporting**: `report()` for full diagnostics, `summary_report()`, `performance_report()`

## Requirements

- [pgfr_record](https://database.dev/dventimi/pgfr_record) must be installed first
- Optional: `pg_stat_statements` for query-level analysis

## Install

```sql
-- Install core first if not already installed
\i _record/install.sql
SELECT pgfr_record.enable();

-- Then install analyze
\i _analyze/install.sql
```

## Quick start

```sql
-- Compare two snapshots
SELECT * FROM pgfr_analyze.compare(now() - '1 hour', now());

-- Wait event summary over a time range
SELECT * FROM pgfr_analyze.wait_summary(now() - '1 hour', now());

-- Generate a diagnostic report for the last hour
SELECT pgfr_analyze.report('1 hour');

-- Anomaly report over a time range
SELECT * FROM pgfr_analyze.anomaly_report(now() - '1 hour', now());

-- What was happening at a specific time?
SELECT * FROM pgfr_analyze.what_happened_at('2024-01-15 14:32');

-- Reconstruct an incident timeline
SELECT * FROM pgfr_analyze.incident_timeline(
    '2024-01-15 14:00'::timestamptz,
    '2024-01-15 15:00'::timestamptz
);

-- Detect performance regressions
SELECT * FROM pgfr_analyze.detect_regressions('1 day');

-- Detect query storms
SELECT * FROM pgfr_analyze.detect_query_storms('1 hour');

-- Capacity summary
SELECT * FROM pgfr_analyze.capacity_summary('7 days');
```

## Functions

### Comparison and analysis

| Function                                         | Description                                |
|--------------------------------------------------|--------------------------------------------|
| `compare(start, end)`                            | Compare two snapshots side-by-side         |
| `wait_summary(start, end)`                       | Wait event breakdown over a time range     |
| `statement_compare(start, end)`                  | Query performance changes between points   |
| `activity_at(timestamp)`                         | Activity snapshot closest to a timestamp   |
| `recent_waits_current()`                         | Current wait event data from ring buffer   |
| `recent_activity_current()`                      | Current activity data from ring buffer     |
| `recent_locks_current()`                         | Current lock data from ring buffer         |

### Reporting

| Function                         | Description                      |
|----------------------------------|----------------------------------|
| `report(interval)`               | Comprehensive diagnostic report  |
| `report(start, end)`             | Report for a specific time range |
| `summary_report(start, end)`     | Summary statistics               |
| `performance_report(start, end)` | Performance-focused report       |
| `anomaly_report(start, end)`     | Detailed anomaly analysis        |
| `check_alerts()`                 | Check active alert conditions    |

### Forensics

| Function                        | Description                        |
|---------------------------------|------------------------------------|
| `what_happened_at(timestamp)`   | Point-in-time analysis             |
| `incident_timeline(start, end)` | Reconstruct event timeline         |
| `blast_radius(queryid)`         | Measure impact of a specific query |
| `blast_radius_report(interval)` | Report on high-impact queries      |

### Performance analysis

| Function                           | Description                          |
|------------------------------------|--------------------------------------|
| `detect_query_storms(interval)`    | Find abnormal query patterns         |
| `detect_regressions(interval)`     | Find performance regressions         |
| `table_hotspots(start, end)`       | Tables with high activity            |
| `table_compare(start, end)`        | Table stats changes over time        |
| `index_efficiency(start, end)`     | Index usage analysis                 |
| `unused_indexes(interval)`         | Indexes with no scans                |

### Capacity planning

| Function                           | Description                          |
|------------------------------------|--------------------------------------|
| `capacity_summary(interval)`       | Resource utilization summary         |
| `capacity_report(interval)`        | Text capacity report                 |
| `quarterly_review()`               | Comprehensive capacity review        |
| `capacity_dashboard` (view)        | Resource utilization overview        |

### Configuration tracking

| Function                             | Description                   |
|--------------------------------------|-------------------------------|
| `config_changes(start, end)`         | PostgreSQL config changes     |
| `config_at(timestamp)`               | Config at a point in time     |
| `config_health_check()`              | Configuration recommendations |
| `db_role_config_changes(start, end)` | Database/role config changes  |
| `db_role_config_summary()`           | Current db/role overrides     |

### Pre-flight

| Function                           | Description                          |
|------------------------------------|--------------------------------------|
| `preflight_check()`                | Pre-installation validation          |
| `preflight_check_with_summary()`   | Validation with text summary         |

## Related extensions

- [pgfr_record](https://database.dev/dventimi/pgfr_record) -- core snapshot collection (required)
- [pgfr_control](https://database.dev/dventimi/pgfr_control) -- vacuum diagnostics, scale factor tuning, bloat analysis

See the [top-level README](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/README.md) and [REFERENCE.md](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/REFERENCE.md) for full documentation.


Reporting and analysis functions for pg_flight_recorder

# pg_flight_recorder_reporting

Reporting and analysis functions for [pg_flight_recorder](https://database.dev/dventimi/pg_flight_recorder).

Requires `pg_flight_recorder` core to be installed first.


# pgfr_control

Vacuum control and bloat analysis extension for [pgfr_record](https://database.dev/dventimi/pgfr_record). Provides closed-loop vacuum diagnostics, autovacuum scale factor recommendations, and table bloat estimation.

## What it does

pgfr_control reads the table snapshot data collected by pgfr_record and computes dead tuple growth rates, vacuum operating modes, and recommended autovacuum scale factors. It classifies vacuum health, estimates table bloat without requiring pgstattuple, and monitors OID consumption. It is non-invasive: it reads snapshots, computes recommendations, and reports -- it never auto-applies changes.

## Key features

- **Closed-loop vacuum control** with state machine: normal, catch_up, and safety modes based on XID age and dead tuple trends
- **Dead tuple growth rate and trend analysis** via linear regression over snapshot history
- **Autovacuum scale factor recommendations** computed from actual dead tuple accumulation rates
- **Vacuum diagnostics**: classifies tables as NOT_SCHEDULED, RUNNING_BUT_LOSING, BLOCKED, or HEALTHY with actionable guidance
- **Table bloat estimation** without pgstattuple -- uses dead tuple ratio and size metrics
- **OID consumption rate** and exhaustion time estimation
- **Table size growth rate** tracking for detecting bloat accumulation
- **Non-invasive**: reads snapshots and recommends changes, never auto-applies

## Requirements

- [pgfr_record](https://database.dev/dventimi/pgfr_record) must be installed first

## Install

```sql
-- Install core first if not already installed
\i _record/install.sql
SELECT pgfr_record.enable();

-- Then install control
\i _control/install.sql
```

## Quick start

```sql
-- Check vacuum mode for a table
SELECT * FROM pgfr_control.vacuum_control_mode('my_table'::regclass);

-- Get vacuum diagnostic for a table
SELECT * FROM pgfr_control.vacuum_diagnostic('my_table'::regclass);

-- Get recommended autovacuum scale factor
SELECT * FROM pgfr_control.compute_recommended_scale_factor('my_table'::regclass);

-- Full vacuum control report
SELECT * FROM pgfr_control.vacuum_control_report(now() - '1 hour', now());

-- Bloat report with trends
SELECT * FROM pgfr_control.bloat_report('24 hours');

-- Check OID exhaustion timeline
SELECT pgfr_control.time_to_oid_exhaustion();
```

## Functions

### Vacuum control

| Function                                | Description                                       |
|-----------------------------------------|---------------------------------------------------|
| `vacuum_control_mode(oid)`              | Determine operating mode (normal/catch_up/safety) |
| `compute_recommended_scale_factor(oid)` | Recommend autovacuum scale factor                 |
| `vacuum_diagnostic(oid)`                | Classify vacuum health with actionable guidance   |
| `vacuum_control_report(start, end)`     | Vacuum control recommendations for all tables     |

### Dead tuple analysis

| Function                                 | Description                                         |
|------------------------------------------|-----------------------------------------------------|
| `dead_tuple_growth_rate(oid, interval)`  | Dead tuple accumulation rate (tuples/second)        |
| `dead_tuple_trend(oid, interval)`        | Dead tuple trend via linear regression              |
| `time_to_budget_exhaustion(oid, budget)` | Estimate time until autovacuum threshold is reached |

### Bloat estimation

| Function                        | Description                                          |
|---------------------------------|------------------------------------------------------|
| `estimate_table_bloat(oid)`     | Estimate table bloat without pgstattuple             |
| `bloat_report(interval)`        | Bloat report with size trends and recommendations    |
| `table_size_growth_rate(oid, interval)` | Table size growth rate (bytes/second)        |

### OID monitoring

| Function                        | Description                          |
|---------------------------------|--------------------------------------|
| `oid_consumption_rate(interval)`| OID usage rate (OIDs/second)         |
| `time_to_oid_exhaustion()`      | Estimate time until OID exhaustion   |

## Vacuum diagnostic modes

| Diagnostic          | Meaning                                                      |
|---------------------|--------------------------------------------------------------|
| `NOT_SCHEDULED`     | Autovacuum hasn't run -- scale factor may be too high        |
| `RUNNING_BUT_LOSING`| Vacuum runs but dead tuples grow faster than cleanup         |
| `BLOCKED`           | Vacuum is blocked by long-running transactions or locks      |
| `HEALTHY`           | Vacuum is keeping up with dead tuple accumulation            |

## Related extensions

- [pgfr_record](https://database.dev/dventimi/pgfr_record) -- core snapshot collection (required)
- [pgfr_analyze](https://database.dev/dventimi/pgfr_analyze) -- reporting, anomaly detection, time-travel forensics

See the [top-level README](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/README.md) and [REFERENCE.md](https://github.com/dventimisupabase/pg-flight-recorder/blob/main/REFERENCE.md) for full documentation.


# pg_flight_recorder_control

Control functions for [pg_flight_recorder](https://database.dev/dventimi/pg_flight_recorder).

Requires `pg_flight_recorder` core to be installed first.


dventimi

Packages