PostgreSQL Schema Design

About

This skill equips Claude with specialized knowledge for architecting PostgreSQL databases, focusing on performance, data integrity, and modern features. It provides strict guidance on data type selection (preferring TIMESTAMPTZ and BIGINT), indexing strategies for various workloads, and advanced patterns like declarative partitioning and Row-Level Security. By following these rules, developers can avoid common pitfalls such as silent truncations, inefficient vacuuming, and missing foreign key indexes, resulting in a production-grade database foundation.

Key Features

• Advanced data modeling for JSONB, arrays, range types, and full-text search.
• Comprehensive indexing strategies including B-tree, GIN, GiST, and BRIN for specialized query paths.
• Data integrity enforcement through normalization (3NF) and complex constraints like EXCLUDE and CHECK.
• 1 GitHub stars
• Performance patterns for update-heavy vs. insert-heavy workloads and safe schema evolution.
• Enforcement of optimal PostgreSQL data types and avoidance of legacy types like SERIAL or VARCHAR(n).

Use Cases

• Architecting a new relational schema for high-concurrency applications.
• Implementing specialized features like geospatial data with PostGIS or time-series partitioning.
• Refactoring existing tables to improve query performance and reduce storage bloat.