postgres

name

postgres

description

Use when designing, querying, or managing a PostgreSQL database. Enforces enterprise production rules for advanced querying, composite indexing, JSONB data handling, and strict optimization patterns (avoiding N+1). Keywords: PostgreSQL query, JSONB, pg, pgvector, RLS, Postgres migration. MUST-ASK: Require explicit database engine name before triggering. Never guess. Do NOT trigger for generic "set up a database" requests. NOT for SQLite or MySQL. NOT for ORM-managed migrations.

PostgreSQL Production Standards

PostgreSQL is a deeply capable object-relational database. Because of its advanced feature set, AI agents and orchestrated deployment workflows MUST adhere to strict operational boundaries to avoid common pitfalls like index bloating, inefficient JSONB parsing, and locking regressions.

1. Advanced Querying & Safety

• Strict Parameterization: Just like MySQL, executing parameterized queries ($1, $2, ...) is a non-negotiable hard mandate. String interpolation is globally prohibited.
• Explicit Columns: NEVER use SELECT * in production code. You must specifically query required columns. This minimizes data transfer latency and prevents application crashes if schema columns mutate.
• N+1 Avoidance: Agents scaffolding code must implement batch loading architectures (e.g., DataLoader) or advanced JOIN logic when building APIs. Do not execute identical iterative queries inside loops.
• Guarded Modifications: Any UPDATE or DELETE MUST contain a deterministic WHERE block.

2. Advanced Indexing Patterns

• Targeted Strategies: Do not blindly index every column. Optimize based on frequency.
• Partial Indexes: If you routinely query states like WHERE active = true, you must use a Partial Index (CREATE INDEX idx_active_users ON users(email) WHERE active = true;) instead of indexing the entire column.
• Composite Layout: For standard queries targeting multiple identifiers, employ Composite Indexes with the most selective columns first.

3. Operations & Migrations

• Transaction Safety: Wrap multi-step mutations in BEGIN; ... COMMIT;. Standardize transactions to stay small and fast to prevent lock contentions.
• Analytical Overviews: Use EXPLAIN ANALYZE locally when debugging slow queries to analyze sequential sweeps relative to index scans.
• Schema Extensibility: Add constraints and columns dynamically (e.g., ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);). Always apply sane DEFAULT properties to avoid backfilling issues on massive tables.

4. Modern PostGres Functionality

• JSONB Arrays/Objects: Use JSONB, never JSON. JSONB is stored in a decomposed binary format, allowing fast, native index checking (via @> or ? operators) instead of full parsing upon retrieval.
• Row-Level Security (RLS): Emphasize defining default-deny security protocols using ENABLE ROW LEVEL SECURITY.
• Ecosystem Integration: Whenever available, prefer using structured postgres-mcp tools for safe schema interpretation over native Bash psql piping.