PostgreSQL Project

---
applyTo: "**/*.sql, **/migrations/**"
---

# PostgreSQL Development Instructions (2025)

_Last updated: October 2025_

- Always use the fetch tool to look up the latest PostgreSQL documentation: https://www.postgresql.org/docs
- Design normalized database schemas following 3NF or higher
- Use proper data types for columns (avoid generic types like text for everything)
- Implement indexes strategically for query performance
- Use foreign keys for referential integrity
- Leverage PostgreSQL-specific features: JSONB, arrays, full-text search, CTEs

**Core Principles:**

- Use transactions for data consistency (BEGIN, COMMIT, ROLLBACK)
- Implement proper constraints: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY
- Use views for complex queries and data abstraction
- Create stored procedures and functions for business logic when appropriate
- Use schemas for logical organization of database objects
- Implement proper naming conventions (snake_case for tables and columns)

**Query Optimization:**

- Use EXPLAIN ANALYZE to understand query performance
- Create indexes on frequently queried columns
- Use partial indexes for filtered queries
- Implement covering indexes when beneficial
- Avoid N+1 queries with proper JOINs

**Data Types:**

- Use UUID for distributed primary keys
- Use JSONB for semi-structured data (not JSON)
- Use TIMESTAMP WITH TIME ZONE for datetime values
- Use NUMERIC for precise decimal calculations
- Use appropriate integer sizes (SMALLINT, INTEGER, BIGINT)

**Security:**

- Use parameterized queries to prevent SQL injection
- Implement row-level security (RLS) when needed
- Grant minimal necessary privileges
- Use SSL/TLS for database connections

**Summary:**

> For all PostgreSQL work, always use the fetch tool to look up the latest documentation from https://www.postgresql.org/docs. Design efficient, secure, and maintainable database schemas.