PostgreSQL 的 Fuzzy Matching

在「Fuzzy Name Matching in Postgres」這邊看到 PostgreSQL 下怎麼設計 Fuzzy Matching 的方式,文章裡用的方法主要是出自 PostgreSQL 的文件:「F.15. fuzzystrmatch」。

文章最後的解法是 Soundex + Levenshtein

翻了一下資料,這個領域另外有 NYSIIS (New York State Identification and Intelligence System):

The New York State Identification and Intelligence System Phonetic Code, commonly known as NYSIIS, is a phonetic algorithm devised in 1970 as part of the New York State Identification and Intelligence System (now a part of the New York State Division of Criminal Justice Services). It features an accuracy increase of 2.7% over the traditional Soundex algorithm.

以及 Metaphone

Metaphone is a phonetic algorithm, published by Lawrence Philips in 1990, for indexing words by their English pronunciation. It fundamentally improves on the Soundex algorithm by using information about variations and inconsistencies in English spelling and pronunciation to produce a more accurate encoding, which does a better job of matching words and names which sound similar. As with Soundex, similar-sounding words should share the same keys. Metaphone is available as a built-in operator in a number of systems.

不過這些都是以英文為主,中文的沒特別翻到...

PostgreSQL 的 scale 建議

Hacker News Daily 上看到「Postgres scaling advice for 2021」這篇,講 PostgreSQL 要怎麼 scale,在 Hacker News 上也有對應的討論可以看:「Postgres scaling advice (cybertec-postgresql.com)」。

文章前面先提到分散式系統的複雜度會導致 RDBMS 上的一些假設失效,所以如果可以用單台機器暴力解,就儘量用單台機器來解 (scale up 的情境),裡面就提到了一些「暴力可以解決很多問題」的說明,差不多就是前幾天提到的「Let's Encrypt 升級資料庫伺服器 (AMD YES?)」。

後面提到如果真的要放進分散式的 RDBMS (scale out 的情境),怎麼設計資料結構會比較好。

這邊剛好也可以提一下,量夠大的時候要把 OLTPOLAP 的應用分開,現在有很多 OLAP 資料庫可以選擇,同步的工具也很成熟了,通常效能會比在 OLTP 上面硬跑來的好。

最後提一下,文章裡面對於 transaction per second 可以拉很高,有些假設沒有明寫出來。這需要盡可能把 transaction 拆小,避免常常有 giant transaction 卡住整個資料庫,這點對於一般的系統會需要做不少改寫...

不過最後比較疑惑的是,這種文章怎麼會上 Hacker News 的啊...

用 GPT-3 直接產生對應的 SQL query

Hacker News Daily 上看到的東西,直接給 GPT-3 條件,叫 GPT-3 給出對應的 SQL query,這就有點誇張了...:「Automating My Job with GPT-3」。

這樣丟進去:

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct.

Input: how many users signed up in the past month?

然後這樣出來:

GPT-3 Response: SELECT COUNT(*) FROM users
WHERE signup_time > now() - interval '1 month'

把欄位名稱替換掉就可以用了... 而且接下來就更誇張了,直接要求 GPT-3 也要照著設定的 table schema 給答案:

Instruction: Given an input question, respond with syntactically correct PostgreSQL. Be creative but the SQL must be correct. Only use tables called "users" and "charges". The "users" table has columns: id (integer), signup_dt (timestamp), email (character varying), and plan_type (character varying). The "charges" table has columns: amount (bigint), user_id (integer), and charge_dt (timestamp).

Input: how much revenue did we have in the past 7 days?

然後輸出了:

GPT-3 Response: SELECT SUM(amount) FROM charges WHERE charge_dt > now() - interval '7 days'

接下來是在同樣 instruction 下,跨表格的問題:

Input: how much revenue have we had from users that signed up in the last 6 months?

這時候 INNER JOIN 就跑出來了:

