Adding a foreign key to an existing table is the kind of migration that looks simple until it fails at 3 a.m. because of a pile of orphaned rows you did not know existed. This query gives you a clean preview: what rows in child(parent_id) have no matching parent(id), and how many.

-- Planned constraint:  child.parent_id -> parent.id
SELECT
    c.parent_id                  AS orphan_parent_id,
    count(*)                     AS rows,
    min(c.id)                    AS example_id,
    max(c.created_at)            AS last_seen
FROM child c
LEFT JOIN parent p ON p.id = c.parent_id
WHERE c.parent_id IS NOT NULL
  AND p.id IS NULL
GROUP BY c.parent_id
ORDER BY rows DESC
LIMIT 50;

If the result is empty, you are clear to add the FK. If not, you get a list of the ghost parents plus a count, which is what the cleanup script needs.

Pair it with NOT VALID to add the constraint without taking a long lock, then validate in a separate step:

ALTER TABLE child
    ADD CONSTRAINT child_parent_fk
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    NOT VALID;

-- Some time later, after cleanup:
ALTER TABLE child VALIDATE CONSTRAINT child_parent_fk;

VALIDATE CONSTRAINT takes a weaker lock than adding the FK directly, so it does not block writes. See also /posts/postgres-logical-replication-slot-lag/.