PostgreSQLの全文検索は遅いって嘘！？爆速チューニング術でElastic並みの性能を叩き出す！

メリットは、使いやすさのために保存したいけど、diskとかmemoryを圧迫したくないときかな。例えば、vectorを事前に計算してindex化しておけば、サイズが2倍にならずに済む。

それってPostgresで関数の結果でindex作るのと同じメリットだよね。

それってトリガーのsyntax sugarでしょ。そんなに大きなメリットじゃないと思うな。

disk容量が増えるだけじゃなくて、トリガーとかでメインcolumnと同期する必要があるし、バックアップも大変になるよね。なんで「疑問に思った」の？デメリットしか見当たらないんだけど。weighted queriesとかjoined tablesで検索する必要があるなら別だけど。

generated columnだから、更新のoverheadはないけど、PGのgenerated columnは全部保存されるんだよね。text searchのcorpusは、ソースcolumnに1回、tsvectorとしてindexに1回、generated columnとして1回保存されることになる。disk容量が50%も増えるってことだよ。

そのextra spaceを更新するoverheadはやっぱりあるよね。

それは確かに問題だね。pg_searchに有利な点だ。
PG 18ではvirtual generated, indexable columnsができるから、pg_searchのメリットはなくなるかもね。

Postgres FTSを10年以上推奨してるよ。Solr searchを置き換えてから、メンテナンスが楽になったし、queryの柔軟性が増したし、速度もほとんど変わらなかった。
マジ最高。
Elasticはユースケースが違うけど、pgはほとんどのワークロードで十分だよ。

Postgres searchを一番大きい規模で使ったのはどのくらい？

ユーザー30万人くらいのサイトで、まだスケールアップしてたんだって。

Postgres FTSをわざと誤解させるか、基本的なドキュメントを読んでないかのどっちかだと思っちゃうな。あと、順番逆の方が良かったかもね。Hanlon’s Razorって言うじゃん？

ドキュメント読まずにナイーブなことやっちゃう人向けの記事だと思えば、まあアリかもね。そういう人結構いるし。

＞誰かがこの間違いを犯すのは、Postgres FTSを意図的に誤って伝えるか、基本的なドキュメントを読んでいないかのどちらかだとしか考えられない。“システム管理者のノリ、マジ卍”

2008年くらいからpg full text使ってるけど、SOLRとかElasticSearchも使って大規模な検索とかレコメンドもやってきたよ。postgres full text searchの問題は、遅いんじゃなくて柔軟性がないこと。ちょっと検索をチューニングしたいだけでも無理ゲー。サブストリングすら無理だし、カスタムトークナイズも無理。トークナイズパイプラインなんてないに等しいし。SolrとかElasticSearchなら設定で複雑なインデックスとか検索処理できるのに、postgresはマジで何もない。postgresのデベロッパーは他のソリューション触ったことないんじゃないかな。トークナイズとかフィルター設定の話しても、わかってないっぽいし。フィールドを連結してインデックス作るのもマジめんどい。フィールドの重み付けとかもできないし。マジおもちゃレベル。

どっちの記事にもexplain planがないのが残念。クエリがインデックス使ってれば、tsvectorのリチェックはマッチしたやつだけだし、ベンチマーククエリはLIMIT 10だからリチェック少ないよね？Edit: 確かにクエリの述語には2つのginインデックスの条件があるから、プランナーは行ごとにリチェックすれば回避できるはずなのに、片方のインデックスのマッチを全部リチェックすることを選んでるのか。

マジでなんでみんなpostgresに全部入れようとするの？ベクトル検索とか、フルテキスト検索とか、ワークロードオーケストレーションとか、キューとか、マジで意味わからん。

データベースと別の検索インデックス（Elasticsearch、Solr、Xapian）を使うシステムをいくつか作ったけど、一番大変なのは検索インデックスとデータベースの同期を保つこと。PostgreSQL、MySQL、SQLiteに組み込まれてる検索エンジンを使うと、この問題がマジで楽になる。

規模によるよね。自分一人でやるなら、一つのシステム（データベース、モノレポ、何でも）が100%ベスト。複数のシステムを大規模でやったこともあるけど、エンジニアがいっぱいいて、コストに見合うならアリ。全部一つのシステムにまとめると、いつか崩壊するけど、できるうちはマジ最高。

