1. Overview

1.概述

In this article, we’ll explore TimescaleDB, an open-source time-series database built on top of PostgreSQL. We’ll delve into its features, examine its capabilities, and discuss how to effectively interact with this database.

在本文中，我们将探讨TimescaleDB，这是一个基于PostgreSQL构建的开源时间序列数据库。我们将深入探讨其特性，检查其功能，并讨论如何与此数据库进行有效交互。

2. What Is TimescaleDB

2.什么是 TimescaleDB

TimescaleDB is an open-source database extension for PostgreSQL, designed to handle time-series data effectively. It extends PostgreSQL’s capabilities to provide dedicated features for time-series data including automated time partitioning, optimized indexing, and compression.

TimescaleDB 是 PostgreSQL 的开源数据库扩展，旨在有效处理时间序列数据。它扩展了 PostgreSQL 的功能，为时间序列数据提供专用功能，包括自动时间分区、优化索引和压缩。

Let’s have a brief look at some of its key features:

让我们简要了解一下它的一些主要功能：

Hypertables are a key concept in TimescaleDB. They are PostgreSQL tables that automatically partition data by time.
It supports continuous aggregates, allowing us to pre-compute and store aggregates. It speeds up query performance by avoiding the need to compute aggregates dynamically while querying.
It employs advanced compression techniques to minimize storage requirements while maintaining query performance.
TimescaleDB uses multi-dimensional indexing and time-based indexing to speed up queries, making it well-suited for time-series data.

3. Installation

3.安装

To get started with TimescaleDB, we first need to make sure to have PostgreSQL installed on our machine. We can install PostgreSQL using the package manager of our operating system, or by downloading it from PostgreSQL’s website.

要开始使用 TimescaleDB，我们首先需要确保计算机上安装了 PostgreSQL。我们可以使用操作系统的软件包管理器安装 PostgreSQL，或者从 PostgreSQL 的 website 下载。

After successfully installing PostgreSQL, we can proceed to install TimescaleDB. We’ll also need to install Homebrew before initiating the installation:

成功安装 PostgreSQL 后，我们可以继续安装 TimescaleDB。在开始安装之前，我们还需要安装 Homebrew ：

brew tap timescale/tap
brew install timescaledb

4. Using TimescaleDB

4.使用 TimescaleDB

4.1. Initializing TimescaleDB

4.1.初始化 TimescaleDB

Upon successful installation, let’s connect to our PostgreSQL instance and run the query to initialize the TimescaleDB instance:

安装成功后，让我们连接到 PostgreSQL 实例并运行查询来初始化 TimescaleDB 实例：

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

This query sets up the necessary extension and prepares our PostgreSQL environment for efficient time-series data handling.

该查询设置了必要的扩展，为我们的 PostgreSQL 环境高效处理时间序列数据做好了准备。

4.2. Creating a Hypertable

4.2.创建超级表

TimescaleDB introduces the concept of Hypertable, which is a partitioned table optimized for time-series storage. A hypertable is always partitioned on time, since it’s intended for time-series data, and possesses the flexibility to partition on additional columns as well. We can use one of the supported data types for partitioning: date, smallint, int, bigint, timestamp, and timestamptz.

TimescaleDB 引入了超表格（Hypertable）的概念，这是一种针对时间序列存储进行了优化的分区表。Hypertable 总是按时间分区的，因为它的目标是时间序列数据，而且还可以灵活地按其他列分区。我们可以使用其中一种受支持的数据类型进行分区：date, smallint, int, bigint, timestamp, 和 timestamptz.

Creating a hypertable involves creating a table first and subsequently converting it to a hypertable. Let’s first create a regular table:

创建超表格需要先创建表格，然后将其转换为超表格。让我们先创建一个普通表格：

CREATE TABLE employee(
    id int8 NOT NULL,
    name varchar(255) NOT NULL,
    login_time timestamp,
    logout_time timestamp);

Now, let’s convert it into a hypertable:

现在，让我们把它转换成一个高密度表格：

SELECT create_hypertable('employee', by_range('login_time'));

The required parameters of create_hypertable() are the table name employee and the dimension builder by_range(‘login_time’). The dimension builder defines which column we want to use to partition the table.

create_hypertable() 所需的参数是表名 employee 和维度创建器 by_range(‘login_time’) 。维度创建器定义了我们要用于分割表的列。

In this case, we could skip the dimension builder because create_hypertable() automatically assumes that a single specified column is range-partitioned by time by default.

在这种情况下，我们可以跳过维度生成器，因为create_hypertable() 默认情况下会自动假设单个指定列按时间进行了范围分区。

For existing data in the table, we can include the migrate_data option for migration:

对于表中的现有数据，我们可以使用 migrate_data 选项进行迁移：

SELECT create_hypertable('employee', by_range('login_time'), migrate_data => true);

4.3. Inserting and Querying Data

4.3.插入和查询数据

With the hypertable in place, we can start adding data. TimescaleDB provides effective mechanisms for handling large volumes of timestamped information. Let’s perform the insertion:

有了 hypertable，我们就可以开始添加数据了。TimescaleDB 提供了处理大量时间戳信息的有效机制。让我们执行插入操作：

INSERT INTO employee values('1', 'John', '2023-01-01 09:00:00', '2023-01-01 18:30:00');
INSERT INTO employee values('2', 'Sarah', '2023-01-02 08:45:12', '2023-01-02 18:10:10');

One of the compelling features of TimescaleDB is its capability to perform efficient queries on time-series data using standard SQL. With our data successfully populated, let’s proceed to query it:

TimescaleDB的一个引人注目的功能是它能够使用标准SQL对时间序列数据执行高效查询。数据已成功填充，让我们开始查询：

SELECT * FROM employee WHERE login_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 12:00:00';

This query retrieves data for a specific time range, showcasing the ease of analyzing and visualizing time-series data with TimescaleDB.

该查询检索特定时间范围内的数据，展示了使用 TimescaleDB 分析和可视化时间序列数据的便捷性。

5. Conclusion

5.结论

In this article, we presented a quick overview of TimescaleDB, delving into the fundamental steps for setting it up and querying data. TimescaleDB efficiently handles large time-series datasets, making it invaluable for robust data management in various applications.

在本文中，我们快速介绍了 TimescaleDB，深入探讨了设置和查询数据的基本步骤。TimescaleDB 可高效处理大型时间序列数据集，因此在各种应用中进行稳健的数据管理非常有价值。