解決不了問題,就解決提出問題的人

Docker 就是你的實驗室,執行記錄

需要一個乾淨的 PostgreSQL 實驗環境時,可以利用 Docker Playground 來快速產生一個系統空間。除了快速之外,最方便的就是只需要瀏覽器就能操作。

還不會使用 Docker 的朋友也可以先照著打,進去之後就當作 Linux (Debian) 來使用即可。當然會使用 Docker 的朋友可以玩出更多的變化。

一、註冊 Docker Hub 的帳號: https://hub.docker.com/

二、連線到 Docker Hub 的 postgres 專頁: https://hub.docker.com/_/postgres

三、往下拉到「Try in PWD」之處,點下去。

四、使用 docker 的帳號登入。

五、登入後點選「Start」就開始吧!

六、經過一下子的啓動時間,這個畫面就表示環境準備好了,點「CLOSE」開始操作。

七、點選中間 Terminal 的窗格,開始輸入指令。簡單執行過程大概會長得像下圖。

  • 取得目前啓動了哪些容器
docker ps
  • 連線進入 postgres 的那個容器,複製貼上你所看到的 CONTAINER ID。
    稍後如果不小心登出了容器(ctrl-d 按太快),只要再執行這行登入即可。內容都還會存在。
docker exec -it 643527b85470 bash
  • 看到 root 之後表示成功登入該容器了,切換成 postgres 使用者
su - postgres
  • 使用 psql 連線進入資料庫
psql
  • 執行一個指令看看
select version();

八、看一下左上角,你有 4 個小時可以好好地玩!

倒數完不會爆炸請放心 😄

Keep calm and use Postgres


要從安全的來源下載安裝檔唷!

一、https://postgresql.org : 是官方首頁喔。就點 Download 吧!

二、選擇你要安裝的作業系統,點選 Windows

三、Download the installer。Windows 的安裝套件由 EDB 公司提供,所以接下來的下載頁面會導引到 EDB 公司網頁。

四、選擇 Windows x86–64,就會開始下載了。

五、下載好請執行安裝程式(需要管理者權限),檔名一般是長這樣:postgresql-13.1–1-windows-x64.exe 。正常啓動後就點選 Next。

六、PostgreSQL 的系統程式安裝路徑,先不用改。Next。

七、先不要使用 Stack Builder。Next。

八、PostgreSQL 資料的儲存目錄,先不要改。Next。

九、最高管理者的密碼。還不知道是什麼的話,就先打"123456",第一次安裝的人都會忘記,之後可以改。Next。

十、選擇 PostgreSQL 使用的連接埠,預設是 5432。如果不是顯示 5432,自己要記起來。Next。

十一、Locale 用預設的。Next。

十二、要正式開始安裝囉!Next。

十三、真的要開始安裝囉!Next。

十四、安裝中,請稍待。

十五、恭喜完成安裝。Finish。

十六、打開你的開始工具列,搜尋並打開 PostgreSQL 的目錄,點選執行"SQL Shell (psql)"。

十七、輸入連線資訊,先按預設值照著打。Password 就前面安裝時輸入的"123456"。

十八、Hello Postgres,如果出現了類似的結果就完成了!

select 'Hello Postgres', now();

十九、結束休息一下吧!

二十、請開始與 PostgreSQL 同行:https://docs.postgresql.tw/tutorial/the-sql-language/concepts

建議你可以從使用手冊所提供的新手教學開始,慢慢熟悉 PostgreSQL 的資料庫世界。

Keep calm and use Postgres.


大量資料、大量連線、大量運算、資料多樣化

許多人在評估資料庫的能力時,都只停留在資料所佔有的空間有多少。實際上,資料庫並不是只有把資料儲存下來而已,它還有很多問題是在儲存之後需要面對的。標題已經破題了,就是四大問題的分類,除了用於評估資料庫系統設計之外,也必須用這些面向來評估你的資料應用形態。當資料庫系統設計與資料應用形態相符合時,才是最適合的選擇。

而這四大問題當然不會單獨發生,也不會是恆久不變的形態。所以在評估、上線到維護,整個系統的生命週期的過程中,都應該定期檢視資料需求的傾向,以作為資料庫調教的依據。

然而,由於不會有無限的資源,所以在成本考量下,在評估之後,勢必還是得做出某種程度的取捨,而這才是我們需要呈現的專業價值。當然我們都知道錢花得更多會越好,但你也需要知道怎樣才是花在刀口上。