.8 Temperature GPT-3 Response: SELECT SUM(charges.amount) FROM users INNER JOIN charges ON users.id = charges.user_id WHERE signup_dt >= DATE_SUB(now(), INTERVAL '6 months')

後面的問題也很精彩,看起來之後可以接上 BI dashboard,直接丟句子進去,然後拉各種資料出來視覺化?

Amazon DocumentDB 推出相容 MongoDB 4.0 的版本

在「Amazon DocumentDB (with MongoDB compatibility) adds support for MongoDB 4.0 and transactions」這邊看到 AWSAmazon DocumentDB 上推出相容 MongoDB 4.0 的版本。

把年初在 Ptt 上寫的「Re: [請益] 選擇mongoDB或是relational database ??」這篇拿出來講一下,MongoDB 4.0 最大的改進就是 multi-document transactions 了。

不過 AWS 先前推出 DocumentDB (MongoDB) 時看到的限制,大家都猜測是用 PostgreSQL 當底層 (「AWS 推出 MongoDB 服務:Amazon DocumentDB」與「大家在猜 Amazon DocumentDB 的底層是不是 PostgreSQL...」),雖然目前還是不太清楚,但如果這個猜測屬實的話,要推出各種 transaction 的支援完全不是問題 XDDD

Percona 對 MongoDB 的建議

看到「5 Things DBAs Should Know Before Deploying MongoDB」這篇,裡面給了五個建議,其中第五點頗有趣:

5) Whenever Possible, Working Set < RAM

As with any database, fitting your data into RAM will allow for faster reads than from disk. MongoDB is no different. Knowing how much data MongoDB has to read in for your queries can help you determine how much RAM you should allocate to your database.

這樣的設計邏輯很奇怪啊,你不要扯其他 database 啊,你們家主力的 InnoDB 一直都沒有推薦要 Working Set < RAM 啊,反過來才是用 InnoDB 的常態吧,而且在 PostgreSQL 上也是這樣吧 XDDD

現在上面的文章真的是挑著看了... XD

RDS 推出 ARM 版本

Amazon RDS 推出了 ARM 的版本:「New – Amazon RDS on Graviton2 Processors」,包含了 MySQLMariaDBPostgreSQL 的版本都有支援,不過看起來需要比較新版的才能用:

You can choose between M6g and R6g instance families and three database engines (MySQL 8.0.17 and higher, MariaDB 10.4.13 and higher, and PostgreSQL 12.3 and higher).

官方宣稱可以提供 35% 的效能提昇,考慮費用的部份會有 52% 的 c/p 值提昇:

Graviton2 instances provide up to 35% performance improvement and up to 52% price-performance improvement for RDS open source databases, based on internal testing of workloads with varying characteristics of compute and memory requirements.

對於 RDS 這種純粹就是個服務的應用來說,感覺應該不會有什麼轉移成本,只要測過沒問題,換過去等於就是現賺的。看起來等 RI 約滿了就可以切...

EnterpriseDB 買下 2ndQuadrant

算是 PostgreSQL 社群裡面的大事情,看到大老在討論 EnterpriseDB (EDB) 買下 2ndQuadrant 的事情:「Community Impact of 2nd Quadrant Purchase」,這兩家公司都是 PostgreSQL 社群裡面重量級的台柱。

先翻了一下新聞稿,兩邊的官方新聞稿分別是「How EDB Became the Leader in the Postgres Market」與「How EDB Became the Leader in the Postgres Market」。

回到原來的文章,裡面提到了 core team 的不成文規定,這個部份可以從 Contributor Profiles 這邊看到目前 core team 有五位成員,Peter Eisentraut 來自 2ndQuadrant,而 Bruce Momjian (這是文章作者自己) 與 Dave Page 則是來自 EnterpriseDB:

First, there is an unwritten rule that the Postgres core team should not have over half of its members from a single company, and the acquisition causes edb's representation in the core team to be 60% — the core team is working on a solution for this.

裡面有提到目前正在找辦法解決中,但不知道目前會怎麼解決,讓出位置可能是一個方法,加到七個人應該也是個方法,反正方法不算少,就等著看...

