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

雑多 †

ストリーミングレプリケーション: リアルタイムのレプリケーション 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と時間を費やすことになりました。