本文不適合財大氣粗的朋友閱讀 😏

本文以 PostgreSQL 資料庫為例,你也可以套用在其他資料庫系統上。

大量資料

大量資料是傳統的評估方式,使用磁碟佔用空間來計算,例如 100 TB 的資料量,或是以資料筆數,像是 100 億筆資料之類的。不過這種喊出來的數字其實沒有意義,因為你需要考量的是硬體成本和資料回應需求的差距。

硬體面向除了很直觀的存取速度之外,其實儲存裝置也很佔機房空間,而儲存年限的設定是資料政策面必須要定義的。

現代的資料庫系統,只是把資料儲存起來的話,都只看硬體上限即可。實際的使用上,簡單來看,記憶體的大小才是關鍵。在一般的條件過濾應用,所參考的索引如果能完整放進記憶體中,那才會符合一般對「可以處理」的想像。

所以,考量點其實應該是你的資料應用每次「讀取」多少資料,來評估資料庫是否可以處理「大量資料」,將會更有意義。

例如一般使用 B-tree 索引,索引比資料大的情況比比皆是。在 PostgreSQL 上你還可以選擇使用 BRIN 索引來調整索引的大小,通常用於資料倉儲方面的需求會很有效果。當然,如何設計分散式資料庫系統來達到水平擴充,也會是你需要思考的課題。

大量連線

大量連線指的是同時間(concurrent)的連線數量。還可以區分為兩個面向,一種是大量的連線,存取不同的資料;另一種則是大量的連線,存取相同的資料。兩者共通的設計上都必須要有 Connection Pool

你需要先測試一下資料庫系統的連線數與效能分數的關連,PostgreSQL 上基本會使用 pgbench 作為標準測試。我的經驗上,PostgreSQL 最佳效率點大約會落在 500~600 個連線數之間。超過這個數目的需求,就是屬於大量連線。而你必須在你的環境上實際實驗過才能確認這個臨界點。

一般大量連線的考量是儘可能服務更多的資料要求,而不是單一查詢的效率。其中 idle 的連線是最不應該出現的,有關 timeout 方面的設定需要細心地評估和溝通。在大量連線的情況下,資源的使用率很重要,必須確保每一個在資料庫裡的連線都真正在存取資料。

存取不同資料的大量連線,增加記憶體的大小是很有幫助的;而存取相同資料的大量連線,例如訂票的需求,則需要應用系統的協助,例如透過快取的設計,避免無謂的資料操作。

大量運算

大量運算一般的標的是資料分析方面的應用,假設上會是運算時間較長的運算,時間單位是以分鐘計的。和大量連線的系統設計可能會矛盾的是,你應該降低最大連線數的設定,讓長時間的運算可以擁有更多資源來處理。

你必須要很基本地理解到,再快的 CPU,一個 CPU 核心,一次也只能做一件事,越多連線數所產生的 Context Switch 只會造成更多無謂的延遲。

除了顯而易見的 CPU 和 Memory 資源高低之外,I/O 問題反而是最先浮現的。由於多數資料是來自於線上交易產生,為了平衡負載,會設計 OLTP 和 OLAP 的資料需求轉換。具體來說,就是把資料抄寫出來做資料分析之用,時常都不只抄寫一份。

在 PostgreSQL 中,有 Stream Replication 和 Logical Replication 可做運用。在這裡不談細節的運作,但原則上,你需要確保每一份抄寫都是有資料處理意義的。例如備份、異質資料源轉換、資料標準化、統計分類等等。

重要提醒,資料庫系統是有運算能力的。

許多優秀的分析軟體提供了資料庫系統所沒有的資料運算方式,所以會有大量的資料匯入匯出在批次執行很正常。但資料庫也不是什麼都沒有,設計上應該儘可能讓資料庫做完資料庫可以做的運算後,再匯出到特定的分析系統之中。由於資料傳輸的成本很高,所以在資料庫內先行運算,儘量降低需要傳輸的資料量,本身就能夠直接加速產生運算的結果。

PostgreSQL 裡提供了各式的彙總運算,請善加利用。

資料多樣化

當企業的業務量提升到穩定發展的層次時,業務樣態逐步增加的話,其資料多樣化的問題自然就會產生。所謂資料多樣化指的通常是資料型別繁多以及異質資料庫系統整合的問題。

