sql - 高效的存储模式，可存储数百万个不同类型的值

Question

我将要构建一个sql数据库，它将包含数十万个对象的统计计算结果。计划使用postgres，但这个问题同样适用于mysql。
例如，假设我有50万个电话记录。现在，每个PhoneCall都将通过后台作业系统进行统计计算。例如，aPhoneCall具有以下统计信息：
call_duration：秒（浮动）
setup_time：秒（浮动）
dropouts：检测到音频丢失的时段（数组），例如[5.23, 40.92]
hung_up_unexpectedly：真或假（布尔值）
这些只是简单的例子；实际上，统计数字更为复杂。每个统计都有一个与之关联的版本号。
我不确定这类计算数据的哪种存储模式最有效。不过，我并没有考虑完全规范化数据库中的所有内容。到目前为止，我已经提出了以下选择：
选项1-一列中的长格式
我将统计名称及其值分别存储在一列中，并引用主事务对象。值列是一个文本字段；该值将被序列化（例如，作为json或yaml），以便可以存储不同类型（字符串、数组等）。统计表的数据库布局为：
statistic_id（主键）
phone_call_id（FK）
statistic_name（字符串）
statistic_value（文本，序列化）
statistic_version（整数）
created_at（日期时间）
我使用这个模式已经有一段时间了，它的优点是我可以根据电话和统计名称轻松筛选统计数据。我还可以轻松地添加新的统计类型，并按版本和创建时间进行筛选。
但在我看来，值的（反）序列化使得它在处理大量数据方面效率很低。此外，我不能在sql级别执行计算；我总是必须加载和反序列化数据。或者postgres中的json支持有那么好，这样我仍然可以选择这个模式吗？
选项2-统计作为主要对象的属性
我还可以考虑收集所有类型的统计名称，并将它们作为新列添加到phone call对象中，例如：
id（主键）
call_duration
setup_time
dropouts
hung_up_unexpectedly
…
这将是非常有效的，每个列都有自己的类型，但是我不能再存储不同版本的统计信息，也不能根据它们创建的时间进行筛选。整个统计的商业逻辑消失了。添加新的统计数据也不太容易，因为这些名称都是内置的。
选项3——不同列的统计信息
这可能是最复杂的。我只存储一个对统计类型的引用，该列将根据该引用进行查找：
statistic_id（主键）
phone_call_id（FK）
statistic_name（字符串）
statistic_value_bool（布尔值）
statistic_value_string（字符串）
statistic_value_float（浮动）
statistic_value_complex（序列化或复杂数据类型）
statistic_value_type（表示bool，string等的字符串）
statistic_version（整数）
created_at（日期时间）
这意味着表将非常稀疏，因为只有一个statistic_value_列将被填充。这会导致性能问题吗？
选项4——标准化形式
尝试规范化选项3，我将创建两个表：
statistics
id（主键）
version
created_at
statistic_mapping
phone_call_id（FK）
statistic_id（FK）
statistic_type_mapping
statistic_id（FK）
type（字符串，表示bool，string等）
statistic_values_boolean
statistic_id（FK）
value（布尔）
…
但这不会有任何进展，因为我不能动态地加入到另一个表名，是吗？还是应该根据统计ID加入所有statistic_values_*表？我的应用程序必须确保没有重复的条目存在。
总而言之，给定这个用例，什么是在关系数据库中存储数百万个统计值（例如PASGRESs）的最有效的方法，当需求是统计类型可以被添加或改变，并且同时存在多个版本，并且值的查询应该是有效率的吗？

Answer 1

IMO可以使用以下简单的数据库结构来解决您的问题。
统计类型词典
一个非常简单的表-只是stat.type的名称和描述：

create table stat_types (
  type        text not null constraint stat_types_pkey primary key,
  description text  
);

（如果元素的数量有限，可以将其替换为enum）
项目中每种对象的stat表
它包含对象的fk，stat.type的fk（或者仅仅是枚举），这一点很重要， jsonb字段具有与其类型相关的任意stat.data。例如，这样的电话表：

create table phone_calls_statistics ( 
  phone_call_id uuid  not null references phone_calls,
  stat_type     text  not null references stat_types,
  data          jsonb,
  constraint phone_calls_statistics_pkey primary key (phone_call_id, stat_type)  
);

我假设表的pk类型是：

create table phone_calls (
  id uuid not null constraint phone_calls_pkey primary key
-- ...
);

phone_calls字段有不同的结构，这取决于它的stat.type。通话持续时间示例：

{
   "call_duration": 120.0
}

或者对于辍学者：

{
   "dropouts": [5.23, 40.92]
}

让我们玩数据游戏：

insert into phone_calls_statistics values 
  ('9fc1f6c3-a9d3-4828-93ee-cf5045e93c4c', 'CALL_DURATION', '{"call_duration": 100.0}'),
  ('86d1a2a6-f477-4ed6-a031-b82584b1bc7e', 'CALL_DURATION', '{"call_duration": 110.0}'),
  ('cfd4b301-bdb9-4cfd-95db-3844e4c0625c', 'CALL_DURATION', '{"call_duration": 120.0}'),
  ('39465c2f-2321-499e-a156-c56a3363206a', 'CALL_DURATION', '{"call_duration": 130.0}'),
  ('9fc1f6c3-a9d3-4828-93ee-cf5045e93c4c', 'UNEXPECTED_HANGUP', '{"unexpected_hungup": true}'),
  ('86d1a2a6-f477-4ed6-a031-b82584b1bc7e', 'UNEXPECTED_HANGUP', '{"unexpected_hungup": true}'),
  ('cfd4b301-bdb9-4cfd-95db-3844e4c0625c', 'UNEXPECTED_HANGUP', '{"unexpected_hungup": false}'),
  ('39465c2f-2321-499e-a156-c56a3363206a', 'UNEXPECTED_HANGUP', '{"unexpected_hungup": false}');

获取平均、最小和最大通话持续时间：

select 
  avg((pcs.data ->> 'call_duration')::float) as avg,
  min((pcs.data ->> 'call_duration')::float) as min,
  max((pcs.data ->> 'call_duration')::float) as max
from 
  phone_calls_statistics pcs 
where 
  pcs.stat_type = 'CALL_DURATION';

获取意外挂起的次数：

select 
  sum(case when (pcs.data ->> 'unexpected_hungup')::boolean is true then 1 else 0 end) as hungups  
from 
  phone_calls_statistics pcs 
where 
  pcs.stat_type = 'UNEXPECTED_HANGUP'; 

相信该解决方案非常简单灵活，具有良好的性能潜力和完善的可扩展性。主表有一个简单的索引；所有查询都将在其中执行。您始终可以扩展统计类型的数量及其计算。
实例： https://www.db-fiddle.com/f/auATgkRKrAuN3jHjeYzfux/0