在大型 PostgreSQL 数据库中实现列压缩的策略

扩展Postgres数据库是不断增长的应用程序的必经之路。当您看到表扩展为数百万甚至数十亿行时，曾经敏捷的查询开始滞后，并且不断增加的基础设施成本开始给您的利润蒙上长长的阴影。您陷入了一个难题：您不想放弃您心爱的 PostgreSQL，但您似乎需要一种更有效的方法来处理不断增长的数据集。

在本文中，我们将向您讲述如何为 PostgreSQL 构建灵活、高性能的列式压缩机制以提高其可扩展性。通过将列式存储与专门的压缩算法相结合，我们能够实现任何其他关系数据库都无法比拟的令人印象深刻的压缩率（+95%）。

通过压缩数据集，您可以进一步扩展 PostgreSQL 数据库。正如我们将在本文中看到的，这种高效的压缩设计可让您将大型 PostgreSQL 表的大小减少最多 10-20 倍。您可以在较小的磁盘上存储更多数据（也称为省钱），同时提高查询性能。时间尺度压缩也是完全可变的，使数据库管理和操作变得简单：您可以在压缩表中添加、更改和删除列，并且可以直接插入、更新和删除数据。

欢迎使用更具可扩展性的 PostgreSQL！

内容概述

• 为什么 PostgreSQL 需要数据库压缩
• 但是吐司呢？
• 为什么压缩不是 PostgreSQL 原生的？行数据库与列数据库简介
• 行数据库与列数据库：该选择什么？
• 在面向行的数据库上构建列式存储
• 将 PostgreSQL 转变为混合行列存储
• 幕后：从行数据到压缩柱状数组
• 高效查询压缩数据
• 按segmentby列对常用查询数据进行分组
• 按segmentby定义分段
• 通过orderby进行高级微调
• 时间尺度压缩的演变
• 最终结果：大型 PostgreSQL 数据库的查询速度更快，存储占用空间更少
• 压缩和分层存储：时间尺度的存储生命周期
• 继续使用 PostgreSQL

为什么 PostgreSQL 需要数据库压缩

但在详细介绍如何构建压缩之前，让我们花几分钟回答这个问题：为什么有必要向 PostgreSQL 添加新的数据库压缩机制？

让我们首先了解现代应用程序的需求和一些软件历史。

我们喜欢 Postgres：我们相信它是构建应用程序的最佳基础，因为它的可靠性、灵活性和丰富的生态系统的结合是任何其他数据库都难以比拟的。但 Postgres 诞生于几十年前——这种稳健性并非没有缺点。

如今，开发人员使用 PostgreSQL 的用途远远超出其最著名的传统 OLTP（在线事务处理）用例。许多数据密集型、高要求的应用程序（24/7 运行并处理不断增长的数据量）均由 PostgreSQL 提供支持：

• PostgreSQL 数据库用于获取来自交通管理系统、公用设施网络和公共安全监视器的大量传感器数据流。

  
  

• 能源公司正在使用 PostgreSQL 来存储和分析来自智能电网和可再生能源的指标。

  
  

• 在金融领域，PostgreSQL 是实时跟踪市场变动数据的系统的核心。

• 电子商务平台正在使用 PostgreSQL 来跟踪和分析用户交互生成的事件。

  
  

• Postgres 甚至被用作矢量数据库来为新一波的人工智能应用提供动力。

  
  

因此，Postgres 表增长得非常快，表达到数十亿行已成为生产中的新常态。

不幸的是，PostgreSQL 本身就无法处理如此大的数据量：查询性能开始滞后，数据库管理变得痛苦。为了解决这些限制，我们构建了时标数据库，一个扩展，通过自动分区、连续聚合、查询规划器改进以及更多功能来扩展 PostgreSQL 的性能以满足要求苛刻的应用程序。

为 PostgreSQL 构建高性能压缩机制也是一个同样重要的解锁。这些不断增长的数据集不仅对良好的性能构成挑战，而且数据的积累会导致磁盘变得越来越大，存储费用也越来越高。 PostgreSQL 需要一个解决方案。

但是吐司呢？

