🐼 👩🏻‍🎤 🆖 PostgreSQLの安全性 ✋🏾 🛂 🌱

私は3年前にPostgreSQLを使い始めたことがたまたまありましたが、その間、想像できるすべての可能性のあるレーキを系統的に収集することができました。実を言うと、私が3年前に苦い経験を自分と分かち合う機会があれば、私の人生はずっと単純になり、神経細胞はより全体になります。それが、PostgreSQLで開発する際に固守する一連のルールを含む絶対に主観的な記事を書くことにした理由です。おそらく、この記事は、誰かが私が収集したレーキをバイパスするのに役立つでしょう（そして、他の人を踏みつけます！）。

ルールの同じリスト

以下のほぼすべてのポイントは、苦しみと高揚に満ちた悲しい物語です。そして、「痛み！」という言葉は、物語がうまくいっているポイントを示しており、その記憶は今でも夜震えています。

データベーススキーマのバージョン管理

データベーススキーマは、作成したコードです。バージョン管理システム内にあり、プロジェクトの残りのバージョンとバージョン管理されている必要があります。 PostgreSQLの場合、これらの目的でPyrseasが最も好きでした。すべてのPostgreSQL固有のオブジェクトを含むスキーマをバージョン管理されたyamlファイルに変換します。純粋なSQLとは異なり、ブランチでこのようなファイルを操作し、変更をマージすると便利です。最後のステップでは、yamlファイルがデータベーススキーマと比較され、SQLへの移行が自動的に生成されます。
痛み！ 戦闘基地に直接変更を適用しないでください

変更が単純で、非常に緊急で、本当にしたい場合でも。最初に、開発者に基づいて適用し、ブランチにコミットし、トランクに基づいて変更を適用する必要があります（戦闘ベースと同じ）。そして、すべてが体幹に収まったら、戦闘基地に申請します。それは長く、妄想的ですが、多くの問題から救います。
痛み！ 削除または更新を書く前に、どこに書くか

そして、コードを実行する前に、息を吐き、3回カウントして、正しいベースのセッションにいることを確認してください。私は、3人の「私たちの父」が走ることさえ考えないで、切捨てについて静かです、アーメン！

UPD koropovskiy ：現在のセッションの自動コミットをオフに設定する方が便利です。

tgz ：または、更新と削除の各書き込みが始まる前。
テスト駆動開発

必ず最初にテストを作成してから、データベースオブジェクトを作成してください。スキーム、テーブル、関数、型、拡張機能など、あらゆるオブジェクトについて話しています-例外はありません！最初は難しいように思われますが、後で何度も感謝します。回路を最初に作成したとしても、何かを見落としがちです。また、6か月でテーブルをリファクタリングする場合、作成するテストのみが、あらゆる機能での突然の攻撃からあなたを救います。 PostgreSQLの場合、素晴らしいpgTAP拡張機能があります。各回路で、テスト用の関数を記述するための追加回路「schema_name_tap」を作成することをお勧めします。そして、pg_proveを介してテストを実行します。
PITRを調整することを忘れないでください

キャプテンオブエビデンスの役割を果たすことを恐れていますが、どのベースにもバックアップを設定する必要があります。さらに、いつでもベースを復元できることが望ましいです。これは、災害復旧だけでなく、開発者がデータベースの特定のタイムスライスで作業するための多くの興味深い機会を提供します。 PostgreSQLにはこのためのバーマンがいます。
データの一貫性

データベース内の一貫性のないデータが良い結果をもたらすことはありませんでした。少数であっても、ベース全体を簡単にゴミに変えることができます。したがって、外部キーやチェックなどの正規化および制限を決して無視しないでください。許容レベルの複雑さとパフォーマンスを備えた正規化形式でスキームを実装できないことを確認した後にのみ、非正規化形式（jsonbなど）を使用してください-非正規化ビューは、一貫性のないデータにつながる可能性があります。非正規化の支持者のすべての議論に、彼らは理由のために正規化を思いついたと答え、意味のある表情で黙っています。
最初に遅延可能な外部キーを作成します

