特定PostGIS表插入速度异常缓慢的排查求助

问题背景 #

我使用PostgreSQL 10.10 + PostGIS 2.5搭建了车辆位置表，表结构如下：

Column     |             Type             | Collation | Nullable |                      Default                      | Storage | Stats target | Description
---------------+------------------------------+-----------+----------+---------------------------------------------------+---------+--------------+-------------
 id            | integer                      |           | not null | nextval('seq_carlocation'::regclass) | plain   |              |
 timestamp     | timestamp with time zone     |           | not null |                                                   | plain   |              |
 location      | postgis.geometry(Point,4326) |           |          |                                                   | main    |              |
 speed         | double precision             |           |          |                                                   | plain   |              |
 car_id        | integer                      |           | not null |                                                   | plain   |              |
 timestamp_dvc | timestamp with time zone     |           |          |                                                   | plain   |              |
Indexes:
    "carlocation_pkey" PRIMARY KEY, btree (id)
    "carlocation_pkey_car_id" btree (car_id)
Foreign-key constraints:
    "carlocation_pkey_car_id_fk" FOREIGN KEY (car_id) REFERENCES car(id) DEFERRABLE INITIALLY DEFERRED

通过Python/Django后端以每秒1-5次的频率单条插入位置数据。

问题描述 #

该表的插入操作偶尔耗时数秒甚至数分钟，pg_stat_statements数据证实了这一异常。即使数据库负载极低时，该插入的耗时也未明显降低，但其他同频甚至更高频、需维护更多索引的插入仅需数毫秒。将慢日志中的查询语句单独执行EXPLAIN ANALYZE时，规划与执行时间均远小于1ms，未发现异常。

已尝试的优化措施 #

• 删除了除主键与外键外的所有索引（原本timestamp、location字段均有索引）
• 重建整张表
• 重建序列seq_carlocation
• 每月归档数据（导出至S3并删除表内数据），表内最大数据量为200万条
• 每次归档后执行VACUUM ANALYZE

可能的原因分析 #

1. 外键延迟检查的阻塞 #

外键设置为DEFERRABLE INITIALLY DEFERRED，意味着约束检查会延迟到事务提交时执行。如果Django的插入操作被包裹在长事务中，或者同一事务内存在其他涉及car表的操作（如更新、锁表），事务提交时PostgreSQL需要验证car_id的存在性，若此时car表有行锁或索引扫描阻塞，就会导致插入耗时剧增。

排查方向：

• 出现慢插入时，查询pg_locks表，查看是否有涉及car表的锁等待（如relation类型锁、tuple级锁）
• 检查Django的事务管理逻辑，是否存在不必要的长事务包裹插入操作

2. 序列的锁竞争或缓存问题 #

虽然已重建序列，但PostgreSQL 10默认序列缓存为1（CACHE 1），当并发请求获取序列值时会产生序列锁竞争。即使插入频率不高，若存在其他场景的序列访问（如批量操作），或序列OWNED BY设置异常，也可能导致偶尔阻塞。

排查方向：

• 修改序列缓存大小：ALTER SEQUENCE seq_carlocation CACHE 100;，减少锁竞争次数
• 查看pg_stat_activity，是否有会话处于waiting状态，且wait_event_type为Lock、wait_event为relation

3. PostGIS几何类型的隐式验证开销 #

location字段是geometry(Point,4326)，插入时需要验证几何数据的合法性（如SRID正确性、点格式有效性）。若Django传入的数据存在格式异常，或PostGIS依赖的系统库出现资源竞争，可能导致插入耗时突增。

排查方向：

• 查看PostgreSQL日志，是否有几何类型验证相关的警告或错误
• 在Django后端提前验证几何数据合法性，避免无效数据传入数据库

4. 存储层的IO突发延迟 #

即使数据库负载低，存储层（磁盘、SAN、云存储）可能出现突发IO延迟，比如磁盘碎片、缓存失效、存储节点切换等。PostgreSQL插入需要写入WAL日志和数据文件，IO层延迟会直接导致插入耗时变长。

排查方向：

• 出现慢插入时，用iostat -x 1查看操作系统IO统计，是否有磁盘利用率飙升或响应时间变长
• 检查PostgreSQL WAL相关参数，比如wal_sync_method设置是否合理，checkpoint_timeout是否导致频繁checkpoint（checkpoint会触发大量IO写入）

5. Django ORM的额外开销 #

单独执行SQL很快，但Django ORM可能存在额外操作：

• pre_save/post_save信号量中存在耗时逻辑
• 开启ATOMIC_REQUESTS导致每个请求都在事务中，增加开销
• 对象序列化/反序列化的额外损耗

排查方向：

• 用原生SQL替代Django ORM执行插入，观察是否仍出现慢插入
• 检查Django注册的信号量，是否有不必要的耗时逻辑

内容的提问来源于stack exchange，提问作者waquner