database - PostgreSQL中支持关系操作的Cassandra的TimeUUID的替代

Question

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

我需要迁移的内容：该表具有一个TimeUUID列，用于将时间存储为UUID。该列也用作聚类键。时间存储为UUID，以避免在同一毫秒内插入行时发生冲突。另外，此列涉及where子句（通常为timeUUID between 'foo' and 'bar'），并且产生了正确的结果。

我需要将其迁移到的位置：我正在迁移到Postgres，因此需要找到合适的替代方法。 PostgreSQL具有UUID数据类型，但是到目前为止，我已经阅读并尝试将它存储为4字节int，但是在带关系运算符的where子句中使用时，UUID类似于String对待。

select * from table where timeUUID > 'foo'将在结果中包含xyz。

根据我的理解，UUID甚至TimeUUID不必总是增加。因此，与具有相同数据集的Cassandra相比，Postgres产生了错误的结果。

到目前为止，我已经考虑了什么：我考虑将其存储为BIGINT，但是对于时间分辨率（以毫秒为单位），它很容易受到冲突的影响。我可以争取mirco / nano秒的分辨率，但恐怕BIGINT会用尽它。

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

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

我到底需要什么（tl; dr）：


具有较高时间分辨率的某种方式或避免冲突的方式（唯一值生成）。
该列应支持关系运算符，即
uuid_col < 'uuid_for_some_timestamp'。


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

Answer 1

tl; dr

别再用卡桑德拉语思考了。设计师在设计中做出了一些错误的决定。


将UUID用作identifier。
使用日期时间类型来跟踪时间。


➥请勿将两者混用。

混合两者是Cassandra的缺陷。

卡桑德拉滥用UUID

不幸的是，Cassandra滥用UUID。您的困境显示了他们的做法很不幸。

UUID的目的严格是生成标识符，而无需与其他方法（例如序列号）所需的中央机构进行协调。

Cassandra使用Version 1 UUIDs，它采用当前时刻加上任意小的数字，并与发行计算机的MAC address组合。所有这些数据将构成UUID中的大多数128 bits。

Cassandra做出了糟糕的设计决策，无法及时提取该时刻用于时间跟踪，这违反了UUID设计的意图。 UUID从未打算用于时间跟踪。

UUID标准中有多个替代版本。这些替代方案不一定包含时间。例如，Version 4 UUIDs而是使用从加密强度较高的生成器生成的随机数。

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


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


Postgres将UUID定义为本机数据类型。因此，如何存储这些值实际上与我们无关，在将来的Pos​​tgres版本（或其新的可插拔存储方法）中可能会发生变化。您传入一个UUID，然后您将获得一个UUID，这就是我们作为Postgres用户所知道的。另外，很高兴得知Postgres（以其当前的“堆”存储方法）将UUID值有效地存储为128位，而不是效率不高，例如，存储用于规范地显示UUID的十六进制字符串的文本对人类。

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


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


要了解更多信息，请参见：Generating a UUID in Postgres for Insert statement?

至于您的迁移，我建议将“讲真话”作为一般的好方法。日期时间值应存储在带有适当标记名称的日期类型列中。标识符应存储在具有适当标签名称的适当类型（通常为整数类型或UUID）的主键列中。

因此，不要再玩卡桑德拉（Cassandra）玩的愚蠢的聪明游戏了。

提取日期时间值，将其存储在日期时间列中。 Postgres具有出色的日期时间支持。具体来说，您需要将值存储在SQL标准类型TIMESTAMP WITH TIME ZONE的列中。此数据类型表示时刻，即时间轴上的特定点。

Java中表示时刻的等效类型为Instant或OffsetDateTime或ZonedDateTime。 JDBC 4.2规范仅要求对第二个（而不是第一个或第三个）的支持。在Stack Overflow上搜索有关此Java和JDBC信息的更多信息，因为已经对此进行了很多次讨论。

继续使用UUID，但仅将其用作Postgres中新表的指定主键列。您可以告诉Postgres自动生成这些值。


将UUID存储为CHAR


不，请勿将UUID存储为文本。


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


TIMESTAMP WITH TIME ZONE和TIMESTAMP WITHOUT TIME ZONE之间存在很大的差异。所以永远不要只说时间戳。

Postgres始终在UTC中存储TIMESTAMP WITH TIME ZONE。提交的值中包含的任何时区或偏移量信息都将用于调整为UTC，然后将其丢弃。 Java将此类型的值检索为UTC。所以没问题。

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

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




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


如果主机硬件时钟可以做到，则版本1 UUID跟踪和时间的分辨率可以达到100纳秒（1/10微秒）。 java.time类以微秒的分辨率捕获时间（从Java 9和更高版本开始）。 Postgres以微秒的分辨率存储时刻。因此，使用Java＆Postgres，您将在这方面与Cassandra保持紧密联系。

存储当前时刻。

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

恢复。

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

我可以寻求微秒/纳米秒的分辨率


你不能。如今，传统的计算机时钟无法精确跟踪纳秒级的时间。

仅将时间跟踪用作标识符值是一个有缺陷的想法。


UUID甚至TimeUUID不必总是增加


您永远不能指望时钟总是在增加。时钟得到调整和重置。计算机硬件时钟不是那么准确。不了解计算机时钟的局限性是Cassandra设计的幼稚和不合理的方面之一。

这就是为什么版本1 UUID与当前时刻一起使用任意小的数字（称为 clock sequence）的原因，因为当重置/调整时钟时，当前时刻可能会重复。负责任的UUID实现应注意时钟回落，然后递增该小数字以补偿并避免重复。根据RFC 4122第4.1.5节：


对于UUID版本1，时钟序列用于帮助避免在时钟倒退设置或节点ID更改时可能出现的重复。

如果时钟向后设置，或者可能已向后设置
（例如，在系统关闭电源的情况下），并且UUID生成器可以
不能确定没有生成大于UUID的时间戳
设置时钟的值，那么时钟序列必须
被改变。如果时钟序列的先前值是已知的，则它
可以增加否则应将其设置为随机或
高质量的伪随机值。


UUID specifications中没有任何东西可以保证“一直在增加”。回到我的开幕词，Cassandra滥用了UUID。