8个多数人不知道的PostgreSQL实用技巧，工作事半功倍

分享一些日常工作中能提升效率和代码质量的PostgreSQL技巧。PostgreSQL 功能强大，但很多开发者可能只接触了冰山一角。掌握下面这些技巧，能让SQL代码更简洁、性能更高。

在深入这些技巧之前，一个顺手的本地开发环境是必不可少的。推荐使用 ServBay，它不仅可以一键安装和管理PostgreSQL，还支持多个不同版本的数据库实例同时运行。

开发者轻轻松松就能PostgreSQL 14和16之间切换测试，无需要复杂的配置和输入命令行。对于想要动手实践下面这些技巧的朋友来说，这无疑是个便利的工具。

好了，我们开始正题。

技巧一：用 EXPLAIN ANALYZE 透视查询性能

当一条SQL查询变慢时，第一反应是什么？猜测哪个字段没加索引？EXPLAIN ANALYZE 命令能能够让开发者直接看到PostgreSQL的执行计划和实际的执行细节。

代码示例：

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@servbay.com';

为什么这样更好？

这个命令会告诉你查询的每一步成本有多高，是否使用了索引，扫描了多少行数据，以及时间花费在哪里。这是诊断慢查询的第一步，也是最重要的一步。这个能显示出问题是出在全表扫描（Full Table Scan）还是连接（Join）上。养成在优化前先分析的习惯，能避免开发的时候走很多弯路。

技巧二：自动捕获慢查询

EXPLAIN ANALYZE 是手动诊断的利器，但我们还需要一个机制来主动发现问题。通过设置 log_min_duration_statement，就可以让PostgreSQL自动记录所有超过指定执行时间的查询。

代码示例：

-- 对当前数据库设置，所有执行超过250毫秒的查询都会被记录到日志
ALTER DATABASE postgres SET log_min_duration_statement = '250ms';

为什么这样更好？

这是一种哨兵机制。在开发或生产环境中，开发者不可能手动检查每一条查询。设置一个合理的阈值（比如200ms或500ms），就能自动捕获那些潜在的性能瓶颈，可以在问题扩大化之前就介入处理。

技巧三：更优雅地比较多列

当 WHERE 子句中有多个 AND 条件时，代码会显得有些冗长。PostgreSQL支持元组（tuple）比较语法，可以让查询更紧凑，可读性也更高。

场景一：多个 AND 条件

传统写法：

SELECT country, company, department
FROM suppliers
WHERE country = 'America' AND company = 'ServBay' AND department = 'Building';

更优雅的写法：

SELECT country, company, department
FROM suppliers
WHERE (country, company, department) = ('America', 'ServBay', 'Building');

场景二：多个 OR 组合条件

传统写法：

SELECT country, company, department
FROM suppliers
WHERE department = 'Building' AND (
  (country = 'America' AND company = 'ServBay') OR
  (country = 'Canada' AND company = 'Mytest')
);

更优雅的写法：

SELECT country, company, department
FROM suppliers
WHERE department = 'Building' AND (country, company) IN (('America', 'ServBay'), ('Canada', 'Mytest'));

为什么这样更好？

代码不只是给机器执行的，更是给人看的。这种元组语法将相关的比较条件组织在一起，意图更明确，减少了重复的 AND 和 OR，在处理多列组合查询时尤其好用。

技巧四：用 ON CONFLICT 实现优雅的 UPSERT

“UPSERT” (Update or Insert) 是一个常见需求：如果记录存在就更新，不存在就插入。在过去，这需要先 SELECT 查询，再根据结果决定 INSERT 或 UPDATE，这不仅繁琐，还可能引发竞态条件。

代码示例：

INSERT INTO users(id, email)
VALUES (1, 'test@servbay.com')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email;

为什么这样更好？

