Postgresql Table Design

Use when designing or reviewing PostgreSQL schemas, data types, keys, constraints, indexes, JSONB, partitioning, RLS, or live migrations.

9 stars 3 forksOther

Install

skills CLI (Claude Code, Cursor, Codex and more)
npx skills add sandbaseai/workbuddy-skill --skill postgresql-table-design
Manual install
git clone --depth 1 https://github.com/sandbaseai/workbuddy-skill.git /tmp/workbuddy-skill
mkdir -p ~/.claude/skills
cp -r /tmp/workbuddy-skill/skills/postgresql-table-design ~/.claude/skills/postgresql-table-design

Personal skills live in ~/.claude/skills. Use .claude/skills in a project folder to share a skill with your team.

From the SKILL.md

# PostgreSQL Table Design

先从数据语义、访问路径、写入/更新模式和生命周期设计 PostgreSQL 表,再决定类型、约束、索引和分区。schema 质量不仅是 DDL 能执行,还要保证一致性、查询计划、迁移可回滚、权限和数据删除要求。

## 使用边界

- 开始前确认 PostgreSQL/extension 版本、数据量、读写比例、查询/SLO、复制、分区、tenant/PII、owner、备份和目标环境。
- 默认只读审查 DDL、schema/catalog、查询计划、锁/复制指标和迁移脚本;在隔离 clone 用脱敏或合成数据验证。
- 不擅自执行生产 DDL、索引构建、`VACUUM`/`CLUSTER`、权限/RLS、回填、删除或扩容;这些操作需要授权、备份和回滚方案。
- DDL、路径、样本、错误和统计信息不得泄露凭据、个人数据或完整业务行。

## 类型、键与约束

保持 snake_case,避免依赖带引号的大小写标识符。默认使用 `BIGINT GENERATED ALWAYS AS IDENTITY` 或有明确理由的 UUID;事件时间用 `TIMESTAMPTZ`,金额/精确小数用 `NUMERIC`,字符串用 `TEXT` 并在确有业务限制时加 length check。`BOOLEAN` 通常 `NOT NULL`,枚举只适合小且稳定的集合;会演进的业务值使用受约束 text 或 lookup table。

优先 3NF 消除更新异常,只有在真实 plan/基线证明收益后才反规范化。每个 reference/entity 表定义 PK;FK 明确 `ON DELETE/UPDATE` 语义并手动为引用列建索引。`CHECK` 中的 NULL 会通过三值逻辑,需与 `NOT NULL` 配合;确认 UNIQUE 对 NULL 的语义,PG15+ 可按需要使用 `NULLS NOT DISTINCT`。

```sql
CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'paid', 'canceled')),
  total NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_active_customer_idx ON orders (customer_id)
  WHERE status IN ('pending', 'paid');
```

用 exclusion constraint 防止时间/range 重叠等数据库可表达的不变量;对循环关系或批量导入谨慎使用 `DEFERRABLE`,并验证提交时行为。不要只在应用层实现唯一性、金额范围、状态转移或租户边界。
Read the full skill on GitHub

Details

Skill name
postgresql-table-design
Path
skills/postgresql-table-design/SKILL.md
License
MIT
Repo updated
8 September 2026

FAQ

What does the Postgresql Table Design skill do?

Use when designing or reviewing PostgreSQL schemas, data types, keys, constraints, indexes, JSONB, partitioning, RLS, or live migrations.

How do I install the Postgresql Table Design skill?

skills CLI (Claude Code, Cursor, Codex and more): npx skills add sandbaseai/workbuddy-skill --skill postgresql-table-design. Skills go in ~/.claude/skills (personal) or .claude/skills (project), and Claude loads them automatically when a task matches the description.

Is Postgresql Table Design popular?

It lives in sandbaseai/workbuddy-skill, which has 9 GitHub stars and 3 forks.

Related skills

Browse the most popular Claude skills, all other skills, or write your own with the skills generator.