How does this skill improve PostgreSQL performance?

It provides specific patterns for indexing, such as using GIN for JSONB and B-tree for selective columns, and suggests table-specific settings like fillfactor for update-heavy environments.

Can it handle modern PostgreSQL features?

Absolutely. It includes guidance on modern features like NULLS NOT DISTINCT (PG15+), JSONB, identity columns, and declarative partitioning.

Does this skill help with data integrity?

Yes, it enforces strict practices regarding NOT NULL constraints, foreign key indexing, and custom CHECK or EXCLUDE constraints to prevent invalid data entry.

Why does it recommend BIGINT over UUID for primary keys?

It prefers BIGINT GENERATED ALWAYS AS IDENTITY for performance and storage efficiency, suggesting UUIDs only when global uniqueness or opacity is strictly required.

PostgreSQL Table Design Guide

Name: PostgreSQL Table Design Guide
Author: trancong12102

bytrancong12102

•

데이터베이스 관리

Architects high-performance, production-ready PostgreSQL schemas using industry-standard best practices and domain-specific patterns.

소개

This skill provides Claude with a comprehensive knowledge base for designing robust PostgreSQL databases. It moves beyond basic SQL to offer specific guidance on optimal data types, advanced indexing strategies like GIN and GiST, table partitioning for large datasets, and performance tuning for specific workloads. By incorporating PostgreSQL 'gotchas' and modern features like Identity columns and Row-Level Security, this skill ensures your database architecture is scalable, maintainable, and optimized for peak performance.

주요 기능

Comprehensive constraint logic covering PKs, FKs, CHECK constraints, and exclusion policies.
Workload-specific optimizations for update-heavy, insert-heavy, and upsert-heavy tables.
Expert data type selection including TIMESTAMPTZ, BIGINT identity, and JSONB recommendations.
Advanced indexing patterns for B-tree, GIN, GiST, BRIN, and partial/expression indexes.
Scalability strategies via declarative partitioning (Range, List, Hash) and TimescaleDB patterns.
1 GitHub stars

사용 사례

Refactoring legacy table structures to improve query performance and eliminate data redundancy.
Implementing complex access controls using PostgreSQL Row-Level Security (RLS) and custom domain types.
Designing normalized 3NF database schemas for new applications from scratch.

소개

주요 기능

Comprehensive constraint logic covering PKs, FKs, CHECK constraints, and exclusion policies.
Workload-specific optimizations for update-heavy, insert-heavy, and upsert-heavy tables.
Expert data type selection including TIMESTAMPTZ, BIGINT identity, and JSONB recommendations.
Advanced indexing patterns for B-tree, GIN, GiST, BRIN, and partial/expression indexes.
Scalability strategies via declarative partitioning (Range, List, Hash) and TimescaleDB patterns.
1 GitHub stars

사용 사례

Refactoring legacy table structures to improve query performance and eliminate data redundancy.
Implementing complex access controls using PostgreSQL Row-Level Security (RLS) and custom domain types.
Designing normalized 3NF database schemas for new applications from scratch.