Tag: sql

GCP 推出 AlloyDB，一套相容 PostgreSQL 協定的資料庫服務

也是在清 RSS reader 的時候翻到的，看起來是在今年的 Google I/O 上發表的服務，AlloyDB：「AlloyDB for PostgreSQL under the hood: Intelligent, database-aware storage」，值得提的是這篇有中文版可以看：「適用於 PostgreSQL 的 AlloyDB 隆重登場：從此擺脫成本高昂的老舊資料庫」。

另外還有一篇比較偏 PR 的文章也可以看看：「Introducing AlloyDB for PostgreSQL: Free yourself from expensive, legacy databases」，這篇就比較針對的提到了與 AWS 的服務相比，但畢竟是 PR 稿沒有明講 (出事會比較好打模糊戰)，但我猜測是與 Aurora 對比：

AlloyDB was also two times faster for transactional workloads than Amazon’s comparable service.

宣稱在 OLTP 上快了兩倍 (原來的三倍？)，但應該都是以 PostgreSQL 下去改，猜測可能是底層的 storage 與 replication 比較好？

AlloyDB 設計上是考慮了 HTAP (Hybrid transactional/analytical processing) 的使用，所以同時可以提供 OLAP 與 OLTP 的應用：

[...] This makes AlloyDB a great fit for business intelligence, reporting, and hybrid transactional and analytical workloads (HTAP).

直接在一個資料庫內處理 OLAP 與 OLTP 這點的確會讓 AlloyDB 比 AWS 目前能提供的方案方便不少 (然後想一下 BigQuery 團隊...)。

目前在 AWS 對應的方案應該是透過 Redshift 來解決，另外一個方案是透過 Athena 來跑。

最後來看價錢，如果效能變成兩倍但價錢也是兩倍的話，就代表在價格上沒優勢。

先看機器的部份，如果是拿 Aurora 這邊 Intel-based 的 db.r5.24xlarge (96 vCPU + 768 GB RAM) 來算的話是 US$13.92/hr，而如果換算到 AlloyDB 的話是 US$14.94528/hr，相除是 0.9314，大約 7% 的差距，可以算是同一個級距。

如果 Aurora 這邊是拿 ARM-based 的 db.r6g.16xlarge (64 vCPU + 512 GB RAM) 來算的話是 US$8.306/hr，換算到 AlloyDB 的話是 US$9.96352/hr，相除是 0.8336，這邊就差超過 16% 了...

(這邊剛好回顧一下 "Amazon’s comparable service" 這段，不確定他是跟 Intel-based 比還是跟 ARM-based 比，畢竟 ARM 除了比較便宜外，還有效能的提昇)

但最大的差異應該是在 storage 相關的部份。其中 Aurora 這邊的空間與 I/O 是分開收費的，以 us-east-1 來說，storage 是 US$0.10/GB/mo，而 I/O 是 US$0.20/million-requests，在 AlloyDB 這邊來說，Regional cluster storage 是 US$0.0004109/GB/hr (us-east4)，變成是 US$0.295848/GB/mo，兩邊相比後可以算出來對等的計價會是 AWS 的 storage 加上 AWS 給你 1.47M 的 I/O (per GB)。

這樣算起來把資料丟 S3 跑 Athena 可能不會比較貴... (當然效能是另外的主題了)

光就檯面上的資料來看，看起來是個不錯的東西，等後續有人跳進去用看看感想...

這個月 GitHub 的不穩問題，都是 mysql1 這個 cluster 的鍋...

GitHub 針對了這個月的四次 downtime 說明，大致上都跟 mysql1 這組 cluster 有關：「An update on recent service disruptions」，這是 Keith Ballinger 發的文章，找了一下掛的頭銜是 SVP of Engineering at GitHub。

文章裡提到的 mysql1 在「Partitioning GitHub’s relational databases to handle scale」這邊可以看到一些資訊 (我在「GitHub 的 MySQL 架構與數字」這邊也有提到)，基本上有 ProxySQL + Vitess 兩套方案在 scale，但可以看出來主資料庫本身還是有很大的 loading 在上面跑。

這次的問題是 mysql1 看起來這次遇到了效能上的瓶頸，不過還是沒找到原因，這可以從這幾次的說明看出來，從第一次的 outage：

The incident appeared to be related to peak load combined with poor query performance for specific sets of circumstances.

第二次的：

