我有一个 Postgres 表,其中包含大量索引列(总共大约有 100 个索引列,是的,我需要全部索引列,是的,它们都需要单独索引)。任何行更新都会导致所有索引被更新,这对于数据库引擎来说是大量的工作。
我想了解 Postgres 文档页面上标题为“索引锁定注意事项”的讨论的并发含义,以及Postgres 是单线程(多进程)的事实,即当前设计如何影响读取器和写入器的性能。考虑到我有这么多的列索引,大量的并发查询。
我对这些事情的解释如下(如有错误,请指正):
- 更新单个行的写入器不会阻止读取器,除非读取器正在运行生成包含正在更新的行的结果集的查询。
- 仅当编写者尝试同时更新同一行时,他们才会互相阻塞。
- 来自多个写入器的基于 btree 的索引的并发更新会根据一组通常执行正确操作的规则进行合并(因此同时更新相同的索引不会导致写入器阻塞,除非它们正在更新同一行)。
我的问题是:
- 如果 Postgres 是单线程的,怎么可能有多个并发的读取器或写入器呢?如果您有多个进程正在运行,它们是否仅仅依赖于磁盘缓存的进程间一致性(或者必须手动将内容刷新到磁盘)来协调并发更新?
- 如果由于行更新而更新大量索引时任何内容可能被阻塞怎么办?如果在更新期间有任何事情可能被阻止,是否可以在一致性与可用性之间进行权衡,例如,行更新不是原子的(即,索引一次更新一个,但对所有索引的更新不必以原子方式发生)?我可以接受以更高并发的名义缺乏一致性。
单个 PostgreSQL 数据库会话过去是单线程的,因为有一个后端进程处理连接的 SQL 语句。PostgreSQL 9.6 引入了并行查询,它允许后端进程在语句持续时间内启动额外的进程。但即使没有这个,您也可以拥有许多并发数据库会话,每个会话都有一个后端进程,因此可以有足够的并发性。这些进程之间的通信通过进程间通信技术(如共享内存、信号和信号量)进行。
您的假设大部分都是正确的,除了并发编写者没有合并索引修改。并发数据修改请求通过各种锁定技术(信号量、互斥锁和自旋锁)进行序列化。
没有办法以牺牲数据完整性和一致性为代价来配置 PostgreSQL 以获得更好的性能。PostgreSQL 在这方面是相当无情的。我怀疑你的问题是一个理论问题,而不是基于你已经遇到的问题。对于具有大量索引的宽表,我认为并发不是您的大问题,而是数据修改本身的缓慢。我建议您更改应用程序的规格;请参阅这个问题了解我对此的想法。
单线程并不重要。它是具有共享内存的多进程,进程管理并发的方式与线程的方式没有太大不同。
有两种锁,重量级锁(通常)持续整个事务期间,而轻量级锁和自旋锁仅持续很短的时间。
编写者使用轻量级锁或自旋锁来阻止读取器,以确保一个进程在另一个进程检查数据时不会更改数据。这通常发生在页级别,而不是行级别。因此,当作者正在向页面写入内容时,读者无法检查它。但一旦作者完成(通常是微秒或更短的时间),他们就可以了。如果他们想要查看的行已更新,他们将只提取旧值而不是新值。
作者在页面级别上阻止其他作者的时间很短,就像他们阻止读者一样。如果两个写入者想要更新同一行,那么其中一个将无限期地阻塞在重量级锁上,等待另一个写入或回滚。
如果他们正在更新同一行,则该问题将在到达索引之前得到解决。因此索引不会强加新的“重量级”锁定问题。他们确实施加了更多的轻量级锁定,但通常只与他们施加的工作量成正比。
这很难相信,除非你指的是在某种专门意义上缺乏一致性。没有一致性,你会得到错误的结果。如果您不在乎结果是否错误,则不需要任何索引,只需添加
WHERE/AND 1=0
到所有查询中即可,无需索引它们应该很快。如果你问问题,就意味着你不知道答案。在这种情况下,隐藏信息有点自以为是,因为你觉得它不相关:为了知道信息是否相关,你需要知道答案,但你不需要知道答案,因为你在问问题; )
对于大量低基数列来说,一个优秀的解决方案是布隆过滤器索引。您必须加载扩展:
不幸的是,它最多只支持 32 列,因此如果您有更多列,则需要多个索引。仍然是 100 列... 4 个索引可能会比 100 个索引使用更少的资源。
另一种选择是为每个(属性名称,值)对提供一个数字,将其存储到整数数组中,并在其上放置要点索引。这有点麻烦,例如“hair=blonde”可能对应于“数组中有数字123”。
我用 1M 行做了一个小基准测试,Bloom 索引以很大优势获胜。
因此,我建议您尝试一下,并使用最常见的搜索查询进行基准测试,同时调整签名长度等布隆参数。由于 32 列的限制,如何将列拆分为索引可能也很重要。
请注意,您的问题与全文搜索相同。查找包含“hair=blonde and status=single”的行与将属性编码为关键字并对“hair_blonde status_single”进行全文搜索完全相同。
因此,另一种选择是仅使用快速全文引擎。但数据库集成可能会很糟糕。我不建议使用 postgres 的全文引擎,因为它基于 gist 索引,这意味着直接使用 gist 索引可以获得更好的性能。
--
基准测试数据生成脚本
行非常小,这使得位图索引扫描效率较低。对于较大的行,位图索引扫描标记的每个页面包含较少的要过滤的行,因此应该更快。
不幸的是布隆过滤器索引不支持布尔值,所以我使用了整数列。
由于听起来您的主要好奇心是为了提高并发性而对一致性进行权衡,因此您可能希望了解的主题称为事务隔离级别。这是 PostgreSQL(以及大多数数据库系统)中基于 SQL 标准的实现,该标准控制着这种权衡:
根据隔离级别的不同,上述现象可能会以不同程度发生:
下面是PostgreSQL 提供的隔离级别及其潜在现象的表:
PostgreSQL 中的默认隔离级别
Read Committed
基本上意味着读取器阻止写入器,写入器阻止读取器。在不同的数据库系统中,您可能对隔离级别感兴趣Read Uncommitted
,它允许读取同时写入的数据,但 PostgreSQL 实际上并没有以这种方式实现此隔离级别 - 这是一件好事,因为它很危险对于大多数用例来说都存在风险。相反,PostgreSQL 内置了多版本并发控制,它允许乐观并发。此功能允许它在数据同时更改(写入)时维护数据的先前状态,以隐式允许并发读取器也能够读取该数据。这个简短的DBA.StackExchange 答案进一步讨论了这个问题。
除此之外,请参阅我对您的帖子的评论,了解如何总体改进数据库设计以提高性能和并发性。