On Mon, 2023-02-13 at 16:09 +0100, Mkrtchyan, Tigran wrote:
> Typically we expect that UPDATE is a slow operation in PostgreSQL, however,
> there are cases where it's hard to understand why. In particular, I have a
> table like
>
> ```
> CREATE SEQUENCE t_inodes_inumber_seq
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1;
>
>
> CREATE TABLE t_inodes (
> inumber bigint PRIMARY KEY,
> icrtime timestamp with time zone NOT NULL,
> igeneration bigint NOT NULL
> );
> ```
>
> and a transaction that inserts and update an entry in that table:
>
> ```
> BEGIN;
> INSERT INTO t_inodes (inumber, icrtime, igeneration)
> VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset
>
> UPDATE t_inodes SET igeneration = igeneration + 1 where inumber = :inumber;
> END;
> ```
>
> The pgbench shows the following result:
>
> ```
> $ pgbench -h localhost -n -r -f update.sql -t 10000 -c 64 -j 64 testdb
> pgbench (15.0 (Debian 15.0-1.pgdg110+1))
> transaction type: update.sql
> scaling factor: 1
> query mode: simple
> number of clients: 64
> number of threads: 64
> maximum number of tries: 1
> number of transactions per client: 10000
> number of transactions actually processed: 640000/640000
> number of failed transactions: 0 (0.000%)
> latency average = 11.559 ms
> initial connection time = 86.038 ms
> tps = 5536.736898 (without initial connection time)
> statement latencies in milliseconds and failures:
> 0.524 0 BEGIN;
> 0.819 0 INSERT INTO t_inodes (inumber, icrtime,
> igeneration)
> 0.962 0 UPDATE t_inodes SET igeneration = igeneration + 1
> where inumber = :inumber;
> 9.203 0 END;
> ```
>
> My naive expectation will be that updating the newly inserted record should
> cost nothing... Are there ways
> to make it less expensive?
Updating a newly inserted row is about as expensive as inserting the row in the
first place.
You can reduce the overall impact somewhat by creating the table with a
"fillfactor" below
100, in your case 90 would probably be enough. That won't speed up the UPDATE
itself, but
it should greatly reduce the need for VACUUM.
Yours,
Laurenz Albe