The following day, we saw the same peak traffic pattern and load on mysql1. We were not able to pinpoint and address the query performance issues before this peak, and we decided to proactively failover before the issue escalated.

第三次的：

While we had reduced load seen in the previous incidents, we were not fully confident in the mitigations.

In this third incident, we enabled memory profiling on our database proxy in order to look more closely at the performance characteristics during peak load.

到最近第四次的：

In order to reduce load, we throttled webhook traffic and will continue to use that as a mitigation to prevent future recurrence during peak load times as we continue to investigate further mitigations.

可以看到基本上還沒完，之後再遇到問題時應該還是會把 webhook traffic 拿出來開刀...

PostgreSQL 15 將可以對透過 UNIQUE 限制 NULL 的唯一性了

看到「Waiting for PostgreSQL 15 – Add UNIQUE null treatment option」這篇文章裡面提到 PostgreSQL 對 UNIQUE 多加了一些功能進去：「Add UNIQUE null treatment option」。

The SQL standard has been ambiguous about whether null values in unique constraints should be considered equal or not.  Different implementations have different behaviors.  In the SQL:202x draft, this has been formalized by making this implementation-defined and adding an option on unique constraint definitions UNIQUE [ NULLS [NOT] DISTINCT ] to choose a behavior explicitly.

This patch adds this option to PostgreSQL.  The default behavior remains UNIQUE NULLS DISTINCT.  Making this happen in the btree code is pretty easy; most of the patch is just to carry the flag around to all the places that need it.

The CREATE UNIQUE INDEX syntax extension is not from the standard, it's my own invention.

I named all the internal flags, catalog columns, etc. in the negative ("nulls not distinct") so that the default PostgreSQL behavior is the default if the flag is false.

Reviewed-by: Maxim Orlov 
Reviewed-by: Pavel Borisov 
Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com

以往針對某個欄位下 UNIQUE 後，雖然同樣的值是無法 INSERT 進去，但 NULL 則是個例外，是可以塞多次進去的。

現在則是提供選項指定對 NULL 的解讀了；預設還是保留原來行為的 UNIQUE NULLS DISTINCT (把每個 NULL 都當作不同的值看待)，特別指定後會變成 UNIQUE NULLS NOT DISTINCT (把每個 NULL 都當作一樣的值，進而被 UNIQUE 條件限制)。

在下一個版本的 PostgreSQL 15 就會出現這個功能了...

用 PostgreSQL 的 int4range 與 GiST

發現自己根本還不熟悉 PostgreSQL 的特性，寫一下記錄起來。

產品上常常會有 coupon 與 voucher 之類的設計，這時候通常都會設定 coupon 或 voucher 的有效期間，在 MySQL 的環境下可能會這樣設計：

CREATE TABLE coupon (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(255) NOT NULL,
  started_at INT UNSIGNED NOT NULL,
  ended_at INT UNSIGNED NOT NULL
);

另外是設計 index 的部份，在產品推出夠久後，通常是過期的 coupon 或 voucher 會比目前還有效的多，而還沒生效的 coupon 與 voucher 通常都不多，所以會設計成對 ended_at 放一組 B-tree index：

CREATE INDEX ON t1 (ended_at);

這個設計不算差，不過用了一些假設。

如果不想要用這些假設，可以改用 Spatial 的資料型態去模擬並且加上 index (使用到 LineString Class)，這樣就直接對 a < x < b 這類查詢更有效率，不過缺點就是可讀性會比較差。

在 PostgreSQL 這邊就有更清晰的資料結構來處理這些事情，主要是有一般性的 int4range 與 int8range 以及時間類的 tsrange、tstzrange 與 daterange (參考「Range Types」這邊有更多資料型態)，所以會變成：

CREATE TABLE coupon (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  code VARCHAR NOT NULL,
  active_at INT4RANGE NOT NULL
);

然後用 GIST 建立 index：

CREATE INDEX ON t1 USING GIST(active_at);

後續的 query 語法就用 <@ 的語法：

SELECT COUNT(*) FROM coupon WHERE 10000 <@ active_at;

塞了 10M 筆資料後的 table 可以看到本來需要的時間是：

Time: 779.542 ms

變成：

Time: 5.510 ms

不過缺點就是 SQLite 沒支援這些資料型態，對於 test case 就一定得跑個 PostgreSQL 起來測...

MangoDB：拿 PostgreSQL 當作後端的 MongoDB 相容層