但是 PostgreSQL 现有的 TOAST 方法呢？尽管它的名字很神奇🍞😋， TOAST 无法有效地系统地减小大型 PostgreSQL 数据库的大小。

TOAST 是 PostgreSQL 用于存储和管理不适合单个数据库页面的大值的自动机制。虽然 TOAST 将压缩作为实现这一目标的技术之一，但 TOAST 的主要作用并不是全面优化存储空间。

例如，如果您有一个由小元组组成的 1 TB 数据库，无论您尝试进行多少微调，TOAST 都无法帮助您系统地将 1 TB 转换为 80 GB。当超大属性超过 2 KB 的阈值时，TOAST 将自动压缩连续的超大属性，但 TOAST 对于较小的值（元组）没有帮助，您也无法应用更高级的用户可配置配置，例如压缩所有超过一个月的数据在特定的表中。 TOAST 的压缩严格基于各个列值的大小，而不是更广泛的表或数据集特征。

TOAST 还会带来显着的 I/O 开销，特别是对于具有频繁访问的过大列的大型表。在这种情况下，PostgreSQL 需要从 TOAST 表中检索外线数据，这是与访问主表不同的 I/O 操作，因为 PostgreSQL 必须遵循从主表到 TOAST 表的指针来读取完整的数据。这通常会导致性能更差。

最后，TOAST 的压缩并不是为了提供特别高的压缩比而设计的，因为它对所有数据类型使用一种标准算法。

为什么压缩不是 PostgreSQL 原生的？行数据库与列数据库简介

对 TOAST 的快速提及也有助于我们理解 PostgreSQL 在有效压缩数据方面的局限性。正如我们刚才所看到的，TOAST 的压缩逐行处理数据，但这种面向行的架构分散了压缩算法赖以发展的同质性，从而导致压缩的可操作性受到根本限制。这是关系数据库（如本机 Postgres）在存储优化方面经常达不到要求的根本原因。

让我们来分解一下。传统上，数据库分为两类之一：

• 面向行的数据库按行组织数据，每行包含特定记录的所有数据。它们针对频繁插入、更新和删除记录的事务处理进行了优化，并且对于操作涉及单个记录或小数据子集（例如，检索有关特定客户的所有信息）的 OLTP 系统非常有效。

  
  

• 另一方面，面向列（又名“列”）数据库按列组织数据。每列存储多个记录中特定属性的所有数据。它们通常针对 OLAP 系统（在线分析处理）进行优化，其中查询通常涉及跨许多记录的聚合和操作。

  
  

让我们用一个例子来说明这一点。假设我们有一个包含四列的users表： user_id 、 name 、 logins和last_login 。如果该表存储一百万个用户的数据，则它将有效地具有一百万行和四列，在磁盘上连续物理存储每个用户的数据（即每一行）。

在这种面向行的设置中，user_id = 500,000 的整行被连续存储，从而加快检索速度。因此，浅层和宽层查询在行存储上会更快（例如，“获取用户 X 的所有数据”）：

 -- Create table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100), logins INT DEFAULT 0, last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Assume we have inserted 1M user records into the 'users' table -- Shallow-and-wide query example (faster in row store) SELECT * FROM users WHERE user_id = 500000;

相比之下，列式存储将所有user_id存储在一起，所有名称存储在一起，所有登录值存储在一起，等等，以便每列的数据连续存储在磁盘上。这种数据库架构有利于深而窄的查询，例如“计算所有用户的平均登录次数”：

 -- Deep-and-narrow query example (faster in column store) SELECT AVG(logins) FROM users;

列式存储尤其适合对宽数据进行窄查询。在列式数据库中，只需读取logins列数据即可计算平均值，而无需从磁盘加载每个用户的整个数据集。

正如您现在可能已经猜到的，将数据存储在行与列中也会影响数据的压缩程度。在列式数据库中，各个数据列通常是相同类型，并且通常来自更有限的域或范围。

因此，列式存储通常比行式数据库压缩得更好。例如，我们之前的logins列都是整数类型，并且可能只包含一小部分数值（因此熵较低，压缩效果很好）。将其与面向行的格式进行比较，在面向行的格式中，一整行数据包含许多不同的数据类型和范围。

但即使它们在 OLAP 式查询和可压缩性方面显示出优势，列式存储也并非没有权衡：

