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.

python

from praisonaiagents import Agent, MemoryConfig
from praisonaiagents import db

agent = Agent(
    name="ProductionBot",
    instructions="You are a helpful assistant.",
    memory=MemoryConfig(
        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.

Diagram

Quick Start

Simple Usage

bash

pip install psycopg2-binary praisonai

python

from praisonaiagents import Agent, MemoryConfig
from praisonaiagents import db

agent = Agent(
    name="ProductionBot",
    memory=MemoryConfig(
        db=db(database_url="postgresql://user:pass@localhost:5432/praisonai"),
        session_id="session-1",
    ))
agent.start("Hello!")

With Configuration
python
```
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,
)
```
Tip
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

Diagram

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.

Diagram

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

python

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( memory=MemoryConfig( 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().

Cold-start bursts are safe with async_postgres

create_conversation_store("async_postgres", ...) guards pool creation with double-checked locking, so concurrent first-requests under FastAPI share one connection pool instead of building and leaking duplicates.

SQLite Persistence

Local file database for development

Database Persistence

Compare all persistence backends

PostgreSQL Persistence

Quick Start

How It Works

Init-Failure Handling

Configuration Options

URL formats

Best Practices

Related