在 Hacker News Daily 上看到「A truly Open Source MongoDB alternative」這個東西，在「MangoDB: An open-source MongoDB alternative (mangodb.io)」的討論也可以翻一翻。

MongoDB 最主要的問題就是 4.0 以及以後的版本變成 SSPL 後就不是 open source license 了。

而 MangoDB 是個 Proxy service，前端提供 MongoDB 的協定，後端則是 PostgreSQL。

在 GitHub 上的「MangoDB-io/MangoDB」這邊可以看到 MangoDB 主要是用 Golang 寫的，軟體授權則是 Apache License 2.0。

The core of our solution is a stateless proxy, which converts MongoDB protocol queries to SQL, and uses PostgreSQL as a database engine. This will be compatible with MongoDB drivers, and should work as a drop-in replacement to MongoDB in many cases.

這對於只有提供 MongoDB 當儲存層的軟體來說會是個替代方案。不過如果是自己開發的話，這其實也證明了可以直接用 PostgreSQL，基本上 MongoDB 的功能都可以在 PostgreSQL 上找到方案。

話說這個讓我想到 2019 年 AWS 推出 Amazon DocumentDB 的時候，大家都一直在猜 Amazon DocumentDB 是不是拿 PostgreSQL 前面加上一些東西 (參考「大家在猜 Amazon DocumentDB 的底層是不是 PostgreSQL...」)，後來也有人發現再更早之前就有人 PoC 過了：「A proof of concept MongoDB clone built on Postgres (github.com/jerrysievert)」，現在一般的公認應該就是，只是 AWS 沒有 open source 出來。

目前 MangoDB 還在早期的階段，但看起來這波應該會有一些能量進去幫忙...

Babelfish：讓 PostgreSQL 可以吃 Microsoft SQL Server 的協定

看到「Goodbye Microsoft SQL Server, Hello Babelfish」這篇，AWS 對 Aurora (PostgreSQL) 推出了可以吃 Microsoft SQL Server 協定的 Babelfish：

Today, we are making Babelfish for Aurora PostgreSQL available. Babelfish allows Amazon Aurora PostgreSQL-Compatible Edition to understand the SQL Server wire protocol.

查了一下資料發現是去年年底的時候發表的：「Want more PostgreSQL? You just might like Babelfish」，不過當時沒注意到這東西，大概是因為是 preview 的關係：

We are open sourcing Babelfish in 2021. Until then, you can use Babelfish on Amazon Aurora in a preview to see how it works and to get a sense for whether this is the right approach for you.

用起來不知道怎樣，但感覺很值得注意，目前雖然沒用到 Microsoft SQL Server 的東西，但以後遇到可以考慮看看...

除了在 AWS 上用以外，也可以自己到 GitHub 上拉 patch 回來上：「babelfish-for-postgresql」。

話說回來，PostgreSQL 被 AWS 拿來用在好多地方啊，先前大家也猜是 DocumentDB 後面是 PostgreSQL (參考「大家在猜 Amazon DocumentDB 的底層是不是 PostgreSQL...」這篇)，不知道之後會不會想要跟 Oracle 的律師打架...

Percona 連載到 PostgreSQL 存 JSON object 以及增加 Index 的方式了...

先前 Percona 的人在講 MySQL 存 JSON object 的方式，現在開始講在 PostgreSQL 裡存 JSON object，並且增加 index 的方式了：「Storing and Using JSON Within PostgreSQL Part One」。

這基本上就是不想用 MongoDB，但還是有需要極為彈性而選擇用 JSON object 的需求。

首先先先建立一個表格，這邊直接用 JSONB：

alice=# CREATE TABLE table1 (id SERIAL PRIMARY KEY, jb JSONB);

接著拿「A dataset of English plaintext jokes」這邊的 reddit_jokes.json 來玩，我先把 JSON 裡面的內容變成 JSON Lines 格式：

cat reddit_jokes.json | jq -c '.[]' > reddit_jokes.jsonl

然後 COPY 了十次，多一點資料，後面可以看效能：

alice=# COPY table1 (jb) FROM '/tmp/reddit_jokes.jsonl' CSV QUOTE e'\x01' DELIMITER e'\x02';
-- (repeat this command 10 times)

接著跑個 SELECT 看看速度，我跑了幾次大約都在 260ms 上下：