この場合、トランザクションの最後に制限のチェックを延期します。これにより、実行中に免責との不整合を取得できます（ただし、最後にはすべてが一貫しているか、エラーが発生します）。さらに、トランザクション内のフラグを即時に変更することにより、トランザクションの適切なタイミングで制限チェックを強制できます。

UPD コメントは、遅延可能は多くのインポートタスクを単純化するあいまいなプラクティスであるが、トランザクション内のデバッグプロセスを複雑にし、初心者開発者にとっては悪いプラクティスであることを示しています。遅延キーは、キーを持たないよりも優れていると頑固に傾いていますが、質問の別の見方を検討してください。
パブリックスキーマを使用しないでください

これは、拡張機能の機能のユーティリティ図です。必要に応じて、個別のスキームを作成します。それらをモジュールのように扱い、エンティティの論理的に別個のセットごとに新しいスキーマを作成します。
APIの個別のスキーマ

アプリケーション側で呼び出される関数の場合、別個のスキーム「api_v_version_number」を作成できます。これにより、データベースへのインターフェイスである関数の場所を明確に制御できます。このスキームの関数に名前を付けるには、テンプレート「entity_get / post / patch / delete_arguments」を使用できます。
監査トリガー

トリガーは、アクションの監査に最適です。また、任意のテーブルのアクションを記録するユニバーサルトリガー関数を作成することをお勧めします。これを行うには、information_schemaからターゲットテーブルの構造に関するデータを抽出し、実行されているアクションに応じて古い行が挿入されるか新しい行が挿入されるかを理解します。この決定により、コードはより愛情深く~~魅惑的になり~~ます。

トリガーを使用して累積レジスターを計算する場合は、ロジックに注意してください-エラーが1つあり、一貫性のないデータを取得する可能性があります。噂によると、これは非常に危険なカンフーです。
痛み！ データを新しいスキーマにインポートする

データベース開発者の生活の中で最も恐ろしいが、定期的に発生するイベント。 PostgreSQLはFDWを本当に助けてくれるので、9.6でうまく機能しました（開発者が気にするなら、FDWはリモート側で計画を立てることができます）。ところで、「外部スキーマのインポート」などの便利な構造があります。これにより、テーブルの束にラッパーを書く必要がなくなります。データベース内の既存の外部キーと主キーを削除および復元するための一連のSQLコマンドを保存する一連の関数を用意することもお勧めします。インポートすることをお勧めします。まず、ターゲットテーブルと同じ構造のデータでビューセットを作成します。そして、それらから（コピーではなく）コピーを使用して挿入を行います。 SQLコマンドのシーケンス全体を別のバージョン管理されたファイルに保存し、-1キーを使用してpsqlを介して（単一のトランザクションで）実行することをお勧めします。ところで、PostgreSQLで最初にバックアップを作成し、指を交差させることでfsyncをオフにできるのはインポートのみです。
SQLで記述しないでください：1999

いいえ、本当に、それ以来、多くの水が流出しました。全世代が学校を卒業し、レンガの携帯電話は1999年の基準によってスーパーコンピューターに変わりました。一般的に、あなたは私たちの父親が書いたように書くべきではありません。 "with"を使用すると、コードがよりきれいになり、上から下に読むことができ、結合ブロック間でループしません。ところで、同じ名前のフィールド間で結合が行われる場合、「オン」ではなく「使用」を使用する方が簡潔です。そしてもちろん、バトルコードでオフセットを使用しないでください。そして、忘れられがちな「横方向に結合」するような素晴らしいものがあります-そしてその瞬間、子猫は世界で悲しいです。

UPD 「with」を使用すると、その実行結果によってCTEが作成されることを忘れないでください。CTEはメモリを消費し、クエリを実行するときにインデックスをサポートしません。そのため、あまりにも頻繁に使用し、「with」のポイントまで使用すると、リクエストのパフォーマンスに悪影響を与える可能性があります。したがって、スケジューラを介してリクエストを分析することを忘れないでください。「with」は、以下のクエリのいくつかの部分で異なる方法で使用されるテーブルを取得する必要がある場合に特に適しています。また、「with」を使用するとクエリの可読性が大幅に向上し、PostgreSQLのすべての新しいバージョンでより効率的に機能することを忘れないでください。他のものが等しい-この特定のデザインを好む。
一時テーブル

