企业官网建设流程全解析

做后端开发这些年，接触过不少数据库，MySQL、Oracle、SQL Server 都折腾过，但真要让我选一个“日常顺手、功能耐打”的数据库，我会毫不犹豫选 PostgreSQL。尤其是它的数组类型，很多人眼里可能只是“能存个列表”的小功能，实际用好了，能在不少场景里直接省掉一张关联表，或者砍掉一段又臭又长的 JSON 解析代码。这篇就从增删改查和索引优化两条线，把 PostgreSQL 数组的实战细节完整过一遍，适合正在用 PG 做业务开发、想提升查询性能、或者准备面试时被问到“数组索引怎么优化”的朋友参考。

PostgreSQL 的数组不是花架子，它是完整的一等公民类型：可以建表、可以加索引、可以参与聚合、可以和普通列一样做条件过滤。但恰恰因为它太灵活，很多人要么不敢用，要么用了之后写查询时总踩坑。我见过不少同事把数组当成“可以存放多个值的字符串”来用，存的时候拿逗号拼，查的时候用 LIKE 去匹配，最后性能惨不忍睹还怪数据库不行。问题不在 PostgreSQL，在于没有用对姿势。这篇文章会把数组的完整操作链路拆开，告诉你每一步该怎么做、为什么要这么做、以及怎么让数组查询真正跑出索引的效果。

1. 先搞清楚：数组到底适合解决什么问题

1.1 数组不是用来炫技的，它解决的是“一行多值”的需求

业务里“一行对应多个值”的需求太常见了。一篇文章有多个标签、一个用户有多个角色、一个订单有多个物流轨迹点、一个商品有多个轮播图 ID。传统关系型数据库的第一反应是拆一张子表，文章表和标签表做关联，查询时 JOIN 一把。这种设计没问题，完全符合关系模型理论，数据规范化程度也高，但实际用起来有几个让你难受的地方：

一是查询要走 JOIN，SQL 变复杂，尤其当主表数据量大、标签表也需要过滤时，优化器稍不留神就给你整出嵌套循环或者排序合并，慢得让人抓狂。二是应用层要处理的对象从“一行记录”变成了“一行记录加一个子集合”，ORM 映射起来特别别扭，很多框架的关联查询懒加载能把你坑到怀疑人生。三是如果只是单纯想把多值存下来，比如一组数字 ID、一组短字符串，拆表有点杀鸡用牛刀的意思。

数组类型在这里的价值就是：把“多值”直接塞进一行里，查询时只要关注主表，不需要 JOIN，不需要子查询，也不需要额外的应用层拼装逻辑。一条记录就是一个完整对象，干净利落。

1.2 数组、JSONB、关联表，到底怎么选

这不是一个非此即彼的问题，而是要看你的数据形态和访问模式。

数组最适合的场景是：元素类型固定、长度相对稳定、访问模式基本是“整存整取”或者“判断是否存在某个值”。比如标签（text[]）、ID 列表（bigint[]）、权限码集合（int[]），这类数据用数组非常顺手。JSONB 适合的是结构不固定、嵌套层级深、需要存储复杂文档的场景，比如用户扩展属性、接口请求日志、配置快照。如果你只用 JSONB 存一个扁平列表，其实是用大炮打蚊子，JSONB 的解析开销、存储膨胀都比数组高不少。

关联表则适合：数据需要独立维护、需要和其他表做复杂 JOIN、需要分页展示子表、需要针对子记录做频繁单点更新。如果有一天你发现自己在对数组做“查找并修改某个元素的属性”，那基本说明该拆表了。

我个人的选型习惯是：只读或近只读的集合用数组，结构灵活多变用 JSONB，需要强一致性和复杂关联用子表。这不是教条，而是经过项目验证的取舍逻辑——数组牺牲了一部分规范化，换来了查询路径的极简和性能上的确定性。

1.3 数组的类型体系与存储形态

PostgreSQL 数组支持几乎所有基础类型：int、bigint、text、varchar、numeric、date、timestamp，甚至自定义复合类型。最常用的是 int[] 和 text[]，前者适合存 ID 集合，后者适合存标签、名称集合。还可以有多维数组，比如 int[][]，但实际业务里用得不多，而且多维数组在索引优化上没有太多发挥空间，后面会专门说限制。

数组在存储层本质上就是一个变长类型，内部按二进制格式保存元素序列。你看到的花括号字面量 '{1,2,3}' 只是外部表示，内部并不是文本。索引可以建立在数组列上，元素可以被高效检索，这让数组不再只是“存储容器”，而是一个可被数据库引擎深度优化的数据类型。

