File size: 2,889 Bytes
87553a7 |
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 |
import psycopg2
import os
from dotenv import load_dotenv
# Load env variables (optional, mostly for local dev)
load_dotenv()
# ================= CONFIGURATION (FILL THESE IN) =================
# 1. HOST: Use the "Transaction Pooler" Host (IPv4 compatible)
# Found in: Settings -> Database -> Connection Pooling
# Example: "aws-0-ap-south-1.pooler.supabase.com"
DB_HOST = "aws-1-ap-south-1.pooler.supabase.com"
# 2. USER: Use the "Transaction Pooler" User
# Found in: Settings -> Database -> Connection Pooling
# Example: "postgres.yourprojectid" (e.g., postgres.erecrmjorkafmqwspytb)
DB_USER = "postgres.erecrmjorkafmqwspytb"
# 3. PASSWORD: Your Database Password (same as before)
DB_PASS = "$Kanishka20"
# 4. PORT: MUST be 5432 (Do not change to 6543!)
# We use the pooler URL for connectivity, but Port 5432 to force "Session Mode"
# so we can run the SET commands below.
DB_PORT = 5432
# =================================================================
def build_index():
conn = None
try:
print(f"🔌 Connecting to {DB_HOST} on Port {DB_PORT}...")
conn = psycopg2.connect(
host=DB_HOST,
database="postgres",
user=DB_USER,
password=DB_PASS,
port=DB_PORT
)
conn.autocommit = True
cur = conn.cursor()
print("🚀 Connection successful!")
# 1. Disable Timeout (Prevents the 60-second crash)
print("⚙️ Step 1: Disabling timeouts...")
cur.execute("SET statement_timeout = 0;")
# 2. Boost Memory (Prevents the '65MB required' crash)
# We give it 150MB of RAM just for this session
print("⚙️ Step 2: Boosting memory to 150MB...")
cur.execute("SET maintenance_work_mem = '150MB';")
# 3. Clean up
print("🧹 Step 3: Cleaning up old indexes...")
cur.execute("DROP INDEX IF EXISTS child_vectors_embedding_idx;")
# 4. Build Index
print("🏗️ Step 4: Building IVFFlat Index (lists=100)...")
print(" (This will take 1-3 minutes. Please wait...)")
# 'lists=100' is the sweet spot for ~80,000 vectors
cur.execute("""
CREATE INDEX child_vectors_embedding_idx
ON child_vectors
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
""")
print("✅ SUCCESS! Index built. Your backend should now be instant.")
except Exception as e:
print(f"\n❌ ERROR: {e}")
print("Tip: Double check you copied the 'Pooler' Host and User correctly from Supabase Settings.")
finally:
if conn:
conn.close()
print("🔌 Connection closed.")
if __name__ == "__main__":
build_index() |