主页 / dba / 问题

问题[timescaledb](dba)

Martin Hope
amq
Asked: 2022-01-20 05:12:30 +0800 CST
  • 2

我记录了带有开始和结束时间戳的会话持续时间：

user_id | session_id | session_start                 | session_end
--------+------------+-------------------------------+------------------------------
1       | 1          | 2021-02-25 10:10:00.000 +0100 | 2021-02-25 10:20:00.000 +0100
1       | 2          | 2021-02-25 10:50:00.000 +0100 | 2021-02-25 10:55:00.000 +0100
1       | 3          | 2021-02-25 11:40:00.000 +0100 | 2021-02-25 12:30:00.000 +0100

获取每个会话的持续时间就像减去两个时间戳一样简单。现在，我想用挂钟每小时桶来表示会话持续时间，每个用户求和。

这里的主要问题是间隔跨越多个小时的会话。一个从 11:40 开始到 12:30 结束的会话应该用 11:00 20 分钟的存储桶和 12:00 30 分钟的存储桶来表示：

user_id | bucket   | duration
--------+----------+---------
1       | 00:00:00 | 00:00:00
1       | 01:00:00 | 00:00:00
...
1       | 10:00:00 | 00:15:00
1       | 11:00:00 | 00:20:00
1       | 12:00:00 | 00:30:00

我尝试使用time_seriesand date_trunc，但没有成功。

理想情况下，存储桶还包括日期，这也可能简化逻辑。如果没有，一次选择一天也可以。

user_id | bucket              | duration
--------+---------------------+----------
1       | 2021-02-25 00:00:00 | 00:00:00
1       | 2021-02-25 01:00:00 | 00:00:00
...
1       | 2021-02-25 10:00:00 | 00:15:00
1       | 2021-02-25 11:00:00 | 00:20:00
1       | 2021-02-25 12:00:00 | 00:30:00

我将使用查询结果生成一个热图，其中一个轴为用户，另一个轴为小时。

postgresql timescaledb
Martin Hope
MortenSickel
Asked: 2021-10-14 22:34:43 +0800 CST
  • 1

我正在运行一个 PostgreSQL 数据库，该数据库正在记录许多系统的健康状况信息。目前，它在标准的 PostgreSQL 服务器上运行，但似乎 TimescaleDB 有一些可用的特性。

大多数系统都是直接记录的，因此数据在收集后立即存储。但有些系统并不总是在线。他们离线时会在本地记录数据，在线时会将收集到的数据下载并插入数据库。这种模式会对 TimescaleDB 造成任何问题吗？在某些文档中，按时间顺序写入数据似乎很重要。

postgresql timescaledb
Martin Hope
Emre Sevinç
Asked: 2021-06-18 01:10:00 +0800 CST
  • 0

在 'vanilla' PostgreSQL 12.7数据库中，我通常运行以下查询来了解具有 100+ 百万行的表中的估计行数：

----------------------------------------------------------
-- Return the estimated number of rows for the given table
----------------------------------------------------------
SELECT reltuples::bigint AS estimate_number_of_rows
FROM   pg_class
WHERE  oid = to_regclass('name_of_some_big_table');

这种类型的查询不适用于我们的多节点TimescaleDB安装上的分布式超表。

我检查了TimescaleDB API 参考，但找不到我要找的东西。

有没有这样一个简单的查询来快速返回分布式超表中的估计行数？

timescaledb
Martin Hope
Mikko Ohtamaa
Asked: 2021-06-14 10:56:18 +0800 CST
  • 1

我有一个带有本地 Linux 卷映射的 Docker PostgreSQL (TimescaleDB) 开发人员实例。

version: '3'
services:
  dex-timeseriesdb:
    image: timescale/timescaledb:latest-pg12
    # https://stackoverflow.com/a/56754077/315168
    shm_size: 1g
    container_name: dex-timeseriesdb
    environment:
      POSTGRES_USER: postgres
    volumes:
       - $PWD/data/postgresql:/var/lib/postgresql/data

