postgresql-optimization

-- Advanced JSONB queries
CREATE TABLE events (
 id SERIAL PRIMARY KEY,
 data JSONB NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN index for JSONB performance
CREATE INDEX idx_events_data_gin ON events USING gin(data);

-- JSONB containment and path queries
SELECT * FROM events 
WHERE data @> '{"type": "login"}'
 AND data #>> '{user,role}' = 'admin';

-- JSONB aggregation
SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';

-- PostgreSQL arrays
CREATE TABLE posts (
 id SERIAL PRIMARY KEY,
 tags TEXT[],
 categories INTEGER[]
);

-- Array queries and operations
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
SELECT * FROM posts WHERE array_length(tags, 1) > 3;

-- Array aggregation
SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;

-- Advanced window functions
SELECT 
 product_id,
 sale_date,
 amount,
 -- Running totals
 SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
 -- Moving averages
 AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
 -- Rankings
 DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
 -- Lag/Lead for comparisons
 LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
FROM sales;

-- PostgreSQL full-text search
CREATE TABLE documents (
 id SERIAL PRIMARY KEY,
 title TEXT,
 content TEXT,
 search_vector tsvector
);

-- Update search vector
UPDATE documents 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- GIN index for search performance
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);

-- Search queries
SELECT * FROM documents 
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');

-- Ranking results
SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
FROM documents 
WHERE search_vector @@ plainto_tsquery('postgresql')
ORDER BY rank DESC;

-- EXPLAIN ANALYZE for performance analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'::date
GROUP BY u.id, u.name;

-- Identify slow queries from pg_stat_statements
SELECT query, calls, total_time, mean_time, rows,
 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';

-- Expression indexes for computed values
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- Covering indexes to avoid table lookups
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

-- Check connection usage
SELECT count(*) as connections, state 
FROM pg_stat_activity 
GROUP BY state;

-- Monitor memory usage
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

-- Create custom types
CREATE TYPE address_type AS (
 street TEXT,
 city TEXT,
 postal_code TEXT,
 country TEXT
);

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Use domains for data validation
CREATE DOMAIN email_address AS TEXT 
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Table using custom types
CREATE TABLE customers (
 id SERIAL PRIMARY KEY,
 email email_address NOT NULL,
 address address_type,
 status order_status DEFAULT 'pending'
);

-- PostgreSQL range types
CREATE TABLE reservations (
 id SERIAL PRIMARY KEY,
 room_id INTEGER,
 reservation_period tstzrange,
 price_range numrange
);

-- Range queries
SELECT * FROM reservations 
WHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');

-- Exclude overlapping ranges
ALTER TABLE reservations 
ADD CONSTRAINT no_overlap 
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);

-- PostgreSQL geometric types
CREATE TABLE locations (
 id SERIAL PRIMARY KEY,
 name TEXT,
 coordinates POINT,
 coverage CIRCLE,
 service_area POLYGON
);

-- Geometric queries
SELECT name FROM locations 
WHERE coordinates point(40.7128, -74.0060) 

- **Use EXPLAIN (ANALYZE, BUFFERS)** for detailed query analysis 

- **Configure postgresql.conf** for your workload (OLTP vs OLAP) 

- **Use connection pooling** (pgbouncer) for high-concurrency applications 

- **Regular VACUUM and ANALYZE** for optimal performance 

- **Partition large tables** using PostgreSQL 10+ declarative partitioning 

- **Use pg_stat_statements** for query performance monitoring

## 📊 Monitoring and Maintenance

### Query Performance Monitoring

### Database Maintenance

 

- **VACUUM and ANALYZE**: Regular maintenance for performance 

- **Index Maintenance**: Monitor and rebuild fragmented indexes 

- **Statistics Updates**: Keep query planner statistics current 

- **Log Analysis**: Regular review of PostgreSQL logs

## 🛠️ Common Query Patterns

### Pagination

### Aggregation

### JSON Queries

## 📋 Optimization Checklist

### Query Analysis

 

- Run EXPLAIN ANALYZE for expensive queries 

- Check for sequential scans on large tables 

- Verify appropriate join algorithms 

- Review WHERE clause selectivity 

- Analyze sort and aggregation operations 

### Index Strategy

 

- Create indexes for frequently queried columns 

- Use composite indexes for multi-column searches 

- Consider partial indexes for filtered queries 

- Remove unused or duplicate indexes 

- Monitor index bloat and fragmentation 

### Security Review

 

- Use parameterized queries exclusively 

- Implement proper access controls 

- Enable row-level security where needed 

- Audit sensitive data access 

- Use secure connection methods 

### Performance Monitoring

 

- Set up query performance monitoring 

- Configure appropriate log settings 

- Monitor connection pool usage 

- Track database growth and maintenance needs 

- Set up alerting for performance degradation

## 🎯 Optimization Output Format

### Query Analysis Results

CREATE INDEX idx_table_column ON table(column);

## 🚀 Advanced PostgreSQL Features

### Window Functions
```sql
-- Running totals and rankings
SELECT 
 product_id,
 order_date,
 amount,
 SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
 ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

-- Recursive queries for hierarchical data
WITH RECURSIVE category_tree AS (
 SELECT id, name, parent_id, 1 as level
 FROM categories 
 WHERE parent_id IS NULL
 
 UNION ALL
 
 SELECT c.id, c.name, c.parent_id, ct.level + 1
 FROM categories c
 JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;