PostgreSQL: PostgreSQL拡張エコシステム、外部データラッパー、ベクトル検索

-- 利用可能な拡張を一覧表示
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UUID v4（ランダム）を生成
SELECT uuid_generate_v4();

-- UUID v1（時刻ベース）を生成
SELECT uuid_generate_v1();

-- 列のデフォルト値として使用
CREATE TABLE api_keys (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    key_name    TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ソルト付きでパスワードをハッシュ化
SELECT crypt('MySecret123', gen_salt('bf'));

-- パスワードを検証
SELECT crypt('MySecret123', stored_hash) = stored_hash AS is_match;

-- AES暗号化
SELECT encode(encrypt('credit card data'::bytea,
    'secret_key_16bytes'::bytea, 'aes'), 'hex');

-- ランダムトークンを生成
SELECT encode(gen_random_bytes(32), 'hex');

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 類似度スコアを表示
SELECT similarity('PostgreSQL', 'Postgres');

-- 高速あいまい検索用のGINトライグラムインデックス
CREATE INDEX idx_products_name_trgm ON products
    USING GIN (name gin_trgm_ops);

-- しきい値付きあいまい検索
SELECT name, similarity(name, 'iphon') AS score
FROM products
WHERE name % 'iphon'
ORDER BY score DESC;

     name      | score
---------------+-------
 iPhone 15 Pro |  0.42
 iPhone 14     |  0.38
(2 rows)

-- 最初にshared_preload_librariesに含める必要あり
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 合計実行時間でトップ10クエリ
SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 統計をリセット
SELECT pg_stat_statements_reset();

  result  
----------
   42.50
(1 row)

CREATE EXTENSION IF NOT EXISTS postgis;

-- ポイントジオメトリを保存
CREATE TABLE stores (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    location GEOMETRY(POINT, 4326)
);

-- 座標を挿入（経度、緯度）
INSERT INTO stores (name, location)
VALUES ('Dubai Mall', ST_SetSRID(ST_MakePoint(55.2796, 25.1972), 4326));

-- 5km以内の店舗を検索
SELECT name,
    ST_Distance(location::geography,
        ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography
    ) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography, 5000);

INSERT 0 1

-- ステップ1: 拡張をインストール
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- ステップ2: サーバー接続を作成
CREATE SERVER legacy_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.100', port '5432', dbname 'legacy');

-- ステップ3: ローカルユーザーをリモート認証情報にマッピング
CREATE USER MAPPING FOR current_user
    SERVER legacy_db
    OPTIONS (user 'admin', password 'secret123');

-- ステップ4: 外部スキーマをインポート
IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, products, orders)
    FROM SERVER legacy_db
    INTO legacy_schema;

-- これでリモートテーブルをローカルのようにクエリ可能
SELECT u.name, COUNT(o.id) AS order_count
FROM legacy_schema.users u
JOIN legacy_schema.orders o ON u.id = o.user_id
GROUP BY u.name;

 count 
-------
     5
(1 row)

-- ローカル：新しいPG商品カタログ
-- リモート：postgres_fdw経由のレガシーMySQL注文データ

SELECT p.name,
       SUM(loi.quantity) AS total_sold,
       SUM(loi.quantity * loi.unit_price) AS revenue
FROM products p
JOIN legacy_schema.order_items loi ON p.id = loi.product_id
WHERE p.category = 'Electronics'
GROUP BY p.name
ORDER BY revenue DESC;

  result  
----------
   42.50
(1 row)

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_server
    FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE access_logs_csv (
    ip_address    TEXT,
    request_time  TIMESTAMP,
    method        TEXT,
    path          TEXT,
    status_code   INT,
    response_time NUMERIC
) SERVER csv_server
OPTIONS (filename '/var/log/nginx/access.csv', format 'csv', header 'true');

-- nginxログをSQLで分析
SELECT path,
       COUNT(*) AS hits,
       AVG(response_time) AS avg_ms
FROM access_logs_csv
WHERE status_code = 200
GROUP BY path
ORDER BY hits DESC
LIMIT 20;

 count 
-------
     5
(1 row)

-- レガシーからローカルにユーザーを移行
INSERT INTO users (name, email, created_at)
SELECT name, email, created_at
FROM legacy_schema.users
WHERE id NOT IN (SELECT legacy_id FROM users);

-- 増分同期にdblinkを使用
CREATE EXTENSION IF NOT EXISTS dblink;

