04 开发者必备 PostgreSQL SQL 与数据库编程能力

对很多应用开发者来说，数据库只是“存取数据的地方”。这正是系统后来越来越慢、越来越难维护的重要原因。真正高效的 PostgreSQL 使用方式，是尽可能让数据库完成它擅长的集合计算、一致性控制和近数据处理，而不是把大量数据搬到应用层再做二次加工。

先建立一个判断标准

下面这类事情，优先考虑交给 PostgreSQL:

批量过滤、聚合、排序、分组
基于唯一约束的幂等写入
一次事务内的一致性更新
与数据强相关的派生值生成
审计、回写、状态迁移等数据库内逻辑

下面这类事情，更适合留在应用层:

复杂业务流程编排
跨系统调用
长耗时外部交互
高度可变、难以稳定抽象的业务规则

这条边界一清楚，数据库编程就不容易走偏。

开发者最该先学会的 SQL 能力

1. 批量写入和幂等写入

很多系统性能差，不是数据库不行，而是应用一行一行写。

应该优先掌握:

insert into users (id, name, email)
values
  (1, 'a', 'a@test.com'),
  (2, 'b', 'b@test.com')
on conflict (id) do update
set name = excluded.name,
    email = excluded.email;

这比应用层循环更新更稳，也更容易保证幂等。

2. `returning`

写入后立即拿结果，是 PostgreSQL 很实用的能力。

insert into orders (user_id, status, amount)
values (1001, 'pending', 99.00)
returning id, created_at;

别再额外查一次。

3. 窗口函数

很多“排行榜、去重取最新、按组排序”问题，如果不会窗口函数，就会在应用层写出一堆低效代码。

select *
from (
  select
    o.*,
    row_number() over (partition by user_id order by created_at desc) as rn
  from orders o
) t
where rn = 1;

4. 公共表达式和可读性

with 不是性能魔法，但它是很好的可读性工具。复杂 SQL 如果分层写清楚，后面调试和解释都会轻松很多。

函数、存储过程、触发器什么时候值得用

适合用函数或过程的场景

多个服务都要复用同一段数据库逻辑
一组写操作必须在数据库内部一次完成
需要把某些规则放到最靠近数据的位置

不适合滥用的场景

业务流程变化很快
涉及大量外部调用
团队没有数据库代码审查和版本管理习惯

原则很简单: 数据库编程要服务于“降低复杂度”，而不是制造第二套难维护的应用系统。

触发器最容易出的问题

触发器的危险不在于它不好用，而在于它“太隐蔽”。

常见问题包括:

应用写一条语句，却触发了一串隐式动作
批量导入时被触发器拖慢
审计逻辑藏在触发器里，团队并不知道

所以，触发器适合做少量、明确、稳定的事情，比如更新时间戳、轻量审计、简单派生字段。超过这个边界，就该谨慎。

开发者写 SQL 时最值钱的习惯

用集合思维代替循环思维
用约束保证正确，而不是只靠代码约定
用一条高质量 SQL 代替多次往返
把可读性当成性能优化的前提

SQL 能力不是“会写语法”，而是能不能把数据问题变成数据库擅长解决的问题。这恰恰是 PostgreSQL 最值得学的地方。

03 Schema and Access 05 Transactions and Locks