社区编辑申请
注册/登录
正确使用PostgreSQL的数组类型
开发 前端 PostgreSQL
在Heap中,我们依靠PostgreSQL支撑大多数后端繁重的任务,我们存储每个事件为一个hstore blob,我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组,并将这些事件按时间排序。
在Heap中,我们依靠PostgreSQL支撑大多数后端繁重的任务,我们存储每个事件为一个hstore blob,我们为每个跟踪的用户维护一个已完成事件的PostgreSQL数组,并将这些事件按时间排序。 Hstore能够让我们以灵活的方式附加属性到事件中,而且事件数组赋予了我们强大的性能,特别是对于漏斗查询,在这些查询中我们计算不同转化渠道步骤间的输出。

在这篇文章中,我们看看那些意外接受大量输入的PostgreSQL函数,然后以高效,惯用的方式重写它。 

你的***反应可能是将PostgreSQL中的数组看做像C语言中对等的类似物。你之前可能用过变换阵列位置或切片来操纵数据。不过要小心,在PostgreSQL中不要有这样的想法,特别是数组类型是变长的时,比如JSON、文本或是hstore。如果你通过位置来访问PostgreSQL数组,你会进入一个意想不到的性能暴跌的境地。

这种情况几星期前在Heap出现了。我们在Heap为每个跟踪用户维护一个事件数组,在这个数组中我们用一个hstore datum代表每个事件。我们 有一个导入管道来追加新事件到对应的数组。为了使这一导入管道是幂等的,我们给每个事件设定一个event_id,我们通过一个功能函数重复运行我们的事 件数组。如果我们要更新附加到事件的属性的话,我们只需使用相同的event_id转储一个新的事件到管道中。 

