PostgreSQL hashインデックスでUNIQUE制約
TEXT型とか最近増えてきたUUID型を使ってると、インデックスが肥大化するんだけど
これはb-treeインデックスで、そのカラムのデータがそのまま格納されてるから
でUUIDや訳あってTEXT型をPRIMARY KEY使う事があることがあるんだけど
数百万件データを入れるとインデックスがバカバカしくなってくる
で、hashインデックスを使えるとPostgreSQLのhashインデックスは4バイトになるので劇的に小さくなるはず！なんだけど、UNIQUE属性が付けられない
で、PRIMARY KEYの要件ってUNIQUEとNOT NULLなので、それを実現してやればいいわけだ
で、ふと思いついたのが EXCLUDE 制約で、調べてみると
PostgreSQL 11.5文書:CREATE TABLE — 新しいテーブルを定義する
B-treeやHashインデックスを排他制約で使用することは許容されますが、そうすることにあまり意味はありません。 これが通常の一意性制約より良いことは何もないからです。 このため現実的にはアクセスメソッドは常にGiSTもしくはSP-GiSTとなります。
b-treeやhashでそれを使うメリットは少ないみたいなことが書いてあるけど、大規模で、hashの場合メリットあるやろって事で、試してみる
ALTER TABLE data_table ADD EXCLUDE USING hash (id_column WITH =);
ALTER TABLE data_table ALTER COLUMN id_column SET NOT NULL ;
行けるわ
UNIQUE制約だとインデックススキャンした時点で確定するけど
EXCLUDE制約だと、INSERTとかのたびに、SELECTが走って、hashインデックススキャン、見つからなければインサートだけど、見つかれば、実テーブルデータスキャンしてRECHECKしてってなるから遅いって事なんだろうな
動作自体は遅いからb-treeやhashインデックスを使うのは、向いてないって事なんだろうな
でもインデックスが肥大化する環境では有用だと思う
大規模になると、早く動く事より、やや遅くてもデータが増えた時に遅くなりにくい方法の方がいいのだ
