Java + PostgreSQL CRUD 实战：驱动选型、连接池与事务边界全解析

先说结论：Java配PostgreSQL做CRUD，真正值得你花时间的不是那几个增删改查方法本身，而是驱动版本怎么选、连接怎么配、事务边界怎么划、批量操作怎么写。这四个点我在这几年的开发里都实打实踩过坑，这篇就按项目落地顺序，从环境准备讲到问题排查，把一套能直接抄走的方案完整写出来。

这篇内容适合正在学JDBC的初级工程师、从MySQL往PostgreSQL迁移的老开发，以及想要在团队内搭建一套通用CRUD模板的后端负责人。全文以实际可运行为准，代码都验证过，参数都给了依据，你跟着走一遍就能跑通。

1. 环境准备与版本选型

1.1 PostgreSQL版本怎么选

很多新手上来就问“下载哪个版本”，其实这个问题得分角色看。如果你是在本地开发机上跑，直接选最新的稳定版就好，比如当前的16.x或17.x。如果你是在生产环境部署，那就选上一个稳定大版本，等社区把新版本的坑踩得差不多了再升级。别一上来就追最新，数据库这种基础组件，稳定比新功能重要得多。

以我个人的实际体会，PostgreSQL 16是个分水岭。它把逻辑复制的性能优化做上去了，还加了pg_stat_io这样的视图，排查IO问题比之前舒服很多。17版本在真空处理和WAL锁方面又有改进，但如果你不关心这些底层细节，16和17对你写Java CRUD来说几乎没有差别。真正影响你的，是JDBC驱动和数据库大版本之间的兼容性，这一点后面专门讲。

选择版本时还要注意字符集和排序规则。PostgreSQL初始化数据库时默认的locale跟你的操作系统强相关，如果你用Docker镜像，官方镜像默认是C或POSIX排序，写入中文数据时排序可能不符合预期。建议在docker run或initdb时明确指定-e LANG=C.UTF-8或--locale=C.UTF-8，避免后面排序、索引出现诡异行为。

1.2 安装方式对比与选择

常见安装方式有四种：Docker容器、Windows安装包、Linux发行版包管理器、源码编译。这四种我全用过，各自适用场景完全不同。

Docker方式我用的最多。一条命令就是一套完整数据库，不用的时候直接删掉容器，完全不污染本机环境。但有个前提：容器数据必须挂载到宿主机，否则容器一删数据全没。我的习惯是这样：

docker run -d \ --name pg16 \ -e POSTGRES_USER=test \ -e POSTGRES_PASSWORD=test123 \ -e POSTGRES_DB=mydb \ -e LANG=C.UTF-8 \ -p 5432:5432 \ -v /data/pg16:/var/lib/postgresql/data \ postgres:16

这里-v挂载的是数据目录，宿主机上的/data/pg16会在容器首次启动时被初始化成PostgreSQL数据目录。如果你用的是macOS或Windows的Docker Desktop，磁盘性能会比本机原生安装差一些，但日常开发写CRUD完全感觉不出来。

源码编译这个路子，我劝你非必要不碰。虽然网上很多教程强调“可以完全定制”，但代价是你得自己解决readline、zlib、openssl这些依赖，编译一次少说二十分钟，而且后续维护还是自己扛。真正的价值场景是离线内网环境，连yum源都没有的机器上，你用源码包在另一台同架构机器编译出二进制再拷过去，这个方案反而最省事。

1.3 JDK与构建工具准备

Java这边，我建议直接用JDK 17或21。Spring Boot 3.x强制要求JDK 17以上，MyBatis-Plus 3.5.x在JDK 17下跑得很稳。之前我试过在JDK 8上跑新版本的PostgreSQL驱动，倒是能跑，但要么是驱动不支持新特性，要么是老依赖拖后腿，整体体验不值当。

