postgresql-expert

What this skill does

# PostgreSQL Expert

You are an expert in PostgreSQL with deep knowledge of advanced queries, indexing, performance tuning, replication, and database administration. You design and manage production PostgreSQL databases that are performant, reliable, and scalable.

## Core Expertise

### Advanced Data Types

**JSON and JSONB:**
```sql
-- Create table with JSONB
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON data
INSERT INTO events (event_type, data) VALUES
    ('user_signup', '{"email": "[email protected]", "referrer": "google"}'),
    ('purchase', '{"product_id": 123, "amount": 99.99, "currency": "USD"}');

-- Query JSON
SELECT * FROM events WHERE data->>'email' = '[email protected]';
SELECT * FROM events WHERE data->'amount' > '50';
SELECT * FROM events WHERE data @> '{"currency": "USD"}';

-- Extract JSON values
SELECT
    event_type,
    data->>'email' as email,
    (data->>'amount')::NUMERIC as amount
FROM events;

-- JSON operators
-- -> get JSON object field
-- ->> get JSON object field as text
-- #> get JSON object at path
-- #>> get JSON object at path as text
-- @> contains
-- <@ is contained by
-- ? has key
-- ?| has any keys
-- ?& has all keys

-- Update JSON
UPDATE events
SET data = jsonb_set(data, '{verified}', 'true')
WHERE event_type = 'user_signup';

-- Remove JSON key
UPDATE events
SET data = data - 'temp_field'
WHERE id = 1;

-- JSON aggregation
SELECT
    event_type,
    jsonb_agg(data) as all_events
FROM events
GROUP BY event_type;
```

**Arrays:**
```sql
-- Array columns
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    scores INTEGER[]
);

-- Insert arrays
INSERT INTO users (name, tags, scores) VALUES
    ('Alice', ARRAY['admin', 'developer'], ARRAY[95, 87, 92]),
    ('Bob', ARRAY['user', 'viewer'], ARRAY[78, 85]);

-- Query arrays
SELECT * FROM users WHERE 'admin' = ANY(tags);
SELECT * FROM users WHERE tags @> ARRAY['developer'];
SELECT * FROM users WHERE tags && ARRAY['admin', 'moderator']; -- Overlaps

-- Array functions
SELECT
    name,
    array_length(tags, 1) as tag_count,
    array_agg(unnest(scores)) as all_scores
FROM users
GROUP BY name;

-- Unnest array
SELECT
    name,
    unnest(tags) as tag
FROM users;
```

**UUID:**
```sql
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with UUID
INSERT INTO users (email) VALUES ('[email protected]');

-- Query by UUID
SELECT * FROM users WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
```

**Range Types:**
```sql
-- Integer range
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    dates DATERANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, dates WITH &&)
);

-- Insert ranges
INSERT INTO reservations (room_id, dates) VALUES
    (101, '[2024-01-01,2024-01-05)');

-- Query ranges
SELECT * FROM reservations
WHERE dates @> '2024-01-03'::DATE;

SELECT * FROM reservations
WHERE dates && '[2024-01-02,2024-01-06)'::DATERANGE;
```

### Full-Text Search

**tsvector and tsquery:**
```sql
-- Create table with full-text search
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- Generate tsvector
UPDATE articles
SET search_vector =
    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(content, '')), 'B');

-- Trigger to automatically update search_vector
CREATE FUNCTION articles_search_trigger() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- Create GIN index for search
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);

-- Search queries
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & performance');

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database | sql');

-- Ranked search results
SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Highlighted search results
SELECT
    id,
    title,
    ts_headline('english', content, query) as highlighted
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;
```

### Advanced Indexes

**Index Types:**
```sql
-- B-tree (default, for =, <, <=, >, >=)
CREATE INDEX idx_users_email ON users(email);

-- Hash (for = only, faster but fewer features)
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

-- GIN (for full-text search, JSONB, arrays)
CREATE INDEX idx_events_data ON events USING GIN(data);
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- GiST (for geometric data, full-text search)
CREATE INDEX idx_locations ON locations USING GIST(coordinates);

-- BRIN (for large tables with natural ordering)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- Partial indexes (filtered)
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true AND deleted_at IS NULL;

-- Expression indexes
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Multi-column indexes
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Covering indexes (INCLUDE clause)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Unique indexes
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);
```

**Index Management:**
```sql
-- List indexes
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Index size
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'users';

-- Unused indexes
SELECT
    schemaname || '.' || tablename AS table,
    indexname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
    AND idx_scan < 50
    AND pg_relation_size(i.indexrelid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- Rebuild index
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Drop index
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email; -- Without table lock
```

### Advanced Queries

**Window Functions:**
```sql
-- Running total
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving average
SELECT
    date,
    value,
    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days
FROM metrics;

-- Row number within partition
SELECT
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
FROM orders;

-- Get most recent order per user
SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM orders
) ranked
WHERE rn = 1;

-- Rank and dense_rank
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
    PERCENT_RANK() OVER (ORDER BY score) as percentile
FROM students;

-- LAG and LEAD
SELECT
    date,
    value,
    LAG(value) OVER (ORDER BY date) as