简介
PostgreSQL 和 ClickHouse 代表了开源数据库中的佼佼者，各自以其优势和劣势应对不同的用例。最近在 ClickHouse Cloud 中启用了 PostgreSQL（和 MySQL）集成后，我们认为应该借此机会提醒用户如何将这些强大的集成与 ClickHouse 结合使用。虽然我们专注于 Postgres，但所有函数都有其等效的 MySQL 版本，并且应该很容易推导出来。
在本系列的第一部分中，我们将介绍 PostgreSQL 函数和表引擎。第二篇文章将探讨数据库引擎，并演示如何将 Postgres 与 ClickHouse 字典结合使用。
如果您想更深入地研究这些示例，ClickHouse Cloud 是一个很好的起点 - 使用免费试用版启动集群，加载数据，让我们处理基础设施，然后开始查询！
有兴趣在 ClickHouse Cloud 中试用 Postgres 集成吗？立即开始使用，即可获得 300 美元免费额度，有效期 30 天。
我们在 ClickHouse Cloud 中使用开发服务——也可以使用生产服务，两者都可以。对于我们的 Postgres 实例，我们使用 Supabase，它提供了一个慷慨的免费层级，足以满足我们的示例需求。
Supabase 提供的不仅仅是 Postgres 数据库，它还是 Firebase 的完整替代方案，具有身份验证、即时 API、边缘函数、实时订阅和存储。如果您想使用本文中的数据构建一个真正的应用程序，Supabase 将加速其开发和部署。
互补性
PostgreSQL，也称为 Postgres，是一个免费开源的关系数据库管理系统，专注于可扩展性、SQL 兼容性和通过事务实现的 ACID 属性。作为世界上最流行的 OSS OLTP（在线事务处理）数据库，它适用于数据高度事务性且需要支持数千个并发用户的用例。
ClickHouse 是一个开源的面向列的 OLAP（在线分析处理）数据库，用于实时分析工作负载。它专注于支持闪电般快速的分析查询，通常用于实时分析、可观测性和数据仓库等用例。
最近出现了一种成功的架构模式，将 ClickHouse 与 PostgreSQL 结合使用，为分析“速度层”提供动力。在这种模式中，PostgreSQL 用作事务性真理来源，并服务于以基于行的操作为主的操作用例。然而，高级分析查询最好通过 ClickHouse 利用其面向列的模型来以毫秒级响应复杂的聚合。这种互补性关系极大地受益于两种 OSS 技术之间存在的紧密集成。
业务案例和数据集
场景：我们正在运营一个房产列表网站，为数千用户提供服务。价格可能会更新，并且/或者当房产下架或降价时，行可能会被删除。这代表了 Postgres 的一个很好的用例，它将保存数据的真理来源。我们虚构的业务也希望对这些数据进行分析，从而需要在 Postgres 和 ClickHouse 之间移动数据。
在我们的示例中，我们使用了相对较小的 Postgres 和 ClickHouse 实例：ClickHouse Cloud 中的开发者层级（高达 1 TB 存储和 16 GB 总内存）和 Supabase 中的免费层级。后者将数据库大小限制为 500 MB。因此，我们选择了一个中等大小的数据集，该数据集符合我们的业务用例和这些实例大小：英国房价数据集。在我们的文档中通篇使用，这在很大程度上符合这些要求，只有 2800 万行。每行代表过去 20 年在英国的一次房屋销售，字段代表价格、日期和位置。字段的完整描述可以在这里找到。
我们将此数据集作为与 Postgres 兼容的 SQL 分发，可直接插入，可从这里下载。
加载数据
注册 Supabase 后，在免费层级下创建一个新项目，并设置适当的安全密码，然后从设置中获取数据库端点。
在我们的示例中，我们使用 psql 客户端执行所有查询。Supabase 还为那些想要远离终端的人提供了 Web 客户端。我们的 Postgres 模式如下所示。我们还创建了一些索引，后续查询应该能够直观地利用这些索引。
CREATE TABLE uk_price_paid ( id integer primary key generated always as identity, price INTEGER, date Date, postcode1 varchar(8), postcode2 varchar(3), type varchar(13), is_new SMALLINT, duration varchar(9), addr1 varchar(100), addr2 varchar(100), street varchar(60), locality varchar(35), town varchar(35), district varchar(40), county varchar(35) ) psql -c "CREATE INDEX ON uk_price_paid (type)" psql -c "CREATE INDEX ON uk_price_paid (town)" psql -c "CREATE INDEX ON uk_price_paid (extract(year from date))"
一些基本的分析查询
在 ClickHouse 中加载数据之前，让我们回顾一下为什么我们可能需要在 Postgres 之外进行分析工作负载。请注意以下查询的计时。呈现的结果是五次执行中最快的一次。我们还尝试优化这些查询，尽可能利用索引，并欢迎进一步的建议！
英国公寓的年平均价格
psql -c "\timing" -c "SELECT extract(year from date) as year, round(avg(price)) AS price FROM uk_price_paid WHERE type = 'flat' GROUP BY year ORDER BY year" year | price ------+-------- 1995 | 59004 1996 | 63913 … 2021 | 310626 2022 | 298977 (28 rows) Time: 28535.465 ms (00:28.535)
这比预期的要慢。EXPLAIN 对于此查询，指示类型索引未被利用，从而导致全表扫描。原因是查询计划器依赖于表统计信息。类型列的基数非常低 - 5 个值，意味着在 3400 万行中，有 630 万行具有 flat 值，约占总数据集的 1/6。由于 type='flat' 行分布在所有数据块中（每个块约 24 行），因此在任何单个块中具有 flat 值的概率非常高（每个块 24 行时，概率为 1/6）。因此，查询计划器确定并行顺序扫描将比读取索引（然后在数据中搜索相关行）更有效。
我的同事 Vadim Punski 实际上提出了一种大大加快此查询速度的方法。我们已在此处发布了解决方案here，但已排除，因为它代表了对 Postgres 的相当糟糕的使用，并将导致大量的存储空间占用。由于 120 秒的查询超时，对表架构的更改也无法在 Supabase 的免费层级上完成。
城市中最昂贵的邮政编码
从上面的查询中我们知道，如果线性扫描更便宜，Postgres 将不会使用索引，因为大多数块中都存在过滤器子句值。如果我们按不太常见的城市（例如布里斯托尔）进行过滤，则可以利用索引，并且查询性能的提高非常显着。
psql -c "\timing" -c "SELECT postcode1, round(avg(price)) AS price FROM uk_price_paid WHERE town='BRISTOL' GROUP BY postcode1 ORDER BY price DESC LIMIT 10" postcode1 | price -----------+-------- BS1 | 410726 BS19 | 369000 BS18 | 337000 BS40 | 323854 BS9 | 313248 BS8 | 301595 BS41 | 300802 BS6 | 272332 BS35 | 260563 BS36 | 252943 (10 rows) Time: 543.364 ms
相关的查询计划显示了我们索引的使用。如果您在此处更改城市（例如伦敦），Postgres 可能会利用顺序扫描，具体取决于目标城市中出售的房产数量。
伦敦过去 20 年价格变化百分比最大的邮政编码
psql -c "\timing" -c "SELECT med_2002.postcode1, median_2002, median_2022, round(((median_2022 - median_2002)/median_2002) * 100) AS percent_change FROM ( SELECT postcode1, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2002 FROM uk_price_paid WHERE town = 'LONDON' AND extract(year from date) = '2002' GROUP BY postcode1 ) med_2002 INNER JOIN ( SELECT postcode1, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2022 FROM uk_price_paid WHERE town = 'LONDON' AND extract(year from date) = '2022' GROUP BY postcode1 ) med_2022 ON med_2002.postcode1=med_2022.postcode1 ORDER BY percent_change DESC LIMIT 10" postcode1 | median_2002 | median_2022 | percent_change -----------+-------------+-------------+---------------- EC3A | 260000 | 16000000 | 6054 SW1A | 525000 | 17500000 | 3233 EC2M | 250000 | 4168317.5 | 1567 EC3R | 230000 | 2840000 | 1135 W1S | 590000 | 6410000 | 986 WC2A | 255000 | 2560000 | 904 W1K | 550000 | 5000000 | 809 W1F | 280000 | 2032500 | 626 WC1B | 390000 | 2205000 | 465 W1J | 497475 | 2800000 | 463 (10 rows) Time: 8903.676 ms (00:08.904)
此查询实际上性能相当好。此查询对 town 和 extract(year from date) 索引都执行位图扫描。这大大减少了需要读取的数据量，如查询计划所示，从而加快了查询速度。
作为 ClickHouse 专家，我们欢迎进一步改进这些查询以加快其速度，并欢迎除简单强制使用索引之外的其他替代方案！
稍后我们将在 ClickHouse 开发者实例中执行这些查询。由于底层硬件和可用资源存在差异，这并不是一个公平的基准。我们还可以利用其他 PostgreSQL 功能来进一步优化这些查询，例如 CLUSTER。但是，我们应该看到显着改进，这表明了为什么我们可能希望将这种工作负载类型转移到 ClickHouse。
从 ClickHouse 查询 Postgres
我们有几种方法可以使用 ClickHouse 访问 Postgres 中的数据
- 使用 postgresql 函数。这会为每个查询创建一个连接并流式传输到 ClickHouse。简单的 WHERE 子句会在可能的情况下下推（例如，使用 ClickHouse 特定的函数会阻止下推）以识别匹配的行。返回匹配的行后，聚合、JOIN、排序和 LIMIT 子句将在 ClickHouse 中执行。
- 使用 PostgreSQL 表引擎在 ClickHouse 中创建表。这允许在 ClickHouse 中镜像整个 Postgres 表。在实现方面，这与 postgresql 函数没有区别，即，行选择在可能的情况下下推，但这大大简化了查询语法 - 我们可以像在 ClickHouse 中使用任何其他表一样使用该表。
- 使用 PostgreSQL 数据库引擎创建数据库。在这种情况下，我们镜像整个数据库，并且可以使用其所有相应的表。这也允许我们执行 DDL 命令来修改和删除底层 PostgreSQL 实例中表中的列。
前两个在 ClickHouse Cloud 中可用，后者即将公开。让我们演示一下上面的函数，从 ClickHouse 重新运行查询。这里数据仍然保留在 PostgreSQL 中，数据仅在查询执行期间流式传输到 ClickHouse - 它不会持久化到本地 MergeTree 表中。这主要用于临时分析以及将小型数据集连接到本地表。请注意，我们的 ClickHouse Cloud 实例与我们的 Supabase 数据库位于相同的 AWS 区域，以最大限度地减少网络延迟并最大限度地提高带宽连接。
英国公寓的年平均价格
SELECT toYear(date) AS year, round(avg(price)) AS price FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'flat' GROUP BY year ORDER BY year ASC ┌─year─┬──price─┐ │ 1995 │ 59004 │ │ 1996 │ 63913 │ ... │ 2021 │ 310626 │ │ 2022 │ 298977 │ └──────┴────────┘ 28 rows in set. Elapsed: 26.408 sec. Processed 4.98 million rows, 109.59 MB (175.34 thousand rows/s., 3.86 MB/s.)
上面的查询再次导致 Postgres 中的全表扫描，结果流式传输到 ClickHouse 在其中进行聚合。这提供了与直接在 Postgres 上执行查询相当的性能。
城市中最昂贵的邮政编码
SELECT postcode1, round(avg(price)) AS price FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE town='BRISTOL' AND postcode1 != '' GROUP BY postcode1 ORDER BY price DESC LIMIT 10 ┌─postcode1─┬──price─┐ │ BS1 │ 410726 │ │ BS19 │ 369000 │ │ BS18 │ 337000 │ │ BS40 │ 323854 │ │ BS9 │ 313248 │ │ BS8 │ 301595 │ │ BS41 │ 300802 │ │ BS6 │ 272332 │ │ BS35 │ 260563 │ │ BS36 │ 252943 │ └───────────┴────────┘ 10 rows in set. Elapsed: 2.362 sec. Processed 424.39 thousand rows, 15.11 MB (143.26 thousand rows/s., 5.10 MB/s.)
这次 town 子句被下推到 Postgres，索引在其中被利用，从而减少了要返回到 ClickHouse 的数据量。性能主要取决于两个数据库的带宽和连接性。尽管位于相同的 AWS 区域，但我们仍然遇到一些开销，但性能仍然相当。
伦敦过去 20 年价格变化百分比最大的邮政编码
SELECT med_2002.postcode1, median_2002, median_2022, round(((median_2022 - median_2002) / median_2002) * 100) AS percent_change FROM ( SELECT postcode1, median(price) AS median_2002 FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE (town = 'LONDON') AND (toYear(date) = '2002') GROUP BY postcode1 ) AS med_2002 INNER JOIN ( SELECT postcode1, median(price) AS median_2022 FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE (town = 'LONDON') AND (toYear(date) = '2022') GROUP BY postcode1 ) AS med_2022 ON med_2002.postcode1 = med_2022.postcode1 ORDER BY percent_change DESC LIMIT 10 ┌─postcode1─┬─median_2002─┬─median_2022─┬─percent_change─┐ │ EC3A │ 260000 │ 16000000 │ 6054 │ │ SW1A │ 525000 │ 17500000 │ 3233 │ │ EC2M │ 250000 │ 4168317.5 │ 1567 │ │ EC3R │ 230000 │ 2840000 │ 1135 │ │ W1S │ 590000 │ 6410000 │ 986 │ │ WC2A │ 255000 │ 2560000 │ 904 │ │ W1K │ 550000 │ 5000000 │ 809 │ │ W1F │ 280000 │ 2032500 │ 626 │ │ WC1B │ 390000 │ 2205000 │ 465 │ │ W1J │ 497475 │ 2800000 │ 463 │ └───────────┴─────────────┴─────────────┴────────────────┘ 10 rows in set. Elapsed: 59.859 sec. Processed 4.25 million rows, 157.75 MB (71.04 thousand rows/s., 2.64 MB/s.)
此查询明显比直接 Postgres 执行慢。这很大程度上归因于 toYear(date) 没有下推到 Postgres，而 (extract(year from date)) 索引可以在其中被利用。此查询还会从 Postgres 流式传输结果两次 - 连接的每一侧一次。
但是，我们可以重写此查询以使用 ClickHouse 的条件聚合函数 medianIf。除了更简单和更直观之外，它还通过避免连接和双重读取 Postgres 表而更快。
SELECT postcode1, medianIf(price, toYear(date) = 2002) AS median_2002, medianIf(price, toYear(date) = 2022) AS median_2022, round(((median_2022 - median_2002) / median_2002) * 100) AS percent_change FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE town = 'LONDON' GROUP BY postcode1 ORDER BY percent_change DESC LIMIT 10 ┌─postcode1─┬─median_2002─┬─median_2022─┬─percent_change─┐ │ EC3A │ 260000 │ 16000000 │ 6054 │ │ SW1A │ 525000 │ 17500000 │ 3233 │ │ EC2M │ 250000 │ 4168317.5 │ 1567 │ │ EC3R │ 230000 │ 2840000 │ 1135 │ │ W1S │ 590000 │ 6410000 │ 986 │ │ WC2A │ 255000 │ 2560000 │ 904 │ │ W1K │ 550000 │ 5000000 │ 809 │ │ W1F │ 280000 │ 2032500 │ 626 │ │ WC1B │ 390000 │ 2205000 │ 465 │ │ W1J │ 497475 │ 2800000 │ 463 │ └───────────┴─────────────┴─────────────┴────────────────┘ 10 rows in set. Elapsed: 36.166 sec. Processed 2.13 million rows, 78.88 MB (58.79 thousand rows/s., 2.18 MB/s.)
使用表引擎在语法上简化了这一点。创建它的最简单方法是使用下面的 CREATE AS 语法。当 ClickHouse 在本地创建表时，Postgres 中的类型将映射到等效的 ClickHouse 类型 - 如后续的 SHOW CREATE AS 语句所示。请注意，我们使用设置 external_table_functions_use_nulls = 0，以确保 Null 值表示为其默认值（而不是 Null）。如果设置为 1（默认值），ClickHouse 将为每列创建 Nullable 变体。
CREATE TABLE uk_price_paid_postgresql AS postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') SHOW CREATE TABLE uk_price_paid_postgresql CREATE TABLE default.uk_price_paid_postgresql ( `id` Int32, `price` Int32, `date` Date, `postcode1` String, `postcode2` String, `type` String, `is_new` Int16, `duration` String, `addr1` String, `addr2` String, `street` String, `locality` String, `town` String, `district` String, `county` String ) AS postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '[HIDDEN]')
这使我们之前的查询更简单一些，结果相同。
SELECT postcode1, medianIf(price, toYear(date) = 2002) AS median_2002, medianIf(price, toYear(date) = 2022) AS median_2022, round(((median_2022 - median_2002) / median_2002) * 100) AS percent_change FROM uk_price_paid_postgresql WHERE town = 'LONDON' GROUP BY postcode1 ORDER BY percent_change DESC LIMIT 10 ┌─postcode1─┬─median_2002─┬─median_2022─┬─percent_change─┐ │ EC3A │ 260000 │ 16000000 │ 6054 │ │ SW1A │ 525000 │ 17500000 │ 3233 │ │ EC2M │ 250000 │ 4168317.5 │ 1567 │ │ EC3R │ 230000 │ 2840000 │ 1135 │ │ W1S │ 590000 │ 6410000 │ 986 │ │ WC2A │ 255000 │ 2560000 │ 904 │ │ W1K │ 550000 │ 5000000 │ 809 │ │ W1F │ 280000 │ 2032500 │ 626 │ │ WC1B │ 390000 │ 2205000 │ 465 │ │ W1J │ 497475 │ 2800000 │ 463 │ └───────────┴─────────────┴─────────────┴────────────────┘ 10 rows in set. Elapsed: 28.531 sec. Processed 2.13 million rows, 78.88 MB (74.52 thousand rows/s., 2.76 MB/s.)
我们可以显式定义我们的表类型，并避免使用 SHOW CREATE AS。
CREATE TABLE default.uk_price_paid_v2 ( `price` UInt32, `date` Date, `postcode1` String, `postcode2` String, `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4), `is_new` UInt8, `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2), `addr1` String, `addr2` String, `street` String, `locality` String, `town` String, `district` String, `county` String ) ENGINE = PostgreSQL('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '')
关于性能，有以下几点需要注意
- 如果过滤器子句很简单，例如 =, !=, >, >=, <, <= 和 IN，ClickHouse 可以下推过滤器子句，从而可能利用 Postgres 中的索引。如果它们涉及 ClickHouse 特定的函数（或者如果 Postgres 确定全表扫描是最佳执行方法），则将执行全表扫描，并且不会利用 Postgres 索引。由于需要将整个数据集流式传输到 ClickHouse，这可能会导致查询运行位置的性能差异很大。如果带宽连接不是问题，并且即使直接执行查询，Postgres 也需要执行全表扫描，那么性能差异将不太明显。
- 如果使用
postgres函数或表引擎，请注意 Postgres 需要的查询数量。在我们之前的示例中，我们最大限度地减少了函数的使用以加快查询速度。在这种情况下，需要在能够利用 Postgres 索引以最大限度地减少流式传输到 ClickHouse 的数据之间取得平衡。
从 Postgres 到 ClickHouse
到目前为止，我们只将查询下推到 Postgres。虽然偶尔对临时分析和查询小型数据集很有用，但您最终会希望利用 ClickHouse 的 MergeTree 表及其在分析查询方面的相关性能。在 Postgres 和 ClickHouse 之间移动数据就像使用 INSERT INTO x SELECT FROM 语法一样简单。
在下面的示例中，我们创建一个表并尝试从我们 Supabase 托管的 Postgres 实例中插入数据
CREATE TABLE default.uk_price_paid ( `price` UInt32, `date` Date, `postcode1` LowCardinality(String), `postcode2` LowCardinality(String), `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4), `is_new` UInt8, `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2), `addr1` String, `addr2` String, `street` LowCardinality(String), `locality` LowCardinality(String), `town` LowCardinality(String), `district` LowCardinality(String), `county` LowCardinality(String) ) ENGINE = MergeTree ORDER BY (type, town, postcode1, postcode2) INSERT INTO uk_price_paid_v2 SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') ↘ Progress: 21.58 million rows, 3.99 GB (177.86 thousand rows/s., 32.89 MB/s.) (0.5 CPU, 39.00 MB RAM) 0 rows in set. Elapsed: 121.361 sec. Processed 21.58 million rows, 3.99 GB (177.86 thousand rows/s., 32.89 MB/s.) Received exception from server (version 22.11.1): Code: 1001. DB::Exception: Received from oxvdst5xzq.us-west-2.aws.clickhouse.cloud:9440. DB::Exception: pqxx::sql_error: Failure during '[END COPY]': ERROR: canceling statement due to statement timeout . (STD_EXCEPTION)
在上面的示例中，我们尝试从 Supabase 中提取所有 2800 万行。不幸的是，由于 Supabase 对查询施加了 2 分钟的全局时间限制，因此此查询无法完成。为了解决这个问题，我们按类型列进行过滤以获取数据的子集 - 这些查询中的每一个都可以利用下推的过滤器并在 2 分钟内完成。
INSERT INTO uk_price_paid SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'other' INSERT INTO uk_price_paid SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'detached' INSERT INTO uk_price_paid SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'flat' INSERT INTO uk_price_paid SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'terraced' INSERT INTO uk_price_paid SELECT * EXCEPT id FROM postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_price_paid', 'postgres', '') WHERE type = 'semi-detached'
在撰写本文时，克服这些查询限制需要用户在其数据的适当基数列上拆分数据。但是，其他服务或自我管理的实例可能不会施加此限制。
使用我们新的 MergeTree 表，我们可以直接在 ClickHouse 中执行我们之前的查询。
英国公寓的年平均价格
SELECT toYear(date) AS year, round(avg(price)) AS price FROM uk_price_paid WHERE type = 'flat' GROUP BY year ORDER BY year ASC ┌─year─┬──price─┐ │ 1995 │ 59004 │ │ 1996 │ 63913 │ │ 1997 │ 72302 │ │ 1998 │ 80775 │ │ 1999 │ 93646 │ ... │ 2019 │ 300938 │ │ 2020 │ 319547 │ │ 2021 │ 310626 │ │ 2022 │ 298977 │ └──────┴────────┘ 28 rows in set. Elapsed: 0.079 sec. Processed 5.01 million rows, 35.07 MB (63.05 million rows/s., 441.37 MB/s.)✎
城市中最昂贵的邮政编码
SELECT postcode1, round(avg(price)) AS price FROM uk_price_paid WHERE (town = 'BRISTOL') AND (postcode1 != '') GROUP BY postcode1 ORDER BY price DESC LIMIT 10 ┌─postcode1─┬──price─┐ │ BS1 │ 410726 │ │ BS19 │ 369000 │ │ BS18 │ 337000 │ │ BS40 │ 323854 │ │ BS9 │ 313248 │ │ BS8 │ 301595 │ │ BS41 │ 300802 │ │ BS6 │ 272332 │ │ BS35 │ 260563 │ │ BS36 │ 252943 │ └───────────┴────────┘ 10 rows in set. Elapsed: 0.077 sec. Processed 27.69 million rows, 30.21 MB (358.86 million rows/s., 391.49 MB/s.)✎
伦敦过去 20 年价格变化百分比最大的邮政编码
SELECT postcode1, medianIf(price, toYear(date) = 2002) AS median_2002, medianIf(price, toYear(date) = 2022) AS median_2022, round(((median_2022 - median_2002) / median_2002) * 100) AS percent_change FROM uk_price_paid WHERE town = 'LONDON' GROUP BY postcode1 ORDER BY percent_change DESC ┌─postcode1─┬─median_2002─┬─median_2022─┬─percent_change─┐ │ EC3A │ 260000 │ 16000000 │ 6054 │ │ SW1A │ 525000 │ 17500000 │ 3233 │ │ EC2M │ 250000 │ 4168317.5 │ 1567 │ │ EC3R │ 230000 │ 2840000 │ 1135 │ │ W1S │ 590000 │ 6410000 │ 986 │ 191 rows in set. Elapsed: 0.062 sec. Processed 2.62 million rows, 19.45 MB (41.98 million rows/s., 311.48 MB/s.)✎
这里查询性能的差异是巨大的。为了透明起见，除了“ClickHouse 在分析查询方面更快”之外，还有其他原因。
- 这是一个 ClickHouse Cloud 中的开发者实例，具有 8GB RAM 和 2 个内核。我们无法了解分配给每个 Supabase 实例的资源，但这可能更多。
- 所有查询都执行了 5 次，并使用了其中最小的一次。这确保我们使用两个数据库的“热”性能并利用任何文件系统缓存。
- 我们为 ClickHouse 表优化了主键，以最大限度地减少扫描的行数。
尽管存在这些差异，但 ClickHouse 在线性扫描和分析类型查询方面明显表现出色，尤其是在可以利用主索引的情况下 - 这通过我们更严格的基准测试得到了加强。
结论
在本博客系列的第一部分中，我们展示了 ClickHouse 和 Postgres 如何互补，通过示例演示了如何使用原生 ClickHouse 函数和表引擎在它们之间轻松移动数据。在下一部分中，我们将展示如何使用 Postgres 来驱动字典，这些字典会自动保持同步并用于加速连接查询。
同时，如果您想了解更多关于 Postgres 集成的信息，我们有关于数据摄取的免费培训课程，其中广泛涵盖了这些主题。
