psycopg2 - The PostgreSQL Adapter Everyone Actually Uses

If you're doing PostgreSQL with Python, you're almost certainly using psycopg2. It's been around since 2006, which in Python years makes it ancient, but in a good way. Federico Di Gregorio and Daniele Varrazzo built something that actually works and keeps working.

The Technical Reality

psycopg2 is basically a Python wrapper around PostgreSQL's C library (libpq). This means it's fast and doesn't do stupid things with your data, unlike pymongo or sqlite3 which are pure Python.

Thread Safety That Actually Works: You can share connections between threads without everything exploding. Don't share cursors though - that way lies madness and race conditions that will haunt your production logs. The connection-level locking means you can have multiple threads hitting the same connection without psycopg2 shitting the bed. The Python threading documentation explains the underlying concepts, and there's a good Stack Overflow discussion about database threading patterns.

Memory That Doesn't Leak: The C implementation means large result sets won't eat your server's RAM. Use server-side cursors for big queries or you'll learn about Linux's OOM killer the hard way. Watched a startup kill their prod database at 2am because they SELECT * FROM users without LIMIT and crashed the server with 10GB of user data loaded into memory. Took down their entire platform for 6 hours while they figured out why everything was fucked. The PostgreSQL memory usage docs explain the server-side implications, and here's a detailed blog post about cursor patterns that saved my bacon once.

SQL Injection Protection: Use parameterized queries or you WILL get owned. psycopg2 handles escaping properly, but it can't fix stupid. Saw a company lose 3 days worth of user uploads because they forgot to parameterize one DELETE query in an admin script. "DELETE FROM files WHERE user_id = " + user_input + ";" - yeah, that ended exactly how you'd expect when someone typed "1 OR 1=1" into the form. The OWASP SQL Injection Guide covers the attack vectors, psycopg2's query composition docs explain the right way to do it, and here's a terrifying collection of real SQL injection examples if you need motivation to fix your code.

Real-World Usage

Django uses psycopg2 as its default PostgreSQL backend, and SQLAlchemy relies on it too. This isn't marketing fluff - it means the combination has been battle-tested in production at scale. Flask-SQLAlchemy builds on this foundation, FastAPI works great with it, and Celery task queues use it for persistence. When you're debugging at 3am, you want boring, reliable tools. Here's Instagram's engineering blog talking about their PostgreSQL usage at scale.

Version 2.9.10 came out October 2024 and supports Python 3.8 through 3.12 (3.13 support is still broken as of late 2024). It handles pretty much every PostgreSQL feature you'll actually use - or at least I haven't hit any missing ones yet. The maintainers put new development into psycopg3, but psycopg2 isn't going anywhere - too much production code depends on it.

DB API 2.0 Compliance (AKA Why You Can Swap Adapters)

psycopg2 follows the Python DB API 2.0 spec, which means if you know one Python database adapter, you basically know them all. Mostly. This is great for testing - you can swap in SQLite for quick tests and PostgreSQL for real work.

Connection Pools: The psycopg2.pool module handles connection pooling, but connection pools seem simple until you have 500 connections all waiting for locks and your database goes read-only at 2am on Friday. Watched an entire startup go down because some genius left a transaction open in a background analytics script that ran for 14 hours. Every new connection hung waiting for the lock, users couldn't log in, and it took 4 hours of debugging to find the one fucking script that was holding everything hostage. In production, you'll probably want something like PgBouncer between your app and PostgreSQL anyway. The PostgreSQL connection limits documentation explains the server-side constraints, and this connection pooling deep dive covers the patterns that actually work in production. SQLAlchemy's engine documentation also has solid pooling advice.

Server-Side Cursors: These things save your server's memory but add complexity. Use them for big result sets only - the overhead isn't worth it for small queries. And yes, you can name them, which is useful for debugging when cursors start timing out.

PostgreSQL Features (The Ones You'll Use)

Data Types That Don't Suck: PostgreSQL arrays become Python lists, JSON/JSONB fields work with Python dicts, and UUIDs just work. The type system is actually pretty solid once you stop trying to fight it.

COPY Operations: These are blazing fast for bulk data import/export, but the syntax is annoying and error-prone. When you need to move a million rows, though, COPY TO/FROM beats INSERT statements by a lot. Just be prepared to spend 2 hours debugging CSV escaping issues that make no sense. I've seen COPY operations fail silently because of one screwed up character buried somewhere in the middle of the dataset. The PostgreSQL COPY documentation covers all the edge cases, and here's a comprehensive COPY performance guide that explains when to use it. This Stack Overflow thread has saved me multiple times with CSV encoding issues.

Async Support: Not the modern async/await kind - psycopg2 predates that. It has old-school async connections and LISTEN/NOTIFY for real-time updates. If you want real async, use psycopg3 or asyncpg.

Installation Hell (Or How to Avoid It)

The eternal question: psycopg2 or psycopg2-binary?

pip install psycopg2-binary  # Just works (until production)
pip install psycopg2         # Compile it yourself (prepare for pain)

psycopg2-binary is fine for development but you might hit weird issues in production. Alpine Linux users know the pain - the binary package breaks on Alpine because of musl libc in ways that violate the Geneva Convention. Alpine's musl libc makes psycopg2-binary explode with cryptic segfaults that happen randomly in production when you're trying to demo for investors. Since Docker became mandatory around 2018, everyone hits the Alpine glibc issues at least once.

psycopg2 (source) means compiling against system PostgreSQL libraries. Prepare for "PostgreSQL development headers not found" errors. On Ubuntu: apt-get install libpq-dev python3-dev. On macOS with Homebrew: brew install postgresql. On CentOS/RHEL: yum install postgresql-devel.

If compilation fails, it's usually missing PostgreSQL dev headers or the compiler can't find them. Check your PATH, check your library paths - honestly it's always one of those two things.