分布式 PostgreSQL 集群(Citus)官方示例 - 实时仪表盘 (3)

如果您要运行全局查询，则会出现类似的问题，例如在上个月访问您客户的任何站点的唯一 IP 地址的数量。在没有 HLL 的情况下，此查询涉及将 IP 地址列表从 worker 传送到 coordinator 以进行重复数据删除。这既是大量的网络流量，也是大量的计算。 通过使用 HLL，您可以大大提高查询速度。

首先你必须安装 HLL 扩展；github repo 有说明。接下来，您必须启用它：

postgresql-hll

https://github.com/citusdata/postgresql-hll

CREATE EXTENSION hll;

这在 Hyperscale 上是不必要的，它已经安装了 HLL 以及其他有用的扩展。

现在我们准备好在 HLL 汇总中跟踪 IP 地址。首先向汇总表添加一列。

ALTER TABLE http_request_1min ADD COLUMN distinct_ip_addresses hll;

接下来使用我们的自定义聚合来填充列。 只需将它添加到我们汇总函数中的查询中：

@@ -1,10 +1,12 @@ INSERT INTO http_request_1min ( site_id, ingest_time, request_count, success_count, error_count, average_response_time_msec + , distinct_ip_addresses ) SELECT site_id, minute, COUNT(1) as request_count, SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count, SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count, SUM(response_time_msec) / COUNT(1) AS average_response_time_msec + , hll_add_agg(hll_hash_text(ip_address)) AS distinct_ip_addresses FROM http_request

仪表板查询稍微复杂一些，您必须通过调用 hll_cardinality 函数读出不同数量的 IP 地址：

SELECT site_id, ingest_time as minute, request_count, success_count, error_count, average_response_time_msec, hll_cardinality(distinct_ip_addresses) AS distinct_ip_address_count FROM http_request_1min WHERE ingest_time > date_trunc('minute', now()) - interval '5 minutes';

HLL 不仅速度更快，还可以让你做以前做不到的事情。 假设我们进行了汇总，但我们没有使用 HLL，而是保存了确切的唯一计数。 这很好用，但您无法回答诸如在过去的一周内，我们丢弃了原始数据有多少不同的会话？之类的问题。

使用 HLL，这很容易。您可以使用以下查询计算一段时间内的不同 IP 计数：

SELECT hll_cardinality(hll_union_agg(distinct_ip_addresses)) FROM http_request_1min WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

您可以在项目的 GitHub 存储库中找到有关 HLL 的更多信息。

postgresql-hll

https://github.com/aggregateknowledge/postgresql-hll

使用 JSONB 的非结构化数据

Citus 与 Postgres 对非结构化数据类型的内置支持配合得很好。 为了证明这一点，让我们跟踪来自每个国家/地区的访客数量。 使用半结构数据类型可以让您不必为每个国家添加一列，并最终得到具有数百个稀疏填充列的行。我们有一篇博文解释了半结构化数据使用哪种格式。这篇文章推荐使用 JSONB，在这里我们将演示如何将 JSONB 列合并到您的数据模型中。

博文

https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb

首先，将新列添加到我们的汇总表中：

ALTER TABLE http_request_1min ADD COLUMN country_counters JSONB;

接下来，通过修改汇总函数将其包含在汇总中：

@@ -1,14 +1,19 @@ INSERT INTO http_request_1min ( site_id, ingest_time, request_count, success_count, error_count, average_response_time_msec + , country_counters ) SELECT site_id, minute, COUNT(1) as request_count, SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count SUM(response_time_msec) / COUNT(1) AS average_response_time_msec - FROM http_request + , jsonb_object_agg(request_country, country_count) AS country_counters + FROM ( + SELECT *, + count(1) OVER ( + PARTITION BY site_id, date_trunc('minute', ingest_time), request_country + ) AS country_count + FROM http_request + ) h

现在，如果您想在仪表板中获取来自美国的请求数量，您可以将仪表板查询修改为如下所示：

SELECT request_count, success_count, error_count, average_response_time_msec, COALESCE(country_counters->>'USA', '0')::int AS american_visitors FROM http_request_1min WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval; 更多

分布式 PostgreSQL 集群(Citus)官方示例 - 多租户应用程序实战