postgresql-best-practices

@wimolivier

PostgreSQL 18+ enterprise best practices for database development. USE THIS SKILL WHEN THE USER: - Creates schemas, tables, functions, procedures, or triggers - Writes PL/pgSQL code (naming conventions: l_, in_, io_, co_ prefixes) - Implements Table API (SECURITY DEFINER functions, schema separation) - Manages migrations, indexes, constraints, or query performance - Works with PostgreSQL 18+ features (uuidv7, virtual columns) - Builds Medallion Architecture data warehouses (Bronze/Silver/Gold) - Reviews code for anti-patterns or migrates from Oracle PL/SQL CORE PATTERNS: - Three-schema separation: data (tables) → private (internal) → api (external) - Table API: All access via SECURITY DEFINER with SET search_path - Native PL/pgSQL migration system (no Flyway/Liquibase needed) - Trivadis naming: l_ (local), in_ (input), io_ (inout), co_ (constant)