PostgreSQL这么强大？难怪它无处不在

PostgreSQL 被广泛认为是最受欢迎的 SQL 数据库之一，通常在涉及在线事务处理系统的项目中成为首选。然而，它的多功能性远不止于此，能够有效地管理不太常见的 SQL 使用场景以及不使用 SQL 的工作流程。在这篇文章中，我们将探讨 PostgreSQL 在各种情况下的出色表现，并提供如何在这些情况下使用它的建议。

过去十年里，科技格局发生了巨大的变化。一方面，我们利用大数据、数据湖、Spark等并行处理框架以及低代码工具，在OLAP解决方案上取得了显著进展。另一方面，OLTP事务变得越来越复杂，数据不再具有很强的关系性，甚至变得非关系化，而托管架构也有了显著的演变。这些变化对技术行业产生了深远的影响。

如今，常常在一个数据库中同时执行OLTP和OLAP任务十分常见，这种策略被称为混合事务/分析处理（HTAP）。这种方法可以避免数据库间的数据传输，节省时间并允许更频繁地执行复杂查询（比如每15分钟一次）。然而，这会给数据库带来沉重负担，因为OLAP事务可能会长时间锁定表，从而减慢OLTP操作。

另一个重要的变化是我们处理的数据类型。我们处理的数据越来越多地是非关系型的数据格式，比如 JSON 或 XML，机器学习数据，如嵌入式数据，以及地理空间数据或时间序列数据。数据领域通常是基于非 SQL 的。

此外，我们的任务已经变得更加复杂。我们不再仅仅计算汇总数据，现在我们要找出相似的文档，训练大规模语言模型，并处理数百万来自物联网设备的指标。

幸好，PostgreSQL的擴展性使其非常適合這些不斷變化的流程。不管是處理傳統的關係數據還是更複雜的結構，PostgreSQL提供各種擴展來提高性能。我們來深入這些流程，看看PostgreSQL能怎樣幫助我們。

全文搜索（FTS）是一种技术，它涉及扫描文档中的每个单词，以将其与查询匹配。它不仅限于查找包含特定短语的文档，还考虑了类似短语、拼写错误、模式、通配符、同义词等变体。这种复杂性使得全文搜索（FTS）更难执行，因为每个查询变得更加复杂，并且可能会产生更多的假阳性结果。此外，数据集不仅需要被扫描，还需要被转换和预处理，以便在搜索时计算和利用聚合值。

PostgreSQL 支持使用 tsvector 数据类型和 _pgtrgm 扩展进行全文检索。tsvector 让我们从文档中提取词元并为它们建立索引（GiST 或 GIN）。_pgtrgm 扩展让我们从文本中提取三字符单元，然后对它们进行模糊匹配查询。

虽然这可以运作，但内置的Postgres本身的机制可能还不够强大。如下所示的各种基准测试表明，对于大型（100GB以上）语料库，建立索引可能需要超过24小时，并且很可能会因内存不足而失败。

另一个限制是，这些解决方案不支持语义搜索。我们虽然可以轻松搜索特定单词，但无法搜索同义词。同样，要找到只包含一个词而不包含另一个词的文本也更加困难。幸运的是，所有这些问题都可以得到解决。

虽然 Postgres 本身支持全文搜索（FTS），但其他扩展程序表现得更好。例如，_pgsearch，ParadeDB 的一个组件 _pgsearch，比 tsvector 解决方案快 20 倍。此扩展程序基于 Okapi BM25 算法，该算法常被搜索引擎用来评估文档的相关性。它利用逆文档频率（IDF）公式。

_pgsearch 意思是：

• 基于 Postgres 构建，无需依赖任何外部搜索引擎
• 采用了类似 Apache Lucene 的解决方案
• 支持模糊搜索、数据聚合、相关性调整以及高亮显示
• 采用了与 Elasticsearch 相同的算法
• 无需手动重新索引

这使PostgreSQL与Elasticsearch非常具有竞争力，详见基准测试结果，PostgreSQL和Elasticsearch的对比。

OLAP（在线分析处理）是一类软件技术，使用户能够在大量数据上执行复杂的分析操作和查询。OLAP主要用于实现数据挖掘、业务报告和分析，让用户能够从数据中获取有助于决策的洞察。

