skills.rest
mefardales
mefardalesCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

database-design

Design PostgreSQL relational schemas, write SQL migrations, and optimize queries.

Updated Mar 12, 2026
Search Tags:#query optimization#schema design#database#postgresql#migrations#data modeling#sql
One-click install
npx skills add https://github.com/mefardales/skillbox --skill database-design-mefardales
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-design
Source: https://github.com/mefardales/skillbox/tree/main/skills/backend/database-design
Command: npx skills add https://github.com/mefardales/skillbox --skill database-design-mefardales

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill helps you design efficient, scalable, and maintainable relational database schemas, write database migrations, optimize queries, and make informed data modeling decisions.

Core Features & Use Cases

  • Schema Design: Follows best practices for naming conventions, standard columns, and data types.
  • Relationship Management: Implements one-to-many, many-to-many, and one-to-one relationships correctly.
  • Indexing Strategies: Guides on when and how to use various index types (B-tree, GIN, partial, composite) for optimal performance.
  • Query Optimization: Provides techniques for improving SQL query speed using EXPLAIN ANALYZE and common patterns.
  • Migrations: Outlines best practices for creating safe and reversible database migrations.
  • Constraints: Emphasizes the use of NOT NULL, CHECK, UNIQUE, and foreign key constraints.
  • Performance Patterns: Suggests advanced techniques like connection pooling, read replicas, partitioning, and denormalization.

Quick Start

Design a PostgreSQL schema for a blog with users, posts, and tags, including necessary indexes and foreign keys.

Frequently Asked Questions about database-design

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

FAQPage Schema
How do I design a PostgreSQL database schema with proper relationships and indexes?▼

To design a PostgreSQL database schema, define tables with appropriate data types, implement one-to-many and many-to-many relationships using foreign keys, and apply B-tree or composite indexes to optimize query performance and ensure scalability.

What's the best way to optimize slow SQL queries in PostgreSQL?▼

The best way to optimize slow SQL queries is to run EXPLAIN ANALYZE to identify bottlenecks, then apply targeted indexing strategies like partial or GIN indexes, utilize constraints, and adopt performance patterns such as partitioning or denormalization.

How do I write safe and reversible database migrations for SQL?▼

Writing safe database migrations involves outlining step-by-step schema changes that adhere to best practices, ensuring operations are reversible, maintaining data integrity through constraints, and avoiding locking issues during deployment.

When should I use partial or composite indexes in database schema design?▼

Use partial indexes when querying a specific subset of rows frequently, and use composite indexes when queries filter or sort by multiple columns simultaneously, ensuring optimal index type selection for PostgreSQL performance patterns.

Does this database schema design approach work for large-scale applications?▼

Yes, this database schema design approach supports large-scale applications by incorporating advanced performance patterns like connection pooling, read replicas, table partitioning, and strategic denormalization to maintain query speed and maintainability.

Related Skills

Explore More Software Engineering Skills→
DriveConnect-alphaDriveConnect-alpha
1

postgresql-modeling

Define PostgreSQL schemas with relationships, constraints, and SQL scripts.

Official
Advanced
Harmeet10000Harmeet10000
7

sql-optimization-patterns

Analyzes SQL slow queries and recommends indexing and rewrite strategies.

Community
Advanced
Nick777-PixelNick777-Pixel

drizzle

Generate TypeScript ORM migrations and models from Drizzle schema definitions for PostgreSQL projects.

Community
Intermediate
miles990miles990
20

data-design

Guide relational and NoSQL database schema design with normalization and pipeline patterns.

Community
Advanced