PostgreSQL

MySQL

PostgreSQLのrootはpostgres

データ構造 †

• データベースクラスタ
  • データベースの集合
• データベース
  • スキーマの集合
  • デフォルトで template0, template1, postgres というデータベースがある
    • 初期状態では、template0 = template1
    • ユーザ定義のテンプレートを作成するために template1 を変更して利用できる。
    • template0は書き込みができないため、常に初期状態が保たれ、template1の内容を引き継がせたくないデータベースを作成したいケースが出てきた場合は、template0を基にして新規作成すれば良い
• スキーマ
  • テーブルや関数などオブジェクトの集合
  • デフォルトで public というスキーマがある。これはデフォルトですべてのロールにアクセス権限と CREATE 権限が与えられており、 public スキーマの中にどのロールでもテーブルを作成することができます。
• テーブル
  • 通常のテーブル

ロール †

• ロール
  • ほとんどユーザと同じ概念
  • PostgreSQL が立ち上がるとpostgresという名前のロールが作成されている。このロールは何でもできるスーパーユーザ
  • ロールには、権限を複数設定できる。
• 権限
  • 権限は、ユーザの実行可能範囲を増やす。
  • 権限は、ロールに0+個付くことができる。
  • 権限には、SELECT、 INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、 EXECUTE、USAGEがある。
  • GRANT 特権 ON テーブル名 TO ロール名;のようにしてテーブル固有の権限を作成できる。

クエリ †

スキーマ †

• materials テーブルのカラムと型をリストする

SELECT 
  column_name, 
  data_type 
FROM 
  information_schema.columns 
WHERE 
  table_name = 'materials'

配列 †

• 配列 groups に 'com' が入っているか？
  • where 'com' = ANY(groups2)
  • ANY は配列のどれかが 'com' = groups2[i] を満たせば OK

TOAST †

データページに収まらないようなstringがレコード内に入っているときにTOASTが使われる

PostgreSQLでは、各レコード（行）はデータページと呼ばれる固定サイズのブロックに格納されます。このデータページのサイズは通常8KBです。もしレコード内のデータ（特に text や bytea などの大きなデータ型）が大きすぎてデータページに収まらない場合、PostgreSQLはTOASTという技術を使ってそのデータを効率的に管理します。

具体的には、TOASTは以下のような処理を行います。

大きなデータをチャンクに分割: 大きなデータは、扱いやすいサイズの小さなチャンク（通常2KB）に分割されます。 チャンクを別のテーブルに保存: 分割されたチャンクは、pg_toast_* という名前の別のテーブル（TOASTテーブル）に保存されます。 元のテーブルにはポインタを保存: 元のテーブルのレコードには、TOASTテーブルに保存されたチャンクへのポインタ（参照情報）が保存されます。 これにより、大きなデータを持つレコードでもデータページに収まるようになり、データベース全体の効率的な管理が可能になります。

ただし、TOASTを使用することでデータへのアクセスにオーバーヘッドが生じる可能性があります。特に、大きなデータを頻繁に読み書きする場合は、パフォーマンスに影響が出る可能性があります。

pg_toast の内部構造

pg_toast スキーマには、主に以下のテーブルが含まれます。

pg_toast.<テーブルOID>_index:

各テーブルの TOAST された値へのインデックスを保持します。 chunk_id 列は、TOAST された値が格納されているチャンクを識別します。 chunk_seq 列は、チャンク内の特定の値へのオフセットを示します。 pg_toast.<テーブルOID>:

実際の TOAST された値のチャンクを格納します。 各チャンクは、最大 TOAST_MAX_CHUNK_SIZE (通常 2KB) のサイズを持ちます。 大きな値は複数のチャンクに分割されて格納されます。 TOAST の仕組み

テーブルの列に大きな値が挿入されると、PostgreSQL はその値を TOAST するかどうかを判断します。 TOAST が必要と判断された場合、値は複数のチャンクに分割され、pg_toast.<テーブルOID> テーブルに格納されます。 pg_toast.<テーブルOID>_index テーブルには、各チャンクの位置情報が記録されます。 元のテーブルの列には、TOAST された値へのポインタ (OID とインデックス情報) が格納されます。 TOAST された値が必要になると、PostgreSQL はポインタを使用して pg_toast テーブルから必要なチャンクを読み込み、元の値を再構築します。

雑多 †

ストリーミングレプリケーション: リアルタイムのレプリケーション WAL: プライマリのトランザクションの全履歴。これがレプリケーションサーバに転送される。

PostgreSQLは追記型アーキテクチャのため、updateやdeleteとなったレコードを削除することなく、削除フラグを付与する形となる。そのフラグされた領域を再利用可能としてくれる処理。

今のポスグレでは、いい感じで自動的にvacuumしてくれる機能が用意されており、これに任せてしまうのが推奨。デフォルトで有効となっています。

auto vacuumが実行される条件はこちら。 autovacuum_vacuum_threshold + (テーブル行数 * autovacuum_vacuum_scale_factor) < dead tuple数

https://aws.amazon.com/jp/blogs/news/a-case-study-of-tuning-autovacuum-in-amazon-rds-for-postgresql/ VACUUM: デッドタプルを削除するためのコマンド。これからは VACUUM を実行する前に、デッドタプルがあるか確認してください。そしてデッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。 VACUUM は、削除されたタプルによって占められた領域を回収します。 PostgreSQLの通常動作では、削除されたタプルや更新によって不要となったタプルはテーブルから物理的に削除されません。 これらのタプルはVACUUMが完了するまで存在し続けます。 そのため、特に更新頻度が多いテーブルでは、VACUUMを定期的に実行する必要があります。https://www.postgresql.jp/document/8.0/html/sql-vacuum.html PostgreSQL は同時実行される読み込みや書き込みを支援するために MVCC (Multiversion Concurrency Controll / 多版型同時実行制御) を使用します。行が更新された時、タプル(新たなバージョンの行)が作成され、表に新たに挿入されます。古いバージョンの行はデッドタプルとして参照されますが、物理的には削除されず、その後に実行されるトランザクションからは不可視としてマークされます。 書き込み回数の多いデータベースの場合は、自動バキュームを頻繁に実行するようにチューニングすることをお勧めします。そうすることで、テーブルやインデックスを膨らませるデッドタプルの蓄積を避けることができます。

Amazon RDS for PostgreSQL バージョン 9.6.3 のデフォルト設定では、autovacuum_vacuum_scale_factorが 0.1 となっています。これはタプル全体の10パーセント以上がデッドタプルとなった時に当該表がバキュームすべきものとして認識されることを意味します。IOPSの急激な上昇が発生する前には、この閾値に達しているテーブルは存在せず、自動バキュームは開始されていませんでした。しかし、たくさんの表が大きくなり、数億行に達しました。いくつかの表でこの10パーセントの閾値に達した時には、それらの表のタプルは既に数百万行に肥大化していたのです。自動バキュームセッションはそれらの処理のために大量のI/Oと時間を費やすことになりました。