JDK 21的主要价值在于虚拟线程，如果你用Tomcat或Jetty的虚拟线程模式，并发能力会有质的提升。不过对普通CRUD项目来说，JDK 17已经超过够用的标准了，真正的瓶颈通常在数据库侧而不是应用侧。

构建工具我推荐Maven，虽然Gradle的构建速度更快，但Maven在依赖管理和可读性上更直观，绝大多数Spring Boot项目的脚手架也默认用Maven。后面所有配置我都基于Maven写，你照着建项目就行。

2. 连接层配置与驱动选型

2.1 JDBC驱动版本匹配

Java连PostgreSQL的官方驱动是org.postgresql:postgresql，groupId是org.postgresql。版本选择参考官方兼容矩阵：驱动42.3.x支持PostgreSQL 10到14，42.6.x支持到15和16，42.7.x则全面适配16和17。别老想着用最新版，稳定够用就好。

我的建议是：数据库16配驱动42.7.4以上，数据库15配42.6.0以上。驱动是向后兼容的，旧驱动连新数据库偶尔会有“认证方式不支持”之类的问题，但新驱动连旧数据库基本没问题。直接看Maven中央仓库的Release版本，挑最新的42.7系列就是个稳妥选择。

<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.7.4</version> </dependency>

这里有个经验：JDBC驱动除了postgresql这个主包之外，没有额外依赖。它不像MySQL驱动那样还需要单独的protobuf或gson，所以关联依赖非常干净，这算是PostgreSQL驱动做得好的一点。

2.2 JDBC URL与关键参数

连接串的格式长这样：

jdbc:postgresql://localhost:5432/mydb?currentSchema=public&applicationName=myapp&connectTimeout=5&socketTimeout=30

每个参数背后都有实际意义。currentSchema指定默认schema，如果你想连的不是public而是别的schema，必须显式设置它，否则所有SQL里的表名都得加前缀。applicationName是给DBA用的，数据库的pg_stat_activity视图里能直接看到当前连接属于哪个应用，排查问题的时候一眼就能定位，比在服务端抓IP方便多了。

connectTimeout我习惯设成5秒。默认值如果是0，表示无限等待，一旦网络环境抖动，业务线程会全部卡在建立连接上，那场面非常难看。socketTimeout设成30秒，防止某条SQL执行时数据库端挂了而客户端傻等。

还有一个容易被忽略的参数是prepareThreshold。PostgreSQL驱动默认在同一个连接上执行同一个SQL五次之后，才会把这个SQL切换成服务端预编译语句。对频繁执行的写操作来说，这个机制能显著降低SQL解析开销，但如果你在分析慢日志时发现大量unnamed prepared statement，可以把这个值改成1，强制第一次执行就走服务端预编译。需要注意的是，这也会让连接占用的服务端缓存更大，所以在一般场景下保持默认就够。

2.3 连接池HikariCP的配置要点

在Java里裸写JDBC连接是可行但不可持续的。每次建立连接都要经过TCP握手、认证、参数协商，一个连接的生命周期可能上百毫秒，这个开销在业务请求里是白白浪费的。所以生产环境必须用连接池，HikariCP是Spring Boot的默认选择，也是目前性能最好的池子。

spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 600000 max-lifetime: 1800000 connection-timeout: 3000

这四个关键参数我一个个说。maximum-pool-size决定池子里最多有多少个连接，公式是核心并发线程数 × (1 + 平均等待时间 / 平均执行时间)。如果你的服务是20个核心线程、每个请求平均耗10ms、数据库查询平均3ms，那差不多就是20×1.5=30。但一般业务系统用不着算这么精细，20到30就足够了，池子开太大反而浪费数据库资源。minimum-idle是池子至少保持多少空闲连接，应对突发流量，设成5就行。max-lifetime是连接的最大存活时长，一定要小于数据库端的wait_timeout或清理策略，我们把它设成30分钟，能让数据库更平滑地回收连接。connection-timeout是客户端从池子里获取连接的最长等待时间，我设了3秒，一旦池子不够用而请求又等不到连接，就直接抛异常暴露问题，而不是无限阻塞。

