postgresql

name	postgresql
description	PostgreSQL backend patterns: full-text search, JSONB, arrays, pg_notify, PL/pgSQL functions, connection pooling with asyncpg and node-postgres

PostgreSQL Skill

When to activate

• Using PostgreSQL-specific features beyond standard SQL (full-text search, JSONB, arrays, pg_notify)
• Setting up connection pooling with asyncpg (Python) or node-postgres (Node.js)
• Writing PL/pgSQL functions or triggers
• Using PostgreSQL's pub/sub via LISTEN/NOTIFY
• Querying and indexing JSONB columns
• Building a real-time feature backed by PostgreSQL instead of Redis pub/sub

When NOT to use

• Standard CRUD with an ORM — use the Prisma or SQLAlchemy skill instead
• Generic SQL queries — use the SQL skill
• When you're on Neon or Supabase — also check those dedicated skills for platform-specific patterns

Instructions

Connection setup

Python (asyncpg — fastest async PostgreSQL driver):

import asyncpg
import os

# Connection pool (create once at startup)
pool: asyncpg.Pool

async def create_pool():
    global pool
    pool = await asyncpg.create_pool(
        dsn=os.environ["DATABASE_URL"],
        min_size=2,
        max_size=10,
        command_timeout=30,
        server_settings={
            "application_name": ,
            : ,  
        },
    )

  () ->  | :
      pool.acquire()  conn:
        row =  conn.fetchrow(
            , user_id
        )
         (row)  row  


  ():
      pool.acquire()  conn:
          conn.transaction():
             conn.execute(
                ,
                amount, from_id
            )
             conn.execute(
                ,
                amount, to_id
            )