ON CONFLICT 是原子操作。这意味着整个判断和执行过程不会被其他事务中断，保证了数据的一致性。它将复杂的应用层逻辑下沉到数据库层面，代码更简洁，性能也更好。EXCLUDED 关键字则巧妙地引用了试图插入但发生冲突的数据。

技巧五：用 RETURNING 子句减少数据库往返

在执行 INSERT, UPDATE, 或 DELETE 操作后，我们常常需要获取被操作行的某些信息，比如新插入行的自增 id 或者更新时间。通常的做法是再执行一条 SELECT 语句，但这完全没必要。

代码示例：

INSERT INTO users (name, email)
VALUES ('Alicia', 'alicia@example.com')
RETURNING id, created_at;

-- UPDATE 和 DELETE 同样适用
-- UPDATE users SET last_login = NOW() WHERE id = 1 RETURNING id, last_login;

为什么这样更好？

• 效率提升：将两个网络请求合并为一个，减少了应用与数据库之间的通信开销。
• 避免竞态条件：在一个事务中，如果是先 INSERT 再 SELECT，中间可能有其他事务插入了数据，导致你查回的不是想要的那一行。RETURNING 在同一个操作中返回数据，杜绝了这种可能。

技巧六：不止于全列索引——部分索引与表达式索引

很多人对索引的理解停留在 CREATE INDEX ON table (column)。实际上，PostgreSQL提供了更精细的索引类型，能用更小的代价实现更高的查询效率。

部分索引：

只对表中符合特定条件的行创建索引。比如，我们只关心活跃用户的查询。

CREATE INDEX idx_active_users ON users (last_login)
WHERE is_active = TRUE;

为什么这样更好？ 索引尺寸大大减小，维护成本更低。对于那些只查询表中一小部分数据的场景，性能提升明显。

表达式索引:

对一个表达式或函数的结果创建索引。最常见的场景是大小写不敏感的查询。

CREATE INDEX idx_lower_email ON users (LOWER(email));

为什么这样更好？ 如果没有这个索引，WHERE LOWER(email) = '...' 这样的查询会导致全表扫描。有了表达式索引，查询可以直接利用索引定位数据，速度呈数量级提升。

技巧七：半结构化数据JSON/JSONB

关系型数据库也能灵活处理无固定结构的数据。PostgreSQL 原生支持 JSON 和 JSONB 类型。

代码示例：

-- 查询JSON字段中的某个key
SELECT data->>'name' AS name
FROM users
WHERE data->>'status' = 'active';

为什么这样更好？

JSONB 是 JSON 的二进制优化版本，它在存储时会做一些预处理，查询性能和索引支持都远胜于 JSON。通过 GIN 索引来加速对 JSONB 内部字段的查询，实现了关系型数据库的稳定性和 NoSQL 的灵活性二者兼得。

技巧八：简单列表就用 ARRAY 数据类型

当需要为一个字段存储一个简单的值列表（比如文章的标签、学生的课程）时，常规做法是创建一个关联表。但如果关系不复杂，使用数组类型会更直接。

代码示例：

CREATE TABLE students (id SERIAL, subjects TEXT[]);
INSERT INTO students (subjects) VALUES ('{Math,Science}');

-- 查询选了数学的学生
SELECT * FROM students WHERE 'Math' = ANY(subjects);

为什么这样更好？

对于简单的一对多关系，使用数组可以避免额外的 JOIN 操作，简化了数据库模式和查询语句。当然，这是一种权衡，如果列表元素需要存储更多信息或者关系变得复杂，还是应该回归到规范化的关联表设计。

总结

今天介绍的这些技巧，从性能诊断到代码优化，再到数据建模，覆盖了日常开发的多个方面。通过PostgreSQL内置的强大功能，来大大提升工作效率。

理论千遍，不如动手一遍。如果你正在寻找一个方便的本地环境来演练这些技巧，ServBay 确实是个不错的选择，它能够快速搭建并管理多个PostgreSQL实例。希望这些分享能对你有所启发。