What is the recommended ID type for new tables?

It prefers 'BIGINT GENERATED ALWAYS AS IDENTITY' for most tables to ensure high performance and sequential safety, recommending UUIDs only for distributed systems.

Does it support modern PostgreSQL features like JSONB?

Yes, it includes best practices for JSONB storage and GIN indexing for semi-structured data, while advising when to stick to relational columns.

Does this skill handle foreign key indexing?

Yes, it specifically enforces the manual creation of indexes on foreign key columns, as PostgreSQL does not auto-index them, preventing potential locking issues and slow joins.

Why does it recommend TIMESTAMPTZ over TIMESTAMP?

It mandates TIMESTAMPTZ to ensure that all event data is stored with absolute time reference, avoiding common bugs related to server or application timezone shifts.

How does it handle large-scale data?

The skill provides detailed patterns for declarative partitioning (Range, List, Hash) for tables exceeding 100 million rows, ensuring maintenance and query performance.

PostgreSQL Schema Architect

Name: PostgreSQL Schema Architect
Author: HermeticOrmus

byHermeticOrmus

0•

データベース管理

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

This skill provides comprehensive, domain-specific guidance for designing high-performance PostgreSQL databases. It covers essential core rules like normalization to 3NF, specific data type recommendations (such as preferring TIMESTAMPTZ and BIGINT), and advanced indexing strategies including GIN and GiST. By incorporating PostgreSQL-specific 'gotchas'—like the lack of automatic foreign key indexing and the nuances of MVCC—this skill ensures that your database structures are not just syntactically correct, but optimized for the unique architecture of PostgreSQL. It is particularly useful for developers building complex applications that require robust constraints, efficient partitioning for large datasets, and safe schema evolution.

主な機能

01Performance-tuned partitioning (Range, List, and Hash)

02Advanced indexing strategies (Partial, Expression, GIN, and BRIN)

03Expert data type selection (BIGINT identity, TIMESTAMPTZ, JSONB)

040 GitHub stars

05Best practices for Row-Level Security (RLS) and schema evolution

06Robust constraint implementation (EXCLUDE, CHECK, and NULLS NOT DISTINCT)

ユースケース

01Optimizing existing tables to reduce bloat and improve query performance

02Designing high-concurrency e-commerce or financial database schemas

03Implementing partitioning and BRIN indexes for massive time-series datasets

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add hermeticormus/floraheritage postgresql

For use in Claude.ai and ChatGPT

Download Skill