PostgreSQLでもカウンター処理を簡単に実装できる！

Computer, Database, Development 1月 28, 2016 #PostgreSQL
(Last Updated On: 2018年8月13日)

PostgreSQL 9.5がリリースされました。これに含まれるUPSERT機能を使えばPostgreSQLでも簡単にカウンター処理を実装できます。以前でもトリガーやルール、CTE(Common Table Expression)を使って実装できましたが、追加されたUPSERT機能を使った方が簡単です。

UPSERT機能とは簡単に言うと「INSERTできる場合はINSERT、できない場合はUPDATEする」という機能です。

PostgreSQLのUPSERTサポート

PostgreSQLのUPSERTはINSERT文の拡張として実装されています。MySQLユーザーの方ならINSERT ON DUPLICATE KEY UPDATEと同じような機能／構文だと言うと理解り易いと思います。

PostgreSQL 9.5から追加されたON CONFLICT [ conflict_target ] conflict_actionを利用するとUPSERTを行えます。

PostgreSQLのUPSERTでカウンターを作る

細かい説明は抜きにしてページへのアクセスをカウントするテーブルとSQL文を紹介します。

カウンタテーブル

CREATE TABLE counter (
  url text PRIMARY KEY,
  count int8
);

UPSERTをするにはUNIQUE制約があるキー（プライマリーキーなど）が必要です。カウンタテーブルにはプライマリーキーがあるので十分です。

UNIQUE制約の名前を知る必要があるのでpsqlの\dコマンドで確認します。

yohgaki@127 ~=# \d counter
  テーブル "public.counter"
┌───────┬────────┬──────────┐
│  列   │   型   │  修飾語  │
├───────┼────────┼──────────┤
│ url   │ text   │ not null │
│ count │ bigint │          │
└───────┴────────┴──────────┘
インデックス:
    "counter_pkey" PRIMARY KEY, btree (url)

conflict_actionにはDO UPDATE SETが利用でき、UPDATE文と同じように更新するカラムを指定できます。更新する値には式やサブクエリも利用できます。カウンターの作成には式を利用します。

トランザクションを利用しなくても一文で実行できるので、とても簡単にカウンターを増加させることができます。

INSERT INTO counter (
  url, 
  count
) VALUES (
  'http://example.com/the-page.html',
  1
) ON CONFLICT ON CONSTRAINT counter_pkey DO UPDATE SET
  count = counter.count + 1;

このクエリを何度か実行すると、実行した回数分だけcountが増加します。

yohgaki@127 ~=# SELECT * FROM counter;
┌──────────────────────────────────┬───────┐
│               url                │ count │
├──────────────────────────────────┼───────┤
│ http://example.com/the-page.html │     4 │
└──────────────────────────────────┴───────┘
(1 行)

時間: 0.257 ms

何が便利なのか？

UPSERT機能を利用しない場合、

  • SELECTして行の有無を確認
  • 行がない場合はINSERT
  • 行がある場合は、SELECTで取得したcount値に+1してUPDATE

という処理になります。別にそれ程便利になっていない、と思うかも知れませんがかなり便利になりました。

UPSERTが無いと、複数のSQL文を実行するのでトランザクションが必要です。トランザクションが無いとカウンタの増加漏れが発生します。トランザクションを利用してもデフォルトの分離レベルだとUPDATEに失敗する場合があります。FOR UPDATEを使ったりシリアライザブル分離レベルでも同じURLを挿入しようとして失敗する場合があります。失敗するケースがあるので、上記の処理に加えてトランザクションが失敗した場合にトランザクションを再実行する処理が必要になります。

UPSERTなら一文なのでアトミックに実行されます。これだけで完結するのでトランザクションが！と考えなくても、使わなくても問題ありません。

まとめ

MySQLには昔から同じような機能がありましたが、この機能を使おうと思ったことが無かったので「トランザクションでやれば十分」と思っていました。UPSERTの単純かつ便利なユースケースは？と考えてみると、昔から使っている方には当たり前だと思いますが、カウンターのような簡単な処理が簡単に記述できると思いついたので紹介しました。

UPSERT機能のリクエストが多かった、とは聞いていましたが確かにあるとかなり便利です。色々便利に使うことにします。

とは言ってもカウンターの様に単純な物で対象が少数で限定されていれば、最初にINSERTさえしてしまえば

UPDATE 
  counter 
SET 
  count = (SELECT 
             count 
           FROM 
             counter 
           WHERE 
             url = 'http://example.com/the-page.html') + 1;

としてトランザクションなしに実装することも可能です。ベンチマークを取ってみると面白いと思いますが、省略します。

 

投稿者: yohgaki