postgresql

# SKILL: PostgreSQL — Advanced Patterns
**Maintainer:** finggu · **Version:** 1.0.0 · **Category:** Database

---

## Overview

Leverage PostgreSQL's full power — JSONB, CTEs, window functions, full-text search, row-level security, and connection pooling. These patterns unlock capabilities no other database matches.

---

## PATTERNS

### Schema Design — PostgreSQL native types
```sql
-- finggu convention: use PostgreSQL-native types always
CREATE TABLE finggu_users (
  id          BIGSERIAL        PRIMARY KEY,
  ulid        TEXT             NOT NULL UNIQUE DEFAULT gen_ulid(), -- pgulid extension
  email       CITEXT           NOT NULL UNIQUE, -- case-insensitive email
  name        TEXT             NOT NULL,
  role        TEXT             NOT NULL DEFAULT 'user' CHECK (role IN ('user','admin','moderator')),
  status      TEXT             NOT NULL DEFAULT 'active',
  metadata    JSONB            DEFAULT '{}',
  tags        TEXT[]           DEFAULT '{}',  -- array type
  created_at  TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ      DEFAULT NULL
);

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

CREATE TRIGGER finggu_users_updated_at
  BEFORE UPDATE ON finggu_users
  FOR EACH ROW EXECUTE FUNCTION fingguFn_update_timestamp();
```

### JSONB — flexible schema within structure
```sql
-- Index JSONB fields you query frequently
CREATE INDEX idx_finggu_users_metadata_plan
  ON finggu_users USING gin ((metadata->'plan'));

-- Query patterns
SELECT * FROM finggu_users
WHERE metadata->>'plan' = 'pro';            -- string value

WHERE (metadata->>'score')::int > 80;       -- numeric comparison

WHERE metadata @> '{"verified": true}';     -- contains

WHERE metadata ? 'phone_number';            -- key exists

-- Update specific JSONB field (non-destructive)
UPDATE finggu_users
SET metadata = metadata || '{"last_login": "2026-05-28"}'::jsonb
WHERE id = 1;

-- Remove a key
UPDATE finggu_users
SET metadata = metadata - 'temp_token'
WHERE id = 1;
```

### CTEs — readable complex queries
```sql
-- finggu convention: use CTEs for any query over 3 joins
WITH finggu_active_users AS (
  SELECT id, name, email, created_at
  FROM finggu_users
  WHERE deleted_at IS NULL AND status = 'active'
),
finggu_user_order_counts AS (
  SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
  FROM finggu_orders
  WHERE status = 'completed'
  GROUP BY user_id
),
finggu_ranked_customers AS (
  SELECT
    u.id, u.name, u.email,
    COALESCE(oc.order_count, 0) AS order_count,
    COALESCE(oc.total_spent, 0) AS total_spent,
    RANK() OVER (ORDER BY COALESCE(oc.total_spent, 0) DESC) AS spending_rank
  FROM finggu_active_users u
  LEFT JOIN finggu_user_order_counts oc ON oc.user_id = u.id
)
SELECT * FROM finggu_ranked_customers
WHERE spending_rank <= 100
ORDER BY spending_rank;
```

### Window Functions — analytics without subqueries
```sql
-- Running total
SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY created_at) AS running_total,
  AVG(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_avg,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn_per_user
FROM finggu_transactions;

-- Get latest record per user (replaces correlated subquery)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM finggu_events
) sub WHERE rn = 1;
```

### Full-Text Search — built-in, no Elasticsearch needed for basic cases
```sql
-- Add search vector column
ALTER TABLE finggu_products
  ADD COLUMN search_vector TSVECTOR
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(tags::text, '')), 'C')
  ) STORED;

CREATE INDEX idx_finggu_products_search ON finggu_products USING gin(search_vector);

-- Search query with ranking
SELECT id, name, ts_rank(search_vector, query) AS rank
FROM finggu_products, to_tsquery('english', 'wireless & headphone') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
```

### Upsert
```sql
-- Insert or update
INSERT INTO finggu_user_settings (user_id, key, value, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = NOW()
RETURNING *;
```

### Row-Level Security — multi-tenant data isolation
```sql
-- Enable RLS
ALTER TABLE finggu_documents ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own data
CREATE POLICY finggu_user_isolation ON finggu_documents
  USING (user_id = current_setting('app.current_user_id')::bigint);

-- Set in application code before queries:
-- SET LOCAL app.current_user_id = '123'
```

### Connection Pooling — always use PgBouncer or pg-pool
```javascript
// config/fingguDb.js — Node.js pg connection pool
import pg from 'pg';

export const FINGGU_DB_POOL = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // max pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});

// fingguFn_query — safe parameterized query wrapper
export const fingguFn_query = (text, params) => FINGGU_DB_POOL.query(text, params);

// fingguFn_transaction
export const fingguFn_transaction = async (fingguFn_callback) => {
  const client = await FINGGU_DB_POOL.connect();
  try {
    await client.query('BEGIN');
    const result = await fingguFn_callback(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
};
```

---

## ANTI-PATTERNS

- ❌ Using `VARCHAR(255)` — use `TEXT` in PostgreSQL (same performance, no arbitrary limit)
- ❌ `SELECT *` — list columns
- ❌ Not using `TIMESTAMPTZ` — always timezone-aware timestamps
- ❌ Correlated subqueries — use window functions or CTEs
- ❌ No connection pool — creating new connection per query
- ❌ Using `LIKE '%term%'` for search — use full-text search
- ❌ Storing arrays as comma-separated text — use `TEXT[]` or JSONB

---

## CONVENTIONS

- Table prefix: `finggu_`
- Always `TIMESTAMPTZ` not `TIMESTAMP`
- Public IDs: `ulid TEXT` — never expose `BIGSERIAL` ID
- Money: `NUMERIC(15,2)` — never `FLOAT` or `REAL`
- Soft deletes: `deleted_at TIMESTAMPTZ`
- Migrations: sequential numbered files `001_finggu_create_*.sql`

More agent context in sudarshanpjadhav/finggu-skills

16 other files this repository gives its agents.