skills.rest
DucCuong159
DucCuong159COMMUNITY
Explore all skills by this author →
GitHub

postgresql

Design PostgreSQL schemas with data types, indexes, constraints, and partitioning patterns.

Updated Aug 11, 2026
Search Tags:#indexing#schema design#database#postgresql#sql#partitioning#data types
One-click install
npx skills add https://github.com/DucCuong159/Realtime-chatapp --skill postgresql-duccuong159
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql
Source: https://github.com/DucCuong159/Realtime-chatapp/tree/main/.agent/skills/postgresql
Command: npx skills add https://github.com/DucCuong159/Realtime-chatapp --skill postgresql-duccuong159

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing a PostgreSQL schema involves many database-specific decisions—data types, indexing strategies, constraints, partitioning, and row-level security—where wrong choices cause performance problems and maintenance debt that are hard to fix later. ## Core Features & Use Cases - Schema Design Guidance: Covers primary keys, normalization to 3NF, NOT NULL and CHECK constraints, and foreign key indexing rules specific to PostgreSQL behavior. - Data Type & Index Selection: Recommends correct types (TIMESTAMPTZ, NUMERIC, TEXT, JSONB, arrays, ranges) and index strategies (B-tree, GIN, GiST, BRIN, partial, covering, expression indexes). - Scale & Safety Patterns: Addresses partitioning, row-level security, update-heavy and insert-heavy workloads, upserts, and safe schema evolution with transactional DDL. - Use Case: When building a new orders table, use this Skill to choose identity columns, enforce status CHECK constraints, index the foreign key on user_id, and plan time-based partitioning for growth. ## Quick Start Ask the AI to design a PostgreSQL schema for your application's tables following best practices for data types, indexes, and constraints.

Frequently Asked Questions about postgresql

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

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

Start by capturing entities, access patterns, and scale targets, then normalize to 3NF and add NOT NULL and CHECK constraints. Use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys, TIMESTAMPTZ for timestamps, and create indexes only for query paths you actually use.

What data types should I use in PostgreSQL?▼

Prefer TEXT over VARCHAR, NUMERIC for money, TIMESTAMPTZ for timestamps, and BIGINT for integers. Avoid timestamp without time zone, char(n), the money type, and serial; use identity columns instead.

Does PostgreSQL automatically index foreign key columns?▼

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

When should I use table partitioning in PostgreSQL?▼

Use declarative partitioning for very large tables over 100M rows where queries consistently filter on the partition key, typically time. RANGE partitioning suits time-series data, LIST suits discrete values, and HASH distributes load evenly.

How do I index JSONB columns in PostgreSQL?▼

Create a GIN index on the JSONB column to accelerate containment, key existence, and path queries. For containment-only workloads use the jsonb_path_ops operator class, and extract frequently filtered scalar fields into generated columns with B-tree indexes.

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. Use non-volatile defaults for fast metadata-only changes, and prefer CREATE INDEX CONCURRENTLY to avoid blocking writes.

Related Skills

Explore More Software Engineering Skills→
Datus-aiDatus-ai
1.5k

transfer-reconciliation

Verify post-transfer row count parity and target table accessibility.

Official
Intermediate
benreceveurbenreceveur

database-migrator

Automate version-controlled database schema migrations across PostgreSQL, MySQL, and SQLite.

Community
Advanced
SynkraAISynkraAI
3.1k

aios-data-engineer

Design database schemas and configure Supabase RLS policies.

Official
Advanced
lobehublobehub
81.4k

drizzle

Define TypeScript database schemas and generate migrations with Drizzle ORM.

Official
Intermediate