postgresql

name	postgresql
description	PostgreSQL optimization, indexing strategies, CTEs, window functions, extensions (pg_trgm, PostGIS), partitioning, and production tuning
layer	domain
category	database
triggers	["postgresql","postgres","pg","sql optimization","database indexing","cte","window function","database partitioning"]
inputs	["Schema design requirements","Query performance issues","Scaling requirements"]
outputs	["Optimized schema designs","Query optimization strategies","Indexing recommendations"]
linksTo	["prisma","drizzle","supabase","django","fastapi","nodejs","golang"]
linkedFrom	["error-handling","monitoring"]
preferredNextSkills	["prisma","drizzle","supabase"]
fallbackSkills	["mongodb"]
riskLevel	low
memoryReadPolicy	selective
memoryWritePolicy	none
sideEffects	[]

PostgreSQL Domain Skill

Purpose

Provide expert-level guidance on PostgreSQL schema design, indexing strategies, query optimization, CTEs, window functions, partitioning, extensions, connection management, and production tuning.

Key Patterns

1. Schema Design

-- Use UUIDs for distributed-safe primary keys
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Base table pattern with audit fields
CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT NOT NULL,
    name        TEXT NOT NULL,
    role        TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('admin', 'member', 'viewer')),
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ, -- Soft delete

    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at  now();
     ;
;
$$  plpgsql;

  users_updated_at
    BEFORE   users
      
      update_updated_at();


 INDEX idx_users_email_active
     users (email)
     is_active    deleted_at  ;


 INDEX idx_users_role_created
     users (role, created_at )
     deleted_at  ;