asyncpg and psycopg3, Deep Dive

Two PostgreSQL drivers, one asynchronous and one standard, and when the difference in speed is worth it.

Complete 17 notebooks 17 written

Before this guide: sqlite3, Deep Dive.

The notebooks

Open in Colab runs a notebook in your browser, and Read shows it on GitHub without running it. Every notebook runs on its own, and the answers to its exercises are in a separate notebook, linked as Solutions.

1	A Server of Your OwnThe second writer your SQLite file refused, and PostgreSQL running in your session in about two minutes	Open in Colab Read Solutions
2	Connecting and Executingexecute and fetchall, dict_row instead of tuples, and what the with block actually commits	Open in Colab Read Solutions
3	Transactions and ErrorsInFailedSqlTransaction, and the savepoint that saves the rest of the batch	Open in Colab Read Solutions
4	Placeholders and Identifiers%s against the f-string, executemany, and sql.Identifier for what no placeholder can carry	Open in Colab Read Solutions
5	Types and AdaptationEvery value becomes bytes on the wire, and three of your Python objects have no rule for it	Open in Colab Read Solutions
6	JSONBWrap the dict in Jsonb, query with @>, and read EXPLAIN before and after a GIN index	Open in Colab Read Solutions
7	Server-Side Cursorsexecute has already pulled every row into memory before fetchall runs, and a named cursor is how you stop it	Open in Colab Read Solutions
8	COPYBulk loading in both drivers, out as well as in, and the row with one column too many	Open in Colab Read Solutions
9	Pipeline ModeRound trips removed, and the PipelineAborted raised at a statement that was fine	Open in Colab Read Solutions
10	AsyncConnectionawait psycopg, and the two tasks that share one connection and take turns	Open in Colab Read Solutions
11	asyncpg$1 rather than %s, and every statement in autocommit until you open a transaction	Open in Colab Read Solutions
12	Prepared StatementsAnd the ALTER TABLE that invalidates a statement the server is still holding	Open in Colab Read Solutions
13	Connection Poolspsycopg_pool, create_pool, and why a pool sized to the database's cores beats one sized to your traffic	Open in Colab Read Solutions
14	Connecting to a Hosted ServerA DSN out of the environment, sslmode against asyncpg's ssl argument, and the connection limit a managed server enforces	Open in Colab Read Solutions
15	LISTEN and NOTIFYA push from the database, and the notification that never arrives without autocommit	Open in Colab Read Solutions
16	Which DriverWhere async wins, where it does not, and the missing await that fakes a win	Open in Colab Read Solutions
17	An Event StoreCOPY in, JSONB out, a pool in front, and one wake-up for a thousand rows	Open in Colab Read Solutions