PostgreSQL 中数据库大小减小的策略：基于使用的方法

数据库定价模型很困难。作为寻找托管数据库的开发人员，搜索过程中最烦人（但却至关重要）的方面之一不仅涉及评估适合您的数据库大小的解决方案的前期价格，还涉及定价的运作方式以及扩展的效果。

除了评估数据库定价时的细微差别（例如，“随着我的数据增长，账单会增加多少？”、“我是否要按每次写入或读取付费？”以及“我是否需要为每次备份支付更多费用？” ），开发人员往往会忽略一个方面：数据库定价模型的构造方式将影响您管理数据和评估 Postgres 数据库大小的方式。

不同的定价模型需要不同的方法来运行 PostgreSQL。例如，如果您被锁定在一个大磁盘上，您可能不会优先考虑减少数据库大小。如果您按数据读取付费，您可能会在运行某些查询时三思而后行；如果您按数据进入付费，您可能会对新摄取数据的频率和数量持谨慎态度。

每个定价元素都会微妙地影响您最终采用的策略和行为，推动您采用特定的方式来管理数据库并与数据库交互，以确保成本效益和最佳性能。

在 Timescale，我们几个月前过渡到基于使用的存储模型，并获得了良好的客户反馈。在这篇博文中，我们将探讨自过渡到此模型以来我们从客户那里观察到的运营优势，以及如何在基于使用的模型中优化管理 PostgreSQL 存储的策略。

基于使用情况的存储模型在数据库领域变得越来越流行——谁愿意为不使用的磁盘空间付费？尽管如此，基于使用的模型并非没有后果，您需要考虑一些最佳实践来有效地驾驭它。

PostgreSQL 存储模型快速回顾

为了为我们关于在基于使用的模型中管理数据库大小的讨论奠定一个共同点，让我们首先快速介绍定价在我们的 PostgreSQL 平台中的工作原理（时间尺度）以及它与其他托管 PostgreSQL 产品（例如 Amazon RDS for PostgreSQL 和 Amazon Aurora）的比较。

从昨天（💥）开始，我们在 Timescale 平台中提供两种类型的数据库服务：

• 动态 PostgreSQL 服务，开发人员可以使用动态计算创建 PostgreSQL 数据库，从而在不牺牲性能的情况下节省资金。

  
  

• 时间序列服务，开发人员可以创建 PostgreSQL 数据库，通过超表、列式压缩、连续聚集体，以及分层存储。

  
  

让我们重点关注如何在我们的平台上定价存储。这两种服务都带有基于使用的存储模型，这意味着：

• 开发人员根据他们使用的容量付费，没有小字——没有最小数据库大小，没有最小扩展步骤。如果到月底您已使用 128 GB，则您将需要支付该费用。您可以从 1 GB 开始，逐渐增长到 TB。

• 不会根据写入的数据、数据传输或运行的查询收取任何费用。

• 创建数据库或扩展时无需分配存储。

• 无需手动缩小磁盘大小。

  
  

为了让大家明白这一点，我们来列出该模型、Amazon RDS PostgreSQL 和 Amazon Aurora 之间的区别：

总而言之，以下是我们比较的三个主要结论：

• Timescale 和 Aurora 均按您的实际数据库大小收费。相比之下，RDS PostgreSQL 对预置存储收费。您无法缩小 RDS 中的卷大小。

  
  

• Timescale 不对数据写入、数据传输或查询操作收取费用。 RDS 和 Aurora 均按数据传输和额外的备份存储收费，并且您可能会产生一些额外的 I/O 费用，具体取决于您选择的存储类型。

  
  

• Timescale 没有最小缩放步骤，而 Aurora 在从 10 GB 开始后以 10 GB 增量进行缩放。

  
  

正如你所看到的，Timescale 的模型更接近于Aurora I/O 优化不同的是，Timescale 缺乏扩展步骤，也没有数据传输等额外费用。相比之下，RDS 很好地表示了基于分配的模型，即使RDS缺乏“存储层”传统上在使用此模型的数据库供应商中发现。

基于使用的定价如何改进 PostgreSQL 存储管理

正如我们之前介绍的，不同的定价模型意味着不同的数据库体验。当我们从基于分配的模型转变为基于使用的模型时，我们的客户告诉我们，他们看到三个运营领域立即得到改进：

• 他们在启动数据库时不需要预先配置存储，从而减少了过度配置错误，从而降低了存储费用。
• 他们在扩展时不必考虑存储问题。
• 他们可以缩小规模——例如，如果他们删除了数据，他们就不再为其付费。

基于使用的模型消除了存储过度配置的问题

在传统的基于分配的模型中，开发人员经常发现自己需要预测其存储需求，这通常会导致存储过度配置。当 Timescale 在基于使用的模型上运行时，我们在整个设备组中观察到了这一点：我们的大多数客户没有使用其全部磁盘容量，这意味着他们实际上是在为尚未使用的存储空间付费。基于使用的模型消除了这种猜测游戏（以及错误猜测的后果）。

扩展时无需考虑存储问题

