What PostgreSQL data types are recommended for modern apps?

The skill recommends using BIGINT for IDs, TIMESTAMPTZ for events, NUMERIC for currency, and TEXT instead of VARCHAR, while avoiding outdated types like Serial.

Does it support database security best practices?

Yes, it includes implementation details for Row-Level Security (RLS) and complex constraints to ensure data integrity and secure multi-tenant access.

How does it handle advanced features like JSONB?

It provides guidance on when to use JSONB versus relational columns and how to effectively index semi-structured data using GIN indexes.

Does this skill help with PostgreSQL performance tuning?

Yes, it provides specific patterns for indexing, vacuum management, and table design to optimize both read and write performance in high-traffic environments.

Can this skill help with large-scale data partitioning?

Absolutely. It covers RANGE, LIST, and HASH partitioning strategies for tables exceeding 100 million rows to ensure manageable maintenance and query speed.

PostgreSQL Schema Design

Name: PostgreSQL Schema Design
Author: Microck

byMicrock

•

数据库管理

Architects high-performance PostgreSQL databases using industry best practices for schema design, indexing, and data integrity.

This skill provides specialized guidance for designing robust and scalable PostgreSQL databases by leveraging domain-specific implementation patterns. It covers essential best practices such as 3NF normalization, proper data type selection (preferring TIMESTAMPTZ and BIGINT), and advanced indexing techniques including GIN, GiST, and BRIN. It helps developers avoid common pitfalls like silent coercions and unindexed foreign keys, while offering sophisticated strategies for performance-critical scenarios such as partitioning for large datasets, Row-Level Security (RLS) implementation, and optimizing update-heavy workloads.

主要功能

01Optimized schema normalization and table design patterns

02Declarative partitioning implementation for high-volume datasets

03Performance tuning for insert-heavy and update-heavy workloads

04Advanced indexing strategies including partial, expression, and covering indexes

05Strict data type enforcement and 'do-not-use' validation for modern PG versions

062 GitHub stars

使用场景

01Optimizing database performance through strategic indexing and partitioning

02Designing a scalable multi-tenant SaaS database architecture with Row-Level Security

03Refactoring legacy schemas to modern PostgreSQL standards like Identity columns

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add microck/opendots-microck postgresql

For use in Claude.ai and ChatGPT

Download Skill