把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo

Hacker News Daily 上看到「Static torrent website with peer-to-peer queries over BitTorrent on 2M records (boredcaveman.xyz)」這個討論,作者試著在網頁上跑 SQLite + VFS + WebTorrent

這好像是這陣子一連串的 combo 技累積出來的東西:

  • 首先當然是把 SQLite 丟到網頁上跑的「sql.js」,這個專案比較久了,2019 年有第一個 release;
  • 然後最近有人透過 HTTP Range (Byte serving) 實做 SQLite VFS 的「sql.js-httpvfs」,這樣就不需要一次下載整包 SQLite;

接下來就是文章作者把 HTTP Range 換掉,改用 BitTorrent 的 pieces 來處理,在網頁端的話就順勢拿 WebTorrent 來用,對於很熱門的網站來說還蠻有趣的設計,但也可以預期網頁的反應速度應該不會太快,偏 PoC...

SQLite 3.37.0 以及 STRICT table 的設計

Hacker News 首頁上看到「SQLite Release 3.37.0 (sqlite.org)」,原文在「SQLite Release 3.37.0 On 2021-11-27」這邊。

這個版本引入了 STRICT Tables,先前在「SQLite 目前在規劃的 Strict Table,以及我從來不知道原來可以這樣惡搞...」這邊有提過。

官方給出來的範例是這樣,如果沒有要求 STRICT 的話,可以看到各種變化:

CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123

加上 STRICT 後就會與「預期」的結果比較接近:

CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'

對於希望 database 在處理資料嚴謹一點的人來說,應該是個不錯的新功能,但畢竟不是預設值,對於剛跨進來用的人應該還是有中獎機會 XD

用 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 這邊就有更清晰的資料結構來處理這些事情,主要是有一般性的 int4rangeint8range 以及時間類的 tsrangetstzrangedaterange (參考「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 起來測...

SQLite 目前在規劃的 Strict Table,以及我從來不知道原來可以這樣惡搞...

Hacker News Daily 上看到「STRICT Tables」這篇,在講 SQLite 目前在規劃 strict table,對應的討論可以參考「Strict Tables – Column type constraints in SQLite - Draft (sqlite.org)」這邊。

我在 draft 文件開頭看到這個驚人的事實:轉型失敗的時候會直接寫進去,不是錯誤或是 0 或是 NULL 之類的值 XDDD

For example, if a table column has a type of "INTEGER", then SQLite tries to convert anything inserted into that column into an integer. So an attempt to insert the string '123' results in an integer 123 being inserted. But if the content cannot be losslessly converted into an integer, for example if the input is 'xyz', then the original string is inserted instead. See the Datatypes In SQLite document for additional information.

實際上測試建了一個表格測試:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY NOT NULL, col1 INTEGER);
sqlite> INSERT INTO a (id, col1) VALUES (1, 'a');
sqlite> SELECT * FROM a;
id          col1      
----------  ----------
1           a         

我果然跟 SQLite 不熟...

快速產生 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...

DuckDB

看到篇有趣的介紹,在講 DuckDB:「DuckDB」。

[I]t uses the PostgreSQL parser but models itself after SQLite in that databases are a single file and the code is designed for use as an embedded library, distributed in a single amalgamation C++ file (SQLite uses a C amalgamation).

看起來是個以 OLAP 為中心而設計出來的資料庫,然後在 Python 下可以直接透過 pip 裝起來。

看起來像是個用單機拼 throughput 的東西,但提供大家熟悉的界面。

Hacker News 上可以看到「DuckDB – An embeddable SQL database like SQLite, but supports Postgres features (duckdb.org)」這邊給了不少方向,

Canonical 推出的 Dqlite (High-Availability SQLite)

第一眼看到的時候直接有種不知道 Canonical 在幹什麼的感覺,翻完說明後大概知道可以用的地方,但還是覺得範圍有點小:「Dqlite - High-Availability SQLite」。

一種使用情境是,在 embedded system 上面同步資料的一種方案... 吧?例如網路連線的頻寬或是品質受限,無法順利傳到 Internet 端的伺服器上,所以希望在本地端就可以解決一些事情,但又不方便在本地端直接弄個 PostgreSQL 出來?

Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.

另外一個是用到了 C 實做的 Raft 協定:

Dqlite (“distributed SQLite”) extends SQLite across a cluster of machines, with automatic failover and high-availability to keep your application running. It uses C-Raft, an optimised Raft implementation in C, to gain high-performance transactional consensus and fault tolerance while preserving SQlite’s outstanding efficiency and tiny footprint.

讓 IoT 裝置參與 Raft 嗎... 好像只能說有趣... XD

SQLite 的全文搜尋功能

算是補充之前看過,但一直沒研究的東西...

看到 Simon Willison 的「Exploring search relevance algorithms with SQLite」這篇才花些時間看了一下 SQLite 的搜尋功能。

看起來不論是 FTS4 或是 FTS5 都沒有處理 CJK 文字的功能,可能要當作 unigram 之類的方式處理 (參考「Unicode support for non-English characters with Sqlite Full Text Search in Android」這篇),不過排名的部份有支援 BM25,整體看起來應該是還算堪用。