postgresql - 在 Postgres 中的索引上插入具有 ExclusiveLock 的块

Question

数据库 - 谷歌云 SQL，Postgresql 11。

我们有一个分区表，其中一个分区是最活跃的，有 4 bln 行 (3Tb)。该表有几列作为主键，其中一列json带有gin索引。

CREATE TABLE messages (
    key_col1,
    key_col2,
    message_type text NOT NULL,
    message jsonb NOT NULL,
    CONSTRAINT messages_pkey PRIMARY KEY (key_col1, key_col2, message_type)
)
PARTITION BY LIST (message_type);

CREATE INDEX gin_index ON messages USING gin (message);

CREATE TABLE messages_type_1 PARTITION OF messages FOR VALUES IN ('MessageType1')

该表经常插入到，与ON CONFLICT DO NOTHING。

最近，一些插入开始阻塞。它持续约 25 分钟。然后它解决了，所有已堆积的插入都完成了，而其他一些插入则被阻塞了。

这个查询

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE MODE <> 'RowExclusiveLock'
ORDER BY a.query_start;

显示这些锁

| relation  | transactions |      mode     | granted | query         | query_start
+---------------------------------------------------------------------------------------------
 [NULL]        [NULL]        ExclusiveLock   true     INSERT INTO ...  2020-07-28 19:20:37
 [NULL]        [NULL]        ExclusiveLock   true     INSERT INTO ...  2020-07-28 19:20:37
 [NULL]       1480644902     ExclusiveLock   true     INSERT INTO ...  2020-07-28 19:20:37
 gin_index     [NULL]        ExclusiveLock   true     INSERT INTO ...  2020-07-28 19:20:37

这些是 autovacuum 设置

autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold    50
autovacuum_freeze_max_age   200000000
autovacuum_max_workers  3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime  1min
autovacuum_vacuum_cost_delay    20ms
autovacuum_vacuum_cost_limit    -1
autovacuum_vacuum_scale_factor  0.2
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1

vacuum_cleanup_index_scale_factor   0.1
vacuum_cost_delay   0
vacuum_cost_limit   1000
vacuum_cost_page_dirty  20
vacuum_cost_page_hit    1
vacuum_cost_page_miss   10
vacuum_defer_cleanup_age    0
vacuum_freeze_min_age   50000000
vacuum_freeze_table_age 150000000
vacuum_multixact_freeze_min_age 5000000
vacuum_multixact_freeze_table_age   150000000

这些排他锁正常吗？我该如何进一步调试呢？

标签： postgresqllocking