PostgreSQL 数据库迁移后留下废弃空表？用 information_schema 审计 schema 残留
在一次广告数据源从日表切换到周表后，我发现 schema 里还躺着一张只在最早期的 migration 里建过、0 行数据、运行时代码从不引用的空表——还带着过时的字段名和没规范化的中文列。
在开发 AI运营 时遇到此问题——基于大语言模型的智能分析，自动洞察市场趋势、用户行为、销售数据，提供精准运营策略。这次数据源切换后，写入端早已迁到新的周表，旧日表的清理 migration 也补了，唯独一张只在 baseline 里 CREATE 过的月表被遗漏——它既没有对应的新写入，也没有 DROP，就那样潜伏在 schema 里，带着早已废弃的字段定义。
TL;DR
废弃表的典型特征：只在早期/baseline migration 里 CREATE、当前代码 0 引用、常带旧字段或未规范化的列名。批量迁移时它们不会被自动处理，需要主动用 information_schema.tables 列出 schema 全表，再与代码引用比对，定位 orphan 表后写一条 DROP migration 清理——而不是手动 psql 删完就了事。
问题现象
一张典型的废弃表长这样：
- 0 行数据——业务早已不再写入它；
- 0 运行时引用——代码里
grep不到任何SELECT/INSERT，只剩 migration 文件里的CREATE； - 旧字段残留——字段名是上一版命名（如
ad_plan_id/product_id），与当前规范不一致； - 未规范化的列名——甚至还有中文列名没来得及改。
它不报错、不影响线上运行，所以从「线上没出问题」的视角完全无感。但它的危害是隐性的：误导后来者以为它仍在用、占用 schema 命名空间、在跨表审计时制造噪音，还可能被某个误判的 SELECT * 意外读到脏数据。
根因
数据库迁移有一个普遍的模式：migration 是「加法」的。
一次数据源切换通常这样演进：
- 早期 baseline migration
CREATE了一批表（日表、月表）； - 业务跑通后，写入端开始依赖这些表；
- 需求变化，引入新表（周表），写入端逐步迁移过去；
- 旧表的写入停了，补一条 migration
DROP旧日表； - 但月表/其他只在 baseline 建过、从未被写入端直接引用的表，没有对应的
DROP。
问题出在第 5 步：迁移注意力集中在「现在用到的表」上——哪些表在写入、哪些 SQL 在查。而「曾经存在、但从未进入主链路」的表既不在写入端、也不在查询端，自然不会触发任何 DROP，于是成了 orphan。这类残留和 Airflow 删除 DAG 后元数据残留 是同一类问题：「删了入口、忘了清结构」，是迁移类问题的高发区。
解决方案
核心流程：列全表 → 比对引用 → 确认空表 → 写 migration DROP → 验证。
步骤 1：用 information_schema 列出 schema 下所有基础表
-- 列出某 schema 下所有基础表（排除视图）
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
AND table_type = 'BASE TABLE'
ORDER BY table_name;
information_schema.tables 是 SQL 标准目录视图，跨 PostgreSQL/MySQL/SQL Server 通用，字段稳定，非常适合写进审计脚本。
步骤 2：grep 代码库确认运行时引用
对每张候选表，在代码库里搜索引用，排除 migration 文件本身：
# 搜索运行时代码引用，排除 migrations 目录
grep -rn "ad_product_monthly_stats" src/ --include="*.py" \
| grep -v "migrations/"
# 0 行输出 → 运行时无引用，进入候选
0 引用是判定 orphan 的关键证据。注意一定要排除 migration 目录——baseline 里的 CREATE 不算「引用」。
步骤 3：确认是空表
SELECT count(*) FROM your_schema.ad_product_monthly_stats;
-- 0 → 确认无数据，可安全清理
对有数据的表要格外谨慎：先确认它真的废弃（而非只是近期没写入），有疑问就先做逻辑备份再处理。
步骤 4：写一条 migration DROP（而非手动删）
-- db-migrations/{project}/027_drop_ad_product_monthly_stats.sql
DROP TABLE IF EXISTS your_schema.ad_product_monthly_stats;
务必走 migration 文件：它会被版本控制、在所有环境（开发/预发/生产）一致重放，留下审计轨迹。手动 psql 删一次，换台机器就又长回来了。
步骤 5：验证已删除
SELECT to_regclass('your_schema.ad_product_monthly_stats');
-- 返回 NULL 表示表已不存在
to_regclass() 是验证表是否存在的标准手段，返回 NULL 即确认删除成功。
批量审计：按前缀一次性排查同类遗漏
单张表清掉后，按前缀把同类表全部列出来逐个核对，避免「清了一张、漏了兄弟」：
-- 列出某前缀下所有表，逐个走 步骤2-5
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
AND table_name LIKE 'ad_%'
ORDER BY table_name;
注意事项
- DROP 前先备份/快照：生产库删表不可逆。对任何有数据的表，先确认废弃再做逻辑备份（如
CREATE TABLE ... AS SELECT导出到归档库）。 - 外键依赖要排查：如果有其他表的外键指向它，
DROP TABLE会失败。确认依赖已解除或有意CASCADE——但CASCADE会连带删除依赖对象，生产环境慎用。 - 走 migration，不要手动 psql：手动删除只在当前环境生效，迁移文件才能保证多环境一致并留下记录。
- 用前缀批量审计：一次切换通常涉及一组同前缀的表（如
ad_*），清完一张后用LIKE 'ad_%'把兄弟表都过一遍，主动发现同类遗漏。
常见问题
怎么列出 PostgreSQL 数据库中的所有表？
查 information_schema.tables，过滤 table_schema 和 table_type = 'BASE TABLE'，即可列出某 schema 下所有基础表。它比 psql 的 \dt 更适合写进脚本做自动化审计，且是 SQL 标准、跨数据库通用，代码可移植性更好。
PostgreSQL 怎么找出没被使用的废弃表？
用 information_schema.tables 列出全部表，再与代码库或查询日志的引用做比对，运行时代码 0 引用且无写入的表即为废弃候选；空表可进一步用 SELECT count(*) 确认行数，确认无数据、无外键依赖后再写 migration DROP 清理。
information_schema 和 pg_catalog 有什么区别？
information_schema 是 SQL 标准定义的目录视图，跨 PostgreSQL/MySQL/SQL Server 通用、字段稳定不易变，适合写可移植的审计脚本；pg_catalog 是 PostgreSQL 专有目录，信息更全更细（如精确行数估算、存储细节），但版本间可能调整。做通用 schema 审计优先用 information_schema。