skills.rest
Tgoldi
TgoldiCOMMUNITY
Explore all skills by this author →
GitHub

postgresql

Design PostgreSQL schemas with data types, indexes, constraints, and partitioning patterns.

1|Updated May 10, 2026
Search Tags:#indexing#schema design#database#postgresql#sql#partitioning#data types
One-click install
npx skills add https://github.com/Tgoldi/claude-skills --skill postgresql-tgoldi
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql
Source: https://github.com/Tgoldi/claude-skills/tree/main/postgresql
Command: npx skills add https://github.com/Tgoldi/claude-skills --skill postgresql-tgoldi

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing a PostgreSQL schema involves many database-specific decisions—data types, indexing strategies, constraints, partitioning, and row-level security—where wrong choices lead to performance problems and painful migrations later. ## Core Features & Use Cases - Data Type Selection: Guidance on choosing correct types such as TIMESTAMPTZ over TIMESTAMP, TEXT over VARCHAR, NUMERIC for money, and identity columns over serial. - Indexing & Constraints: Covers B-tree, GIN, GiST, BRIN, partial, covering, and expression indexes plus PK, FK, UNIQUE, CHECK, and EXCLUDE constraints. - Scale Patterns: Partitioning strategies, row-level security, JSONB guidance, and workload-specific designs for insert-heavy, update-heavy, and upsert workloads. - Use Case: When building a new orders table, use this Skill to generate a schema with proper identity primary keys, foreign key indexes, CHECK constraints on status values, and TIMESTAMPTZ defaults. ## Quick Start Ask the AI to design a PostgreSQL schema for your application entities, including indexes and constraints for your expected query patterns.

Frequently Asked Questions about postgresql

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a PostgreSQL table schema with best practices?▼

Start by capturing entities, access patterns, and scale targets, then normalize to 3NF. Use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys, TIMESTAMPTZ for timestamps, TEXT for strings, and NUMERIC for money, adding NOT NULL and CHECK constraints where semantically required.

What data types should I use in PostgreSQL?▼

Prefer TIMESTAMPTZ over TIMESTAMP, TEXT over VARCHAR(n), NUMERIC over money or float for decimals, and identity columns over serial. Use JSONB for semi-structured attributes and BIGINT for integers unless storage is critical.

Does PostgreSQL automatically index foreign key columns?▼

No, PostgreSQL does not auto-index foreign key columns. You must manually create indexes on referencing columns to speed up joins and prevent locking issues during parent table deletes or updates.

When should I partition a PostgreSQL table?▼

Partition tables exceeding roughly 100 million rows when queries consistently filter on the partition key, typically time. Use declarative RANGE partitioning for time-series, LIST for discrete values, or HASH for even distribution, and avoid table inheritance.

Why do PostgreSQL sequences have gaps in ID values?▼

Gaps in identity and sequence values are normal behavior caused by rollbacks, crashes, and concurrent transactions. This is expected and should not be fixed; IDs are not guaranteed to be consecutive.

When should I use JSONB instead of regular columns in PostgreSQL?▼

Use JSONB only for optional or variable semi-structured attributes, keeping core relations in typed columns. Index JSONB with GIN for containment and key-existence queries, and extract frequently filtered scalar fields into generated columns with B-tree indexes.

Related Skills

Explore More Software Engineering Skills→
snoodleboot-iosnoodleboot-io

data-model-discovery

Gathers and validates data model requirements through structured discovery questions before schema design.

Official
Intermediate
MicrockMicrock
322

database-testing

Validate database schemas, constraints, migrations, and transaction isolation.

Community
Intermediate
bilacchibilacchi

postgresql-optimization

Optimizes PostgreSQL queries, indexes, configuration, and maintenance through a seven-phase workflow.

Community
Intermediate
cwinvestmentscwinvestments
412

migration-planner

Generate database schema migration scripts with rollback strategies.

Community
Advanced