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

0•

Database Management

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

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

01Advanced indexing guidance for B-tree, GIN, GiST, BRIN, and Partial indexes

02Implementation of Row-Level Security (RLS) and complex CHECK constraints

03Expert data type selection including UUIDv7, TIMESTAMPTZ, and JSONB

040 GitHub stars

05Optimization strategies for insert-heavy and update-heavy workloads

06Scalability patterns for partitioning by range, list, and hash

Use Cases

01Refactoring existing databases to resolve performance bottlenecks and deadlocks

02Implementing specialized data needs like full-text search, vector search, or geospatial data

03Designing a high-performance relational schema for a scalable web application

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add hermeticormus/after-the-third-cup postgresql

For use in Claude.ai and ChatGPT

Download Skill