优化您的数据以实现实时分析

连接到您的 Timescale Cloud 服务

在 Timescale Console 中打开一个 SQL 编辑器。您也可以使用 psql 连接到您的服务。

在超表上启用列式存储

使用 CREATE TABLE 为您的时序数据创建超表。为了在列式存储数据上进行高效查询，请记住按您最常用于过滤数据的列进行segmentby分段。例如

• 对超表使用CREATE TABLE

  CREATE TABLE crypto_ticks (
     "time" TIMESTAMPTZ,
     symbol TEXT,
     price DOUBLE PRECISION,
     day_volume NUMERIC
  ) WITH (
    tsdb.hypertable,
    tsdb.partition_column='time',
    tsdb.segmentby='symbol', 
    tsdb.orderby='time DESC'
  );

  复制

  如果您自托管 TimescaleDB v2.19.3 及更早版本，请创建一个 PostgreSQL 关系表，然后使用 create_hypertable 进行转换。之后，通过调用 ALTER TABLE 来启用 Hypercore。

• 对连续聚合使用ALTER MATERIALIZED VIEW

  ALTER MATERIALIZED VIEW assets_candlestick_daily set (
     timescaledb.enable_columnstore = true, 
     timescaledb.segmentby = 'symbol' );

  复制

  在您说“嗯？”之前，需要说明的是，连续聚合是一种特殊的超表。

添加策略以在特定时间间隔将数据块转换为列式存储

创建一个 columnstore_policy，它会在特定时间间隔自动将超表中的数据块转换为列式存储。例如，将昨天的加密货币交易数据转换为列式存储

CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '1d');

复制

TimescaleDB 针对列式存储中压缩数据的快速更新进行了优化。要修改列式存储中的数据，请使用标准 SQL。

检查列式存储策略

1. 查看数据存储空间节省情况

   当您将数据转换为列式存储时，除了为分析进行优化外，数据还会被压缩 90% 以上。这有助于您节省存储成本，并使您的查询以极快的速度运行。要查看节省的空间量

   SELECT 
     pg_size_pretty(before_compression_total_bytes) as before,
     pg_size_pretty(after_compression_total_bytes) as after
   FROM hypertable_columnstore_stats('crypto_ticks');

   复制

   您会看到类似以下内容

   之前	之后
   194 MB	24 MB

2. 查看您设置的策略或已存在的策略

   SELECT * FROM timescaledb_information.jobs
   WHERE proc_name='policy_compression';

   复制

   请参阅 timescaledb_information.jobs。

暂停列式存储策略

SELECT * FROM timescaledb_information.jobs where 
   proc_name = 'policy_compression' AND relname = 'crypto_ticks'

-- Select the JOB_ID from the results
  
SELECT alter_job(JOB_ID, scheduled => false);

复制

请参阅 alter_job。

重启列式存储策略

SELECT alter_job(JOB_ID, scheduled => true);

复制

请参阅 alter_job。

移除列式存储策略

CALL remove_columnstore_policy('crypto_ticks');

复制

请参阅 remove_columnstore_policy。

禁用列式存储

如果您的表在列式存储中有数据块，则必须在禁用列式存储之前将数据块转换回行式存储。

ALTER TABLE crypto_ticks SET (timescaledb.enable_columnstore = false);

复制

请参阅 alter_table_hypercore。