> ## Documentation Index
> Fetch the complete documentation index at: https://docs.praison.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

```mermaid theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
graph LR
    subgraph "PostgreSQL Persistence"
        A[🧠 Agent] --> B[💬 Conversations]
        B --> C[🐘 PostgreSQL]
        C --> D[🌐 Network]
        A --> E[🔍 Advanced Queries]
        E --> C
        C --> F[📊 Analytics]
    end
    
    classDef agent fill:#8B0000,stroke:#7C90A0,color:#fff
    classDef data fill:#189AB4,stroke:#7C90A0,color:#fff
    classDef storage fill:#10B981,stroke:#7C90A0,color:#fff
    
    class A agent
    class B,E data
    class C,D,F storage
```

## Quick Start

<Steps>
  <Step title="Basic Setup">
    ```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
    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
    ```
  </Step>

  <Step title="With Connection Pooling">
    ```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
    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")
    ```
  </Step>
</Steps>

***

## How It Works

```mermaid theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
sequenceDiagram
    participant Agent
    participant PostgresDB as PostgreSQL Database
    participant Connection as Connection Pool
    participant Tables as Database Tables
    
    Agent->>PostgresDB: Initialize connection
    PostgresDB->>Connection: Create connection pool
    Agent->>PostgresDB: Save message
    PostgresDB->>Connection: Get connection
    Connection->>Tables: INSERT INTO messages
    Agent->>PostgresDB: Query history
    PostgresDB->>Connection: Get connection
    Connection->>Tables: SELECT FROM conversations
    Tables-->>Connection: Return data
    Connection-->>PostgresDB: Results
    PostgresDB-->>Agent: Conversation history
```

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

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
# 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

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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:

```bash theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
# 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:

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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:

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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:

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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:

```python theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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

<AccordionGroup>
  <Accordion title="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
  </Accordion>

  <Accordion title="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
  </Accordion>

  <Accordion title="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
  </Accordion>

  <Accordion title="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
  </Accordion>
</AccordionGroup>

***

## Related

<CardGroup cols={2}>
  <Card title="MySQL Persistence" icon="database" href="/docs/features/persistence-mysql">
    Alternative SQL database option with different strengths
  </Card>

  <Card title="Database Persistence Overview" icon="database" href="/docs/features/persistence">
    Compare all available persistence backends
  </Card>
</CardGroup>