Toast是insert性能杀手？

NickYoung

发布于 2026-07-09 16:33:14

又和开发干了一架！！！一条简单的insert跑了30分钟还未结束，正常运行且未被阻塞，到底是什么原因呢？且看老杨下文分析。

问题现象

业务反馈一个简单的insert跑了半小时以上还没结束，吐槽数据库性能太差。

老杨心里嘀咕，大概率又是锁等待之类的阻塞呗。查看等待事件后，恐怕不是那么简单呦，又一次遇到了OidGen。

psql (18.1)
Type "help" for help.

postgres=# select pid,state,wait_event,wait_event_type,usename, EXTRACT(EPOCH FROM (now()-query_start)), substr(query, 0, 150) from pg_stat_activity where state != 'idle' and EXTRACT(EPOCH FROM (now()-query_start)) > 1;
  pid   |        state        |  wait_event   | wait_event_type | usename |     extract     |                                                                        substr                                                                         
--------+---------------------+---------------+-----------------+-------------------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------
 194603 | active              | OidGen        | LWLock          | ikun    |    11477.398844 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
59450 | active              | OidGen        | LWLock          | ikun    |    11615.275271 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
 167559 | active              |               |                 | ikun    |    14632.717235 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
89479 | active              | OidGen        | LWLock          | ikun    |    17749.547152 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
  63593 | active              |               |                 | ikun    |    17750.637583 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
195777 | active              | OidGen        | LWLock          | ikun    |    16565.933308 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
  21036 | active              | OidGen        | LWLock          | ikun    |    11803.266466 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
91034 | active              |               |                 | ikun    |    11741.360506 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
 174046 | active              | OidGen        | LWLock          | ikun    |    17750.606075 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
153549 | active              |               |                 | ikun    |    11514.392464 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
 151813 | active              | OidGen        | LWLock          | ikun    |    17749.627277 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
189482 | active              |               |                 | ikun    |    11563.017702 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
  46653 | active              | OidGen        | LWLock          | ikun    |    17748.996777 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
132967 | active              |               |                 | ikun    |    11547.572100 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
  18857 | active              | OidGen        | LWLock          | ikun    |    17748.824389 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
184788 | active              | OidGen        | LWLock          | ikun    |    11599.806458 | INSERTINTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_
 131596 | active              | OidGen        | LWLock          | ikun    |    11541.901097 | INSERT INTO public.operation_log ("endtime","opt_id","create_time","update_time","opt_name","country","company_

OidGen这个等待事件，我之前写过一篇[1]。当创建的对象足够多时，再建对象获取oid的时间会比较长，就会有这个等待事件。

不过这次是insert，涉及创建什么对象呢？

先打堆栈看看， heap_insert -> heap_toast_insert_or_update -> toast_save_datum -> GetNewOidWithIndex调用链交代的很清楚，看起来是表有大字段，插入toast元组时获取oid，且在等待LWLock。

pstack 167559
#0  0x00007fdb0f801b3b in do_futex_wait.constprop.1 () from /lib64/libpthread.so.0
#1  0x00007fdb0f801bcf in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2  0x00007fdb0f801c6b in sem_wait@@GLIBC_2.2.5 () from /lib64/libpthread.so.0
#3  0x0000000000725602 in PGSemaphoreLock ()
#4  0x00000000007a9ec4 in LWLockAcquire ()
#5  0x000000000051d163 in GetNewObjectId ()
#6  0x00000000005452a8 in GetNewOidWithIndex ()
#7  0x00000000004a290a in toast_save_datum ()
#8  0x000000000050eb88 in toast_tuple_externalize ()
#9  0x00000000004dddcc in heap_toast_insert_or_update ()
#10 0x00000000004cfd46 in heap_insert ()
#11 0x00000000004db838 in heapam_tuple_insert ()
#12 0x0000000000670e8c in ExecInsert ()
#13 0x000000000067231b in ExecModifyTable ()
#14 0x0000000000645ce2 in standard_ExecutorRun ()
#15 0x00007fdb08ebe62d in pgss_ExecutorRun () from /data/postgresql/lib/pg_stat_statements.so
#16 0x00000000007bee3a in ProcessQuery ()
#17 0x00000000007bf8a4 in PortalRunMulti ()
#18 0x00000000007bfc18 in PortalRun ()
#29 0x00000000007bd7f5 in PostgresMain ()
#20 0x000000000048ed77 in ServerLoop ()
#21 0x0000000000738689 in PostmasterMain ()
#22 0x000000000048fe92 in main ()

为什么插入toast元组会获取oid呢？

这是由于chunk_id字段是oid类型，其值就是调用GetNewOidWithIndex获取。

kundb=> \d+ pg_toast.pg_toast_1143412359
   Column   |  Type   | Storage 
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain
 Owning table: "public.operation_log"
Indexes:
    "pg_toast_1143412359_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Access method: heap

toast的管理是数据库自动进行的，当大字段值大小超过2K左右就会触发toast。

toast相关可以看下灿灿写的这篇[2]

刚好这篇中也提到了这个问题。

走读下代码。在toast_save_datum函数[3]中，有两种场景：

1、普通插入（!OidIsValid(rel->rd_toastoid)）：调用 GetNewOidWithIndex：遍历当前toast表的索引条目从中获取一个未使用的OID

2、重写表场景逻辑（OidIsValid(rel->rd_toastoid)）：

a)尝试复用旧toast值的OID：

