如何获取Timescale压缩表的pg_stat_user_tables中n_tup_ins统计数据？

Great question! The issue you're seeing is because Timescale hypertables store their data across multiple physical chunks, and pg_stat_user_tables only tracks statistics for individual physical tables (including those chunks) — not the logical hypertable itself. Here's how you can resolve this:

1. Are there pg_stats views or methods to get insert row statistics for compressed tables? #

Absolutely! Timescale provides system views that link chunks back to their parent hypertable, allowing you to aggregate statistics across all chunks belonging to a hypertable. There's also a dedicated timescaledb_information.pg_stat_hypertable view for hypertable-specific metrics, though it doesn't cover every field from pg_stat_user_tables.

2. How to implement this? #

To retain the exact set of metrics you're already using (matching your original pg_stat_user_tables query), use an aggregated SQL query that rolls up chunk statistics into logical hypertable metrics. Here's a complete, ready-to-use query:

SELECT
  current_database() AS datname,
  ht.schema_name AS schemaname,
  ht.table_name AS relname,
  sum(pst.seq_scan) AS seq_scan,
  sum(pst.seq_tup_read) AS seq_tup_read,
  sum(pst.idx_scan) AS idx_scan,
  sum(pst.idx_tup_fetch) AS idx_tup_fetch,
  sum(pst.n_tup_ins) AS n_tup_ins,
  sum(pst.n_tup_upd) AS n_tup_upd,
  sum(pst.n_tup_del) AS n_tup_del,
  sum(pst.n_tup_hot_upd) AS n_tup_hot_upd,
  sum(pst.n_live_tup) AS n_live_tup,
  sum(pst.n_dead_tup) AS n_dead_tup,
  sum(pst.n_mod_since_analyze) AS n_mod_since_analyze,
  max(COALESCE(pst.last_vacuum, '1970-01-01Z')) AS last_vacuum,
  max(COALESCE(pst.last_autovacuum, '1970-01-01Z')) AS last_autovacuum,
  max(COALESCE(pst.last_analyze, '1970-01-01Z')) AS last_analyze,
  max(COALESCE(pst.last_autoanalyze, '1970-01-01Z')) AS last_autoanalyze,
  sum(pst.vacuum_count) AS vacuum_count,
  sum(pst.autovacuum_count) AS autovacuum_count,
  sum(pst.analyze_count) AS analyze_count,
  sum(pst.autoanalyze_count) AS autoanalyze_count
FROM pg_stat_user_tables pst
JOIN timescaledb_information.chunks c 
  ON pst.relname = c.chunk_name AND pst.schemaname = c.chunk_schema
JOIN timescaledb_information.hypertables ht 
  ON c.hypertable_schema = ht.schema_name AND c.hypertable_name = ht.table_name
GROUP BY datname, schemaname, relname

UNION ALL

-- Keep statistics for regular non-hyper tables
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
WHERE relname NOT IN (SELECT chunk_name FROM timescaledb_information.chunks);

What this query does:

• Aggregates metrics like n_tup_ins (insert counts) across all chunks belonging to each hypertable, using the logical hypertable name in the relname field.
• For time-based fields (e.g., last_vacuum), it takes the most recent value across all chunks of the hypertable.
• Uses UNION ALL to preserve statistics for your regular non-hyper tables, so you don't lose existing metrics.

Update your Postgres Exporter setup:

Replace your original SQL query with this aggregated version. Your existing metrics YAML configuration can stay identical — all field names match your current setup, so the exporter will generate the same metrics but now with logical hypertable names instead of chunk names.

Alternative: Use Timescale's dedicated hypertable stats view

If you don't need every field from pg_stat_user_tables, you can use timescaledb_information.pg_stat_hypertable directly. This view maintains pre-aggregated hypertable metrics and requires less manual setup:

SELECT
  current_database() AS datname,
  schema_name AS schemaname,
  table_name AS relname,
  insert_count AS n_tup_ins,
  update_count AS n_tup_upd,
  delete_count AS n_tup_del,
  hot_update_count AS n_tup_hot_upd,
  live_rows AS n_live_tup,
  dead_rows AS n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM timescaledb_information.pg_stat_hypertable;

Note this view doesn't include scan-related metrics like seq_scan or idx_scan, so use it only if those metrics aren't critical for your use case.

内容的提问来源于stack exchange，提问作者Eljah