skills.rest
octanutri-clin
octanutri-clinCOMMUNITY
Explore all skills by this author →
GitHub

postgresql-table-design

Designs and reviews PostgreSQL schemas covering data types, indexing, constraints, and partitioning.

Updated Jul 9, 2026
Search Tags:#indexing#schema design#database#postgresql#sql#constraints#partitioning
One-click install
npx skills add https://github.com/octanutri-clin/octaclin --skill postgresql-table-design-octanutri-clin
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/octanutri-clin/octaclin/tree/main/.agents/skills/postgresql-table-design
Command: npx skills add https://github.com/octanutri-clin/octaclin --skill postgresql-table-design-octanutri-clin

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing a PostgreSQL schema without deep knowledge of its type system, indexing behavior, and MVCC internals leads to slow queries, bloated tables, and subtle correctness bugs. This Skill provides concrete rules and patterns for creating well-structured, performant PostgreSQL tables. ## Core Features & Use Cases - Data Type Selection: Enforces correct choices such as TIMESTAMPTZ over TIMESTAMP, TEXT over VARCHAR(n), NUMERIC for money, and BIGINT GENERATED ALWAYS AS IDENTITY over serial. - Indexing & Constraints Guidance: Covers B-tree, GIN, GiST, BRIN, partial, covering, and expression indexes, plus PK/FK/UNIQUE/CHECK/EXCLUDE constraint patterns including the need to manually index foreign keys. - Advanced Patterns: Addresses partitioning strategies, JSONB indexing, row-level security, generated columns, upsert design, safe schema evolution, and workload-specific tuning for insert-heavy or update-heavy tables. - Use Case: When creating a new orders table, apply the Skill to choose identity columns, add a status CHECK constraint, index the foreign key on user_id, and avoid common gotchas like unindexed FKs or volatile defaults that rewrite the table. ## Quick Start Review my PostgreSQL schema for the users and orders tables and suggest improvements following best practices.

Frequently Asked Questions about postgresql-table-design

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

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

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

What data types should I use in PostgreSQL?▼

Use BIGINT GENERATED ALWAYS AS IDENTITY for IDs, TIMESTAMPTZ for timestamps, NUMERIC for money, TEXT for strings, and BOOLEAN with NOT NULL. Avoid serial, timestamp without time zone, varchar(n), char(n), money, and timetz.

Does PostgreSQL automatically index foreign keys?▼

No, PostgreSQL does not auto-index foreign key columns. You must add explicit indexes on referencing columns to speed up joins and prevent locking issues during parent table deletes or updates.

When should I use JSONB instead of regular columns in PostgreSQL?▼

Use JSONB only for optional or semi-structured attributes, keeping core relations in typed columns. Index JSONB with GIN for containment and key-existence queries, and extract frequently filtered scalar fields into generated columns with B-tree indexes.

When should I partition a PostgreSQL table?▼

Partition tables exceeding roughly 100 million rows when queries consistently filter on the partition key, typically time. Use declarative RANGE partitioning for time-series, LIST for discrete values, or HASH for even distribution, and include the partition key in unique constraints.

Why does adding a column with a default rewrite my PostgreSQL table?▼

Adding a NOT NULL column with a volatile default like now() or gen_random_uuid() forces a full table rewrite. Non-volatile defaults are fast, so prefer them or add the column nullable and backfill separately.

Related Skills

Explore More Software Engineering Skills→
cncorpcncorp

sql-reader

Query production PostgreSQL databases with read-only credentials.

Official
Basic
AlexBaum-aiAlexBaum-ai

postgres-manager

Manage PostgreSQL databases by querying data, inspecting schemas, and validating migrations.

Community
Advanced
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced
kangnam7654kangnam7654

migration-advisor

Advise on safe database schema and data migrations across PostgreSQL, MySQL, and ORMs.

Community
Advanced