• 检索单个行的查询性能要低得多（有时甚至无法运行）。

  
  

• 他们的架构不太适合传统的 ACID 事务。

  
  

• 通常不可能在柱状存储中进行更新。

  
  

• 基于行的存储更容易利用指数（例如B树）来快速找到合适的记录。

  
  

• 使用行存储，可以更轻松地标准化数据集，以便您可以更有效地将相关数据集存储在其他表中。

行数据库与列数据库：该选择什么？

那么，哪个更好：行式还是柱式？

传统上，您会根据您的工作负载评估两者之间的权衡。如果您正在运行典型的 OLTP 用例，您可能会选择面向行的关系数据库，例如 PostgreSQL；如果您的用例显然是 OLAP，您可能会倾向于使用ClickHouse 等列式存储。

但如果您的工作量实际上是两者的混合呢？

• 您的应用程序查询通常可能是浅而宽的，单个查询访问许多数据列以及跨许多不同设备/服务器/项目的数据。例如，您可能正在支持面向用户的可视化，该可视化需要显示特定制造工厂中所有传感器的最后记录的温度和湿度。此类查询需要访问与构建条件匹配的所有行中的多个列，可能跨越数千或数百万条记录。

  
  

• 但您的某些查询也可能是深而窄的，单个查询在较长时间内为特定传感器选择较少数量的列。例如，您可能还需要分析特定设备过去一个月的温度趋势以检查异常情况。这种类型的查询将集中于单个列（温度），但需要从与目标时间段内的每个时间间隔相对应的大量行中检索此信息。

  
  

• 您的应用程序也可能是数据密集型且插入（追加）繁重的。正如我们之前讨论的，每秒处理数十万次写入已成为新常态。您的数据集可能也非常精细，例如，您可能每秒都在收集数据。继续前面的示例，您的数据库需要同时提供这些大量写入和持续读取服务，以实时支持面向用户的可视化。

  
  

• 您的数据是append-mostly ，但不一定是append-only 。您可能需要偶尔更新旧记录或可能记录迟到或无序的数据。

  
  

这个工作负载既不是传统意义上的OLTP，也不是OLAP。相反，它包含两者的元素。那么该怎么办？

混合动力！

在面向行的数据库上构建列式存储

为了服务像前面的示例这样的工作负载，单个数据库必须包含以下内容：

• 能够轻松维持每秒数十万次写入的高插入率

  
  

• 支持插入迟到或无序的数据，以及修改现有数据

  
  

• 足够的灵活性，可以有效处理大型数据集中的浅而宽和深而窄的查询

  
  

• 压缩机制能够显着减小数据库大小以提高存储效率

  
  

这就是我们在向 TimescaleDB（进而向 PostgreSQL）添加列压缩时要实现的目标。

将 PostgreSQL 转变为混合行列存储

正如我们在上一节中提到的，我们构建了 TimescaleDB 来扩展 PostgreSQL，使其具有更高的性能和可扩展性，使其适合要求苛刻的工作负载，例如时间序列数据。 TimescaleDB 是作为 PostgreSQL 扩展实现的：在此过程中，它继承了 PostgreSQL 的所有优点，例如完整的 SQL、巨大的查询和数据模型灵活性、久经考验的可靠性、热情的开发人员和用户群以及最大的数据库生态系统之一大约。

理论上，这意味着 TimescaleDB 也被锁定为 PostgreSQL 的面向行的存储格式，并具有适度的可压缩性。事实上，没有什么是一点工程技术解决不了的。

两个观察结果。第一的， 大多数大型 PostgreSQL 工作负载都具有类似时间序列的结构，也就是说，它们是重追加的（相对于重更新的），具有松散顺序的主键，例如时间戳或串行事件 ID。其次，此类数据集定期通过扫描或汇总进行查询，而不仅仅是点查询。有了这些观察结果，我们为 TimescaleDB 构建了一种新颖的列式存储功能（我们将在下一节中详细介绍），它使我们能够实现无与伦比的可压缩性。

事实上，这种行到列的转换不需要应用于整个数据库。作为 Timescale 用户，您可以将 PostgreSQL 表转换为混合行列存储，准确选择要以列形式压缩的数据通过我们简单的 API并根据您的应用程序需要从两种存储架构中受益。