2. 数组的增删改查：实操全流程

2.1 建表：数组字段的定义与默认值

建表定义数组列很简单，直接在类型名后面加方括号。下面这个例子是内容管理系统中很典型的场景，一篇文章可以打多个标签，同时记录每天的浏览量：

CREATE TABLE articles ( id bigserial PRIMARY KEY, title text NOT NULL, tags text[] DEFAULT '{}', view_counts int[] DEFAULT ARRAY[0, 0], created_at timestamptz DEFAULT now() );

DEFAULT '{}'是空数组字面量，DEFAULT ARRAY[0, 0]是构造器表达式。两个写法等价，都能给数组字段提供默认值。这里有一个新手容易犯的错：空数组'{}'不是 NULL，它是一个长度为 0 的数组。默认值设置为空数组，能避免后续查询里到处写COALESCE。

如果你希望数组存储时保证元素唯一，PostgreSQL 原生数组没有唯一性约束，只能靠应用层或者触发器保证。如果非常需要唯一性，intarray 扩展能提供一些辅助函数，但整体上数组不是一个强约束容器，别把它当 Set 用。

2.2 插入数据：三种写法各有适用场景

数组插入有三种主流写法，我建议你全部掌握，因为不同场景下它们各有优势。

第一种是字面量写法：

INSERT INTO articles (title, tags) VALUES ('PG数组实战', '{postgresql,数组,索引优化}');

字面量用花括号包裹，元素之间用逗号分隔。这种写法简洁，适合 SQL 脚本、数据迁移、手工维护场景，但缺点是如果元素里包含特殊字符（比如逗号、反斜杠、花括号），就必须加转义，阅读性会比较差。

第二种是 ARRAY 构造器：

INSERT INTO articles (title, tags) VALUES ('PG数组实战', ARRAY['postgresql', '数组', '索引优化']);

ARRAY 构造器把元素当成普通 SQL 常量处理，不需要担心花括号字面量的转义问题，参数化查询也更好写。应用层往数据库传数组时，用这条最方便，比如 JDBC 或 pg 驱动直接传 Java 数组。

第三种是从查询结果聚合：

INSERT INTO articles (title, tags) SELECT 'PG数组实战', array_agg(tag_name) FROM tag_table WHERE article_id = 100;

这种方式在数据回填、历史数据迁移时非常实用。把子查询里的多行结果聚合到一个数组，直接插入目标表，省去应用层循环查询再拼接的步骤。array_agg还可以配ORDER BY控制元素顺序，比如array_agg(tag_name ORDER BY tag_name)。

2.3 查询：下标、切片与包含判断

数组查询是整篇文章的重头戏，因为大多数索引优化问题都出在这一环节。

按下标取元素

PostgreSQL 数组的下标从 1 开始，这一点和 Java、JavaScript、Python 都不一样，是新手最容易踩的坑。取第一个标签：

SELECT title, tags[1] FROM articles WHERE id = 1;

如果下标越界，返回结果是 NULL 而不是报错，这点要注意：tags[10] 对一个只有 3 个元素的数组来说，结果是 NULL，不会异常。这在应用层取值时容易造成“看起来没数据”的错觉。

切片获取子数组

SELECT title, tags[1:2] FROM articles WHERE id = 1;

切片返回的是一个新数组，包含原数组第 1 到第 2 个元素。切片的边界可以省略，比如tags[:2]表示从开头到第 2 个元素，tags[2:]表示从第 2 个元素到结尾。切片的边界写错会返回 NULL，比如tags[3:2]这种下界大于上界的写法，返回的是空数组还是 NULL 取决于具体边界，这条细节建议你本地实测一次，心里有个数。

包含判断：@> 操作符

这是数组查询里最核心的操作符。@>表示“左边的数组是否包含右边数组的所有元素”。比如查询包含 postgresql 标签的文章：

SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

如果 tags 为 '{postgresql, 数组, 索引优化}'，这个条件成立。@> 的顺序是“左边大集合、右边小集合”，千万别写反。写反了就是“右边的数组是否包含左边的数组”，语义完全不同，而且也会影响索引匹配。

重叠判断：&& 操作符

如果想查标签中任意一个匹配就可以，用&&操作符：

SELECT * FROM articles WHERE tags && ARRAY['postgresql', '运维'];

语义是“两个数组是否有交集”，只要有一个元素相同就返回 true。这个操作符也是走 GIN 索引的。

