Skill · em Construir backend e dados

postgres-schema-design

Comprehensive PostgreSQL-specific table design reference covering data types, indexing, constraints, performance patterns, and advanced features

Procedência

Antes de instalar

2 arquivos · 26,8 KB · só texto, nenhum script

Instalar na sua CLI

O comando baixa a versão fixada (commit 57f899e) direto da origem, para a pasta que a CLI lê. Precisa de curl (macOS e Linux); no Windows não há comando, porque o Rook Labs é para macOS.

Claude Code

Neste projeto: instala em .claude/skills/postgres-schema-design/.

d=".claude/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Global: instala em ~/.claude/skills/postgres-schema-design/.

d="$HOME/.claude/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Codex

Neste projeto: instala em .agents/skills/postgres-schema-design/.

d=".agents/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Global: instala em ~/.agents/skills/postgres-schema-design/.

d="$HOME/.agents/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Antigravity

Neste projeto: instala em .agents/skills/postgres-schema-design/.

d=".agents/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Global: instala em ~/.gemini/antigravity-cli/skills/postgres-schema-design/.

d="$HOME/.gemini/antigravity-cli/skills/postgres-schema-design"
u="https://raw.githubusercontent.com/davila7/claude-code-templates/57f899e5394bb8ca166f38eacae8f0853cbfe033/cli-tool/components/skills/database/postgres-schema-design"
curl -fsSL --create-dirs \
  -o "$d/SKILL.md" "$u/SKILL.md" \
  -o "$d/LICENSE.txt" "$u/LICENSE.txt"

Peça ao Rook

Já usa o Rook Labs? Cole no chat do Rook: instale a skill https://rooklabs.sh/marketplace/cct.postgres-schema-design

Prévia do SKILL.md

---
name: postgres-schema-design
description: Comprehensive PostgreSQL-specific table design reference covering data types, indexing, constraints, performance patterns, and advanced features
---

# PostgreSQL Table Design

## 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. Premature denormalization creates maintenance burden.
- Add **NOT NULL** everywhere it’s 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 integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).

## PostgreSQL “Gotchas”

- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.
- **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). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round.
- **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.
- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.

## Data Types

- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version).
…

Ver todo o marketplace