PostgreSQL Persistence

Production-grade SQL database persistence with advanced features and scalability

PostgreSQL provides enterprise-grade SQL database persistence with advanced features like JSONB storage, full-text search, and horizontal scaling for production applications.

Quick Start

1. Basic Setup

   from praisonaiagents import Agent, db
   
   agent = Agent(
       name="ProductionBot",
       instructions="You are a helpful assistant.",
       db=db(database_url="postgresql://username:password@localhost:5432/praisonai"),
       session_id="prod-session"
   )
   
   response = agent.chat("Hello! This is saved to PostgreSQL.")
   print(response)  # Conversation persisted to PostgreSQL
   
2. With Connection Pooling

   from praisonaiagents import Agent, db
   
   # Production configuration with connection pooling
   agent = Agent(
       name="ScalableBot",
       instructions="You are a helpful assistant.",
       db=db(database_url="postgresql://user:pass@localhost:5432/mydb?pool_size=10"),
       session_id="scalable-session"
   )
   
   agent.chat("This uses connection pooling for better performance")
   

---

How It Works

PostgreSQL stores data in optimized tables with advanced features:

Table	Features	Benefits
conversations	JSONB metadata, indexes	Fast metadata queries
messages	Full-text search, timestamps	Searchable conversation history
runs	Execution tracking, performance stats	Agent monitoring
tool_calls	JSON tool parameters	Rich tool usage analytics

---

Configuration Options

Database URL Formats

# Basic connection
db(database_url="postgresql://user:password@localhost:5432/database")

# With SSL (production recommended)
db(database_url="postgresql://user:pass@host:5432/db?sslmode=require")

# Connection pooling
db(database_url="postgresql://user:pass@host:5432/db?pool_size=20&max_overflow=5")

# Read replica configuration
db(database_url="postgresql://user:pass@primary:5432/db")

Advanced PostgreSQL Configuration

from praisonaiagents import Agent, db

# Custom PostgreSQL database with specific options
postgres_db = db.PostgresDB(
    host="localhost",
    port=5432,
    user="praisonai_user", 
    password="secure_password",
    database="praisonai_production",
    # Connection pool settings
    pool_size=10,
    max_overflow=20,
    # SSL settings for production
    sslmode="require"
)

agent = Agent(
    name="EnterpriseBot",
    instructions="You are a production assistant.",
    db=postgres_db,
    session_id="enterprise-session"
)

---

Docker Setup

Quick PostgreSQL setup with Docker:

# Start PostgreSQL container
docker run -d \
    --name praisonai-postgres \
    -e POSTGRES_DB=praisonai \
    -e POSTGRES_USER=praisonai_user \
    -e POSTGRES_PASSWORD=your_password \
    -p 5432:5432 \
    postgres:15

# Connect and verify
docker exec -it praisonai-postgres psql -U praisonai_user -d praisonai

Then use in your agent:

from praisonaiagents import Agent, db

agent = Agent(
    name="DockerBot",
    db=db(database_url="postgresql://praisonai_user:your_password@localhost:5432/praisonai"),
    session_id="docker-session"
)

---

Advanced Queries and Analytics

PostgreSQL enables powerful analytics on conversation data:

import psycopg2
from praisonaiagents import Agent, db

# Create agent and have conversations
agent = Agent(
    name="AnalyticsBot",
    db=db(database_url="postgresql://user:pass@localhost:5432/analytics"),
    session_id="analytics-session"
)

# Have some conversations
agent.chat("Help me analyze customer feedback")
agent.chat("What are the trending topics this month?")

# Direct database access for analytics
conn = psycopg2.connect(
    host="localhost",
    database="analytics", 
    user="user",
    password="pass"
)
cursor = conn.cursor()

# Advanced analytics queries
cursor.execute("""
    SELECT 
        DATE_TRUNC('hour', created_at) as hour,
        COUNT(*) as message_count,
        COUNT(DISTINCT session_id) as unique_sessions
    FROM messages 
    WHERE created_at > NOW() - INTERVAL '24 hours'
    GROUP BY hour
    ORDER BY hour;
""")

analytics = cursor.fetchall()
for hour, msg_count, sessions in analytics:
    print(f"{hour}: {msg_count} messages, {sessions} sessions")

# Full-text search across conversations
cursor.execute("""
    SELECT session_id, content, created_at
    FROM messages
    WHERE content ILIKE %s
    ORDER BY created_at DESC
    LIMIT 10;
""", ("%customer feedback%",))

results = cursor.fetchall()
conn.close()

---

High Availability Setup

For production deployments with high availability:

import os
from praisonaiagents import Agent, db

# Primary database with read replica
primary_db_url = os.getenv("PRIMARY_DATABASE_URL")
replica_db_url = os.getenv("REPLICA_DATABASE_URL")

# Write to primary, read from replica when possible
agent = Agent(
    name="HABot",
    db=db(
        database_url=primary_db_url,
        # Some implementations support read replicas
        read_replica_url=replica_db_url
    ),
    session_id="ha-session"
)

---

Migration from SQLite

Migrate existing SQLite data to PostgreSQL:

import sqlite3
import psycopg2
from praisonaiagents import db

# Export from SQLite
sqlite_conn = sqlite3.connect("old_conversations.db") 
sqlite_cursor = sqlite_conn.cursor()

sqlite_cursor.execute("SELECT session_id, role, content, created_at FROM messages")
messages = sqlite_cursor.fetchall()

# Import to PostgreSQL  
postgres_conn = psycopg2.connect("postgresql://user:pass@localhost:5432/new_db")
postgres_cursor = postgres_conn.cursor()

for session_id, role, content, created_at in messages:
    postgres_cursor.execute("""
        INSERT INTO messages (session_id, role, content, created_at)
        VALUES (%s, %s, %s, %s)
    """, (session_id, role, content, created_at))

postgres_conn.commit()
postgres_conn.close()
sqlite_conn.close()

print("Migration completed!")

---

Best Practices

Connection Management

• Use connection pooling in production (pool_size=10-20)
• Configure appropriate timeouts for your use case
• Monitor connection pool metrics
• Use persistent connections for long-running applications

Performance Optimization

• Create indexes on frequently queried columns (session_id, created_at)
• Use EXPLAIN ANALYZE to optimize slow queries
• Consider partitioning large tables by date
• Use read replicas for analytics workloads

Security

• Always use SSL connections in production (sslmode=require)
• Use environment variables for credentials
• Implement database user permissions (read-only for analytics)
• Regular security updates and patches

Backup and Recovery

• Set up automated daily backups with pg_dump
• Test backup restoration procedures regularly
• Use point-in-time recovery for critical applications
• Consider cross-region backup replication

---

Related

MySQL Persistence

Alternative SQL database option with different strengths

Database Persistence Overview

Compare all available persistence backends