Spaces:
Runtime error
Runtime error
File size: 5,774 Bytes
330b6e4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | -- Performance optimization indexes for chat agent database
-- This migration adds indexes to improve query performance for common operations
-- Messages table indexes for performance optimization
-- Index for session-based queries (most common)
CREATE INDEX IF NOT EXISTS idx_messages_session_timestamp
ON messages(session_id, timestamp DESC);
-- Index for user message queries across sessions
CREATE INDEX IF NOT EXISTS idx_messages_session_role
ON messages(session_id, role);
-- Index for language-specific queries
CREATE INDEX IF NOT EXISTS idx_messages_language_timestamp
ON messages(language, timestamp DESC);
-- Composite index for recent message queries
CREATE INDEX IF NOT EXISTS idx_messages_session_recent
ON messages(session_id, timestamp DESC, role)
WHERE timestamp > NOW() - INTERVAL '7 days';
-- Index for full-text search on message content (PostgreSQL specific)
-- This will be created conditionally based on database type
DO $$
BEGIN
-- Check if we're using PostgreSQL
IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
-- Create GIN index for full-text search
CREATE INDEX IF NOT EXISTS idx_messages_content_gin
ON messages USING gin(content gin_trgm_ops);
ELSE
-- Create regular index for LIKE queries
CREATE INDEX IF NOT EXISTS idx_messages_content_text
ON messages(content);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Fallback to regular index if GIN is not available
CREATE INDEX IF NOT EXISTS idx_messages_content_text
ON messages(content);
END $$;
-- Chat sessions table indexes
-- Index for user session queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_user_active
ON chat_sessions(user_id, is_active, last_active DESC);
-- Index for session cleanup queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_last_active
ON chat_sessions(last_active)
WHERE is_active = true;
-- Index for language-based session queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_language
ON chat_sessions(language, created_at DESC);
-- Language contexts table indexes (if exists)
-- Index for session context lookups
CREATE INDEX IF NOT EXISTS idx_language_contexts_session
ON language_contexts(session_id, updated_at DESC);
-- Partial indexes for active sessions only (more efficient)
CREATE INDEX IF NOT EXISTS idx_chat_sessions_active_user
ON chat_sessions(user_id, last_active DESC)
WHERE is_active = true;
-- Index for message count aggregation
CREATE INDEX IF NOT EXISTS idx_messages_session_count
ON messages(session_id)
WHERE role IN ('user', 'assistant');
-- Performance optimization for timestamp range queries
CREATE INDEX IF NOT EXISTS idx_messages_timestamp_range
ON messages(timestamp)
WHERE timestamp > NOW() - INTERVAL '30 days';
-- Composite index for pagination queries
CREATE INDEX IF NOT EXISTS idx_messages_session_pagination
ON messages(session_id, id, timestamp DESC);
-- Add database-specific optimizations
DO $$
BEGIN
-- PostgreSQL specific optimizations
IF (SELECT version() LIKE '%PostgreSQL%') THEN
-- Enable auto-vacuum for better performance
ALTER TABLE messages SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE chat_sessions SET (autovacuum_vacuum_scale_factor = 0.1);
-- Set statistics target for better query planning
ALTER TABLE messages ALTER COLUMN session_id SET STATISTICS 1000;
ALTER TABLE messages ALTER COLUMN timestamp SET STATISTICS 1000;
ALTER TABLE chat_sessions ALTER COLUMN user_id SET STATISTICS 1000;
-- Create partial unique index for active sessions
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_sessions_unique_active
ON chat_sessions(user_id, language)
WHERE is_active = true;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Continue if PostgreSQL-specific features are not available
NULL;
END $$;
-- Add comments for documentation
COMMENT ON INDEX idx_messages_session_timestamp IS 'Primary index for session message queries ordered by timestamp';
COMMENT ON INDEX idx_messages_session_role IS 'Index for filtering messages by role within sessions';
COMMENT ON INDEX idx_messages_language_timestamp IS 'Index for language-specific message queries';
COMMENT ON INDEX idx_chat_sessions_user_active IS 'Index for user session queries with activity filter';
COMMENT ON INDEX idx_chat_sessions_last_active IS 'Index for session cleanup and maintenance queries';
-- Create function for index usage monitoring (PostgreSQL)
DO $$
BEGIN
IF (SELECT version() LIKE '%PostgreSQL%') THEN
CREATE OR REPLACE FUNCTION get_index_usage_stats()
RETURNS TABLE(
schemaname text,
tablename text,
indexname text,
idx_scan bigint,
idx_tup_read bigint,
idx_tup_fetch bigint
) AS $func$
BEGIN
RETURN QUERY
SELECT
s.schemaname::text,
s.relname::text,
s.indexrelname::text,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.schemaname = 'public'
AND (s.relname = 'messages' OR s.relname = 'chat_sessions' OR s.relname = 'language_contexts')
ORDER BY s.idx_scan DESC;
END;
$func$ LANGUAGE plpgsql;
COMMENT ON FUNCTION get_index_usage_stats() IS 'Function to monitor index usage statistics for performance tuning';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END $$; |