长度与维度

array_length(tags, 1)返回第一维的长度，cardinality(tags)返回元素总数。对一维数组来说两者一致；对多维数组，cardinality 返回所有维度的元素乘积。判断空数组更推荐用cardinality(tags) = 0或者直接tags = '{}'，但要注意 NULL 与空数组的区别。

搜索是否包含某个元素（另一种常见写法）

有些老 DBA 习惯用position(tag in tags)或者tags @> ARRAY[tag]，前者是字符串思维，不适配数组。记住：PostgreSQL 里数组的元素匹配就用@>，它背后是倒排索引和位图扫描，性能比任何形式的遍历都要强。

2.4 修改数据：整数组替换、单元素更新、函数式修改

数组的更新分为三种粒度，对应三条不同的 SQL。

整体替换

把标签列整个换成新数组：

UPDATE articles SET tags = ARRAY['数据库', 'PostgreSQL'] WHERE id = 1;

这种方式最直接，适合“用户在前端编辑了整个标签列表，提交后覆盖存储”的场景。

单元素更新

直接按下标更新某个位置：

UPDATE articles SET tags[1] = 'PG实战' WHERE id = 1;

如果下标越界，PostgreSQL 会自动扩展数组，中间空缺的位置用 NULL 填充。比如 tags 原来是 3 个元素，你更新 tags[5]，结果数组长度变成 5，中间第 4、5 个元素中第 5 个有值，第 4 个是 NULL。这个行为有好有坏，坏处是你可能不小心制造出带空洞的数组，后续查询长度和遍历时都要注意。

函数式修改

追加、删除、拼接元素用数组函数：

-- 追加一个元素 UPDATE articles SET tags = array_append(tags, '实战') WHERE id = 1; -- 追加到数组头部 UPDATE articles SET tags = array_prepend('实战', tags) WHERE id = 1; -- 删除所有等于指定值的元素 UPDATE articles SET tags = array_remove(tags, '临时标签') WHERE id = 1; -- 替换指定元素 UPDATE articles SET tags = array_replace(tags, '旧值', '新值') WHERE id = 1; -- 拼接两个数组 UPDATE articles SET tags = array_cat(tags, ARRAY['新增1', '新增2']) WHERE id = 1;

array_append和array_cat的区别是，前者追加一个元素，后者拼接整个数组。元素数量多时，array_cat更高效，因为底层可以走一次内存分配。

这里有个很重要的性能认知：数组字段的任何更新，在 PostgreSQL 里都是整行重写。因为 PostgreSQL 的 MVCC 机制决定了更新会产生新版本行，数组列作为变长字段也会被整体写入新版本。也就是说，即使你只改了 tags[1]，数据库也把整行数据复制了一份。理解了这一点，就知道数组不应该用于高频单点更新的场景。

2.5 删除数据：清空数组与删除字段

删除数组列中的部分元素，用array_remove即可：

UPDATE articles SET tags = array_remove(tags, '不再需要的标签') WHERE id = 1;

清空整个数组，把它重置为空数组：

UPDATE articles SET tags = '{}' WHERE id = 1;

把数组字段置为 NULL：

UPDATE articles SET tags = NULL WHERE id = 1;

注意'{}'和NULL的区别：'{}'是一个空数组，array_length查询返回 0，@>和&&判断都返回 false，但 IS NULL 判断返回 false；而NULL是“没有这个值”，IS NULL返回 true，@>返回 NULL。在 WHERE 条件里，NULL 的包含判断会被当成 false 处理，这点在数据清理时很容易搞混。

如果彻底不需要这个字段了，用 DDL 删除：

ALTER TABLE articles DROP COLUMN tags;

数组的删除不像关联表那么灵活，它无法做到“删除第一个满足条件的元素”这种精细操作，array_remove会删除所有等于目标值的元素。如果需要更细粒度的操作，比如只删除第一个匹配项，你需要先把数组 unnest 成行，处理完再聚合回去，这个后面会说。

3. 数组索引优化：GIN 索引是如何起飞的

3.1 为什么 B-tree 索引对数组使不上劲

关系型数据库最常用的 B-tree 索引，对数组列几乎是无能为力的。原因在于 B-tree 的索引结构建立在“有序比较”之上，它能高效处理等值查询和范围查询，比如id = 5、create_date > '2024-01-01'。但数组列内部是一组无序的集合，你要查询的不是“整个数组等于某个数组”，而是“数组内部是否有某个元素”。

传统 B-tree 索引在这种场景下只能做到全数组匹配，比如：

