What is this issue?
Replication slots track an xmin value representing the oldest transaction
the slot might still need. PostgreSQL cannot vacuum away rows that might be
visible to this transaction.
An old xmin (> 50 million transaction age) indicates:
- The slot consumer isn't keeping up
- A logical replication subscriber is lagging
- The slot might be abandoned
This is different from replication lag (bytes behind) - xmin age affects
VACUUM's ability to clean up the entire database.
Why it matters
Global Bloat
Affects all tables, not just replicated ones
Vacuum Ineffective
VACUUM runs but can't remove dead tuples
XID Pressure
Contributes to XID age accumulation
Disk Growth
Database size grows without bound
How PG Pilot detects it
```sql SELECT slot_name, slot_type, database, active, xmin, age(xmin) AS xmin_age, catalog_xmin, age(catalog_xmin) AS catalog_xmin_age FROM pg_replication_slots WHERE age(xmin) > 50000000 OR age(catalog_xmin) > 50000000 ORDER BY age(xmin) DESC NULLS LAST; ```
How to fix it
Identify the slot consumer
Determine what's using this slot:
SELECT slot_name, slot_type, plugin, database
FROM pg_replication_slots
WHERE slot_name = 'your_slot';- Physical slots: Streaming replication standby
- Logical slots: Logical replication or CDC tools
Check consumer status
For physical replication:
SELECT FROM pg_stat_replication;For logical replication:
SELECT FROM pg_stat_subscription; -- on subscriberHelp the consumer catch up
- Ensure the consumer is running and healthy
- Check network connectivity
- Verify consumer has sufficient resources
- For logical replication, check for errors in subscriber logs
Drop if abandoned
If the slot consumer is permanently gone:
SELECT pg_drop_replication_slot('slot_name');Warning: This may cause data loss if the consumer returns.
Prevention
- Monitor slot xmin age with PG Pilot
- Ensure consumers keep up with replication
- Set up alerts before xmin age becomes critical
- Document all replication slots and their purposes