はじめに
教科書
https://lets.postgresql.jp/documents/tutorial/postgresql-abc/1-1
第１回 データベースとデータベース管理システム
データベースとは、大量のデータをコンピュータシステム上で扱えるように集めたもの
データベース管理システムとは、そのデータベースを効率よく読み書きするためのソフトウェア
リレーショナルデータベースとデータベース管理システム超入門
特定の行の特定のカラムのことを「フィールド」
直感的で非常に簡単な構造をしているのがリレーショナルデータベースの特徴
処理
データベース管理システムでは「トランザクション」と呼ばれる単位でデータの書き換えをコントロール
PostgreSQLとは
PostgreSQL（ポストグレスキューエル）は、オープンソースソフトウェア（以下 OSS）として開発されているリレーショナルデータベース管理システム (RDBMS) です。多機能で高性能な RDBMS であることやライセンスなどの面から、特に Web システムのバックエンドデータベースを中心として利用が拡大
PostgreSQL は、RDBMS としては最も初期に実装され様々な RDBMS 製品の基にもなった Ingres に端を発しています。 Ingres の後、Ingres の後 (post) ということで Postgres と名づけられ、データベース界では著名なカリフォルニア大学のマイケル・ストンブレーカー教授のもと 大学の研究プロジェクトとしてオブジェクト指向の要素を取り入れたオブジェクト・リレーショナルデータベースとして実装されました。その後、研究プロジェクトの継続として 大学院生によって Postgres95 という名前で 2 年ほどメンテナンスされたのち開発体制の変更と問い合わせ言語が 独自の PostQUEL から標準の SQL に対応したことから名称を変え、現在の PostgreSQL となりました。 (実はまた昔の Postgres という名前に戻そうという動きも開発コミュニティ内にあります) PostgreSQL となってからは、2006 年に 10 周年を迎えました。Ingres 時代から数えると実に 20 年以上開発が行われている 歴史あるデータベースです。
プログラミング言語のインタフェース
以下に対応している主要なプログラミング言語のインタフェースを列挙
PostgreSQL の特徴
- 標準SQLへの準拠
- 並列実行性能に重点
CPU 数が増えた場合のスケーラビリティが高く、接続数が増えても性能が維持されます。追記型のデータ構造も並列実行性能を高めるための工夫の一つです。
- 日本語対応
厳密には日本語だけの対応だけではなくマルチバイト対応
PostgreSQLのデータ管理方式
PostgreSQLでは、行データが更新されたり削除されたりした時に、実際の行データは消しません。それまでの行データに削除されて使用されなくなった印をつけて検索の対象から外すようにします。バキューム処理は、これらの不要な行データを回収する処理を行います。
反面、更新処理が多くなると、不要になった行データの量が増えすぎて物理的なデータ量が大きくなってしまうので、ディスク容量を圧迫したり、シーケンシャルスキャンの性能が劣化してしまいます。
VACUUMとVACUUMFULL
バキューム処理を行うには、VACUUM文あるいはVACUUMFULL文を使用します。
- vacuum
- 不要データを回収して再利用可能な状態にする
- vacuum full
- ぎょうデータの物理的な配置を移動させてデータファイルの圧縮を行う。実行すると表全体に強いロックが取得されて他のユーザが処理を行えなくなる。大量にデータを削除した後にデータファイルのサイズを縮小してディスク空き容量を増やしたい場合に実行すると良い
#DBMSの機能
* データの保護機能を実現
* 列単位でアクセス権限設定可能
* トランザクションを制御
* 同時排他制御
* データの生合成を維持
* 障害からの安全な復旧
* アプリケーションからのインタフェースを提供
データモデル
データベースを構築する範囲を対象世界と呼ぶ
対象世界をモデル化したものがデータモデル
データモデルを作成することをデータモデリングという
対象世界を抽象化して概念データモデルを作成し、概念データモデルを変換して論理データモデルとする。
概念データモデル
概念データモデルは対象世界を抽象化して対象世界のデータ構造を概念的に捉えた結果を表現したモデル。概念データモデルの作成においてはERモデルが用いられる。
設定ファイル
postgresql.confはパラメータ設定を行うファイル。ファイルはinitdbで作成され、＄PGDATAディレクトリ内に配置されます。次の書式でパラメータを設定する。
parmeter = 設定値
- パラメータは一行に一つずつ設定する。複数行に分けて一つのパラメーターを設定することはできない。
- パラメータメイト設定値の間のイコールは省略できる。パラメータメ名と設定値の間には空白がなければならない。
接続と認証
listen_addresses
postgresqlのIPアドレス
Amazon RDS for PostgreSQLにおける自動バキュームのケーススタディ
デッドタプルを削除するための自動バキュームチューニング
次の式は表に対して自動バキュームを行うかどうかの閾値を計算します。
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * タプル数
自動バキュームをより頻繁に実行させるためのトリガーとして、私はautovacuum_vacuum_scale_factorに 0.02 若しくは 0.01 などの小さな値を用いることを勧めます。
このパラメータをデータベースレベルで単一の値にするのではなく、表のレベルに応じて異なる値にされることを推奨します。
自動バキュームセッションが同時実行しても自動バキュームの閾値に達したすべての表を処理しきれなかった
autovacuum_max_workersを増加させることを考えるのであれば、次の2つのパラメータセットを確認し、それに応じて調整されることをお勧めします。
- maintenance_work_mem
- autovacuum_work_mem
これら2つのパラメータはそれぞれの自動バキュームセッションが使用できる最大メモリ量を設定します。
DB パラメータ値
DB パラメータの値は、以下のいずれかとして指定できます。
- 整数定数
- DB パラメータ式
- DB パラメータ関数
- 文字列定数
- ログ式 (log 関数は log 基数 2 に相当します)。value={log(DBInstanceClassMemory/8187281418)*1000} など。
DB パラメータ式
DB パラメータ式は整数値に解決される式あるいはブール値であり、中かっこ {} で囲まれます。式は、DB パラメータ値で指定したり、DB パラメータ関数の引数として指定したりできます。
{FormulaVariable}
{FormulaVariable*Integer}
{FormulaVariable*Integer/Integer}
{FormulaVariable/Integer}
DB パラメータ式の変数
各式の変数は整数あるいはブール値を返します。変数名では大文字と小文字が区別されます。
- AllocatedStorage
- データボリュームのサイズをバイト単位で返します。
- DBInstanceClassMemory
- 現在の DB インスタンスに関連付けられている DB インスタンスクラスに割り当てられたメモリから、インスタンスを管理する Amazon RDS プロセスによって使用されるメモリを引いたバイト数を返します。
- EndPointPort
- DB インスタンスに接続するときに使用されるポートの番号を返します。
DB パラメータ式の演算子
DB パラメータ式では、2 つ演算子 (除算と乗算) がサポートされています。
DB パラメータ関数
- GREATEST()
- 整数またはパラメータ式のリストから最大値を返します。
- LEAST()
- 整数またはパラメータ式のリストから最小値を返します。
- SUM()
- 指定した整数またはパラメータ式の値を加算します。
DB パラメータ値の例
Oracle の processes パラメータで GREATEST 関数を指定して、ユーザープロセスの数を設定できます。その数は、80 または DBInstanceClassMemory を 9,868,951 で割った値の、いずれか大きい方の値に設定されます。
GREATEST({DBInstanceClassMemory/9868951},80)
参考文献
- PostgreSQLのメモリ設計
- PostgresのRDSチューニング https://qiita.com/awakia/items/9981f37d5cbcbcd155eb