CREATE EXTENSION IF NOT EXISTS tencentdb_wal_stat;

SELECT extname, extversion FROM pg_extension 
WHERE extname = 'tencentdb_wal_stat';

tencentdb_wal_stat(wal_num integer)
RETURNS SETOF record

-- Summarize WAL generation volume by database (analyzing the latest 50 WAL files)
SELECT database_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size,
       ROUND(SUM(wal_bytes + wal_fpi_bytes) * 100.0 / 
             SUM(SUM(wal_bytes + wal_fpi_bytes)) OVER (), 1) AS pct
FROM tencentdb_wal_stat(50)
GROUP BY database_name
ORDER BY SUM(wal_bytes + wal_fpi_bytes) DESC;

-- Summarize by schema dimension
SELECT database_name, schema_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size
FROM tencentdb_wal_stat(50)
WHERE database_name = 'func_veri'  -- Replace with the target database name
  AND schema_name IS NOT NULL AND schema_name != ''
GROUP BY database_name, schema_name
ORDER BY SUM(wal_bytes + wal_fpi_bytes) DESC;

-- View the top 10 tables that generate the most WAL
SELECT database_name, schema_name, relation_name, relation_kind,
       wal_records,
       pg_size_pretty(wal_bytes::bigint) AS wal_size,
       pg_size_pretty(wal_fpi_bytes::bigint) AS fpi_size,
       pg_size_pretty((wal_bytes + wal_fpi_bytes)::bigint) AS total_size
FROM tencentdb_wal_stat(50)
WHERE relation_kind IN ('table', 'index')
ORDER BY (wal_bytes + wal_fpi_bytes) DESC
LIMIT 10;

-- View WAL distribution by RMGR type
SELECT rmgr_name,
       SUM(wal_records) AS total_records,
       pg_size_pretty(SUM(wal_bytes)::bigint) AS wal_size,
       ROUND(SUM(wal_records) * 100.0 / SUM(SUM(wal_records)) OVER (), 1) AS pct
FROM tencentdb_wal_stat(50)
GROUP BY rmgr_name
ORDER BY SUM(wal_records) DESC;

-- FPI proportion analysis
SELECT 
    pg_size_pretty(SUM(wal_bytes)::bigint) AS wal_data_size,
    pg_size_pretty(SUM(wal_fpi_bytes)::bigint) AS fpi_size,
    pg_size_pretty(SUM(wal_bytes + wal_fpi_bytes)::bigint) AS total_size,
    ROUND(SUM(wal_fpi_bytes) * 100.0 / 
          NULLIF(SUM(wal_bytes + wal_fpi_bytes), 0), 1) AS fpi_pct
FROM tencentdb_wal_stat(50);

Step 1: View the overall WAL generation overview.
    └── tencentdb_wal_stat(50) aggregated by database
         │
Step 2: Locate the target database.
    └── Summarize by schema to locate abnormal schemas.
         │
Step 3: Drill down to the table level.
    └── View the top 10 tables by relation.
         │
Step 4: Analyze the cause.
    ├── A high proportion of Heap type → A large volume of INSERT/UPDATE/DELETE operations
    ├── A high proportion of B-tree type → High index maintenance overhead
    └── A high FPI proportion → Excessive checkpoint frequency
         │
Step 5: Take measures.
    ├── Limit the write rate for abnormal tenants.
    ├── Suspend non-core batch tasks.
    └── Optimize the index policy.

-- Record the WAL generation volume for each database hourly (can be written to a monitoring table)
INSERT INTO wal_monitor_log (check_time, database_name, wal_records, wal_bytes)
SELECT now(), database_name,
       SUM(wal_records), SUM(wal_bytes + wal_fpi_bytes)
FROM tencentdb_wal_stat(10)
GROUP BY database_name;