让我们用一个例子来说明它是如何实际工作的。想象一个温度监控系统每秒从多个设备收集读数，存储时间戳、设备 ID、状态代码和温度等数据。

为了有效地访问最新的温度数据，特别是对于您可能想要分析来自不同设备的最新读数的操作查询，您可以将最新的数据（例如，上周）保留在传统的未压缩、面向行的 PostgreSQL 结构中。这支持高摄取率，并且对于最近数据的点查询也非常有用：

 -- Find the most recent data from a specific device SELECT * FROM temperature_data WHERE device_id = 'A' ORDER BY timestamp DESC LIMIT 1; -- Find all devices in the past hour that are above a temperature threshold SELECT DISTINCT device_id, MAX(temperature) FROM temperature WHERE timestamp > NOW() - INTERVAL '1 hour'   AND temperature > 40.0;

但是，一旦这些数据已经存在几天，像前一个这样的浅而宽的查询就不会再频繁运行：相反，深而窄的分析查询会更常见。因此，为了提高此类查询的存储效率和查询性能，您可以自动选择将所有超过一周的数据转换为高度压缩的列格式。要在 Timescale 中执行此操作，您需要定义如下压缩策略：

 -- Add a compression policy to compress temperature data older than 1 week SELECT add_compression_policy('temperature_data', INTERVAL '7 days');

数据压缩后，对温度数据（无论是在特定设备上还是跨多个设备）运行深窄分析查询将显示最佳查询性能。

 -- Find daily max temperature for a specific device across past year SELECT time_bucket('1 day', timestamp) AS day, MAX(temperature) FROM temperature_data WHERE timestamp > NOW() - INTERVAL '1 year'   AND device_id = 'A' ORDER BY day; -- Find monthly average temperatures across all devices SELECT device_id, time_bucket('1 month', timestamp) AS month, AVG(temperature) FROM temperature_data WHERE timestamp < NOW() - INTERVAL '2 weeks' GROUP BY device_id, month ORDER BY month;

我们如何表示从行格式到列格式的“转变”？ Timescale 的超级表用于根据分区键（例如时间戳或其他序列 ID 列）将数据分区为“块”。然后，每个块存储与该分区键的特定范围的时间戳或其他值相对应的记录。在上面的示例中，温度数据将按周分区，以便最新的块保持行格式，而所有较旧的周都转换为列格式。

这种混合行列存储引擎是一个非常强大的工具，可以优化大型 PostgreSQL 数据库中的查询性能，同时显着减少存储占用空间。正如我们将在本文后面看到的，通过将数据转换为列格式并应用专门的压缩机制，我们不仅能够加快分析查询的速度，而且还可以实现高达 98% 的压缩率。想象一下这对您的存储费用有何影响！

幕后：从行数据到压缩柱状数组

在深入了解查询性能和存储节省的详细信息之前，我们首先介绍一下该机制的幕后工作原理：如何实际执行从行到列的转换以及如何将压缩应用于列式数据。

当压缩策略生效时，它本质上将原始 PostgreSQL 超表中传统上的大量单独记录（想象 1,000 个密集的行）转换为单一的、更紧凑的行结构。在这种压缩形式中，每个属性或列不再存储每行的单个条目。相反，它封装了这 1,000 行中所有相应值的连续有序序列。我们将这 1,000 行称为一个批次。

为了说明这一点，让我们想象一个这样的表：

 | Timestamp | Device ID | Status Code | Temperature | |-----------|-----------|-------------|-------------| | 12:00:01 | A | 0 | 70.11 | | 12:00:01 | B | 0 | 69.70 | | 12:00:02 | A | 0 | 70.12 | | 12:00:02 | B | 0 | 69.69 | | 12:00:03 | A | 0 | 70.14 | | 12:00:03 | B | 4 | 69.70 |

为了准备这些数据进行压缩，Timescale 首先会将此表格数据转换为列式存储。给定一批数据（约 1,000 行），每列的数据都会聚合到一个数组中，每个数组元素对应于原始行之一的值。该过程产生一行，每列存储该批次中的一组值。

 | Timestamp | Device ID | Status Code | Temperature | |------------------------------|--------------------|--------------------|-------------------------------| | [12:00:01, 12:00:01, 12...] | [A, B, A, B, A, B] | [0, 0, 0, 0, 0, 4] | [70.11, 69.70, 70.12, 69....] |

