用 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 起來測...

Amazon Redshift 可以處理座標資料了

這一個月 AWS 因為舉辦一年一度的 AWS re:Invent,會開始陸陸續續放出各種消息... 這次是 Amazon Redshift 宣佈支援 spatial data,這樣一來就能夠方便的處理座標資料了:「Using Spatial Data with Amazon Redshift」。

支援的種類與使用的限制可以在官方的文件裡面看到,也就是「Querying Spatial Data in Amazon Redshift」與「Limitations When Using Spatial Data with Amazon Redshift」這兩篇。

其實限制還蠻多的,而其中這條看起來有點痛,擴充性受到限制:

Data types for Python user-defined functions (UDFs) don't support the GEOMETRY data type.

看起來暫時是先支援計算的部份,之後看看有沒有機會變成原生型態的支援,這樣使用起來才會比較順...

Amazon Aurora (MySQL) 推出相容於 MySQL 5.7 的版本

Amazon Aurora (MySQL) 推出相容於 MySQL 5.7 的版本了:「Amazon Aurora is Compatible with MySQL 5.7」。

不過網站上的介紹還沒更新:

Amazon Aurora is a relational database service that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. The MySQL-compatible edition of Aurora delivers up to 5X the throughput of standard MySQL running on the same hardware, and is designed to be compatible with MySQL 5.6, enabling existing MySQL applications and tools to run without requiring modification.

5.7 其中一個賣點在於支援 Spatial index (透過 R-tree),不過 AWS 的版本看起來是自己用 B-tree 加上 Z-order curve 實做:「Amazon Aurora under the hood: indexing geospatial data using Z-order curves」。

我覺得看看就好啦,拿 244GB RAM 的 r3.8xlarge 跑 1GB 的 data set,當大家是傻逼嗎...

Amazon Aurora 的 MySQL-Compatible Edition 開始提供相容於 MySQL 5.7 的服務

Amazon AuroraMySQL-Compatible Edition 開始支援相容 MySQL 5.7 的服務:「Announcing Preview of Amazon Aurora with MySQL 5.7 Compatibility」。

Amazon Aurora with MySQL 5.7 compatibility offers enhancements such as JSON support, spatial indexes, generated columns and performance improvement of 5x over MySQL 5.7, and up to 10x for spatial datasets.

雖然目前還在 Preview (需要申請才能用),而且開放區域有限,但總算是有消息了:

The preview is currently available in US East (N. Virginia), US East (Ohio), US West (Oregon), and Canada (Montreal).

會一定要 5.7 的,主要的需求應該是在 spatial index 上吧... JSON 反倒還好。

MySQL 5.7.4

在「The MySQL 5.7.4 Milestone Release is available」這篇可以看到 MySQL 5.7.4 的消息。除了 InnoDB 的改善外,可以看到對 AES 加密的功能 (AES Encryption Modes)。

不過...

Historically, and still used as defaults in 5.6 and 5.7, we are using a relatively small key size (128 bits, corresponding to “SECRET” according to NSA) and block mode (ECB, encrypting equal blocks with equal code blocks) to calculate the cipher.

居然是支援 ECB,這會不會驚爆我的眼球啊,我以為最少是 CTR...

ECB 代表相同內容的 block 就會被加密成相同的密文,這樣就有很多可以攻擊的方式了。而 CTR 至少可以抵抗這一點...

另外一個賣點是「InnoDB Spatial Indexes in 5.7.4 LAB release」,目前只支援二維資料:

Currently, InnoDB spatial index supports only two dimension data, but we do have plan to extend to multi-dimension. In addition, we are doing more performance tuning to make it more efficient.

R-tree 實做的,畢竟是個開始...

MySQL 5.7...

Oracle 的「MySQL :: MySQL 5.7 Reference Manual :: 1.4 What Is New in MySQL 5.7」列出 MySQL 5.7 預定會有的功能。由於還在發展階段,這頁還會繼續變動。

針對 ALTER TABLE 有不少改善,以下的條件下 ALTER TABLE 將不會產生 temporily table (不會卡住):

  • table 改名。
  • column 改名。
  • column 改 default value。
  • enum 或 set 在不修改原來值的情況下增加值。
  • partition 相關操作。
  • index 改名。
  • index 新增與刪除。(僅限 InnoDB)

幾個常見的操作變得更簡單了,pt-online-schema-change 的功能會慢慢被整合回 MySQL。

然後 InnoDB 要支援 spatial data types 了,不過 index 還沒支援... 不知道有沒有機會看到 :o