Thread: Performance slowing down when doing same UPDATE many times

Hi,

we recently found a bug in one of our applications which was doing exactly the same UPDATE operation a few thousand times inside a transaction. This caused the UPDATEs to become slower and slower from some milliseconds to some seconds. We already fixed the application but I am wondering if this might be a PostgreSQL bug, too.

Here is a simple test case that performs and benchmarks 100,000 UPDATEs (benchmarking only every 10,000th to reduce output):

BEGIN;

CREATE TEMP TABLE test (id integer PRIMARY KEY, flag boolean DEFAULT false);

INSERT INTO test (id) SELECT generate_series(1, 100000);

DO $$

DECLARE

  s timestamp;

  e timestamp;

BEGIN

  FOR i IN 1..100000 LOOP

    SELECT clock_timestamp() INTO s;

    UPDATE test SET flag = true WHERE id = 12345;

    SELECT clock_timestamp() INTO e;

    IF i%10000 = 0 THEN

      RAISE NOTICE '%', e-s;

    END IF;

  END LOOP;

END $$;

ROLLBACK;

The output looks like this:

NOTICE:  00:00:00.000525

NOTICE:  00:00:00.000992

NOTICE:  00:00:00.001404

NOTICE:  00:00:00.001936

NOTICE:  00:00:00.002374

NOTICE:  00:00:00.002925

NOTICE:  00:00:00.003525

NOTICE:  00:00:00.004015

NOTICE:  00:00:00.00453

NOTICE:  00:00:00.004976

The problem only occurs inside a transaction and if the same dataset is updated. I´m using PostgreSQL 9.1.15.

Jan