# Postgresql Tuning

Tuple
http://liuyangming.tech/02-2019/PostgreSQL-Tuple.html

pg_type
https://ithelp.ithome.com.tw/articles/10218972

postgresql 數值型態研究
https://ithelp.ithome.com.tw/articles/10220426
postgresql 字元型態研究
https://ithelp.ithome.com.tw/articles/10219925
postgresql Enum
https://ithelp.ithome.com.tw/articles/10224450
postgresql UUID
https://ithelp.ithome.com.tw/articles/10225719

I/O
http://liuyangming.tech/12-2018/Tuning_Of_PG.html
https://severalnines.com/blog/tuning-io-operations-postgresql

work_mem Explain
https://www.enterprisedb.com/postgres-tutorials/how-tune-postgresql-memory

關於 表空間無法釋放的方法 (pg_reorg)
https://blog.csdn.net/gjc592/article/details/115713781?spm=1001.2101.3001.6650.3&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-3-115713781-blog-45244953.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-3-115713781-blog-45244953.pc_relevant_default&utm_relevant_index=6

1. DB會自動下 VACUUM

VACUUM analyse
歸0 n_dead_tup

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('public.message_mapping_announcement');

SELECT * FROM pgstatindex('public.message_mapping_announcement_pk');

VACUUM full lottery_settled_finish;

-- 實作VACUUM analyse
-- 可以清出一點tuple空間free_space 但效果沒有很好 (SELECT * FROM pgstattuple('public.lottery_settled_finish');)
-- 歸0 n_dead_tup (select * from pg_stat_all_tables)

-- VACUUM full lottery_settled_finish;
-- 可以清出大多tuple空間free_space  (SELECT * FROM pgstattuple('public.lottery_settled_finish');)

Explain prettier
https://explain.dalibo.com/

產收假資料
mockaroo.com

1. partition 探討 (1 小時研究)(2小時文章)
2. pg_stat_statements
3. trigger 影響速度(check, if else, trigger 2層以上)
4. tuple分配 (1 小時研究)(3小時文章)
5. db lock 各種情境!?
- ON CONFLICT DO NOTHING
- row lock
- table lock-

6. master slave
6-1. master slave衝突的復原
7. pgagent schedule
8. tree的分類效能
9. pg_activity
10. pg_cancel_backend、pg_terminate_backend、各種lock 砍process 的影響
11. 跨DB使用fdw
12. throughout put 分析
13. view 探討
14. Mview探討
15. connection pool探討(微服務、begin commit rollback)
16. pg_dump
17. explain, explain analyze的實戰
18. vacuum 的研究
19. 時間統一的作法(多時區的延伸)
20. function query在pg_stat_statements看的到嗎? 如何定義transaction
21. order,limit, offset query效能影響
22. 效能調整組合技(pg_user, pg_activity, pg_stat_statements, pg_lock ?)
23. foriegn key是好的嗎? 效能影響
24. in, not in, select (n*id), having, exists, case then, like, =any
25. 設計原則(參考)
26. WAL 文件的調整 效能影響
27. 效能調整的(https://www.readfog.com/a/1646533655193030656 )
28. aggregation 相關函數(效能)
29. DB 升級的研究(直接升級、備份+匯入)
30. JSON, JSONB 效能、text、
31. pg_repack(vacuum full 比較)

pg_upgrade
https://gist.github.com/johand/1224125b1b55629346855d9d21b99a24