我们设置了一个Prometheus Postgres Exporter，希望我们可以获得插入表的行的统计信息
pg_stat_user_tables:
query: |
SELECT
current_database() datname,
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum,
COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum,
COALESCE(last_analyze, '1970-01-01Z') as last_analyze,
COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
FROM
pg_stat_user_tables
metrics:
- datname:
usage: "LABEL"
description: "Name of current database"
- schemaname:
usage: "LABEL"
description: "Name of the schema that this table is in"
- relname:
usage: "LABEL"
description: "Name of this table"
- seq_scan:
usage: "COUNTER"
description: "Number of sequential scans initiated on this table"
- seq_tup_read:
usage: "COUNTER"
description: "Number of live rows fetched by sequential scans"
- idx_scan:
usage: "COUNTER"
description: "Number of index scans initiated on this table"
- idx_tup_fetch:
usage: "COUNTER"
description: "Number of live rows fetched by index scans"
- n_tup_ins:
usage: "COUNTER"
description: "Number of rows inserted"
- n_tup_upd:
usage: "COUNTER"
description: "Number of rows updated"
- n_tup_del:
usage: "COUNTER"
description: "Number of rows deleted"
- n_tup_hot_upd:
usage: "COUNTER"
description: "Number of rows HOT updated (i.e., with no separate index update required)"
- n_live_tup:
usage: "GAUGE"
description: "Estimated number of live rows"
- n_dead_tup:
usage: "GAUGE"
description: "Estimated number of dead rows"
- n_mod_since_analyze:
usage: "GAUGE"
description: "Estimated number of rows changed since last analyze"
- last_vacuum:
usage: "GAUGE"
description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
- last_autovacuum:
usage: "GAUGE"
description: "Last time at which this table was vacuumed by the autovacuum daemon"
- last_analyze:
usage: "GAUGE"
description: "Last time at which this table was manually analyzed"
- last_autoanalyze:
usage: "GAUGE"
description: "Last time at which this table was analyzed by the autovacuum daemon"
- vacuum_count:
usage: "COUNTER"
description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
- autovacuum_count:
usage: "COUNTER"
description: "Number of times this table has been vacuumed by the autovacuum daemon"
- analyze_count:
usage: "COUNTER"
description: "Number of times this table has been manually analyzed"
- autoanalyze_count:
usage: "COUNTER"
description: "Number of times this table has been analyzed by the autovacuum daemon"
但对于被压缩的表，我们没有得到任何统计数据，就像它不是按时间尺度计算的一样。
表创建如下：
– Create hypertable
SELECT create_hypertable('our_table', 'timestamp', 'unit_id', 1,
if_not_exists => true, chunk_time_interval => INTERVAL '1 day'
);
-- Add compression policy
SELECT add_compression_policy('our_table', INTERVAL '1 day');
-- Enable compression
ALTER TABLE our_tableSET (
timescaledb.compress,
timescaledb.compress_segmentby = 'unit_id'
);
– Compress chunks older then 1 day
SELECT compress_chunk(i)
FROM show_chunks('our_table', older_than => INTERVAL '1 day') i;
为此类表插入行的pg_stats是什么？有可能吗？
可以清楚地看到，统计数据是为单个块计算的，我们可以看到块输入率。有没有一种方法可以查询一些时间尺度的东西，看看是否插入了特定的逻辑表数据(不是查看漏洞，而是查看我们的表统计数据(？
pg_stat_user_tables_n_tup_ins{datname="postgres",relname="_hyper_1_489_chunk",schemaname="_timescaledb_internal",server="timescaledb2:5432"} 92106
我不确定我是否理解你所说的"所有受影响的表"；，但是要在一个查询中获取所有超表，可以使用::regclass强制转换超表名称。来自一些操场数据库的示例，其中包含一些随机超表：
playground=# select hypertable_name, approximate_row_count(hypertable_name::regclass) from timescaledb_information.hypertables ;
┌──────────────────┬───────────────────────┐
│ hypertable_name │ approximate_row_count │
├──────────────────┼───────────────────────┤
│ batteries │ 0 │
│ conditions │ 1000000 │
│ default_forecast │ 0 │
│ forecast │ 1321500 │
│ metrics │ 3162240 │
│ ticks │ 7202 │
└──────────────────┴───────────────────────┘
(6 rows)