---
name: ecc-postgres-patterns
description: PostgreSQL database patterns for query optimization, schema design, indexing, and security. Use when working with PostgreSQL databases.
---

# PostgreSQL Patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security.

## Schema Design

### Use UUIDs for public IDs, serial for internal:
```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    public_id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL
);
```

### Timestamps pattern:
```sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();
```

## Indexing

```sql
-- B-tree for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- GIN for JSONB and full-text search
CREATE INDEX idx_metadata ON documents USING GIN(metadata);

-- Partial indexes for common filters
CREATE INDEX idx_active_users ON users(is_active) WHERE is_active = true;

-- Composite indexes for multi-column queries
CREATE INDEX idx_user_created ON posts(user_id, created_at DESC);
```

## Query Optimization

- Use `EXPLAIN ANALYZE` to identify slow queries
- Prefer `JOIN` over subqueries
- Use `LIMIT` with `OFFSET` for pagination
- Consider materialized views for expensive aggregations
- Use `LATERAL` joins for complex per-row queries

## Security

- Use parameterized queries (never string interpolation)
- Row Level Security for multi-tenant apps
- Least privilege for database users
- Encrypt sensitive columns with `pgcrypto`
