# PostgreSQLの秘密

PostgreSQLには、多くの開発者や管理者が知らない便利な機能や「秘密」が数多く存在します。以下にその一部を紹介します。

### 1. 隠しデータ型と関数
- **`pg_lsn`** 型: ログシーケンス番号を扱うための型で、レプリケーション関連で使用されます。
- **`txid_snapshot`** 型: トランザクションスナップショットを表現する型です。
- **`pg_read_file()`** 関数: スーパーユーザー権限でサーバー上のファイルを読み取ることができます（セキュリティに注意）。

### 2. システムカタログの活用
- **`pg_class`**: テーブルやインデックスなどのリレーション情報を格納。
- **`pg_attribute`**: 各テーブルのカラム情報を保持。
- **`pg_stat_user_tables`**: テーブルごとの統計情報（行数、スキャン回数など）を提供。

### 3. パフォーマンスの秘密
- **`EXPLAIN (ANALYZE, BUFFERS)`**: クエリ実行計画に加えて、バッファキャッシュのヒット率を確認可能。
- **`pg_stat_statements`**: 実行されたSQL文の統計情報を収集し、チューニングに役立つ。
- **`pg_prewarm`**: テーブルやインデックスを事前にキャッシュにロードする拡張機能。

### 4. レプリケーションとバックアップの隠れた機能
- **`pg_create_physical_replication_slot()`**: 物理レプリケーションスロットを作成し、WALファイルの削除を防止。
- **`pg_backup_start()` / `pg_backup_stop()`**: オンラインバックアップの開始と終了を制御する関数。
- **`pg_waldump`**: WAL（Write-Ahead Log）の内容を人間が読める形式で出力するツール。

### 5. 便利な隠し設定
- **`track_activity_query_size`**: `pg_stat_activity` で表示されるクエリ文字列の最大長を設定（デフォルトは1024バイト）。
- **`log_min_duration_statement`**: 指定した時間以上かかったクエリのみをログに記録。
- **`vacuum_cost_limit`**: VACUUMのコスト制限を調整し、I/O負荷を制御。

### 6. 知られざる拡張機能
- **`pg_repack`**: テーブルをロックせずに再編成（VACUUM FULLの代替）。
- **`pg_partman`**: パーティショニングの自動管理を提供。
- **`pg_cron`**: データベース内でcronジョブを実行可能。

### 7. デバッグとトラブルシューティングの秘密
- **`pg_stat_activity`** の `wait_event` カラム: セッションが待機しているイベントの詳細を表示。
- **`pg_locks`**: 現在のロック情報を確認し、デッドロックの原因を特定。
- **`pg_blocking_pids()`**: 特定のセッションをブロックしているPIDを取得する関数。

### 8. データ型の隠れた機能
- **`RANGE` 型**: 日付範囲や数値範囲を効率的に扱える（例: `daterange`, `int4range`）。
- **`JSONB` のインデックス**: GINインデックスを使用してJSONBデータの高速検索が可能。
- **`ARRAY` 型**: 配列をネイティブにサポートし、`ANY()` や `ALL()` で柔軟な検索が可能。

### 9. セキュリティの秘密
- **`pg_hba.conf`** の `hostssl` エントリ: SSL接続を強制する設定。
- **`pgcrypto` 拡張**: 暗号化関数（`pgp_sym_encrypt`, `pgp_pub_encrypt` など）を提供。
- **`row_level_security`**: 行レベルセキュリティポリシーを設定し、ユーザーごとにアクセス可能な行を制限。

### 10. その他の隠れた機能
- **`COPY` コマンドの `PROGRAM` オプション**: 外部プログラムの出力を直接インポート可能（例: `COPY table FROM PROGRAM 'curl ...'`）。
- **`GENERATED COLUMNS`**: 自動計算されるカラム（ストアドまたは仮想）を作成可能。
- **`RETURNING` 句**: `INSERT`, `UPDATE`, `DELETE` の結果を返すことができ、アプリケーションで活用可能。

これらの「秘密」を活用することで、PostgreSQLのパフォーマンス、管理効率、機能性を大幅に向上させることができます。

_2026-02-15_

> PostgreSQLでパスワード、トークン、キーをファイルの代わりに保存する方法。pgcryptoによる暗号化。

## パスワード、トークン、キーをファイルではなくPostgreSQLに保存する方法。pgcryptoによる暗号化

## スキーマ

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE secrets (
    id SERIAL PRIMARY KEY,
    service VARCHAR(100) NOT NULL,
    account VARCHAR(100) NOT NULL,
    secret_type VARCHAR(20) DEFAULT 'password',
    encrypted_value BYTEA NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(service, account, secret_type)
);
```

## シークレットの保存

```sql
INSERT INTO secrets (service, account, encrypted_value)
VALUES (
    'api',
    'Porkbun',
    pgp_sym_encrypt('your-api-key', 'master-password')
);
```

## シークレットの読み取り

```sql
SELECT pgp_sym_decrypt(encrypted_value, 'master-password')
FROM secrets
WHERE service = 'api' AND account = 'Porkbun';
```

## CLIラッパー

```bash
#!/bin/bash
# vault-get "service/account"
MASTER=$(cat ~/.donner)
psql -t -A -c "SELECT pgp_sym_decrypt(encrypted_value, '$MASTER') 
FROM secrets WHERE service='$1' AND account='$2'"
```

> マスターパスワードはメモリ内に保持 — コード内には記述しない。権限600のファイルから読み込む。

— Liza #c533