内容提要
文章实测了PostgreSQL频繁执行CREATE TEMP TABLE的隐藏代价:每次建删两列表会写入并删除31行系统目录、创建删除3个文件,导致目录膨胀和死元组;每事务还产生132条共享失效消息,所有会话(含空闲)都须读取。5000个空闲连接下,临时表DDL吞吐从500 TPS降至127–249 TPS,写锁等待占91%。建议将临时表移出热代码,改用CTE或PL/pgSQL变量,并用连接池减少空闲会话。
延伸解读
临时表DDL的固定目录开销
每次创建并删除一个两列临时表,PostgreSQL都会向系统目录插入并删除31行记录,同时创建和移除3个文件(表、TOAST表及其索引)。这些操作与业务数据量无关,属于固定开销。即使没有空闲会话,每事务建删临时表的吞吐也仅为3783 TPS,而会话级保留临时表可达17866 TPS,CTE则高达86997 TPS。这意味着在热代码中频繁使用临时表,会持续消耗目录写入和文件系统操作资源。
空闲连接放大共享失效队列争用
每个事务建删临时表会产生132条共享失效消息,所有后端(包括空闲会话)都必须读取。空闲会话不主动读取,导致队列槽位无法回收;当队列过半时,写入者发送追赶信号,但一次只能唤醒一个会话。在5000个空闲连接下,临时表DDL吞吐从500 TPS降至127–249 TPS,写锁等待占活跃时间的91%。因此,连接池减少空闲会话,对缓解此类争用至关重要。
替代方案与适用边界
文章建议将临时表移出热代码:CTE适用于多数场景,复杂逻辑可用PL/pgSQL变量(如数组和unnest),避免目录变更。若必须保留临时表,应每会话创建一次并使用ON COMMIT DELETE ROWS,避免每事务建删。但该模式每次提交仍取三个ACCESS EXCLUSIVE锁并发送5条失效消息,且需要直连或会话模式连接池;在事务池模式下,临时表只能随事务创建和删除,回到高开销模式。
监控与调优的实操信号
文章给出三个检查点:在活跃会话历史中观察LWLock:SInvalWrite(PostgreSQL 12及更早为SInvalWriteLock);对比pg_stat_activity中idle会话数与max_connections;定期查询pg_stat_sys_tables中pg_class、pg_attribute等目录表的n_tup_del变化,pg_attribute的删除速率可估算临时表建删频率。降低空闲连接可通过连接池、缩小应用连接池及设置idle_session_timeout实现。
Q&A
频繁在事务里创建临时表对PostgreSQL性能影响有多大?
在16 vCPU、无空闲会话的测试中,每个事务创建并删除临时表的模式吞吐为3783 TPS,而每个会话保留一张临时表可达17866 TPS,使用CTE可达86997 TPS。创建删除临时表本身导致约4.7倍的性能下降,而使用临时表(相比CTE)又带来约4.9倍的下降。
CREATE TEMP TABLE 每次会写入多少系统目录行和文件?
对于两列(id int, v text)的临时表,每次创建会插入31行系统目录记录(包括pg_class、pg_type、pg_attribute、pg_depend等),并创建3个文件(表、TOAST表、TOAST索引)。删除时同样删除31行并移除3个文件。
为什么空闲会话会拖慢临时表DDL的吞吐?
因为每个事务的临时表DDL会产生132条共享失效消息,所有后端(包括空闲会话)都必须读取这些消息。空闲会话不主动读取,导致消息在队列中堆积,写进程需要等待LWLock:SInvalWrite锁。在5000个空闲会话下,临时表DDL吞吐从500 TPS降至127-249 TPS,写锁等待占活跃时间的91%。
如何检测数据库中临时表导致的目录膨胀和锁等待?
可以检查三个信号:1) 在活跃会话历史中查看LWLock:SInvalWrite(PG12及更早为SInvalWriteLock);2) 查询pg_stat_activity中state='idle'的会话数与max_connections对比;3) 间隔一分钟查询pg_stat_sys_tables中pg_class、pg_attribute等表的n_tup_del变化,pg_attribute的删除速率除以19约等于每秒删除的临时表数量。
有哪些替代方案可以避免频繁创建临时表?
推荐将临时表移出热代码。简单场景可用CTE替代;复杂逻辑可用PL/pgSQL变量(如select t from t、array()、unnest())避免目录变更;批量插入可用数组参数配合unnest()。如果必须使用临时表,应每个会话创建一次并设置ON COMMIT DELETE ROWS,避免每个事务都创建删除。
临时表对主备复制有什么影响?
在测试中,主库以2000 TPS创建删除临时表持续60秒,备库落后731 MiB WAL(约24秒),需要33秒追赶;若有3000个空闲会话,延迟达1065 MiB。备库回放时需重放目录行、3个ACCESS EXCLUSIVE锁记录、129条失效消息和一次控制文件fsync,回放进程60-64%的CPU用于获取锁。