SELECT dblink_connect('legacy', 'host=192.168.1.100 dbname=legacy user=admin password=secret123');

SELECT * FROM dblink('legacy',
    'SELECT id, name, email FROM users WHERE created_at > now() - interval ''1 day'''
) AS t(id BIGINT, name TEXT, email TEXT);

INSERT 0 1

-- pgvectorをインストール
CREATE EXTENSION IF NOT EXISTS vector;

-- エンベディングベクトル付き商品テーブル（OpenAIの1536次元）
CREATE TABLE products_vec (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    embedding   vector(1536)
);

CREATE TABLE

-- エンベディング付き商品を挿入
INSERT INTO products_vec (name, category, price, embedding)
VALUES ('ワイヤレスヘッドホン', 'Electronics', 79.99,
    '[0.012, -0.034, 0.056, ...]'::vector);

-- コサイン距離で類似商品トップ5を検索
SELECT p.id, p.name, p.category, p.price,
       p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec p
ORDER BY p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

INSERT 0 1

-- 高速近似検索用HNSWインデックス
CREATE INDEX idx_products_vec_hnsw ON products_vec
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- 検索精度と速度を調整
SET hnsw.ef_search = 100;

-- インデックス付きクエリ（大規模データセットではるかに高速）
SELECT name,
       embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 10;

CREATE TABLE

-- IVFFlatインデックス（最適なセントロイドのためにデータロード後に構築）
CREATE INDEX idx_products_vec_ivf ON products_vec
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- 再現率向上のためにprobesを増加
SET ivfflat.probes = 10;

SELECT name, embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS dist
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

CREATE TABLE

-- AIモデルから商品エンベディングを保存
INSERT INTO products_vec (name, category, price, embedding)
VALUES
    ('ランニングシューズ',     'Sports',   129.99, array_to_vec(ARRAY[0.1,0.2,0.3])::vector),
    ('ヨガマット',          'Sports',    39.99, array_to_vec(ARRAY[0.11,0.19,0.31])::vector),
    ('Bluetoothスピーカー', 'Electronics', 49.99, array_to_vec(ARRAY[0.5,0.1,0.2])::vector);

-- ユーザーが「ランニングシューズ」を閲覧、類似商品をレコメンド
WITH target AS (
    SELECT embedding FROM products_vec WHERE name = 'ランニングシューズ'
)
SELECT p.name, p.category, p.price,
       p.embedding <=> (SELECT embedding FROM target) AS similarity
FROM products_vec p
WHERE p.name != 'ランニングシューズ'
ORDER BY similarity
LIMIT 3;

INSERT 0 1

-- 現在の拡張バージョンを確認
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- 拡張をアップグレード
ALTER EXTENSION pgvector UPDATE TO '0.7.0';

-- 全拡張をアップグレード
SELECT extname,
       installed_version,
       default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

-- フルセットアップ：EコマースAI向け拡張 + FDW + pgvector

-- ステップ1: コア拡張をインストール
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgvector;

-- ステップ2: ベクトル検索付き商品カタログ
CREATE TABLE products_ai (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    attrs       JSONB DEFAULT '{}',
    embedding   vector(1536)
);

-- ステップ3: あいまい名前検索用GINインデックス
CREATE INDEX idx_products_name_trgm ON products_ai
    USING GIN (name gin_trgm_ops);

-- ステップ4: ベクトル類似度用HNSWインデックス
CREATE INDEX idx_products_embedding ON products_ai
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ステップ5: FDW経由でレガシーデータベースに接続
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.50', port '5432', dbname 'legacy_shop');
CREATE USER MAPPING FOR current_user SERVER legacy_db
    OPTIONS (user 'migrate_user', password 'secure_pass');
IMPORT FOREIGN SCHEMA public LIMIT TO (old_products, old_users)
    FROM SERVER legacy_db INTO legacy;

-- ステップ6: データを移行・変換
INSERT INTO products_ai (name, category, price, attrs)
SELECT name, category, price,
       jsonb_build_object('weight_kg', weight, 'color', color)
FROM legacy.old_products
WHERE active = true;

-- ステップ7: あいまい検索＋ベクトル検索の組み合わせ
SELECT p.name, p.price,
       similarity(p.name, 'wireless earbuds') AS text_score,
       p.embedding <=> '[0.015,-0.030,0.050]'::vector AS vec_dist
FROM products_ai p
WHERE p.name % 'wireless earbuds'
ORDER BY vec_dist ASC
LIMIT 5;