PostgreSQL

前言 前陣子我的藥師朋友經常需要手動計數藥丸，以便進行包藥作業．這個過程既耗時又容易出錯。為了解決這一問題，我幫他設計了一個簡單的應用程式，使用 YOLOv8 模型來自動識別並計算照片中藥丸的數量，從而快速提供準確的結果 專案說明 專案的詳細介紹我把他整理到 Notion 上，裏面有更詳細的說明 Ref: GitHub Repo 系統架構 這個專案需求非常簡單，其實單純一個 Monolithic 的架構就可以應付，但為了練習看看微服務(Micro service)的切分，就嘗試將不同的功能切分成不同的服務，彼此用 Restful API 溝通 Tech Stack 簡單列一下使用到的技術 Backend Python(FastAPI) Node.js(Express) written in TypeScript Auth JWT Google Auth Redis AI Model YOLOv8 Label-Studio (Labeling Tool) Frontend React written in TypeScript PWA Storage PostgreSQL (database) Minio (Image storage) 心得 這是第一次嘗試 React 的 PWA，用 service worker 滿直覺也滿簡單的，配置一下部署到 Netlify 上，就可以把網站下載到手機桌面上充當一個 APP 使用 這是第一次嘗試 Yolov8 的模型，並使用 Label-Studio 工具作為標註，使用起來相當直覺，當有多人需要同時標註時，簡單在電腦啟動 server，然後用 ngrok 臨時建個 tunnel，開放給別人一起標註...

前言 索引(Index)類型的選擇對於資料庫效能的優化至關重要，不同的索引類型適用於不同的查詢和資料結構。近期開發常常會接觸到 PostgreSQL，所以順手整理幾種常見的 PostgreSQL 索引類型及其適用場景 索引類型 B-tree B-tree 是 PostgreSQL 中最常用的索引類型，也是 PostgreSQL 建立索引時，未指名索引類型時預設的索引類型 B-tree 中的每個節點通常包含多個 key-value pair，其中 key 用於索引，value 指向表中相應的資料 它適用於以下幾種查詢： 比較操作：<、>、>=、<= 區間查詢：BETWEEN 空值查詢：IS NULL 語法如下 -- 建立 table 的索引 CREATE INDEX index_name ON table_name; -- 建立單獨 column 的索引 CREATE INDEX index_name ON table_name (column_name); Hash Hash 索引是應用 hash 的特性來達到快速查詢的情境．適合需要判定相等(equality check)的查詢： 相等：= 存在：IN 遇到資料異動(INSERT, UPDATE, DELETE)的時候，為了解決 hash 碰撞(collision)和重製資料(rehash data)，維護 hash 的行爲會比 B-tree 帶來更昂貴的成本． 除此之外，雖然 Hash 索引在等值查詢上的效能非常好，但它在範圍查詢(range query)和排序(sorting)的情境上不及 B-tree 來得適合，因此在使用上需要特別小心，一般來說在沒有特殊要求下，使用 B-tree 已能滿足整體效能需求...

