PostgreSQL表膨脹終結(jié)者
PostgreSQL數(shù)據(jù)庫表在刪除數(shù)據(jù)后磁盤空間未釋放，該怎么辦？
主流的壓縮表工具有哪些？該如何選擇？
1、從空間未釋放說起
近期生產(chǎn)環(huán)境出現(xiàn)一張表占用size已達(dá)2T,且會(huì)定期刪除記錄，但是，空間一直未釋放，是何原因？
原因就在于vacuum，而vacuum怎么存儲(chǔ)，清理數(shù)據(jù)的可參考官方文檔進(jìn)行查看。https://www.postgresql.org/docs/current/routine-vacuuming.html
出現(xiàn)表一直膨脹，該如何處理？開源社區(qū)的魅力就在于很多大神會(huì)提供很多工具來解決對(duì)應(yīng)的問題，而本問題則有2種主要的工具：pg_repack和pgcompacttable
2. 工具對(duì)比
2.1 pg_repack
pg_repack的處理方式是創(chuàng)建一張新表，再將歷史數(shù)據(jù)從原表中拷貝一份到新表。在拷貝過程中為了避免表被鎖定，會(huì)創(chuàng)建了一個(gè)額外的日志表來記錄原表的改動(dòng)，并添加了一個(gè)涉及INSERT、UPDATE、DELETE操作的觸發(fā)器將變更記錄同步到日志表。當(dāng)原始表中的數(shù)據(jù)全部導(dǎo)入到新表中，索引重建完畢以及日志表的改動(dòng)全部完成后，pg_repack會(huì)用新表替換舊表，并將原舊表Drop掉。此工具過程簡單且靠譜，單需要額外的磁盤空間來報(bào)錯(cuò)臨時(shí)創(chuàng)建的中間表。
2.2 pgcompacttable
pgcompacttable利用了PostgreSQL的一個(gè)有趣特性：在執(zhí)行INSERT和UPDATE操作時(shí)，會(huì)將所有新版本的行移到表最開始的可用空間。此為pgcompacttable工具的關(guān)鍵，因?yàn)槿绻麖哪┒朔聪蜷_始更新所有行，最終所有可用空間被這些行填充，并將表尾部的空間全部釋放以便讓定期vacuum進(jìn)行truncate。這樣一來，pgcompacttable通過批量更新和vacuum強(qiáng)制移動(dòng)，最終整個(gè)表被重新整理，達(dá)到壓縮的效果。此工具對(duì)磁盤空間要求低，且性能影響可控。
2.3 對(duì)比
為了便于大家選擇工具，簡單做了一個(gè)對(duì)比說明供參考。
| pg_repack | pgcompacttable | |
| 是否需要保證性能 | 否 | 是 |
| 是否移動(dòng)表/索引 | 是 | 否 |
| 是否有足夠空間 | 是 | 否 |
| 壓縮速率是否高 | 是 | 否 |
小結(jié)：因很多場(chǎng)景下磁盤空間有限，因而經(jīng)常選擇使用pgcompacttable較多，下面就記錄一下pgcompacttable的安裝及使用。
3. pgcompacttable部署及使用實(shí)例
3.1 添加pgstattuple
pgcompacttable工具使用過程中需要依賴pgstattuple，因此需先添加pgstattuple。如果是源碼安裝的postgresql,則源碼里包含了postgresql-contrib，因此，進(jìn)行編譯及安裝即可。
yum install perl-Time-HiRes perl-DBI perl-DBD-Pg -ycd contrib/makemake install
編譯完成后會(huì)產(chǎn)生幾個(gè)文件
lib/pgstattuple.so
share/extension/pgstattuple*
之后在所需要使用的數(shù)據(jù)庫里添加pgstattuple
psql -d testdbtestdb=# create extension if not exists pgstattuple;CREATE EXTENSION
3.2 部署pgcompacttable
下載依賴及安裝包后即可使用
yum install perl-Time-HiRes perl-DBI perl-DBD-Pg -ysu - postgresgit clone https://github.com/dataegret/pgcompacttable.git
3.3 pgcompacttable使用
pgcompacttable可以對(duì)database級(jí)別、schema級(jí)別、table級(jí)別進(jìn)行壓縮
./pgcompacttable -h localhost -U postgres -d testdb./pgcompacttable -h localhost -U postgres -d testdb -n public./pgcompacttable -h localhost -U postgres -d testdb -n public -t test_table1
2. mysql8.0新增用戶及加密規(guī)則修改的那些事
3. 比hive快10倍的大數(shù)據(jù)查詢利器-- presto
4. 監(jiān)控利器出鞘：Prometheus+Grafana監(jiān)控MySQL、Redis數(shù)據(jù)庫
5. PostgreSQL主從復(fù)制--物理復(fù)制
6. MySQL傳統(tǒng)點(diǎn)位復(fù)制在線轉(zhuǎn)為GTID模式復(fù)制
