# PostgreSQL Conlifcts 問題處理

## 已知
1. 系統尖峰時端是禮拜一到五的 09:00AM ~ 06:00 PM
2. 只要 autovacuum 碰上 standby 的 long running query 就有可能發生 conflicts 以及 replication lag
3. 月初的時候 table 總 row 數少，因此可能頻繁觸發 autovacuum
4. 前端某版本會做大量 query 造成 db 繁忙，現在應該已經修正

## 目標
1. 在非尖峰時間做 vacuum or autovacuum，避免尖峰時段 autovacuum 造成 conflicts
2. 減少 long running query ，避免造成 conflicts

## 目標1: 調整 autovacuum 避免造成 conflicts
接下來會用幾種手段來嘗試將 autovacuum 發生的時間跟尖峰時段錯開

### 手段 1: 調高 `autovacuum_vacuum_threshold` 到 `200,000`
由於月初時 table 的總 row 數少， autovacuum 的 threshold 是 `vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples` 因此很容易因為 number of tuples 少而達到 threshold。

`YuCheng` 建議可以將 `autovacuum_vacuum_threshold` 調到 `200,000` ，月底時，這些根據月份 renew 的 table 總 row 數最多可能為 `160,000,000 - 250,000,000` 之間，這時 `200,000` 跟方程式中的另一項相比很小，就幾乎沒有影響了。因此調整這個參數到 `200,000` ，可以讓 table 在月初時減少 autovacuum ，而不太會影響到月底時的 autovacuum。

### 手段 2: 調整 update 的邏輯
`YuCheng` 表示可以在 update 時，只 update 數值不同的部分，如此可以大量減少 update 的頻率，借而降低 dead tuples 的數量，因此不會頻繁 vacuum

### 手段 3: 調整參數避免尖峰時段 autovacuum
查了一下 `pg_stat_user_tables` 之後發現，下面幾張 table 會在 09:00AM-10:00AM 之間因為頻繁的 update 而造成 autovacuum。

如果可以的話，最好在 09:00AM 之前就先 vacuum 過，並且讓這些 table 不要在尖峰時段中做 autovacuum。

以月底的狀況來看，
|table|total rows|autovacuum per day|dead tuples number triggered autovacuum|
|---|---|---|---|
|estimated_volume_1m_202305|10,000,000|2|2,000,000|
|stock_kbar_1m_202305|4,000,000|0.2|800,000|
|ti_202305|100,000,000|0.5|20,000,000|
|bs_indicator_1m_202305|5,000,000|2|1,000,000|
|ohlcv_1m_202305|1,600,000|0.5|300,000|
|kbar_1m_202305|3,000,000|0.2|600,000|
|trade_info_1d_v2|260,000|0.2||
|options_info_1m_202305|800,000|0.5|170,000|

可以發現這些 table 中只有 `estimated_volume_1m_202305`，`bs_indicator_1m_202305` 會一天超過兩次 autovacuum (要加上 kbar_1d_) 屬於 update 頻率較高的 table

有幾種做法:
1. 在每天凌晨時用 cronjob 對這些 table 做 `scheduled` vacuum，然後對於 `update rows/total rows` ratio 較高的 table，提高 `autovacuum_vacuum_threshold` 或是直接關掉 autovacuum (因為這些 table total rows 都不多，一天做一次 vacuum 是 ok 的)
2. 要減少 maintainance effort 的話，可以只做 `scheduled` vacuum (但要調整 kbar_1d 的 autovacuum 參數因為 autovacuum 頻率太高了)
3. 如果目前的 conflicts 數量是可以接受的，也可以就不動作

### 設定 scheduled vacuum
1. 安裝 pg_cron 然後在 psql 中做設定
- 待研究
3. 在 ubuntu 上使用 crontab 設定
- 待研究

# 指標
1. 將 conflicts 數目降到 0
2. 盡量減少 maintainance effort

## 目標2: 減少 long running queries
目前有可以用 `pg_stat_statments` 來查看跑很久的 queries，可以考慮也在 prometheus exporter 中加入相關 metrcis 來監控。

根據 `pg_stat_statments` 發現 `trade_info` 相關的 queries 會比較慢，可以考慮先嘗試優化這項 query。