Databases
Database Publication Cleanup: Retire Logical Replication Feeds After Consumers Move
Database publication cleanup starts when logical replication feeds survive after CDC consumers, audit streams, or warehouse loaders move elsewhere. A quiet publication can still pin WAL, expose tables, or preserve a replay path, so the review has to separate retired feeds from low-frequency consumers.
For stale logical replication publications and subscriptions, cleanup should start with slot lag, subscription status, published tables, downstream jobs, and a tested recreate path. The useful output is a replication-feed retirement record with publication name, subscriber owner, lag evidence, table scope, staged disable, and recreate SQL: Pause or narrow subscriptions before dropping publication state, protect scheduled consumers, and make the restore or recreate path visible before final removal.
Key takeaways
- Review stale logical replication publications and subscriptions through Slot lag, Subscriber status, Published tables, not age alone.
- Use one replication and reporting cycle, including backfills and month-end loads, before deciding that quiet means unused.
- Start with the reversible move: pause or narrow subscriptions before dropping publication state.
- Slow down when losing replay position or continuing to expose tables after downstream consumers migrate is still plausible.
- Prevent repeat cleanup by making teams create publications with owner, consumer list, table scope, and review date.
Identify the Data Contract
Start with one database or schema area where publications, subscriptions, replication slots, and downstream owners can be seen together. The best cleanup scope is small enough that owners can answer quickly but wide enough to include backfills, audit exports, and warehouse jobs that make removal risky.
| Field | Why it matters |
|---|---|
| Owner | Cleanup needs a person or team that can accept the decision |
| Current purpose | A short reason to keep the item, written in present tense |
| Last meaningful use | confirmed flush LSN, slot lag, subscription last message, and downstream job runs |
| Dependency evidence | publication tables, subscriber connection info, pipeline schedules, and owner review |
| Risk if wrong | The outage, data loss, access failure, or rollback gap the review must avoid |
| Next action | Keep, reduce, archive, disable, remove, or investigate |
Do not make the inventory larger than the decision. A short list with owners and evidence beats a perfect spreadsheet that nobody is willing to act on.
Replication Evidence to Collect
The useful question is not “how old is it?” It is “what would break, become harder to recover, or lose accountability if this disappeared?” For database publication cleanup, collect enough evidence to answer that without relying on naming conventions.
| Check | What to look for | Cleanup signal |
|---|---|---|
| Slot lag | Replication slot name, confirmed flush LSN, restart LSN, retained WAL, and lag trend | The slot is inactive or safe to advance after consumer approval |
| Subscriber status | Subscription enabled state, last message time, connection errors, and apply worker logs | The downstream consumer has moved or no longer connects |
| Published tables | Publication table list, schema changes, row filters, and sensitive data scope | The feed no longer carries current data contracts |
| Recreate path | Publication DDL, subscription config, initial snapshot notes, and rollback owner | The team can restore the feed if a hidden consumer appears |
Use several signals together. Activity can miss monthly jobs and incident-only paths. Ownership can be stale. Cost can distract from security or recovery risk. The strongest case combines runtime data, dependency checks, owner review, and a rollback plan.
If the evidence conflicts, label the item “investigate” with a named owner and review date. That is still progress because the next review starts with a narrower question.
Example Publication Review
Use read-only catalog queries to prove whether a publication and its slots still have live subscribers.
SELECT slot_name, plugin, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
ORDER BY active, slot_name;
SELECT pubname, schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'legacy_audit_feed';These queries show slot state and table scope. They do not prove deletion safety until downstream owners confirm snapshot, replay, and audit requirements.
Pause Before Dropping
Use the least permanent move that proves the decision. In database publication cleanup, removal is only one possible outcome; reducing size, narrowing permission, shortening retention, archiving, or disabling a trigger may produce the same benefit with less risk.
- Pause or disable the downstream subscription before dropping the publication.
- Narrow the publication table list before removing the whole feed when only part of the contract moved.
- Keep publication DDL, subscription config, and initial snapshot notes beside the cleanup decision.
Track the cleanup candidate with a simple priority score:
| Score | Good sign | Bad sign |
|---|---|---|
| Impact | Meaningful spend, risk, toil, noise, or confusion disappears | The item is cheap and low-risk but politically distracting |
| Confidence | Owner, purpose, and dependency path are understood | The team is guessing from age or name |
| Reversibility | Restore, recreate, re-enable, or rollback path exists | Deletion would be the first real test |
| Prevention | A rule can stop recurrence | The same pattern will return next month |
Start with high-impact, high-confidence, reversible candidates. Defer confusing items only if they get an owner and a date; otherwise “defer” becomes another word for keeping waste permanently.
Data You Should Not Rush
Some cleanup candidates are supposed to look quiet. Do not rush these cases:
- Audit feeds, fraud review streams, and legal-retention copies that run outside normal analytics windows.
- Backfill jobs that keep a subscription disabled until a scheduled replay.
- Publications that share a replication slot or downstream service account with active consumers.
For these cases, use a longer observation window, explicit owner approval, and a staged reduction. The point is not to avoid cleanup; it is to avoid making the first proof of dependency an outage.
Run the Data Review
Run database publication cleanup as a decision review, not an open-ended hygiene project.
- Pick the narrow scope and export the candidate list.
- Add owner, current purpose, last-use evidence, dependency checks, and risk if wrong.
- Remove obvious false positives, then ask owners to choose keep, reduce, archive, disable, remove, or investigate.
- Apply the least permanent useful change first.
- Watch the signals that would reveal a bad decision.
- Complete the final removal only after the review window closes.
- Save a replication-feed retirement record with publication name, subscriber owner, lag evidence, table scope, staged disable, and recreate SQL.
For broader cleanup planning, use the cleanup library to pair this guide with related notes.
Keep Retention Explicit
Prevention should change the creation path, not just the cleanup path. For database publication cleanup, the useful prevention fields are data owner, retention policy, recreate path, and review date. Make those fields part of normal creation and review.
- Create publications with owner, consumer list, table scope, and review date.
- Put publication DDL and subscription ownership near the migration or pipeline repository.
- Review replication feeds when CDC consumers, audit exports, or warehouse loaders move.
The recurring review should be short: sort by impact, pick the unclear items, assign owners, and close the loop on anything nobody claims. If the review keeps producing the same class of candidate, fix the creation path instead of celebrating repeated cleanup.
Example Decision Record
Use a compact record so the cleanup can be reviewed later without reconstructing the whole investigation.
| Field | Example entry for this cleanup |
|---|---|
| Candidate | Stale logical replication publications and subscriptions in transactional databases, replication slots, downstream consumers, schemas, migration files, and data pipeline ownership records |
| Why it looked stale | Low recent activity, unclear owner, or no current consumer after the first review |
| Evidence checked | Slot lag, Subscriber status, and owner confirmation |
| First reversible move | Add or repair ownership metadata before changing anything ambiguous |
| Watch signal | The metric, alert, job, route, query, or owner complaint that would show the cleanup was wrong |
| Final action | Keep, reduce, archive, disable, or remove after a window long enough to include scheduled and low-frequency use, not just a quiet afternoon |
| Prevention rule | Require owner and review-date metadata at creation time |
This record is intentionally small. If the decision needs a long narrative, the candidate is probably not ready for removal yet. Keep investigating until the owner, evidence, reversible move, and prevention rule are clear.
FAQ
How often should teams do database publication cleanup?
Use a window long enough to include scheduled and low-frequency use, not just a quiet afternoon for the first decision, then set a recurring cadence based on change rate. Fast-moving non-production systems may need monthly review; slower systems can be quarterly if every unclear item has an owner and a review date.
What is the safest first action?
The safest first action is usually ownership repair plus evidence collection. After that, add or repair ownership metadata before changing anything ambiguous. That creates a visible test before permanent deletion.
What should not be removed quickly?
Do not rush anything connected to rare scheduled work that runs monthly, quarterly, or only during incidents. Also slow down when the cleanup affects recovery, compliance, customer-specific behavior, rare schedules, or security response.
How do you make the decision useful later?
Write the decision as a small operational record: candidate, owner, evidence, chosen action, watch signals, rollback path, final date, and prevention rule. That format helps future engineers, search engines, and AI assistants understand the cleanup without guessing.