skills.rest
5onyy
5onyyCOMMUNITY
Explore all skills by this author →
GitHub

postgresql

Design PostgreSQL schemas with data types, indexes, constraints, and partitioning patterns.

Updated Apr 9, 2026
Search Tags:#indexing#schema design#database#postgresql#sql#partitioning#data types
One-click install
npx skills add https://github.com/5onyy/essential-ai-agent-skills --skill postgresql-5onyy
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql
Source: https://github.com/5onyy/essential-ai-agent-skills/tree/main/.cursor/skills/postgresql
Command: npx skills add https://github.com/5onyy/essential-ai-agent-skills --skill postgresql-5onyy

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing a PostgreSQL schema involves many database-specific decisions—data types, indexing strategies, constraints, partitioning, and row-level security—where wrong choices lead to performance problems, data integrity issues, and painful migrations later. ## Core Features & Use Cases - Data Type Selection: Guidance on choosing correct types (TIMESTAMPTZ over TIMESTAMP, TEXT over VARCHAR, NUMERIC for money, BIGINT identity over serial) and which types to avoid entirely. - Indexing & Constraints: Covers B-tree, GIN, GiST, BRIN, partial, covering, and expression indexes, plus PK/FK/UNIQUE/CHECK/EXCLUDE constraint patterns. - Scale Patterns: Partitioning strategies, row-level security, JSONB guidance, and workload-specific designs for insert-heavy, update-heavy, and upsert-heavy tables. - Use Case: When building a new orders table, use this Skill to generate a schema with an identity primary key, proper foreign key indexes, CHECK constraints on status values, and TIMESTAMPTZ timestamps following PostgreSQL best practices. ## Quick Start Use the postgresql skill to design a schema for a users and orders database with proper indexes and constraints.

Frequently Asked Questions about postgresql

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a PostgreSQL table schema with best practices?▼

Start with a BIGINT GENERATED ALWAYS AS IDENTITY primary key, normalize to 3NF, add NOT NULL constraints where semantically required, and create indexes only for query paths you actually use. Prefer TIMESTAMPTZ for timestamps, NUMERIC for money, and TEXT for strings.

What data types should I avoid in PostgreSQL?▼

Avoid timestamp without time zone (use timestamptz), char(n) and varchar(n) (use text), the money type (use numeric), timetz, and serial (use generated always as identity). These types cause timezone bugs, padding issues, or precision problems.

Does PostgreSQL automatically index foreign key columns?▼

No, PostgreSQL does not automatically create indexes on foreign key columns. You must add them manually to speed up joins and prevent locking issues when parent rows are deleted or updated.

When should I use table partitioning in PostgreSQL?▼

Use declarative partitioning for very large tables over 100 million rows where queries consistently filter on the partition key, typically a time or date column. RANGE partitioning suits time-series data, LIST suits discrete values, and HASH distributes load evenly.

Should I use JSONB or regular columns in PostgreSQL?▼

Keep core relational data in typed columns and use JSONB only for optional or semi-structured attributes. Index JSONB columns with GIN for containment and key-existence queries, and extract frequently queried scalar fields into generated columns with B-tree indexes.

Why do PostgreSQL sequences have gaps in ID values?▼

Gaps in identity and sequence values are normal behavior caused by rollbacks, crashes, and concurrent transactions. This is expected and you should not try to make IDs consecutive, as doing so adds complexity without benefit.

Related Skills

Explore More Software Engineering Skills→
Datus-aiDatus-ai
1.5k

transfer-reconciliation

Verify post-transfer row count parity and target table accessibility.

Official
Intermediate
MicrockMicrock
322

database-testing

Validate database schemas, constraints, migrations, and transaction isolation.

Community
Intermediate
arbazkhan971arbazkhan971
26

postgres

Optimize PostgreSQL production performance with tuning, indexing, and replication guidance.

Community
Advanced
arcasilesgrouparcasilesgroup
54

schema

Design database schemas and plan safe migrations for PostgreSQL, MySQL, and SQLite.

Official
Advanced