OLAP系统主要用于在大规模数据集上执行聚合和计算的查询，通常处理的是历史数据，而不是实时信息。这些系统被优化用于读取密集型操作，常用于分析趋势、识别模式或跟踪关键绩效指标（如KPI）的场合。

Postgres主要针对OLTP工作负载。它支持锁定机制、隔离级别和并发访问。然而，虽然这些功能极大地增强了OLTP解决方案，但对于OLAP查询却不利。原因在于：

• 在处理复杂连接时效率较低
• 隔离级别和锁会降低性能
• OLTP依赖于缓存数据，而OLAP则不依赖缓存数据
• PostgreSQL需要支持行级安全等会降低OLAP查询性能的功能，以及其他影响OLAP查询性能的因素

如[这个基准测试]所示，Postgres 是非常多功能的，但处理 OLAP 时，其速度可能比专用解决方案慢几个数量级。例如，在处理视频数据时，ClickHouse 的速度甚至比 Postgres 快 20 倍。幸运的是，Postgres 有许多专门针对 OLAP 的扩展。

Postgres 中增强 OLAP 的解决方案之一是 ParadeDB，它采用列式存储。它是 PostgreSQL 的一个扩展插件，利用 pg_lakehouse 扩展来运行使用 DuckDB 的查询，这是一个进程内 OLAP 数据库。得益于列式存储，它在性能上高度优化，可以在性能上比原生 PostgreSQL 快几个数量级。他们声称可以快 94 倍，但这取决于您的具体用例，实际可能更快 更多。

DuckDB 是一个矢量化查询处理引擎，它以足够小的数据块处理数据，使其适合缓存内存，从而避免了由于昂贵的 I/O 操作导致的性能下降。利用列式存储，DuckDB 可以利用单指令多数据（SIMD）指令同时对多个数据执行相同的操作，从而进一步提高性能。

在提高性能方面，除了使用列存储格式之外，还可以通过优化OLAP操作中的数据计算过程来进一步提升效率。这通常涉及到计算诸如平均值和中位数等数据集的聚合。当原始数据集发生变化时，我们不必每次都完全重新计算这些聚合值。我们还可以寻找更高效的计算策略，而不是每次更新数据源时都进行全面的重新计算。