SELECT * FROM articles WHERE tags = ARRAY['postgresql', '数组'];

这种全等值查询 B-tree 能帮上忙，但实际业务里这种需求少之又少。更常见的需求是“包含某个标签”“与某组标签有交集”，这时候 B-tree 只能老老实实全表扫描。有些新手尝试给数组列建 B-tree 索引，然后发现 EXPLAIN 里完全没有走索引，就是因为没搞清楚 B-tree 的定位。

3.2 GIN 索引的原理：倒排思想在数据库里的落地

GIN（Generalized Inverted Index）索引的底层是倒排索引，这名字听着高级，思路其实非常简单。想象一本技术书的书末索引：它不是按页码罗列内容，而是把每个关键词映射到它出现的所有页码。GIN 做的事情类似：把数组里的每个元素提取出来，为每个元素记录它出现在哪些行里。

当你执行tags @> ARRAY['postgresql']时，GIN 索引可以立刻定位到所有包含 postgresql 这个元素的行，然后通过位图把符合条件的结果集返回。这个过程的复杂度和数组长度无关，只和这个元素匹配的行数有关，所以查询性能非常稳定。

GIN 的代价在写入端：每次插入或更新一行，需要把数组中的所有元素都拆开，逐个更新倒排索引项。所以 GIN 索引会让写入变慢，但换来了查询的巨大提升。这正好对应数组的最佳使用场景——低频写入、高频查询。

GIN 默认的数组操作符类是array_ops，支持 @>（包含）、&&（重叠）、<@（被包含）、= （数组整体相等）等操作符。日常用的最多的是 @> 和 &&。

3.3 创建索引：语法与操作符类的选择

对 text[] 的 tags 列建 GIN 索引：

CREATE INDEX idx_articles_tags ON articles USING gin (tags);

对 int[] 列建 GIN 索引，可以先用 intarray 扩展：

CREATE EXTENSION IF NOT EXISTS intarray;

intarray 扩展提供了专门针对 int4、int8 数组的gin__int_ops操作符类，优势是索引体积更小、构建更快，还额外支持&&、@>这些操作符的优化。创建索引时指定：

CREATE INDEX idx_articles_view_counts ON articles USING gin (view_counts gin__int_ops);

这里有个容易忽略的细节：intarray 的gin__int_ops在处理包含 NULL 元素的数组时，行为与默认的array_ops不同，如果你无法保证数组元素不为 NULL，用默认操作符类更稳妥。我的建议是，除非你明确知道数据里不会有 NULL，否则先用默认array_ops，等性能确实成为瓶颈再切换优化。

3.4 实测：从全表扫描到索引扫描

光说不练假把式，我用一个实际的例子展示索引的效果。假设 articles 表有 20 万行数据，每行 tags 平均有 4 个元素。

没有 GIN 索引时，执行包含查询：

EXPLAIN ANALYZE SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

计划里通常出现：

Seq Scan on articles (cost=0.00..10000.00 rows=1000 width=...) (actual time=0.05..85.32 rows=9600 loops=1) Filter: (tags @> '{postgresql}'::text[])

全表扫描，一条条过滤，耗时 85 毫秒左右。数据量涨到 200 万行时，这个数字会线性增长到数百毫秒甚至秒级。

创建索引后，再次执行同样的查询：

CREATE INDEX idx_articles_tags ON articles USING gin (tags); ANALYZE articles; EXPLAIN ANALYZE SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

计划变为：

Bitmap Index Scan on idx_articles_tags (cost=0.00..40.20 rows=9600 width=0) (actual time=0.35..0.35 rows=9600 loops=1) Index Cond: (tags @> '{postgresql}'::text[]) Bitmap Heap Scan on articles (cost=40.20..800.00 rows=9600 width=...) (actual time=0.40..1.20 rows=9600 loops=1)

先走 Bitmap Index Scan 定位行号，再回表取数据，整体耗时降到 2 毫秒以内。20 万行数据看起来提升不算夸张，但把量级放大到千万行，全表扫描基本不可用，GIN 索引依然能稳定在几十毫秒内返回结果。这个量级下的差异，就是线上能用和不能用的区别。

4. 避坑指南：数组查询为什么有时候不听话

4.1 下标查询走不了索引，改用包含判断

最典型的坑就是很多人会写：

SELECT * FROM articles WHERE tags[1] = 'postgresql';

