PostgreSQL :: Tuning 2026

1.txt

// 1. แต่ละ table มีจำนวน row เท่าไร มี size ของข้อมูลเท่าไร
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

ตัวอย่างผลการทำงาน
"customers"	"20240 MB"

// 2. table นั้น ๆ ที่มัน size ใหญ่ ๆ มีรายละเอียดอย่างไร
SELECT pg_size_pretty(pg_total_relation_size('customers')) AS total_size,
       pg_size_pretty(pg_relation_size('customers')) AS table_size,
       pg_size_pretty(pg_indexes_size('customers')) AS index_size;
 
 ตัวอย่างผลการทำงาน
 Total size, Table size, Index size
"20240 MB"	"8940 MB"	"11300 MB"

2.txt

SELECT
	 indexrelname AS index_name,
	 pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
	 idx_scan AS index_scan
FROM pg_stat_user_indexes
WHERE relname = 'customers'
ORDER BY pg_relation_size(indexrelid) DESC;


ตัวอย่างผลการทำงาน
    Index name,            Index size, Index scan
"idx_customers_email"	      "8230 MB"	      0
"customers_pkey"	          "2380 MB"	    100000
"idx_customers_city"	      "690 MB"	     564

3.txt

# Tune Autovacuum for Proactive Cleanup
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;  -- Reduce dead tuples before vacuuming
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;  -- Vacuum earlier
ALTER SYSTEM SET autovacuum_naptime = 10;  -- Run autovacuum more frequently

# Use REINDEX to Rebuild Indexes
REINDEX INDEX CONCURRENTLY your_index_name;