Hydra 通过 pg_ivm 使用物化视图来存储计算的分析信息，从而增强聚合查询的处理能力。这种方法通过避免每次执行新查询时都重新计算存储的视图结果，从而减少了冗余计算。在 [这个基准测试](https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJBdGhlbmEgKHNpbmdsZSkiOmZhbHNlLCJBdXJvcmEgZm9yIE15U1FMIjpmYWxzZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiQ2l0dXMiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOnRydWUsImNsaWNraG91c2UtbG9jYWwgKHNpbmdsZSkiOnRydWUsIkNsaWNrSG91c2UiOmZhbHNlLCJDbGlja0hvdXNlICh0dW5lZCkiOmZhbHNlLCJDbGlja0hvdXNlICh6c3RkKSI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQiOnRydWUsIkNyYXRlREIiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsImRhdGFmdXNpb24iOnRydWUsIkFwYWNoZSBEb3JpcyI6ZmFsc2UsIkRydWlkIjpmYWxzZSwiRHVja0RCIChQYXJxdWV0KSI6dHJ1ZSwiRHVja0RCIjp0cnVlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR3JlZW5wbHVtIjpmYWxzZSwiSGVhdnlBSSI6ZmFsc2UsIkh5ZHJhIjp0cnVlLCJJbmZvYnJpZ2h0IjpmYWxzZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6ZmFsc2UsIk1hcmlhREIiOmZhbHNlLCJNb25ldERCIjpmYWxzZSwiTW9uZ29EQiI6ZmFsc2UsIk15U1FMIChNeUlTQU0pIjpmYWxzZSwiTXlTUUwiOmZhbHNlLCJQaW5vdCI6ZmFsc2UsIlBvc3RncmVTUUwiOnRydWUsIlF1ZXN0REIgKHBhcnRpdGlvbmVkKSI6ZmFsc2UsIlF1ZXN0REIiOmZhbHNlLCJSZWRzaGlmdCI6ZmFsc2UsIlNlbGVjdERCIjpmYWxzZSwiU2luZ2xlU3RvcmUiOmZhbHNlLCJTbm93Zmxha2UiOmZhbHNlLCJTUUxpdGUiOmZhbHNlLCJTdGFyUm9ja3MgKHR1bmVkKSI6ZmFsc2UsIlN0YXJSb2NrcyI6ZmFsc2UsIlRpbWVzY2FsZURCIChjb21wcmVzc2lvbikiOmZhbHNlLCJUaW1lc2NhbGVEQiI6ZmFsc2UsIkR1Y2tEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJQYXJhZGVEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwiUGFyYWRlREIgKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlfSwidHlwZSI6eyJzdGF0ZWxlc3MiOnRydWUsIm1hbmFnZWQiOnRydWUsIkphdmEiOnRydWUsImNvbHVtbi1vcmllbnRlZCI6dHJ1ZSwiQysrIjp0cnVlLCJNeVNRTCBjb21wYXRpYmxlIjp0cnVlLCJyb3ctb3JpZW50ZWQiOnRydWUsIkMiOnRydWUsIlBvc3RncmVTUUwgY29tcGF0aWJsZSI6dHJ1ZSwiQ2xpY2tIb3VzZSBkZXJpdmF0aXZlIjp0cnVlLCJlbWJlZGRlZCI6dHJ1ZSwiUnVzdCI6dHJ1ZSwic2VhcmNoIjp0cnVlLCJkb2N1bWVudCI6dHJ1ZSwidGltZS1zZXJpZXMiOnRydWV9LCJtYWNoaW5lIjp7InNlcnZlcmxlc3MiOnRydWUsIjE2YWN1Ijp0cnVlLCJMIjp0cnVlLCJNIjp0cnVlLCJTIjp0cnVlLCJYUyI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDUwMGdiIGdwMiI6dHJ1ZSwiYzUuNHhsYXJnZSwgNTAwZ2IgZ3AyIjp0cnVlLCJjNmEubWV0YWwsIDUwMGdiIGdwMiI6dHJ1ZSwiMTYgdGhyZWFkcyI6dHJ1ZSwiMjAgdGhyZWFkcyI6dHJ1ZSwiMjQgdGhyZWFkcyI6dHJ1ZSwiMjggdGhyZWFkcyI6dHJ1ZSwiMzAgdGhyZWFkcyI6dHJ1ZSwiNDggdGhyZWFkcyI6dHJ1ZSwiNjAgdGhyZWFkcyI6dHJ1ZSwibTVkLjI0eGxhcmdlIjp0cnVlLCJmMTZzIHYyIjp0cnVlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6dHJ1ZSwicmEzLjE2eGxhcmdlIjp0cnVlLCJyYTMuNHhsYXJnZSI6dHJ1ZSwicmEzLnhscGx1cyI6dHJ1ZSwiUzIiOnRydWUsIlMyNCI6dHJ1ZSwiMlhMIjp0cnVlLCIzWEwiOnRydWUsIjRYTCI6dHJ1ZSwiWEwiOnRydWV9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTIiOnRydWUsIjE2Ijp0cnVlLCIzMiI6dHJ1ZSwiNjQiOnRydWUsIjEyOCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwidW5kZWZpbmVkIjp0cnVlfSwibWV0cmljIjoiaG90IiwicXVlcmllcyI6W3RydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWVdfQ==) 中，你可以看到它性能的提升。

随着物联网（IoT）技术的发展和设备融入我们的日常生活，新的挑战也随之出现。其中之一是高效处理每天生成的海量传感器数据。

这些传感器产生的数据属于时间序列类别；它表示一个按照时间顺序记录的测量值序列，这些值可以是实数值或离散值，并且它们是在固定、规律的时间间隔内记录的——例如，每分钟记录一次家中的温度。这些数据不仅在即时监控方面有用，还能用于预测分析，比如根据过去趋势预测未来情况，或识别并纠正异常，从而优化我们日常生活的体验。时间序列在多种类型的数据中都有用，尤其是在时间是数据集结构中的一个重要因素时，这些数据具有连续的实数值或离散特征。

Postgres支持数组，可以用于时间序列数据的分析。它们还能进行透明压缩，并且可以像普通表一样轻松管理和操作。Postgres还提供了generate_series函数，可以用来生成数据并进行处理。

虽然数组可以像普通函数一样进行查询，但它们不提供专门支持时间序列数据所需的功能。不过在 Postgres 中，还有很多其他扩展可以使用。

