Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0c17f81

Browse files
Updated table stats backend, modified table_size_detailed query
1 parentf636efb commit0c17f81

File tree

2 files changed

+80
-59
lines changed

2 files changed

+80
-59
lines changed

‎config/pgwatch-prometheus/metrics.yml‎

Lines changed: 22 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -285,7 +285,7 @@ metrics:
285285
from
286286
pg_locks
287287
where
288-
pid!= pg_backend_pid()
288+
pid<> pg_backend_pid()
289289
and database = (select oid from pg_database where datname = current_database())
290290
limit 5000
291291
)
@@ -368,7 +368,7 @@ metrics:
368368
category as tag_category,
369369
vartype as tag_vartype,
370370
case when setting ~ '^-?[0-9]+$' then setting::bigint else null end as numeric_value,
371-
case when source!= 'default' then 0 else 1 end as is_default,
371+
case when source<> 'default' then 0 else 1 end as is_default,
372372
1 as configured
373373
from pg_settings
374374
gauges:
@@ -390,7 +390,7 @@ metrics:
390390
from pg_class c
391391
join pg_namespace n on n.oid = c.relnamespace
392392
where relkind in ('p', 'r')
393-
and relpersistence!= 't'
393+
and relpersistence<> 't'
394394
and not n.nspname like any (array[E'pg\\_%', 'information_schema', E'\\_timescaledb%'])
395395
and not exists(select * from pg_inherits where inhrelid = c.oid)
396396
and exists(select * from pg_inherits where inhparent = c.oid)
@@ -414,7 +414,7 @@ metrics:
414414
pg_table_size(relid) as table_size_b,
415415
abs(greatest(ceil(log((pg_table_size(relid) + 1) / 10 ^ 6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,..
416416
pg_total_relation_size(relid) as total_relation_size_b,
417-
case when reltoastrelid!= 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b,
417+
case when reltoastrelid<> 0 then pg_total_relation_size(reltoastrelid) else 0::int8 end as toast_size_b,
418418
(extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum,
419419
(extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze,
420420
case when 'autovacuum_enabled=off' = ANY (c.reloptions) then 1 else 0 end as no_autovacuum,
@@ -432,14 +432,14 @@ metrics:
432432
autovacuum_count,
433433
analyze_count,
434434
autoanalyze_count,
435-
case when c.relkind!= 'p' then age(c.relfrozenxid) else 0 end as tx_freeze_age
435+
case when c.relkind<> 'p' then age(c.relfrozenxid) else 0 end as tx_freeze_age
436436
from pg_stat_user_tables ut
437437
join
438438
pg_class c on c.oid = ut.relid
439439
where
440440
-- leaving out fully locked tables as pg_relation_size also wants a lock and would wait
441441
not exists(select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock')
442-
and c.relpersistence!= 't' -- and temp tables
442+
and c.relpersistence<> 't' -- and temp tables
443443
)
444444
445445
select /* pgwatch_generated */
@@ -526,7 +526,7 @@ metrics:
526526
from pg_class c
527527
join pg_namespace n on n.oid = c.relnamespace
528528
where relkind in ('p', 'r')
529-
and relpersistence!= 't'
529+
and relpersistence<> 't'
530530
and not n.nspname like any (array[E'pg\\_%', 'information_schema', E'\\_timescaledb%'])
531531
and not exists(select * from pg_inherits where inhrelid = c.oid)
532532
and exists(select * from pg_inherits where inhparent = c.oid)
@@ -550,7 +550,7 @@ metrics:
550550
pg_table_size(relid) as table_size_b,
551551
abs(greatest(ceil(log((pg_table_size(relid) + 1) / 10 ^ 6)), 0))::text as tag_table_size_cardinality_mb, -- i.e. 0=<1MB, 1=<10MB, 2=<100MB,..
552552
pg_total_relation_size(relid) as total_relation_size_b,
553-
case when c.reltoastrelid!= 0 then pg_total_relation_size(c.reltoastrelid) else 0::int8 end as toast_size_b,
553+
case when c.reltoastrelid<> 0 then pg_total_relation_size(c.reltoastrelid) else 0::int8 end as toast_size_b,
554554
(extract(epoch from now() - greatest(last_vacuum, last_autovacuum)))::int8 as seconds_since_last_vacuum,
555555
(extract(epoch from now() - greatest(last_analyze, last_autoanalyze)))::int8 as seconds_since_last_analyze,
556556
case when 'autovacuum_enabled=off' = ANY (c.reloptions) then 1 else 0 end as no_autovacuum,
@@ -568,7 +568,7 @@ metrics:
568568
autovacuum_count,
569569
analyze_count,
570570
autoanalyze_count,
571-
case when c.relkind!= 'p' then age(c.relfrozenxid) else 0 end as tx_freeze_age,
571+
case when c.relkind<> 'p' then age(c.relfrozenxid) else 0 end as tx_freeze_age,
572572
extract(epoch from now() - last_seq_scan)::int8 as last_seq_scan_s
573573
from pg_stat_user_tables ut
574574
join pg_class c on c.oid = ut.relid
@@ -578,7 +578,7 @@ metrics:
578578
where
579579
-- leaving out fully locked tables as pg_relation_size also wants a lock and would wait
580580
not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock')
581-
and c.relpersistence!= 't' -- and temp tables
581+
and c.relpersistence<> 't' -- and temp tables
582582
order by case when c.relkind = 'p' then 1e9::int else coalesce(c.relpages, 0) + coalesce(t.relpages, 0) + coalesce(tir.relpages, 0) end desc
583583
limit 1500 /* NB! When changing the bottom final LIMIT also adjust this limit. Should be at least 5x bigger as approx sizes depend a lot on vacuum frequency.
584584
The general idea is to reduce filesystem "stat"-ing on tables that won't make it to final output anyways based on approximate size */
@@ -1328,40 +1328,24 @@ metrics:
13281328
current_database() as tag_datname,
13291329
n.nspname as tag_schema,
13301330
c.relname as tag_table_name,
1331-
n.nspname || '.' || c.relname as tag_table_full_name,
13321331
c.oid as table_oid,
13331332
c.reltoastrelid,
13341333
-- Main table size components (bytes)
13351334
pg_relation_size(c.oid, 'main') as table_main_size_b,
13361335
pg_relation_size(c.oid, 'fsm') as table_fsm_size_b,
13371336
pg_relation_size(c.oid, 'vm') as table_vm_size_b,
13381337
pg_indexes_size(c.oid) as table_indexes_size_b,
1339-
-- TOAST table components (if exists)
1340-
case
1341-
when c.reltoastrelid != 0 then pg_relation_size(c.reltoastrelid, 'main')
1342-
else 0::int8
1343-
end as toast_main_size_b,
1344-
case
1345-
when c.reltoastrelid != 0 then pg_relation_size(c.reltoastrelid, 'fsm')
1346-
else 0::int8
1347-
end as toast_fsm_size_b,
1348-
case
1349-
when c.reltoastrelid != 0 then pg_relation_size(c.reltoastrelid, 'vm')
1350-
else 0::int8
1351-
end as toast_vm_size_b,
1352-
case
1353-
when c.reltoastrelid != 0 then pg_indexes_size(c.reltoastrelid)
1354-
else 0::int8
1355-
end as toast_indexes_size_b,
1356-
-- Calculated totals for reference
1357-
pg_table_size(c.oid) as table_total_size_b,
1358-
pg_total_relation_size(c.oid) as relation_total_size_b,
1359-
-- Size cardinality for grouping (logarithmic scale)
1338+
pg_relation_size(c.reltoastrelid, 'main') as toast_main_size_b,
1339+
pg_relation_size(c.reltoastrelid, 'fsm') as toast_fsm_size_b,
1340+
pg_relation_size(c.reltoastrelid, 'vm') as toast_vm_size_b,
1341+
pg_indexes_size(c.reltoastrelid) as toast_indexes_size_b,
1342+
pg_total_relation_size(c.reltoastrelid) as toast_total_size_b,
1343+
pg_total_relation_size(c.oid) as total_relation_size_b,
13601344
abs(greatest(ceil(log((pg_total_relation_size(c.oid) + 1) / 10 ^ 6)), 0))::text as tag_size_cardinality_mb
13611345
from pg_class c
13621346
join pg_namespace n on n.oid = c.relnamespace
1363-
where c.relkind in ('r', 'p') -- regular tables andpartitioned tables
1364-
and n.nspname not in ('information_schema', 'pg_catalog', 'pg_toast')
1347+
where c.relkind in ('r', 'p', 'm') -- regular tables,partitioned tables, materialized views
1348+
and n.nspname not in ('information_schema', 'pg_toast')
13651349
and not exists (
13661350
select 1 from pg_locks
13671351
where relation = c.oid and mode = 'AccessExclusiveLock'
@@ -1373,7 +1357,6 @@ metrics:
13731357
tag_datname,
13741358
tag_schema,
13751359
tag_table_name,
1376-
tag_table_full_name,
13771360
tag_size_cardinality_mb,
13781361
table_main_size_b,
13791362
table_fsm_size_b,
@@ -1383,16 +1366,10 @@ metrics:
13831366
toast_fsm_size_b,
13841367
toast_vm_size_b,
13851368
toast_indexes_size_b,
1386-
table_total_size_b,
1387-
relation_total_size_b,
1388-
-- Additional calculated metrics
1389-
(table_main_size_b + toast_main_size_b) as actual_data_size_b,
1390-
(table_fsm_size_b + table_vm_size_b + toast_fsm_size_b + toast_vm_size_b) as metadata_size_b,
1391-
(table_indexes_size_b + toast_indexes_size_b) as total_indexes_size_b,
1369+
total_relation_size_b,
13921370
(toast_main_size_b + toast_fsm_size_b + toast_vm_size_b + toast_indexes_size_b) as total_toast_size_b
13931371
from table_sizes
1394-
where table_main_size_b > 0 -- only include tables with actual data
1395-
order by relation_total_size_b desc
1372+
where total_relation_size_b > 0
13961373
gauges:
13971374
-table_main_size_b
13981375
-table_fsm_size_b
@@ -1402,13 +1379,12 @@ metrics:
14021379
-toast_fsm_size_b
14031380
-toast_vm_size_b
14041381
-toast_indexes_size_b
1405-
-table_total_size_b
1406-
-relation_total_size_b
1382+
-total_relation_size_b
14071383
-actual_data_size_b
14081384
-metadata_size_b
14091385
-total_indexes_size_b
14101386
-total_toast_size_b
1411-
statement_timeout_seconds:300
1387+
statement_timeout_seconds:15
14121388
pg_invalid_indexes:
14131389
description:>
14141390
This metric identifies invalid indexes in the database.

‎flask-backend/app.py‎

Lines changed: 58 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -484,10 +484,11 @@ def get_btree_bloat_csv():
484484
logger.error(f"Error processing btree bloat request:{e}")
485485
returnjsonify({"error":str(e)}),500
486486

487-
@app.route('/table_bloat/csv',methods=['GET'])
488-
defget_table_bloat_csv():
487+
@app.route('/table_info/csv',methods=['GET'])
488+
defget_table_info_csv():
489489
"""
490-
Get the most recent pg_table_bloat metrics as a CSV table.
490+
Get comprehensive table information including bloat metrics and detailed size information as a CSV table.
491+
Combines pg_table_bloat and table_size_detailed metrics for complete table analysis.
491492
"""
492493
try:
493494
# Get query parameters
@@ -513,14 +514,27 @@ def get_table_bloat_csv():
513514
filter_str='{'+','.join(filters)+'}'iffilterselse''
514515

515516
# Metrics to fetch with last_over_time to get only the most recent value
517+
# Include both bloat metrics and detailed size metrics
516518
metric_queries= [
519+
# Bloat metrics
517520
f'last_over_time(pgwatch_pg_table_bloat_real_size_mib{filter_str}[1d])',
518521
f'last_over_time(pgwatch_pg_table_bloat_extra_size{filter_str}[1d])',
519522
f'last_over_time(pgwatch_pg_table_bloat_extra_pct{filter_str}[1d])',
520523
f'last_over_time(pgwatch_pg_table_bloat_fillfactor{filter_str}[1d])',
521524
f'last_over_time(pgwatch_pg_table_bloat_bloat_size{filter_str}[1d])',
522525
f'last_over_time(pgwatch_pg_table_bloat_bloat_pct{filter_str}[1d])',
523526
f'last_over_time(pgwatch_pg_table_bloat_is_na{filter_str}[1d])',
527+
# Detailed size metrics
528+
f'last_over_time(pgwatch_table_size_detailed_table_main_size_b{filter_str}[1d])',
529+
f'last_over_time(pgwatch_table_size_detailed_table_fsm_size_b{filter_str}[1d])',
530+
f'last_over_time(pgwatch_table_size_detailed_table_vm_size_b{filter_str}[1d])',
531+
f'last_over_time(pgwatch_table_size_detailed_table_indexes_size_b{filter_str}[1d])',
532+
f'last_over_time(pgwatch_table_size_detailed_toast_main_size_b{filter_str}[1d])',
533+
f'last_over_time(pgwatch_table_size_detailed_toast_fsm_size_b{filter_str}[1d])',
534+
f'last_over_time(pgwatch_table_size_detailed_toast_vm_size_b{filter_str}[1d])',
535+
f'last_over_time(pgwatch_table_size_detailed_toast_indexes_size_b{filter_str}[1d])',
536+
f'last_over_time(pgwatch_table_size_detailed_total_relation_size_b{filter_str}[1d])',
537+
f'last_over_time(pgwatch_table_size_detailed_total_toast_size_b{filter_str}[1d])',
524538
]
525539

526540
prom=get_prometheus_client()
@@ -542,20 +556,45 @@ def get_table_bloat_csv():
542556
'schemaname':metric_labels.get('schemaname',''),
543557
'tblname':metric_labels.get('tblname',''),
544558
}
559+
value=float(entry['value'][1])
560+
561+
# Bloat metrics
545562
if'real_size_mib'inquery:
546-
metric_results[key]['real_size_mib']=float(entry['value'][1])
563+
metric_results[key]['real_size_mib']=value
547564
elif'extra_size'inqueryand'extra_pct'notinquery:
548-
metric_results[key]['extra_size']=float(entry['value'][1])
565+
metric_results[key]['extra_size']=value
549566
elif'extra_pct'inquery:
550-
metric_results[key]['extra_pct']=float(entry['value'][1])
567+
metric_results[key]['extra_pct']=value
551568
elif'fillfactor'inquery:
552-
metric_results[key]['fillfactor']=float(entry['value'][1])
569+
metric_results[key]['fillfactor']=value
553570
elif'bloat_size'inquery:
554-
metric_results[key]['bloat_size']=float(entry['value'][1])
571+
metric_results[key]['bloat_size']=value
555572
elif'bloat_pct'inquery:
556-
metric_results[key]['bloat_pct']=float(entry['value'][1])
573+
metric_results[key]['bloat_pct']=value
557574
elif'is_na'inquery:
558-
metric_results[key]['is_na']=int(float(entry['value'][1]))
575+
metric_results[key]['is_na']=int(value)
576+
577+
# Size metrics (convert bytes to MiB for consistency)
578+
elif'table_main_size_b'inquery:
579+
metric_results[key]['table_main_size_mib']=value/ (1024*1024)
580+
elif'table_fsm_size_b'inquery:
581+
metric_results[key]['table_fsm_size_mib']=value/ (1024*1024)
582+
elif'table_vm_size_b'inquery:
583+
metric_results[key]['table_vm_size_mib']=value/ (1024*1024)
584+
elif'table_indexes_size_b'inquery:
585+
metric_results[key]['table_indexes_size_mib']=value/ (1024*1024)
586+
elif'toast_main_size_b'inquery:
587+
metric_results[key]['toast_main_size_mib']=value/ (1024*1024)
588+
elif'toast_fsm_size_b'inquery:
589+
metric_results[key]['toast_fsm_size_mib']=value/ (1024*1024)
590+
elif'toast_vm_size_b'inquery:
591+
metric_results[key]['toast_vm_size_mib']=value/ (1024*1024)
592+
elif'toast_indexes_size_b'inquery:
593+
metric_results[key]['toast_indexes_size_mib']=value/ (1024*1024)
594+
elif'total_relation_size_b'inquery:
595+
metric_results[key]['total_relation_size_mib']=value/ (1024*1024)
596+
elif'total_toast_size_b'inquery:
597+
metric_results[key]['total_toast_size_mib']=value/ (1024*1024)
559598
exceptExceptionase:
560599
logger.warning(f"Failed to query:{query}, error:{e}")
561600
continue
@@ -564,8 +603,14 @@ def get_table_bloat_csv():
564603
output=io.StringIO()
565604
fieldnames= [
566605
'database','schemaname','tblname',
606+
# Bloat metrics
567607
'real_size_mib','extra_size','extra_pct','fillfactor',
568-
'bloat_size','bloat_pct','is_na'
608+
'bloat_size','bloat_pct','is_na',
609+
# Size metrics (all in MiB)
610+
'table_main_size_mib','table_fsm_size_mib','table_vm_size_mib',
611+
'table_indexes_size_mib','toast_main_size_mib','toast_fsm_size_mib',
612+
'toast_vm_size_mib','toast_indexes_size_mib','total_relation_size_mib',
613+
'total_toast_size_mib'
569614
]
570615
writer=csv.DictWriter(output,fieldnames=fieldnames)
571616
writer.writeheader()
@@ -578,11 +623,11 @@ def get_table_bloat_csv():
578623
# Create response
579624
response=make_response(csv_content)
580625
response.headers['Content-Type']='text/csv'
581-
response.headers['Content-Disposition']='attachment; filename=table_bloat_latest.csv'
626+
response.headers['Content-Disposition']='attachment; filename=table_info_latest.csv'
582627
returnresponse
583628

584629
exceptExceptionase:
585-
logger.error(f"Error processing tablebloat request:{e}")
630+
logger.error(f"Error processing tableinfo request:{e}")
586631
returnjsonify({"error":str(e)}),500
587632

588633
if__name__=='__main__':

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp