数据分析SKILL.md

PostgreSQL 表结构设计

v1

选择字段类型、约束与索引,梳理 PostgreSQL 数据模型的取舍。

Claude Code # 数据处理# Agent Skills# SQL

技能概述

这项技能做什么

选择字段类型、约束与索引,梳理 PostgreSQL 数据模型的取舍。

对应原始技能标识:postgresql-table-design。本站整理中文说明,技能原文由 wshobson/agents 维护。

适用场景与输入

分类:数据分析。建议准备:数据字段与样本、业务口径、待回答的问题、数据规模和运行环境。

获取与使用

  1. 打开固定版本的完整技能目录,按仓库说明获取技能。
  2. 原目录包含 references 等配套资源。请获取完整技能目录,单独下载本站的 SKILL.md 不包含这些文件。
  3. 在 Claude Code 或具备相应工具能力的兼容 Agent 中加载;兼容 SKILL.md 格式不代表各平台工具完全一致。
  4. 参照页面的使用示例提供材料,按照原文步骤执行,并核对实际输出。

输出与验证

说明数据假设和处理步骤,给出查询或分析代码,并列出结果核验方法。本站未执行来源仓库中的脚本,也不把示例当作已经完成的运行结果。

来源与许可

MIT License

Copyright (c) 2024 Seth Hobson

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

如何使用这个 Skill

下载 SKILL.md,查看文件中的说明与依赖,再按你的 Agent 支持的方式导入。

SKILL.mdMarkdown · 1 个文件

SKILL.md

---
name: postgresql-table-design
description: Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
---

# PostgreSQL Table Design

## When to Use

- Designing a new PostgreSQL schema, or reviewing one before it ships.
- Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.
- Deciding whether and how to partition a large table, or how to store semi-structured data.
- Planning a schema change on a live database without downtime.

The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload
patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,
and worked DDL examples are in `references/details.md`; open it when a section below points there.

## Core Rules

- Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.
- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic.
- Add **NOT NULL** everywhere it is semantically required; use **DEFAULT**s for common values.
- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.
- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integers; **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).

## PostgreSQL Gotchas

- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names; use `snake_case`.
- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE NULLS NOT DISTINCT (...)` (PG15+) to restrict to one NULL.
- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.
- **No silent coercions**: length/precision overflows error out (no truncation). Inserting 999 into `NUMERIC(2,0)` fails, unlike databases that silently truncate or round.
- **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...).
- **Heap storage**: no clustered PK by default; `CLUSTER` is a one-off reorganization, not maintained on later inserts.
- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.

## Data Types

- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY`; `UUID` for distributed or opaque IDs, generated with `uuidv7()` (PG18+) or `gen_random_uuid()`.
- **Numbers**: `BIGINT` unless storage is critical; `DOUBLE PRECISION` over `REAL`; `NUMERIC(p,s)` for money and exact decimals.
- **Strings**: `TEXT`, with `CHECK (LENGTH(col) <= n)` when a limit is needed; `BYTEA` for binary. Case-insensitive lookups: expression index on `LOWER(col)`, or `CITEXT` when a constraint must be case-insensitive.
- **Time**: `TIMESTAMPTZ`, `DATE`, `INTERVAL`. `now()` is transaction start; `clock_timestamp()` is wall clock.
- **Booleans**: `BOOLEAN NOT NULL` unless tri-state is required.
- **Enums**: `CREATE TYPE ... AS ENUM` only for small, stable sets; evolving business values get `TEXT` + `CHECK` or a lookup table.
- **JSONB** over JSON, indexed with GIN, for optional/semi-structured attributes only.
- Arrays, ranges, network, geometric, full-text, domain, composite, and vector types, plus TOAST storage and collation control: see `references/details.md`.

### Types to avoid

| Avoid | Use instead |
|---|---|
| `timestamp` (without time zone) | `timestamptz` |
| `char(n)`, `varchar(n)` | `text` (+ `CHECK` on length if needed) |
| `money` | `numeric` |
| `timetz` | `timestamptz` |
| `timestamptz(0)` or any precision | `timestamptz` |
| `serial` | `generated always as identity` |