postgresqlで全部やるってことは、必ずしも一つのデータベースサーバーでやるって意味じゃないよ。検索、キュー、アナリティクス（カラムナー）とか、それぞれ別のサーバーでやってもいいし、全部バニラのトランザクショナルなpostgresqlサーバーからレプリケーションすればいいじゃん。アプリに必要なのは一つの技術だけで、必ずしも一つのサーバーじゃないんだから。

検索で一番大事で難しいのはETLだよね。Extract、Transform、Load。Loadはデータを処理場所に置くこと。Transformはアルゴリズムを適用して処理すること。Loadはストアに突っ込むこと。Transformがマジ重要。ExtractとLoadは簡単で、数行で実装できる。Transformはアプリ固有のビジネスロジック。クエリするのはDBのストアとは違うもの。計算コストが高いし、スケールすると大変。アプリサーバーやDBでやるのはNG。検索はインデックス時に計算しておくとクエリが楽になる。言語ごとのトークン化、Page Rank計算、正規化、セマンティックベクター、データの強化とか色々ある。これらを全部省くとシンプルになるけど、検索品質は下がるかもね。PostgreSQLを検索インデックスにする場合でも、ETLパイプラインでアプリDBと分離しないとダメ。Elasticsearchの方が機能は多いけど。高速化だけじゃなくて品質も大事。ETLをちゃんとやれば、DBと検索インデックスを同じ場所に置く意味がなくなるから、もっと最適なものを選べばいい。

ペナルティなしで余計なサービス追加を避けられるなら、スキル習得やDevOps担当の雇用、サービスの維持とかも不要になる。サービス追加のコストは過小評価されがち。エンタープライズレベルなら包括的なシステムの価値は理解できるけど、30000人以上の企業で必要なサービスを全部まとめて、DBとWebアプリサーバー1台にできたら、コスト削減できると思う。

うちの会社で数年前にシステム棚卸ししたら、DB（テーブルじゃなくて！）の数がエンジニアの数と同じくらいだった。QAとか本番環境含めて。その棚卸しチームも、データを入れるために新しいDB作ったし、QAとテスト環境も作った。多分、古いシステムのために前のDBも残ってると思うよ。

それ、当たり前だよー。DBの数がエンジニアの100倍とか1000倍の会社で働いたことあるし。MySQLだとスキーマと1対1だったりするかもね。

ElasticsearchとPG使ってるけど、速くて良い感じ。でも、データがPGにあるのに、別のサーバーにインデックスする必要があるから、色々面倒。うちの場合は、そこまでやる価値なかったかも。PGを最適化すれば、同じくらい速くなったと思う。キューをPGに移したら、トランザクションで更新とジョブ開始をラップできて、同期の問題も減った。キャッシュの無効化は難しい問題の一つ。

名前付けとオフバイワンエラーもね。

RDBMSなら何でも同じだけど、速くて簡単になるってこと。ORMでデータを操作してたら、SQL一発で終わる処理が、オブジェクトを複数のコンピューターシステムに渡すのに数時間かかった。FTSエンジンをSQLエンジンに入れれば、APIとかフレームワークとかサードパーティとかレイテンシとか全部避けられる。SQLは複雑なデータを構造化するのに最適な方法だと思う。SQLビューで済むものを、ネストされたイテレータの山にするのはマジ勘弁。コードは少ない方が良い。

＞There is ZERO honor in offloading what could have been a SQL view into a shitty pile of nested iterators somewhere. I don’t understand where any of this energy comes from. The less code the better. It’s pure downside.”
激しく同意。ORMを使う理由を聞くと、SQLを知らないとか、SQLはデータアナリストの仕事だと思ってるみたい。オブジェクト指向とか構造体とかポインタとか配列じゃないと、エンジニアリングじゃないと思ってる。SQLの宣言的な性質が嫌なのかも。

これな。ORMはマジでクソ。

ちょっと前にまた経験したけど、マジでそう。アプリケーション管理インターフェースを置き換えてて、設定パラメータがたくさんあって、リレーショナルデータベースに最適だった。でも、フロントエンドから送られてくるドキュメントとして扱いたかったから、GORMを使った。最初は良かったけど、すぐに破綻した。特にデータモデルが深くネストされてると。GORMでXをどう解決するんだ？ってなって、ドキュメントも少ないし、コミュニティのメンバーもすぐ燃え尽きる。