另外他提出來的兩個問題我覺得都還好,就是併購本來就會發生的事情。

這次的併購算是 PostgreSQL 社群裡面蠻熱鬧的事情,雖然是商業公司之間的併購,但社群這邊應該也會有不少變化...

PostgreSQL 13 的 B-Tree Deduplication

Hacker News 上看到「Lessons Learned from Running Postgres 13: Better Performance, Monitoring & More」這篇文章,其中有提到 PostgreSQL 13 因為 B-Tree 支援 deduplication,所以有機會縮小不少空間。

搜了一下源頭是「Add deduplication to nbtree.」這個 git commit,而 PostgreSQL 官方的說明則是在「63.4.2. Deduplication」這邊可以看到。

另外值得一提的是,這個功能在 CREATE INDEX 這頁可以看到在 PostgreSQL 13 預設會打開使用。

依照說明,看起來本來的機制是當 B-Tree index 內的 key 相同時,像是 key1 = key2 = key3 這樣,他會存 {key1, ptr1}{key2, ptr2}{key3, ptr3}

在新的架構下開啟 deduplication 後就會變成類似 {key1, [ptr1, ptr2, ptr3]} 這樣的結構。可以看出來在 key 重複的資料很多的時候,可以省下大量空間 (以術語來說的話,就是 cardinality 偏低的時候)。

這樣看起來可以降低不少壓力...

PostgreSQL 的 SERIALIZABLE 的 bug

這是 Jespen 第一次測試 PostgreSQL,就順利找出可重製的 bug 了:「PostgreSQL 12.3」。

第一個 bug 是 REPEATABLE READ 下的問題,不過因為 SQL-92 定義不夠嚴謹的關係,其實算不算是 bug 有討論的空間,這點作者 Kyle Kingsbury 在文章裡也有提出來:

Whether PostgreSQL’s repeatable-read behavior is correct therefore depends on one’s interpretation of the standard. It is surprising that a database based on snapshot isolation would reject the strict interpretation chosen by the seminal paper on SI, but on reflection, the behavior is defensible.

另外一個就比較沒問題了,是 SERIALIZABLE 下的 bug,在 SQL-92 下對 SERIALIZABLE 的定義是這樣:

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

也就是說,在 SERIALIZABLE 下一堆 transaction 的執行結果,你至少可以找到一組排序,使得這些 transaction 的結果是等價的。

而 Jespen 順利找出了一組 transaction (兩個 transaction),在 SERIALIZABLE 下都成功 (但不應該成功):

對於這兩個 transaction,不論是上面這條先執行,還是下面這條先執行,都不存在等價的結果,所以不符合 SERIALIZABLE 的要求。

另外也找到一個包括三個 transaction 的情況:

把 transaction 依照執行的結果把 dependency 拉出來,就可以看出來裡面產生了 loop,代表不可能在 SERIALIZABLE 下三個都成功。

在 Jespen 找到這些 bug 後,PostgreSQL 方面也找到軟體內產生 bug 的部份,並且修正了:「Avoid update conflict out serialization anomalies.」,看起來是在 PostgreSQL 引入 Serializable Snapshot Isolation (SSI) 的時候就有這個 bug,所以 9.1 以後的版本都有這個問題...

這次順利打下來,測得很漂亮啊... 翻了一下 Jespen 上的記錄,發現好像還沒測過 MySQL,應該會是後續的目標?

從 Oracle 轉移到 PostgreSQL 的工具 Orafce

在「Migrating from Oracle to PostgreSQL: Tips and Tricks」這邊看到在討論怎麼從 Oracle 資料庫轉移到 PostgreSQL 上。

開頭介紹了 Orafce,實做了一些 Oracle 上的 function,可以使得轉移時不用改寫太多 SQL:

The "orafce" project implements of some functions from the Oracle database. The functionality was verified on Oracle 10g, and the module is useful for production work.

先記錄起來,之後如果有機會的話比較好找...