agentsclimarketplace

Designing postgres schemas

Skill pumarogie/claude-postgres-skills/skills/designing-postgres-schemas

Guides Postgres schema design when creating tables, choosing keys and data types, defining foreign keys and deletion behavior, modeling JSONB, or planning retention for unbounded event and log data.From its SKILL.md

Install
npx -y skills add pumarogie/claude-postgres-skills --skill designing-postgres-schemas

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

  • 2 stars2 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

4.2 KB, as published. Nobody here has run it

Designing Postgres Schemas

Overview

Design from invariants and real access paths. Put correctness in types, constraints, and keys; add indexes for actual reads and writes. Make growth and retention explicit before deployment.

Quick Reference

DecisionStarting pointCheck before committing
Primary keybigint identity or uuidGeneration location, exposure, index locality
Time instanttimestamptzDisplay zone belongs at the application boundary
Local civil timetimestamp plus explicit zone/rulesUse only when the value is intentionally not an instant
RelationshipForeign keyIndex the referencing columns used for joins/deletes
Flexible attributesjsonbPromote constrained, filtered, or joined fields to columns
Unbounded events/logsTime-based retention planConsider partitioning before the table becomes large

Use timestamptz for an instant. It normalizes an instant and displays it in the session time zone; it does not retain the input's zone name or original offset. If the originating IANA zone matters to product behavior, store it separately (for example, origin_tz text). Use timestamp only for an intentional wall-clock value; otherwise differently configured clients can silently interpret the same zone-less value as different instants.

Baseline pattern

CREATE TABLE tasks (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   uuid NOT NULL,
    status      text NOT NULL CHECK (status IN ('pending', 'running', 'done')),
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

Give every table a stable key unless a documented reason forbids it. Use GENERATED BY DEFAULT only when callers must supply identity values.

Design from operations

For each table, write down:

  • uniqueness and validity rules to enforce;
  • hot filters, joins, ordering, and update paths;
  • row count, write rate, and retention;
  • deletion behavior and the maximum cascade fan-out;
  • which fields change often and will create dead tuples;
  • tenant or ownership boundaries.

Foreign keys do not automatically index the referencing side. Add that index for parent changes or child lookups. Treat large cascades as writes with lock, WAL, and vacuum costs.

Never add ON DELETE CASCADE to a high-volume relationship without bounding its fan-out and accepting its lock, WAL, latency, and vacuum impact. Prefer an explicit, observable deletion workflow when one parent can own many rows.

Decide JSONB from query patterns

Ask what must be filtered, joined, sorted, validated, or made unique before choosing storage.

  • Allow jsonb as a deliberate escape hatch for genuinely unsettled or variable attributes.
  • Promote every stable field used for filtering, joining, sorting, uniqueness, or relational integrity to a typed column.
  • State the tradeoff: fields kept only in JSONB forfeit ordinary column NOT NULL/CHECK constraints, foreign keys, and cheap per-column planner statistics.
  • JSONB can be indexed. Use GIN for containment/key queries, or a B-tree expression index on an extracted value such as (metadata->>'external_id') for a specific access path. An index does not restore relational constraints or ordinary column statistics.

For unbounded event or log tables, design retention and partition early when dropping time ranges will be routine. See postgres-advanced-patterns for maintenance, writing-performant-queries for indexes, and writing-safe-migrations for live changes.

Common Mistakes

  • Using timestamp for an instant because all current users share one time zone.
  • Omitting a primary key from a table that will later need updates, deduplication, or queue claims.
  • Adding a foreign key without considering the referencing-side index and deletion fan-out.
  • Storing stable relational fields only inside JSONB.
  • Allowing an event table to grow without a retention or partition-maintenance plan.

What ships with it

Read from the repository

Just SKILL.md. No reference files, no scripts.

Gives 2 of the 12 instructions most databases sql skills give

Counted across 609 of the 712 authors here whose files we hold, read 2026-09-06

  • Index all foreign key columnshere, and in 26 of 609
  • Use cursor pagination instead of offsetin 25 of 609, across 20 files
  • Use timestamptz for timestampshere, and in 21 of 609
  • Specify columns instead of using select starin 20 of 609, across 10 files
  • Use parameterized queries for all database interactionsin 20 of 609, across 19 files
  • Use Enum for categorical datain 17 of 609, across 7 files
  • Order by frequently filtered columnsin 17 of 609, across 7 files
  • Batch data insertsin 17 of 609, across 7 files
  • Use expand-contract pattern for schema changesin 17 of 609
  • Use materialized views for real-time aggregationsin 16 of 609, across 6 files
  • Partition tables by timein 16 of 609, across 6 files
  • Use smallest appropriate data typesin 16 of 609, across 6 files

Said here and by no other author read

  • design schemas from invariants and access paths
  • promote stable JSONB fields to typed columns
  • plan retention for unbounded event tables
  • document uniqueness and validity rules

Grouped from the skills themselves: near-identical wordings counted once, and counted by distinct author, so one author publishing three of these counts once.

Keep looking

Skills are one crate of 613,373. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.