Compatible with Cursor, Claude Code, Cline, Roo Code, Antigravity

Relational Database Schema Normalization & Indexing Strategy

Design production PostgreSQL schemas with composite indexes, foreign key strategies, and partitioning.

PostgreSQLSQLDatabaseIndexingArchitecture

v1.0.0 · Updated 2026-08

Skill Objective

Design high-performance PostgreSQL database schemas with optimized B-tree/GIN indexes, partition strategies, and foreign key integrity.

Execution Workflow Procedure

Ordered steps the coding agent executes when performing this skill.

Analyze Query Access Patterns

Map primary SELECT, JOIN, WHERE, and ORDER BY query patterns along with expected write volumes.

Design Composite & Partial Indexes

Create composite indexes following the Equality-First Range-Second rule, and add partial indexes for filtered queries.

Formulate Table Partitioning Strategy

Design range or hash partitioning for multi-million row tables to facilitate instant data retention drops.

Agent Safety Guardrails & Anti-Hallucination Rules

DO NOT index low-cardinality boolean columns alone without partial WHERE filters.
DO NOT create redundant indexes that share the same leftmost prefix.

Expected Output Format

Production DDL migration script with index justifications.

📊 Query Pattern Analysis: Evaluation of high-frequency queries.

🛠️ Production DDL Script: SQL statements with constraints and indexes.

Domain Verification Checks

Leftmost Prefix Rule: Places equality columns first in composite indexes.

GIN Indexing: Applies GIN jsonb_path_ops on JSONB fields.

Declarative Partitioning: Implements monthly range partitioning.

Example Agent Invocations

"Design the PostgreSQL schema and indexing strategy for an audit log table handling 25M rows/month."

"Review this customer activity schema to optimize queries filtering by tenant_id and created_at."

Install / Configure This Skill

# Place in .cursorrules or create .cursor/rules/relational-schema-indexing-strategy.mdc
# Trigger: Whenever you perform relational database schema normalization & indexing strategy

---
name: relational-schema-indexing-strategy
description: Design PostgreSQL database schemas, composite B-tree indexes, GIN JSONB indexes, and table partitions.
version: 1.0.0
compatibility: [Cursor, Claude Code, Cline, Roo Code, Antigravity]
---

# Relational Database Schema Normalization & Indexing Strategy

## Objective
Design scalable PostgreSQL relational schemas with optimal indexing and partitioning tailored to access patterns.

## Workflow
1. **Access Pattern Mapping**: Identify critical `WHERE`, `JOIN`, and `ORDER BY` clauses.
2. **Index Optimization**: Apply composite indexes (Equality first, Range second) and partial indexes.
3. **Partitioning**: Implement monthly range partitioning for high-volume time-series datasets.

Complementary Agent Prompt

Need a quick one-time prompt for chat?

Try the Database Schema & Relational Indexing Strategy — a lightweight version designed for 1-click copying directly into Claude, ChatGPT, or Cursor chat.

View Prompt