即使在应用压缩算法之前，这种格式也可以通过大大减少 Timescale 的内部每行开销来立即节省存储空间。 PostgreSQL 通常每行增加约 27 字节的开销（例如，对于多版本并发控制或 MVCC）。因此，即使没有任何压缩，如果我们上面的模式是 32 字节，那么之前需要[1,000 * (32 + 27)] ~= 59 KB 的批次中的 1,000 行数据现在需要[1,000 * 32 + 27 ] ~= 此格式为 32 KB 。

[旁白：这种将较大的表“分组”为较小的批次，然后连续存储每个批次的列（而不是整个表的列）的概念实际上与 Apache Parquet 文件格式中的“行组”类似。尽管我们只是事后才意识到这种相似性！]

但这种转换的一大优点是，现在，给定一种连续存储相似数据（时间戳、设备 IDS、温度读数等）的格式，我们可以对其采用特定于类型的压缩算法，以便单独压缩每个数组。这就是 Timescale 实现令人印象深刻的压缩率的方式。

Timescale 自动采用以下压缩算法。所有这些算法都是“无损的，”因此我们不会通过压缩而放弃精度或引入不准确度；任何产生的解压都会完美地重建原始值。

• 大猩猩压缩用于浮标

  
  

• Delta-of-Delta +简单-8b和游程编码时间戳和其他类似整数类型的压缩

  
  

• 对具有一些重复值的列进行整行字典压缩（+ 顶部 LZ 压缩）

  
  

• 适用于所有其他类型的基于 LZ 的数组压缩

  
  

我们扩展了 Gorilla 和 Simple-8b 以按相反顺序处理解压缩数据，从而使我们能够加快使用向后扫描的查询速度。

我们发现这种特定于类型的压缩非常强大：除了更高的压缩率之外，一些技术（例如 Gorilla 和 delta-of-delta）在解码过程中比基于 LZ 的压缩快 40 倍，从而大大提高了查询性能。

解压缩数据时，Timescale 可以对这些单独的压缩批次进行操作，逐批解压缩它们，并且仅对请求的列进行解压缩。因此，如果查询引擎可以确定仅需要从最初包含 100 万行数据的表块中处理 20 个批次（对应于 20,000 条原始数据行），那么查询可以执行得更快，因为它正在读取和解压缩数据少很多。让我们看看它是如何做到的。

高效查询压缩数据

以前基于数组的格式提出了一个挑战：即数据库应获取并解压缩哪些行来解决查询？

让我们再次以温度数据为例。几种自然类型的查询不断出现：按时间范围选择和排序数据，或根据设备 ID 选择数据（在 WHERE 子句中或通过 GROUP BY）。我们如何有效地支持此类查询？

现在，如果我们需要最后一天的数据，查询必须浏览时间戳数据，该数据现在是压缩数组的一部分。那么数据库是否应该解压缩整个块（甚至整个超表）以找到最近一天的数据？

或者，即使我们可以识别分组到压缩数组中的各个“批次”（如上所述），来自不同设备的数据是否散布在一起，因此我们需要解压缩整个数组以查找它是否包含有关特定设备的数据？虽然这种更简单的方法仍然可以产生良好的压缩性，但从查询性能的角度来看，它的效率并不高。

为了解决以列格式高效定位和解压缩特定查询数据的挑战， Timescale 引入了“分段依据”和“排序依据”列的概念。

按segmentby列对常用查询数据进行分组

回想一下，Timescale 中的数据最初是逐块转换为压缩柱状形式的。为了提高基于特定列过滤的查询的效率（例如，经常按device_id查询），您可以选择将此特定列定义为“ 压缩分段“ 柱子。这种方法对于组织压缩数据非常有益。

这些segmentby列用于对每个压缩块内的数据进行逻辑分区。压缩引擎首先将具有相同segmentby键的所有值分组在一起，而不是如上所示构建任意值的压缩数组。

