X(Twitter) Zenn GitHub RSS 共有

PostgreSQL

作成日時：2025-01-23
更新日時：2025-01-23

PostgreSQL

追記型アーキテクチャ。
自動VACUUMあり。

cron使って何らかの処理を定期実行できる。
pg_cron。

テンプレート

PostgreSQLのやつ。
新規DB作成時のもとになる。

FOREIGN DATA WRAPPER

FDW。

列指向テーブル

HydraやCitusといったextensionを使用すれば、列指向テーブルを使えるらしい。

UPSERT

MERGEとON CONFLICTがある。
MERGEは削除出来たりもする。

RETURNING句

更新された行のデータを返してくれる。
標準SQLだが、MySQLには無いっぽい。

6.4. 更新された行のデータを返す

実行計画

EXPLAIN

一番深い階層から実施。
同一階層は上から実施。

QUERY PLAN
HashAggregate  (cost=2821.37..2921.37 rows=10000 width=16) (actual time=44.517..46.098 rows=10000 loops=1)
  Group Key: orders.order_id
  Batches: 1  Memory Usage: 1425kB
  ->  Hash Join  (cost=270.00..2073.61 rows=99701 width=12) (actual time=1.614..28.700 rows=99999 loops=1)
        Hash Cond: (order_details.order_id = orders.order_id)
        ->  Seq Scan on order_details  (cost=0.00..1541.00 rows=100000 width=8) (actual time=0.012..5.334 rows=100000 loops=1)
        ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=1.545..1.546 rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 519kB
              ->  Seq Scan on orders  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.006..0.574 rows=10000 loops=1)
Planning Time: 0.255 ms
Execution Time: 46.722 ms

行セキュリティポリシー(RLS)

ユーザ単位でアクセスできる行を制限できる。

5.7. 行セキュリティポリシー

SerialとIDENTIFY

PostgreSQLで連番を自動生成するIDENTITY列。SERIALとどちらを使うべきか | フューチャー技術ブログ

VACUUM FULL後の大量データ投入処理が遅い

概要

大量データの投入処理において、VACUUM前は数秒で終わったが、
VACUUM FULL後は数分かかった。

原因

テーブルの物理サイズが最小化される。
余分に確保している領域も小さい。
その状態で大量のデータを登録すると、新しく記憶領域を確保しなければならない。
これでパフォーマンス低下。

対応

事前に領域を確保しておく。
ダミーデータ投入とかで。

INSERT INTO your_table 
SELECT /* ダミーデータ */ 
FROM generate_series(1, 400000);
DELETE FROM your_table;

投入の過程で記憶領域を確保する。
その後に削除しても、確保した記憶領域はそのまま。

メモ

サービスIN直後の大量データ投入とかまずそう。
初期エクステントサイズが1ページとかの場合とか。

SET

SETコマンドは実行時設定パラメータを変更する。
SET LOCALの効果は、コミットのされたかどうかにかかわらず現在のトランザクションが終了するまでしか持続しない。

SET

ルール

第41章 ルールシステム

SQLを置き換えたり、追加出来たりする。

関数とドメイン定義

タイムスタンプ

statement_timeout()⇒SQL実行時のタイムスタンプ。
clock_timestamp()⇒実際の現在時刻
now()とか⇒トランザクション開始時のタイムスタンプ。

createuserと”CREATE USER”コマンド

基本同じでユーザを作成する。
前者はOSのコマンドとして実行できる。
後者はSQL文。

CREATE USER

同時実行制御

MVCC による PostgreSQL の並列性 | Heroku Dev Center

直列化異常

複数のトランザクションを正常にコミットした結果が、それらのトランザクションを1つずつあらゆる可能な順序で実行する場合とは一貫性がない状態。
SERIALIZEだと、直列化異常が発生したらエラーにしてくれる。
回避するためには下記を実施。

ファントムリード

PostgreSQLにおいては、REPEATABLE READ以上でファントムリードは発生しない。
追記型アーキテクチャによるMVCCのため。
各トランザクションは独自のスナップショットを持ち、他のトランザクションによる新しい行の追加が自身のスナップショットには反映されないため。
レコードにトランザクションIDが付与されており、自身のトランザクションIDより古いデータを参照するため、
トランザクション開始後に挿入/更新されたデータは見えない。

参考

FILLFACTOR

ページ内の空き領域の内、どれくらいを挿入用に使うか。
デフォルト値：テーブルは100%、インデックスは90%。

空けておけば、更新時に新しいページの生成を抑止できる。
⇒追記型アーキテクチャ

その他