postgresql-table-design
Design PostgreSQL schemas with expert guidance
PostgreSQL schema design requires knowledge of data types, indexing strategies, constraints, and performance patterns. This skill provides comprehensive guidance on PostgreSQL-specific best practices, advanced features, and common pitfalls to help you build efficient, maintainable database schemas.
Download the skill ZIP
Upload in Claude
Go to Settings → Capabilities → Skills → Upload skill
Toggle on and start using
Test it
Using "postgresql-table-design". How should I store monetary values in PostgreSQL?
Expected outcome:
Use NUMERIC(p,s) for monetary values, never float types. For example: amount NUMERIC(10,2) for currency with two decimal places. This provides exact decimal arithmetic without floating-point precision issues. Avoid the deprecated money type.
Using "postgresql-table-design". What is the difference between BIGINT GENERATED ALWAYS AS IDENTITY and UUID for primary keys?
Expected outcome:
- BIGINT GENERATED ALWAYS AS IDENTITY: Sequential integer IDs. Preferred for most use cases. Benefits: smaller storage, faster indexing, better locality. Use when global uniqueness across systems is not required.
- UUID: Globally unique identifiers. Use when: merging data from multiple databases, federating systems, or requiring opaque non-sequential IDs. Generate with uuidv7() on PostgreSQL 18+ or gen_random_uuid() on older versions.
Using "postgresql-table-design". Should I add indexes on foreign key columns?
Expected outcome:
Yes, always add explicit indexes on foreign key columns. PostgreSQL does not auto-index FK columns unlike some databases. This speeds up joins and prevents locking issues during parent table deletes or updates. Example: CREATE INDEX ON orders (user_id) for user_id foreign key.
Security Audit
SafeThis skill is a documentation-only resource providing PostgreSQL schema design guidance. All 237 static findings are false positives triggered by SQL examples and technical terminology in markdown code blocks. The skill contains no executable code, network calls, or file system access. Safe for publication.
Quality Score
What You Can Build
Design a new database schema
Get guidance on table structure, data types, constraints, and indexing strategies when designing a new PostgreSQL database for an application.
Optimize existing schema performance
Learn about indexing strategies, partitioning options, and performance patterns to improve query performance and reduce database bloat.
Review schema design decisions
Validate data type choices, constraint usage, and normalization decisions against PostgreSQL best practices before implementation.
Try These Prompts
I need to create a users table with email, name, and timestamps. What is the recommended PostgreSQL schema?
I am storing product prices, IP addresses, and user preferences. What PostgreSQL data types should I use?
My orders table has queries filtering by user_id, status, and created_at. What indexes should I create?
I need to store millions of sensor readings per day with queries filtering by device and time range. How should I design the schema and partitioning?
Best Practices
- Start with normalized schemas to third normal form and only denormalize when you have measured performance problems with specific high-value queries
- Use TIMESTAMPTZ for all timestamp columns, TEXT for strings, NUMERIC for money, and BIGINT GENERATED ALWAYS AS IDENTITY for primary keys unless you need UUIDs
- Create indexes for columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses, and always add explicit indexes on foreign key columns
Avoid
- Do not use VARCHAR(n) or CHAR(n) data types; use TEXT with CHECK constraints for length limits if needed
- Do not use TIMESTAMP without time zone, the money type, or SERIAL; use TIMESTAMPTZ, NUMERIC, and GENERATED ALWAYS AS IDENTITY instead
- Do not denormalize data prematurely before measuring actual performance issues; premature denormalization creates maintenance burden without proven benefits