# postgresql

> Practical guidance for schema design, migrations, ops, testing, and performance in PostgreSQL for FastAPI + SQLAlchemy + Alembic projects. Includes safe-ops guardrails.

- Author: Marc Pujol
- Repository: kilburn/AIseminar
- Version: 20251028114812
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/kilburn/AIseminar
- Web: https://mule.run/skillshub/@@kilburn/AIseminar~postgresql:20251028114812

---

# .claude/skills/postgresql/SKILL.md
---
name: postgresql
description: Practical guidance for schema design, migrations, ops, testing, and performance in PostgreSQL for FastAPI + SQLAlchemy + Alembic projects. Includes safe-ops guardrails.
---

# PostgreSQL Skill

Use this Skill whenever tasks involve relational data modeling, migrations, SQLAlchemy ORM, or database ops.

## When to use
- Designing/altering tables, constraints, indexes.
- Writing SQLAlchemy models, queries, transactions.
- Creating Alembic migrations.
- Optimizing slow queries or diagnosing locks.

## Golden rules (quick)
- Prefer **explicit constraints**: `NOT NULL`, `CHECK`, `UNIQUE`, FK with `ON DELETE ...`.
- Use **surrogate PKs** (`BIGSERIAL`/`GENERATED BY DEFAULT AS IDENTITY`) + **natural keys** as `UNIQUE`.
- Always add **indexes that match your access patterns**; measure before/after with `EXPLAIN (ANALYZE, BUFFERS)`.
- Keep migrations **idempotent** and **reversible** where possible.
- Default all writes in the app to **transactions** with **READ COMMITTED** unless you require stricter isolation.

## Connection & env
- DSN shape: `postgresql+psycopg://user:pass@host:5432/dbname`
- Use **connection pooling** in production (e.g., SQLAlchemy `QueuePool`).
- Set `statement_timeout` (e.g., 5–15s) and `idle_in_transaction_session_timeout`.

## Schema & modeling
- Timestamps: `created_at TIMESTAMPTZ DEFAULT now()`, `updated_at TIMESTAMPTZ`.
- Soft delete: `deleted_at TIMESTAMPTZ NULL` (index if frequently filtered).
- Status enums: Postgres `ENUM` or lookup table; prefer lookup table for portability.
- JSONB for semi-structured fields; index with `GIN` (`jsonb_path_ops`) when filtered.

### Example SQLAlchemy model
```py
from sqlalchemy import (
    Column, BigInteger, Text, Enum, Boolean, DateTime, func, ForeignKey, CheckConstraint, Index
)
from sqlalchemy.orm import relationship, Mapped, mapped_column, declarative_base

Base = declarative_base()

class Task(Base):
    __tablename__ = "tasks"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    title: Mapped[str] = mapped_column(Text, nullable=False)
    priority: Mapped[str] = mapped_column(Enum("low", "medium", "high", name="priority_enum"), nullable=False, server_default="medium")
    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    is_done: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    created_at: Mapped = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)

    __table_args__ = (
        CheckConstraint("length(title) BETWEEN 1 AND 280", name="tasks_title_len_ck"),
        Index("ix_tasks_owner_done", "owner_id", "is_done"),
    )
````

## Alembic migrations

* **Never** hand-edit autogenerate outputs without reviewing diffs.
* For large tables: use **concurrent indexes** (`op.execute("CREATE INDEX CONCURRENTLY ...")`) in separate migration.
* Add **downgrade** when feasible; if not, explain why in comments.

### Example autogenerate flow

```bash
alembic revision --autogenerate -m "add priority to tasks"
alembic upgrade head
```

### Concurrent index template

```py
def upgrade() -> None:
    op.execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_tasks_owner_done ON tasks(owner_id, is_done)")
def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_tasks_owner_done")
```

## Query patterns

* Paginate with keyset when possible:

```sql
-- keyset: where (owner_id, id) > (:owner_id, :cursor_id) order by owner_id, id
```

* Use **`SELECT ... FOR UPDATE SKIP LOCKED`** for work queues.
* Use **CTEs** for readability; benchmark as they can be optimization fences pre-PG12.

## Transactions & isolation

* Default `READ COMMITTED`.
* For race-free counters or balance transfers, use **`SERIALIZABLE`** or row-level locks with **`FOR UPDATE`**.

## Testing (pytest)

* Use **transactional tests** with rollbacks.
* Seed minimal fixtures; avoid global mutable state.
* Validate migrations with a **migration test**: start at base, `upgrade head`, assert schema.

## Performance checklist

* Add the **right index** (btree for equality/sorting; **GIN** for JSONB containment; **GiST/SP-GiST** for geo; **BRIN** for append-only large tables).
* Keep **rows narrow**; avoid unbounded `TEXT` on hot tables.
* Vacuum & analyze; tune `work_mem`, `shared_buffers`, `effective_cache_size` sensibly.

## Ops & safety

* Backups: periodic **base backups + WAL archiving**; verify restores.
* Use **`pg_stat_activity`** to find blockers.
* Never run destructive ops during peak hours; wrap in feature flags/migrations.

### Dangerous ops guardrail

* Dropping columns/tables or changing NULLability on big tables must be:

  1. reviewed,
  2. tested on a clone,
  3. executed off-peak,
  4. accompanied by backups and a rollback plan.