What primary key convention does this skill follow?

It prefers BIGINT GENERATED ALWAYS AS IDENTITY for standard reference tables and recommends UUID (specifically uuidv7) for distributed systems or global uniqueness.

Does it provide guidance on indexing?

Yes, it covers B-tree for standard queries, GIN for JSONB and arrays, GiST for geometric or range types, and BRIN for large, naturally ordered datasets.

How does it handle time-sensitive data?

The skill strictly enforces the use of TIMESTAMPTZ to ensure time zone awareness and avoid common 'timestamp without time zone' pitfalls.

Can it help with database security?

Absolutely. It includes best practices for Row-Level Security (RLS) and the use of domain types and constraints to ensure data integrity at the schema level.

Does it support modern PostgreSQL features?

Yes, it includes modern features like NULLS NOT DISTINCT (PG15+), declarative partitioning, and pgvector for AI-related similarity searches.

PostgreSQL Table Design

Name: PostgreSQL Table Design
Author: HermeticOrmus

byHermeticOrmus

•

Database Management

Designs optimized PostgreSQL schemas using industry best practices for performance, data integrity, and scalability.

About

This skill equips Claude with specialized knowledge for architecting robust PostgreSQL database schemas. It provides comprehensive guidance on selecting optimal data types, implementing 3NF normalization, and configuring advanced indexing strategies like GIN and GiST. By enforcing modern PostgreSQL conventions—such as identity columns over serial types and TIMESTAMPTZ for global consistency—this skill helps developers avoid common pitfalls while optimizing for specific workloads like high-concurrency inserts, update-heavy tables, or complex time-series data.

Key Features

Implementation of Row-Level Security (RLS) and complex CHECK constraints
Advanced partitioning guidance for large-scale data and time-series management
Comprehensive indexing strategies including B-tree, GIN, GiST, and partial indexes
Optimization patterns for insert-heavy, update-heavy, and upsert workloads
2 GitHub stars
Expert data type selection including identity columns, JSONB, and network types

Use Cases

Refactoring legacy schemas to modern PostgreSQL standards and performance patterns
Architecting new application databases from scratch using 3NF and proper constraints
Designing high-performance indexing for complex query paths and JSONB data

About

Key Features

Implementation of Row-Level Security (RLS) and complex CHECK constraints
Advanced partitioning guidance for large-scale data and time-series management
Comprehensive indexing strategies including B-tree, GIN, GiST, and partial indexes
Optimization patterns for insert-heavy, update-heavy, and upsert workloads
2 GitHub stars
Expert data type selection including identity columns, JSONB, and network types

Use Cases

Refactoring legacy schemas to modern PostgreSQL standards and performance patterns
Architecting new application databases from scratch using 3NF and proper constraints
Designing high-performance indexing for complex query paths and JSONB data