PostgreSQLのパフォーマンスをマスターする - 効率的なデータベースのためのWeWardのプラクティス

ユーザ・エクスペリエンスを向上させるために1ミリ秒を争うWeWardでは、PostgreSQLを卓越したパフォーマンス・エンジンに変えるという野心的な旅に出ました。この記事では、PostgreSQLを大幅に最適化するために直面した課題、実装したソリューション、ベストプラクティスについて説明します。

なぜデータベースの最適化が重要なのか？

ユーザーが即座のレスポンスタイムを期待する世界では、遅いデータベースはフラストレーションや機会損失につながります。WeWardでは、この課題は、毎日何百万ものトランザクションと、完璧なパフォーマンスを必要とする複雑なクエリに反映されます。当社の課題には、遅いクエリ、過剰な負荷、拡張性の向上が必要なアーキテクチャなどがありました。

これらの障害をどのように克服したかを紹介しよう。

ステップ1：クエリの最適化

PostgreSQLの最適化の基礎は問い合わせ分析から始まります。私たちは問い合わせの実行計画を調査することで非効率を特定しました。

実行計画分析

使用 説明する そして アナライズそして、私たちはクエリのX線画像を手に入れた。

問題のあるクエリの例

SELECT level_customer.*
FROM level_customer
JOIN level ON level.id = level_customer.level_id
WHERE level_customer.customer_id = XXXXXXX
 AND level_customer.start_date <= '2024-05-07'::date
 AND level.version = 2
ORDER BY level_customer.start_date DESC, level.value DESC
LIMIT 1;


検出された問題

• ソートを効率的に処理するためのインデックスがない。
• プランナーによって選択された準最適結合戦略。

解決策：インデックスの作成：

CREATE INDEX idx_level_customer_customer_date_value
ON level_customer (customer_id, start_date DESC, level_id DESC)；


プランナーの調整PostgreSQLに ネスティッド・ループ・ジョイン デフォルトの ハッシュジョイン この特定のクエリに対して。

マテリアライズド・ビュー：複雑なクエリに対して、マテリアライズド・ビューは各実行の計算オーバーヘッドを削減し、非常に安定したパフォーマンスを提供します。

ステップ 2: レプリカによる負荷分散

1日に数百万のクエリが発生するため、負荷を分散するためにレプリカベースのアーキテクチャを採用しました。

アーキテクチャのセットアップ：

• プライマリー・ノード：読み取りと書き込みの両方を処理し、データの一貫性を確保する。
• レプリカノード：読み取り専用で、プライマリノードの負荷を軽減し、読み取り負荷の高いクエリの応答時間を改善します。

メリット

• パフォーマンスの向上：読み取りクエリをレプリカにオフロードすることで、レイテンシーが短縮され、スループットが向上します。
• 高可用性：プライマリノードに障害が発生した場合、レプリカを昇格させることができ、サービスの継続性を確保します。
• スケーラビリティ：トラフィックの増加に応じて、複数のレプリカノードを追加して水平方向に拡張することができます。

技術的な考察：

• レプリケーションのレイテンシー：プライマリ・ノードとレプリカ・ノード間で若干の遅延が発生する可能性がある。
• 最終的な一貫性：レプリカに対する読み取りクエリは、若干古いデータを返す可能性があります。
• モニタリング信頼性の高いレプリケーションを確保するためには、プロアクティブなモニタリングが不可欠です。

ステップ 3: パーティショニングによる大規模テーブルの管理

なぜパーティションなのか？

何十億行もの行を含むテーブルは、読み取りや書き込みの処理速度を著しく低下させます。パーティショニングはテーブルをより小さなサブセットに分割し、クエリの効率を向上させる。

道具だ： pg_パートマン

メリット

• 自動パーティション管理：事前に定義された基準に基づいてパーティションの作成と削除を行います。
• クエリー・パフォーマンスの向上：クエリは関連するパーティションのみを対象とします。
• より簡単な履歴データメンテナンス：現在のデータを中断することなく、アーカイブとパージを簡素化します。

ステップ4：接続の安定化

各 PostgreSQL 接続はシステムリソースを消費します。サーバの過負荷を防ぐために

• 接続の制限： 最大接続数 は、利用可能なハードウェアとアプリケーションのニーズに基づいて調整された。
• コネクション・プーリング：既存の接続を再利用することで、サーバーの負荷を軽減し、アプリケーションの応答性を向上。

PostgreSQLの主なメモリパラメータ：

• 共有バッファ: 頻繁にアクセスされるデータをキャッシュするためのメモリ割り当てを制御する。
• ワークメモリー: クエリ実行中のソートとハッシュ・テーブルのためのメモリを定義します。

ステップ5：データレイクによるスケーラブルなアーキテクチャ

重要な（"ホット"）データはPostgreSQLに残り、アクセス頻度の低い（"コールド"）データはAmazon S3またはGoogle Cloud Storageに転送される。コールドデータの分析にはGoogle BigQueryを使用する。

メリット

• スケーラビリティ：クラウドプラットフォーム上のストレージ容量は無制限。
• コスト効率：コールドデータ保管にかかるコストを削減。
• 柔軟性：各データタイプに適したツール。

👁 ステップ6：モニタリングと観測可能性

WeWardは、以下のような高度な監視ツールを使用しています：

• AWS Performance Insights：データベースパフォーマンスをリアルタイムに可視化。
• アプリケーション・パフォーマンス・モニタリング（APM）：エンド・ツー・エンドのトランザクション・トレース
• データベース・パフォーマンス・モニタリング（DBM）：クエリ・パフォーマンスの洞察。

メリット

• 迅速な問題検出。
• パフォーマンスの最適化。

結論：将来への強固な基盤

厳密なクエリ最適化、インテリジェントなリソース管理、最新のアーキテクチャを組み合わせることで、PostgreSQLはWeWardにとって重要な味方となりました。これらのソリューションは、データベースの可能性を最大化することを目的とするあらゆる組織に適用可能です。

PostgreSQLを最適化するための戦略を教えてください。あなたの経験を共有し、会話に参加してください！

モバイルアプリをご覧ください：WeWard

‍