PostgreSQL Persistence

Production SQL persistence with JSONB, connection pooling, and serverless support

PostgreSQL persists agent conversations with JSONB metadata, connection pooling, and automatic serverless retry.

from praisonaiagents import Agent, db

agent = Agent(
    name="ProductionBot",
    instructions="You are a helpful assistant.",
    db=db(database_url="postgresql://user:pass@localhost:5432/praisonai"),
    session_id="prod-session",
)
agent.start("Hello — save this to PostgreSQL")

The user talks to production agents; PostgreSQL stores conversations with JSONB metadata.

Quick Start

1. Simple Usage

   pip install psycopg2-binary praisonai
   

   from praisonaiagents import Agent, db
   
   agent = Agent(
       name="ProductionBot",
       db=db(database_url="postgresql://user:pass@localhost:5432/praisonai"),
       session_id="session-1",
   )
   agent.start("Hello!")
   
2. With Configuration

   from praisonai.persistence.conversation.postgres import PostgresConversationStore
   
   store = PostgresConversationStore(
       url="postgresql://user:pass@localhost:5432/praisonai",
       schema="public",
       table_prefix="praison_",
       auto_create_tables=True,
       pool_size=10,
       max_retries=3,
       retry_delay=0.5,
   )
   

   When you pass structured credentials (PostgresDB(host=..., user=..., password=...)), special characters in user/password are URL-encoded automatically — you don’t need to quote_plus them yourself. This applies only to the structured constructor; if you pass a raw database_url= string, escape it yourself as before.

---

How It Works

Feature	Description
JSONB columns	Rich metadata queries on session data
Serverless retry	Auto SSL and retry for Neon, CockroachDB, Xata
Schema support	Isolate tables with a PostgreSQL schema

---

Init-Failure Handling

PostgreSQL initialization distinguishes transient outages from fatal misconfiguration so a typo never hides behind a cooldown window.

• Transient (memoized for the cooldown, retried after): ConnectionError, TimeoutError, OSError, and DBAPI operational errors matched by class name across the MRO (for example psycopg2.OperationalError, psycopg.errors.OperationalError) — no optional-dependency import needed.
• Fatal (raised immediately on every call): PermissionError, credential/typo/misconfiguration errors, ValueError/TypeError from bad config.
• KeyboardInterrupt / SystemExit / other BaseExceptions bypass the handler entirely and never poison the memoized state.

---

Configuration Options

Option	Type	Default	Description
url	str	None	Full connection URL (overrides individual options)
host	str	"localhost"	Database host
port	int	5432	Database port
database	str	"praisonai"	Database name
user	str	"postgres"	Database user
password	str	""	Database password
schema	str	"public"	PostgreSQL schema
table_prefix	str	"praison_"	Prefix for table names
auto_create_tables	bool	True	Create tables automatically
pool_size	int	5	Connection pool size
max_retries	int	3	Retries on connection errors (serverless cold-start)
retry_delay	float	0.5	Base delay between retries in seconds

URL formats

db(database_url="postgresql://user:pass@localhost:5432/praisonai")
db(database_url="postgresql://user:pass@host:5432/db?sslmode=require")

Aliases: neon, cockroachdb, crdb, xata resolve to the postgres backend. For async, use create_conversation_store("async_postgres", ...).

---

Best Practices

Use db() for most agents

Agent(db=db(database_url="postgresql://...")) is the simplest path — store wiring is automatic.

Enable SSL in production

Set sslmode=require in the URL for encrypted connections.

Use serverless aliases for Neon/Xata

Point at Neon or Xata URLs — SSL and retry are applied automatically.

Resume sessions after restart

Reuse the same session_id — conversation history loads on the next agent.start().

---

Related

SQLite Persistence

Local file database for development

Database Persistence

Compare all persistence backends