Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
Design high-performance, maintainable schemas specifically for PostgreSQL. This skill covers data type selection, indexing strategies, and advanced features like partitioning and RLS.
| Resource | Description |
|---|---|
| Core Rules & Gotchas | surrogate keys, normalization, and PITR pitfalls. |
| Data Types Guide | Deep-dive into IDs, numeric types, and specialized storage. |
| Indexing & Partitioning |
| B-tree, GIN, GiST indexes and range/list partitioning. |
| Performance Patterns | Update-heavy vs. Insert-heavy design and safe migrations. |
| JSONB & Extensions | Managing semi-structured data and using pgvector/PostGIS. |
Choose correct data types (e.g., TIMESTAMPTZ, BIGINT) and enforce invariants with constraints.
Implement indexes for actual access paths (PKs, FKs, frequent filters).
Utilize JSONB for flexible attributes and partitioning for large-scale data.
See JSONB & Extensions.
Execute safe, transactional DDL changes.
See Performance Patterns.
BIGINT GENERATED ALWAYS AS IDENTITYTIMESTAMPTZNUMERIC(p, s)database-architect: High-level data architecture.database-optimizer: SQL tuning.Design PostgreSQL schemas that are reliable, performant, and scalable from day one.