Lock - extend

The extend event occurs when a backend process is waiting to lock a relation to extend it while another process has a lock on that relation for the same purpose.

The event extend indicates that a backend process is waiting to extend a relation that another backend process holds a lock on while it’s extending that relation. Because only one process at a time can extend a relation, the system generates a extend wait event. INSERT, COPY, and UPDATE operations can generate this event.

When the extend event appears more than normal, possibly indicating a performance problem, typical causes include the following:

• Surge in concurrent inserts or updates to the same table

  There might be an increase in the number of concurrent sessions with queries that insert into or update the same table.

• Insufficient network bandwidth

  The network bandwidth on the DB instance might be insufficient for the storage communication needs of the current workload. This can contribute to storage latency that causes an increase in extend events.

We recommend different actions depending on the causes of your wait event.

First, determine whether there’s an increase in tup_inserted and tup_updated metrics and an accompanying increase in this wait event. If so, check which relations are in high contention for insert and update operations. To determine this, query the pg_stat_all_tables view for the values in n_tup_ins and n_tup_upd fields. For information about the pg_stat_all_tables view, see pg_stat_all_tables in the PostgreSQL documentation.

To get more information about blocking and blocked queries, query pg_stat_activity as in the following example:

SELECT
    blocked.pid,
    blocked.usename,
    blocked.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query,
    blocking.wait_event AS blocking_wait_event,
    blocking.wait_event_type AS blocking_wait_event_type
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
where
blocked.wait_event = 'extend'
and blocked.wait_event_type = 'Lock';

   pid  | usename  |            query             | blocking_id |                         blocking_query                           | blocking_wait_event | blocking_wait_event_type
  ------+----------+------------------------------+-------------+------------------------------------------------------------------+---------------------+--------------------------
   7143 |  myuser  | insert into tab1 values (1); |        4600 | INSERT INTO tab1 (a) SELECT s FROM generate_series(1,1000000) s; | DataFileExtend      | IO

After you identify relations that contribute to increase extend events, use the following techniques to reduce the contention:

• Find out whether you can use partitioning to reduce contention for the same table. Separating inserted or updated tuples into different partitions can reduce contention.

• If the wait event is mainly due to update activity, consider reducing the relation’s fillfactor value. This can reduce requests for new blocks during the update. The fillfactor is a storage parameter for a table that determines the maximum amount of space for packing a table page. It’s expressed as a percentage of the total space for a page. For more information about the fillfactor parameter, see CREATE TABLE in the PostgreSQL documentation.

  Important

  We highly recommend that you test your system if you change the fillfactor because changing this value can negatively impact performance, depending on your workload.