How does this skill handle PostgreSQL primary keys?

It prioritizes 'BIGINT GENERATED ALWAYS AS IDENTITY' for optimal performance and sequence management, suggesting UUIDs only when global uniqueness or opacity is specifically required.

Can it help with high-concurrency database workloads?

Absolutely. It includes specific patterns for update-heavy tables, such as fillfactor adjustments and heap-only tuple (HOT) optimizations to reduce bloat.

Is JSONB supported for semi-structured data?

Yes, it recommends JSONB for optional attributes and provides instructions on implementing GIN indexes to ensure those fields remain searchable and performant.

Does it support advanced indexing strategies?

Yes, it provides guidance on using B-tree, GIN for JSONB/arrays, GiST for ranges/geometry, and BRIN for massive time-series datasets to maximize query performance.

How does it treat timezone-sensitive data?

It strictly enforces the use of 'TIMESTAMPTZ' over 'TIMESTAMP' to ensure data integrity across different time zones and avoid common calculation errors.

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 to architect professional-grade PostgreSQL databases. It moves beyond basic SQL by enforcing strict best practices such as utilizing BIGINT identity columns over serial types, implementing proper indexing for foreign keys, and choosing specialized data types like TIMESTAMPTZ and JSONB correctly. It provides expert guidance on advanced features including Row-Level Security (RLS), declarative partitioning for large datasets, and sophisticated indexing strategies like GIN and GiST. Whether you are building a new system or refactoring an existing one, this skill ensures your database is performant, maintainable, and avoids common 'gotchas' inherent in PostgreSQL.

Key Features

Advanced indexing guidance for B-tree, GIN, GiST, BRIN, and Partial indexes
Implementation of Row-Level Security (RLS) and complex CHECK constraints
Expert data type selection including UUIDv7, TIMESTAMPTZ, and JSONB
0 GitHub stars
Optimization strategies for insert-heavy and update-heavy workloads
Scalability patterns for partitioning by range, list, and hash

Use Cases

Refactoring existing databases to resolve performance bottlenecks and deadlocks
Implementing specialized data needs like full-text search, vector search, or geospatial data
Designing a high-performance relational schema for a scalable web application

About

Key Features

Advanced indexing guidance for B-tree, GIN, GiST, BRIN, and Partial indexes
Implementation of Row-Level Security (RLS) and complex CHECK constraints
Expert data type selection including UUIDv7, TIMESTAMPTZ, and JSONB
0 GitHub stars
Optimization strategies for insert-heavy and update-heavy workloads
Scalability patterns for partitioning by range, list, and hash

Use Cases

Refactoring existing databases to resolve performance bottlenecks and deadlocks
Implementing specialized data needs like full-text search, vector search, or geospatial data
Designing a high-performance relational schema for a scalable web application