資料型別的問題在於針對該資料型別的運算處理是否完備,否則只是儲存起來,無法檢索或相關運算,只算是做了半套。以近來許多人使用的 JSON 型別為例,能有索引支援快速檢索指定路徑內容的話,放在這樣的資料庫裡才算是可以便利地使用。

其他還有像是地理資訊資料、全文檢索資料、時間序列資料等等,甚至你需要專用資料庫系統來處理這些類型的資料。

在資料多樣化這點上,我認為關鍵在於資料流的掌握。一個方向是減少資料庫系統的種類,降低程式設計和管理的複雜度;另一個方向是建置資料類型的專屬資料庫系統,使用其中一個資料庫來串連資料流,以最佳化特定資料的處理。兩個方向並不是互斥的,可以在資料庫設計上相互搭配。

每個人都可以幫助資料庫

以上也只是蜻蜓點水般說明大致的問題分類而已,應該就能夠瞭解到這些需求也不是只靠資料庫系統就能滿足,更全面地看待這些問題是有其必要性的。

有人可能會認為全面性的瞭解似乎很困難,但反過來說,其實每一個面向的專業提升都能對資料庫的運用有所貢獻。例如在商業理解上更精確的話,或許你就不需要讓資料庫大海撈針了;程式寫得有效率的話,資料庫連線閒置的時間就更短,進而增加資源使用率。

作為軟體工程師不應該劃地自限,應該嘗試去瞭解資料需求的全貌。當你更理解資料,就可以讓資料庫表現得更好,這是只有你才能做得到的事。


符合人性、善用資料

JSON 在 PostgreSQL 中開始被支援,是市場導向,也是一種符合人性的發展。JSON 作為善用資料的方式之一,在傳統關連式資料庫的角度看來可能是有些衝突的。本文提供給還在觀望的朋友參考,直接的結論是,如果你想用就可以用,無論功能和效能都符合基本需求,和市場上流行的 Document Database 擁有同一個等級的表現。

本篇摘要整理發表於 ModernWeb’20 的演說內容。

以目前趨勢來看,JSON 已經是最受觀迎的資料格式之一了。資料庫系統可以直接支援專屬處理方式的話,當然是很方便的事。但這樣自由表現的資料結構,相對於嚴格的正規化傳統觀念,是否會有負面的影響?

我的觀念是,資料庫的理想,終究還是在「符合人性」與「善用資料」兩個面向的落實。現行的資料庫系統實作上找到了平衡點,而你得到了更多的資料處理的自由。這樣的發展當然是好的方向。

以 PostgreSQL 的功能面來看,版本 12 才支援 JSONPath,對於資料的查詢才算是比較完整。即使 9.2 就開始支援 JSON,但如果要享受更先進的功能的話,最好可以升級到 12 以上的版本。

由於 JSON Document 可能會擁有比一般欄位資料更多的內容,對於查詢語句的長度也許會成為一個問題。PostgreSQL 的系統程式和手冊說明都確定是以 1GB 為上限,一般來說應該是很夠用。但要注意的是,這指的是位元組的長度,在多國語言編碼或相關程式處理需求的情況下,實際的最大長度可能不如你想得這麼多。雖然並不常見,但如果你的查詢語句長達數百 MB 時,就需要注意這個問題。

JSON 和 JSONB 的差異是很多人關心的,簡單來說,JSONB 就是 PostgreSQL 已經預先處理好的結果,所以可以直接其他像是索引等功能的操作。但也因為是處理過的結果,和原始內容不是百分之百相等的字串,如果你的 JSON 內容表現了超過標準以外的含義時,就應該還是保留原始的資料。

詳細內容請參閱使用手冊:JSON Type

在談到效能的部份,要注意的並不是 PostgreSQL 有多好,而是 PostgreSQL 確實可以在 JSON 處理的方案中,作為一個選項的存在。以單機資料庫而言是很不錯,但在水平擴充的需求上就不會是優勢。

我認為 PostgreSQL 在 JSON 的良好支援後,和其他異質的資料庫之間,有了更多共通的處理方式,在資料處理生態系統中,能夠更有效地提供資料交換的機會。

但在磁碟空間的運用上,PostgreSQL 仍有很大的進步空間,建議搭配支援即時壓縮的檔案系統會更好。