新しいサービスをメンテするのはマジで面倒くさいんだよね。Postgresと他のDBへのアトミックコミットができないのも最悪。

分散システムの問題を避けるのがマジ重要。分散システムってマジで構築が難しいから、Postgresを垂直スケールしまくって、どうしても無理ってなるまで頑張る。

Postgresで分散DBも作れるんだよ。例えば、複数の書き込みノードがあるDBなら、イベント配送モデルを実装して、論理レプリケーションでサーバーがイベントを発行して他のサーバーをサブスクライブすればOK。あとは競合解決ルールを実装する必要があるね。Postgresの型システムを使えばCRDTシステムも作れそう(誰かもうやってるかも)。

IBMのメインフレームは君のために作られたようなもんだよ。めっちゃ信頼性の高いコンピューターがあると思ってくれ。ホットスワップ可能なディスク、RAM、CPUとか、冗長電源とか、冗長ネットワークスタックとか。OSは再起動する必要がないように設計されてるし。IBMは今でも数十億ドルも売り上げてるんだぜ。

「全部1つにまとめるのは不安だから分散させたい」って言われるけどさー。10個のノードに状態を分散させるのはマジで簡単で、最高だよねー(棒)。

Postgresは、データベースっぽいこと全般が得意だからね。全部80%くらいの出来だとしても、最初に手を出すものとしてPostgresを選ぶのはアリだと思う。でも、自分がその20%に入ってないか確認するのを忘れがち。Postgresじゃなくて他のものを使うべきなのに、誰も確認してないってケースは結構あると思う。

PostgresはSQLで必要なことを全部やるのに最適なプラットフォームだし、パフォーマンスもマジで良い。PostgRESTを使えばREST APIもほぼ無料で手に入る(スキーマ設計は必要だけどね)。それに、Postgresは進化が早いし、開発者とユーザーのコミュニティも活発だから、これからもっと凄い機能が追加されるはず。(例えば、AIOスレッドを追ってるけど、パフォーマンスが大幅に向上するパッチが来る予定。)

新しいプロジェクトを始める時って、データ量が少ないうちは、プロジェクトが失敗したり、ボトルネックが予想と違ったりする可能性もあるから、理論的な最適化よりもプロダクトに集中した方が良い場合が多いんだよね。
今の職場では、簡単だし、コードが全部Postgresに繋がってるから、バイナリデータも含めて全部Postgresに突っ込んでた。RDSのストレージコストが高くなってきたから、S3とかDynamoDBにデータを移し始めたけどね。
それに、クラウドで便利なキューイングとかキャッシュの製品が簡単に使えるわけじゃない人もいるし。KafkaとかMongoDBとかをデプロイするよりも、1つの複雑なもの(どうせメンテする必要がある)を扱う方が良い場合もあるんだよね。
移行を簡単にするために、コードを抽象化しておくことを強くおすすめするよ。

MySQLも最新バージョンで同じことができるよ(ベクター検索)。でも、理由はシンプルさとコストだね。「Choose Boring Technology」って聞いたことある？Postgresは退屈。めっちゃ複雑だけど、パフォーマンス、信頼性、柔軟性が手に入る。マニュアルは1つ読めばいいし、言語も1つ覚えればいい(アプリ以外でね)。1つのことを深く知ればいいんだ。ESの管理は大変だし、やったことあるけど、Postgresも管理は大変。でも、1つの大変なことをマスターしてページングされるか、2つの大変なことをマスターしてページングされるか選ぶなら、前者を選ぶ。

みんながすぐに専門サービスにオフロードしちゃうような仕事を、Postgresは得意としてるんだよね。キュー、通知、スケジュールされたジョブとか。拡張機能で専門化もできるし。

データに近いサービスほど実装が楽で、速度も出やすいってことじゃないかなー。PostgresのFTSはマジ最高で、ベクトル検索とRAGを組み合わせると、お手軽なのにマジでいい感じになるんだよね。