因此，关于 device_id A 的 1,000 行数据在存储在单个压缩行中之前会被密集备份，关于 device_id B 的 1,000 行数据，依此类推。因此，如果选择device_id作为segmentby列，则每个压缩行都包含有关特定设备 ID 的压缩列式数据批次，这些数据未压缩地存储在该行中。 Timescale 还在压缩块内的这些分段值上构建了索引。

 | Device ID | Timestamp | Status Code | Temperature | |-----------|--------------------------------|-------------|-----------------------| | A | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 0] | [70.11, 70.12, 70.14] | | B | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 4] | [69.70, 69.69, 69.70] |

这种数据的连续存储极大地提高了通过segmentby列过滤的查询效率。当运行按device_id过滤的查询（其中device_id是segmentby列）时，Timescale 可以快速选择（通过索引）块中具有指定设备 ID 的所有压缩行，并且它可以快速跳过数据（并避免解压缩） ）与请求的设备无关的数据。

例如，在此查询中，Timescale 将仅有效地定位和处理那些包含 device_id A 数据的压缩行：

 SELECT AVG(temperature) FROM sensor_data WHERE device_id = 'A'   AND time >= '2023-01-01'   AND time < '2023-02-01';

此外，Timescale 超表存储与每个块关联的元数据，指定块覆盖的值的范围。因此，如果超表按周进行时间戳分区，那么当查询规划器运行上述查询时，它知道只处理涵盖 1 月份的 4-5 个块，从而进一步提高查询性能。

按segmentby定义分段

首次启用超表压缩时，您可以指定用于分段的列。选择使用哪个列应基于查询中经常使用的列。事实上，您可以使用多个列进行分段：例如，您可以（比如说）将具有相同tenant_id 和device_id 的批次分组在一起，而不是按device_id 将批次分组在一起。

不过，请注意不要过度选择：定义太多的分段列会降低压缩效率，因为每个额外的分段列都会有效地将数据分成越来越小的批次。

如果您无法再创建 1,000 个记录批次的数据，而是在特定块内只有 5 个具有指定的分段键的记录，那么它根本无法很好地压缩！

但是，一旦确定了要分段的列，在超表中启用压缩时就可以轻松配置它们：

 ALTER TABLE temperature_data SET (   timescaledb.compress,   timescaledb.compress_segmentby = 'device_id' );

通过orderby进行高级微调

TimescaleDB 通过每个块内的战略数据排序（由compress_orderby参数指定）来增强压缩数据的查询性能。虽然按时间戳排序的默认设置（时间序列数据中的典型分区键）适用于大多数场景，但了解这种优化可能很有价值。请继续阅读以获得更深入的技术视角。

再次考虑每周块的示例和仅请求有关一天的数据的查询。在具有时间戳索引的常规表中，查询可以有效地遍历该索引来查找当天的数据。

但是，压缩数据的情况有所不同：时间戳被压缩，并且在不解压缩整个批次的情况下无法访问。在每个单独的时间戳上创建索引会适得其反，因为它可能会因变得过大而抵消压缩的好处。

Timescale 基本上通过根据时间戳对要批处理的数据进行“排序”来解决这个问题。然后，它记录有关每个批次的最小和最大时间戳的元数据。当执行查询时，此元数据使查询引擎能够快速识别哪些压缩行（批次）与查询的时间范围相关，从而减少完全解压缩的需要。

这种方法与分段列的使用很好地配合。在压缩过程中，数据首先按segmentby列进行分组，然后根据orderby参数进行排序，最后分为更小的、按时间戳排序的“小批量”，每个小批量最多包含1,000行。

TimescaleDB 的分段和排序相结合显着增强了常见时间序列和分析查询的性能。这种跨时间（通过orderby ）和空间（通过segmentby ）的优化可确保 TimescaleDB 有效地管理和查询大规模时间序列数据，从而在压缩和可访问性之间提供优化的平衡。

时间尺度压缩的演变

我们的压缩设计的第一个版本于 2019 年与TimescaleDB 1.5一起发布。在许多版本之后，Timescale 压缩已经取得了长足的进步。

我们初始版本的主要限制之一是，一旦数据被压缩，而没有首先手动解压缩它所在的整个超表块，我们就不允许对数据进行任何进一步的修改，例如插入、更新、删除。