前言 近年來隨著網路發展愈來愈快，高併發的場景愈來愈常見，高併發性能是指系統能夠有效地處理大量同時訪問和操作數據的能力，這在網路應用、金融系統、電子商務等領域愈來愈重要．隨著數據量的不斷增長和用戶需求的不斷擴展，我們需要確保我們的資料庫系統能夠承受高負載、高併發的壓力，同時保持良好的性能和穩定性。在這樣的背景下，優化資料庫的設計和架構、實施有效的併發控制策略成為至關重要的任務。 本篇是研究了一般 RDBMs 如何處理高併發問題的常見解法 Lock 機制 我們都知道寫程式的時候，如果有兩個 process/thread 要對同筆資料進行操作，為了避免資料的不一致，我們常會加上鎖的機制，臨時將該資源上鎖，僅允許一次只能被一個 process/thread 更動．同樣的，RDBMs 也有相同的機制來處理這種情形，這也是傳統資料庫處理高併發的最基本原理 SELECT … FOR UPDATE 為了避免兩個 transaction 同時對同筆資料進行操作，造成資料不一致的問題，一般常見的 RDBMs 都有提供底下這個 SQL 語法 BEGIN SELECT ... FOR UPDATE <各式針對該資料的操作, ex: UPDATE, DELETE...> COMMIT 一旦開啟新的 transaction 並執行這段 SQL 語法，在還沒 COMMIT 以前，資料庫將會替當前這筆資料加上鎖，鎖住這些即將要被更動的資料，因為資料庫悲觀的認為他們隨時有可能被修正(悲觀鎖)，一旦上鎖後，下一個 tx 要進行操作時，都需要等待該鎖釋放後才能進行 至於這個鎖是行級鎖還是表級鎖，以及這個排他鎖會鎖哪些操作(SELECT? INSERT? UPDATE? DELETE?)，則因各種資料庫背後的機制而不同 以 MySQL(InnoDB)為例，如果 SELECT 的對象加上 WHERE 條件是主鍵或有加上索引，則僅會加上行級鎖，鎖住當前查詢到的資料．但如果 WHERE 條件不是主鍵或非索引，則整個資料表都會被鎖住．正因如此，一旦 SELECT ... FOR UPDATE 沒寫好，MySQL 是有可能把整個資料表都鎖住，造成其他 transaction 需要等待鎖釋放才能進行操作，進而增加 latency 而影響使用者體驗．(以上前提要是 MySQL 的 InnoDB 作為 storage engine，因為 MyISAM 預設都是表級鎖)...

前言 PostgreSQL 是我目前工作與專案上最常用到的 RDBMs，為了能夠更了解 PostgreSQL 的運行以彌補底層的知識，就找到了 這系列 的文章，覺得內容非常的詳細與完整，很適合做為 PostgreSQL 進階的學習教材，就花了些時間整理了一下 底下的內容都是整理自 Distributed Systems Authority 的文章 PostgreSQL 底層架構 開始以前先放上 Distributed Systems Authority 繪製的架構圖，底下會依據各元件一一說明 整體來說，PostgreSQL 是採用近幾年蔚為流行的 Client-Server 架構 Client (frontend) 泛指一系列 client 連線方式，包含：psql, createdb, dropdb, createuser… 等 command line 指令 Server (backend) 可以概括稱之為 server process，負責管理 database files，接受 client 的連線，並執行 client 一系列相關的操作 當 server 收到每一筆 client 的連線，main process 都會 fork 出新的 process 來處理，所以 client 和 server 的溝通是與 main process 無關的 整體而言，backend 可分成底下四個元件： Process Manager 當執行 /usr/local/pgsql/bin/postgres 後就會建立第一個 daemon process，監聽每一個 client 對 server 發起的連線請求，在 max_connections 的數量限制下，daemon process 會為每一個連線 spawn 出 backend process 來讓 client query...

前言 前陣子因為專案需求，需要把某個 docker 的 PostgreSQL container 資料備份到另一台 docker container 內，因此紀錄一下整體過程 簡例 首先，先拉取 postgresql 14.6 版的 image docker pull postgres:14.6 docker image | grep postgres # 取得image id 再來準備一份測試的 seed.csv，等等要匯入 postgresql 中 title,content Eric,Hello Madi,Hi Danny,GoAhead Kyle,Looks grood 將 postgresql 的 container 跑起來，並 bind mount 掛載剛剛的 csv 檔 docker run -itd --rm --name example -p 5432:5432 -v "$(pwd)"/seed.csv:/seed.csv -e POSTGRES_PASSWORD=postgres <image_id> 接著，跳進去 container 內，把 csv 的內容匯入 postgresql 中 docker exec -it example psql -U postgres -d postgres /* 要先依照csv的內容建立table */ CREATE TABLE article(title text, content text); /* 將csv匯入table */ COPY article (title, content) FROM '/seed....