ADR-0069: psycopg2-binary as Raptor's Postgres driver

Status: Accepted
Date: 2026-05-10 UTC
Epic: #1556 — Raptor SQLite → Postgres migration
Design doc: docs/architecture/raptor-postgres-migration/design.md §4.1

Context

Raptor (backend_v2) is migrating from sqlite3 to Heroku Postgres. A Python Postgres driver is required. The three realistic options are:

• psycopg2-binary — synchronous, bundles libpq, de-facto standard for Flask+SQLAlchemy
• psycopg (psycopg3) — newer synchronous + asyncio API, no bundled libpq
• asyncpg — asyncio-only, no SQLAlchemy 2.x sync dialect support

Raptor is a synchronous WSGI Flask app. Asyncio drivers are out of scope.

Decision

Use psycopg2-binary>=2.9.

Consequences

Positive:

• Bundles libpq — no system-level libpq-dev required on Heroku dynos or in CI. pip install psycopg2-binary is self-contained.
• SQLAlchemy's primary and most-tested dialect is postgresql+psycopg2. The Console app (raxx-console-*) already ships psycopg2-binary — consistent across the Raptor and Console dependency graphs.
• Large body of Stack Overflow answers, Alembic issues, and Flask-SQLAlchemy examples target this driver. Reduces onboarding friction.
• MIT licensed — compatible with TradeMasterAPI MIT license.

Negative:

• psycopg2-binary bundles a libpq copy that may lag the system libpq version. On Heroku this is not an issue (no system libpq to conflict with).
• The -binary variant is not recommended for production by the psycopg2 authors (they prefer compiling against the system libpq for security patch currency). However, Heroku's dyno OS does not guarantee a matching libpq-dev version, making the compiled variant impractical in this environment. This is an accepted trade-off.
• psycopg3 has a cleaner API and is the direction of future development. Migrating from psycopg2 to psycopg3 is a one-line SQLAlchemy URL change (postgresql+psycopg://) plus a driver swap; it is not a callsite change. This migration is deferred to post-v1.

Alternatives Considered

psycopg (psycopg3)

• Requires a C extension compile against system libpq (or psycopg[binary]).
• SQLAlchemy 2.x has a postgresql+psycopg dialect that is stable but has fewer production references than psycopg2.
• No architectural benefit for a synchronous Flask app. Deferred to post-v1.

asyncpg

• asyncio-only. Incompatible with synchronous Flask/SQLAlchemy 2.x sync usage. Rejected.