Skip to content

PostgreSQL チートシート

高度な機能、拡張性、SQL 準拠を備えた、強力なオープンソースのリレーショナルデータベースシステム。

01

はじめに

psql による接続

PostgreSQL のコマンドラインクライアントが、psql です。バックスラッシュ(\)で始まるのが、メタコマンドです。データベースの一覧には \l を、テーブルの記述には \d を、ヘルプには \? を使うしましょう。ほとんどのドライバがサポートし便利なのが、接続文字列(postgresql://)です。SQL ファイルの実行には、\i filename.sql を使います。パスワードのタイプを避けるには、PGPASSWORD env 変数か ~/.pgpass ファイルを設定しましょう。

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

データベースの作成と削除

新しいデータベースを作るのが、CREATE DATABASE です。owner、encoding、locale、template を指定できます。ロケール固有のデータのないきれいなテンプレートが、template0 で、カスタマイズ可能なのが、template1(デフォルト)です。アクティブな接続がないことを要求するのが、DROP DATABASE です — (13+ の)WITH (FORCE) が先に切断します。自分が接続しているデータベースは落とせません。名前を更新するが、ディスク上のディレクトリを更新しないのが、ALTER DATABASE RENAME です。

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

スキーマと search_path

データベースオブジェクトのための名前空間が、スキーマです — テーブルのためのフォルダのような。デフォルトのスキーマが「public」です。修飾されていない名前を参照したとき、どのスキーマが検索されるかを決めるのが、search_path です。マルチテナントアプリの整理、関心の分離、バージョン管理にスキーマを使いましょう。依存オブジェクトを落とすのが CASCADE で、オブジェクトが存在すれば拒否するのが、RESTRICT(デフォルト)です。1 つの接続内のマルチテナンシーには、データベースよりスキーマの方が柔軟です。

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

設定

postgresql.conf に置かれるのが、PostgreSQL の設定です。現在値を読むのが SHOW で、セッションのために変えるのが SET、現在のトランザクションのためだけが SET LOCAL、永続するデフォルトを設定するのが、ALTER DATABASE/ROLE です。サーバーの再起動を必要とするのが、一部の設定(shared_buffers、max_connections)で、即座に適用されるのが、他(work_mem、statement_timeout)です。再起動なしで設定ファイルの変更を適用するのが、pg_reload_conf() です。遅いクエリのためには、log_min_duration_statement を監視しましょう。

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

psql のスクリプティングと変数

psql がスクリプティングをサポートするのは、変数(\set)、ファイルの取り込み(\i)、出力のリダイレクト(\o)、シェル実行(\!)です。:varname により代入されるのが、変数です。スクリプティングのループと条件分岐には、\set を使いましょう。ファイルを実行して exit するのが、-f フラグです。バッチジョブと移行に有用です。複雑なスクリプティングのためには、PL/pgSQL の関数か、移行管理のための sqitch や Flyway のような外部ツールを検討しましょう。

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

テーブルと制約

テーブルの作成

自動増分整数のためのシーケンスを自動作成するのが、serial です。新しいテーブルには、代わりに(SQL 標準であり PG10 以降推奨の)GENERATED ALWAYS AS IDENTITY を使いましょう。制約:PRIMARY KEY(一意 + 非 null)、UNIQUE、NOT NULL、CHECK、REFERENCES(外部キー)です。親が削除されたとき依存行を削除するのが、ON DELETE CASCADE で、FK を NULL に設定するのが、SET NULL です。セッション後に消えるのが、TEMP のテーブルです — 中間結果に最適です。

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Identity 列(PG10+)

serial の SQL 標準の代替が、GENERATED ALWAYS AS IDENTITY です。シーケンスを列に結び付けるため、列の削除がシーケンスを落とします(serial が孤立シーケンスを残します)。ID の手動挿入を防ぐのが、ALWAYS です(強制するには OVERRIDING SYSTEM VALUE を)。serial のように振る舞うのが、BY DEFAULT です(手動挿入を許します)。新しいテーブルには identity 列を使い、既存のものには serial で構いません。どちらも下ではシーケンスを使います。

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

テーブルの変更

テーブルを再作成せずにスキーマを進化させるのが、ALTER TABLE です。デフォルト(非揮発)付きの列追加が、PG11 以降は速いです(フルのテーブルの書き換えなし)。変換のための USING 句と共に、フルの書き換えを必要とし得るのが、列タイプの変更です。依存オブジェクト(ビュー、外部キー)を落とすのが、CASCADE です — 慎重に使いましょう。将来の管理を容易にするため、名前付きの制約を加えましょう。スクリプトを冪等にするのが、IF EXISTS/IF NOT EXISTS です。

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

制約

データベースレベルでデータの整合性を強制するのが、制約です。アプリのチェックより必ずこれを推奨しましょう。中間テーブルに機能するのが、複合キーです。PK の変化を FK へ伝播させるのが、ON UPDATE CASCADE で、FK を null にするのが、ON DELETE SET NULL です(FK 列は nullable でなければなりません)。コミット時(ステートメントごとではなく)に確認するのが、DEFERRABLE の制約です。循環参照に有用です。管理しやすいよう制約に名前を付けましょう — 自動生成された名前が醜く、データベース間で異なります。

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

テーブルの継承

PostgreSQL 固有の機能が、テーブルの継承です。親から列を継承するのが、子テーブルです。ONLY を使わない限り、親のクエリが、すべての子からの行を返します。テーブルのパーティショニング(宣言的パーティショニング以前)と分類のモデル化に有用です。しかし、継承には癖があります:外部キーが伝播せず、UNIQUE の制約がテーブルごとにしか適用されません。新しいパーティショニングの必要性には、(PARTITION BY の)宣言的パーティショニングを代わりに使いましょう — より堅牢でより良くサポートされています。

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

データ型

数値型

データにサイズが合う整数型を使いましょう — 小さい範囲には smallint、大きいものには bigint です。real/double より遅いが正確なのが(金銭には不可欠)、numeric/decimal です。金銭に float を使ってはいけません — 丸め誤差が蓄積します。レガシーなのが、serial で、GENERATED AS IDENTITY を推奨しましょう。ロケールを意識し不柔軟なのが、money 型です — 代わりに numeric(10,2) を。隙間のある自動増分では、ロールバック/クラッシュでシーケンスが数をスキップし得ます(パフォーマンスのための設計です)。

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

テキストと文字型

PostgreSQL では、varchar、char、text が同一の性能を持ちます。無制限には text を、長さの制約が必要なときには varchar(n) を推奨しましょう。スペースで埋められ、まれにしか有用でないのが、char(n) です。メタデータのみの(速い)操作であるのが、varchar の長さ変更です。% と _ のワイルドカードを使うのが LIKE で、大文字小文字を区別しないのが、ILIKE、POSIX 正規表現が ~ です。全文検索には、tsvector/tsquery を(後にカバーします)。テキストは常に text/varchar として保存し、bytea としてではありません。

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

日付と時刻型

タイムスタンプには必ず timestamptz(timestamp ではなく)を使いましょう — UTC で保存し、表示時にセッションのタイムゾーンへ変換します。マルチタイムゾーンのアプリでバグを引き起こすのが、(tz なしの)naive な timestamp です。算術に強力なのが、interval です(「1 day」、「2 hours」、「3 months」)。単位へ切り下げるのが、date_trunc です。柔軟なのが、to_char/to_timestamp の形式文字列です。セッションかロールごとにタイムゾーンを設定しましょう — データはかかわらず UTC で保存されます。

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON と JSONB

バイナリの JSON であるのが、jsonb です。クエリとインデックスが速く、挿入がわずかに遅く、キーの順序を保ちません。すべての新しい作業には jsonb を使いましょう。jsonb を返すのが、-> で、テキストを返すのが、->> です。最も強力な JSON 演算子であり、GIN インデックスと良く機能するのが、@>(包含)です。パスを修正するのが、jsonb_set、オブジェクトをマージするのが ||、キーを取り除くのが、- です。JSON フィールドの頻繁なクエリのためには、抽出された値の上に、GIN インデックスか式インデックスを加えましょう。リレーショナルデータベース内の柔軟なスキーマに理想的なのが、JSONB です。

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

配列とカスタム型

(ほとんどの言語と違い)1 起点であるのが、PostgreSQL の配列です。主要なクエリ演算子が、ANY と @> で、配列を行へ展開する(結合に最適)のが、unnest() です。強力ですが、賢明に配列を使いましょう — 配列要素のクエリ/フィルタリングを頻繁に行うなら、正規化されたテーブルの方が良いかもしれません。挿入時に順序付けされ検証されるのが、Enum です。構造化された列を可能にするのが、composite タイプです。複雑なネストデータには、composite の配列の代わりに JSONB を検討しましょう。

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

CRUD 操作

INSERT

挿入/更新/削除された行を返し、挿入後の別の SELECT を不要にするのが、PostgreSQL の機能である RETURNING です。強力なのが、ON CONFLICT(UPSERT)です:提案された行を参照するのが、DO UPDATE の EXCLUDED で、静かにスキップするのが、DO NOTHING です。個々の挿入よりはるかに速いのが、マルチ行の挿入です。テーブル間でデータをコピーするのが、INSERT ... SELECT です。一括ロードには COPY を(さらに速い)— SQL のパースのオーバーヘッドをバイパスするからです。

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

すべての行を更新する意図でない限り、WHERE を必ず含めましょう。更新での結合を可能にするのが(非標準だが強力)、UPDATE ... FROM です。何が変わったか示すのが、RETURNING です — 監査には不可欠です。単一のステートメントでの条件付き更新を可能にするのが、CASE 式です。値を変えない更新が no-op になります(WAL なし、ステートメントレベルのトリガー発火なし)。テーブルを肥大させ得るのが、大きな更新であるため — バッチ化し、その後に VACUUM しましょう。

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE と TRUNCATE

行を 1 行ずつ取り除くのが、DELETE です(トリガーを発火し、トランザクションで見えます)。すべての行を即座に取り除くのが、TRUNCATE です(DDL、同じやり方ではトランザクション的でなく、RESTART IDENTITY でシーケンスをリセットします)。テーブル全体のクリアには TRUNCATE を使いましょう — 桁違いに速いです。このテーブルを参照する外部キー付きのテーブルを truncate するのが、CASCADE です。監査ログに有用なのが、RETURNING 付きの DELETE です。DELETE には必ず WHERE を使いましょう — なければ、(トランザクションの中なら安全に、コミットすれば破滅的に)すべてを削除します。

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

SELECT の基本

グループごとの最初の行を返す PostgreSQL の拡張が、DISTINCT ON です。「カテゴリごとの最新」のクエリに強力です(DISTINCT ON の列を最初に ORDER BY しなければなりません)。単純なページネーションだが、大きなオフセットでは遅いのが、LIMIT/OFFSET です。より良いパフォーマンスのためには、キーセットのページネーション(WHERE id > last_id)を使いましょう。スキーマ変更時の驚きを避けるため、本番では(SELECT * ではなく)列を必ず明示的に指定しましょう。SQL 標準の構文が、FETCH FIRST n ROWS ONLY です。

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE(PG15+)

(SQL 標準、PG15+ の)MERGE が、ON CONFLICT より強力です:一致条件に基づき、1 つのステートメントで挿入、更新、「かつ」削除ができます。複雑な同期操作(ETL、データウェアハウスのマージ)に使いましょう。単純な upsert には、より簡潔で広くサポートされているのが、ON CONFLICT です。ソース(テーブルかクエリ)と一致条件を必要とするのが、MERGE です。アクションを定義するのが、WHEN MATCHED/NOT MATCHED 句です。きめ細かい制御のための省略可能な AND 条件を、各分岐が持てます。

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

クエリとフィルタリング

WHERE と条件

大文字小文字を区別しない LIKE(PostgreSQL の拡張)が、ILIKE です。POSIX 正規表現が ~(大文字小文字を区別)、大文字小文字を区別しないのが ~*、否定が !~ です。NULL を比較可能な値として扱うのが、IS DISTINCT FROM です(NULL = NULL は真でなく NULL です)。配列とサブクエリで機能するのが、ANY/ALL です。複雑なテキストのマッチングには、パフォーマンスのために全文検索(tsvector)か trigram のインデックス(pg_trgm)を検討しましょう。大きなテーブルには、WHERE 句で使われる列を必ずインデックスしましょう。

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY とページネーション

大きな dataset では OFFSET より劇的に速いのが、(WHERE id > last_id の)キーセットのページネーションです — OFFSET が行をスキャンし破棄します。大きなテーブルで遅いのが、ORDER BY random() で、統計的なサンプリングには TABLESAMPLE を使いましょう。NULL がどこでソートされるかを制御するのが、NULLS FIRST/LAST です(ASC/DESC によりデフォルトが異なります)。ページネーション時には必ず ORDER BY を含めましょう — なければ、行の順序が未定義です。安定したページネーションのためには、一意の列で順序付けましょう。

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY と集約

グループ化列により行を折りたたむのが、GROUP BY で、グループごとに計算されるのが、集約(count、sum、avg、min、max)です。グループをフィルタするのが、HAVING です(グループ化の前に行をフィルタするのが、WHERE です)。各レベルで小計を加えるのが、ROLLUP、すべての組み合わせを加えるのが、CUBE、欲しいグループ化を正確に指定できるのが、GROUPING SETS です — レポーティングにすべて強力です。行を数えるのが count(*) で、非 null の値を数えるのが、count(column) です。一意のカウントには、count(DISTINCT col) を。

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT と集合の操作

結果を組み合わせ重複を取り除くのが、UNION です(遅い)。重複を保つのが、UNION ALL です(速く、重複がないと分かっているか気にしないなら推奨)。共通の行を返すのが、INTERSECT で、差を返すのが、EXCEPT です。互換的な列のタイプと数を要求するのが、集合の操作です。PostgreSQL の宝石が、DISTINCT ON です:ORDER BY に基づきグループごとの最初の行を返します — 「カテゴリごとの最新/最も高価」のクエリに完璧です。連鎖できるのが、すべての集合の操作ですが、優先順位を制御するには括弧を使いましょう。

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE と条件ロジック

SQL へ if/then/else のロジックを加えるのが、CASE 式です。値に一致するのが、simple CASE で、条件を評価するのが、searched CASE です。条件付きのカウントのため、集約内の CASE よりきれいなのが、(PostgreSQL の)FILTER 句です。SELECT、WHERE、ORDER BY、そして GROUP BY さえ、CASE が現れ得ます。データベースに属すべき、ピボット、条件付きの整形、ビジネスロジックに使いましょう。複雑なマルチ分岐のロジックには、関数かビューを検討しましょう。

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

結合

INNER と OUTER の結合

一致する行だけを返すのが、INNER JOIN です。すべての左の行を保ち(一致がなければ右の列が NULL)、これが「存在すれば関連データを含める」の最もよくある結合です。めったに使われないのが(LEFT として書き直す)、RIGHT JOIN です。すべてを保つのが、FULL OUTER JOIN です。一致のない行を見つけるアンチ結合のパターンが、LEFT JOIN の後の WHERE p.id IS NULL です。曖昧さを避けるため、列名は必ずテーブルの別名(u.id、p.user_id)で修飾しましょう。

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

マルチテーブルの結合

多くのテーブルを PostgreSQL は結合できますが、各結合がコストを加えるため — 結合がインデックスを使うことを確保しましょう。階層データに有用なのが、自己結合です(深い階層には再帰 CTE を検討しましょう)。集約での結合では、(count(*) でなく)count(p.id) 付きの LEFT JOIN が、投稿のないユーザーに 0 を与えます。オプティマイザーが結合の順序を選びますが、明快なクエリを書くことが助けになります。複雑なマルチテーブルのクエリには、ビューの作成を検討しましょう。

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN と LATERAL

デカルト積を作るのが(A のすべての行と B のすべての行)、CROSS JOIN です。めったに欲しいものではありませんが、データ生成のための generate_series と共に有用です。強力なのが、LATERAL です:サブクエリが外側のクエリの列を参照でき、行ごとの関数のように。グループごとのトップ N のクエリに理想的です(単純なケースには、窓関数よりはるかにきれいです)。一致のない行を含めるのが、LEFT JOIN LATERAL ... ON true です。

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

結合の戦略(EXPLAIN)

テーブルのサイズ、インデックス、統計に基づき、PostgreSQL が結合の戦略を選びます。O(N*M) であるのが、Nested Loop です — 小さい、またはインデックス付きのルックアップには問題ありません。より小さい入力上にハッシュテーブルを構築しプローブするのが、Hash Join で — 大きいソートされていない結合に優れています。両方の入力がソートされていることを要求するのが、Merge Join です — インデックスが順序を提供するとき素晴らしいです。本番で戦略を強制せず、オプティマイザーに決めさせましょう。本物のタイミングを見るには、EXPLAIN (ANALYZE) を使い、必要ならインデックス/統計を調整しましょう。

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

結合のパフォーマンスのヒント

最も大きな影響のある結合の最適化が、外部キー列のインデックスです。PostgreSQL は FK のためにインデックスを自動作成し「ません」。ANALYZE で統計を新鮮に保ちましょう(auto-vacuum が行いますが、一括ロード後の手動 ANALYZE が助けになります)。結合列を関数で包むのを避けましょう(インデックスの使用を防ぎます)。必要な列だけを選びましょう。オプティマイザーがパーティションを剪定できるよう、巨大なテーブルにはパーティショニングを検討しましょう。pg_stat_statements で遅い結合を監視しましょう。

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

集約と窓関数

集約関数

count/sum/avg を超える豊かな集約を PostgreSQL が持ちます。値を文字列/配列へ連結するのが、string_agg と array_agg です — N+1 クエリの回避に最適です。真偽値を集約するのが、bool_or/bool_and です。正確なパーセンタイルを計算するのが、percentile_cont です(中央値 = 0.5)— 分析に非常に有用です。最も頻繁な値を返すのが、mode() です。条件付き集約には、(CASE よりきれいな)FILTER 句(count(*) FILTER (WHERE...))です。出力の順序を制御するのが、集約内の ORDER BY です。

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

窓関数

現在の行に関連する行の集合をまたいで計算するのが、窓関数です — GROUP BY のように行を折りたたみません。窓のグループを定義するのが、PARTITION BY で、内部の順序を定義するのが、ORDER BY です。常に一意なのが、ROW_NUMBER で、同点の後で数をスキップするのが、RANK、スキップしないのが、DENSE_RANK です。他の行へのアクセスが、LAG/LEAD です — 時系列の分析に完璧です。窓に含まれる行を制御するのが、フレーム句(ROWS BETWEEN ...)です。WHERE/GROUP BY/HAVING の後に評価されるのが、窓関数です。

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

窓のフレームと NTILE

窓関数がどの行を見るかを制御するのが、窓のフレームです。集約のためのデフォルトのフレームが、(累計の)RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。LAST_VALUE では、フレームを UNBOUNDED FOLLOWING へ広げなければならず、さもなければ(デフォルトのフレームが現在で終わるため)現在の行を返します。行を N バケットへ分割するのが、NTILE です — 四分位数/十分位数に有用です。フレームの境界の行からの値を返すのが、FIRST_VALUE/LAST_VALUE です。窓関数をマスターする鍵が、フレームの理解です。

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

FILTER 句

集約内の CASE よりきれいで、しばしば速いのが、PostgreSQL の拡張である FILTER 句です。複数の条件付き集約が必要な、ピボット風のレポートに特に有用です。クエリがテーブルを一度スキャンし、すべての集約を 1 パスで計算します — 複数のサブクエリよりはるかに効率的です。(多くの PG の拡張と違い)SQL 標準の一部が、FILTER であり、他のデータベースもサポートします。

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

共通テーブル式(CTE)

クエリの読みやすさを改善し、サブクエリの再利用を可能にするのが、CTE(WITH 句)です。デフォルトで非再帰 CTE をインライン化するのが、PostgreSQL 12+ です(そのため、性能がサブクエリと一致します)。12 より前では、最適化のフェンスが、CTE でした。階層/ツリーデータに強力なのが、RECURSIVE の CTE です — アンカーのクエリが種を蒔き、再帰の部分が成長します。1 つのステートメントでの複雑なデータ移動(あるテーブルからの削除、別のテーブルへのアーカイブ)を可能にするのが、RETURNING 付きの CTE です。複雑なクエリを読みやすいステップに分解するのに、CTE を。

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

インデックス

B-Tree インデックス

デフォルトで最もよくあるインデックスタイプが、B-tree です — 等価、範囲、ソートに対応します。左から右へ検索されるのが、複合インデックスであるため、等価列を先に置き、その後に範囲/ソート列です。スペースを節約し速いのが、(WHERE 句の)部分インデックスです — よくあるフィルタ済みのクエリ(WHERE active = true など)に使いましょう。関数の結果のインデックスを可能にするのが、式インデックスです。書き込みをブロックせず構築するのが(遅いが本番には安全)、CONCURRENTLY です。外部キーを必ずインデックスしましょう!

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

GIN と GiST インデックス

各行が複数のインデックス付きの値(JSONB のキー、配列の要素、テキストのトークン)を持つとき、理想的なのが、GIN インデックスです。@>(包含)、?(キーの存在)、全文の @@ 演算子をサポートします。大きいが、これらのクエリを効率的に扱うのが、GIN インデックスです。幾何データ(点、多角形)とレンジタイプ向けなのが、GiST で — 重なり(&&)、包含(@>)、被包含(<@)をサポートします。構築/更新が B-tree より遅いのが、GIN と GiST の両方ですが、B-tree ができないクエリを可能にします。JSONB と全文には GIN を、空間とレンジのクエリには、GiST を。

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN とその他のインデックスタイプ

極めてコンパクトな(GB でなく KB)のが、BRIN(Block Range INdex)です。ブロックの範囲ごとに min/max を保存します。物理的な行の順序がクエリの順序と一致するとき(新しいデータが追記される時系列のログなど)、効果的です。ほとんどのブロックを取り除く「十分に良い」フィルタリングを与えるのが、BRIN で、PostgreSQL が残りの行を確認します。巨大な追記のみのテーブルにとって、ゲームチェンジャーが BRIN です。等価にしか対応しない(レンジでない)が、ポイントルックアップに速いのが、ハッシュインデックスです。IP ルーティングテーブルのような、不均衡なデータに適したのが、SP-GiST です。

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

インデックスの保守と分析

pg_stat_user_indexes でインデックスの使用を監視しましょう — 未使用のインデックスがスペースを浪費し書き込みを遅くします。重複の除去(同じ列、同じテーブル)をしましょう。肥大化したインデックスを再構築するのが、REINDEX です(重い更新/削除で起きます)。ロックを避けるのが、(12+ の)CONCURRENTLY です。正常であるのが、インデックスの肥大です。autovacuum がインデックスを保守しますが、時々手動の REINDEX が要ります。非常に大きなインデックスには、REINDEX CONCURRENTLY か pg_repack(ロックなしで再構築するサードパーティツール)を検討しましょう。インデックスの変化は、ステージングで必ずテストしましょう。

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

インデックスの戦略

PK(自動インデックス)と FK(手動)から始めましょう。ホットなクエリの経路のためにインデックスを加えましょう。最も左の接頭辞のルールに従うのが、複合インデックスです — 選択性とクエリパターンにより列を順序付けましょう。行の取得なしで、(インデックスのみのスキャン)インデックスだけでクエリを満たさせるのが、(カバリングインデックスの)INCLUDE です — 読み取りが重いワークロードには巨大です。すべてをインデックスしてはいけません。各インデックスが書き込みのオーバーヘッドを加えます。pg_stat_user_indexes を使い、定期的に見直し刈り込みましょう。インデックスの使用を検証するには、必ず EXPLAIN を。

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

トランザクションと同時実行

トランザクションの基本

ステートメントをアトミックな単位へグループ化するのが、トランザクションです — すべてが成功するか、すべてが失敗します。始めるのが BEGIN、保存するのが COMMIT、取り消すのが ROLLBACK です。トランザクション内の部分的なロールバックを可能にするのが、savepoint です — 複雑な操作でのエラー回復に有用です。デフォルトで、各ステートメントを PostgreSQL が自動コミットします。マルチステートメントの操作(送金、マルチ行の挿入)には、必ず明示的なトランザクションを使いましょう。ロックの競合とデッドロックを減らすため、トランザクションは短く保ちましょう。

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

分離レベル

ほとんどのアプリには良いのが、PostgreSQL のデフォルト(READ COMMITTED)です — ダーティリードはなく、同じトランザクション内の再読み込みが、新しいデータを見得ます。SQL 標準より強いのが、PostgreSQL の REPEATABLE READ です — (スナップショット分離を使い)ファントムも防ぎます。最も強く、競合するトランザクションを中止し得るのが、SERIALIZABLE です — アプリが、SQLSTATE 40001 で再試行しなければなりません。ほとんどの Web アプリには、READ COMMITTED で十分です。リトライロジック付きで、重要な正しさ(財務の台帳)のためには、SERIALIZABLE を。

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

ロックとデッドロック

更新のための行をロックするのが(同時修正を防ぐ)、SELECT FOR UPDATE です — 「読み、確認し、更新する」のパターンに有用です。整数キーを使うアプリケーションレベルのロックが、advisory ロックです — 分散プロセスの調整に最適です。2 つのトランザクションが互いを待つとき起きるのが、デッドロックです。PostgreSQL が検出し 1 つを中止します(捉えて再試行しましょう)。デッドロックを避けるには、トランザクションをまたいで一貫した順序でロックを取得しましょう。トランザクションは短く保ちましょう。塞がったセッションのためには、pg_locks を監視しましょう。

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC とスナップショット分離

読み手が書き手を決してブロックせず、その逆もないのが、PostgreSQL の MVCC です — 各トランザクションがスナップショットを見ます。新しい行のバージョンを作るのが、UPDATE です。VACUUM が取り除くまで、古いバージョンが残ります。古いバージョンの後始末を防ぐのが、長期間実行されるトランザクションであり、肥大を引き起こします。トランザクションは短く保ちましょう!「idle in transaction」のセッションを監視しましょう — スナップショットを保持し後始末をブロックします。重大な問題が、トランザクション ID のラップアラウンドです(autovacuum が防ぎますが、autovacuum が苦戦していたら監視しましょう)。

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

ロックのパターン

行を悲観的にロックするのが、FOR UPDATE です — 重大な一貫性(送金)に使いましょう。低い競合には良いのが、楽観的ロッキング(version 列)です:ロックがなく、競合で再試行するだけです。job キューに完璧なのが、SKIP LOCKED です — 複数の worker がブロックせず異なるジョブを掴みます。ロックされていたら速く失敗するのが、NOWAIT です(「更新を試みるが待たない」に)。アプリケーションレベルの操作(移行、cron ジョブ)を調整するのが、advisory ロックです。高い競合には悲観的を、低い競合には楽観的を選びましょう。

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

ビューとマテリアライズドビュー

ビューの作成

テーブルのように振る舞う保存されたクエリが、ビューです。自動的に更新可能なのが(単一のテーブル、集約なし)、単純なビューです — 挿入/更新が基底テーブルへ通ります。行がビュー越しに見えなくなる挿入/更新を防ぐのが、WITH CHECK OPTION です。複雑なクエリの単純化、セキュリティの強制(列レベルのアクセス)、安定した API の提供に、ビューを使いましょう。依存オブジェクトを落とすのが、CASCADE です。データを保存せず(毎回クエリを実行します)、そののが、ビューです。

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

マテリアライズドビュー

クエリの結果を保存するのが、マテリアライズドビューです。クエリがはるかに速いが、refresh されるまで古いです。高コストな集約(ダッシュボード、レポート)に使いましょう。ビューをロックせず refresh するのが(読み手が動き続けます)、CONCURRENTLY ですが、一意のインデックスが要ります。pg_cron や外部ツールで refresh をスケジュールしましょう。リアルタイム風のデータには数分ごとに、歴史的分析には日次です。トレードオフが、新鮮さ対クエリ速度です — 大きいテーブルでの速い分析の鍵が、マテリアライズドビューです。

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

セキュリティのビュー

強力なセキュリティの道具であるのが、ビューです。テーブルへのアクセスを付与せず、ユーザーが見るべき列/行だけを公開します。ビュー上に SELECT を付与し、基底テーブル上にはしません。行レベルのセキュリティのためには、ビューの代わりに、PostgreSQL の組み込みの RLS(Row Level Security)のポリシーを検討しましょう — より堅牢で、直接のテーブルアクセスとも機能します。ユーザーごとのフィルタリングを可能にするのが、セッション変数(current_setting)付きのビューです。アプリからスキーマの複雑さを隠す、安定した API 層を作るのに、ビューを使いましょう。

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

ビューの管理

制限があるのが、CREATE OR REPLACE VIEW です — 末尾に列を加えられますが、取り除き・並べ替えられません。重大な変化には、落として再作成しましょう(しかし、まず依存を確認します)。他のビューに依存し得るのが、ビューであり、基底ビューの削除には CASCADE が要ります。定義の検査には、pg_views を。メンテナンススクリプトに便利なのが、すべてのマテリアライズドビューを refresh する DO ブロックです — しかし、可能な限り、ロックを避けるため CONCURRENTLY を。

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

再帰ビュー

再帰 CTE を包むのが(ツリー/グラフの走査に最良)、再帰ビュー(CREATE RECURSIVE VIEW)です。無限ループ(データの中の循環)を防ぐため、再帰部分に深さ/距離の上限を必ず含めましょう。走査パスを追跡・表示する簡単な方法が、(テキスト連結の)path 列です。非常に深い階層には、より良いパフォーマンスのために、closure テーブルか ltree 拡張を検討しましょう。便利だが、大きな dataset では遅くなり得るのが、再帰ビューです — 結合列にインデックスを加えましょう。

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

関数と演算子

文字列関数

包括的な文字列関数を PostgreSQL が持ちます。部分文字列を見つけるのが、position() と strpos() です。SQL 標準なのが、FROM/FOR 付きの substring() で、よりコンパクトなのが、substr() です。区切りデータに便利なのが、split_part() です。各単語を大文字にするのが、initcap() です。文字列を固定幅へパディングするのが(整形に有用)、lpad/rpad です。文字列と配列の間で変換するのが、string_to_array/array_to_string です。パターンマッチングのほとんどのニーズを、LIKE/ILIKE(ワイルドカード)、~(正規表現)、SIMILAR TO(SQL パターン)がカバーします。

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

日付/時刻関数

等価なのが(トランザクション開始時刻)、now() と current_timestamp です。コンポーネントを取得するのが、EXTRACT/date_part です。単位へ切り下げるのが、date_trunc で — 時間バケット化データには不可欠です。インターバルを返すのが(人間可読)、age() です。整形/パースを扱うのが、to_char/to_date/to_timestamp です。時系列データのギャップの充填には、日付付きの generate_series が信じられないほど有用です(ゼロカウントの日を示すため、LEFT JOIN しましょう)。timestamptz を必ず使い、セッションのタイムゾーンを尊重するのが、日付の算術です。

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

数値と数学の関数

(double ではなく)numeric 上で機能するのが、2 引数付きの round() です — 先に cast しましょう。ラジアンを使うのが、三角関数で(変換には radians()/degrees() を)。0〜1 を返すのが、random() で、範囲のため数学と組み合わせましょう。PostgreSQL の主力が、generate_series です。シーケンス、日付範囲、ギャップの充填のために、その場で行を生成します。便利なのが、gcd/lcm(13+)です。財務の計算には、(正確な)numeric を必ず使いましょう — double 上の round のような浮動小数点関数が、精度の問題を持ち得ます。

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

条件と NULL の関数

最初の非 NULL 値を返すのが、COALESCE です — デフォルト化には不可欠です。値が一致すれば NULL を返すのが、NULLIF で、ゼロ除算の回避には CASE よりきれいです。値を比較するのが、GREATEST/LEAST です(GREATEST では NULL が伝播しますが、LEAST では常にではありません)。一般的な条件式が、CASE です。PG のショートカットが、ISNULL/NOTNULL です。辞書式に比較するのが、行比較(ROW(a,b) < ROW(c,d))です — 複合キーのページネーションに有用です。NULL の挙動の理解(NULL = NULL が真でなく NULL)が、PostgreSQL では決定的に重要です。

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

配列と JSON の関数

データベース内の強力なデータ操作を可能にするのが、配列の関数です:行への配列の展開が(結合に最適)、unnest で、逆が array_agg です。JSONB の演算子:jsonb を返すのが、-> と #> で、テキストを返すのが、->> と #>> です。最重要の JSONB 演算子であり、GIN インデックスを使うのが、@>(包含)です。キーの存在を確認するのが、? です。列から JSON を構築するのが、jsonb_build_object です。JSON を修正するのが、jsonb_set/||/- です。一級のデータ型にするのが、JSONB に対するこれらの関数です — 柔軟でありながらクエリ可能です。

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

トリガー

トリガー関数

trigger を返さなければならず、PL/pgSQL(か他の言語)で書かれるのが、トリガー関数です。トリガー内で利用可能な特殊な変数が、TG_OP、TG_TABLE_NAME、OLD、NEW です。NEW を修正でき(NULL を返すことで操作をスキップできる)のが、BEFORE トリガーです。副作用(監査ログ、通知)のためのものが、AFTER トリガーです。行ごとに発火するのが、FOR EACH ROW で、1 回発火するのが、FOR EACH STATEMENT です。強力ですが、隠れた副作用を引き起こし得るため、横断的関心事(監査、タイムスタンプ)に使い、ビジネスロジックには使いません。

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

ステートメント対行のトリガー

行ごとに発火するのが、FOR EACH ROW トリガーです(一括操作では遅くなり得ます)。1 回発火するのが、FOR EACH STATEMENT です(個々の OLD/NEW へアクセスできませんが、一括のために遷移テーブルを使えます)。行を修正・スキップできるのが、BEFORE トリガーで、副作用のためのものが、AFTER トリガーです。トリガーがいつ発火するかフィルタするのが(行ごとに評価される)、WHEN 句です。すべての変更された行へのアクセスを、ステートメントトリガーに与えるのが、(REFERENCING NEW TABLE/OLD TABLE の)遷移テーブルです — 行ごとのオーバーヘッドなしで、一括操作の監査に最適です。

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

イベントトリガー(DDL)

DDL コマンド(CREATE/ALTER/DROP)で発火するのが、イベントトリガーです。スキーマ変更の監査や、本番での危険な操作の阻止に有用です。コマンドを識別するのに、tg_event と tg_tag を使います。よくあるユースケース:DROP TABLE の阻止、コンプライアンスのためのすべての DDL のロギング、移行スクリプトの自動生成です。コマンドの後に発火するのが(ddl_command_end)、横取りし得るのが、(ddl_command_start の)イベントトリガーです。テーブルレベルでなく、データベースレベルです。控えめに使うこと — すべての DDL 操作にオーバーヘッドを加えます。

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

トリガーの管理

一括ロードのために一時的にトリガーを無効化しましょう(はるかに速い)— しかし、再有効化を忘れないこと!すべてのトリガーとルールを無効化するのが、session_replication_role = 'replica' です — 一括インポートやレプリケーションに使いましょう。より狙いを定めたものが、ALTER TABLE ... DISABLE TRIGGER です。一括ロード後には、トリガーを必ず再有効化しましょう。トリガーの使用を監視しましょう — 行ごとのオーバーヘッドを加えるため、トリガーが隠れたパフォーマンス問題を引き起こし得ます。すべてのトリガーを一覧するのが、information_schema.triggers で、検査には psql の \d+ がより速いです。

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

よくあるトリガーのパターン

よくあるトリガーのパターン:自動タイムスタンプ(updated_at)、監査ログ(コンプライアンスのためのすべての変更の追跡)、ビジネスルールの強制(テーブルをまたぐ検証)、非正規化データの同期(カウンタの維持)です。各パターンが本物の問題を解決しますが、複雑さとオーバーヘッドを加えます。代替を検討しましょう:タイムスタンプにはデフォルト、アプリケーションレベルの監査ロギング、単純なルールには CHECK の制約、非正規化にはマテリアライズドビューです。ロジックがデータベースに住まなければならないとき(複数のアプリがデータへアクセスする、またはルールが交渉不可)、トリガーを使いましょう。

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

ストアドプロシージャと PL/pgSQL

PL/pgSQL の関数

PostgreSQL の手続き言語が、PL/pgSQL です — SQL へ、変数、条件分岐、ループ、エラー処理を加えます。値かテーブルを返すのが、関数です。クエリの結果を変数へ割り当てるのが、SELECT INTO です。クエリから行を返すのが、RETURN QUERY です。デフォルトを持てるのが、パラメータです。再利用可能なロジック、複雑なクエリ、ビジネスルールのカプセル化に関数を使いましょう。関数は単純で名前が明確に保ちましょう。パフォーマンスのためには、単純なクエリには SQL の関数(LANGUAGE sql)を検討しましょう — オプティマイザーがインライン化し得ます。

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

制御構造

完全な制御構造を PL/pgSQL が持ちます:IF/ELSIF/ELSE、CASE、LOOP/EXIT/CONTINUE、WHILE、FOR(整数範囲)、FOREACH(配列の反復)です。ループを抜けるには、EXIT WHEN を。範囲を反復するのが(含む)、FOR i IN 1..n です。配列を反復するのが、FOREACH です。(安全な識別子の注入のための format() 付きで)動的な SQL を実行するのが、EXECUTE です。データベース内のループは小さく保ちましょう — 大きいループは、可能な限りセットベースの SQL であるべきです。一括操作には、行ごとのループの代わりに、SELECT 付きの INSERT/UPDATE を。

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

ストアドプロシージャ(PG11+)

関数と違うのが、プロシージャ(PG11+)です:トランザクションを管理でき(内側の COMMIT/ROLLBACK)、関数のように値を返さず(代わりに OUT パラメータを使い)、CALL で呼ばれます。トランザクション制御を必要とするマルチステートメントの操作(ETL、データ移行)にプロシージャを使い、値を返す計算とクエリには関数を使いましょう。トランザクションのロジックをカプセル化する SQL 標準の方法が、プロシージャです。PG11 より前には、関数が commit/rollback できませんでした — そのギャップを埋めるのが、プロシージャです。

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

エラー処理

カスタムエラー付きでトランザクションを中止するのが、RAISE EXCEPTION です。(try/catch のような)エラーを捉えるのが、EXCEPTION ブロックで — 優雅な劣化によく使われます。5 文字のエラーコードが、SQLSTATE で、メッセージが、SQLERRM です。役立つコンテキストを加えるのが、USING HINT/DETAIL です。デバッグに素晴らしいのが(psql で見えます)、RAISE NOTICE です。本物のバグを覆い隠すのを避けるため、OTHERS でなく、特定のエラー(division_by_zero、unique_violation)を捉えましょう。EXCEPTION の内側では、トランザクションが中止状態であり、(PG11+ のプロシージャまで)コミット/ロールバックしかできず、さらなるクエリは実行できません。

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

動的な SQL

文字列を SQL として実行するのが、動的な SQL(EXECUTE)です — テーブル/列の名前が変数のときに不可欠です。SQL インジェクションを防ぐため、%I(識別子)と %L(リテラル)付きの format() を必ず使いましょう — ユーザー入力を直接連結してはいけません!安全にパラメータを渡すのが(準備されたステートメントのように)、USING です。強力だが、クエリプランナーと静的分析ツールには不透明なのが、動的な SQL です。必要なとき(動的なテーブル名、DDL 生成)に使い、可能な限り静的な SQL を推奨しましょう。徹底的にテストしましょう — 動的な SQL のエラーが、コンパイル時でなく実行時です。

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

セキュリティと権限

ユーザーとロール

同じエンティティであるのが、PostgreSQL でのユーザーとロールです。LOGIN 権限を持つロールが、「ユーザー」にすぎません。権限を中心的に管理するため、(LOGIN なしの)グループロールを作り、それらを GRANT でログインロールへ付与しましょう。パスワードの有効期限を設定するのが、VALID UNTIL です。ロールごとの同時接続に上限を設けるのが、CONNECTION LIMIT です。マルチテナントの隔離に素晴らしいのが、ロールごとの SET search_path です。個々のユーザーへの権限の付与でなく、権限のグループ化のために、必ずロールを使いましょう。すべての権限とメンバーシップを先に取り除くことを、DROP ROLE が要求します。

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

権限の付与

権限:テーブルには SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER、スキーマには USAGE/CREATE、シーケンスには USAGE/SELECT、関数には EXECUTE、データベースには CONNECT/CREATE/TEMPORARY です。セキュリティに強力なのが、列レベルの付与です。将来のオブジェクト上で権限を付与するのが、ALTER DEFAULT PRIVILEGES です — 新しいテーブルが頻繁に作られるとき不可欠です。serial/identity 列のあるテーブルへ挿入するロールには、シーケンスの USAGE を必ず付与しましょう。最小特権のアクセスのため、過剰な権限を取り消しましょう。

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

行レベルセキュリティ(RLS)

行ごとのアクセス制御を強制するのが、RLS(Row Level Security)です — ポリシーに一致する行だけが、ユーザーに見えます。読み取りをフィルタするのが、USING で、書き込みを検証するのが、WITH CHECK です。現在のユーザーを識別するため、アプリ固有のセッション変数を設定しましょう(SET app.user_id)。テーブルの所有者さえポリシーに従わせるのが、FORCE RLS です。すべての RLS をスキップするのが、BYPASSRLS のロール属性です(管理者用)。マルチテナントアプリに強力なのが、RLS です — ユーザーごとのビューは不要です。注意:RLS のポリシーは正しくなければなりません。静かにデータをフィルタするからです。異なるユーザーで徹底的にテストしましょう。

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

認証と SSL

防御の最初の線が、pg_hba.conf です。どのホスト/ユーザーが、どのように接続できるかを制御します。scram-sha-256(md5 や trust ではなく)を必ず使いましょう。接続のために SSL を要求するのが、hostssl です。暗号化を確保するのが、sslmode=require で、(最も安全な)証明書も検証するのが、verify-full です。本番では:すべてのリモート接続に SSL を使い、既知の IP へ pg_hba.conf を制限し、scram-sha-256 を使いましょう。編集後に pg_reload_conf() でリロードしましょう。未認可の接続のためには、pg_stat_activity を監視しましょう。

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

暗号化と監査

列レベルの暗号化を提供するのが、pgcrypto です。可逆暗号化のための pgp_sym_encrypt/decrypt と、パスワードのハッシュ化のための crypt/gen_salt です。パスワードには bcrypt(bf)を使いましょう — 設計により遅い(総当たりに耐えます)です。コンプライアンスのため、サーバーログへのすべての DDL と DML 操作のロギングが、pgAudit です。機密データには、アプリケーション層での暗号化を推奨しましょう(鍵はデータベースに住むべきではありません)。パスワードは必ずハッシュ化しましょう(可逆的に暗号化してはいけません)。アクティブなクエリの監査のためには、pg_stat_activity を監視しましょう。多層防御のため、これらの道具を組み合わせましょう。

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

バックアップとリストア

pg_dump の基本

論理バックアップ(データベースを再作成する SQL ステートメント)を作るのが、pg_dump です。圧縮され並列リストアと選択的なリストアをサポートするのが、カスタム形式(-F c)です — 必ずこちらを推奨しましょう。並列ダンプとリストアを可能にするのが、ディレクトリ形式(-F d)です。すべてのデータベースに加えて、ロールとテーブルスペースをバックアップするのが(完全なクラスタバックアップに)、pg_dumpall です。ダンプを冪等にするのが(複数回リストア可能)、--clean --if-exists です。大きなデータベースのためには、(ディレクトリ形式のみ)並列ダンプのための --jobs を検討しましょう。

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

カスタム/ディレクトリ形式からのリストアを行うのが、pg_restore です(素の SQL ではありません — それには psql を使う)。マルチコアでははるかに速い、並列リストアを可能にするのが、--jobs=N です。既存オブジェクトを先に落とすのが、--clean --if-exists です。別のユーザーへのリストア時には不可欠なのが、--no-owner です。ダンプの内容を示すのが、--list で、選択的なリストアのためには、--table/--schema を。大きなリストアには、トリガーと制約を一時的に無効化し、その後再有効化しましょう。リストアを必ずテストしましょう — リストアできないバックアップは無価値です。

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

物理バックアップと PITR

データディレクトリ全体のバイナリコピーを作るのが、pg_basebackup です。大きなデータベースでは pg_dump より速く、PITR に対応します。PITR には WAL(Write-Ahead Log)のアーカイブが不可欠です:すべての WAL ファイルをアーカイブし、任意の時点への、ベースバックアップ + WAL のリプレイでリストアしましょう。特定のタイムスタンプへの回復(誤った DROP TABLE のような間違いの取り消し)を可能にするのが、recovery_target_time です。名前付きの回復ターゲットを作るのが、pg_create_restore_point です。バックアップのゴールドスタンダードが、PITR です — 論理バックアップのための pg_dump と組み合わせましょう。

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

レプリケーションのセットアップ

リアルタイムで WAL をレプリカへ送るのが、プライマリです(ストリーミングレプリケーション)。1 コマンドでレプリカをセットアップするのが、pg_basebackup -R です。読み取り専用であり(hot standby)、読み取りクエリに応答できるのが、レプリカです。レプリケーションの遅延を監視しましょう(プライマリでは pg_stat_replication、レプリカでは pg_last_xact_replay_timestamp)。レプリカをプライマリにするのが(フェイルオーバーに使いましょう)、昇格(pg_ctl promote)です。自動フェイルオーバーには、Patroni、repmgr、pg_auto_failover のような道具を使いましょう。レプリカをチェーンすることで、プライマリの負荷を減らすのが、カスケードのレプリケーションです。

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

論理レプリケーション

テーブルレベルで変更を複製するのが、論理レプリケーション(PG10+)です。物理レプリケーションより柔軟です:異なる PostgreSQL のバージョン、選択的なテーブル、各側での異なるスキーマです。PUBLICATION を作るのが、パブリッシャーで、SUBSCRIPTION を作るのが、サブスクライバーです。初期データがコピーされ、その後、変更がリアルタイムで流れます。レプリケーションを止めるのが(既存の PK 付きの INSERT などの)競合です — SKIP か手動の修正で扱いましょう。ダウンタイムゼロのアップグレード(旧バージョンから新バージョンへの複製、その後切り替え)に素晴らしいです。データベース間のデータ共有や部分的なレプリケーションに、使いましょう。

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

パフォーマンスとチューニング

EXPLAIN

クエリ計画を示すのが、EXPLAIN で、本物のタイミングを示すのが、クエリを実行する ANALYZE です。見るべきもの:大きなテーブル上の Seq Scan(インデックスの欠落)、高い Rows Removed by Filter(非効率なクエリ)、外部のソート(work_mem を増やしましょう)。最も速いのが、Index Only Scan です(カバリングインデックスが必要です)。Seq Scan と Index Scan の間にあるのが、Bitmap Scan です。プランナーの推定が「cost」の数値で、本物が「actual time」です。チューニングには必ず EXPLAIN (ANALYZE, BUFFERS) を使い — 本物のボトルネックを明らかにします。古い統計を見つけるため、推定行と本物の行を比較しましょう。

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM と ANALYZE

UPDATE/DELETE で死んだ行を作るのが(VACUUM が取り戻します)、PostgreSQL の MVCC です。自動的に動くのが autovacuum です(ON のままにしましょう!)が、書き込みが重いテーブルのため調整が必要かもしれません。テーブルを書き直し(OS へスペースを取り戻します)、排他ロックを取るのが、VACUUM FULL です。オンラインの再構成のためには、代わりに pg_repack を。プランナーが使う統計を更新するのが、ANALYZE です — 一括ロード後には決定的に重要です。悪いクエリ計画の原因が、古い統計です。テーブルごとに autovacuum_scale_factor を調整しましょう(大きく書き込みが重いテーブルでは低く)。vacuum の遅れの検出には、n_dead_tup を監視しましょう。

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

メモリと設定

PostgreSQL の共有キャッシュが、shared_buffers です(クラシックな推奨が、RAM の 25% です)。プランナーへ OS キャッシュを伝えるのが、effective_cache_size です(RAM の 50〜75% へ設定。ヒントにすぎません)。ソート/ハッシュの操作ごとであるのが、work_mem です。多くの接続と共に高すぎると、メモリを使い尽くし得ます。VACUUM と CREATE INDEX を速くするのが、maintenance_work_mem です。SSD のためには、random_page_cost=1.1 を設定しましょう(デフォルトの 4.0 が遅い HDD を仮定します)。性能に劇的に影響するのが、これらの設定であるため — 変化は必ずベンチマークしましょう。

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

クエリの最適化

最大の勝ち:FK 列のインデックス、一括ロードのための COPY、ページネーションのための OFFSET の回避(キーセットを使う)、インデックス付き列を関数で包まないこと、です。サブクエリのためには、IN よりしばしば速いのが、EXISTS です。インデックスのみのスキャンを防ぎ I/O を浪費するのが、SELECT * です。個々の挿入より 10〜100 倍速いのが、バッチ挿入(マルチ行か COPY)です。プランナーが正確な統計を持つよう、一括ロード後には ANALYZE を実行しましょう。改善を検証するため、変化の前後に必ず EXPLAIN (ANALYZE) を。

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

クエリのモニタリング

最も価値のあるモニタリングツールが、pg_stat_statements です。クエリ数、total/mean 時間、行を追跡します。shared_preload_libraries で有効化しましょう。リアルタイムのクエリを示すのが、pg_stat_activity です(長い duration と「idle in transaction」を見ましょう)。肥大のために、死んだタプルの割合を監視しましょう。落とすべき未使用のインデックス(idx_scan=0)を見つけましょう — スペースを浪費し書き込みを遅くします。遅いクエリを自動的にログするのが、log_min_duration_statement の設定です。体系的にボトルネックを特定・修正するため、これらの道具を EXPLAIN と組み合わせましょう。

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

パーティショニング

レンジパーティショニング

時系列データに理想的なのが、レンジパーティショニングです。正しいパーティションへ挿入を自動的にルートし、クエリでパーティションを剪定するのが(関連するものだけをスキャン)、PostgreSQL です。範囲外のデータを受け止めるため、DEFAULT のパーティションを作りましょう(または将来のパーティションを作るパーティショニングジョブをセットアップしましょう)。独立したテーブルであるのが、各パーティションです。独立に管理できます(遅い DELETE の代わりに DROP TABLE で古いデータを落とします)。非常に大きな dataset に機能するのが、(レンジに続くハッシュの)サブパーティショニングです。pg_partman 拡張で、パーティション作成を自動化しましょう。

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

リストパーティショニング

離散値(地域、カテゴリ、テナント ID)向けなのが、リストパーティショニングです。特定の値のための行を保持するのが、各パーティションです。レンジパーティショニングのように、クエリでパーティションを剪定するのが、PostgreSQL です。マルチテナントのアプリに素晴らしいです — 各テナントのデータが隔離され、独立にバックアップ/リストアでき、別のストレージへ動かせます。予想外の値のためには DEFAULT を。新しい値のための新しいパーティションの追加は、CREATE TABLE ... FOR VALUES IN (...) です。アーカイブに有用なのが、ALTER TABLE ... DETACH PARTITION によるパーティションの分離です。

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

ハッシュパーティショニング

パーティションキーをハッシュすることで、行をパーティションへ均等に分散するのが、ハッシュパーティショニングです — 負荷の分散に最適です。パーティションキーでフィルタするクエリが(WHERE user_id = 42)、1 パーティションへ剪定され、すべてのパーティションをスキャンするのが、レンジのクエリ(WHERE user_id > 100)です。作成時に固定されるのが、パーティション数です(modulus が 2 のべきでなければなりません)— 後の追加が、テーブルの再作成を必要とします。期待するデータサイズに基づき、パーティション数を選びましょう。均等な分散が必要で、常にパーティションキーによりクエリするなら、ハッシュパーティショニングを使いましょう。

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

パーティションの保守

独立したテーブルへパーティションを変えるのが、DETACH PARTITION です。親をロックせず古いデータをアーカイブするのに完璧です。既存のテーブルを新しいパーティションとして加えるのが、ATTACH PARTITION です(一致するスキーマと制約が要ります)。階層化ストレージのためには(古いパーティションをより安いディスクへ動かす)、tablespaces を。時系列には不可欠なのが、パーティション作成と保守の自動化をする、pg_partman です。パーティション剪定のメカニズムが、(デフォルトで「partition」に設定された)constraint exclusion です。将来のパーティションが尽きると、INSERT が失敗するため、パーティション作成は必ず先に計画しましょう。

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

パーティショニングの戦略

パーティションするのは、テーブルが大きくなったとき(>100GB)か、速いデータのライフサイクルが必要なとき(DELETE の代わりに古いパーティションを落とす)です。最もよくあるクエリパターンに基づき、パーティションキーを選びましょう — パーティションキーでフィルタするときだけ、パーティション剪定が機能します。パーティション化されたテーブル上の UNIQUE の制約が、パーティションキーを MUST で含めます(PG が強制します)。パーティション化されたテーブル上のインデックスが、すべてのパーティションへ自動的に適用されます。パーティションが多すぎるのを避けましょう(計画のオーバーヘッドです)。時系列には、月次パーティションがよくある甘味点です。自動化のためには、pg_partman を。

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

応用機能

全文検索

組み込みの全文検索を PostgreSQL が持ちます:tsvector(前処理されたテキスト)、tsquery(検索語)、@@ 演算子です。検索を速くするのが、GIN インデックスです。単語をトークン化しステミングするのが、to_tsvector です(英語、中国語などを — 言語を設定しましょう)。結果をスコアリングするのが、ts_rank で、一致をハイライトするのが、ts_headline です。検索列を自動維持するのが、tsvector_update_trigger です。単純な検索には、これが Elasticsearch に匹敵します — 外部のサービスは不要です。規模の大きい複雑な検索には、Elasticsearch/OpenSearch を検討しましょうが、PostgreSQL の FTS から始めましょう。

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

JSONB の応用クエリ

強力なクエリのために、(PG12+ の)JSONB のパス式が、SQL/JSON のパス言語($)を使います:1 つの式でのフィルタ、述語、抽出です。リレーショナルデータから JSON を構築するのが、jsonb_agg と jsonb_build_object です — API レスポンスに最適です。デフォルトの GIN より小さく速いが、@> とパス演算子(@?、@@)にしか対応しないのが、jsonb_path_ops の GIN インデックスです。一致する配列要素を取り出すのが、jsonb_path_query で、行から JSON 配列を構築するのが、jsonb_agg です — ORM なしで SQL 内に直接 API レスポンスを生成するのに完璧です。

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

PostGIS による地理空間

完全な空間データベースに変えるのが、PostGIS です — CREATE EXTENSION で加えましょう。GPS 座標(lat/lng)には geography を、投影された座標には geometry を使いましょう。距離内の点を見つけるのが、ST_DWithin で、正確に測るのが、ST_Distance です。GiST インデックスを効率的に使う、KNN(最近傍)のクエリを可能にするのが、<-> 演算子です。geometry/geography の列上には、必ず GiST のインデックスを作りましょう。注意:ST_MakePoint が(lng, lat)を取り — 経度が先です!本気の地理空間のワークロードには、専用の GIS システムに匹敵するのが、PostGIS です。

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

Foreign Data Wrappers(FDW)

ローカルのテーブルであるかのように、外部のデータソースをクエリできるのが、FDW(Foreign Data Wrappers)です。他の PostgreSQL サーバーへ接続するのが、postgres_fdw で、CSV ファイルを読むのが、file_fdw です。他の FDW が存在します:MySQL、Oracle、SQLite、REST API 用などです。可能なときは、リモートサーバーへクエリが push down されます(WHERE、JOIN)が、サーバーをまたぐ結合がローカルでデータを取得します。ETL なしでの、データ統合、移行、フェデレーテッドクエリに素晴らしいのが、FDW です。ネットワークのオーバーヘッドのため、ローカルテーブルより遅いのが、性能です。

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

Advisory ロックと同時実行

どのテーブル行にも結び付かない、アプリケーション定義のロックが、advisory ロックです — 調整に完璧です。明示的に解放されるかセッションが終わるまで残るのが、セッションのロックで、COMMIT/ROLLBACK で自動解放されるのが、トランザクションのロックです。ノンブロッキングなのが、pg_try_advisory_lock です — 「worker は 1 つだけ」のパターンに最適です。安全な job-queue 処理のために、FOR UPDATE SKIP LOCKED と組み合わせましょう。bigint か 2 つの int により識別されるのが、ロックです。エラー経路では、ロックと unlock を必ずペアにしましょう。PostgreSQL での安全な並行バックグラウンドジョブシステムの背骨が、advisory ロックです。

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

拡張

拡張の管理

コアの PostgreSQL を超える機能を加えるのが、拡張です — CREATE EXTENSION でインストールしましょう。ディスクにインストールされているものを示すのが、pg_available_extensions で、ロードされているものを示すのが、pg_extension です。データベースごとにインストールされなければならないのが、拡張です。一部(pg_stat_statements のような)が、shared_preload_libraries での設定エントリも必要です。依存オブジェクトを落とすのが、CASCADE です。再現性のため、本番では拡張のバージョンを必ず固定しましょう。PostgreSQL の重要な強みが、拡張です — ただのデータベースでなく、プラットフォームへ変えます。

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements(クエリのプロファイリング)

最も価値のある PostgreSQL の拡張が、pg_stat_statements です。すべてのクエリの実行統計を記録します。total_exec_time(影響)か mean_exec_time(クエリごとのコスト)により、遅いクエリを見つけましょう。欠けたインデックスを示唆するのが、高い rows/call です。似たクエリが集約されるよう、(リテラルを $1 に置き換え)クエリを正規化するのが、拡張です。新鮮なパフォーマンスを追跡するため、デプロイ後には統計を reset しましょう。最適化のとき最初に手を伸ばす道具が、これです。時間がどこへ行くか正確に示すからです。

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto(暗号化とハッシュ化)

ハッシュ化(digest、HMAC)、UUID 生成(gen_random_uuid)、暗号化(PGP の対称/非対称)を提供するのが、pgcrypto です。UUID v4 を生成する標準的な方法が、gen_random_uuid() です(PG13 以降は組み込みですが、古いバージョンのために pgcrypto が提供します)。パスワードの保存のためには、アプリのコードで bcrypt か argon2 を推奨しましょう — データの整合性のためのものであり、パスワードの保存のためのものではないのが、pgcrypto のハッシュ化です。保存時の列の暗号化に適したのが、PGP の暗号化です。本番では、暗号化の鍵は必ずデータベースの外で管理しましょう。

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID と ID の生成

ランダムな UUID のためには、gen_random_uuid()(PG13+)を使いましょう — 組み込みであり拡張が不要です。必要なら、v1(時間ベース)と v5(名前空間)の UUID を提供するのが、uuid-ossp です。自動増分整数には、serial より、GENERATED ALWAYS AS IDENTITY(PG10+)を推奨しましょう — SQL 標準であり、誤った手動挿入を防ぎ、権限をきれいに扱います。1 回のラウンドトリップで挿入された ID を得るのが、RETURNING id です。分散システムには素晴らしいのが、UUID で、シングルノードのアプリにはより単純なのが、identity 列です。

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

有用な拡張のカタログ

広大なのが、PostgreSQL の拡張のエコシステムです。ファジーなテキストマッチング(% 演算子)と速い LIKE クエリを可能にするのが、pg_trgm です。地理空間の能力を加えるのが、PostGIS です。時系列のためのパーティション作成の自動化をするのが、pg_partman です。それぞれ、時系列と水平スケーリングのためのサードパーティ拡張が、TimescaleDB と Citus です。レガシーなのが、hstore です — 代わりに jsonb を使いましょう。国際化された検索のために、分音記号を剥ぎ取るのが、unaccent です。利用可能な拡張の閲覧には、pg_available_extensions を、コミュニティの拡張には、PGXN(PostgreSQL Extension Network)を確認しましょう。

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

モニタリングとトラブルシューティング

アクティブなクエリ(pg_stat_activity)

PostgreSQL が何をしているかのリアルタイムのビューが、pg_stat_activity です。状態を確認しましょう(active、idle、idle in transaction)。よくある問題が、長時間実行されるクエリと、idle-in-transaction のセッションです。後者が、ロックを保持し vacuum を防ぎます。SIGINT を送るのが(クエリがキャンセルされ、セッションが生き延びます)、pg_cancel_backend で、セッション全体を殺すのが、pg_terminate_backend です。殺す前に必ず調査しましょう。塞がったトランザクションを自動的に殺すのが、idle_in_transaction_session_timeout の設定です。max_connections に当たらないよう、接続数を監視しましょう。

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

ロックとブロッキング

よくある本番の問題が、ロックの競合です。すべてのアクティブなロックを示すのが、pg_locks で、セッションが待っているのが、未付与のロックです。誰が誰をブロックしているか正確に特定するのが、ブロッキングチェーンのクエリです。デッドロックと遅いクエリのデバッグには不可欠です。(ALTER TABLE、DROP、VACUUM FULL からの)AccessExclusive がすべてをブロックするため — メンテナンスの窓で実行しましょう。セッションが永遠に待つのを防ぐのが、lock_timeout の設定です。通常の犯人が、長いトランザクションです — トランザクションは短く保ち、早くコミットしましょう。

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Vacuum と肥大

MVCC を使うのが、PostgreSQL です — UPDATE/DELETE が、VACUUM が取り戻す死んだタプルを作ります。自動的に動くのが autovacuum ですが、大きいか書き込みが重いテーブルのため、調整が必要かもしれません。pending の後始末を示すのが、n_dead_tup で、autovacuum が追いついていないのが、高い dead_pct です。ディスクスペースを取り戻すが AccessExclusive ロックを取るのが、VACUUM FULL です。オンラインの肥大除去のためには、pg_repack を。vacuum をブロックするのが(古い行を見られます)、長期間実行されるトランザクションであり、肥大とトランザクション ID のラップアラウンドのリスクを引き起こします。ラップアラウンドを防ぐため(約 20 億で強制失敗します)、xid_age を監視しましょう。

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

ログの分析

postgresql.conf で設定されるのが、ロギングです。遅いクエリをログするのが、log_min_duration_statement です(すべてには 0、100ms を超えるものには 100)。ロックの競合を捉えるのが、log_lock_waits です。外部テーブルとして(file_fdw で)ログをクエリさせるのが、CSV ロギングです。よくある接頭辞:%t(タイムスタンプ)、%p(PID)、%u(ユーザー)、%d(データベース)です。本番では、ログをパースし可視化する、pgBadger のようなログコレクタを使いましょう。本番ですべてのクエリをログしてはいけません(I/O のオーバーヘッド)— 集約の分析には、代わりに pg_stat_statements を。

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

ヘルスチェックと指標

主要な健康の指標:キャッシュのヒット率(>99% を目指しましょう — 低ければ、shared_buffers を増やしましょう)、インデックスの使用率(低いということは、インデックスの欠落か悪いクエリ)、テーブルのサイズ(肥大を早く捉える)、接続数です。運用データの金鉱であるのが、pg_stat_user_tables と pg_statio_user_tables です。ダッシュボードのためには、Prometheus + postgres_exporter でモニタリングをセットアップしましょう。見るべきもの:成長する死んだタプル、落ちるキャッシュのヒット率、増加する接続、予想より速く成長するテーブルです。深夜の緊急事態を防ぐのが、定期的なヘルスチェックです。

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?