PostgreSQL：多对多关系中用整数数组替代额外表是否可行？

一、简洁性与效率对比 #

1. 简洁性 #

• 整数数组方案：无需额外创建关联表，表结构更紧凑。插入/查询关联ID列表时无需JOIN，写法更直观，比如直接执行 INSERT INTO posts (title, tag_ids) VALUES ('xxx', ARRAY[1,2,3])，查询关联ID直接取数组字段即可。
• 关联表方案：需要新建专门的关联表，插入关联关系时需执行多条INSERT，查询时需通过JOIN关联主表，但符合数据库范式，逻辑分层更清晰，复杂场景下更易维护。

2. 效率 #

• 小数据量/低变更场景：数组方案更占优，少了JOIN操作，单条记录存储关联数据，IO开销更低，简单查询速度更快。但如果数组元素过多，会导致单条记录体积过大，降低缓存命中率。
• 大数据量/高频变更场景：关联表更高效。比如删除单个关联关系时，数组方案需要拆分、修改、重新写入整个数组；而关联表仅需删除单条记录。查询特定关联关系（如找所有关联某ID的记录）时，数组依赖ANY或@>操作符，即使建GIN/GIST索引，性能也不如关联表的B-tree主键/外键索引，且数据库对JOIN查询的优化更成熟。

二、数据完整性保障 #

• 关联表方案：可通过外键约束直接保障数据一致性，比如关联的ID必须存在于主表，删除主表记录时可配置ON DELETE CASCADE等规则，无需额外代码，可靠性拉满。
• 整数数组方案：无法直接用外键约束，只能通过触发器或应用层代码校验数组内的ID合法性，实现复杂且易出漏洞，维护成本高。

三、整数数组方案是否可行？ #

不是不可行，但仅适合特定场景：

• 适用场景：数据量小、关联关系极少变更、查询以获取完整关联ID列表为主，且对数据完整性要求不极端的场景（比如临时存储的关联标记）。
• 不适用场景：数据量大、关联关系频繁增删、需要严格数据完整性、有复杂查询需求的生产场景（比如用户-角色、订单-商品的关联），此时数组方案会带来维护困难、性能下降、数据不一致的风险。

示例代码 #

整数数组方案表结构

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tag_ids INT[]
);
-- 可选：建GIN索引优化数组查询
CREATE INDEX idx_posts_tag_ids ON posts USING GIN(tag_ids);

关联表方案表结构

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT
);
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT
);
CREATE TABLE post_tags (
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

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