PostgreSQL 详解：开源关系型数据库的核心概念与架构

-- B-tree（默认）
CREATE INDEX idx_email ON users(email);

-- GIN 索引（JSONB 查询加速）
CREATE INDEX idx_data ON orders USING GIN(data jsonb_path_ops);

-- BRIN（时序大数据）
CREATE INDEX idx_time ON logs USING BRIN(created_at) WITH (pages_per_range = 128);

-- 创建含 JSONB 列的表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL(10,2),
  attributes JSONB
);

-- 插入数据（数组、嵌套对象）
INSERT INTO products (name, price, attributes) VALUES
  ('MacBook Pro', 1999.00,
   '{"color": "silver", "weight": 1.4, "tags": ["laptop", "apple"], "spec": {"ram": "16GB", "cpu": "M3"}}'),
  ('iPad Air', 599.00,
   '{"color": "blue", "tags": ["tablet", "apple"], "spec": {"ram": "8GB", "gpu": true}}');

-- JSONB 包含查询（需 GIN 索引加速）
SELECT * FROM products WHERE attributes @> '{"color": "silver"}';

-- JSONB 路径取值（->> 返回文本，-> 返回 JSONB）
SELECT name, attributes->>'color' as color,
       attributes->'spec'->>'ram' as ram
FROM products;

-- JSONB 数组包含（包含 "laptop" 标签）
SELECT * FROM products WHERE attributes->'tags' @> '["laptop"]';

-- JSONB 创建 GIN 索引后的高效查询
CREATE INDEX idx_products_attrs ON products USING GIN(attributes jsonb_path_ops);

-- 为文章表设置全文搜索
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED
);

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 搜索标题权重最高
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgre') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 中文全文本搜索需要额外安装 zhparser
-- 或使用 pg_search（较新的扩展）

-- RANGE 分区示例（按日期按月分区）
CREATE TABLE logs (
  id SERIAL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  level TEXT,
  message TEXT
) PARTITION BY RANGE (created_at);

-- 创建具体分区
CREATE TABLE logs_2025_01 PARTITION OF logs
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE logs_2025_02 PARTITION OF logs
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

-- 插入时自动路由到对应分区
INSERT INTO logs(level, message) VALUES ('INFO', 'Application started');

-- 查询单个分区（秒级 → 毫秒级）
SET constraint_exclusion = on; -- 自动生效
SELECT * FROM logs WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

-- 显式事务
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 如果失败可以 ROLLBACK TO SAVEPOINT sp1
COMMIT;

-- 隐式事务（单语句自动包裹）
UPDATE orders SET status = 'shipped' WHERE id = 123;

# macOS（推荐）
brew install postgresql@17
brew services start postgresql@17

# Ubuntu/Debian
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
sudo apt update
sudo apt install postgresql-17 postgresql-contrib
sudo systemctl enable --now postgresql

# Docker（开发推荐）
docker run -d --name postgres \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=admin123 \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -v pg_data:/var/lib/postgresql/data \
  postgres:17-alpine

\l                            -- 列出所有数据库
\c database_name              -- 切换数据库
\dt                           -- 列出所有表
\d table_name                -- 查看表结构（含索引、外键）
\du                           -- 列出所有角色
\dn                           -- 列出所有 Schema
\df                           -- 列出所有函数
\i /path/to/file.sql         -- 执行 SQL 文件
\timing on                   -- 显示每条 SQL 执行时间
\x                            -- 以扩展模式显示结果
\q                            -- 退出

-- 创建带密码的用户
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword' LOGIN;
DELETE FROM pg_authid WHERE rolname = 'myuser'; -- 危险操作

-- 创建角色（禁止登录，用于分组权限）
CREATE ROLE app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
GRANT app_read TO myuser;

-- 数据库级权限
CREATE DATABASE myapp OWNER myuser;
REVOKE ALL ON DATABASE myapp FROM PUBLIC; -- 安全基线

-- 给用户开启创建 Schema 的权限
ALTER USER myuser CREATEDB;

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = remote_apply  # 主库等待备库应用
hot_standby = on                   # 允许备库只读

; pgbouncer.ini
databases:
  myapp = host=localhost port=5432 dbname=myapp

pgbouncer:
  listen_port = 6432
  listen_addr = 0.0.0.0
  auth_type = md5
  pool_mode = transaction
  max_client_conn = 10000
  default_pool_size = 20
  min_pool_size = 10

-- 查看膨胀率最高的表
SELECT schemaname, tablename, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- 手动 VACUUM（不锁表）
VACUUM (VERBOSE, ANALYZE) logs;

-- 查看表物理大小与膨胀率
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total,
       round(100 * (pg_relation_size(relid) - pg_table_size(relid)) / pg_relation_size(relid), 2) AS bloat_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;