作者：Paulinho Giovannini Pereira
PostgreSQL 以其强大的功能集、对 SQL 标准的兼容性以及开源开发理念而闻名。这些属性使其成为全球许多开发人员和组织的理想选择，难怪它是唯一一个四次获得 DB-Engines 评选的 年度数据库管理系统 (DBMS)，包括 2023 年 的数据库。
但虽然 PostgreSQL 以其强大和通用性而闻名，但随着数据量和应用程序需求的增长，有效扩展它仍然面临挑战。扩展 Postgres 不仅需要深入了解其架构，还需要 对数据库设计进行战略性方法。
此外，扩展复杂性超出了处理更多数据的范围；它们会影响分析设计和整体系统架构，导致工程开销增加，并且需要对系统进行重新设计以支持更大规模的操作。
在本文中，我们将深入探讨扩展 PostgreSQL 的具体策略和工具，解决这些挑战以及处理大规模数据环境的注意事项。我们的重点将是提供有关如何扩展 PostgreSQL 以高效地管理和处理每天数 TB 数据的实用见解和指导，平衡性能和可维护性。
让我们开始吧。
如前所述，扩展 PostgreSQL 会带来各种挑战，每项挑战都会以不同的方式影响系统的性能和效率。这些包括
大量数据摄取量：在应用程序分析或物联网监控等场景中很常见，这些场景通常涉及时间序列或 时间数据，PostgreSQL 通常面临大量新的数据条目。与标准业务数据不同，这些场景通常涉及为每次更新添加新行，而不是覆盖现有行。这会显着提高数据库的摄取率，要求您关注优化新数据如何集成到系统中。
昂贵的存储要求：大型且不断扩展的数据库需要高效的存储管理。这不仅涉及确保有足够的存储空间，还涉及管理数据的存储方式，以便优化访问并降低成本作为 数据生命周期管理 的一部分。
查询变慢：随着数据库规模的增加，查询的复杂性也可能增加。这会导致响应时间变慢，数据检索效率降低，从而影响整体系统性能。
分析延迟：随着数据库规模的扩展，监控和分析可能会变得更加复杂和缓慢。在更大规模的环境中，跟踪数据库性能和优化查询以用于分析目的可能会很困难 (Timescale 可以帮助您监控查询)。
如您所见，为了优化 PostgreSQL 数据库的性能，您必须仔细评估所有这些挑战。您需要解决当前问题并预测未来的扩展需求。这就是为什么最有效的扩展策略必须适应性强，能够管理系统上不断增加的需求，无论是通过优化数据摄取、管理复杂查询、高效存储，还是强大的分析。
在接下来的部分中，我们将深入探讨解决这些障碍的具体策略和工具，确保 PostgreSQL 系统在增长过程中保持高效和可扩展。
让我们看看 PostgreSQL 面临的每个扩展问题的解决方案
PostgreSQL 表现出令人印象深刻的基线摄取能力，大约每秒 10 万行。但是，对于所有用例来说，这个速率可能不够，尤其是那些涉及时间序列数据或高频数据更新的用例。确定这是否足以满足您的具体要求至关重要。
对于更高的摄取需求，像 Timescale 这样的平台，它可以协调多个摄取过程，变得至关重要。Timescale 基于 PostgreSQL 构建，建议每个摄取过程每秒处理 50-10 万行，并根据需要使用多个过程进行扩展。
资源分配：确保您的客户端和 Timescale（或 PostgreSQL）服务拥有足够的资源，特别是 CPU，以处理摄取量。在 Timescale，我们建议将 CPU 数量调整为至少与摄取过程的数量相同，例如，四个摄取过程等于四个 CPU 实例。
网络结构：为了提高摄取率，请通过将客户端和服务器放置在彼此附近，最好是在同一个云区域中，以最大限度地提高数据吞吐量来优化您的网络。
批量插入：此外，利用批量插入来提高效率。在每个 INSERT 语句中插入许多行，而不是逐行插入，可以显着提高您的摄取率。
INSERT INTO your_table (column1, column2)
VALUES
('value1a', 'value2a'),
('value1b', 'value2b'),
...;此 SQL 脚本演示了如何执行批量插入，这对于在 PostgreSQL 中高效地摄取数据至关重要。
我们在 13 个提高 PostgreSQL 插入性能的技巧 中更深入地探讨了这一建议，您还可以在 这里 获得更多关于优化摄取率的 Timescale 特定建议。
通过这些策略，PostgreSQL 可以有效地管理甚至提高其摄取量，确保数据摄取保持高效和可扩展。
随着 PostgreSQL 数据库的增长，管理存储成本成为扩展的关键方面。大型表，尤其是达到 TB 级大小的表，可能会产生大量的硬件和访问时间成本。
数据的性质：评估存储的数据类型，重点关注访问频率和数据段的大小。了解哪些数据被频繁访问，哪些数据很少使用，但仍然必要，至关重要。
数据增长：评估随着新的数据被摄取，数据段如何增长。此分析将告知您的存储扩展策略。
估计云存储成本：基于此评估，您现在可以尝试估计云存储的成本，例如 AWS RDS for PostgreSQL。您可以阅读我们的 RDS 成本估算，以节省您的工作。我们研究了从合适的存储类型（通用或预配）到数据传输成本、备份成本以及降低账单的策略，例如卸载数据，等各个方面。
分层存储：管理成本的一种更简单、更便宜的选择是使用像 Timescale 的 分层存储这样的解决方案，它是一种多层存储体系结构，允许您将较旧的、不常用的数据层到低成本存储层，同时仍然能够访问这些数据，并且不会影响您常用的数据的性能。
这种方法能够有效压缩很少使用的数据，并使成本降低几个数量级，数据每月 0.021 美元/GB 的固定价格——比 Amazon S3 更便宜。
随着 PostgreSQL 表格大小的增加，查询变慢是一个常见问题，会影响数据管道各个方面的性能，包括数据摄取、工程和分析。有效地管理大型表格需要特定的策略。
PostgreSQL 支持表格分区，这是一种将大型表格分割成更小、更易于管理的片段或分区的方法。这可以显著提高查询性能，尤其是在大多数访问的行集中在少数分区中的情况下。在这篇博文中，我们概述了一些情况 我们应该考虑 Postgres 分区。PostgreSQL 的分区可以通过多种方式完成
范围分区：根据关键列将表格划分为范围。
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);此脚本演示了如何根据 'logdate' 列创建具有范围分区的表格。
列表分区：通过显式列出每个分区的关键值来进行分区。
哈希分区：使用模数和余数进行分区，这对于均匀分布数据很有用。
子分区：允许进一步将分区划分为子分区，每个子分区都有其自己的索引和约束。
CREATE TABLE measurement_y2021m07 PARTITION OF measurement
FOR VALUES FROM ('2021-07-01') TO ('2021-08-01')
PARTITION BY RANGE (city_id);以下是如何为 'measurement' 表格创建子分区的示例。
但是，设置和管理分区结构的复杂性很大。关于分区数量和大小的决策以及随着分区结构扩展调整查询处理需要仔细规划和持续维护。
为了简化分区过程，TimescaleDB 引入了超表格的概念。 超表格自动生成和管理数据分区，使整个过程无缝且高效。它们特别针对基于时间的分区进行了优化，但也可以处理其他类型，例如主键。一个普通的 PostgreSQL 表格可以轻松地转换为超表格，这显著降低了分区管理中的复杂性和人工操作。
超表格提供了几个优势
自动分区：它们根据指定的间隔自动创建和管理分区，确保最佳数据分配，无需人工干预。
改进的查询性能：Timescale 的查询计划程序会智能地将查询路由到适当的分区，确保高效的数据访问。
列式压缩：超表格支持列式压缩，以实现更快的查询和更低的存储成本，使其成为处理大量数据的理想选择。
使用超表格，PostgreSQL 数据库可以在没有传统分区复杂性的情况下管理数百 TB 的数据，为大规模数据管理提供可扩展且高效的解决方案。
在处理 PostgreSQL 中的大型表格时，分析速度可能会显著减慢，影响仪表板和分析的效率。为了缓解这个问题，已经开发出不同的方法，例如物化视图和连续聚合。
物化视图本质上是数据的快照，可以通过提前存储复杂计算的结果来加快查询时间。虽然它们易于创建且灵活，但它们也有一些缺点
静态特性：物化视图是静态快照，需要使用 TRIGGER 或 CRON 类应用程序进行手动更新。
数据替换：通常，每个 PostgreSQL 物化视图的刷新都会替换所有历史数据，这可能会占用大量资源，并阻止旧的原始数据被删除以节省空间。
2019 年，TimescaleDB 引入了连续聚合来解决这些局限性，使海量时间序列数据的持续聚合更有效率
动态和自动更新：连续聚合会自动跟踪底层原始数据的变化，并使用用户定义的策略来保持物化数据的最新，无需人工干预。
存储效率：与以前版本相比，连续聚合所需的存储空间要少得多，直接转化为存储成本节省。
增强的灵活性和性能：它们允许使用任何聚合函数，克服了诸如无法使用 DISTINCT、FILTER 或 ORDER BY 等限制。自 TimescaleDB 2.7 以来，连续聚合更快、更轻量级，提供更佳的性能和更低的存储需求。
数据降采样和压缩：您可以在删除原始数据后保留物化数据，从而实现大型数据集的降采样。较旧的数据也可以被压缩，节省空间并提高查询性能。
分层连续聚合：为了使定义连续聚合的体验更有效，Timescale 在 TimescaleDB 2.9 中引入了分层连续聚合。熟悉连续聚合后，您可以开始在其他连续聚合的基础上创建它们。
通过利用连续聚合，开发人员可以有效地管理分析中的延迟， 确保其 PostgreSQL 驱动的应用程序保持高效，即使它们扩展到处理大量数据。
CREATE VIEW daily_temperature_avg
WITH (timescaledb.continuous) AS
SELECT city_id,
time_bucket('1 day', logdate) as bucket,
AVG(peaktemp) as avg_temp
FROM measurement
GROUP BY city_id, bucket;此 SQL 脚本演示了如何在 TimescaleDB 中创建连续聚合视图，简化数据聚合并提高大规模时间序列数据的查询效率。
到目前为止，Timescale 如何成为一个全面的解决方案来帮助您扩展 PostgreSQL 已经很明显了，它提供了一套旨在增强 PostgreSQL 数据库的可扩展性和性能的功能。
Timescale 不仅简化了扩展过程，还确保您的 PostgreSQL 数据库保持高效、经济高效，并且能够处理现代数据应用程序不断增长的需求。
因此，如果您正在寻找一种能够提供快速数据摄取、将不常用的数据分层到低成本存储层、自动分区和闪电般快速的数据汇总的解决方案， 立即免费试用 Timescale。