由於在收集資料時,發現許多網友期待能夠取代其他資料庫系統。我倒不這麼認為,也覺得不會發生。每一個資料庫系統深究起來都有其獨特之處,而值得慶幸的是,我們因為擁有了更多的選擇,可以選擇更符合人性,更能善用資料的方向前行。

想用就用 JSON in PostgreSQL


只有實戰才能累積能力的技術

本文提供一個簡單案例作為識讀 Query Plan 的入門,理解資料庫系統的行為,並且回饋給自己擬出改善策略。Query Plan 的識讀和改善,資料形態與開發文化的掌握是很關鍵的要素,但都是只有實際的職場才能接觸到的情境,是難以取代的「真人智慧」。

有關為何會有查詢計畫,請參閱另一篇「略懂查詢處理的老規矩」。

EXPLAIN ANALYZE

  • 列出查詢計畫及實際執行的結果,找出效能瓶頸。
  • 所有資料庫使用者都必須學習的技術,自己下的查詢自己檢查。
  • 進階使用者的技術分野。
  • 只有實戰才能累積能力的技術。
  • 查詢計畫可能會因為資料分佈不同而改變。
  • 一定要在實際的資料上評估。
  • 不同資料庫的查詢邏輯不同,調教方式不能移植。

以下為入門案例,初步瞭解實際情況之後,再閱讀其他理論資訊,會更有感覺。

改善前

問題分析

  1. 總執行時間為: 14329.521 (actual time)* 1 (loops) = 14329.521 ms 約 14 秒。
  2. 兩個步驟各佔約 7 秒: 7446.517 * 1 約 7.4 秒,458.859 * 15 約 6.9 秒。

Nested Loop Left Join (7.4 秒)

依階層計算可發現瓶頸在 Bitmap Index Scan on pg060_idx1,489.915 * 15 約 7.3 秒。既然已使用索引查詢,就找出 pg060_idx1 的內容。從上一層 Bitmap Heap Scan on pg060 可知所屬的 Table:

Indexes:
"pg060_pk" PRIMARY KEY, btree (agnt_id, vrfy_wkym, sys_code)
"pg060_idx1" btree (div_no, vrfy_wkym)

其引用的 pg060_idx1 只需要參考第 2 欄位的鍵值,但依舊需要掃描完第 1 鍵值,浪費了很多時間。

Index Scan using pg059_pk on pg059 b (6.9 秒)

情境同上,不適當的複合欄位索引造成索引未能加速查詢。

改善方式

總之經過許多猜想之後,直接跳到結論是建立新的索引:

CREATE INDEX pg059_idx2 ON pg059 (vrfy_wkym);
CREATE INDEX pg060_idx2 ON pg060 (vrfy_wkym);

由查詢計畫可知,僅需要參考一個欄位即可,那就建立單欄位的索引;複合欄位索引,除非查詢條件真的非常符合,否則空間和時間效益都不大(如此案例就是如此)。

不是加了索引就會快唷!!!

你可以參考 PostgreSQL 使用手冊:多欄位索引

改善後

結論

本案例未附上原始查詢語句,因為在分析過程中,不一定需要,關注太多反而是雜訊而沒有幫助。此例的原始語句是 View 的組合查詢,但其實我就沒有去拆解原語句,直接看最後資料庫的查詢計畫。

無論你怎麼輸入查詢語句,查詢計畫才是真的,那才資料庫實際的執行內容。

原始查詢語句用於重構資料邏輯。如果查詢計畫已無改善空間,才回到原始語句,以尋求資料邏輯的改善。(再往上就是應用邏輯或老闆邏輯了)

小練習:https://www.db-fiddle.com/f/awN5Kj5PGoBm1Q4Z5yqjUT/1
多欄位索引的練習,可以線上體驗跑跑看。

延伸問題

「你有觀察過自己的查詢(工作)計畫嗎?」

「面對不同的資料量(工作量),你的查詢計畫有不一樣嗎?」

此篇的口說內容:


你懂資料庫,資料庫就會幫你

A Tour of PostgreSQL Internals — 31 Oct 2000 Tom Lane

你沒看錯,是 2000 年的簡報,這個「老派」的基礎流程已經持續運作超過 20 年了。那現在的 PostgreSQL 呢?請移駕參閱官方使用手冊,然後還是回來看這個簡報吧!