这想表达的是“第一个标签是 postgresql”，这个语义本身没问题，但它完全没法使用 GIN 索引，因为 GIN 索引存储的是每个元素到行号的映射，而不是“下标位置”。你告诉数据库的是“按位置取元素再比较”，数据库只能逐行取出数组再判断。

如果你真正想要的是“标签里包含 postgresql”，应该改成：

SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

语义上有一点差异，前者要求第一个元素精确匹配，后者只要求某个元素匹配。但大多数业务场景要的是后者。这里的关键认知是：想让查询走 GIN 索引，条件必须操作“数组作为集合”的层面，而不是数组内部的具体下标。

4.2 函数包裹导致索引失效

另一个高频踩坑是把数组列包在函数里：

SELECT * FROM articles WHERE array_length(tags, 1) > 10;

这种查询在索引设计上几乎没有直接方案。array_length是函数调用，GIN 索引不会自动感知“某个数组长度大于 10”这种条件。想优化这类查询，比较实用的做法是在应用层或写入时单独维护一个长度字段，或者用表达式索引：

CREATE INDEX idx_articles_tag_len ON articles USING gin ((array_length(tags, 1)));

但是表达式索引只能处理精确的等值或范围匹配，而且维护成本和收益往往不成正比。更推荐的做法是：把“数组长度”这类高频过滤属性显式建模为独立列，单独加普通 B-tree 索引，这是最省心也最可预测的方式。

同样的问题还出现在模糊匹配上。比如：

SELECT * FROM articles WHERE tags @> ARRAY['%postgres%'];

这个写法不会把你想要的模糊匹配效果跑出来。@>是全值匹配，'%postgres%' 是一个普通字符串，它只会匹配数组中恰好等于 '%postgres%' 的元素。要对数组元素做模糊查询，先把数组展开成行再配合 LIKE 和 pg_trgm 扩展实现：

SELECT DISTINCT a.* FROM articles a CROSS JOIN LATERAL unnest(a.tags) AS tag WHERE tag LIKE '%postgres%';

这条 SQL 的缺点是 unnest 之后没法继续使用 GIN 索引，只适合数据量可控的场景。真正需要数组模糊搜索且数据量很大时，更合理的选择是把标签拆成关联表，用 pg_trgm 做 GIN 索引。这也是我反复强调的：数组适合“整存整取+精确包含”，模糊搜索不是它的主场。

4.3 空数组、NULL 元素和包含判断的坑

空数组和 NULL 的区分前面讲过，这里再补充一个实际开发中的翻车案例。有一个统计接口，需要统计所有带标签的文章数，SQL 写成：

SELECT count(*) FROM articles WHERE tags @> ARRAY[''];

结果查出来的总是 0，因为数组里根本没有空字符串元素。过滤空数组的正确姿势是：

SELECT count(*) FROM articles WHERE cardinality(tags) > 0;

或者：

SELECT count(*) FROM articles WHERE tags <> '{}';

NULL 元素也容易造成诡异行为。如果数组里有 NULL 元素，比如'{postgresql,NULL,数组}'，执行：

SELECT * FROM articles WHERE tags @> ARRAY[NULL]::text[];

结果是未知（NULL），在 WHERE 条件里等同于 false。这不是数据库的 bug，而是 SQL 三值逻辑的必然结果。如果你需要过滤“包含 NULL 元素的数组”，只能使用array_position(tags, NULL) IS NOT NULL这类偏门写法，本质上还是函数遍历，逃不开全表扫描。

4.4 数组字段更新时的索引维护成本

GIN 索引的写入代价比 B-tree 高得多，这一点在设计表结构时就必须想清楚。每个数组元素都会更新倒排索引，如果一个数组平均有 10 个元素，每次插入或更新这行数据，GIN 索引就要做 10 次索引项维护。业务里如果有大量高频写入操作，比如日志流水、订单快照，给数组字段建 GIN 索引很可能导致写入性能大幅下降。

我踩过的坑是一个“标签点击统计表”，每天定时更新标签数组，几十万行数据，建了 GIN 索引后，一次全量更新任务从 3 分钟涨到 15 分钟。排查后发现瓶颈不在 SQL，而在 GIN 索引的维护。后来把标签数组改成 JSONB 字段，利用 jsonb 的 GIN 索引特性配合部分场景优化，才把更新时间压了回去。

所以建索引前建议做一次成本评估：如果这个表的业务读多写少，GIN 是神器；如果读写比接近 1:1 或者写更多，建议谨慎，或者只在只读副本上建索引。

5. 高频问题排查与操作速查