Timescale 是一个针对 PostgreSQL 的扩展，可将其转换为高效的时序数据库。它利用智能分区间、超表和聚合功能优化时序数据处理。超表会自动按时间分区存储数据，而表仍然看起来像一个普通的表。

通过按时间划分数据，Timescale 大大提高了查询性能，避免了不必要的全表扫描。它还提供了连续聚合，允许实时更新聚合计算，以便实时处理新数据。此外，Timescale 提供了专门的超函数，用于时间序列分析，使数据分桶、聚合、窗口划分和流水线构建变得简单，从而实现高效的数据处理。这可以带来高达 100倍的性能提升。

Timescale還有許多功能可以提高性能並降低使用成本。例如，它可以自動將舊數據存儲在S3中，以節省很少被訪問的數據的存儲費用。

文档数据库是一种 NoSQL 数据库，用于以文档形式存储、检索和管理数据，通常使用 JSON、BSON（二进制 JSON）或 XML 等格式。每个文档代表一个数据项，并包含字段和值，这些字段和值可以是字符串、数字、数组或嵌套文档等类型。

与关系数据库不同，关系数据库中的数据以表格形式组织，具有行和列，文档数据库则允许更加灵活的数据结构。每个文档可以有不同的结构，非常适合数据模型不断变化或属性不同的场景。这种灵活性使得文档数据库非常适合管理半结构化或非结构化的数据，例如内容管理系统、用户资料、产品目录等。

Postgres 原生支持多种非 SQL 类型。它可以处理 XML、JSON、BSON、空间数据、向量和时间范围等数据类型，等等，更不用说还有很多其他类型。它还支持 hstore 来存储任意键值对。这使得 Postgres 能够处理各种类型的文档。

如果这还不够用，Postgres 与 Mongo 兼容性很好。

FerretDB 是一个开源数据库，设计为 MongoDB 的轻量级替代选择。它作为翻译层存在，充当 MongoDB 查询与传统 SQL 数据库（如 PostgreSQL）之间的桥梁。这使得用户可以在使用类似 MongoDB 的文档数据库 API 的同时，将数据存储在 PostgreSQL 中。简而言之，FerretDB 结合了易于开发人员使用的 API 与 SQL 数据库的可靠性和功能，为希望或需要在开源环境中工作的用户提供了一个灵活的方案。您可以在 这篇文章 中看到一些性能对比。

我们旨在将各种类型的数据存储在一个SQL数据库中，但并没有直接的方法来存储复杂的数据类型，比如电影、歌曲、演员、PDF、图片或视频。这使得识别相似性或执行聚类等任务变得困难。为了进行这样的比较，我们需要将这些对象转换为数值表示——即一组数字（向量或嵌入），来捕捉对象的关键特性。例如，一部电影可以用评分、时长（分钟）、演员数量和使用的歌曲数量等特性来表示。让我们看看Postgres如何支持这一点。

PostgreSQL通过pgvector扩展支持嵌入。此扩展引入了一种新的列类型和专门用于存储及处理嵌入的运算符。使用pgvector，我们能够执行元素级加法等算术运算，计算向量间的欧几里得或余弦距离，计算内积，确定欧几里得范数。它还支持处理向量的其他多种操作。

如果 pgvector 不足以满足需求，pgai 可以简化在 PostgreSQL 中开发搜索和 RAG AI 应用程序的过程。使用 pgai，嵌入式和 AI 生成模型可以更紧密地与数据库集成。这样，您可以直接通过 SQL 查询执行多项任务，例如：

• 为您的数据生成向量嵌入。
• 从诸如Claude Sonnet 3.5、OpenAI GPT-4、Cohere Command和Llama 3等模型中检索大型语言模型的聊天回复，通过Ollama平台。
• 对您的数据进行推理，支持分类、总结和丰富现有PostgreSQL中的关系数据。

您可以在这篇文章中了解更多解决方案的比较。

PostgreSQL 是最常用的一种 SQL 数据库，但它不仅仅是一个 SQL 引擎，它还有很多其他功能。通过各种扩展，它可以处理非关系型数据，执行全文搜索，支持数据分析工作流程，处理时间序列数据等。传统上用于区分 OLAP 和 OLTP 的界限已经不再重要，因为它可以在一个数据库中同时处理 HTAP 工作流。这种多功能性让 PostgreSQL 能够应对各种数据处理需求。