skills.rest
maxjuniorbr
maxjuniorbrCOMMUNITY
Explore all skills by this author →
GitHub

postgresql-table-design

Design PostgreSQL schemas with keys, normalization, typing, indexing, and constraints.

2|Updated Nov 21, 2025
Search Tags:#design#postgresql#sql#constraints#indexes#schema-design
One-click install
npx skills add https://github.com/maxjuniorbr/segflow-crm --skill postgresql-table-design-maxjuniorbr
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/maxjuniorbr/segflow-crm/tree/main/.cursor/skills/postgresql-table-design
Command: npx skills add https://github.com/maxjuniorbr/segflow-crm --skill postgresql-table-design-maxjuniorbr

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

PostgreSQL schema design is prone to inconsistencies and maintenance headaches without a structured approach. This guide provides best-practice rules for keys, normalization, data types, indexing, and constraints to help you model scalable, reliable relational databases.

Core Features & Use Cases

  • Primary key and identity strategies (BIGINT identity; UUID when needed) to ensure stable, globally unique records.
  • Normalization guidance up to 3NF, guidance on when to denormalize for read performance, and clear NOT NULL, DEFAULT, and constraint usage.
  • Indexing and data-type recommendations for robust OLTP schemas, including TIMESTAMPTZ, TEXT, NUMERIC, and foreign keys with proper actions; covers partitioning considerations and safe schema evolution.
  • Practical examples for common domains like users, orders, and audit tables.

Quick Start

Design your first PostgreSQL table with a BIGINT identity primary key, TIMESTAMPTZ timestamps, NOT NULL constraints, and a foreign key to a parent table.

Frequently Asked Questions about postgresql-table-design

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

FAQPage Schema
How do I design a PostgreSQL schema that scales for OLTP workloads?▼

To design scalable PostgreSQL schemas, use BIGINT identity primary keys, TIMESTAMPTZ for timestamps, TEXT for strings, and NUMERIC for money. Apply normalization up to 3NF, NOT NULL constraints, and appropriate indexes like B-tree, GiST, or GIN to ensure maintainable OLTP databases.

What are the best practices for choosing primary keys and data types in PostgreSQL tables?▼

Best practices for PostgreSQL table design recommend choosing BIGINT identity keys for stable records, using UUID when global uniqueness is needed, and selecting TIMESTAMPTZ, TEXT, and NUMERIC data types to maintain data integrity and prevent schema inconsistencies.

When should I use GIN or GiST indexes instead of B-tree in PostgreSQL?▼

Use B-tree indexes for standard equality and range queries in PostgreSQL. Apply GIN or GiST indexes where appropriate for specialized data types or complex search operations, ensuring robust query performance without unnecessary overhead in your OLTP schema.

How do I handle safe schema evolution and constraints in PostgreSQL?▼

Safe schema evolution in PostgreSQL requires adding checks and constraints, enforcing NOT NULL rules, setting meaningful defaults, and using proper foreign key actions. This structured approach prevents maintenance headaches when modifying table structures over time.

Can I denormalize PostgreSQL tables for read performance and when does it make sense?▼

Denormalizing PostgreSQL tables makes sense when read performance demands it, even though normalization up to 3NF is the baseline. Denormalize selectively for specific query optimization while maintaining constraints and proper indexing to balance speed and data integrity.

Related Skills

Explore More Software Engineering Skills→
TonyQ-AITonyQ-AI
9

domain-modeling

Extract core business concepts, entity relationships, and domain rules from requirement specifications.

Community
Intermediate
quanngynxquanngynx

postgres-best-practices

Guide PostgreSQL schema, indexing, and migration changes with EXPLAIN verification.

Community
Intermediate
boludo00boludo00
80

postgresql

Manage PostgreSQL schemas, migrations, and queries with SQLAlchemy and Alembic.

Community
Advanced
samrocks03samrocks03

postgresql-table-design

Design PostgreSQL schemas with primary keys, foreign keys, constraints, and indexing.

Community
Advanced