Database Services for Telecom & Network Operators

Identifies dead tuple accumulation and vacuum lag on high-churn MSISDN subscriber tables and SIM entitlement catalogs.

-- 1. Inspect dead tuples and autovacuum performance on subscriber status tables
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE %subscriber% OR relname LIKE %sim% OR relname LIKE %device%
ORDER BY n_dead_tup DESC LIMIT 5;

-- 2. Inspect active long-running subscriber balance transactions
SELECT pid, now() - xact_start AS xact_duration, state, query
FROM pg_stat_activity
WHERE state != idle AND xact_start IS NOT NULL
ORDER BY xact_duration DESC LIMIT 5;

Tracks real-time insert rates, write amplification, and buffer cache efficiency across active CDR partition chunks.

-- 1. Inspect CDR hypertable chunk insert velocity and tuple insertions
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname LIKE %cdr% OR relname LIKE %call_record%
ORDER BY n_tup_ins DESC LIMIT 5;

-- 2. Inspect background buffer writer performance and dirty page flushes
SELECT buffers_checkpoint, buffers_clean, maxwritten_clean,
       buffers_backend, buffers_alloc
FROM pg_stat_bgwriter;