代码之家 › 专栏 › 技术社区 › Bartosz Sypytkowski

Postgres:将列值更新为相同的值是否会将页标记为脏?

postgresql

Bartosz Sypytkowski · 技术社区 · 6 年前

考虑PostgreSQL中的以下场景(10+的任何版本):

CREATE TABLE users(
    id serial primary key,
    name text not null unique,
    last_seen timestamp
);

INSERT INTO users(name, last_seen)
VALUES ('Alice', '2019-05-01'),
       ('Bob', '2019-04-29'),
       ('Dorian', '2019-05-11');

CREATE TABLE inactive_users(
    user_id int primary key references users(id),
    last_seen timestamp not null);

INSERT INTO inactive_users(user_id, last_seen)
SELECT id as user_id, last_seen FROM users 
WHERE users.last_seen < '2019-05-04' 
ON CONFLICT (user_id) DO UPDATE SET last_seen = excluded.last_seen;

last_seen 列将更新为其已具有的相同值。行的值保持不变,所以没有理由进行I/O写入,对吧?但这是真的吗,或者postgres会执行相应的更新,即使实际值没有改变?

在我的例子中,目标表有数以千万计的行,但是在每次insert调用中只有几百行/几千行会真正改变。

0 回复 | 直到 6 年前

richyen 6 年前

任何 UPDATE 到一行实际上会创建一个新行(将旧行标记为已删除/脏),而不考虑before/after值:

[root@497ba0eaf137 /]# psql
psql (12.1)
Type "help" for help.

postgres=# create table foo (id int, name text);
CREATE TABLE
postgres=# insert into foo values (1,'a');
INSERT 0 1
postgres=# select ctid,* from foo;
 ctid  | id | name 
-------+----+------
 (0,1) |  1 | a
(1 row)

postgres=# update foo set name = 'a' where id = 1;
UPDATE 1
postgres=# select ctid,* from foo;
 ctid  | id | name 
-------+----+------
 (0,2) |  1 | a
(1 row)

postgres=# update foo set id = 1 where id = 1;
UPDATE 1
postgres=# select ctid,* from foo;
 ctid  | id | name 
-------+----+------
 (0,3) |  1 | a
(1 row)

postgres=# select * from pg_stat_user_tables where relname = 'foo';
-[ RECORD 1 ]-------+-------
relid               | 16384
schemaname          | public
relname             | foo
seq_scan            | 5
seq_tup_read        | 5
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 1
n_tup_upd           | 2
n_tup_del           | 0
n_tup_hot_upd       | 2
n_live_tup          | 1
n_dead_tup          | 2
<...>

根据你的例子:

postgres=# select ctid,* FROM inactive_users ;
 ctid  | user_id |      last_seen      
-------+---------+---------------------
 (0,1) |       1 | 2019-05-01 00:00:00
 (0,2) |       2 | 2019-04-29 00:00:00
(2 rows)

postgres=# INSERT INTO inactive_users(user_id, last_seen)
postgres-# SELECT id as user_id, last_seen FROM users 
postgres-# WHERE users.last_seen < '2019-05-04' 
postgres-# ON CONFLICT (user_id) DO UPDATE SET last_seen = excluded.last_seen;
INSERT 0 2
postgres=# select ctid,* FROM inactive_users ;
 ctid  | user_id |      last_seen      
-------+---------+---------------------
 (0,3) |       1 | 2019-05-01 00:00:00
 (0,4) |       2 | 2019-04-29 00:00:00
(2 rows)

WHERE 条款。

EnterpriseDB (EDB)