時間證明資料庫的系統框架技術確實是可以歷久不衰,知識的成本效益相當高

你可以稍微瞭解一下資料庫是如何處理你的查詢語句的。最常遇到的情境是查詢語句調教時,你應該專注在 Planner 的結果 (Plan Trees),而不是查詢語句本身,因為你會知道,你的語句在經過 Parser 和 Rewriter 之後,資料庫系統的認知可能會與你想的不同。

如果你再進一步瞭解 Planner,那就有機會可以協助資料庫調整正確的查詢計畫,順利回饋到你的查詢效能。

所以才要正規化呀

資料庫正規化除了一般學理上所提到的減少資料冗餘、維護資料一致性等等之外,在資料庫內部而言,也更容易設計程式,針對相同的資料規格來進行高效能的資料處理。

在投影片也提到了,這個流程機制是為了讓資料庫系統可以自動產生良好的查詢計畫,人類的工作是設計良好的資料及索引結構,並且適當地使用查詢語句。

隱憂的部份則是為了快速估計處理成本,資料庫是以統計模型的概念來假設資料的樣貌,所以你需要經常更新你的資料統計數據 (vacuum);同時也設計了 prepared statement 機制,來減少重覆性計算查詢計畫的成本。

當你不解為何資料庫會如此設計時,請回頭想想這個流程吧。

查詢流程不是只有資料庫在用

老闆傳訊:幫忙去買個三寶飯回來當午餐吧!

Parser:好der,也快中午了,就去買一下吧。

Rewriter:沒指定店家,那就去買”我自己想吃的”.”沒柴燒臘店”。

Planner:要叫外送還是自己去買呢?現在剛好比較沒人,直接去買比較快吧!

Executor:等等,什麼?要追加 10 個,重死我了,早知道就叫外送了!

資料庫其實比你想的要單純許多,你才是那個更重要的人。

當你懂得如何幫助資料庫的時候,資料庫才會幫助你。


浪費每一個人的大腦和手腳,是不被允許的!

數位轉型(Digital Transformation)算是最近幾年的熱門關鍵字之一吧,與其說是「轉型」,我個人覺得更實用的字眼應該是「參與」。因為數位能力的改變,「參與」其中的人變得更多,才會讓生產力及消費力獲得提升。

以攝影來說,底片攝影從沒有數位工具,進展到數位攝影;原以為只有專業的單眼相機可以拍美照,現在擴展到小手機也能成為大師。隨著數位時代發展讓「參與」攝影這項活動的人越來越多,所謂的攝影,就會一直轉型,再讓「參與」的人不斷增加,自然就會朝多元且美好的方向前進。

反過來說,所有的轉型策略,如果失去了參與,那最好不要浪費大家的時間。

請關心一下人類

The Anatomy of Intel’s Digital Transformation

許多宣傳都太偏重在工具的改變,雖然我是資訊宅男,但這樣還是很奇怪,工具當然要搭配人來選擇呀。例如我在推廣 PostgreSQL 軟體的使用,但我也很討厭無腦推薦 PostgreSQL 的人,我從來都不打算推薦給還沒有準備好認同其價值的人。

必須要讓負面的聲音能夠被聽見,這是幫助每一個人的好方法。還沒有準備好,就一起面對來準備。每一個人都是必要的戰力,千萬不要浪費。要選擇數位工具時,要選擇現行文化參與人類最多的工具。

工具本身絕對不是神,但奇怪的是,許多人都會期待換了XX軟體之後就無敵了?!搞清楚,能讓這些工具成為神的,只有人而已。

從自己做起

那最重要的人是誰呢?是你自己。其實轉型的過程很困難,但也很簡單,就是從自己開始參與。從自己開始做起的話,不需要去想什麼時候才能開始,就是從自己認同的時候開始,可以自己掌握節奏。在嘗試新事物的時候,最終的結果好壞還很難說,但初期投入的經驗,對現在和未來的自己都存在著難以複製的價值。

如果漏掉了自己,不如大方地說你只是在應付老闆吧,大家都是好同事,會好好幫你的。

從自己做起很重要,因為方便收割和看風向,現實最重要。如果你職位低,沒有決策權,真實的參與可以有乖乖牌的形象,站在高點看對風向,才能收放自如;如果你位居高位,這種政治正確的業務,風險很低,能夠親身參與的話,要收割成果的時候,別人根本就很難有話說。