一時テーブルなしでクエリを作成できる場合-ためらわずに作成してください！通常、「with」コンストラクトによって作成されたCTEは許容可能な代替です。事実、PostgreSQLは一時テーブルごとに一時ファイルを作成します...そして、はい、地球上の別の悲しい子猫です。
痛み！ SQLで最も恐ろしいアンチパターン

ビュー構造を使用しない
```
select myfunc() from table;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
```
このようなリクエストの実行時間は、行数とともに直線的に増加します。このようなクエリは、各行に適用される関数を使用せずにいつでも書き換えることができ、実行速度が数桁向上します。
リクエストの主な秘密

テストコンピューターで要求の実行が遅い場合、実稼働では高速に動作しません。これは、車のある道路についての最良の例えです。テストコンピューターは一方通行の道路です。実稼働サーバーは10列の道路です。交通渋滞のない車の多くは、1車線よりもラッシュアワーで10列走行します。しかし、あなたの車が古いバケツである場合、彼女はフェラーリとして行かないでしょう。何本の空の車線が彼女に与えないか。
インデックスを使用、ルーク！

それらをどれだけ正しく作成して使用するかによって異なりますが、リクエストは10分の1秒または1分で実行されます。 Marcus Vinandのb-treeインデックスサイトを閲覧することをお勧めします。これは、インターネットで見たバランスの取れた木についての一般に公開されている最高の説明です。そして彼の本も素晴らしいです、はい。
グループ化またはウィンドウ関数？

いいえ、もちろん、ウィンドウ関数はそれ以上のことができます。ただし、このように集計することもできます。このような場合、私はルールに従います：集約がカバーインデックスに従って計算される場合、グループ化のみ。カバーするインデックスがない場合は、ウィンドウ関数を試すことができます。
set_config

set_configは、トランザクション内でpostgresql.confのプリファレンスを設定するだけでなく、トランザクションにカスタム変数を渡すためにも使用できます（事前にpostgresql.confで定義されている場合）。トランザクションでこのような変数を使用すると、呼び出された関数の動作に非常に興味深い影響を与えることができます。
FTSとトライグラム

彼らは素晴らしいです！ SQLの全機能を保持しながら、全文検索とあいまい検索を提供します。忘れずに使用してください。
独自の例外を呼び出す

多くの場合、大規模なプロジェクトでは、コードとメッセージで多くの例外をスローする必要があります。それらを混同しないように、例外「コード-例外テキスト」の例外用に別のタイプの例外を作成するオプションと、それらを呼び出す関数（「レイズ」のラッパー）、追加および削除があります。また、すべてのデータベースオブジェクトをテストでカバーした場合、どこかで既に使用されている例外コードを誤って削除することはできません。
多くの妄想だけでは十分ではありません

テーブルにACLを設定し、「セキュリティ定義者」で関数を実行することを忘れないでください。関数が読み取り専用の場合、feng shuiでは「安定」フラグを設定する必要があります。
痛み！ ケーキの上のチェリー

UPD サーバーを介してアプリケーションユーザーをデータベースにリダイレクトすることはできません。アプリケーションユーザーをデータベースユーザーに1対1でブロードキャストします。標準的なPostreSQLツールを使用して、データベース内のユーザーとそのグループのセキュリティを同時に設定できるように思えても、これは絶対にしないでください！このスキームでは、接続プールを使用できず、接続された各アプリケーションユーザーは、データベースへのリソースを大量に消費する接続を使い果たします。データベースは数百の接続を保持し、サーバーは数千の接続を保持します。このため、アプリケーションはロードバランサーと接続プールを使用します。また、負荷が増加して各ユーザーを1人ずつデータベースに変換する場合、回線を切断してすべてを書き換える必要があります。

PostgreSQLの安全性

ルールの同じリスト

More articles: