带有一个ID列、排序索引和重复主键的Postgresql表

Question

我想使用PostgreSQL表作为文档的工作队列。每个文档都有一个 ID，并存储在另一个包含大量附加列的普通表中。但这个问题是关于为工作队列创建表的。

我想为这个队列创建一个没有oid的表，只有一列:整数形式的文档ID。如果一个文档的ID存在于这个工作队列表中，这意味着具有该ID的文档是脏的，必须进行一些处理。额外的表应避免真空和死元组问题以及事务死锁，如果主文档表中的每个文档条目上只有一个脏位，则会出现事务死锁。

我的系统的许多部分会将文档标记为脏的，因此会将要处理的ID插入到该表中。这些插入将用于一个事务中的多个ID。我不想使用任何类型的嵌套事务，而且似乎没有任何类型的INSERT IF NOT EXISTS命令。我宁愿在表中有重复的ID。因此，该表中的唯一列必须可以重复。

处理工作队列的进程将删除所有进程id，因此会处理重复的id。(顺便说一句:下一步还有一个队列，所以关于竞争条件，这个想法应该是干净的，没有问题)

但我也希望文档按顺序处理：始终应先处理ID较小的文档。

因此，我希望有一个索引来帮助对ID列进行限制和排序，ID列是工作队列表中唯一的一列。理想情况下，假设我只有一列，这应该是主键。但是主键一定不能有重复的，所以好像我做不到。

如果没有索引，ORDER BY 和 LIMIT 会很慢。

我可以在该列上html" target="_blank">添加一个普通的辅助索引。但我担心PostgreSQL会在磁盘上添加第二个文件（PostgreSQL对每个额外的索引都这样做）并对该表使用双倍数量的磁盘操作。

最好的办法是什么？添加一个随机的虚拟列（如OID），以使主键不会抱怨重复？我必须在队列表中浪费这个空间吗？

或者添加第二个索引是否无害，它会成为直接位于主元组btree中的主索引吗？

我应该删除这上面的所有内容，只留下以下内容吗？原始问题分散注意力，包含太多不相关的信息。

我想在PostgreSQL中有一个具有以下属性的表：

• 整数的一列
• 允许重复
• 列上的有效ORDER BY LIMIT
• INSERTs不应在该表或任何类型的唯一索引中进行任何查询。INSERTs只需找到此表的主文件/主btree的最佳页面，然后将行插入到其他行之间，按ID排序。
• INSERT将批量发生，并且不能失败，期望磁盘已满等。
• 此表不应有额外的btree文件，因此没有辅助索引
• 行应该占用空间不多，例如没有OID

我想不出解决所有这些问题的办法。

我唯一的解决方案是在最后一个项目符号点上妥协：添加一个覆盖整数的主键和一个虚拟列，如OID，时间戳或串行。

另一种解决方案要么使用假设的INSERT IF Not EXISTS，要么使用嵌套事务或带有WHERE的特殊INSERT。所有这些解决方案都将在插入时添加对btree的查询。它们还可能导致死锁。

（也张贴在这里： https://dba.stackexchange.com/q/45126/7788）

Answer 1

从言外之意来看，我认为你正试图实现一个工作排队系统。

停。现在。

工作排队很困难。在关系 DBMS 中排队工作非常困难。人们提出的大多数“聪明”解决方案最终都在没有意识到的情况下在锁上序列化工作，或者他们在并发操作中存在令人讨厌的错误。

使用现有的消息/任务排队系统。ZeroMQ、RabbitMQ、PGQ等。有很多选择，它们具有（a）工作和（b）高效的显著优势。您很可能需要运行一个外部助手进程或服务器，但关系数据库模型的局限性往往使这成为必要。

正如我所能猜到的那样，您所设想的方案听起来似乎在故障处理、插入/删除竞争等方面会遇到无望的并发问题。真的，不要尝试自己设计这个方案，尤其是当您对潜在的并发性和性能问题没有很好的理解时。

Answer 2

你说

我的系统的许多部分都会将文档标记为脏，因此插入ID以处理该表。因此，必须可以重复。

和

具有相同ID的5行与具有相同ID的1或10行意味着相同的事情:它们意味着具有该ID的文档是脏的。

您不需要重复。如果此表的唯一目的是识别脏文档，则包含文档id号的单行就足够了。没有令人信服的理由允许重复。

如果您需要跟踪插入该行的进程，或者按插入行的时间对行进行排序，则每个 ID 号的单个行是不够的，但首先，单个列是不够的。因此，我确信主键约束或唯一约束对您来说会很好。

其他进程必须忽略重复键错误，但这很简单。这些进程无论如何都必须捕获错误——除了重复键之外，还有很多东西可以阻止插入语句成功。

允许重复的实现。。。

create table dirty_documents (
  document_id integer not null
);

create index on dirty_documents (document_id);

将100kID号插入到该表中进行测试。这必然需要更新索引。（废话）包括一堆重复项。

insert into dirty_documents 
select generate_series(1,100000);

insert into dirty_documents
select generate_series(1, 100);

insert into dirty_documents
select generate_series(1, 50);

insert into dirty_documents
select generate_series(88000, 93245);

insert into dirty_documents
select generate_series(83000, 87245);

在我的桌面上花了不到一秒钟的时间，这并不是什么特别的事情，它运行三个不同的数据库服务器，两个Web服务器，并播放Rammstein CD。

选择第一个脏文档ID号进行清理。

select min(document_id) 
from dirty_documents; 

document_id
--
1

仅用了 0.136 毫秒。现在，让我们删除文档 ID 为 1 的每一行。

delete from dirty_documents
where document_id = 1; 

花了0.272毫秒。

让我们从头开始。

drop table dirty_documents;
create table dirty_documents (
  document_id integer primary key
);

insert into dirty_documents 
select generate_series(1,100000); 

花了 500 毫秒。让我们再次找到第一个。

select min(document_id) 
from dirty_documents; 

花费了.054毫秒。这大约是使用允许重复的表所花费时间的一半。

delete from dirty_documents
where document_id = 1;

也花了0.054毫秒，比另一张表快了大约50倍。

让我们重新开始，尝试一个无索引的表。

drop table dirty_documents;
create table dirty_documents (
  document_id integer not null
);

insert into dirty_documents 
select generate_series(1,100000);

insert into dirty_documents
select generate_series(1, 100);

insert into dirty_documents
select generate_series(1, 50);

insert into dirty_documents
select generate_series(88000, 93245);

insert into dirty_documents
select generate_series(83000, 87245);

获取第一个文档。

select min(document_id) 
from dirty_documents; 

花了32.5毫秒。删除那些html" target="_blank">html" target="_blank">文件...

delete from dirty_documents
where document_id = 1;

用了12毫秒

所有这些花了我12分钟。（我用了秒表。）如果你想知道性能会是什么，建立表格和编写测试。