Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
SkillScore91Maintained
Automatic
◆Data Engineer pack
Last verified 8/29/2026
What it changes
Passed 21 of 23 test cases with the skill, and 16 of 23 without it.
Without skill70%
With skill91%
Checks it passed that the model alone missed
Uses TEXT for display name and website URL columns.
Uses now() as default for transaction creation time.
Uses CHECK constraints for string length enforcement.
and 4 more in the full report
5 fixed16 passed either way2 still failing0 broke
Pass-rate lift
+22 pp
Tokens
+73%
Agent turns
0%
Response time
-2%
Model gemini-3.6-flashMeasured 8/29/2026v121 of 23 cases passed with skill
Tokens, turns and time describe resource use, not a dollar price.
See it in action
One of 5 test cases the skill fixed. Same model, same prompt, with and without the skill.
1 of 5
Prompt
Create a PostgreSQL table profiles storing a display name (max 100 chars) and website URL (max 2048 chars). Provide the DDL.
What was checked, without and with the skill
Checked
Without skill
With skill
Uses TEXT for display name and website URL columns.
Missed
Passed
Uses CHECK constraints for string length enforcement.
Missed
Passed
case-14 · gemini-3.6-flash · 8/29/2026 · answers are capped and redacted
What's inside
The instructions your model reads when the skill loads.
SKILL.md76 linessingle file
PostgreSQL Table Design
Use this skill when
Designing a schema for PostgreSQL
Selecting data types and constraints
Planning indexes, partitions, or RLS policies
Reviewing tables for scale and maintainability
Do not use this skill when
You are targeting a non-PostgreSQL database
You only need query tuning without schema changes
You require a DB-agnostic modeling guide
Instructions
Capture entities, access patterns, and scale targets (rows, QPS, retention).
Choose data types and constraints that enforce invariants.
Add indexes for real query paths and validate with EXPLAIN.
Plan partitioning or RLS where required by scale or access control.
Review migration impact and apply changes safely.
Safety
Avoid destructive DDL on production without backups and a rollback plan.
Use migrations and staging validation before applying schema changes.
Core Rules
Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.
Add NOT NULL everywhere it’s semantically required; use DEFAULTs for common values.
Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts, and join keys.
Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integer values, DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).
PostgreSQL “Gotchas”
Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use snake_case for table/column names.
Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL.