支持关系操作的PostgreSQL中Cassandra的TimeUUID的替代方案

Question

我需要将一个表从Cassandra迁移到PostgreSQL。

需要迁移的内容：该表有一个TimeUUID列，用于将时间存储为uuid。此列还用作聚类键。时间存储为UUID，以避免在相同毫秒内插入行时发生冲突。此外，在where子句中涉及此列，通常是在'foo'和'bar'之间的timeuid，它产生了正确的结果。

我需要将它迁移到哪里：我正在迁移到Postgres，所以需要找到一个合适的替代方案。PostgreSQL有UUID数据类型，但根据我目前阅读和尝试的内容，它将其存储为4字节的int，但在where子句中使用关系运算符时，它对UUID的处理类似于String。

选择*from表，其中timeUUID>'foo'将在结果中包含xyz。

根据我的理解，UUID甚至TimeUUID都没有必要一直在增加。因此，Postgres与相同数据集的Cassandra相比会产生错误的结果。

到目前为止我所考虑的：我考虑将它存储为BIGINT，但对于毫秒级的时间分辨率来说，它可能会发生冲突。我可以去获得mirco/nano秒的分辨率，但我担心BIGINT会耗尽它。

将UUID存储为CHAR可以防止冲突，但这样我就失去了在列上应用关系运算符的能力。

时间戳最合适，但我担心时区和碰撞。

我到底需要什么（TL；DR）：

该列应该支持关系运算符，即uuid_col<'uuid_for_some_timestamp'。

PS：这是一个Java应用程序。




Answer 1

别再用卡桑德拉的术语思考了。设计者在他们的设计中做了一些有缺陷的决定。

• 使用UUID作为标识符。
• 使用日期-时间类型跟踪时间。

不要把两者混在一起。

Cassandra使用Version1 UUIDs，该UUIDs取当前时刻，加上任意一个小数字，并与发布计算机的MAC地址结合在一起。所有这些数据构成了UUID中128位的大部分。

Cassandra做出了可怕的设计决定，要及时提取那个时刻用于时间跟踪，这违反了UUID设计的意图。UUID从未打算用于时间跟踪。

UUID标准中有几个可供选择的版本。这些备选方案不一定包含时间上的片刻。例如，版本4的UUID使用从加密性强的生成器生成的随机数。

如果您想生成版本1的uuid，请安装uuid-ossp插件（“扩展”）（包装OSSP uuid库），通常与Postgres捆绑在一起。该插件提供了几个函数，您可以调用这些函数来生成UUID值。

[Postgres]将其存储为4字节int

Postgres将UUID定义为本机数据类型。因此，如何存储这些值实际上与我们无关，并且可能在Postgres的未来版本中（或在其新的可插入存储方法中）发生变化。您传入一个UUID，您将得到一个UUID，这就是我们作为PostGres用户所知道的一切。另外，Postgres（在其当前的“堆”存储方法中）高效地将UUID值存储为128位，而不是低效地存储用于向人类显示UUID的十六进制字符串文本。

请注意，Postgres内置支持存储UUID值，而不是生成UUID值。生成值：

• 有些人使用pgcrypto扩展（如果已经安装在他们的数据库中）。该插件只能生成版本4几乎所有随机的UUID。
• 我建议您改为使用uuid-ossp扩展。这为您提供了多种版本的UUID供选择。

要了解更多信息，请参见：在Postgres中为Insert语句生成UUID？

至于你的迁移，我建议“说实话”作为一个普遍的好方法。日期时间值应存储在日期类型列中，并具有适当标记的名称。标识符应该存储在具有适当标记名称的适当类型（通常是整数类型或UUID）的主键列中。

所以别再玩卡桑德拉玩的那些愚蠢聪明的游戏了。

将UUID存储为字符

不，不要将UUID存储为文本。

时间戳最合适，但我担心时区和碰撞。

当使用其他工具时，问题就会出现，这些工具具有用意良好但有可悲缺陷的特性，即在生成文本以显示字段的值时动态应用默认时区。从Postgres检索的值始终在UCT中，但它的表示方式可能已调整到另一个偏移量或区域。要么避免使用此类工具，要么确保将默认区域设置为UTC本身。所有程序员、DBA和系统管理员都应该学会在工作中使用UTC进行工作和思考。

没有时区的时间戳完全不同。此类型缺少时区或从UTC偏移量的上下文。因此此类型不能表示片刻。它有一个日期和一天中的时间，但仅此而已。这当然是模棱两可的。如果数值是今年1月23日的中午，我们不知道你指的是东京的中午，德黑兰的中午，还是托莱多- 的中午，都是非常不同的时刻，相隔几个小时。Java中的等效类型是LocalDateTime。搜索堆栈溢出以了解更多信息。

时间存储为UUID，以避免在相同毫秒内插入行时发生冲突。

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

检索。

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

我可以获得毫米波/纳秒的分辨率

不，你不能。今天传统的计算机时钟不能精确地以纳秒为单位跟踪时间。

对于UUID Version1，时钟序列用于帮助避免当时钟在时间上向后设置或节点ID更改时可能出现的重复。

如果时钟被向后设置，或者可能已经被向后设置（例如，当系统断电时），并且UUID生成器不能确定没有生成具有大于时钟被设置到的值的时间戳的UUID，那么时钟序列必须被改变。如果时钟序列的前一个值是已知的，可以只是递增；否则，应将其设置为随机值或高质量伪随机值。

UUID规范中没有任何东西promise“总是在增加”。回到我的开场白，卡桑德拉滥用UUID。