int4range

發現自己根本還不熟悉 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 起來測...

Tag: int4range

用 PostgreSQL 的 int4range 與 GiST