## Constraints

- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.
- **FK**: specify `ON DELETE/UPDATE` (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Index the referencing column. Use `DEFERRABLE INITIALLY DEFERRED` for circular dependencies checked at commit.
- **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Prefer `NULLS NOT DISTINCT` unless duplicate NULLs are wanted.
- **CHECK**: row-local; NULL passes (three-valued logic). Combine with `NOT NULL`: `price NUMERIC NOT NULL CHECK (price > 0)`.
- **EXCLUDE**: prevents overlaps with operators, e.g. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` stops double-booking. Needs a GiST-capable type.

## Indexing

- **B-tree**: default for equality/range (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`).
- **Composite**: leftmost-prefix rule (`WHERE a = ? AND b > ?` uses `(a,b)`; `WHERE b = ?` does not). Most selective columns first.
- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` for index-only scans.
- **Partial**: hot subsets, `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`.
- **Expression**: `CREATE INDEX ON tbl (LOWER(email))`; the query must use the same expression.
- **GIN**: JSONB containment/existence, arrays, full-text search. **GiST**: ranges, geometry, exclusion constraints.
- **BRIN**: large, naturally ordered data (time-series) at minimal storage cost; effective when disk order correlates with the indexed column.

## Partitioning

- Use for large tables (>100M rows) whose queries consistently filter on the partition key, or where maintenance (pruning, bulk replacement) follows a key.
- **RANGE** for time-series (`PARTITION BY RANGE (created_at)`; **TimescaleDB** automates it with retention and compression), **LIST** for discrete values, **HASH** for even distribution without a natural key.
- **Constraint exclusion**: the planner prunes partitions through their `CHECK` constraints; declarative partitioning (PG10+) creates them for you.
- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
- **Limitations**: no global UNIQUE constraints—include the partition key in PK/UNIQUE. FKs from partitioned tables need PG11+, FKs referencing a partitioned table need PG12+; on older versions, use triggers.

## Examples

```sql
CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
```

```sql
CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
```

```sql
-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
```

## Going deeper

`references/details.md` holds the material this file only names:

- The full data-type catalog: TOAST storage, collations, arrays, ranges, network, geometric, text search, domains, composites, vectors.
- Table types (`TEMPORARY`, `UNLOGGED`) and row-level security.
- Constraint and index notes, and partitioning DDL for RANGE, LIST, and HASH.
- Workload patterns: update-heavy, insert-heavy, upsert design, safe schema evolution.
- Generated columns and extensions (`pg_trgm`, `citext`, `timescaledb`, `postgis`, `pgvector`, and more).
- JSONB indexing strategies, including `jsonb_path_ops` and extracted B-tree columns.

---

## Source and license

Original skill: https://github.com/wshobson/agents/blob/9c61ab243def98f72da12abd41cfcf5bcd91af9d/plugins/database-design/skills/postgresql-table-design/SKILL.md

Complete source directory: https://github.com/wshobson/agents/tree/9c61ab243def98f72da12abd41cfcf5bcd91af9d/plugins/database-design/skills/postgresql-table-design

This catalog copy preserves the original instructions. Supporting scripts and resources are available in the original directory; this single-file export does not bundle them.

MIT License

Copyright (c) 2024 Seth Hobson

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

使用示例

请使用「PostgreSQL 表结构设计」技能处理下面的任务。

目标:选择字段类型、约束与索引,梳理 PostgreSQL 数据模型的取舍。
输入材料:数据字段与样本、业务口径、待回答的问题、数据规模和运行环境。
输出要求:说明数据假设和处理步骤,给出查询或分析代码,并列出结果核验方法。

请先核对所需资料和工具;缺少关键信息时提出具体问题。涉及对外发布、删除或生产环境变更时,先提供可审查的计划,获得授权后再执行。

关注官方公众号

抢先获取热门 Skill 实操干货、产品更新与官方活动资讯。

官方交流群二维码

加入官方交流群

对接创作者与同好,交流技能开发,共建 AI 技能生态。