timescaledb
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseTimescaleDB
TimescaleDB
TimescaleDB is a time-series database built as an extension on top of PostgreSQL. It gives you the scale of NoSQL time-series with the reliability and tooling of Postgres.
TimescaleDB是一款构建于PostgreSQL之上的时序数据库扩展。它兼具NoSQL时序数据库的扩展性，以及Postgres的可靠性和丰富工具链。
When to Use
适用场景
- SQL familiarity: You want time-series but already know SQL and use Postgres drivers.
- Relational + Time: You need to JOIN your sensor data (Time Series) with Device metadata (Relational Tables).
- Compression: Highest-in-class compression (90%+) for historical data.
- 熟悉SQL：你需要处理时序数据，但已经掌握SQL并正在使用Postgres驱动。
- 关系型+时序数据结合：你需要将传感器时序数据与设备元数据（关系型表）进行JOIN关联。
- 数据压缩：需要对历史数据进行业内领先的高比例压缩（90%以上）。
Quick Start
快速开始
sql
-- Convert standard table to hypertable
SELECT create_hypertable('conditions', 'time');
-- Query using standard SQL time-bucket functions
SELECT time_bucket('15 minutes', time) AS bucket,
avg(temperature)
FROM conditions
GROUP BY bucket
ORDER BY bucket DESC;sql
-- Convert standard table to hypertable
SELECT create_hypertable('conditions', 'time');
-- Query using standard SQL time-bucket functions
SELECT time_bucket('15 minutes', time) AS bucket,
avg(temperature)
FROM conditions
GROUP BY bucket
ORDER BY bucket DESC;Core Concepts
核心概念
Hypertables
Hypertables
The abstraction layer. It looks like a single table, but effectively partitions data into chunks by time interval.
这是TimescaleDB的抽象层。它看起来像一张普通表，但实际上会按时间间隔将数据分区为多个数据块（chunks）。
Continuous Aggregates
Continuous Aggregates
Real-time materialized views. "Keep a running average of temperature per hour". It updates incrementally.
实时物化视图。例如“持续维护每小时温度的运行平均值”，它会进行增量更新。
Compression
Compression
Columnar compression on old chunks. Turns row-based Postgres pages into highly compressed columnar arrays.
对旧数据块进行列式压缩。将Postgres基于行的页面转换为高度压缩的列式数组。
Best Practices (2025)
最佳实践（2025）
Do:
- Enable Compression: It improves query speed (less I/O) and saves massive disk space.
- Use Tiered Storage: Keep recent hot data on SSD, move compressed old data to S3 (Bottomless storage in cloud).
- Join tables: Use the power of Postgres to join your metrics with your business data.
Don't:
- Don't update compressed chunks: Updating old, compressed data is slow (Copy-on-write). Design for append-only patterns.
建议：
- 启用压缩：提升查询速度（减少I/O操作）并大幅节省磁盘空间。
- 使用分层存储：将近期热数据存储在SSD上，将压缩后的旧数据迁移至S3（云环境下的无限存储）。
- 关联表查询：利用Postgres的能力将指标数据与业务数据进行关联。
不建议：
- 不要更新已压缩的数据块：更新旧的压缩数据速度很慢（采用写时复制机制）。设计时应采用仅追加的模式。