sql - 具有许多 boolean 变量的模式的最佳实践

Question

我正在创建一个 Postgresql 数据库，其中有许多 (10-40) 个具有 boolean 值的变量。我想弄清楚存储这些数据的最佳方式是什么，给定中等数量的更新和大量的多列搜索。

创建 30 个左右的 boolean 列并在必要时创建多列索引似乎非常简单。或者，有人建议创建一个组合所有 boolean 值的位串。看起来第二种方案应该更快，但是网上其他人给出的答案似乎是矛盾的(见下文)。

任何建议或解释都会有所帮助。数据有数千万行，但不会更大，我希望选择返回数据的 1/100 到 1/4 之间的某处。

https://stackoverflow.com/questions/14067969/optimized-sql-using-bitwise-operator

alternative to bitmap index in postgresql

更新:

我找到了一个资源，它建议如果您有多个变量(您应该使用单独的列)并且少于 33 个左右(您切换到位串)，则建议使用整数或大整数。这似乎更多地是由存储大小而不是搜索的便利性引起的。

https://dba.stackexchange.com/questions/25073/should-i-use-the-postgresql-bit-string

Answer 1

我在 Database Administrators 找到了相关讨论网站。

首先，我会定义/分析在您的上下文中什么是“最佳”。你只是在寻找速度吗？你的搜索模式是什么？数据/磁盘容量是个问题吗？

你有什么选择？除了位串之外，还可以使用普通的文本字符串、整数数组和单独的列。要快速获取数据，您必须考虑索引。您提到了多列索引。在多个索引中存储/索引相同的位变量是否有意义？

40 位没有太多重复记录意味着最多 2^20 = 1.1E12 条记录。这使得全表扫描成为一件冗长的事情。另一方面，如果您有很多重复的键，则索引并不是很有帮助。

如果您希望得到大约 25% 的结果集，则必须在数据库和应用程序之间传输 2.7E11(部分)记录。假设 10,000 条记录/秒，这将需要 7,736 小时或 10 个月。

我的结论是您应该考虑将数据存储在大 BLOB 中(1.1E12 x 40 位仅为 40 GByte)。您可以对数据进行分区，将感兴趣的部分读入内存并在那里进行搜索。这或多或少是大数据或数据仓库系统正在做的事情。