MongoDB与PostgreSQL跨库关联查询可行性及方案咨询

问题背景 #

我在MongoDB中定义了Post集合的Schema：

const Post = new Schema(
  {
    user_id: {
      type: Number,
      required: true,
    },  
    title: {
      type: String,
      required: true,
    },
    description: {
      type: String,
    },
    attached_files: [IdeaMedia],
    status: {
      type: Number,
      default: 1,
      enum: Object.values(IdeaStatus), // 0- inactive, 1- active, 2- delete
    },
  },
  {
    timestamps: { createdAt: "created_at", updatedAt: "updated_at" },
  }
);

同时在PostgreSQL中用Sequelize定义了User表：

const UserSchema = sequelize.db.define(
  "user",
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    first_name: {
      type: Sequelize.STRING,
      allowNull: true,
    },
    last_name: {
      type: Sequelize.STRING,
      allowNull: true,
    },
    avatar: {
      type: STRING,
      allowNull: true,
    },
    phone: BIGINT,
    email: {
      type: STRING,
      allowNull: false,
      unique: true,
      set(value) {
        this.setDataValue("email", value.trim().toLowerCase());
      },
    },
    status: {
      type: INTEGER,
      defaultValue: 1,
      validate: {
        isIn: {
          args: [[0, 1, 2]],
          msg: "Invalid Status Value",
        },
      },
    },
  },
  {
    tableName: "user",
    createdAt: "created_at",
    updatedAt: "updated_at",
  }
);

现在需要获取带用户信息的帖子列表，不想冗余存储用户字段（避免用户更新后需批量修改所有帖子），想了解跨库关联的可行方案。

可行实现方案 #

1. 应用层手动关联（最直接易实现） #

这是最常用的跨库关联方式，分三步操作：

• 从MongoDB查询目标帖子列表，提取所有user_id；
• 用提取到的user_id批量从PostgreSQL查询对应的用户信息；
• 在代码中将用户信息与对应帖子做匹配组装。

示例代码（Node.js）：

// 1. 查询MongoDB中的帖子
const posts = await Post.find({ status: 1 }).lean();

// 2. 提取所有user_id并去重
const userIds = [...new Set(posts.map(post => post.user_id))];

// 3. 批量查询PostgreSQL中的用户
const users = await UserSchema.findAll({
  where: { id: userIds, status: 1 },
  attributes: ['id', 'first_name', 'last_name', 'avatar']
});

// 4. 将用户信息映射为对象方便匹配
const userMap = users.reduce((map, user) => {
  map[user.id] = user.toJSON();
  return map;
}, {});

// 5. 组装带用户信息的帖子列表
const postsWithUsers = posts.map(post => ({
  ...post,
  user: userMap[post.user_id] || null
}));

优缺点：

• 优点：无需额外组件，实现简单，对数据库无特殊要求；
• 缺点：需要两次数据库查询，若查询期间用户信息更新，可能拿到不一致数据（一般业务场景可接受，强一致需求需额外处理）。

2. CDC数据同步+MongoDB内部关联 #

用变更数据捕获（CDC）工具监听PostgreSQL的用户表变更，同步必要的用户字段（如id、first_name、last_name、avatar）到MongoDB的独立用户信息集合（如user_profiles），之后在MongoDB中用$lookup聚合操作关联Post和user_profiles集合。

核心步骤：

• 部署CDC工具（如Debezium、MongoDB Atlas Trigger），监听PostgreSQL user表的INSERT/UPDATE/DELETE事件；
• 将变更的用户数据同步到MongoDB的user_profiles集合，保持id与PostgreSQL一致；
• 查询帖子时用MongoDB聚合关联：

const postsWithUsers = await Post.aggregate([
  { $match: { status: 1 } },
  {
    $lookup: {
      from: 'user_profiles',
      localField: 'user_id',
      foreignField: 'id',
      as: 'user'
    }
  },
  { $unwind: { path: '$user', preserveNullAndEmptyArrays: true } }
]);

优缺点：

• 优点：查询时只需一次MongoDB操作，性能较好，用户信息变更自动同步；
• 缺点：需要维护CDC同步组件，增加系统复杂度，同步存在一定延迟（最终一致性）。

3. 数据库层面联邦查询 #

如果你的数据库服务支持联邦查询，可以直接在数据库层实现跨库关联：

• PostgreSQL侧：使用mongodb_fdw插件，将MongoDB的Post集合映射为PostgreSQL的外部表，之后用SQL关联本地user表和外部Post表；
• MongoDB侧：如果使用MongoDB Atlas，可通过Data Federation连接PostgreSQL的user表，之后用聚合查询关联Post集合和外部用户表。

示例（PostgreSQL用mongodb_fdw）：

-- 1. 安装mongodb_fdw插件
CREATE EXTENSION mongodb_fdw;

-- 2. 创建服务器连接MongoDB
CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongodb_fdw OPTIONS (address 'mongodb://mongo-host:27017', dbname 'your-db');

-- 3. 创建用户映射
CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'mongo-user', password 'mongo-pass');

-- 4. 创建外部表映射MongoDB的posts集合
CREATE FOREIGN TABLE posts (
  id UUID,
  user_id INTEGER,
  title TEXT,
  description TEXT,
  status INTEGER,
  created_at TIMESTAMP
) SERVER mongo_server OPTIONS (collection 'posts');

-- 5. 关联查询
SELECT p.*, u.first_name, u.last_name, u.avatar
FROM posts p
LEFT JOIN "user" u ON p.user_id = u.id
WHERE p.status = 1 AND u.status = 1;

优缺点：

• 优点：代码层无需处理关联逻辑，直接用SQL/MongoDB聚合查询；
• 缺点：依赖特定数据库插件或云服务，配置复杂，跨库查询性能通常不如本地关联。

其他补充方案 #

如果不想跨库，也可以考虑：

• 将用户和帖子数据迁移到同一数据库（PostgreSQL或MongoDB），使用原生关联（PostgreSQL的外键、MongoDB的$lookup）；
• 若必须冗余用户字段，结合CDC工具自动同步更新，避免手动批量修改帖子。

内容的提问来源于stack exchange，提问作者shivam mishra