超表

Postgres 分区真的那么难吗？超表的介绍

A colorful tiger with a grid (representing the many Postgres partitions) in the background.

作者：Carlota Soto

如果您正在处理不断增长的 PostgreSQL 表，您可能已经熟悉高效管理大型数据集所面临的挑战。

  • 您的查询性能正在下降。

  • 您正在处理维护开销。

  • 您发现很难跟上高吞吐量。

  • 您在管理数据生命周期方面遇到了麻烦。

Postgres 分区是解决这些问题的强力助手。通过对大型 PostgreSQL 表进行分区，您可以保持它们快速高效。但要在生产环境中设置和维护已分区的 PostgreSQL 表可能会很困难。

您可能会想，“是的，我可以通过对表进行分区来提高性能，但这将以花费大量时间进行手动配置、运行维护作业、测试，以及在扩展过程中可能出现的不可预见的问题为代价。这就像拥有一辆功能强大的汽车，但换挡装置非常复杂。”如果您在像 Amazon RDS 这样的产品中使用普通 PostgreSQL，那么这句话有一定道理。毫无疑问，您将花费大量时间来管理已分区的表。此外，您还必须处理自定义脚本，保持严格的维护实践，并仔细监控性能，以便在数据集或吞吐量发生变化时重新审视并调整配置。

但猜猜看：有一种更好的创建 Postgres 分区的方法，它被称为超表。

认识超表：适用于大型 PostgreSQL 表的自动 PostgreSQL 分区

超表（可通过 TimescaleDB 扩展 获取，在 AWS 中可通过 Timescale 平台 获取）是一种创新技术，使创建 Postgres 分区的体验变得无缝。它们自动生成和管理数据分区，而不会改变您的用户体验。

使用超表的感觉与使用普通的 PostgreSQL 表完全一样。但在幕后，超表会创建所有分区魔法，从而加速您的查询和插入操作。这种性能提升将随着表容量的不断增长而持续，使超表具有极高的可扩展性.

A representation of a hypertable—a regular Postgres table—and a chunk (a data partition within a hypertable).

超表看起来像普通的 PostgreSQL 表，但在幕后，它们会被自动分区以提高性能。

超表针对 **基于时间的分区** 进行了优化，因此本文将重点介绍这种分区类型。但是，超表也适用于非基于时间的表，但具有类似的特征，例如，BIGINT 主键。

让我们通过一个例子来解释超表的运作方式。

假设您有一个名为 sensor_data 的 PostgreSQL 表，其中存储了来自各种物联网设备的数据，并带有一个时间戳。该表可能看起来像这样

CREATE TABLE sensor_data (
    device_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature FLOAT NOT NULL,
    humidity FLOAT NOT NULL
);

现在，随着 sensor_data 容量的增长，您开始遇到性能问题和管理复杂性。此时，超表可以提供帮助。如果您使用的是 Timescale，您只需要将 sensor_data 表转换为超表：

SELECT create_hypertable('sensor_data', 'event_time');

就是这样简单。通过这个简单的命令，sensor_data 现在是一个超表，它会根据 event_time 列自动对您的数据进行分区。

您的 PostgreSQL 分区已设置完成。

The data is inserted into a hypertable, automatically partitioning it into chunks.

您的数据会在被插入超表时自动进行分区，您无需手动创建或管理这些分区。

原生分区与超表：有多大区别？

让我们来看看幕后发生了什么。

如果您使用传统原生方法来创建 Postgres 分区，您必须执行以下所有步骤才能在 sensor_data 中设置分区：

  1. 创建一个具有公共模式和约束但没有数据的父表。

  2. 定义子表，每个子表涵盖特定的时间范围。

  3. 为父表和子表添加索引。

  4. 设置用于调度分区创建的作业。

  5. 设置用于管理旧分区的作业。

  6. 将所有内容连接起来。

每个步骤都需要相应的代码；它们要求您运行不同的扩展，例如pg_partman 和cron；您还必须监控这些步骤中可能出现的任何问题，并在整个过程中手动设置调整等。总的来说，您将为自己造成相当大的维护开销。