oldexternal：原 heap 元组中的 toast 指针（指向旧 toast 表）
VARATT_EXTERNAL_GET_POINTER：安全地提取对齐的结构体
如果旧值的va_toastrelid等于当前表的rd_toastoid，说明来自同一个toast表于是复用旧的 va_valueid
但还要检查这个新 toast 表里是否已经存在该 OID（防止重复插入）如果存在，设置 data_todo = 0，跳过写入 chunk，避免浪费空间

b)无法复用，则生成不冲突的新OID：

如果无法复用旧 OID（如旧值不存在，或来自不同toast表）
则循环生成新 OID，直到找到一个既不在新 toast 表也不在旧 toast 表中存在的OID
toastid_valueid_exists(rel->rd_toastoid, ...)：检查旧 toast 表中是否存在该 OID

 /*
  * Insert the correct table OID into the result TOAST pointer.
  *
  * Normally this is the actual OID of the target toast table, but during
  * table-rewriting operations such as CLUSTER, we have to insert the OID
  * of the table's real permanent toast table instead.  rd_toastoid is set
  * if we have to substitute such an OID.
  */
if (OidIsValid(rel->rd_toastoid))
  toast_pointer.va_toastrelid = rel->rd_toastoid;
else
  toast_pointer.va_toastrelid = RelationGetRelid(toastrel);

/*
  * Choose an OID to use as the value ID for this toast value.
  *
  * Normally we just choose an unused OID within the toast table.  But
  * during table-rewriting operations where we are preserving an existing
  * toast table OID, we want to preserve toast value OIDs too.  So, if
  * rd_toastoid is set and we had a prior external value from that same
  * toast table, re-use its value ID.  If we didn't have a prior external
  * value (which is a corner case, but possible if the table's attstorage
  * options have been changed), we have to pick a value ID that doesn't
  * conflict with either new or existing toast value OIDs.
  */
if (!OidIsValid(rel->rd_toastoid))
 {
/* normal case: just choose an unused OID */
  toast_pointer.va_valueid =
   GetNewOidWithIndex(toastrel,
          RelationGetRelid(toastidxs[validIndex]),
          (AttrNumber) 1);
 }
else
 {
/* rewrite case: check to see if value was in old toast table */
  toast_pointer.va_valueid = InvalidOid;
if (oldexternal != NULL)
  {
   struct varatt_external old_toast_pointer;

   Assert(VARATT_IS_EXTERNAL_ONDISK(oldexternal));
   /* Must copy to access aligned fields */
   VARATT_EXTERNAL_GET_POINTER(old_toast_pointer, oldexternal);
   if (old_toast_pointer.va_toastrelid == rel->rd_toastoid)
   {
    /* This value came from the old toast table; reuse its OID */
    toast_pointer.va_valueid = old_toast_pointer.va_valueid;

    /*
     * There is a corner case here: the table rewrite might have
     * to copy both live and recently-dead versions of a row, and
     * those versions could easily reference the same toast value.
     * When we copy the second or later version of such a row,
     * reusing the OID will mean we select an OID that's already
     * in the new toast table.  Check for that, and if so, just
     * fall through without writing the data again.
     *
     * While annoying and ugly-looking, this is a good thing
     * because it ensures that we wind up with only one copy of
     * the toast value when there is only one copy in the old
     * toast table.  Before we detected this case, we'd have made
     * multiple copies, wasting space; and what's worse, the
     * copies belonging to already-deleted heap tuples would not
     * be reclaimed by VACUUM.
     */
    if (toastrel_valueid_exists(toastrel,
           toast_pointer.va_valueid))
    {
     /* Match, so short-circuit the data storage loop below */
     data_todo = 0;
    }
   }
  }
if (toast_pointer.va_valueid == InvalidOid)
  {
   /*
    * new value; must choose an OID that doesn't conflict in either
    * old or new toast table
    */
   do
   {
    toast_pointer.va_valueid =
     GetNewOidWithIndex(toastrel,
            RelationGetRelid(toastidxs[validIndex]),
            (AttrNumber) 1);
   } while (toastid_valueid_exists(rel->rd_toastoid,
           toast_pointer.va_valueid));
  }
 }

/*
  * Initialize constant parts of the tuple data
  */
 t_values[0] = ObjectIdGetDatum(toast_pointer.va_valueid);
 t_values[2] = PointerGetDatum(&chunk_data);
 t_isnull[0] = false;
 t_isnull[1] = false;
 t_isnull[2] = false;

给新的chunk_id匹配unused OID，需要通过toast索引遍历整个toast表的所有数据条目，当toast表的数据条目比较多时，匹配的过程自然就比较久。

受限于system global oid最大值2^32大约42亿，当toast条目足够大没有unused oid时，insert的进程会一直循环获取，直到有free oid。当命中这个场景时pglog中会有打印“OID candidates have been checked xxxxxxxxxxx times, but no unused OID has been found yet.”

我们这个toast表有28亿条记录，因此遍历一次时间久，所以insert大部分时间都在寻找unused OID。

kundb=# select count(1) from pg_toast.pg_toast_1143412359;
   count    
------------
 2882740871
(1 row)

至此问题非常清晰了，看了下表结构，更窝火了，真踏马...这个业务表有249个字段，全是text类型...

那么优化方案就是清理历史冗余数据，让toast条目数缩减下来；同时建议业务整改表结构。

小结

本篇我们分析了又一起OidGen等待事件，本次的场景是toast数据条目多导致insert插入慢。设计表时要合理，不要在一个表上整太多变长大字段，如果是业务实际需求可以拆分到多个表。

Reference

[3] https://github.com/postgres/postgres/blob/REL_18_0/src/backend/access/common/toast_internals.c#L385

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-12-19，如有侵权请联系 cloudcommunity@tencent.com 删除

toast

本文分享自 PostgreSQL运维之道微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度