alice=# SELECT COUNT(*) FROM table1 WHERE (jb->>'score')::int = 10;
 count 
-------
 25510
(1 row)

Time: 264.023 ms

然後針對 score 生個數字的 index：

alice=# CREATE INDEX ON table1 (((jb->>'score')::int));
CREATE INDEX
Time: 1218.503 ms (00:01.219)

接著再跑 SELECT 下去，可以看到速度快超多：

alice=# SELECT COUNT(*) FROM table1 WHERE (jb->>'score')::int = 10;
 count 
-------
 25510
(1 row)

Time: 12.735 ms

另外也可以加 column：

alice=# ALTER TABLE table1 ADD COLUMN score INT GENERATED ALWAYS AS ((jb->>'score')::int) STORED;

然後可以看到速度也不快：

alice=# SELECT COUNT(*) FROM table1 WHERE score = 10;
 count 
-------
 25510
(1 row)

Time: 222.163 ms

幫他補 index：

alice=# CREATE INDEX ON table1 (score);

速度有變快，但不知道為什麼沒有 JSONB 的版本快：

alice=# SELECT COUNT(*) FROM table1 WHERE score = 10;
 count 
-------
 25510
(1 row)

Time: 81.346 ms

算是還蠻好用的，不過得學 JSON query 語法... (應該是還好)

快速產生 SQLite 資料的方式：一分鐘內產生十億筆資料

在「Towards Inserting One Billion Rows in SQLite Under A Minute」這邊看到作者想要在一分鐘內在 MBP 2019 上面寫 1B 筆資料進 SQLite，裡面有些方法還蠻值得玩一下的，這台 MBP 2019 機器的規格是：

The machine I am using is MacBook Pro, 2019 (2.4 GHz Quad Core i5, 8GB, 256GB SSD, Big Sur 11.1)

第一版是 Python 寫的，塞 10M 筆花了 15 分鐘：

In this script, I tried to insert 10M rows, one by one, in a for loop. This version took close to 15 minutes, sparked my curiosity and made me explore further to reduce the time.

加了五個 PRAGMA 的版本變成 100M 筆十分鐘：

The naive for loop version took about 10 minutes to insert 100M rows.

用批次處理則可以降到八分半：

The batched version took about 8.5 minutes to insert 100M rows.

再來是拿經典神器 PyPy 出來用，降到兩分半：

All I had to do was run my existing code, without any change, using PyPy. It worked and the speed bump was phenomenal. The batched version took only 2.5 minutes to insert 100M rows. I got close to 3.5x speed :)

接下來就是跳槽到 Rust 了，中間也有不少 tuning 相關的討論，但直接先跳到最後面好了... 最後 100M 只用了 33 秒：

I created a threaded version, where I had one writer thread that received data from a channel and four other threads which pushed data to the channel. This is the current best version which took about 32.37 seconds.

能用 PyPy 的地方還是可以考慮一下的...

用 Python 的 DuckDB 下 SQL 指令翻 Parquet 的資料

在「Querying Parquet using DuckDB」這邊看到 DuckDB 這個東西，裡面引用的文章是「Querying Parquet with Precision using DuckDB」，可以直接對 Parquet 格式的資料下 SQL 找資料。

先前好像有看到 DuckDB 但沒有太注意，剛剛再次看到，然後玩了一下還蠻有趣的。DuckDB 支援蠻多程式語言與資料格式，不過這邊文章拿 Python 與 Parquet 玩還蠻有趣的...

先把 Parquet 的範例資料抓下來，然後透過 pip 裝 duckdb：

cd /tmp; wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_04.parquet; pip install -U duckdb

然後進到 Python 3 的互動界面：

>>> import duckdb
>>> print(duckdb.query("SELECT COUNT(*) FROM 'taxi_2019_04.parquet' WHERE pickup_at BETWEEN '2019-04-15' AND '2019-04-20'").fetchall())
[(1276565,)]

然後在範例裡面，檔名的部份還可以用 *，看了一下說明，底層是 glob 類的用法：

DuckDB supports the globbing syntax, which allows it to query all three files simultaneously.

文章裡有提到速度比 Pandas 快很多，不過我覺得這好像不太能這樣比，會拿 Pandas 出來的時候常常是其他用法，但至少看起來速度是個 DuckDB 在意的點。

不過反而馬上想到的是，之後處理 CSV 之類的檔案應該也會試看看 DuckDB...

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.

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