# 時間序列資料管理 TimescaleDB (2) 效能比較

## 測試腳本
基於[上一篇](https://hackmd.io/@moment89/timescaledb-utility)建立的測試環境 (PostgreSQL 17.4 + pgBouncer + pg_partman + TimescaleDB)，我建立了一份簡單的[測試腳本](https://github.com/michelle0809/DatabasePerformanceTest)，測試 PostgreSQL Normal Table / Partitioned Table / TimeScaleDB Hypertable 的 `INSERT` / `UPDATE` / `SELECT` TPS。

### 事項
* 均透過 pgBouncer (6432 port) 連線 PostgreSQL test_database 資料庫。
* 環境已安裝 `pg_partman`、`timescaledb` Extension，並已透過 `create_table.sql` 檔案內的語法建立 Normal Table / Partitioned Table / TimeScaleDB Hypertable 資料表。
* 測試會開 10 個 Thread 個別連線資料庫，有 10 個連線同時執行 `INSERT` 或 `UPDATE` 操作，最終計算 TPS。
* Hypertable 已壓縮的表仍可 `INSERT` / `UPDATE` 資料，故也測試壓縮對 `INSERT` / `UPDATE` TPS 的影響。
* 每次 `INSERT` / `UPDATE` 都是 1 筆資料操作。

### 測試項目
* Normal Table
* `INSERT` 7天前 ~ 當日共 1,000,000 筆資料
* `UPDATE` 7天前 ~ 當日共 1,000,000 筆資料
* `SELECT` 7天前 ~ 當日期間隨機 1 小時資料共 10,000 次
* Partitioned Table
* `INSERT` 7天前 ~ 當日共 1,000,000 筆資料
* `UPDATE` 7天前 ~ 當日共 1,000,000 筆資料
* `SELECT` 7天前 ~ 當日期間隨機 1 小時資料共 10,000 次
* Hypertable
* `INSERT` 7天前 ~ 當日共 1,000,000 筆資料
* `UPDATE` 7天前 ~ 當日共 1,000,000 筆資料
* `SELECT` 7天前 ~ 當日期間隨機 1 小時資料共 10,000 次
* Compressed Hypertable
* `INSERT` 7天前 ~ 3天前共 1,000,000 筆資料
* `UPDATE` 7天前 ~ 3天前共 1,000,000 筆資料
* `SELECT` 7天前 ~ 3天前期間隨機 1 小時資料共 10,000 次

## TPS 測試結果
腳本執行結果如下表。

<table border="1">
<thead>
<tr>
<th>測試類型</th>
<th>操作類型</th>
<th style="text-align: right">交易次數</th>
<th style="text-align: right">平均影響資料數</th>
<th style="text-align: right">執行時間(ms)</th>
<th style="text-align: right">TPS</th>
<th style="text-align: right">與 Normal Table 差異</th>
</tr>
</thead>
<tbody>
<tr>
<td rowspan="3">Normal Table</td>
<td>Insert</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">649,772.00</td>
<td style="text-align: right">1,539.00</td>
<td style="text-align: right">基準值</td>
</tr>
<tr>
<td>Update</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">656,834.26</td>
<td style="text-align: right">1,522.45</td>
<td style="text-align: right">基準值</td>
</tr>
<tr>
<td>Select</td>
<td style="text-align: right">10,000</td>
<td style="text-align: right">5,936.69</td>
<td style="text-align: right">607,044.08</td>
<td style="text-align: right">16.47</td>
<td style="text-align: right">基準值</td>
</tr>
<tr>
<td rowspan="3">Partitioned Table</td>
<td>Insert</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">655,100.30</td>
<td style="text-align: right">1,526.48</td>
<td style="text-align: right; color: red">-0.81%</td>
</tr>
<tr>
<td>Update</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">883,995.75</td>
<td style="text-align: right">1,131.23</td>
<td style="text-align: right; color: red">-25.70%</td>
</tr>
<tr>
<td>Select</td>
<td style="text-align: right">10,000</td>
<td style="text-align: right">5,931.11</td>
<td style="text-align: right">135,430.86</td>
<td style="text-align: right">73.84</td>
<td style="text-align: right; color: green">+348.21%</td>
</tr>
<tr>
<td rowspan="3">Hypertable</td>
<td>Insert</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">706,785.63</td>
<td style="text-align: right">1,414.86</td>
<td style="text-align: right; color: red">-8.07%</td>
</tr>
<tr>
<td>Update</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">959,176.44</td>
<td style="text-align: right">1,042.56</td>
<td style="text-align: right; color: red">-31.52%</td>
</tr>
<tr>
<td>Select</td>
<td style="text-align: right">10,000</td>
<td style="text-align: right">5,939.73</td>
<td style="text-align: right">108,437.72</td>
<td style="text-align: right">92.22</td>
<td style="text-align: right; color: green">+459.82%</td>
</tr>
<tr>
<td rowspan="3">Compress Hypertable</td>
<td>Insert</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">740,068.81</td>
<td style="text-align: right">1,351.23</td>
<td style="text-align: right; color: red">-12.20%</td>
</tr>
<tr>
<td>Update</td>
<td style="text-align: right">1,000,000</td>
<td style="text-align: right">1.00</td>
<td style="text-align: right">1,258,084.59</td>
<td style="text-align: right">794.86</td>
<td style="text-align: right; color: red">-47.80%</td>
</tr>
<tr>
<td>Select</td>
<td style="text-align: right">10,000</td>
<td style="text-align: right">16,297.73</td>
<td style="text-align: right">330,449.19</td>
<td style="text-align: right">30.26</td>
<td style="text-align: right; color: green">+83.68%</td>
</tr>
</tbody>
</table>

>[!Note]
>Compress Hypertable 是基於 Hypertable 再執行壓縮、再對壓縮的時段塞資料、再查詢，故同樣查詢 1 小時資料查出來的資料量較多，TPS 僅供參考。

## 小結
Partitioned Table 相較於 Normal Table，在 `INSERT`、`UPDATE` 時需有額外的開銷去判斷資料在哪個 Partition 中，故 TPS 表現略低是可以理解的。與之相對，可換來倍數提升的查詢效能。

TimescaleDB 的 Hypertable 也是同理，犧牲一些 `INSERT`、`UPDATE` 的表現換來更好的查詢效能，甚至提供壓縮歷史資料功能的同時保有對歷史資料操作的空間。

# Lock Usage

在對 Partitioned Table 做查詢時，即使我的查詢只涉及部分分區，從查詢計劃來看也確實只查詢了某幾個分區，但在 pg_locks 系統表會發現 PostgreSQL 對所有分區都上了 AccessShareLock 來分析要對哪些分區做查詢。
隨著資料表下的分區數量逐漸增長、分區數量過多時容易發生問題，可能會發生 lock 數量超過 shared memory 可保存的 lock 數量，而報錯 Out of shared memory 並提示「You might need to increase max_locks_per_transaction」。(就像[這篇文章](https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/)說明的情況)

舉例來說，從 `EXPLAIN ANALYZE` 來看我的 SQL 在 Partitioned Table 只查詢了一個分區。

查看 pg_locks，可以看到 PostgreSQL 查詢器為了定位要在哪些分區內搜尋資料，會需要把該表下所有 Partitioned 加上輕量的 AccessSharedLock，直到 Transaction 結束才釋放。
<center class="half">
<img src="https://hackmd.io/_uploads/BJtA1jnglx.png" width="300"/>
<img src="https://hackmd.io/_uploads/HyQyeonglg.png" width="300"/>
</center>

我很好奇 TimescaleDB 的 Hypertable 是否會有一樣的行為。
下圖用一樣的時間範圍查詢 Hypertable，同樣 `EXPLAIN ANALYZE` 顯示只查詢了一個 Chunk。

可惜的是一樣會對所有 Chunk 上 AccessSharedLock，連已經壓縮的 Chunk 都會。
<center class="half">
<img src="https://hackmd.io/_uploads/HJw3Wohgel.png" width="300"/>
<img src="https://hackmd.io/_uploads/H1zaZihlgl.png" width="300"/>
</center>

## 小結
過往用 Partitioned Table 機制時，我是把歷史資料 Detach + Attach 搬到另一張 Partitioned Table 存放，來避免主表分區過多，衍伸的 Lock 數量問題。
原本期盼 TimescaleDB 的 Hypertable 壓縮策略可以省了這個麻煩又兼顧能在同一張表查詢歷史資料的功能，還是想得太美好了XD

TimescaleDB 沒有提供類似 Partition Detach、Attach 的功能 (move_chunk 在 2.13 以後的版本被移除了)，故長期還是要考慮搭配一些減少 Chunk 數量的手段，例如
1. 乖乖刪除舊資料。
2. 不能刪除的話，用 merge_chunk 合併。
3. 合併後還是太多的話，自己用 COPY 指令轉存到其他表再壓縮。
4. ...