Slide 1

Slide 1 text

PostgreSQL vs Elasticsearch -ファセットカウント編- 籏野 拓 @フォルシア株式会社
 2022.02.15 FORCIA Meetup#4


Slide 2

Slide 2 text

自己紹介 ● 籏野 拓 (Taku Hatano) ○ 新卒4年目 ● ソフトウェアエンジニア@フォルシア株式会社 ○ 福利厚生系アプリ中心に自社プロダクトもちらほら ● 活動領域 ○ webアプリケーション (TypeScript, Node.js, React, Next.js, PostgreSQL) ○ インフラ関連(Ansible, AWS, k8s, docker) 2

Slide 3

Slide 3 text

フォルシアの検索 3

Slide 4

Slide 4 text

What is Spook®? 4 各企業が独自に持つ膨大で複雑なデータに合わせて、 最適な検索を実現するための「技術基盤」

Slide 5

Slide 5 text

Spook®の特長 5 検索対象の属性を軸に絞り込みを行う →PostgreSQLの独自関数を利用し、 より高速な検索を実現

Slide 6

Slide 6 text

さらなる展開 6 近年、キーワード検索への需要の高まりも感じている ↓ の利用検討中

Slide 7

Slide 7 text

PostgreSQL vs Elasticsearch 7

Slide 8

Slide 8 text

キーワード検索編 普通はElasticsearchの方が5倍早い ● C言語による拡張機能(extension) ○ システム開発者向け ● 形態素解析器を呼び出せるが 基本的に自作 ● Javaによる拡張機能(plugin) ○ 応用的・ユーザー志向 ● キーワード検索に必要な機能 ワンストップで提供 8 [参考] FORCIA CUBE「Elasticsearch vs. PostgreSQL」 https://www.forcia.com/blog/001551.html

Slide 9

Slide 9 text

ファセットカウント編 9 「ファセットカウント」 →条件で絞り込む際に  該当の条件が何件であるかを事前に表示する 実際にPostgreSQLとElasticsearchで速度比較してみた

Slide 10

Slide 10 text

対象データ 10 ● 1レコードが複数のカテゴリを持つようなデータを対象とする。 ● カテゴリは1~100のランダムな整数 ● 1レコードあたり1~10のランダムな数のカテゴリをもつ ● postgresはint配列、elasticsearchはNested Typeでデータを持つ { "_index" : "search", "_type" : "_doc", "_id" : "eFHgw34BVdsiHW0pykMc", "_score" : 1.0, "_source" : { "categories" : [{ "id" : 37}, {"id" : 83}] } } id | categories ----+--------------------------------- 1 | {69,87,96,98}

Slide 11

Slide 11 text

集計クエリ 11 select unnest(categories) as category ,count(*) from search group by category order by category 
 { "size": 0, "aggs": { "categories" : { "nested": { "path": "categories" }, "aggs": { "count": { "terms": { "field": "categories.id" , "size": 101, "order": { "_key": "asc" } } } } } } }

Slide 12

Slide 12 text

計測方法 12 ● 以下を10回繰り返して実行時間の平均を算出する ○ 1万/10万/100万行のランダムなデータを生成する ○ それぞれにクエリを投げて実行時間を計測 ※あるアプリでは施設数3万件、プラン数120万件くらい

Slide 13

Slide 13 text

計測結果 13 レコード数 PostgreSQL平均 Elasticsearch平均 10000 0.028s 0.021s 100000 0.130s 0.039s 1000000 1.429s 0.373s ● レコード数が少ない場合は同等のスピード ● レコード数が多いとElasticsearchに軍配 ○ NestedTypeは配列の要素ごとに異なるドキュメント(≒レコード)として保 持されているので展開の必要がない

Slide 14

Slide 14 text

補足: Elasticsearchのオブジェクト配列 14 (公式: https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html ) 単純なオブジェクトの配列でマッピングされている場合、 フラットな構造に展開されてインデックスされる PUT my-index-000001 /_doc/1 { "group" : "fans", "user" : [ { "first" : "John", "last" : "Smith" }, { "first" : "Alice", "last" : "White" } ] } { "group" : "fans", "user.first" : [ "alice", "john" ], "user.last" : [ "smith", "white" ] } firstとlastの組み合わせが維持されない first = “Alice” and last = “Smith” がヒットしてしまう →

Slide 15

Slide 15 text

補足: Elasticsearch Nested Type 15 (公式: https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html ) Nested Typeを利用することで元の組み合わせを保持してインデックスされる { "group" : "fans", "user.first" : "john", "user.last" : "smith" } { "group" : "fans", "user.first" : "alice", "user.last" : "white" } 「nested documents are indexed as separate documents」 ←インデックスされるイメージ first = “Alice” and last = “Smith” はヒットしない

Slide 16

Slide 16 text

Spook®でも計測を行ってみた 16 レコード数 PostgreSQL平均 Elasticsearch平均 Spook®平均 10000 0.028s 0.021s 0.010s 100000 0.130s 0.039s 0.022s 1000000 1.429s 0.373s 0.090s ● PostgreSQLでもチューニングにより十二分な速度が出る

Slide 17

Slide 17 text

まとめ 17 ● ElasticsearchのAggregationsという機能を使うことで GROUP BY相当の集計ができる ● チューニングなどしなくてもある程度の速度が出る ○ デフォルトでフラットな構造にしてインデックスしてくれるため ● PostgreSQLでもチューニングを行うことで高速化は可能

Slide 18

Slide 18 text

EOF