16.2 자주 쓰는 확장

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION pgcrypto;

-- 비밀번호 해시 (bcrypt-like)
SELECT crypt('mypassword', gen_salt('bf', 12));

-- 검증
SELECT crypt('mypassword', stored_hash) = stored_hash;

-- 대칭 암호화
SELECT pgp_sym_encrypt('secret data', 'encryption_key');
SELECT pgp_sym_decrypt(encrypted_col::bytea, 'encryption_key');

-- HMAC
SELECT hmac('message', 'key', 'sha256');

-- UUID
SELECT gen_random_uuid();

CREATE EXTENSION pg_trgm;

CREATE INDEX ON users USING gin (name gin_trgm_ops);

SELECT * FROM users WHERE name ILIKE '%kim%';
SELECT * FROM users WHERE name % 'gimm' ORDER BY name <-> 'gimm';

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(p_parent_table := 'public.events', p_control := 'created_at', ...);

CREATE EXTENSION pgvector;

CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding vector(1536)   -- OpenAI ada-002 차원
);

-- 유사도 검색 (cosine distance)
SELECT id, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
  FROM items
 ORDER BY embedding <=> '[0.1, 0.2, ...]'
 LIMIT 10;

-- HNSW 인덱스 (PG 16+, pgvector 0.5+)
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- IVFFlat 인덱스 (옛 옵션)
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

CREATE EXTENSION postgis;

CREATE TABLE parcels (
  id bigserial PRIMARY KEY,
  name text,
  geom geometry(Polygon, 4326)
);

CREATE INDEX ON parcels USING gist (geom);

-- 공간 쿼리
SELECT name FROM parcels
 WHERE ST_Intersects(geom, ST_MakeEnvelope(127.0, 37.5, 127.1, 37.6, 4326));

-- 거리
SELECT name, ST_Distance(geom::geography, ST_GeogFromText('POINT(127.0 37.5)')) AS distance_m
  FROM parcels
 ORDER BY distance_m
 LIMIT 10;

CREATE EXTENSION pg_cron;

-- 매시간 vacuum
SELECT cron.schedule('hourly-vacuum', '0 * * * *', $$VACUUM ANALYZE big_table$$);

-- 매일 파티션 maintenance
SELECT cron.schedule('partman', '0 1 * * *', $$SELECT partman.run_maintenance()$$);

-- 목록
SELECT * FROM cron.job;

-- 제거
SELECT cron.unschedule('hourly-vacuum');

sudo dnf install -y pg_repack_17

CREATE EXTENSION pg_repack;

pg_repack -d app_main -t orders

CREATE EXTENSION hypopg;

-- 가상 인덱스
SELECT hypopg_create_index('CREATE INDEX ON orders(user_id, status)');

-- EXPLAIN이 그 인덱스 사용한 plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

-- 정리
SELECT hypopg_reset();

/*+ HashJoin(o u) IndexScan(o orders_user_id_idx) */
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

CREATE EXTENSION citext;

CREATE TABLE users (
  email citext UNIQUE
);

INSERT INTO users(email) VALUES ('A@B.com');
SELECT * FROM users WHERE email = 'a@b.com';   -- 매칭

CREATE EXTENSION btree_gist;

CREATE TABLE meetings (
  room_id int,
  when_range tsrange,
  EXCLUDE USING gist (room_id WITH =, when_range WITH &&)
);