简介
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 此查询表明未利用类型索引，导致完全表扫描。造成这种情况的原因是查询计划程序依赖于表统计信息。type 列的基数非常低 - 5 个值，这意味着 3400 万行中有 630 万行具有 flat 值，约占数据集的 1/6。因为 type='flat' 行分布在所有数据块中（每个块约 24 行），所以任何单个块中都具有 flat 值的概率非常高（在每个块有 24 行的情况下，概率为 1/6）。因此，查询计划程序确定并行顺序扫描比读取索引（然后在数据中搜索相关行）更有效。
我的同事 Vadim Punski 实际上提出了一种方法，可以大大加快此查询的速度。我们已将解决方案发布 在这里，但将其排除在外，因为它代表了 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中创建一个表。这允许将整个Postgres表镜像到ClickHouse中。在实现方面，这与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）。如果设置为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分钟的全局时间限制，因此此查询无法完成。为了解决这个问题，我们对type列进行过滤以获取数据的子集 - 这些查询中的每一个都可以利用下推的过滤器并在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内存和2个核心。我们无法查看分配给每个Supabase实例的资源，但这可能更多。
- 所有查询都执行了5次，并使用了其中的最小值。这确保了我们使用两个数据库的“热”性能并利用任何文件系统缓存。
- 我们已经优化了ClickHouse表的索引键以最大限度地减少扫描的行数。
尽管存在这些差异，但ClickHouse在线性扫描和分析型查询方面显然表现出色，尤其是在可以利用主索引时 - 这得到了我们更严格的基准测试的证实。
结论
在本博文系列的第一部分中，我们展示了ClickHouse和Postgres是如何互补的，并通过示例演示了如何使用ClickHouse的原生函数和表引擎在它们之间轻松移动数据。在下一部分中，我们将展示如何使用Postgres为字典提供支持，这些字典会自动保持同步并用于加速连接查询。
同时，如果您想了解有关Postgres集成的更多信息，我们有一个关于数据摄取的免费培训课程，其中广泛介绍了这些主题。