“Timescale 基于使用情况的存储意味着我不必再考虑磁盘大小。我们的存储成本下降了 30%，而且我不需要做任何事情！”

亚当·麦克雷,柔道等级（时间刻度客户）

在基于使用的模型中，存储会随着数据库的增长而无缝扩展。传统基于分配的模型的主要压力来源是磁盘空间耗尽的危险，这可能会导致许多问题，从应用程序停机、事务丢失到最坏情况下的数据损坏。

通过基于使用的模型，开发人员不再需要警惕地监控其存储以避免撞上存储墙。同时，他们不必担心繁重的自动缩放步骤或层级跳跃。

您可以缩减规模（停止为删除的数据付费）

最后但并非最不重要的一点是，基于使用的模型允许您“缩小规模”。如果您删除数据（或设法大幅减少数据库大小），您开始为每个存储支付更少的费用，这听起来很公平。正如我们将在下一节中看到的，Timescale 有一些功能可以帮助客户减少 PostgreSQL 数据库的大小。采用基于使用情况的模型使我们的客户能够在减少磁盘使用量时立即实现节省，从而激励他们保持数据库精简。

如何有效地驾驭基于使用的模型：减少 Postgres 数据库大小的技巧

我们刚才提到的好处显着改善了开发人员使用存储的体验，这就是基于使用的模型变得非常流行的原因。但基于使用情况的定价带来了一个明显（但也深远）的后果：它迫使开发人员采用良好的数据实践来尽可能减少 PostgreSQL 数据库的大小。

当您知道存储成本与实际使用的磁盘大小直接相关时，就会有新的动机更加明智地处理数据存储。如果您采用基于使用的存储模型，那么您有责任确保有效地存储数据。

在某种程度上，这可以被认为是基于使用的模型的“缺点”，并且需要一些工作——但这实际上是因祸得福。在传统的基于分配的模型中，数据卫生可能会在某种程度上被忽视，因为成本是固定的：如果您被锁定在 RDS 中的 500 GB 磁盘中，而您的数据库是 200 GB，那么您似乎没有太大的动力去提高存储使用效率。

但事情是这样的：良好的数据实践不仅仅是为了省钱。要扩展 PostgreSQL 数据库，必须保持其优化。通过采用良好的 PostgreSQL 数据管理实践，您不仅会看到更好的性能，而且您作为数据库管理员的生活也会变得更加轻松。

考虑到这一点，让我们进行一些实践，这些实践将帮助您尽可能有效地导航基于使用的存储模型，从而以系统的方式减小 PostgreSQL 数据库的大小。在 Timescale 的特殊情况下，我们有一些特别有用的功能，我们也会介绍这些功能。

减少 PostgreSQL 数据库的膨胀

在基于使用的模型中，第一个必须做的事情是注意 PostgreSQL 存储如何工作的细节，即必须定期减少膨胀。这不仅可以帮助您确定数据库大小，还可以帮助您确定 I/O 要求。我们将在本节中向您介绍一些基础知识，但是这个 HN 线程有一些很好的建议，我们还写了关于 PostgreSQL 中表膨胀的博客文章。

• 监视死元组。优化 PostgreSQL 存储的主动方法首先是密切关注死元组。如果不加以控制，死元组可能会累积并导致不必要的存储开销。 pgstattuple()扩展可以帮助您理解表如何管理这些元组。

  
  

• 经常吸尘。为了有效地解决表膨胀问题，您可能需要配置 autovacuum 以更频繁地运行。与在 postgresql.conf 中全局调整 autovacuum 设置相比，在每个表的基础上微调这些设置更为精确。这迎合了这样一个事实：不同的表可能有不同的积累死元组的倾向。监控可能阻碍自动清理操作的长时间运行的事务也很重要。

  
  

• 回收未使用的页面。虽然自动真空和真空处理死元组，但回收未使用的页面需要不同的方法。尽管 VACUUM FULL 可用于此目的，但它具有锁定整个表的固有限制。 pg_repack可以帮助你。

做一些 PostgreSQL 微调

系统地减小 PostgreSQL 数据库大小与有效扩展 PostgreSQL 数据库的能力密切相关，即在添加越来越多的数据时保持快速和敏捷。关注（或许调整）一些关键的 PostgreSQL 参数会有所帮助。 本文回顾了最重要的性能参数。以下是您需要考虑的一些方面：

• shared_buffers ：控制用于 PostgreSQL 页面缓存的内存，通常设置为系统总 RAM 的 25%。

  
  

• work_mem ：它设置查询中每个操作分配的内存。在 Timescale 中，建议的设置为(25 % of RAM) / max_connections 。

  
  

• max_connections ：它设置数据库允许的最大并发连接数。连接池程序可以帮助管理许多短期连接。

  
  

• max_worker_processes ：它决定 PostgreSQL 可以启动的最大工作进程数。如果使用 Timescale，设置此参数的公式为： max_worker_processes = 3 + timescaledb.max_background_workers + max_parallel_workers 。

  
  

• max_parallel_workers ：它指定支持并行查询的最大工作线程数。默认设置为等于可用 CPU 的数量。

  
  

