私たちは通信を保存するための PostgreSQL データベースの構造を設計することに成功しました。XNUMX 年が経過し、ユーザーは積極的にデータベースに情報を入力し、現在は次の内容が含まれています。 何百万ものレコード、そして...何かが遅くなり始めました。
- パート 2: 「営利目的」の区分
ことは事実である テーブルのサイズが大きくなるにつれて、インデックスの「深さ」も大きくなります。 - 対数的ではありますが。 しかし、時間の経過とともに、サーバーは同じ読み取り/書き込みタスクを実行することになります。 何倍ものページのデータを処理する最初の頃よりも。
ここが救いです セクショニング.
シャーディング、つまり異なるデータベースまたはサーバー間でデータを分散することについて話しているわけではないことに注意してください。 データを分割しても いくつかの サーバーを使用しても、時間の経過とともにインデックスが「膨張」するという問題は解消されません。 新しいサーバーを毎日稼働させる余裕があれば、問題が特定のデータベースにとどまらないことは明らかです。
「ハードウェアで」パーティショニングを実装するための特定のスクリプトではなく、何をどのように「スライス」する必要があるのか、そのような要望がどのような結果をもたらすのかというアプローチ自体を検討します。
コンセプト
もう一度目標を定義しましょう。今日、明日、そして XNUMX 年後に、読み取り/書き込み操作中に PostgreSQL によって読み取られるデータの量がほぼ同じであることを確認したいと考えています。
どれについても 時系列に蓄積されたデータ (メッセージ、ドキュメント、ログ、アーカイブなど) パーティション化キーとして自然に選択されるのは、 イベント日時。 私たちの場合、そのようなイベントは メッセージを送る瞬間.
ユーザーはほとんどの場合、 「最新」のもののみで動作する そのようなデータ - 彼らは最新のメッセージを読み取り、最新のログを分析します...いいえ、もちろん、さらに遡ることもできますが、これを行うことはほとんどありません。
これらの制約から、最適なメッセージ ソリューションは次のとおりであることは明らかです。 「日常」セクション - 結局のところ、ユーザーはほとんどの場合、「今日」または「昨日」に届いたものを読むことになります。
一日の中でほとんど XNUMX つのセクションだけを読んだり書いたりすると、次のような結果も得られます。 メモリとディスクのより効率的な使用 - テーブル全体の「大きくて太い」インデックスとは対照的に、すべてのセクション インデックスが RAM に簡単に収まるためです。
一歩一歩
一般に、上記で述べたことはすべて、XNUMX つの継続的な利益のように聞こえます。 そしてそれは達成可能ですが、そのためには一生懸命努力する必要があります - なぜなら エンティティの XNUMX つを分割するという決定は、関連するエンティティを「見る」必要性につながります。.
メッセージ、そのプロパティおよび投影
メッセージを日付ごとに分割することにしたため、エンティティ、つまりそれに依存するプロパティ (添付ファイル、受信者のリスト) も分割するのが理にかなっています。 メッセージの日付によっても.
私たちの典型的なタスクの XNUMX つは、メッセージ レジスタ (未読、受信、すべて) を正確に表示することであるため、メッセージ レジスタをメッセージ日付によるパーティションに「取り込む」ことも論理的です。
パーティション化キー (メッセージの日付) をすべてのテーブル (受信者、ファイル、レジストリ) に追加します。 メッセージ自体に追加する必要はありませんが、既存の DateTime を使用します。
スレッド
複数のメッセージに対してトピックは XNUMX つだけであるため、同じモデルでトピックを「切り取る」方法はなく、別のものに依存する必要があります。 私たちの場合はそれが理想的です 通信の最初のメッセージの日付 — つまり、実際にはトピックの創造の瞬間です。
パーティショニング キー (トピックの日付) をすべてのテーブル (トピック、参加者) に追加します。
しかし今、私たちは同時に XNUMX つの問題を抱えています。
- このトピックに関するメッセージはどのセクションで検索すればよいですか?
- メッセージのどのセクションでトピックを探せばよいですか?
もちろん、すべてのセクションで検索を続けることもできますが、これは非常に悲しいことであり、すべての勝利を無効にすることになります。 したがって、正確にどこを見るべきかを知るために、セクションへの論理リンク/ポインターを作成します。
- メッセージに追加させていただきます トピックの日付フィールド
- トピックに追加しましょう メッセージの日付が設定されました この対応関係 (別のテーブルまたは日付の配列にすることができます)
個々の通信ごとにメッセージの日付のリストにはほとんど変更が加えられないため (結局のところ、ほとんどすべてのメッセージは 1 ～ 2 つの隣接する日に分類されます)、このオプションに焦点を当てます。
全体として、データベースの構造はパーティショニングを考慮して次のような形になりました。
テーブル: RU、テーブル/フィールドの名前にキリル文字が含まれていることに嫌悪感がある場合は、見ない方がよいでしょう。
-- секции по дате сообщения
CREATE TABLE "Сообщение_YYYYMMDD"(
"Сообщение"
uuid
PRIMARY KEY
, "Тема"
uuid
, "ДатаТемы"
date
, "Автор"
uuid
, "ДатаВремя" -- используем как дату
timestamp
, "Текст"
text
);
CREATE TABLE "Адресат_YYYYMMDD"(
"ДатаСообщения"
date
, "Сообщение"
uuid
, "Персона"
uuid
, PRIMARY KEY("Сообщение", "Персона")
);
CREATE TABLE "Файл_YYYYMMDD"(
"ДатаСообщения"
date
, "Файл"
uuid
PRIMARY KEY
, "Сообщение"
uuid
, "BLOB"
uuid
, "Имя"
text
);
CREATE TABLE "РеестрСообщений_YYYYMMDD"(
"ДатаСообщения"
date
, "Владелец"
uuid
, "ТипРеестра"
smallint
, "ДатаВремя"
timestamp
, "Сообщение"
uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений_YYYYMMDD"("Владелец", "ТипРеестра", "ДатаВремя" DESC);
-- секции по дате темы
CREATE TABLE "Тема_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
PRIMARY KEY
, "Документ"
uuid
, "Название"
text
);
CREATE TABLE "УчастникТемы_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
, "Персона"
uuid
, PRIMARY KEY("Тема", "Персона")
);
CREATE TABLE "ДатыСообщенийТемы_YYYYMMDD"(
"ДатаТемы"
date
, "Тема"
uuid
PRIMARY KEY
, "Дата"
date
);
かなりのペニーを節約する
さて、not を使ったらどうなるでしょうか フィールド値の分布に基づいて (トリガーと継承、または PARTITION BY によって)、アプリケーション レベルで「手動で」実行すると、パーティショニング キーの値がテーブル自体の名前にすでに格納されていることがわかります。
それで、あなたがそうなら 保存されるデータの量を非常に心配していますか?, その後、これらの「余分な」フィールドを削除し、特定のテーブルに対応できます。 確かに、この場合、いくつかのセクションからのすべての選択をアプリケーション側に転送する必要があります。
出所： habr.com