是什么导致Postgresql中的单个记录更新非常慢？

Question

我被一个简单的查询所困扰，这个查询耗时太长，无法按主键更新单个行。这种情况只发生在某些行中，而不是每次都发生。它是零星的-例如，它可能会影响5-10分钟的id=182，然后在10分钟后影响id=245。应用程序是无锁的，我使用这 wiki来验证没有锁。当一行受到影响时，这种情况会持续几分钟。

表有~20K项，整个数据库在磁盘上占用~500 on。

PG版本9.6.21

表定义：

create table shift (
    id         serial not null constraint shift_pkey primary key,
    created_at timestamp default now() not null,
    updated_at timestamp default now() not null,
    supervisor varchar(120),
    location_id integer,
    position_id integer
    -- additional 12 numeric and integer fields that are not indexed

);

create index ix_shift_position_id
    on shift (position_id);

create index ix_shift_location_id
    on shift (location_id);

更新查询：

UPDATE shift SET updated_at=now(), supervisor='Mr.Bean' WHERE shift.id = 12321

我应该做些什么来排除/修复这个问题？

Answer 1

当应用程序是无锁的(没有显式锁)时，UPDATE语句锁定行。使用问题中的wiki和这个社区提供的有用提示，一个长期运行的后台过程被认为是罪魁祸首。

使用SQLAlchemy ORM的后台进程预计将对内存中的数百条记录进行更改。在所有这些更改之后，进程被认为是刷新(调用UPDATE语句)和提交。但是，修改每个记录后立即刷新更改，刷新和提交之间的长时间意味着记录被锁定很长一段时间。