非正常关闭后，实例不再以FATAL: xlog flush request 0/2CEFA910 is not satisfied --- flushed only to 0/1B48258错误启动：

dex-timeseriesdb    |
dex-timeseriesdb    | PostgreSQL Database directory appears to contain a database; Skipping initialization
dex-timeseriesdb    |
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  listening on IPv6 address "::", port 5432
dex-timeseriesdb    | 2021-06-13 18:50:47.336 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
dex-timeseriesdb    | 2021-06-13 18:50:47.486 UTC [21] LOG:  database system shutdown was interrupted; last known up at 2021-06-13 18:47:35 UTC
dex-timeseriesdb    | 2021-06-13 18:50:49.629 UTC [21] LOG:  database system was not properly shut down; automatic recovery in progress
dex-timeseriesdb    | 2021-06-13 18:50:49.645 UTC [21] LOG:  redo starts at 0/1B46C68
dex-timeseriesdb    | 2021-06-13 18:50:49.648 UTC [21] LOG:  invalid record length at 0/1B48258: wanted 24, got 0
dex-timeseriesdb    | 2021-06-13 18:50:49.648 UTC [21] LOG:  redo done at 0/1B48220
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] LOG:  request to flush past end of generated WAL; request 0/2CEFA910, currpos 0/1B48258
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] CONTEXT:  writing block 0 of relation base/13455/16573_vm
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] FATAL:  xlog flush request 0/2CEFA910 is not satisfied --- flushed only to 0/1B48258
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] CONTEXT:  writing block 0 of relation base/13455/16573_vm
dex-timeseriesdb    | 2021-06-13 18:50:49.701 UTC [1] LOG:  startup process (PID 21) exited with exit code 1
dex-timeseriesdb    | 2021-06-13 18:50:49.701 UTC [1] LOG:  aborting startup due to startup process failure
dex-timeseriesdb    | 2021-06-13 18:50:49.744 UTC [1] LOG:  database system is shut down

这可能是由于不干净的 Docker 关闭造成的数据损坏。

数据库中没有什么重要的东西。但是，我仍然想了解在这种情况下是否可以恢复数据库，而不是从头开始重建它或从备份中恢复。

我使用 shell 测试了卷映射在 Docker 实例中是可写的，所以这应该不是问题。

另请参阅有关 FATAL: xlog flush request, but稍有不同的错误的类似问题。

postgresql timescaledb
Martin Hope
sev
Asked: 2021-05-19 04:11:08 +0800 CST
  • 2

我有一个 PostgreSQL 表，我正在尝试将其转换为 TimescaleDB 超表。

该表如下所示：

CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

当我尝试使用以下命令将此表转换为 TimescaleDB 超表时：

SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

我得到错误：ERROR: cannot create a unique index without the column "event_time" (used in partitioning)

问题 1：从这篇文章https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi我的理解是这个是因为我指定了一个唯一约束 (con1)，它不包含我要分区的列 - event_time。那是对的吗？

问题 2：我应该如何更改我的表或超表才能进行转换？我已经添加了一些关于我计划如何使用数据和数据结构的数据。

数据属性和用法：

  • 可以有多个条目具有相同的 event_time - 这些条目将具有按顺序排列的 entry_id
    • 这意味着如果我有 2 个条目 (event_time 2021-05-18::10:16, id 105, <some_data>) 和 (event_time 2021-05-18::10:16, id 107, <some_data>) 那么id 为 106 的条目也将具有 event_time 2021-05-18::10:16
  • entry_id 不是我生成的，我使用唯一约束 con1 来确保我没有插入重复数据
  • 我将主要在 event_time 上查询数据，例如创建绘图并执行其他分析
  • 此时，数据库包含大约 46 亿行，但很快就会包含更多
  • 我想利用 TimescaleDB 的速度和良好的压缩
  • 我不太关心插入性能

