postgresql-best-practices by wimolivier/postgresql-best-practices
npx skills add https://github.com/wimolivier/postgresql-best-practices --skill postgresql-best-practices ┌─── PostgreSQL 数据库 ──────────────────────────────┐
│ │
│ ┌──────────────────┐ ┌───────────────────────┐ │
│ │ api 模式 │ │ private 模式 │ │
┌─────────────┐ │ │──────────────────│ │───────────────────────│ │
│ 应用程序 │─执行─▶│ get_customer() │───▶│ set_updated_at() │ │
└─────────────┘ │ │ insert_order() │ │ hash_password() │ │
│ │ └────────┬─────────┘ └──────────┬────────────┘ │
│ │ │ │ │
│ │ │ SECURITY DEFINER │ 触发器 │
│ │ ▼ ▼ │
│ │ ┌──────────────────────────────────────────────┐ │
│ │ │ data 模式 │ │
被阻止 │ │──────────────────────────────────────────────│ │
│ │ │ customers orders ... │ │
└ ─ ─ ─ ✕ │ └──────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────┘
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
| 文档 | 用途 |
|---|---|
| quick-reference.md | 快速查阅 - 单页速查表 (打印这个!) |
| schema-architecture.md | 从此开始 - 模式分离模式 (data/private/api) |
| coding-standards-trivadis.md | 编码标准和命名约定 (l_, g_, co_) |
| 文档 | 用途 |
|---|---|
| plpgsql-table-api.md | 表 API 函数、过程、触发器 |
| schema-naming.md | 所有对象的命名约定 |
| data-types.md | 数据类型选择 (UUIDv7, text, timestamptz) |
| indexes-constraints.md | 索引类型、策略、约束 |
| migrations.md | 原生迁移系统文档 |
| anti-patterns.md | 需要避免的常见错误 |
| checklists-troubleshooting.md | 项目清单和问题解决方案 |
| 文档 | 用途 |
|---|---|
| testing-patterns.md | pgTAP 单元测试、测试工厂 |
| performance-tuning.md | EXPLAIN ANALYZE、查询优化、JIT |
| row-level-security.md | RLS 模式、多租户隔离 |
| jsonb-patterns.md | JSONB 索引、查询、验证 |
| audit-logging.md | 通用审计触发器、变更追踪 |
| bulk-operations.md | COPY、批量插入、upsert |
| session-management.md | 会话变量、连接池 |
| transaction-patterns.md | 隔离级别、锁、死锁预防 |
| full-text-search.md | tsvector、tsquery、排名、多语言 |
| partitioning.md | 范围、列表、哈希分区策略 |
| window-functions.md | 帧、排名、运行计算 |
| time-series.md | 时间序列数据模式、BRIN 索引 |
| event-sourcing.md | 事件存储、投影、CQRS |
| queue-patterns.md | 作业队列、SKIP LOCKED、LISTEN/NOTIFY |
| encryption.md | pgcrypto、列加密、TLS |
| vector-search.md | pgvector、嵌入、相似性搜索 |
| postgis-patterns.md | 空间数据、地理查询 |
| 文档 | 用途 |
|---|---|
| oracle-migration-guide.md | PL/SQL 到 PL/pgSQL 转换 |
| cicd-integration.md | GitHub Actions、GitLab CI、Docker |
| monitoring-observability.md | pg_stat_statements、指标、告警 |
| backup-recovery.md | pg_dump、pg_basebackup、PITR |
| replication-ha.md | 流式/逻辑复制、故障转移 |
| 文档 | 用途 |
|---|---|
| data-warehousing-medallion.md | 奖牌架构 - 青铜/白银/黄金层、数据血缘、ETL |
| analytical-queries.md | 分析查询模式、OLAP 优化、GROUPING SETS |
| 脚本 | 用途 |
|---|---|
| 001_install_migration_system.sql | 安装迁移系统 (核心函数) |
| 002_migration_runner_helpers.sql | 辅助过程 (run_versioned, run_repeatable) |
| 003_example_migrations.sql | 迁移模式示例 |
| 999_uninstall_migration_system.sql | 清理移除迁移系统 |
应用程序 → api 模式 → data 模式
↓
private 模式 (触发器, 辅助函数)
| 模式 | 包含 | 访问权限 | 用途 |
|---|---|---|---|
data | 表、索引 | 无 | 数据存储 |
private | 触发器、辅助函数 | 无 | 内部逻辑 |
api | 函数、过程 | 应用程序 | 外部接口 |
app_audit | 审计表 | 管理员 | 变更追踪 |
app_migration | 迁移追踪 | 管理员 | 模式版本控制 |
所有 api 函数必须具有:
SECURITY DEFINER
SET search_path = data, private, pg_temp
CREATE TABLE data.{table_name} (
id uuid PRIMARY KEY DEFAULT uuidv7(),
-- 列...
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER {table}_bu_updated_trg
BEFORE UPDATE ON data.{table_name}
FOR EACH ROW EXECUTE FUNCTION private.set_updated_at();
CREATE FUNCTION api.{action}_{entity}(in_param type)
RETURNS TABLE (col1 type, col2 type)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
SELECT col1, col2 FROM data.{table} WHERE ...;
$$;
CREATE PROCEDURE api.{action}_{entity}(
in_param type,
INOUT io_id uuid DEFAULT NULL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
BEGIN
INSERT INTO data.{table} (...) VALUES (...) RETURNING id INTO io_id;
END;
$$;
SELECT app_migration.acquire_lock();
CALL app_migration.run_versioned(
in_version := '001',
in_description := '描述',
in_sql := $mig$ ... $mig$,
in_rollback_sql := '...'
);
SELECT app_migration.release_lock();
| 前缀 | 类型 | 示例 |
|---|---|---|
l_ | 局部变量 | l_customer_count |
g_ | 会话/全局变量 | g_current_user_id |
co_ | 常量 | co_max_retries |
in_ | IN 参数 | in_customer_id |
out_ | OUT 参数 (仅函数) | out_total |
io_ | INOUT 参数 (过程) | io_id |
c_ | 游标 | c_active_orders |
r_ | 记录 | r_customer |
t_ | 数组/表 | t_order_ids |
e_ | 异常 | e_not_found |
注意 : PostgreSQL 过程仅支持 INOUT 参数,不支持 OUT。对所有过程输出参数使用
io_前缀。
| 对象 | 模式 | 示例 |
|---|---|---|
| 表 | snake_case, 复数 | orders, order_items |
| 列 | snake_case | customer_id, created_at |
| 主键 | id | id |
| 外键 | {table_singular}_id | customer_id |
| 索引 | {table}_{cols}_idx | orders_customer_id_idx |
| 唯一约束 | {table}_{cols}_key | users_email_key |
| 函数 | {action}_{entity} | get_customer, select_orders |
| 过程 | {action}_{entity} | insert_order, update_status |
| 触发器 | {table}_{timing}{event}_trg | orders_bu_trg |
| 使用 | 替代 |
|---|---|
text | char(n), varchar(n) |
numeric(p,s) | money, float |
timestamptz | timestamp |
boolean | integer 标志 |
uuidv7() | serial, uuid_generate_v4() |
GENERATED ALWAYS AS IDENTITY | serial, bigserial |
jsonb | json, EAV 模式 |
RETURNS SETOF table (暴露所有列)SECURITY DEFINER 时缺少 SET search_pathtimestampNOT IN (使用 NOT EXISTS)BETWEEN (使用 >= AND <)serial/bigserial (使用 IDENTITY)varchar(n) 任意限制 (使用 text)NOWAIT/SKIP LOCKED 的 SELECT FOR UPDATE| 特性 | 用法 |
|---|---|
uuidv7() | id uuid DEFAULT uuidv7() - 按时间戳排序的 UUID |
| 虚拟生成列 | col type GENERATED ALWAYS AS (expr) - 在查询时计算 |
RETURNING 中的 OLD/NEW | UPDATE ... RETURNING OLD.col, NEW.col |
| 时间约束 | PRIMARY KEY (id) WITHOUT OVERLAPS |
NOT VALID 约束 | 添加约束而无需全表扫描 |
db/
├── migrations/
│ ├── V001__create_schemas.sql
│ ├── V002__create_tables.sql
│ └── repeatable/
│ ├── R__private_triggers.sql
│ └── R__api_functions.sql
├── schemas/
│ ├── data/ # 表定义
│ ├── private/ # 内部函数
│ └── api/ # 外部接口
└── seeds/ # 参考数据
每周安装次数
94
仓库
GitHub 星标
2
首次出现
2026年3月2日
安全审计
已安装于
codex93
gemini-cli92
amp92
cline92
github-copilot92
opencode92
┌─── PostgreSQL Database ──────────────────────────────┐
│ │
│ ┌──────────────────┐ ┌───────────────────────┐ │
│ │ api schema │ │ private schema │ │
┌─────────────┐ │ │──────────────────│ │───────────────────────│ │
│ Application │─EXECUTE─▶│ get_customer() │───▶│ set_updated_at() │ │
└─────────────┘ │ │ insert_order() │ │ hash_password() │ │
│ │ └────────┬─────────┘ └──────────┬────────────┘ │
│ │ │ │ │
│ │ │ SECURITY DEFINER │ triggers │
│ │ ▼ ▼ │
│ │ ┌──────────────────────────────────────────────┐ │
│ │ │ data schema │ │
BLOCKED │ │──────────────────────────────────────────────│ │
│ │ │ customers orders ... │ │
└ ─ ─ ─ ✕ │ └──────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────┘
| Document | Purpose |
|---|---|
| quick-reference.md | QUICK LOOKUP - Single-page cheat sheet (print this!) |
| schema-architecture.md | START HERE - Schema separation pattern (data/private/api) |
| coding-standards-trivadis.md | Coding standards & naming conventions (l_, g_, co_) |
| Document | Purpose |
|---|---|
| plpgsql-table-api.md | Table API functions, procedures, triggers |
| schema-naming.md | Naming conventions for all objects |
| data-types.md | Data type selection (UUIDv7, text, timestamptz) |
| indexes-constraints.md | Index types, strategies, constraints |
| migrations.md | Native migration system documentation |
| anti-patterns.md | Common mistakes to avoid |
| checklists-troubleshooting.md |
| Document | Purpose |
|---|---|
| testing-patterns.md | pgTAP unit testing, test factories |
| performance-tuning.md | EXPLAIN ANALYZE, query optimization, JIT |
| row-level-security.md | RLS patterns, multi-tenant isolation |
| jsonb-patterns.md | JSONB indexing, queries, validation |
| audit-logging.md | Generic audit triggers, change tracking |
| bulk-operations.md | COPY, batch inserts, upserts |
| session-management.md |
| Document | Purpose |
|---|---|
| oracle-migration-guide.md | PL/SQL to PL/pgSQL conversion |
| cicd-integration.md | GitHub Actions, GitLab CI, Docker |
| monitoring-observability.md | pg_stat_statements, metrics, alerting |
| backup-recovery.md | pg_dump, pg_basebackup, PITR |
| replication-ha.md | Streaming/logical replication, failover |
| Document | Purpose |
|---|---|
| data-warehousing-medallion.md | Medallion Architecture - Bronze/Silver/Gold, data lineage, ETL |
| analytical-queries.md | Analytical query patterns, OLAP optimization, GROUPING SETS |
| Script | Purpose |
|---|---|
| 001_install_migration_system.sql | Install migration system (core functions) |
| 002_migration_runner_helpers.sql | Helper procedures (run_versioned, run_repeatable) |
| 003_example_migrations.sql | Example migration patterns |
| 999_uninstall_migration_system.sql | Clean removal of migration system |
Application → api schema → data schema
↓
private schema (triggers, helpers)
| Schema | Contains | Access | Purpose |
|---|---|---|---|
data | Tables, indexes | None | Data storage |
private | Triggers, helpers | None | Internal logic |
api | Functions, procedures | Applications | External interface |
app_audit | Audit tables | Admins | Change tracking |
All api functions MUST have:
SECURITY DEFINER
SET search_path = data, private, pg_temp
CREATE TABLE data.{table_name} (
id uuid PRIMARY KEY DEFAULT uuidv7(),
-- columns...
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER {table}_bu_updated_trg
BEFORE UPDATE ON data.{table_name}
FOR EACH ROW EXECUTE FUNCTION private.set_updated_at();
CREATE FUNCTION api.{action}_{entity}(in_param type)
RETURNS TABLE (col1 type, col2 type)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
SELECT col1, col2 FROM data.{table} WHERE ...;
$$;
CREATE PROCEDURE api.{action}_{entity}(
in_param type,
INOUT io_id uuid DEFAULT NULL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
BEGIN
INSERT INTO data.{table} (...) VALUES (...) RETURNING id INTO io_id;
END;
$$;
SELECT app_migration.acquire_lock();
CALL app_migration.run_versioned(
in_version := '001',
in_description := 'Description',
in_sql := $mig$ ... $mig$,
in_rollback_sql := '...'
);
SELECT app_migration.release_lock();
| Prefix | Type | Example |
|---|---|---|
l_ | Local variable | l_customer_count |
g_ | Session/global variable | g_current_user_id |
co_ | Constant | co_max_retries |
in_ |
Note : PostgreSQL procedures only support INOUT parameters, not OUT. Use
io_prefix for all procedure output parameters.
| Object | Pattern | Example |
|---|---|---|
| Table | snake_case, plural | orders, order_items |
| Column | snake_case | customer_id, created_at |
| Primary Key | id | id |
| Use | Instead Of |
|---|---|
text | char(n), varchar(n) |
numeric(p,s) | money, float |
timestamptz | timestamp |
boolean |
RETURNS SETOF table (exposes all columns)SET search_path with SECURITY DEFINERtimestamp without timezoneNOT IN with subqueries (use NOT EXISTS)BETWEEN with timestamps (use >= AND <)serial/bigserial (use )| Feature | Usage |
|---|---|
uuidv7() | id uuid DEFAULT uuidv7() - timestamp-ordered UUIDs |
| Virtual generated columns | col type GENERATED ALWAYS AS (expr) - computed at query time |
OLD/NEW in RETURNING | UPDATE ... RETURNING OLD.col, NEW.col |
| Temporal constraints | PRIMARY KEY (id) WITHOUT OVERLAPS |
db/
├── migrations/
│ ├── V001__create_schemas.sql
│ ├── V002__create_tables.sql
│ └── repeatable/
│ ├── R__private_triggers.sql
│ └── R__api_functions.sql
├── schemas/
│ ├── data/ # Table definitions
│ ├── private/ # Internal functions
│ └── api/ # External interface
└── seeds/ # Reference data
Weekly Installs
94
Repository
GitHub Stars
2
First Seen
Mar 2, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex93
gemini-cli92
amp92
cline92
github-copilot92
opencode92
GSAP React 动画库使用指南:useGSAP Hook 与最佳实践
3,100 周安装
PostgreSQL 最佳实践指南:数据建模、索引优化与查询性能调优
360 周安装
RAG检索增强生成系统实现指南:构建基于外部知识的AI问答与文档助手
362 周安装
sdd:create-ideas - 上下文工程套件中的创意生成AI工具,提供高概率与多样性回复
369 周安装
VueUse 核心工具库指南:Vue组合式API必备函数集合与版本更新
93 周安装
Tailwind CSS v4 2025 现代模式指南:CSS 原生配置、容器查询与响应式设计
363 周安装
UI 样式技能:使用 shadcn/ui 和 Tailwind CSS 构建美观、可访问的 React 用户界面
361 周安装
| Project checklists & problem solutions |
| Session variables, connection pooling |
| transaction-patterns.md | Isolation levels, locking, deadlock prevention |
| full-text-search.md | tsvector, tsquery, ranking, multi-language |
| partitioning.md | Range, list, hash partitioning strategies |
| window-functions.md | Frames, ranking, running calculations |
| time-series.md | Time-series data patterns, BRIN indexes |
| event-sourcing.md | Event store, projections, CQRS |
| queue-patterns.md | Job queues, SKIP LOCKED, LISTEN/NOTIFY |
| encryption.md | pgcrypto, column encryption, TLS |
| vector-search.md | pgvector, embeddings, similarity search |
| postgis-patterns.md | Spatial data, geographic queries |
app_migration| Migration tracking |
| Admins |
| Schema versioning |
| IN parameter |
in_customer_id |
out_ | OUT parameter (functions only) | out_total |
io_ | INOUT parameter (procedures) | io_id |
c_ | Cursor | c_active_orders |
r_ | Record | r_customer |
t_ | Array/table | t_order_ids |
e_ | Exception | e_not_found |
| Foreign Key | {table_singular}_id | customer_id |
| Index | {table}_{cols}_idx | orders_customer_id_idx |
| Unique | {table}_{cols}_key | users_email_key |
| Function | {action}_{entity} | get_customer, select_orders |
| Procedure | {action}_{entity} | insert_order, update_status |
| Trigger | {table}_{timing}{event}_trg | orders_bu_trg |
integer flags |
uuidv7() | serial, uuid_generate_v4() |
GENERATED ALWAYS AS IDENTITY | serial, bigserial |
jsonb | json, EAV pattern |
IDENTITYvarchar(n) arbitrary limits (use text)SELECT FOR UPDATE without NOWAIT/SKIP LOCKEDNOT VALID constraints | Add constraints without full table scan |