鉴于我们正在针对基于分析数据和时间序列数据的数据密集型用例进行优化，这些数据主要是插入密集型数据而不是更新密集型数据，因此与传统 OLTP 用例相比，这种限制要小得多数据经常更新的地方（例如，客户信息表）。然而， 正如我们在本文中讨论的，在某些情况下需要回填，这使得使用 TimescaleDB 的开发人员管道变得非常复杂。

我们初始压缩版本的另一个限制是我们不允许表中的架构修改，包括压缩数据。这意味着开发人员在不解压整个表的情况下无法改进他们的数据结构， 例如添加新列以适应新指标或新设备。

今天，所有这些限制都被消除了。 Timescale 现在允许您对压缩数据执行完整的数据操作语言 (DML) 和数据定义语言 (DDL) 操作：

• 您可以通过压缩块插入数据（具有出色的性能）。

• 您可以执行 UPDATE、UPSERT 和 DELETE。

  
  

• 您可以添加列，包括默认值。

  
  

• 您可以重命名和删除列。

  
  

为了自动对压缩数据进行数据修改（使其对我们的用户来说是无缝的），我们通过引入“暂存区域”来改变我们的压缩方法——本质上是一个保持未压缩的重叠块，我们在其中“对未压缩数据”进行操作引擎盖。

作为用户，您无需手动执行任何操作：您可以直接修改数据，而我们的引擎会在幕后自动处理所有事情。更改压缩数据的能力使 Timescale 的混合行列存储引擎更加灵活。

通过暂存区域的这种设计使得 INSERT 与插入未压缩块一样快，因为这确实发生了（当您插入压缩块时，您现在正在写入暂存区域）。它还允许我们直接支持 UPDATE、UPSERT 和 DELETE：当需要更改值时，引擎将压缩数据的相关部分移动到暂存区域，对其进行解压缩，进行更改，然后（异步）再次移动它以压缩形式添加到主表。

（该数据区域通常以最多 1,000 个值的压缩“小批量”规模进行操作，这些值构成底层 PostgreSQL 存储中的“行”，以最大程度地减少需要解压缩以支持修改的数据量。）

这个“暂存区域”仍然具有常规的事务语义，并且您的查询一旦插入其中就会看到这些值。换句话说，查询规划器足够聪明，能够理解如何正确查询这些基于行的“分段”块和常规列式存储。

最终结果：大型 PostgreSQL 数据库的查询速度更快，存储占用空间更少

此时，下一个要问的逻辑问题是：最终结果是什么？压缩如何影响查询性能？使用它可以节省多少磁盘大小？

压缩前与压缩后的查询性能

正如我们在本文中讨论的那样，列式存储通常对于检索单个行的查询表现不佳，但对于查看聚合值的分析查询往往表现得更好。这正是我们在 Timescale 中看到的：涉及平均值的深而窄的查询在使用压缩时会看到显着的性能改进。

让我们通过运行几个查询来说明这一点纽约市出租车数据集，我们在 Timescale 中提供的示例数据集之一。该数据集包含出租车行程的信息，包括接送时间、位置、距离、票价等。

考虑以下查询，要求在特定时间范围内从出租车数据集的子集中获取最高票价金额：

 SELECT max(fare_amount) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01';

当针对未压缩的数据集运行时，查询执行时间为 4.7 秒。我们正在使用小型的、未优化的测试服务并查询数百万行，因此这种性能并不是最好的。但压缩数据后，响应时间降低至 77.074 毫秒：

让我们分享另一个例子。此查询计算给定时间范围内具有特定费率代码的行程次数：

 SELECT COUNT(*) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01' AND   "RatecodeID" = 99;

当针对未压缩的数据执行时，该查询将需要 1.6 秒才能完成。针对压缩数据运行的相同查询只需 18.953 毫秒即可完成。我们再次看到立竿见影的改善！这些只是简单的示例，但它们说明了压缩对于加快查询速度有多么强大。

Timescale 的压缩如何减少 PostgreSQL 存储大小：真实示例