连接池最常见的坑是“连接泄漏”。代码里拿到连接后不往池子里还，随着请求堆积把池子耗尽，然后用HikariPool-1 - Connection is not available, request timed out的报错提醒你。对付这个问题的有效手段有两个：一是代码里务必做到try-with-resources，二是开启HikariCP的泄漏检测leak-detection-threshold: 60000，连接空闲超过60秒不归还就打印堆栈。这个参数对排查问题帮助极大，生产上建议保留观察。

3. CRUD核心实现

3.1 建表与基础数据准备

先建一张最简单的用户表作为示例。PostgreSQL的自增主键推荐用generated always as identity而不是serial。从功能上两者都能实现自增，但identity是SQL标准语法，权限控制和类型约束更严格，已经是社区推荐的写法。

create table t_user ( id bigint generated always as identity primary key, username varchar(64) not null, email varchar(128) not null, nickname varchar(64), age int, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); comment on table t_user is '用户表'; comment on column t_user.id is '主键ID'; comment on column t_user.username is '用户名';

这里有两个值得说的习惯。第一，created_at和updated_at都用timestamptz类型，推荐用带时区的时间戳。Java端的Instant和OffsetDateTime可以无歧义地映射，避免“明明是同一时刻，库里存的时间却跟你本地差了八小时”的尴尬。第二，字段名用全小写加下划线风格，因为PostgreSQL会把不带引号的标识符折叠成小写，Java实体里用username直接对应username，两边都不用做特殊处理。

3.2 JDBC原生方式增删改查

先来一段最基础的JDBC代码，不带任何框架，明白底层之后你会发现后面用ORM只是换了个封装方式。

新增并返回主键：

String sql = """ insert into t_user(username, email, nickname, age) values (?, ?, ?, ?) """; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { ps.setString(1, "zhangsan"); ps.setString(2, "zhangsan@example.com"); ps.setString(3, "张三"); ps.setInt(4, 25); ps.executeUpdate(); try (ResultSet rs = ps.getGeneratedKeys()) { if (rs.next()) { Long id = rs.getLong(1); System.out.println("生成的ID: " + id); } } }

prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)这个重载方法一定要写，否则你拿不到数据库自增生成的ID。PostgreSQL的JDBC驱动内部其实是用INSERT ... RETURNING id帮你实现的，但驱动细节你不用关心，按这个写法来就行。

查询：