超表的作用是封装和自动化所有这些步骤，从而大大减少了您的复杂性、手动工作量以及出错的可能性。

  • 使用超表，无需手动创建父表，也无需为每个时间范围定义子表。您只需将现有表转换为超表即可。

  • 超表还简化了索引。当您在超表上创建索引时，Timescale 会自动在所有当前和将来的分区上创建相应的索引，确保一致的查询性能，无需手动调整。

  • 超表会根据指定的时间间隔自动创建新的分区。当插入新数据时，相应的分区会准备就绪以存储数据，无需手动干预或安排作业。使用 Timescale 可消除分区不存在的风险，完全免除了您对分区管理的担忧。

  • Timescale 维护自己的分区目录，并实施自己的最小锁定策略，以确保应用程序的读写操作永远不会被底层分区操作阻塞（这在原生 PostgreSQL 分区中可能会成为问题）。

将您的 PostgreSQL 表转换为超表后，您可以像往常一样继续对其进行查询。您将立即体验到性能提升。当您执行查询时，Timescale 的查询规划器会将查询智能地路由到适当的分区，确保仅扫描相关数据。整个过程对您是完全透明的，您无需考虑或担心哪个分区包含哪些数据。

当您插入数据时，也会发生类似的简单过程。Timescale 会在幕后负责将您的新数据路由到适当的分区，确保每个分区的大小都保持最佳状态。（Timescale 的默认分区大小为 7 天，但您可以轻松地修改它。）

分区仅仅是开始：超表解锁的功能

超表使分区变得无缝，并且解锁了一系列功能，这些功能将帮助您进一步提高 PostgreSQL 性能，并在管理数据时节省时间。

以下是一些例子：

  • 列式压缩，实现更快的查询和更低廉的存储成本。通过启用 Timescale 压缩，您的超表将从行式转换为列式。这可以将存储使用量减少高达 95%，并解锁闪电般的快速分析查询，同时 允许数据更新。

  • 闪电般快速的分析视图。 通过创建增量更新的物化视图（称为连续聚合），您可以极大地提高聚合查询的性能。

    连续聚合会自动刷新和存储聚合数据，使您能够构建快速的可视化，包括实时洞察和追溯到过去的历史分析。

  • 轻松且可配置的数据保留。 超表允许您使用一个简单的命令设置自动数据保留策略：add_retention_policy。您只需告诉 Timescale 您希望何时删除数据，您的超表将在适当的时候自动删除过时分区。

  • 用于用更少代码行运行分析的 SQL 超函数。 超表附带一整套 超函数，为您提供针对有效查询、聚合和分析大量数据进行了优化的闪电般快速的完整数学分析函数、过程和数据类型集。

  • 更快地DISTINCT和now()查询。 在修剪分区时引用 now( ) 的查询 在 Timescale 中将执行得更好，并且您的有序 DISTINCT 查询将受益于 SkipScan。

  • 内置作业调度程序。 Timescale 作业调度程序 允许您在 PostgreSQL 中调度任何基于 SQL 或函数的作业，这意味着您不需要外部调度程序或其他扩展，如 pg_cron。

何时使用超表：示例用例

总之，如果您计划按时间对 PostgreSQL 表进行分区，那么您一定会从超表中受益。但谁不喜欢一些具体的用例示例呢？

让我们画几个超表最有用场景的场景。不用说，这不是一个完整的列表！如果您对超表和 Timescale 感兴趣，但不确定您的用例是否适合，请随时联系我们。

每秒摄取数千个能源指标

一家领先能源公司的工程团队负责管理从新安装的智能电网中收集的数据，这对于能源公司来说是一项重大投资，该公司现在可以深入了解能源消耗、分配效率和电网健康指标。智能电网中的元素每秒会生成数千个能源指标，这些指标需要妥善收集、分析和管理。

这些能源指标目前存储在 PostgreSQL 中，但工程团队必须找出最佳解决方案，以高效地摄取这些高速数据，而不会丢失粒度或准确性。他们还必须确保可以快速查询这些数据，以进行实时监控和分析。

这将是 Timescale 的理想用例：

  • Timescale 的超表可以处理高摄取率，而不会给团队带来额外的手动工作。

  • 超表还优化了查询性能，确保实时能源数据可以随时用于查询。

  • 随着智能电网的扩展，Timescale 的超表将无缝扩展，以适应不断增长的数据量，而不会影响性能。

  • 鉴于 Timescale 基于 PostgreSQL 构建，工程团队可以利用其现有的知识和工具，确保平稳过渡，并减少学习曲线。

-- Creating a table to store energy metrics from the smart grid
CREATE TABLE energy_metrics (
    element_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    voltage DECIMAL NOT NULL,
    current DECIMAL NOT NULL,
    frequency DECIMAL NOT NULL,
    PRIMARY KEY(grid_id, event_time)
);

-- Converting the energy_metrics table into a hypertable
SELECT create_hypertable('energy_metrics', 'event_time');