我们不要忘记最初是什么让我们来到这里：我们需要一种策略来减少大型 PostgreSQL 数据库的大小，以便进一步扩展 PostgreSQL。为了显示 Timescale 压缩对于此任务的有效性，下表包含了Timescale 客户中看到的压缩率的一些实际示例。

这些存储节省直接转化为节省资金： Timescale 平台使用基于使用情况的存储定价，因此，如果您的存储空间减少，您的账单也会成比例减少。

您最终实现的压缩率取决于几个因素，包括您的数据类型和访问模式。但正如您所看到的，Timescale 压缩可以非常高效 — 我们甚至在内部大量使用它来为我们面向客户的 Insights 产品提供 96% 的压缩率。

我们的团队可以帮助您微调压缩，为您节省尽可能多的钱，所以请随时联系我们。

“通过压缩，我们发现[磁盘大小]平均减少了 97%。”

（迈克尔·加利亚多，工业）

“我们发现 Timescale 的压缩率绝对是惊人的！目前我们的压缩比超过 26，大大减少了存储所有数据所需的磁盘空间。”

（尼古拉斯·昆汀，奥克塔夫）

“Timescale 的压缩效果正如宣传的那样好，这为我们的底层超表节省了 90% 的[磁盘]空间。”

（保罗·贝甘蒂诺，METER 集团）

压缩和分层存储：时间尺度的存储生命周期

最后，如果不提及 Timescale 的分层存储，我们就无法结束本文，我们刚刚发布了正式版本。

除了压缩之外，您现在还有另一个工具可以帮助您在 Timescale 平台中进一步扩展 PostgreSQL 数据库：您可以将旧的、不经常访问的数据分层到低成本对象存储层，同时仍然能够通过标准访问它SQL。

这种低成本存储层的数据统一价格为每 GB/月 0.021 美元，比 Amazon S3 更便宜，让您可以在 PostgreSQL 数据库中保留大量 TB，而成本只是其中的一小部分。

这就是我们的分层存储后端在 Timescale 平台上的工作方式以及低存储层与压缩的配合方式：

• 您的最新数据将写入针对快速查询和高摄取量进行优化的高性能存储层。在这一层中，您可以启用 Timescale 列式压缩来缩小数据库大小并加快分析查询速度，正如我们在本文中讨论的那样。例如，您可以定义一个压缩策略，在 1 周后压缩数据。

  
  

• 一旦您的应用程序不再频繁访问该数据，您可以通过设置分层策略自动将其分层到成本较低的对象存储层。低成本存储层中的数据在您的数据库中仍然完全可查询，并且您可以存储的数据量没有限制 - 高达数百 TB 或更多。例如，您可以定义一个分层策略，将六个月以上的所有数据移至低成本存储层。

  
  

• 一旦您不再需要将此数据保留在数据库中，您可以通过保留策略删除它。例如，您可以在五年后删除所有数据。

继续使用 PostgreSQL

我们通过添加列压缩功能为 Postgres 提供了有效的数据库压缩机制。这是在当今数据密集型世界中扩展 PostgreSQL 数据库的一项重要功能：压缩可以大大节省磁盘使用量（以更便宜的价格存储更多数据）和性能改进（以毫秒为单位运行大量分析查询）。

Timescale 的压缩设计通过将一流的压缩算法与在 PostgreSQL 中创建混合行/列存储的新颖方法相结合，实现了令人印象深刻的压缩率。此功能使 Timescale（以及 PostgreSQL）的存储占用量与定制的、更有限的列式数据库相当。

但与许多列式引擎不同的是，Timescale 支持 ACID 事务语义并直接支持对压缩列式数据的修改（INSERT、UPDATE、UPSERT、DELETE）。由于“一个数据库用于事务工作负载，另一个数据库用于分析”的旧模型已经过时，因此许多现代应用程序运行的工作负载都适合这两种模式。那么，当您可以在 PostgreSQL 中完成这一切时，为什么还要维护两个独立的数据库呢？

Timescale 允许您从 PostgreSQL 开始，使用 PostgreSQL 进行扩展，然后继续使用 PostgreSQL。

创建一个免费帐户今天就尝试一下 Timescale——只需几秒钟，无需信用卡。

- 由卡洛塔·索托和迈克·弗里德曼撰写。