Supabase Postgres Best Practices

After reviewing your Supabase Postgres database for the 'Customer Insights Dashboard' project, I've identified several optimization opportunities:

1. **Index Optimization**: Your 'user_activity' table has a high write volume but lacks proper indexes. Adding a partial index on the 'last_active' column for active users (last_active > NOW() - INTERVAL '30 days') would significantly improve query performance for your active user analytics. Implementation:

```sql
CREATE INDEX idx_user_activity_active ON user_activity(last_active) WHERE last_active > NOW() - INTERVAL '30 days';
```

2. **Connection Pooling**: Your current connection pool settings are suboptimal for your traffic patterns. I recommend adjusting the pool settings to:

```sql
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '4GB';
```

3. **Query Optimization**: Several of your frequently run queries can be optimized. For example, the daily revenue calculation query can be improved by:

```sql
CREATE MATERIALIZED VIEW daily_revenue AS SELECT date_trunc('day', created_at) AS day, SUM(amount) AS total FROM transactions GROUP BY day;
```

4. **Storage Configuration**: Enable auto-vacuum for your large tables to maintain performance during heavy write operations:

```sql
ALTER TABLE large_data_table SET (autovacuum_enabled = true);
```

5. **Monitoring Setup**: Implement regular performance monitoring with these queries:

```sql
-- Slow queries
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- Lock contention
SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid;
```

These optimizations should significantly improve your database performance and reduce operational costs.