「沒有人是局外人」,是我覺得能夠順利走下去的關鍵。

平行處理

其中我覺得值得數位轉型的方向是資訊技術裡所談的「平行處理」。早期有些經營觀念把公司區分成大腦、心臟、手腳之類的,這也太奢侈了吧!能夠發揮最大經營能量的當然是「分散式系統」呀!

所謂「分散式系統」指的是集合很多台電腦,每台電腦都貢獻它的運算和儲存能力,一同承載大量複雜的事務。簡單而言,浪費每一個人的大腦和手腳,是不被允許的!

在數位轉型的過程中,可能會有新型態的溝通工具、線上會議、專案管理、人工智慧專家系統等。這些都可以用來幫助大家分擔決策執行的壓力,不要覺得自己的壓力無法釋放,只要數位轉型公開透明,大家都可以一起參與,有手出手,有腦出腦,減少公司單點故障的風險。

降低數位意識的比例

現在新鮮人找辦公室的工作還需要強調自己會不會電腦打字嗎?

想起小時候剛學會打字時,時常利用這項「專業」幫學校老師們登打排版段考試卷。但其實「打字意識」很低,通常都有餘力同時順便做完一次試考,幫老師挑一些筆誤。對現今的社會人而言,已經是不需要說出來的能力了。

其實我們一直都在做數位轉型,各行各業的進度不同,但那不代表是壞事。數位轉型的過程並不是只有一條直線,不需要盲目跟著其他人躁進。如同前文所述,參與才是重要的目的,因為數位轉型而讓參與經營的同仁增加,代表企業的能量更穩定和龐大;讓參與的客戶增加,代表商機聚集且成長。

我對於數位轉型的期待是,降低數位意識的比例,也就是你應該要越來越不知道你正在數位轉型,也越來越不需要資訊人員介入。在未來,這些都只是一種平凡的生活方式而已。

只要可以幫助自己花更多心思讓身邊的人更好,轉不轉型,都好。

圖文不符


CYBERSEC 2020 臺灣資安大會

本篇分享設定對像為初入 PostgreSQL 資料庫系統的組織或企業。眾所皆知,安全和方便一直是需要取得平衡的,所以本篇先提供初步可以思考的安全策略方向。它們必須要與你的企業文化與系統環境綜合考量,在可供長期運作的前提下,才是當下適當的策略。

本篇以連線過程區分,連線前、連線中、連線後,方便在實際操作時聯想其應有的配置方式。在此之前,還是先強調,人員的安全意識,才是安全配置最重要的一環。

瀏覽簡報

輕量級的企業用 PostgreSQL 安全管理策略

現實=安全、方便、合規

安全是必須和現實接軌的,如果不是可以讓維運人員每日執行的安全策略就沒有意義。合規的標的時常是定義模糊的法規或標準,訂定可以明確執行的管理辦法是比較有效率的方式。

所謂的「安全」很多人的想像是外部的惡意入侵,但現實上多數造成資料庫無法運作反而是同仁們的「手滑」。所以安全策略反而更多的措施是要協助同仁們可以安心作業,自然防呆。

連線前

連線前的重點在於避免不小心連入資料庫。這在多應用程式的開發環境下很容易發生。因為每個團隊都有命名及結構設定的慣性,時常會不知道自己連錯資料庫,就可能會造成資料損壞的風險。關於資料庫連線及預設的權限可以參閱「PostgreSQL 預設權限設定

pgBouncer 作為 Connection Pool,除了效率以外,也順帶提供了安全性的提升,因為資料庫系統可以安心地處理眼前的工作就好,即使應用程式佈署異常,大量開啓資料庫連線無法中止,都不會影響資料庫的正常作業。細節請另參閱「你當然需要 PgBouncer 啊」。

限制 postgres 登入途徑是值得採用的策略,如同 Linux 環境的安全策略都會回收 root 一樣,因為權力影響大,又無法追蹤軌跡。合併於 sudo 一同管理可以簡化安全規則,是很理想的方式。不設密碼是規避組織有密碼變更的安全規則,沒有密碼就不需要變更密碼。

連線中

避免不小心越權更新資料,不論你是手滑還是故意的,對資料庫的傷害都很巨大。由於 PostgreSQL 預設權限是比較大的,在安全的前提下,儘量先縮小權限,當操作到無法操作時再確認業務需求是否真的需要更大的權限,逐漸符合正常應俱備的權限範圍。