所以,我们需要一个功能函数来处理hstores数组,并且,如果两个事件具有相同的event_id时应该使用数组中最近出现的那个。刚开始尝试这个函数是这样写的:

  1. -- This is slow, and you don't want to use it! 
  2. -- 
  3. -- Filter an array of events such that there is only one event with each event_id. 
  4. -- When more than one event with the same event_id is present, take the latest one. 
  5. CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$ 
  6.   SELECT array_agg(event) 
  7.   FROM ( 
  8.     -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id. 
  9.     SELECT event 
  10.     FROM ( 
  11.       -- Rank elements with the same event_id by position in the array, descending. 
  12.       SELECT events[sub] AS event, sub, rank() 
  13.       OVER (PARTITION BY (events[sub] -> 'event_id')::BIGINT ORDER BY sub DESC
  14.       FROM generate_subscripts(events, 1) AS sub 
  15.     ) deduped_events 
  16.     WHERE rank = 1 
  17.     ORDER BY sub ASC 
  18.   ) to_agg; 
  19. $$ LANGUAGE SQL IMMUTABLE; 

这样奏效,但大输入是性能下降了。这是二次的,在输入数组有100K各元素时它需要大约40秒!

Execution Times For dedupe_events_1

这个查询在拥有2.4GHz的i7CPU及16GB Ram的macbook pro上测得,运行脚本为:https://gist.github.com/drob/9180760。

在这边究竟发生了什么呢? 关键在于PostgreSQL存贮了一个系列的hstores作为数组的值, 而不是指向值的指针. 一个包含了三个hstores的数组看起来像
{“event_id=>1,data=>foo”, “event_id=>2,data=>bar”, “event_id=>3,data=>baz”}
相反的是
{[pointer], [pointer], [pointer]}
 

对于那些长度不一的变量, 举个例子. hstores, json blobs, varchars,或者是 text fields, PostgreSQL 必须去找到每一个变量的长度. 对于evaluateevents[2], PostgreSQL 解析从左侧读取的事件直到读取到第二次读取的数据. 然后就是 forevents[3], 她再一次的从***个索引处开始扫描,直到读到第三次的数据! 所以, evaluatingevents[sub]是 O(sub), 并且 evaluatingevents[sub]对于在数组中的每一个索引都是 O(N2), N是数组的长度.

PostgreSQL能得到更加恰当的解析结果,  它可以在这样的情况下分析该数组一次. 真正的答案是可变长度的元素与指针来实现,以数组的值, 以至于,我们总能够处理 evaluateevents[i]在不变的时间内.

即便如此,我们也不应该让PostgreSQL来处理,因为这不是一个地道的查询。除了generate_subscripts我们可以用unnest,它解析数组并返回一组条目。这样一来,我们就不需要在数组中显式加入索引了。

  1. -- Filter an array of events such that there is only one event with each event_id. 
  2. -- When more than one event with the same event_id, is present, take the latest one. 
  3. CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$ 
  4.   SELECT array_agg(event) 
  5.   FROM ( 
  6.     -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id. 
  7.     SELECT event 
  8.     FROM ( 
  9.       -- Rank elements with the same event_id by position in the array, descending. 
  10.       SELECT event, row_number AS index, rank() 
  11.       OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY row_number DESC
  12.       FROM ( 
  13.         -- Use unnest instead of generate_subscripts to turn an array into a set. 
  14.         SELECT event, row_number() 
  15.         OVER (ORDER BY event -> 'time'
  16.         FROM unnest(events) AS event 
  17.       ) unnested_data 
  18.     ) deduped_events 
  19.     WHERE rank = 1 
  20.     ORDER BY index ASC 
  21.   ) to_agg; 
  22. $$ LANGUAGE SQL IMMUTABLE; 

结果是有效的,它花费的时间跟输入数组的大小呈线性关系。对于100K个元素的输入它需要大约半秒,而之前的实现需要40秒。

这实现了我们的需求:

  • 一次解析数组,不需要unnest。
  • 按event_id划分。
  • 对每个event_id采用***出现的。
  • 按输入索引排序。

教训:如果你需要访问PostgreSQL数组的特定位置,考虑使用unnest代替。 

我们希望能够避免失误。有任何意见或其他PostgreSQL的秘诀请@heap。 

[1]特别说明一下,我们使用一个名为Citus Data的贴心工具。更多内容在另一篇博客中! 
[2]参考:https://heapanalytics.com/features/funnels。特别说明一下,计算转换程序需要对用户已完成事件的数组进行一次扫描,但不需要任何join。

原文链接:http://blog.heapanalytics.com/dont-iterate-over-a-postgres-array-with-a-loop/

译文链接:http://www.oschina.net/translate/dont-iterate-over-a-postgres-array-with-a-loop

责任编辑:陈四芳 来源: 开源中国编译
相关推荐

2022-06-20 22:37:25

Linux操作系统命令

2022-05-10 14:11:05

网络安全网络犯罪

2022-06-14 23:34:10

Linux安全服务器

2022-06-28 09:26:25

Python配置文件

2022-06-28 14:01:42

MITOpenAI预训练模型

2022-06-02 07:13:12

Python3.11编程语言

2022-06-27 19:01:04

Python应用程序数据

2022-03-31 11:17:58

JavaScript数组方法

2022-06-27 23:44:37

云原生云存储云计算

2022-06-06 10:20:59

CPUCPU 使用率CPU 负载

2022-05-09 07:08:14

LinuxBashShell

2022-06-16 11:33:57

物联网区块链科技

2022-05-12 14:22:39

NFC标签鸿蒙

2022-02-23 20:38:32

云原生集群Postgres

2022-06-16 10:29:33

神经网络图像分类算法

2022-06-01 14:35:39

gitjmeter脚本

2022-06-17 14:22:02

网络风险管理

2022-06-06 06:10:00

密码验证安全

2022-05-28 10:44:43

云成本云架构多云

2022-05-30 10:31:34

Bash脚本Linux

同话题下的热门内容

哪个版本的JVM最快?无代码软件发展简史及未来趋势携程基于 GraphQL 的前端 BFF 服务开发实践为什么会存在 1px 问题?怎么解决?一文搞定常考Vue-Router知识点EcmaScript 2022 正式发布,有哪些新特性?一文详解|增长那些事儿远程医疗:优势、前景和现有IT解决方案

编辑推荐

太厉害了,终于有人能把TCP/IP协议讲的明明白白了!牛人5次面试腾讯不成功的经验HBase原理–所有Region切分的细节都在这里了Javascript如何监听页面刷新和关闭事件如何搭建一个HTTPS服务端
我收藏的内容
点赞
收藏

51CTO技术栈公众号