讲讲PostgreSQL 中的数组（Array）数据类型及其适用场景

知识点图片

在 PostgreSQL 中，数组（Array） 是一种非常强大且原生的数据类型。与传统的只允许单一值的关系型数据库不同，PostgreSQL 允许将一列定义为多维数组，它可以存储任何内置类型、自定义类型或枚举类型的集合（例如 integer[], text[], jsonb[] 等）。

下面详细为您讲解 PostgreSQL 中数组数据类型的基础用法、核心功能以及它的适用与不适用场景。

一、基础用法与核心操作

1. 定义表与插入数据

可以通过在类型后面加上 [] 来定义数组列。

sql

CREATE TABLE articles (
    id serial PRIMARY KEY,
    title text,
    tags text[],          -- 文本数组
    schedule integer[]    -- 整数数组
);

-- 插入数据的两种写法：
-- 写法一：使用 ARRAY 构造器 (推荐，更符合标准 SQL 习惯，且不易出错)
INSERT INTO articles (title, tags, schedule) 
VALUES ('PG教程', ARRAY['database', 'postgresql'], ARRAY[1, 2, 3]);

-- 写法二：使用花括号的字面量语法
INSERT INTO articles (title, tags, schedule) 
VALUES ('后端开发', '{backend, api}', '{4, 5, 6}');

2. 查询与操作

注意：PostgreSQL 的数组索引默认是从 1 开始的，而不是 0。

sql

-- 查询数组中的特定元素 (获取第一个标签)
SELECT title, tags[1] FROM articles;

-- 筛选包含特定元素的行 (使用 ANY)
SELECT title FROM articles WHERE 'postgresql' = ANY(tags);

-- 筛选包含特定子数组的行 (使用 @> 包含操作符)
SELECT title FROM articles WHERE tags @> ARRAY['database', 'postgresql'];

-- 数组元素追加 (使用 ||)
UPDATE articles SET tags = tags || 'sql' WHERE id = 1;

-- 移除数组中的特定元素 (使用 array_remove)
UPDATE articles SET tags = array_remove(tags, 'database') WHERE id = 1;

3. 神奇的 `unnest()` 函数

unnest() 是数组操作中最常用的函数，它可以将一个数组“展开”成多行（行转列的逆操作），这在做统计和 JOIN 时极其有用。

sql

-- 统计每个标签下有多少篇文章
SELECT unnest(tags) AS tag, count(*) 
FROM articles 
GROUP BY tag 
ORDER BY count DESC;

二、适用场景

使用数组的主要目的是适度反范式（Denormalization），减少 JOIN 操作，提升查询性能或简化数据模型。

1. 标签系统 (Tagging)

这是数组最经典的适用场景。一篇文章可能有几个标签，如果使用传统的三范式，你需要三张表：articles, tags, 和中间表 article_tags。

用数组的优势： 直接在 articles 表中加一个 tags text[] 列即可。配合 GIN 索引，查询某标签下的文章速度极快，且写入时无需维护多张表。

2. 简单的多选属性

例如用户的兴趣爱好（"篮球", "阅读", "编程"），或者订单支持的支付方式。这类数据通常数量级不大，且生命周期依附于主记录。

3. 存储有序的聚合数据 / 时序数据快照

比如存储一个员工一年12个月的薪水，或者某传感器一天内每小时的平均温度：monthly_salary numeric[12]。

优势： 相比于存 12 行数据，存成一个数组可以极大减少行数，读取一条主记录时直接带出所有相关序列，避免了大量的聚合计算。

4. 业务层面的列表缓存 (避免频繁 JOIN)

假设有一个业务场景，需要频繁展示用户的“最近浏览的50个商品ID”。如果每次都去浏览记录表里做 ORDER BY time DESC LIMIT 50，压力会很大。

可以每次用户浏览后，更新用户表中的 recent_view_ids bigint[] 字段，保持其长度为50。读取时直接取出该数组即可。

三、不适用场景（反模式）

虽然数组好用，但滥用会导致严重的设计灾难。以下场景不建议使用数组：

1. 需要强制外键约束的场景

数组元素不能直接作为外键！
假设你的 tags text[] 里的标签是从一个固定的标签库来的。如果管理员在标签库中删除了 "postgresql" 这个标签，你无法利用数据库自带的级联删除（CASCADE）去自动清理 articles 表里的数组元素。如果业务对数据完整性要求极高，必须使用传统的多对多关联表。

2. 数组元素可能会变得非常庞大

PostgreSQL 在读取行时，会将整行（包括整个数组）加载到内存中。如果你在一个列里存了上万个 ID（比如一篇文章的十万个点赞用户 ID），每次读取或更新这篇文章的记录时，都会造成巨大的 I/O 和内存开销。

原则： 数组里的元素数量最好控制在几十个、几百个以内。对于无上限的集合，老老实实建子表。

3. 频繁并发更新单个元素的场景

关系型数据库的锁是行级锁。如果你频繁地并发往同一行的数组里追加/修改元素，会导致大量的锁冲突和行数据膨胀（MVCC 机制决定了更新数组等同于重写整行数据）。

四、性能与索引建议 (极其重要)

如果你的业务场景需要经常使用 WHERE 'tag' = ANY(tags) 或 WHERE tags @> ARRAY['tag'] 来过滤数据，传统的 B-Tree 索引是无效的。

必须使用 GIN (Generalized Inverted Index，广义倒排索引)：

sql

-- 为 tags 数组创建 GIN 索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

建立了 GIN 索引后，类似于 @> (包含) 或 && (重叠/交集) 的查询性能会发生质的飞跃，即使在千万级数据量下也能实现毫秒级响应。

总结

PostgreSQL 的数组类型是一种介于“严格关系型范式”和“NoSQL文档型灵活性”之间的利器。

用它： 当你需要存储少量、依附于主体的集合数据，想要避免繁琐的 JOIN，且不需要强外键约束时。
不用它： 当集合元素可能无限增长、需要严格的外键约束、或者需要针对集合元素进行高并发独立更新时。

右滑查看面试常问

播面