String sql = """ select id, username, email, nickname, age, created_at from t_user where username = ? """; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, "zhangsan"); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { Long id = rs.getLong("id"); String username = rs.getString("username"); OffsetDateTime createdAt = rs.getObject("created_at", OffsetDateTime.class); // 组装实体... } } }

注意created_at用rs.getObject("created_at", OffsetDateTime.class)读取，比getTimestamp再手动转更省事，时区信息也不会丢。

更新与删除：

String updateSql = """ update t_user set nickname = ?, updated_at = now() where id = ? """; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(updateSql)) { ps.setString(1, "新昵称"); ps.setLong(2, 1L); int rows = ps.executeUpdate(); if (rows == 0) { // 没有匹配的记录，业务上要处理这种情况 } }

executeUpdate返回的是受影响行数。rows == 0可能是ID不存在，也可能是数据没变化。如果你用了updated_at = now()这种每次都刷新的字段，那不存在和没变化是能区分出来的。如果没更新任何字段，PostgreSQL也会返回0。

事务边界，这是CRUD里最容易被新手忽略的一环。默认情况下每条SQL都是自动提交的，但真实业务里的“用户注册”往往是“插入用户表+插入账户日志+更新统计计数”这种多步骤操作，任何一个步骤失败都要整体回滚。如果全部写在同一个连接里，你就可以控制事务：

try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(false); try { // execute insert ... // execute update ... conn.commit(); } catch (Exception e) { conn.rollback(); throw e; } }

事务边界规则很简单：要么全部成功，要么全部回滚。这里的常见失误是把conn.commit()写在finally块的close()之前，或者根本没调commit，等连接池回收连接时驱动才隐式回滚，最终表现为“数据偶尔写入、偶尔丢失”。我的建议是：小事务（几十毫秒内完成的）就在方法体内明确提交；长事务（超过几秒）尽量拆小，避免长时间持锁影响并发。

批量插入：

String sql = """ insert into t_user(username, email, nickname, age) values (?, ?, ?, ?) """; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { for (User user : userList) { ps.setString(1, user.getUsername()); ps.setString(2, user.getEmail()); ps.setString(3, user.getNickname()); ps.setInt(4, user.getAge()); ps.addBatch(); } int[] results = ps.executeBatch(); }

注意executeBatch()返回的是每批语句的执行结果数组，Statement.SUCCESS_NO_INFO表示执行成功但没有具体的行数。批量插入一次别太大，建议每500到1000条作为一个批次，太多的话驱动端Buffered内存和数据库端WAL压力都会上来。

3.3 基于MyBatis-Plus实现通用CRUD

如果项目里有若干张表都要做增删改查，而你又不想为每张表都写一遍Mapper XML，那MyBatis-Plus就是当前Java生态里最顺手的方案。社区里最近讨论的“通用CRUD服务”，本质上就是MyBatis-Plus的BaseMapper和IService组合。

首先引入依赖：

<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-spring-boot3-starter</artifactId> <version>3.5.7</version> </dependency>

注意如果你用的是Spring Boot 3.x，必须引入mybatis-plus-spring-boot3-starter，而不是老的mybatis-plus-boot-starter。这个坑我踩过，老版依赖在Spring Boot 3下会直接启动失败。

然后定义实体：

@TableName("t_user") public class User { @TableId(type = IdType.AUTO) private Long id; private String username; private String email; private String nickname; private Integer age; @TableField(fill = FieldFill.INSERT) private LocalDateTime createdAt; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updatedAt; // getter/setter/toString... }

@TableName把实体映射到t_user表，@TableId(type = IdType.AUTO)对应数据库的自增主键。这里要特别提醒：在MyBatis-Plus里千万别把id的type配置成IdType.INPUT，除非你打算自己给ID赋值，否则插入时主键字段会被当成默认值0处理，接着就会触发主键冲突。

接下来定义Mapper接口：

public interface UserMapper extends BaseMapper<User> { }

就一行，增删改查单个对象的基础方法全都有了：selectById、selectList、insert、updateById、deleteById。如果你需要根据某个字段查询，就用QueryWrapper：

List<User> users = userMapper.selectList( new LambdaQueryWrapper<User>() .eq(User::getUsername, "zhangsan") .like(User::getNickname, "张") .orderByDesc(User::getId) .last("limit 10") );

LambdaQueryWrapper最大的价值是类型安全。你写User::getUsername时编译器就能帮你确认字段存在，如果数据库字段改名了，编译期就能发现。这里有个常用技巧：模糊查询时，like默认会在参数两边加%，你不需要自己在参数里拼%。而last("limit 10")是直接拼在SQL末尾的，如果传入的是用户可控的值，必须白名单校验，否则就是SQL注入点。

如果你要写一个“无状态”的通用CRUD服务，核心思路是把这些操作封装成一个通用基类：

public interface BaseCrudService<T> extends IService<T> { } public class BaseCrudServiceImpl<M extends BaseMapper<T>, T> extends ServiceImpl<M, T> implements BaseCrudService<T> { }

这样每张表只需要继承基类，单表的所有CRUD能力自动具备。所谓无状态，是指这个服务内部不持有业务状态，每次调用都只依赖传入的参数，天然支持高并发水平扩展。在一个后台管理系统的项目里，这种设计能帮你把几十张表的增删改查代码压缩到只有实体类和Mapper接口，省下的精力可以用来处理真正的业务逻辑。

但要注意，ServiceImpl自带的方法适合单表操作，如果你的SQL里有JOIN、子查询或者复杂的聚合，还是得在Mapper XML里写自定义SQL，别硬用MyBatis-Plus的Wrapper硬凑，那种SQL写完会非常难维护。我的经验是：单表CRUD直接继承通用服务，多表关联查询走自定义XML，两者结合才是最佳实践。

3.4 PostgreSQL特有的CRUD操作：Upsert

在CRUD之外，PostgreSQL有一个MySQL用户特别羡慕的语法：INSERT ... ON CONFLICT DO UPDATE，也就是常说的Upsert。它的意义在于：一条SQL完成“存在就更新，不存在就插入”的原子操作，不需要先查再判断。

String sql = """ insert into t_user(username, email, nickname, age) values (?, ?, ?, ?) on conflict (username) do update set email = excluded.email, nickname = excluded.nickname, age = excluded.age, updated_at = now() """;

这里的on conflict (username)是冲突判断的约束条件。在业务上，比如用户通过第三方账号登录时先按username尝试更新资料，如果没有这个用户则插入，用一条语句就能解决，且天然没有并发竞态问题。注意excluded前缀，它代表你本次试图插入的那一行数据。如果冲突时你想完全保留旧数据，那就不写do update，只写do nothing。

这种方式特别适合日志流水表、批量同步任务这类场景。写入并发高、重复提交频繁时，Upsert能省掉一条查询SQL，同时避免在代码里做“查重-插入-更新”三步操作造成的竞态窗口。

4. 常见问题与排查技巧实录

4.1 连接失败类问题

问题现象：Connection refused或The connection attempt failed。

排查步骤我总结成一句话：先确认数据库进程在跑，再确认端口通不通，最后看防火墙和Docker端口映射。如果你用Docker部署，最常见的坑是-p 5432:5432没映射对，或容器已经因为数据目录权限问题退出了。

docker ps docker logs pg16 --tail 100

如果日志里出现FATAL: data directory "/var/lib/postgresql/data" has invalid permissions，说明宿主机挂载目录的属主不对。解决办法是给目录授权：chown -R 1000:1000 /data/pg16，PostgreSQL官方镜像里默认用UID 1000跑数据库进程，直接给权限就行。

问题现象：Password authentication failed for user "xxx"。

这个报错一般不是密码错了，而是用户、数据库、认证方式三者里的某一件事情没对上。PostgreSQL默认的pg_hba.conf里本地连接用scram-sha-256认证，如果你用trust或md5，在较新版本里会直接拒绝。检查认证配置时，关键是看pg_hba.conf，不是看密码本身：

local all all scram-sha-256 host all all 0.0.0.0/0 scram-sha-256

问题现象：The driver has not received any packets from the server。

这属于连上了但没完成握手就超时了。常见原因一是服务器listen_addresses没配置成*，只监听了localhost；二是你在JDBC URL里用的主机名解析到了IPv6的::1，但PostgreSQL只监听了IPv4。遇到这类问题时，先用telnet或nc直接验证端口可通，能快速缩小范围。

4.2 驱动类加载问题

老教程会让你写Class.forName("org.postgresql.Driver")，这在JDBC 4.0以后的版本里已经不需要了。驱动JAR包在META-INF/services/java.sql.Driver文件里自动声明了驱动类，DriverManager会通过SPI机制自动加载。如果你手动写Class.forName，在大多数情况下也没事，但在某些容器环境里可能会导致驱动被加载两次，出现奇怪的Multiple drivers found警告。

真正需要检查驱动类加载的场景是：你用了瘦身工具（如Spring Boot的spring-boot-maven-plugin的repackage）或者本地依赖冲突，把驱动包从最终产物里排除了。这时报错通常是No suitable driver found for jdbc:postgresql://...。解决办法是检查mvn dependency:tree，确认postgresql驱动没有被别的东西排掉。

4.3 时区与时间类型映射问题

问题现象：java.time.LocalDateTime写入timestamptz字段后，读出来差8小时。

这几乎是所有从MySQL转PostgreSQL的团队都会遇到的问题。原因在于：timestamp with time zone存储的是绝对时间点，写入时会把你传入的时间按会话时区转成UTC存储，读取时又会按会话时区转回来。如果Java代码里用的是LocalDateTime（不携带时区信息），驱动会默认把系统默认时区套上去转换，一旦应用服务器设置了Asia/Shanghai，数据库会话时区是UTC，就会产生偏差。

我给的建议是统一规范：Java代码和时间交互一律用OffsetDateTime或Instant，JDBC URL里加上TimeZone=Asia/Shanghai，数据库连接属性里设置serverTimezone=Asia/Shanghai。注意这里不是让数据库存你本地时间，而是让驱动知道你的应用期望什么时区，转换才不会出错。

如果你的表本身只需要记录“哪天”而不是“哪一刻”，干脆用date类型，就彻底避开时区问题了。

4.4 大小写与Schema搜索路径问题

问题现象：relation "t_user" does not exist。

我遇到过好多次把表名写成T_User或"T_User"的情况。PostgreSQL对不带引号的标识符会折叠成小写，所以T_User实际会变成t_user。如果你的表真的是大写字母开头的，在Java代码里就得给SQL加双引号，查询自己把自己绕晕。

问题现象：能连上数据库，但SQL执行时找不到表。

当你连接的数据库里存在多个schema，而表不在public下时，就会出现这个现象。解决方案在JDBC URL里加currentSchema=my_schema，或者在连接后执行SET search_path TO my_schema。推荐前者，简单直接。

4.5 批量慢与写入性能问题

如果发现批量插入几千条数据耗时惊人，第一步要确认是不是在循环里单条提交。单条commit的开销在PostgreSQL里非常高，因为每次提交都要刷WAL日志。最简单的优化就是addBatch加executeBatch，把几百条攒在一个事务里提交。如果还不够快，可以用PostgreSQL的COPY协议，通过org.postgresql.copy.CopyManager实现流式导入，10万条数据从几秒降到几百毫秒是完全可能的。

CopyManager copyManager = pgConnection.getCopyAPI(); String copySql = "COPY t_user(username, email, nickname, age) FROM STDIN WITH (FORMAT csv)"; copyManager.copyIn(copySql, new InputStreamReader(inputStream));

COPY是PostgreSQL性能最好的写入方式，但它不做行级冲突检测，适合数据导入和离线同步，不适合在线业务写主表。这点需要把握好。

5. 安全与查询性能的进阶细节

5.1 SQL注入防护的两条铁律

CRUD里的每个参数都可能成为注入点。JDBC里所有用户输入都必须走PreparedStatement的参数占位符，而不是字符串拼接。${}在MyBatis里是直接拼接，#{}才是预编译参数。不要为了省事把排序字段、动态列名也搞成占位符拼接，因为表和列名没法参数化，这时必须用白名单校验。

举一个实际案例。动态排序列，你收到的排序字段是sortField=email，如果直接拼进SQL里，攻击者传一个email; drop table t_user; --就能让你当场社会性死亡。白名单的做法是后端先定义允许排序的字段集合：

private static final Set<String> SORT_WHITELIST = Set.of("id", "username", "email", "created_at"); if (!SORT_WHITELIST.contains(sortField)) { throw new IllegalArgumentException("非法排序字段"); }

这样做之后才是安全的。

5.2 利用PreparedStatement缓存重复SQL

PostgreSQL的会话有个“语法分析缓存”，同一个SQL文本重复执行时，服务端可以跳过重复解析。JDBC驱动的prepareThreshold参数控制了“同一个连接上同一条SQL执行多少次后转为服务端预编译”。在高频CRUD场景里，把prepareThreshold从默认的5改成1，能显著减少服务端的解析开销，但代价是占用的服务端内存会上升。批量接口和热点查询建议开启，一次性的报表SQL就没必要了。

5.3 慢查询定位

写完CRUD之后，迟早会遇到“某个接口突然变慢”。定位手段第一步是在数据库侧开慢查询日志，PostgreSQL的log_min_duration_statement要设一个阈值，比如500毫秒：

alter system set log_min_duration_statement = 500;

这个值是全局配置，改完需要重启或者pg_reload_conf()。别在生产环境随便打开，日志量会特别大。临时排查可以用EXPLAIN (ANALYZE, BUFFERS)单独跑那条慢SQL，重点看有没有Seq Scan、有没有对索引列的隐式类型转换。比如你查询where username = ?，但username列类型是varchar(64)，Java里传的是String，这没问题；如果你传的是数字、或者后端把字符串转成了char，就会导致索引失效。这个坑在隐式类型转换那一栏特别明显。

另外推荐安装pg_stat_statements扩展，它是数据库内置的SQL统计插件，能按总耗时排序展示你系统里最耗时的SQL集合，这对定位“最该优化的SQL”非常有帮助。

5.4 连接数规划与池参数最佳实践

PostgreSQL默认最大连接数是100，这个数字对于小型团队的系统来说往往够用。很多人一遇到连接不够就调大max_connections，这是治标不治本。每个PostgreSQL连接都需要独立进程和内存，连接数开到500以上不仅没好处，反而会因上下文切换拖垮整库性能。

正确的思路是：后端连接池大小要克制，宁可让请求在应用层排队，也别让数据库层并发爆炸。对于大多数业务系统，每个实例20个连接足够。如果实例很多台，总连接数要控制在数据库可用连接数以内，同时还要给DBA的维护连接留出余量。连接池的maximum-pool-size和数据库的max_connections之间的关系，建议按下图这样一个简单模型来思考：应用发起的并发请求会先到连接池排队，而不是直接打到数据库。连接池是缓冲，数据库是最终执行者。池子太大，数据库并发高，锁竞争就多；池子太小，应用请求超时，用户体验差。所以池子大小要配合响应时间目标来调整，而不是随手填个50、100了事。

我个人的经验是：先设20，压测看接口的P95时延，如果数据库空闲但客户端经常报connection-timeout，说明池子偏小，逐步往上加，同时观察数据库CPU和锁等待；如果数据库CPU已经很高了，说明池子太大或SQL写得不行，这个方向比反复调参数更值得投入。

实战心得与避坑清单

最后分享几点我长期实践下来的体会。第一条，连接参数不要用默认值一拉到底。JDBC URL里的connectTimeout和连接池的connection-timeout务必显式配好，否则在一些极端网络下，你面对的是几十个线程集体卡死的局面。第二条，getGeneratedKeys拿自增主键这个操作，在任何ORM里都要确认映射方式。MyBatis-Plus用IdType.AUTO，原生JDBC用RETURN_GENERATED_KEYS，这两个写错，后面做关联查询时数据对不上。第三条，事务不是越多越大越好。一个事务里塞几百条SQL，一旦中途失败只能全部回滚，不仅浪费已执行的工作，数据库锁的持有时间也会让并发直线下降。第四条，在PostgreSQL上做CRUD时，养成看执行计划的习惯。一行EXPLAIN (ANALYZE, BUFFERS)远比你在Java代码里写各种日志更容易发现问题，索引有没有生效一目了然。

还有一个小技巧特别值得记：PostgreSQL的\watch命令可以每隔几秒重新执行上一条SQL，调试计数、监控某张表行数变化时，开两个终端，一个执行select count(*) from t_user; \watch 1，另一个跑测试程序，你会直观看到数据是怎么变化上去的。这个技巧在验证批量插入和事务回滚时特别好用。

CRUD写起来不难，但把每一处细节都处理好，服务的稳定性就会有质的区别。这套方案是我自己在多个项目里反复验证过的，你照着搭一次，以后换任何一张表、任何一个新项目，都能很快跑起来。