-- Sample query to ingest new metrics data into the hypertable
INSERT INTO energy_metrics (element_id, event_time, voltage, current, frequency) 
VALUES  (1, NOW(), 210.5, 10.7, 50.01);

-- Sample query to retrieve the latest energy metrics for real-time monitoring
SELECT * FROM energy_metrics 
WHERE element_id = 1 
ORDER BY event_time DESC 
LIMIT 10;

构建用于监控传感器数据的仪表板

一家工业制造公司在其设施中运行各种重型机械和设备。每台机器都配备有传感器，这些传感器会连续监控和记录 sensor_data 表中的温度数据，以确保最佳性能和安全。

该公司需要其 PostgreSQL 数据库实现两个不同但重要的目标

  • 为工程师和维护人员提供实时温度数据，以检测异常情况，确保机器在安全温度范围内运行。

  • 分析历史温度数据以识别趋势、预测维护需求和提高运营效率。

该团队决定将 sensor_table 转换为超表。为了便于实时监控，他们创建了一个连续聚合，以计算每台机器的平均温度，该平均温度每分钟更新一次

CREATE MATERIALIZED VIEW real_time_avg_temp
WITH (timescaledb.continuous, timescaledb.refresh_interval = '1m') AS
SELECT device_id,
       time_bucket('1 minute', event_time) AS one_min,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_min;

使用 real_time_avg_temp，维护团队可以立即访问每台机器的平均温度，从而能够对温度异常情况做出快速响应，并防止潜在的故障。

为了进行历史分析，该团队创建了另一个连续聚合视图，这次聚合了每日平均温度

CREATE MATERIALIZED VIEW daily_avg_temp
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket('1 day', event_time) AS one_day,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_day;

这两个视图（real_time_avg_temp 和 daily_avg_temp）都馈送到监控仪表板中。维护团队会在问题出现时收到警报。同时，该团队可以查看历史温度趋势、进行分析以预测机器何时可能需要维护，以及优化操作协议以减少过度温度波动。

有效存储大量天气数据

一家环境研究机构正在收集和分析大量 TB 级的天气数据，以研究气候变化。该团队已经熟悉 PostgreSQL，所以他们想继续使用它，但存储成本正在成为一个问题。

该团队决定开始使用 Timescale。在优化数据库以减少存储使用并启用压缩后，他们的存储成本降低到之前的几分之一，同时数据仍然可以完全访问以进行分析。

CREATE TABLE weather_data (
    sensor_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature DECIMAL NOT NULL,
    humidity DECIMAL NOT NULL,
    pressure DECIMAL NOT NULL,
    PRIMARY KEY(sensor_id, event_time)
);

-- Conversion to hypertable 
SELECT create_hypertable('weather_data', 'event_time');

-- Enabling compression
ALTER TABLE weather_data 
SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);

-- Sample query
SELECT * FROM weather_data 
WHERE sensor_id = 1 
AND event_time > NOW() - INTERVAL '1 month'
ORDER BY event_time DESC 
LIMIT 10;

实时查询大量加密数据

一家新的加密货币交易所正在努力为交易者提供实时分析。随着存储在底层 PostgreSQL 数据库中的数据量的增加，工程团队难以保持数据库足够快。对他们来说，提供比竞争对手更出色的用户体验至关重要，竞争对手拥有更成熟但速度更慢的产品。保持其门户网站的速度和响应能力至关重要。

该团队知道，通过对其大型定价表进行分区，他们很有可能提高查询性能。与其尝试自行管理分区（因为他们已经忙得不可开交），工程师决定实施 Timescale。

一旦转换为超表，他们的定价表会自动在数据输入时对其进行分区。以前因延迟而受损的实时分析现在变得快速而准确。附注：查看 Messari 如何使用超表提高性能的故事。

开始使用超表

PostgreSQL 分区是管理大型表的强大工具。单独使用 Postgres 分区可能很复杂，难以实现和维护，但 Timescale 的超表使整个过程变得无缝且自动。最棒的是，通过使用超表，您可以解锁大量其他很棒的功能（例如列式压缩和自动物化视图），这些功能将使扩展 PostgreSQL 数据库变得更加容易。

如果您已准备好探索 Timescale 的超表，请开始注册 Timescale，我们的全托管 PostgreSQL（但速度更快）云解决方案。它免费试用 30 天，不需要信用卡。如果您自托管自己的 PostgreSQL 实例，则可以通过添加 TimescaleDB 扩展来访问超表.