• autovacuum_max_workers ：它确定并发 autovacuum 进程的最大数量。在 Timescale 中，默认设置为 10。

实行索引卫生

定期优化索引将有助于保持 PostgreSQL 的效率，尤其是在数据库扩展时。虽然明智地使用索引可以帮助您提高查询性能，但过多的索引可能会在大型 PostgreSQL 部署中产生问题。

过度索引最明显的后果是增加存储消耗，因为每个索引都需要单独的存储，而存储随着表的大小成比例增长。当表被分区时，这种开销会变得更加重要，就像在 Timescale 的超级表中一样。

不必要的索引还可能在改善写入操作方面产生反作用，因为每个新的数据条目或修改都意味着并发索引更新，从而导致更多的 I/O 操作和潜在的表膨胀。监控索引将帮助您确定哪些索引不再使用，从而保持精简。

一种方法是使用pg_stat_user_indexes ：

 -- Retrieve indexes that might not be used or are infrequently used SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan AS times_used FROM pg_stat_user_indexes WHERE idx_scan = 0 OR idx_scan < some_low_threshold -- replace 'some_low_threshold' with a value that makes sense for your context ORDER BY idx_scan ASC, schemaname, relname;

如果索引在 idx_scan 列中的值为 0，则意味着自上次重置统计信息以来尚未使用该索引，这意味着它可能是优化的候选索引。通过为idx_scan设置较低的阈值，您还可以识别不常用的索引。

通过时间尺度压缩缩小大型表

Timescale 的突出功能之一是其对列式压缩的本机支持，这可以大大减少大型表使用的磁盘空间，而不会影响查询性能。压缩可以提高许多查询的性能。

Timescale 的压缩原理是将常规的基于行的数据转换为更紧凑的列格式。此过程对于时间序列数据特别有效，其中许多列可能包含重复值；通过根据每种数据类型应用不同的压缩算法，我们可以实现令人印象深刻的压缩率（+90%）。这意味着您的大型表最多可以缩小 10 倍。

在 Timescale 中，通过基于时间的压缩策略逐个表启用压缩。例如，此代码启用特定超表中的压缩并自动压缩两周以上的数据：

 -- Enable compression on the hypertable ALTER TABLE your_hypertable SET ( timescaledb.compress, timescaledb.compress_segmentby = 'some_column' ); -- Set a compression policy to compress data older than two weeks SELECT add_compression_policy('your_hypertable', INTERVAL '2 weeks');

要了解有关压缩的更多信息， 查看我们的文档。

制定数据管理策略：将旧数据移至更便宜的存储，删除不再需要的数据

前面的策略对于减小数据库大小非常有帮助，但是您可以在 Timescale 中利用另一种途径来有效地扩展存储：分层存储。

通过创建简单的分层策略，您可以将较旧的、访问较少的数据移动到无底对象存储层：

 -- This policy moves all data older than one month to object storage SELECT add_tiering_policy('example', INTERVAL '1 month);

该对象存储具有以下特点：

• 它的价格比我们的高性能存储更低，让您能够以更少的成本存储大量 TB 的数据。
• 它是无限的，这意味着您可以存储任意数量的数据。
  

这种分层存储架构在 Timescale 的后端中根深蒂固。对象存储不是与数据库分离的“桶”，而是数据库的一个组成部分。查询将透明地访问两个存储层，无需您执行任何操作 - 您只需继续在同一架构上使用标准 SQL 即可。一旦设置了分层策略，就没有其他需要考虑的了！

我们建议在应用程序不频繁访问数据时将数据移动到低成本存储层，因为存在性能成本（对象存储不如我们的常规存储快）。但您可以继续轻松地对该数据运行即席查询（例如，对于客户的用户体验并不重要且不需要顶级性能的查询）。

除了提供这种低成本存储层之外，Timescale 还可以轻松设置数据保留策略以删除不再需要的数据：

 -- Set a data retention policy to drop data older than 10 months SELECT add_retention_policy('your_hypertable', INTERVAL '10 months');

您可以将上述数据保留策略与连续聚合相结合，以有效地对数据集进行下采样，例如，将粒度从一秒减少到一分钟，删除一秒值但保留一分钟聚合。 阅读这篇博文，了解如何执行此操作并主动管理长期数据的示例。

基于使用的模型和数据管理范式

虽然基于使用的模型表面上看起来只不过是定价变化，但它们带来了开发人员如何考虑数据库大小以及如何感知和处理数据的范式转变。

基于使用的模型促进了持续改进的文化，其中重点从单纯的存储管理转向数据库的健康和效率。一开始这需要一些纪律，但是一旦您的思维方式转变并且学习了一些技术，您将处于高效且有效地扩展 PostgreSQL 数据库的最佳位置。

Timescale 拥有宝贵的工具可帮助您系统地减小 PostgreSQL 数据库大小，例如压缩、分层存储和数据保留策略。这使您可以在基于使用的模型中有效地扩展 PostgreSQL 数据库。要亲自体验，请注册 Timescale - 您可以在前 30 天内免费使用它。

-卡洛塔·索塔撰写。