psycopg2 - PostgreSQL Python Adapter: AI-Optimized Technical Reference

Configuration

Installation Options

• psycopg2-binary: Quick development setup, production risk (Alpine Linux + musl libc = segfaults)
• psycopg2: Source compilation required, production-safe
• Dependencies: libpq-dev, python3-dev (Ubuntu), postgresql-devel (CentOS), postgresql (macOS Homebrew)

Production Settings

# Connection timeout prevents hanging connections
connect_timeout=10

# Server-side cursors for large result sets (>1000 rows)
cursor = conn.cursor(name='memory_safe_cursor')

# Connection pooling configuration
from psycopg2.pool import ThreadedConnectionPool
pool = ThreadedConnectionPool(minconn=1, maxconn=20)

Critical Database Settings

• Default PostgreSQL work_mem (4MB) causes connection kills with large queries
• statement_timeout kills long-running queries silently
• Connection limits: Use PgBouncer for >100 concurrent connections

Resource Requirements

Performance Characteristics

• Speed: C-library wrapper = fast execution
• Memory: Low overhead, but unbounded with large result sets
• Thread Safety: Connection-level safe, cursor-level unsafe
• Compatibility: Python 3.8-3.12 (3.13 broken as of late 2024)

Time Investment

• Basic setup: 30 minutes with psycopg2-binary
• Source compilation: 2-4 hours debugging dependencies
• Alpine Docker fix: 3+ hours dependency hell
• Migration to psycopg3: Non-trivial despite documentation claims

Expertise Requirements

• Basic usage: DB API 2.0 knowledge sufficient
• Production deployment: PostgreSQL administration skills required
• Performance tuning: Understanding of PostgreSQL internals essential
• Threading issues: Deep Python threading knowledge needed

Critical Warnings

Memory Destruction Scenarios

# DANGEROUS: Loads entire result set into memory
cursor.execute("SELECT * FROM large_table")  # 10GB+ = server death
all_rows = cursor.fetchall()

# SAFE: Process in chunks
cursor = conn.cursor(name='chunked')
cursor.execute("SELECT * FROM large_table")
while True:
    chunk = cursor.fetchmany(1000)
    if not chunk: break
    process(chunk)

Connection Failure Modes

• SSL SYSCALL error: Network timeouts, not psycopg2 bugs
• "Connection closed" with no context: PostgreSQL killed connection (check PostgreSQL logs, not Python)
• Random production failures: Idle connection timeouts from load balancers
• Transaction lock cascades: One hanging transaction blocks all new connections

Installation Hell Patterns

• Alpine Linux: psycopg2-binary segfaults with musl libc (use ubuntu:slim base image)
• Missing headers error: 90% chance missing libpq-dev/postgresql-devel
• Compilation failures: Wrong PostgreSQL library paths in PATH

Framework Integration Reality

Django Integration

• Default PostgreSQL backend since 2006
• Thread-safe at connection level
• Built-in connection pooling insufficient for >50 concurrent users

SQLAlchemy Patterns

• Engine-level pooling handles connection lifecycle
• Query logging: logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
• Connection pool exhaustion = cascade failures

Async Limitations

• psycopg2 async != modern async/await
• Real async requires psycopg3 or asyncpg
• LISTEN/NOTIFY works but limited scalability

Decision Criteria

psycopg2 vs Alternatives

Scenario	Recommendation	Reasoning
Existing Django app	psycopg2	Migration risk not worth benefits
New async-heavy app	psycopg3/asyncpg	Native async support required
Legacy Python 3.7	psycopg2	Only option with support
Alpine Docker	psycopg2 source	Binary package unreliable
High-throughput OLTP	psycopg3	2-3x performance improvement

When psycopg2 Breaks Down

• >1000 concurrent connections: Use PgBouncer
• >10GB result sets: Consider data export tools instead
• Sub-second query requirements: Profile PostgreSQL, not psycopg2
• Complex async workflows: Migrate to psycopg3

Production Failure Prevention

Monitoring Essentials

# Query logging for performance debugging
import logging
logging.getLogger('psycopg2').setLevel(logging.DEBUG)

# Connection health checks
def check_connection_health(conn):
    try:
        with conn.cursor() as cur:
            cur.execute('SELECT 1')
            return True
    except Exception:
        return False

Common Gotchas

• Cursor sharing between threads: Race conditions and data corruption
• Unclosed cursors: Memory leaks in long-running processes
• Transaction isolation: Default isolation level surprises developers
• COPY operations: Silent failures with malformed CSV data

Error Handling Patterns

# Proper error handling for connection issues
try:
    cursor.execute(query, params)
except psycopg2.OperationalError as e:
    if 'SSL SYSCALL' in str(e):
        # Network issue, retry with new connection
        reconnect()
    else:
        # Database-level problem
        log_error_and_alert(e)

Version Migration Strategy

psycopg2 Maintenance Mode

• Security updates only since 2021
• Python 3.13 support delayed indefinitely
• No new PostgreSQL features

psycopg3 Migration Considerations

• Breaking changes: Connection string format, import paths
• Performance gains: 2-3x faster in benchmarks
• Native async: Real async/await support
• Migration effort: 1-2 weeks for medium-sized applications

Support Timeline

• psycopg2: Maintenance until 2030+ (too much legacy code)
• psycopg3: Active development, recommended for new projects
• Community: Stack Overflow solutions often outdated, check GitHub issues first

Operational Intelligence

Team Knowledge Requirements

• Junior developers: Can use basic features safely
• Senior developers: Required for connection pooling and performance tuning
• DevOps: PostgreSQL administration skills mandatory
• On-call: Must understand PostgreSQL logs for debugging

Hidden Costs

• Alpine Docker builds: 20-minute compilation vs 2-minute binary download
• Memory profiling: Required tooling for large dataset applications
• Connection pool tuning: Dedicated performance testing environment needed
• Error monitoring: PostgreSQL log aggregation essential

Success Patterns

• Start with psycopg2-binary for development
• Compile from source for production deployment
• Implement connection health checks early
• Monitor PostgreSQL metrics, not just Python metrics
• Use server-side cursors by default for unknown query sizes