5.1 问题一：明明建了 GIN 索引，@> 查询还是全表扫描

遇到这个问题的概率比想象中高，尤其是新手。先检查三件事。

第一，表的数据量是不是太小。优化器对只有几千行的表，往往会选择全表扫描，因为走索引的随机 IO 成本比顺序扫描还要高，这属于优化器的正常判断，并不是索引没生效。你可以用SET enable_seqscan = off强制走索引对比一下，但生产环境不要这么干。

第二，查询条件是不是被函数包裹了。比如：

WHERE array_to_string(tags, ',') LIKE '%postgresql%'

这是把数组当字符串处理，GIN 索引完全不认识这种写法。必须改成@>操作符。

第三，操作符类不匹配。如果你建索引时用了gin__int_ops，但查询时用 text[]，类型都不一致，索引自然帮不上忙。可以用\d index_name查看索引的操作符类，确认它的适用范围。

第四，表很长时间没做 ANALYZE，统计信息太旧导致优化器误判。执行ANALYZE articles;再试一次，很多时候问题就这么简单。

5.2 问题二：unnest 之后数据行数变多或丢失空元素

unnest把数组展开成多行是很多人爱用的技巧，但它有认知陷阱。原本一行记录展开后有多个标签，展开后行数会变多，如果不加 DISTINCT，JOIN 结果可能重复。另外，如果数组里有 NULL 元素，unnest 照样会输出一行 NULL，和“没有值”语义不同，过滤时容易漏数据。

我的经验是，需要“一行对一个数组元素”做查询时，优先考虑LEFT JOIN LATERAL unnest(...)，它可以保证主表的行不丢失，即使数组为空也能返回一行主表数据。想展开后再聚合回去，用array_agg配合 GROUP BY。

举个典型场景：要把文章表和用户行为表做关联，行为表里记录的是 tag_id，想让一篇文章关联到所有匹配的 tag_id 并汇总。一次性把文章 tags 展开：

SELECT a.id, u.user_id, count(*) FROM articles a JOIN user_actions u ON u.tag_id = ANY(a.tags) GROUP BY a.id, u.user_id;

这里的ANY(a.tags)避免了显式 unnest 和 join 重复，也是数组用法里容易被忽略的一个隐藏技能。

5.3 问题三：同学从 0 开始的下标引发的越界连环坑

我从 0 开始编排数组下标时，第一次取 tags[0] 得到的是 NULL，然后拿着 NULL 去做了业务判断，排查了半天才发现是下标问题。PostgreSQL 数组下标从 1 开始是硬性规定，无法修改。解决办法有两个：一是强行规定应用层所有数组访问从 1 开始，前端展示时单独处理；二是使用array_lower和array_upper动态取边界，但更推荐前者，因为动态取边界会写出很啰嗦的 SQL。

5.4 数组操作速查表

我把常用的数组操作整理成一张表，方便贴在手边：

这张表最大的价值是帮你在写 SQL 前快速判断：这条查询能不能走索引。凡是下标、函数、长度相关的，基本都要在心里打个问号。

5.5 一个常用的性能优化技巧：数组列上的部分索引

有一种业务场景值得聊一聊：不是对所有数组过滤都建 GIN 索引，而是针对高频查询值建部分索引。比如业务只关心“状态标签”为 active 的记录：

CREATE INDEX idx_articles_active_tag ON articles USING gin (tags) WHERE tags @> ARRAY['active'];

这样索引只维护包含 active 标签的行，索引体积大幅减小，写入性能也有改善。你甚至可以针对不同类型的标签分别建部分索引，查询时优化器会自动匹配。这个技巧是我在广告系统里用过的，标签总量千万级，但活跃标签就几十个分类，部分索引让存储开销降到了原来的三分之一。

6. 最后分享一点项目里的实践体会

在实际项目里，我用数组最多的地方是内容标签聚合、批量 ID 透传和权限位图简化。我的体会是：数组字段适合“读取频繁、写入低频”的场景，一旦你的数组被频繁增加删除、或者元素数量膨胀到几十上百个，还是老老实实拆关联表更稳妥。索引再快也顶不住无休止的整行更新加索引重建。相反，如果业务基本是写入后只读，数组加 GIN 索引的组合确实又简洁又高效。还有一个可以直接拿去用的小技巧：如果要从关联表往数组字段里回填数据，一条 UPDATE 加array_agg子查询就能搞定，千万别在应用层循环挨个 UPDATE。把集合操作思维从应用层搬到 SQL 层，你会发现很多疑难杂症其实根本不存在。