経験はないんだけどさ、理論的にはツールは少ない方が組織にとっては良いんだよねー。[0]を見てみて。求人広告に「postgres」って書くだけで、「postgres, elasticsearch, tool x, tool y, tool z」とか書かなくて済むし。全部知ってるユニコーンを探したり（育てたり）しなくて済むじゃん？ただ、postgresは奥が深いから、postgresでXをやる専門家を探すとなると、結局同じことになっちゃうかもねー。でも、完全に新しいツールを学ぶよりは、postgresの専門知識を学ぶ方が楽だと思うよ。あと、何でもかんでもpostgresでやろうとするのは危険かも。データベースの中でJSONをクエリする必要が出てきたら、ちょっと考えた方がいいかもね。
[0] https://mcfunley.com/choose-boring-technology

Dimitri Fontaineの「The Art of PostgreSQL」にあった、>PostgreSQLをストレージレイヤーとしてではなく、同時データアクセスサービスとして考えるべき＞っていう意見に賛成だな。このサービスはデータ処理もできるんだよね。

みんなの意見で混乱してきたけど、データベースに全部突っ込む理由は、トランザクション処理ができるからだよ。セカンドシステムを導入したら、それはできなくなるんだよね。

分散ロックの仕組みを使えばできるかもだけど、正しくやるのは難しいよねー。

無料だし、安定してるし、優秀だし、どこにでもあるし。「なんで全部Pgに入れないの？」って話だよね。Pgに入れるべきじゃないのは、明らかに優れた代替手段があって、そっちの方がサービスを増やす価値がある場合だけ。例えば、Redisは超高速なレスポンスが必要な場合に有効だけど、超えるべきハードルは高いよね。

PostgreSQLは20年以上裏切ってないなー。完璧じゃないけど、データに関するほとんどの場合にマジで使える（チューニングは必要かも）。

HNや現実世界のほとんどの人は、同時接続ユーザーが10人を超える規模なんて必要ないからね。データベースで十分にスケールできるのに、インフラをゴテゴテ追加してるスクリプトキディが多いよね。

なんでダメなの？パレートの法則だよ。ほとんどのケースで8割はカバーできるし、高度に最適化されたソリューションが必要になったら、その時に移行すればいいじゃん。

良い理由はいろいろ挙げられてるけど、最近Postgresのカルト的な人気が高まってるのもあるよね。

わかるー。Postgres好きな人多いけど、理由聞くと「なんとなく」って人多くない？　INETみたいな便利機能も全然使ってないし。ドキュメント読もうよマジで！

考え方がちょっと違う気がするなー。チーム全員がPostgres推しってすごくない？　ライブラリとかで意見が割れる中、Postgresはみんな納得できる選択肢なんだよ。拡張性も高いし。

pg_searchとvchord_bm25のRPM/DEBパッケージ作ったから、ベンチマークしたい人はどうぞ。

https://pigsty.io/ext/fts/vchord_bm25

https://pigsty.io/ext/fts/pg_search

Postgresネイティブの全文検索実装が増えて嬉しいね。luceneとかtantivyはimmutable segments向けだから、Postgresと組み合わせると微妙になりがち。

segmentsがimmutableってことは、TantivyがPostgresと相性悪いってわけじゃないよ。MVCCとかブロックストレージに対応させればいいんだって。詳しくはこの記事見て。

https://www.paradedb.com/blog/block_storage_part_one

根本的な問題は「dmlごとに新しいsegment作っちゃう」ってことだと思う。緩和はできるけど、良い解決策はなさそう。

1000万件のデータセットっておもちゃみたいなもんだよね。WikipediaとかRedditのコメントみたいにもっと大きいデータセットでベンチマークすべき。

昔、ネイティブFTS使おうとしたんだけど、ダメだったんだよね。1秒に数千件insertがあるテーブルで、更新が遅すぎてタイムアウトしちゃって。インデックス落としたら直ったけど、FTSの性能テストまで行けなかったのが残念。

それって検索インデックスとトランザクションデータを同じテーブルに置いてたのが原因じゃない？　検索インデックス専用のテーブルなら、insertの遅延なんて気にしなくて良くない？

PostgresのFTSをちゃんと使えばElasticsearchとかMeilisearch並の性能出せるのに、最初からそっち行っちゃうチーム多いよね。SQLite + FTS5 + Wasmでブラウザでも同じようなことできるかな？

テキスト検索にはpostgresproの”rum”拡張機能を使ってるよ。テラバイト級のPDFを1秒以内で検索できるんだって。詳しくはこの講演を見てみて！https://github.com/jmscott/talk/blob/master/pgday-austin-20161112.pdf