Timeout 方面的設定是不得已的,畢竟很多查詢都是要執行了才知道結果,也才能累積經驗和記錄,但停損點是必須的。避免浪費時間等待有高可能性失敗的查詢,提早失敗是好的結果。另可參閱「保護自己,善待他人」。

資料遮蔽雖然並不是所有行業都需要,但也可以視為一種製造測試資料的方式。保持測試系統面對測試資料,正式系統使用正式資料的習慣,可以減少許多意外的發生。

連線後

「亡羊補牢,未為遲也」,完全的安全是不可能做到的事,但至少要有亡羊補牢的準備,其中「備份」永遠是必須要做的事。即使有增量備份這樣的功能可以使用,還是必須要時常重新再做完整備份。因為每次的增量備份只要有一次失敗沒有被發現,等於接下來的備份都沒有意義了。

由於 PostgreSQL 相對來說是注重符合 SQL 標準的資料庫系統,所以指令的相容性也很高。所以如果可以的話,請儘量升級到最新版本,在安全和效能上都可以取得更大的優勢。

磁碟空間在 PostgreSQL 的設計上是沒有管理的,全權交給作業系統處理,所以當交易的當下磁碟空間不足的時候,反而限制了維護工作的進行,因為連刪資料都沒有辦法(會需要先寫入 transaction log)。額外在資料庫以外進行磁碟空間的超前佈署就變得十分重要了,這個部份的建議可以參閱「你的資料庫有87%機率超過用量」。

先保護自己人不會搞死自己

資料庫安全的三個要素:Confidentiality、Integrity、Availability。只要任何一項被破壞,資料庫不再被信任可以好好保存資料的時候,就是不安全了。

在導入資料庫系統的初期,更多的安全風險來自於操作的不熟悉。如果能利用安全策略來養成具有安全意識的操作習慣,其實就已經可以安心渡過大多數的安全危機了。


親身投入戰場
是最簡單做出貢獻的方法
沒有之一

「我已經在做了,現在需要你的幫忙」
是最溫暖的話語
「你好棒棒」
留給投身前線的自己吧

只要願意從自己做起
就可以讓團隊自然變得更好


Timeout 是最殘酷的溫柔

因為很多人沒有這個習慣,所以希望新手在看到這篇時可以思考利弊,養成好習慣。
補充一下建議在開始進行 Transaction 的時候,自己可以評估正常情況下的執行時間,有三個參數可以考慮設定:

statement_timeout

查詢預期的最長回應時間,可作為查詢作業的停損點,超時可能代表查詢複雜度過高。

lock_timeout

更新資料時取得鎖定的最長回應時間,超時可能表示資料處理有衝突,儘早 ROLLBACK 反而是好事。

idle_in_transaction_session_timeout

如果你自己的 transaction 在時限內沒有處理完,就會自動終止交易,釋放所有 LOCK。是保護他人的方式。

每一個查詢都不可能是無限的執行時間,在多人交易的執行環境中,保持可預期的執行時間等同於保持了資料庫系統的穩定性。而當執行時間超過預期時,通常是產生了意料之外的錯誤,應該儘早讓系統回報錯誤,儘快修正問題。

statement_timeout

範例如下,可以儘早發現錯誤:

postgres=# \timing on
Timing is on.
postgres=# SET statement_timeout=3000;
SET
Time: 0.424 ms
postgres=# SELECT pg_sleep(5);
ERROR: canceling statement due to statement timeout
Time: 3002.658 ms (00:03.003)

lock_timeout

lock_timeout 自己一個人的時候可能沒什麼感覺,但在大量資料庫連線時,可以節省等待的時間。
兩個不同連線的情況,其共同存取同一TABLE:

CREATE TABLE test (id int, value text);
INSERT INTO test (id, value) VALUES (1, 'abc');
  • Process A
BEGIN;
SET lock_timeout=3000;
SELECT * FROM test WHERE id=1 FOR UPDATE;
-- Process B is beginning the transaction.
-- Process A is doing some application flows.
UPDATE test SET value='def' WHERE id=1;
COMMIT;
  • Process B (另開一個連線測試)
    如果在執行 SELECT 時,Process A 一直都沒有辦法執行到 COMMIT…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store