应用程序积极地将数据缓存在内存中,并且为了支持一致性(防止持久保存陈旧数据),它正在执行以下操作:
-- typical table structure:
create table t1 (
id varchar(16) primary key,
version_stamp int4,
....
)
-- typical update statement
update t1 set
version_stamp = version_stamp + 1,
col1 = ?,
col2 = ?,
...
where id = ? and version_stamp = ?
如果上面提到的更新表明没有更新任何行,则意味着应用程序已尝试保留陈旧数据并引发异常,主要思想是防止或至少最小化此类情况。为此,应用程序正在执行以下查询(每个请求、事务或方法调用):
select version_stamp from t1
where id = ?
如果没有返回行,则意味着该行已被删除,如果返回的行version_stamp
与version_stamp
内存中保存的行不同,则意味着我们正在处理陈旧的数据。
问题是:是否值得将主键定义为:
create unique index on t1(id) include(version_stamp)
或者在这种情况下则不然。此类查询的典型 RPS 约为每秒 10k。
您显示的查询在包含在 PK 索引中后可以从仅索引扫描
SELECT
中受益匪浅。(或者添加一个额外的多列索引来覆盖它。)这是假设您的表足以允许仅索引扫描。version_stamp
VACUUM
对于初学者来说,该数据类型
varchar(16)
是一个不幸的选择,因为id
它在磁盘上占用 17 个字节。空间通常以 8 字节为单位的块进行分配,这会导致 PK 索引中的 7 字节对齐填充变为 24 字节。最坏的情况。(也可能在表中。)参见:bigint
(8 字节)甚至uuid
(16 字节)会表现得更好。varchar
除此之外,处理成本稍高一些。看:好处是:
integer
向 PK 索引添加 an 几乎不会增加其大小,因为它可以占用当前因填充而丢失的 7 个字节中的 4 个字节。它将稍微减少“索引重复数据删除”的好处 - 取决于典型的写入模式和并发级别。(同一个 PK 条目的多个并发版本现在可能具有不同的版本
version_stamp
并且无法压缩。)但这是一个旁注。一个更重要的缺点:到目前为止,您在问题中披露的任何内容都不会妨碍显示命令(或类似命令)的热更新
UPDATE
。添加version_stamp
到 PK 索引可以排除更新该列时的 HOT 更新。索引现在也需要更新,这可能会增加写入操作的成本并导致更多的表和索引膨胀。看:最佳行动方案在很大程度上取决于整体情况:
autovacuum
保持可见性地图最新并应对索引膨胀?id
到更有利的数据类型吗?注意:这是你改变 PK 的方法:
或者一些更复杂的变体,
CREATE INDEX CONCURRENTLY
如果您负担不起表上的长独占锁。看:create unique index ...
就像你显示的那样相关,但又不同。可能不会。如果您使用仅索引扫描找到相同的 version_stamp,那么无论如何您都需要立即访问该表页才能对其进行更新。唯一避免该 IO 的情况是如果您发现不同的 version_stamp 并因此抛出错误。但这可能非常罕见,并且不值得优化。(此外,如果版本标记不同,则可能是最近更新的,因此可见性映射位已被清除,无论如何您都必须访问该页面。)