Instagram 說明用 PostgreSQL 的五個優點...

先不管 Instagram 最近的負成長以及反駁,剛剛在 Instagram Engineering 上看到對 PostgreSQL 的稱讚:「Handling Growth with Postgres: 5 Tips From Instagram

FacebookMySQL 的領域裡的實力以及貢獻度可是數一數二,但 Instagram 在被 Facebook 買下後仍然繼續使用 PostgreSQL,總是有些原因存在... 雖然真正的原因不一定是技術,但這篇試著用技術解釋的內容還是可以看一看,了解 PostgreSQL 有哪些特點...

第一個是講 partial indexes,這與 MySQL community 常講的 partial index 是不同的東西。

MySQL 的 partial index 是指只 index 某個欄位的一部分,像是 VARCHAR(255) 裡面只 index 前面的 10 chars;而 PostgreSQL 的 partial indexes 則是指符合某個條件的 row 才 index。

第二個是 functional indexes,可以針對欄位計算後再 index。MySQL 的 partial index 在 PostgreSQL 裡可以用 functional indexes + substr() 達到相同的效果,而且還有其他 function 可以用,花樣更多。

兩個都是 MySQL 做不到 (或是做不好),但 PostgreSQL 做的不錯的。一般在 MySQL 要達到 PostgreSQL 的這兩個功能需要另外開一個欄位,在裡面儲存去正規化後的值,再對這個欄位 index。

第三個是講 defrag 的事情,這部份 MySQL 也可以用第三方的工具 Percona Toolkit 搞定。第四個講 PostgreSQL 的 Write-Ahead Log,有點像是 MySQL 的 binlog。最後一個講 Python 上的 psycopg2

看來看去就是 index 那塊最明顯。可以直接減少去正規化的欄位...

對 MySQL 的 VARCHAR 欄位使用 INDEX 時可以增加效率的方法...

MySQL 中,如果你有 VARCHAR(255) 這種欄位,不要對直接對這個欄位下 INDEX。因為 key 會以最大長度 255 chars 為固定大小,而非動態決定 (latin1 的時候 1 char 是 1 byte,utf8 是 3 bytes,utf8mb4 是 4 bytes),當資料有 1M row data 就直接吃掉 1MB/3MB/4MB 的空間。

解決方法是利用「index 可以指定只取前面 n chars」這個功能來做,至於 n 要取多少就是要估算了... 在「Optimal index size for variable text in MySQL」這篇把要怎麼做的過程寫得還蠻完整的。

同樣的道理也可以用在固定寬度的 BINARY(16) 系列上。