在Postgresq中存储版本的最佳方法

Question

我正在开发一个API，我想存储使用日志。我的API现在有几个活动版本，在日志中我希望能够按版本排序和搜索。你知道吗

现在版本以vX.X.XX格式保存为字符串（varchars）（从v0.0.16开始），因此无法通过where api_version > "vX.X.XX"查询进行搜索。因此，这不是最好的解决方案，我想切换到一些数字。你知道吗

作为varchars的版本可以通过Postgresql进行排序，但是速度非常慢。你知道吗

在python中，这个版本被创建为元组（例如(1, 0, 0)），因此可以很容易地进行排序。你知道吗

在PostgreSQL中有没有最好的存储版本的方法？ 什么样的类型最适合这样做，并且在排序和搜索方面具有良好的速度性能？你知道吗

Answer 1

在数据库中存储版本号最简单的方法是只为主要、次要等组件保留单独的列，例如

major | middle | minor
1     | 0      | 0

这将使搜索给定版本的日志变得非常容易，例如

SELECT *
FROM logs
WHERE
    major = 1 AND
    middle = 0 AND
    minor = 0;

注意，这个方法使用一个关于版本号的查询sargable。也就是说，可以使用version列上的索引来加速上述查询。你知道吗

存储版本字符串的问题是，搜索单个版本组件要困难得多。而且，任何一个指数也不太可能在大多数时间被使用。您仍然可以存储版本字符串，只是不要使用它进行搜索。你知道吗

Answer 2

不确定Varchar是否是最佳解决方案，是否可以将字段类型更改为数字并在索引字段中重新保存现有数字，例如vX.Y.ZZ为（x*10000+Y*1000+z*10）

另一种方法是将字符串转换为数字，然后排序-如here所述

Answer 3

我认为，一个简单的解决方案是尝试将array作为version字段的数据类型。在您的例子中，它可能是int[]。你知道吗

版本为的表示例：

create table tbl (
    id serial not null primary key,
    "version" int[] null
);

之后，您可以像处理数组一样按版本进行筛选、排序或分组。你知道吗

select * from tbl where "version" > array[2, 3, 10] order by "version";