skills.rest
OMIXEC
OMIXECCOMMUNITY
Explore all skills by this author →
GitHub

postgresql-table-design

Design and review PostgreSQL schemas for keys, types, indexes, and partitioning.

1|Updated Apr 13, 2026
Search Tags:#performance#indexing#postgresql#partitioning#schema-design#jsonb
One-click install
npx skills add https://github.com/OMIXEC/Mobile-dev-skills --skill postgresql-table-design-omixec
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/OMIXEC/Mobile-dev-skills/tree/main/skills/postgresql-table-design
Command: npx skills add https://github.com/OMIXEC/Mobile-dev-skills --skill postgresql-table-design-omixec

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Helps teams design and review PostgreSQL schemas to avoid common pitfalls, improve query performance, and ensure safe schema evolution across OLTP, analytics, and time-series workloads. It translates database design tradeoffs into concrete decisions about keys, types, indexes, partitioning, and extension selection.

Core Features & Use Cases

  • Best-practice guidance for primary key strategy, when to use bigint identity versus UUID, and ID generation considerations.
  • Data type recommendations for timestamps, numeric precision, JSONB usage, arrays, ranges, and vector types to match query and storage requirements.
  • Indexing patterns including B-tree, GIN, GiST, BRIN, covering and partial indexes, expression indexes, and index-only scan strategies.
  • Partitioning and table type guidance for very large tables, time-series data, and bulk-load workloads, plus safe schema evolution and concurrent operations.
  • Extension recommendations including pgcrypto, pg_trgm, timescaledb, postgis, and pgvector for advanced capabilities.
  • Use case examples: designing a users/orders schema, optimizing JSONB access, architecting partitioned event logs, and planning upsert-friendly constraints.

Quick Start

Analyze the provided PostgreSQL schema DDL and recommend concrete changes for primary keys, data types, indexes, partitioning, and JSONB indexing with brief rationale.

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 OLTP and time-series workloads?▼

Designing a PostgreSQL schema requires selecting appropriate primary keys, optimizing data types, applying indexing strategies, and configuring partitioning for time-series or OLTP performance. Concrete recommendations cover bigint identity versus UUID, JSONB access patterns, and BRIN or GIN indexes.

What is the best way to index JSONB columns in PostgreSQL for query performance?▼

Indexing JSONB columns effectively involves using GIN indexes for key-value lookups or expression indexes for specific JSON paths. Best-practice schema design ensures semi-structured data access is optimized without sacrificing storage efficiency or query speed.

When should I use table partitioning in PostgreSQL?▼

Table partitioning in PostgreSQL is necessary for very large tables, time-series data, and bulk-load workloads to improve query performance and maintenance. Proper schema design evaluates partitioning strategies alongside primary key constraints to ensure safe concurrent operations.

How do I choose between B-tree, GIN, and BRIN indexes for my PostgreSQL tables?▼

Choosing between B-tree, GIN, and BRIN indexes depends on your query patterns and data distribution. Schema design best practices recommend B-tree for range queries, GIN for JSONB or array containment, and BRIN for large time-series tables with natural ordering.

Does PostgreSQL schema design support safe schema evolution procedures?▼

PostgreSQL schema design supports safe schema evolution by applying concurrent operations and upsert-friendly constraints. Best-practice guidance ensures you can evolve table structures, add indexes, or modify types without locking out concurrent access.

Do I need PostgreSQL extensions like pgvector or timescaledb for advanced table design?▼

PostgreSQL extensions like pgvector, timescaledb, pgcrypto, postgis, and pg_trgm are recommended for advanced table design when handling vectors, time-series, encryption, or geospatial data. Schema design guidance matches extension selection to specific workload requirements.

Related Skills

Explore More Software Engineering Skills→
JetBrainsJetBrains
302

supabase-postgres-best-practices

Identify and optimize PostgreSQL bottlenecks in schemas, queries, and configuration.

Official
Advanced
Claude-Code-Community-IrelandClaude-Code-Community-Ireland
10

database-migration

Guide relational database schema evolution with migration conventions and rollback procedures.

Official
Advanced
sitharaj88sitharaj88
1

db-postgres

Generate PostgreSQL schemas, queries, indexes, and replication configurations.

Community
Advanced
kyamsSRLkyamsSRL

postgresql-table-design

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

Community
Advanced