PostgreSQLでソータブルなUUIDを生成する

PostgreSQLでIDを生成する方法は連番なIntegerをSEQUENCEで生成するか、UUIDをgen_random_uuid()等でランダム生成するかが一般的だと思われます。WebサービスではIDが連番だとURLからサービス規模を知られてしまうことが懸念されます。そこで私はランダムなUUIDをIDとして利用することが多いです。

一方、gen_random_uuid()で生成されたUUIDは順序性はなく、IDだけで生成順にはソートできません。この問題に対して、ULIDはミリ秒単位のepoch time(48bit) + ランダム(80bit)を用いることで、ランダム性と順序性を両立させています。ID生成におけるランダム性と順序性の比較はhttps://qiita.com/kawasima/items/6b0f47a60c9cb5ffb5c4 が詳しいです。

残念ながらPostgreSQL 11.2以前はULIDには対応していません。そこで、ULIDと同様の方法でUUIDを出力する関数を作ってみました。

create function gen_random_ulid() returns uuid as $$
select substring(
  (
    substring(
      decode(
        to_hex(
          int8'4611686018427387904' + ( -- b'0100000000000000000000000000000000000000000000000000000000000000'::bigint -- to_hexしたときに上位桁が0だと破棄される。必要な桁まで削除されてしまうのを防ぐ。
            (
              extract(epoch from now()) -- create epoch time
              * 1000 -- ミリ秒化 
            )::bigint::bit(64) -- マイクロ秒を切り捨て
            & int8'281474976710655'::bit(64) -- 下位48bitのみを残すため。10889年まで枯渇しないので、今のところなくても良い
          )::bigint
        )
      , 'hex') -- byteaで扱う
    , 3) -- 上位16bitを削除
    || gen_random_bytes(10) -- ulidの下位80bitの生成
  )::text
, 3)::uuid; -- bytea -> textしたときの\xを削除しuuidにキャスト
$$ language sql;

簡単ですね。作成した関数を利用して生成したIDを持つレコードに対するクエリは次のように記述できます。

app=# create table test (id uuid primary key default gen_random_ulid());
CREATE TABLE
app=# insert into test select from generate_series(1,1000);
INSERT 0 1000
-- n回繰り返す

-- ソート
app=# explain analyze select * from test order by id desc;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan Backward using test_pkey on test  (cost=0.29..860.29 rows=24000 width=16) (actual time=0.045..6.179 rows=24000 loops=1)
   Heap Fetches: 0
 Planning Time: 0.147 ms
 Execution Time: 8.114 ms
(4 rows)

-- 範囲検索
app=# explain analyze select * from test where id >= '016ae321-dc38-0000-0000-000000000000';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.29..43.21 rows=1081 width=16) (actual time=0.069..0.714 rows=1000 loops=1)
   Index Cond: (id >= '016ae321-dc38-0000-0000-000000000000'::uuid)
   Heap Fetches: 0
 Planning Time: 0.182 ms
 Execution Time: 0.925 ms
(5 rows)

ソートも検索もIndexのみで検索できるので高速ですね。気になるのは関数の性能です。gen_random_uuid()と比較してみました

app=# create table test (id uuid primary key);
app=# \timing
app=# insert into test (id) select gen_random_uuid() from generate_series(1,1000000);
INSERT 0 1000000
Time: 5149.780 ms (00:05.150)
app=# insert into test (id) select gen_random_ulid() from generate_series(1,1000000);
INSERT 0 1000000
Time: 7144.387 ms (00:07.144)                

桁が変わるほど性能が落ちるわけではなく、実用的だと考えます。

以上です。