skills.rest
Alejocabeza
AlejocabezaCOMMUNITY
Explore all skills by this author →
Website𝕏@AlejoDevVeGitHub

postgresql-table-design

Design PostgreSQL schemas with primary keys, 3NF normalization, constraints, and indexing.

1|1|Updated Dec 15, 2024
Search Tags:#performance#postgresql#constraints#indexes#schema-design#data-types#database-design
One-click install
npx skills add https://github.com/Alejocabeza/dotfiles --skill postgresql-table-design-alejocabeza
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/Alejocabeza/dotfiles/tree/main/opencode/skills/postgresql-table-design
Command: npx skills add https://github.com/Alejocabeza/dotfiles --skill postgresql-table-design-alejocabeza

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

PostgreSQL schema design often suffers from inconsistent keys, poor data types, and weak constraints that hinder data integrity, performance, and maintainability. This guidance provides a comprehensive set of practices to craft robust, scalable, and evolvable tables.

Core Features & Use Cases

  • Enforces core design principles: define a primary key, normalize to 3NF, avoid unnecessary denormalization, and use NOT NULL with sensible DEFAULTs.
  • Guides data types, constraints, and indexing: choose appropriate BIGINT vs UUID, TIMESTAMPTZ, NUMERIC, TEXT; apply FK indexes, and create appropriate partial, expression, and GIN/ GiST indexes.
  • Covers advanced features and patterns: JSONB vs JSON, arrays, enums, domains, composite types, partitioning, and recommended extensions (pg_trgm, timescaledb, postgis, pgvector) to address specific workloads.
  • Includes practical examples and gotchas: typical user and orders table design, identity generation, sequence behavior, and maintenance considerations.

Quick Start

Apply these guidelines to design or refactor a PostgreSQL schema for robust, scalable data models.

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 for OLTP applications to ensure data integrity and performance?▼

To design PostgreSQL schemas for OLTP applications, enforce primary keys, normalize to 3NF, use NOT NULL with defaults, select precise data types, and apply constraints with indexes to ensure data integrity and query performance.

What are the best practices for choosing PostgreSQL data types and indexes?▼

Best practices for PostgreSQL data types and indexes include selecting BIGINT or UUID for keys, using TIMESTAMPTZ and NUMERIC appropriately, and applying FK, partial, expression, and GIN or GiST indexes to optimize specific query workloads.

When should I use JSONB versus standard columns in PostgreSQL table design?▼

Use JSONB in PostgreSQL table design when handling flexible or semi-structured data, contrasting with standard columns for strictly relational data, while following normalization to 3NF to avoid unnecessary denormalization and maintain data integrity.

Does PostgreSQL table design support partitioning and extensions like pgvector or timescaledb?▼

PostgreSQL table design supports partitioning for managing large tables and integrates with recommended extensions like pgvector, timescaledb, postgis, and pg_trgm to handle specific workloads and specialized data models effectively.

How do I refactor an existing PostgreSQL schema to fix weak constraints and poor data types?▼

Refactor PostgreSQL schemas by enforcing primary keys, normalizing to 3NF, applying NOT NULL constraints with defaults, correcting data types, and adding appropriate FK, expression, and GIN or GiST indexes to improve maintainability.

What are common gotchas with PostgreSQL identity generation and sequence behavior?▼

Common PostgreSQL schema design gotchas include handling identity generation and sequence behavior correctly, ensuring appropriate defaults, and managing maintenance considerations when evolving schemas across services and deployments.

Related Skills

Explore More Software Engineering Skills→
lexicalninjalexicalninja
2

database-implementer

Design database schemas, migrations, and data access layers for applications.

Community
Advanced
diegoliriodiegolirio

sql-extract-ddl-oracle-mcp

Export Oracle schema DDL via MCP into Flyway-compatible SQL migrations.

Community
Advanced
bobmatnycbobmatnyc
20

PostgreSQL Performance Optimization

Analyze PostgreSQL 15+ queries, indexes, and configuration using EXPLAIN ANALYZE.

Community
Advanced
infraspecdevinfraspecdev
3

backend-database-review

Review database code for schema design, migration safety, ORM correctness, and query performance risks.

Official
Advanced