PostgreSQLチートシート - psql・CRUD・運用

最頻出10項目

DROP、TRUNCATE、条件なしの UPDATE・DELETE、CASCADE は広範囲のデータを変更します。接続先、対象件数、バックアップ、復元手順を確認してから実行します。

psqlへ接続

psql -h localhost -p 5432 -U app_user -d app_db

パスワードを接続URLやコマンドへ直接書くと、シェル履歴やプロセス一覧に残る可能性があります。対話入力、適切な権限のパスワードファイル、利用中の秘密情報管理手段を使います。

自動処理では、途中のSQLエラーを見落とさないよう ON_ERROR_STOP を指定します。

psql -X -v ON_ERROR_STOP=1 -d app_db -f migration.sql

psqlメタコマンド

テーブル作成

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

よく使う型は integer・bigint、numeric、text、boolean、date、timestamptz、uuid、jsonb です。時点を表す値では、タイムゾーンの扱いを明確にし、一般に timestamptz を検討します。

NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY は、DB側で不正な状態を防ぐ制約です。

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
  status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now()
);

SELECT

SELECT id, email, name
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 20;

SELECT status, count(*) AS order_count, sum(amount) AS total
FROM orders
WHERE created_at >= current_date - interval '30 days'
GROUP BY status
HAVING count(*) >= 10
ORDER BY total DESC;

アプリケーションでは、値を文字列連結してSQLへ埋め込まず、プレースホルダー付きのパラメータ化クエリを使います。

JOIN

SELECT
  orders.id,
  users.name,
  orders.amount
FROM orders
JOIN users ON users.id = orders.user_id
WHERE orders.status = 'paid';

JOIN（INNER JOIN）は両方に一致する行、LEFT JOIN は左側の行をすべて残します。結合キーの重複によって結果行が増えることがあるため、件数も確認します。

INSERT

INSERT INTO users (email, name)
VALUES ('student@example.com', 'Ada')
RETURNING id, email, name;

複数行も1文で追加できます。

INSERT INTO users (email, name)
VALUES
  ('aki@example.com', 'Aki'),
  ('sora@example.com', 'Sora')
RETURNING id;

重複時の動作を明示する場合は ON CONFLICT を使います。

INSERT INTO users (email, name)
VALUES ('student@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id;

意図しない上書きを避けるため、競合対象となる一意制約と更新列を確認します。

UPDATE

最初に同じ WHERE で対象を確認します。

SELECT id, email, active
FROM users
WHERE id = 10;

UPDATE users
SET active = false
WHERE id = 10
RETURNING id, email, active;

条件なしの UPDATE は全行を更新します。複数行更新でも、事前に SELECT count(*) で件数を確認し、可能ならトランザクション内で結果を確認します。

DELETE

SELECT id, email
FROM users
WHERE id = 10;

DELETE FROM users
WHERE id = 10
RETURNING id, email;

条件なしの DELETE は全行を削除します。TRUNCATE はテーブル全体を高速に空にし、DROP TABLE はテーブル定義ごと削除します。外部キーを伴う CASCADE は関連オブジェクトへ影響が広がるため、依存関係を確認せず使わないでください。

トランザクション

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

問題があれば確定前に戻します。

ROLLBACK;

一部まで戻せるように保存点を使えます。

BEGIN;
SAVEPOINT before_update;
UPDATE users SET active = false WHERE id = 10;
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

長時間のトランザクションはロック、VACUUM、接続資源へ影響します。必要な処理だけを短くまとめます。DDLや外部処理を含める場合は、失敗時の境界を事前に設計します。

インデックス

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

CREATE UNIQUE INDEX idx_users_email_lower
ON users (lower(email));

インデックスは検索と並べ替えを速くできる一方、書き込み時間と保存容量を増やします。クエリの WHERE、JOIN、ORDER BY と実データに合わせて設計します。

一覧と定義はpsqlで確認できます。

\di
\d users

本番で通常の CREATE INDEX を実行すると、処理中の書き込みへ影響する場合があります。必要に応じて CREATE INDEX CONCURRENTLY を検討しますが、トランザクションブロック内で実行できないなどの制約があります。

EXPLAIN

実行せずに計画だけ確認します。

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10;

実際に実行して計測します。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE はクエリを実際に実行します。UPDATE、DELETE、INSERT に付けるとデータも変更されるため、読み取り専用だと思って本番で実行しないでください。必要ならトランザクション内で実施して ROLLBACK します。

主に推定行数と実行行数、Seq Scan / Index Scan、実行時間、読み取ったバッファを比較します。遅いという理由だけでインデックスを増やさず、統計情報や返却件数も確認します。

バックアップ

カスタム形式で取得します。

pg_dump -Fc -h localhost -U app_user -d app_db -f app_db.dump

SQL形式で取得する場合です。

pg_dump -h localhost -U app_user -d app_db -f app_db.sql

クラスタ全体のロールやテーブルスペースなどは、DB単位の pg_dump だけでは完全に含まれません。目的に応じて pg_dumpall --globals-only なども検討します。

バックアップファイルの存在だけでは復元可能とは限りません。定期的に別環境へ復元し、アプリケーションから読めることまで確認します。ファイルはDBと別の障害領域へ保管し、暗号化とアクセス権を設定します。

復元

カスタム形式は pg_restore を使います。

createdb -h localhost -U app_user app_db_restore_test
pg_restore \
  -h localhost \
  -U app_user \
  -d app_db_restore_test \
  --exit-on-error \
  app_db.dump

SQL形式は psql で読み込みます。

createdb -h localhost -U app_user app_db_restore_test
psql \
  -X \
  -v ON_ERROR_STOP=1 \
  -h localhost \
  -U app_user \
  -d app_db_restore_test \
  -f app_db.sql

まず新しい空DBへ復元し、件数・制約・アプリ動作を検証します。既存DBへの --clean、--create、--if-exists の利用はオブジェクト削除や接続先変更を伴うため、意味を確認せず追加しないでください。復元対象のPostgreSQLバージョンと拡張機能も確認します。

権限の基本

CREATE ROLE app_user LOGIN;
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

アプリ用ロールにスーパーユーザー権限を与えず、必要なDB・スキーマ・テーブル・シーケンスだけを許可します。パスワードや接続文字列をSQLファイルへ残さないでください。

参考リソース

• PostgreSQL Documentation
• psql
• EXPLAIN
• pg_dump
• pg_restore

×

PR

×

PR