I'm a postgres dev, so I'm surely too close to actually see what's nebulous. That said, I don't think it's that nebulous:
- data in WAL segments has to be checkpointed
- no replication slot, physical or logical, may require the WAL file (see the pg_replication_slots view)
- archiving, if configured, has to have archived the file (see pg_stat_archiver)
It used to be more complicated, for historical reasons we used to keep two checkpoints worth of WAL around, but I don't think any supported versions of postgres still have that behavior.
Edit:
What's more mysterious is whether WAL files are removed when not necessary, or whether they're recycled (renamed to be reused). That's indeed a bit hard to get insight to.
- data in WAL segments has to be checkpointed
- no replication slot, physical or logical, may require the WAL file (see the pg_replication_slots view)
- archiving, if configured, has to have archived the file (see pg_stat_archiver)
It used to be more complicated, for historical reasons we used to keep two checkpoints worth of WAL around, but I don't think any supported versions of postgres still have that behavior.
Edit:
What's more mysterious is whether WAL files are removed when not necessary, or whether they're recycled (renamed to be reused). That's indeed a bit hard to get insight to.