ON CONFLICTとは PostgreSQLのINSERT文にはON CONFLICT句があり、一意制約や主キー制約に違反した場合の挙動を指定できる。ON CONFLICT句を使うと、行が存在しなければINSERT、既に存在すればUPDATEするUPSERT処理を1つの文で記述できる。

pg_stat_progress_vacuumとは 大きなテーブルにVACUUMを実行すると、完了まで数分から数時間かかることがある。pg_stat_progress_vacuumは、現在実行中のVACUUMがどこまで進んでいるかを確認できるビューである。手動実行のVACUUMだけでなく、【PostgreSQL】autovacuumが実行されているか確認する で扱ったautovacuumの進捗も同じビューで確認できる。

autovacuumとは PostgreSQLは行をUPDATE・DELETEしても、既存の行を直接書き換えず新しいバージョンの行を追加する。古い行は不要領域（dead tuple）として残り続け、放置するとテーブルとインデックスが肥大化し、検索性能も低下する。autovacuumはこのdead tupleを定期的に回収するバックグラウンドプロセスであり、デフォルトで有効になっている。

関数を使った検索はインデックスが効かない email列にインデックスがあっても、lower(email)のように関数を適用した式で検索すると、そのインデックスは使われない。 CREATE INDEX idx_users_email ON users (email); EXPLAIN SELECT * FROM users WHERE lower(email) = 'user500@example.com'; Seq Scan on users (cost=0.00..2235.00 rows=500 width=25) Filter: (lower(email) = 'user500@example.com'::text) email列のインデックスはemailそのものの値に対して構築されているため、lower(email)という別の値には使えずSeq Scanになる。

部分インデックスとは CREATE INDEXにWHERE句を付けると、テーブルの一部の行だけを対象にしたインデックスを作成できる。これを部分インデックスと呼ぶ。全行を対象にした通常のインデックスと比べてサイズが小さくなり、更新時のオーバーヘッドも減らせる。

pg_stat_user_indexesとは pg_stat_user_indexesは、インデックスごとの使用状況を集計するビューである。idx_scan列でそのインデックスが何回検索に使われたかを確認できる。

インデックスの再構築が必要になる場面 インデックスはUPDATEやDELETEを繰り返すと、削除済みの領域を残したまま肥大化することがある。 SELECT pg_size_pretty(pg_relation_size('idx_accounts_balance')); pg_size_pretty ---------------- 1936 kB 同じ行を3回更新すると、インデックスのサイズが4倍近くに膨らむ。

通常のCREATE INDEXが取得するロック 通常のCREATE INDEXは対象テーブルにShareLockを取得する。ShareLockはINSERT・UPDATE・DELETEが取得するRowExclusiveLockと競合するため、インデックス作成が完了するまで書き込みがブロックされる。

Index Only Scanとは Index Only Scanはインデックスだけを読んでテーブル本体（ヒープ）にアクセスせず結果を返す実行方式である。テーブルへのアクセスが不要な分、通常のIndex Scanより高速になる。

pg_stat_statementsの有効化 pg_stat_statementsは、実行されたSQL文ごとに実行回数や実行時間を集計する拡張機能である。postgresql.confのshared_preload_librariesに追加し、PostgreSQLを再起動してからCREATE EXTENSIONで有効化する。