我一直在考虑的解决方案：

  • 以某种方式将所有具有相同时间戳的事件打包到一个数组中，并将它们保持在一行中。我认为这会对压缩产生不利影响，并且在查询数据时提供较少的灵活性。此外，我可能最终不得不解压缩每个查询的数据。
  • 删除唯一约束 con1 - 那么我如何确保我不会两次添加同一行？
  • 扩展唯一约束 con1 以包含 event_time - 这不会以某种方式降低性能，同时会导致我不小心插入 2 行的 entry_id 和 pair_id 但不同的 event_time 的错误？（我怀疑这可能会发生）
postgresql timescaledb
Martin Hope
JPM
Asked: 2021-03-17 01:07:14 +0800 CST
  • -1

我有一个像这样的 timescaledb 超表：

create table logs
(
    time         timestamp not null,
    partitionkey text      not null,
    ip           inet,
    raw          text,
    transformed  double precision
);

和索引如下：

create index logs_time_idx
    on logs (time desc);

create unique index logs_partitionkey_time_uindex
    on logs (partitionkey asc, time desc);

当我运行此查询时，需要 20 分钟才能完成：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '%m.60.05482730' 
AND time > NOW() - INTERVAL '3 days'

但是当我运行这个时，它需要 2 秒：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '865617033605366.m.60.05482730'
AND time > NOW() - INTERVAL '3 days'

我尝试仅索引分区键以帮助通配符查询找到匹配值，但这没有效果。

-- created this index later to try and fix the slow wildcard query
create index logs_partitionkey_index
    on logs (partitionkey);

解释通配符查询的计划：

Gather  (cost=1000.57..525711.89 rows=1219 width=81)
  Workers Planned: 2
  ->  Parallel Custom Scan (ChunkAppend) on logs  (cost=0.57..524589.99 rows=509 width=82)
        Chunks excluded during startup: 2
        ->  Parallel Index Scan using _hyper_2_10_chunk_logs_time_idx on _hyper_2_10_chunk  (cost=0.57..263956.91 rows=255 width=81)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
        ->  Parallel Index Scan using _hyper_2_9_chunk_logs_time_idx on _hyper_2_9_chunk  (cost=0.57..260629.72 rows=252 width=83)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

解释具体的partionkey值：

Custom Scan (ChunkAppend) on logs  (cost=0.44..903.08 rows=790 width=82)
  Chunks excluded during startup: 2
  ->  Index Scan using _hyper_2_9_chunk_logs_partitionkey_time_uindex on _hyper_2_9_chunk  (cost=0.57..447.44 rows=392 width=83)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)
  ->  Index Scan using _hyper_2_10_chunk_logs_partitionkey_time_uindex on _hyper_2_10_chunk  (cost=0.57..452.27 rows=396 width=81)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)

TimescaleDB 是否无法执行通配符 (%) 查询，还是我错过了索引？

postgresql-12 timescaledb
Martin Hope
gies0r
Asked: 2019-09-13 02:39:37 +0800 CST
  • 1

在 timescaledb 超表上也可以使用标准的 postgresql 方式吗？

CLUSTER [VERBOSE] tablename [ USING indexname ]

例子：

  1. 创建表和超类型
CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data VARCHAR(8) NOT NULL,

SELECT create_hypertable('l1', 'timestamp', chunk_time_interval => interval '1 day');
  1. 生成这样的索引（可以使用此命令查看）：
l1        | l1_timestamp_idx                                               | CREATE INDEX l1_timestamp_idx ON public.l1 USING btree ("timestamp" DESC)
  1. 而不是CLUSTER这样的数据
CLUSTER l1 USING l1_timestamp_idx;
stdout: CLUSTER

看起来它有效..但是这是推荐的吗？是否可以颠倒CLUSTER命令的顺序？意思是，首先用最低的时间戳对其进行排序。

postgresql timescaledb