skills.rest
kyamsSRL
kyamsSRLCOMMUNITY
Explore all skills by this author →
GitHub

postgresql-table-design

Design PostgreSQL table schemas with types, indexes, constraints, and partitioning.

Updated Mar 18, 2026
Search Tags:#performance#indexing#schema design#database#postgresql#partitioning#jsonb
One-click install
npx skills add https://github.com/kyamsSRL/TinyCopro --skill postgresql-table-design-kyamssrl
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/kyamsSRL/TinyCopro/tree/main/.claude/skills/postgresql-table-design
Command: npx skills add https://github.com/kyamsSRL/TinyCopro --skill postgresql-table-design-kyamssrl

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Provides clear, opinionated guidance to design PostgreSQL table schemas that avoid common pitfalls, reduce operational bloat, and deliver predictable query performance across transactional and analytical workloads.

Core Features & Use Cases

  • Primary key & identity guidance: recommends BIGINT GENERATED ALWAYS AS IDENTITY for most surrogate keys and UUID use cases when global uniqueness or opacity is required.
  • Data type & storage advice: prescribes TIMESTAMPTZ for timestamps, NUMERIC for money, TEXT for strings, JSONB for semi-structured attributes, and vector types for embeddings when applicable.
  • Indexing & partitioning patterns: explains B-tree, GIN, GiST, BRIN, composite and partial indexes, covering/index-only scans, and when to use declarative partitioning or TimescaleDB.
  • Constraints, RLS & evolution: covers PK/FK/UNIQUE/CHECK/EXCLUDE semantics, row-level security policies, safe transactional DDL, and concurrent index creation strategies.
  • Workload-specific recommendations: strategies for update-heavy tables (hot/cold column separation, fillfactor), insert-heavy loads (minimize indexes, COPY), and upsert-friendly designs.
  • Extensions and advanced features: notes useful extensions like pgcrypto, pg_trgm, postgis, pgvector, and TimescaleDB for specialized needs.

Quick Start

Ask the skill to review your proposed table schema and return a normalized PostgreSQL DDL with recommended types, indexes, constraints, and partitioning strategy.

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 schema for high-performance OLTP and analytical workloads?▼

To design a robust PostgreSQL schema, prescribe specific data types like TIMESTAMPTZ and NUMERIC, enforce PK/FK constraints, and apply targeted indexing strategies such as B-tree, GIN, or partial indexes to ensure predictable query performance.

When should I use JSONB columns and GIN indexes in PostgreSQL?▼

Use JSONB columns for semi-structured attribute storage and apply GIN indexes to efficiently query and filter the nested JSONB document structures within your PostgreSQL database.

What is the best way to handle safe schema migrations and concurrent indexing in PostgreSQL?▼

Safe schema migrations in PostgreSQL utilize transactional DDL and concurrent index creation strategies to apply structural changes without locking tables or disrupting production database operations.

How does declarative partitioning improve time-series and insert-heavy PostgreSQL tables?▼

Declarative partitioning improves time-series and insert-heavy PostgreSQL tables by dividing large datasets into manageable segments, reducing index bloat and accelerating query performance for specific time ranges.

Should I use BIGINT GENERATED ALWAYS AS IDENTITY or UUID for PostgreSQL primary keys?▼

Use BIGINT GENERATED ALWAYS AS IDENTITY for most surrogate primary keys, and reserve UUID for specific use cases requiring global uniqueness or key opacity across distributed PostgreSQL systems.

Can I optimize update-heavy PostgreSQL tables using fillfactor and column separation?▼

Optimizing update-heavy PostgreSQL tables involves separating hot and cold columns and adjusting the table fillfactor to reduce page splits and minimize operational bloat during frequent updates.

Related Skills

Explore More Software Engineering Skills→
sarimofficialsarimofficial

sqlmodel-task-models

Define SQLModel schemas for Todo users and tasks with relationships and timestamps.

Community
Intermediate
nguyenyounguyenyou
93

benchmark

Execute multi-layer performance benchmarks for Scalex indexing, queries, and memory.

Community
Advanced
joyz0joyz0

data-migration-design

Manage versioned database schema changes with transactional execution and automated rollback.

Community
Advanced
himanshu231204himanshu231204
2

clickhouse-io

Optimize ClickHouse MergeTree schemas, queries, and bulk data ingestion.

Community
Advanced