PostgreSQL 15 釋出

PostgreSQL 15 出了:「PostgreSQL 15 Released!

先前提到過「PostgreSQL 15 將可以對透過 UNIQUE 限制 NULL 的唯一性了」,反而沒排上這次 release 的重點,翻了一下的確是排不太上 XD

第一個超大的改善是 sorting:

In this latest release, PostgreSQL improves on its in-memory and on-disk sorting algorithms, with benchmarks showing speedups of 25% - 400% based on which data types are sorted.

在「Speeding up sort performance in Postgres 15」這邊有提到四個改動,裡面很詳細的說明了改動的內容,以及 benchmark 差異。

如果以他列出來的四個進展,應該是第二個「Reduce memory consumption by using generation memory context」這個會最容易遇到,也改善最多:

另外是第三個「Add specialized sort routines for common datatypes」也會有一些:

再來是拿 PostgreSQL 當 OLAP engine 用的時候會發生的第四個「Replace polyphase merge algorithm with k-way merge」:

最開頭第一個「Improvements sorting a single column」的 SELECT col1 FROM tab ORDER BY col1; 這種 case 好像用的很少,限制 SELECT 的部份也只能出現後面 sorting 的 column,但如果遇到的話效能提昇很多:

除了 sorting 的改善以外,另外一個是 WAL 支援 LZ4zstd,這對於有寫入量很大的環境應該會有幫助:

PostgreSQL 15 adds support for LZ4 and Zstandard (zstd) compression to write-ahead log (WAL) files, which can have both space and performance benefits for certain workloads.

正式版出來後,應該會有一些整體性的 benchmark 數字可以看,再來等著看...

PostgreSQL 對 fsync() 的修正

上次寫了「PostgreSQL 對 fsync() 的行為傷腦筋...」提到 fsync() 有些地方是與開發者預期不同的問題,但後面忘記跟進度...

剛剛看到 Percona 的人寫了「PostgreSQL fsync Failure Fixed – Minor Versions Released Feb 14, 2019」這篇才發現在 2/14 就出了對應的更新,從 release notes 也可以看到:

By default, panic instead of retrying after fsync() failure, to avoid possible data corruption (Craig Ringer, Thomas Munro)

Some popular operating systems discard kernel data buffers when unable to write them out, reporting this as fsync() failure. If we reissue the fsync() request it will succeed, but in fact the data has been lost, so continuing risks database corruption. By raising a panic condition instead, we can replay from WAL, which may contain the only remaining copy of the data in such a situation. While this is surely ugly and inefficient, there are few alternatives, and fortunately the case happens very rarely.

A new server parameter data_sync_retry has been added to control this; if you are certain that your kernel does not discard dirty data buffers in such scenarios, you can set data_sync_retry to on to restore the old behavior.

現在的 workaround 是遇到 fsync() 失敗時為了避免 data corruption,會直接 panic 讓整個 PostgreSQL 從 WAL replay 記錄,也代表 HA 機制 (如果有設計的話) 有機會因為這個原因被觸發...

不過也另外設計了 data_sync_retry,讓 PostgreSQL 的管理者可以硬把這個 panic 行為關掉,改讓 PostgreSQL 重新試著 fsync(),這應該是在之後 kernel 有修改時會用到...

Wal-Mart 與 Uber/Lyft 合作迎戰 Amazon

Amazon 透過各種技術整合的比較好 (像是先前發表無人機空運的技術 Amazon Prime Air),而 Wal-Mart 則是跟 Uber/Lyft 這樣的公司配合:「Wal-Mart Plans to Test Grocery Delivery Through Uber, Lyft」。


The move steps up competition with Amazon’s burgeoning grocery-delivery service and provides a potential new avenue of growth for Uber and Lyft. The idea is to let Wal-Mart customers pick out groceries online and then have employees fill the order and give it to one of the ride-hailing companies’ drivers. Shoppers will pay a $7-to-$10 delivery charge to Wal-Mart to have the groceries brought to their door.