High-Performance Python Async

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 1 of 17. What if you could double the performance of your Python async code without rewriting a single function? That is the exact promise of the technology we are looking at today: uvloop Architecture. When developers talk about standard Python async, they tend to mix up two distinct layers. The first layer is the application programming interface. This includes the async and await keywords you type, the futures, and the tasks. The second layer is the event loop itself. The event loop is the internal scheduler that monitors sockets, manages network connections, and decides which task runs next. Standard Python provides both the interface and a default event loop written in pure Python. Here is the key insight. The interface and the event loop are decoupled. Python allows you to swap out the underlying scheduler without changing the syntax you write. Think of it like a car. The async API is the steering wheel, the pedals, and the dashboard. You interact with those directly. The event loop is the engine under the hood. Swapping the default Python event loop for uvloop is like dropping a V8 engine into your vehicle. You still steer and brake exactly the same way, but the car moves significantly faster. The core of uvloop relies on two architectural choices to achieve this speed. First, it is a drop-in replacement written entirely in Cython. Cython compiles Python-like code down to highly optimized C extensions. This eliminates the overhead of the standard Python interpreter when executing the hot paths of the scheduler. Pure Python event loops spend a lot of time creating internal objects and managing interpreter state just to handle routine network events. Cython strips that away. Every time the loop checks a socket or wakes up a task, it executes native C code instead of routing through pure Python. Second, uvloop delegates the actual operating system interactions to a C library called libuv. If that name sounds familiar, it is because libuv is the asynchronous I/O engine that powers Node.js. It is battle-tested, highly tuned for network-heavy workloads, and handles all the complex cross-platform details of asynchronous networking. By wrapping libuv in a tight Cython shell, uvloop brings that exact same performance profile directly into Python. The architectural result is massive. By bypassing the pure Python scheduler and relying on a compiled C engine, uvloop makes your asyncio applications at least twice as fast. In many benchmark scenarios involving high connection concurrency, it allows Python to rival the performance of compiled languages like Go. You get the developer velocity of Python with the raw execution speed of native C networking. The transition requires zero changes to your business logic, your database queries, or your API endpoints. The fundamental takeaway here is that performance bottlenecks in standard Python async are rarely about the language syntax, but rather the execution engine, and replacing that engine gives you native C speeds while preserving your existing Python abstractions. If you want to help support the show, you can search for DevStoriesEU on Patreon. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 2 of 17. The most powerful performance optimization in your asynchronous Python application requires no architecture changes, no refactoring, and no complex configuration. It is exactly two lines of code long. Today, we are talking about dropping in uvloop. The standard library asyncio event loop is written in pure Python. Uvloop is a drop-in replacement built on top of libuv, the same engine that powers other high-concurrency runtimes. It makes your standard Python async code execute significantly faster. Replacing the core scheduling mechanism of your application requires telling Python to abandon its default behavior before it starts doing any actual work. In a web server entry point script, like the main file for a FastAPI or aiohttp application, you implement this replacement using an event loop policy. An event loop policy is a global configuration object inside the standard asyncio module. It dictates what kind of event loop gets instantiated whenever the application requests a new one. To swap the loop, you import the asyncio module and the uvloop module. Then, you call the set event loop policy function on the asyncio module. You pass it a fresh instance of the event loop policy provided by the uvloop module. Here is the key insight. You must set this policy early. The call needs to be at the absolute top of your main execution script, right after your imports. The event loop policy only affects the creation of new loops. If you wait to set the policy until after your web framework has already started, or after an asynchronous database driver has initialized, the standard pure Python loop is likely already running. Changing the policy at that point does nothing to the existing loop. Your code will either ignore uvloop entirely, or end up with mixed event loops that cause deadlocks and broken connections. There is an alternative to the policy approach. Instead of changing the global rules for loop creation, you can explicitly create a single uvloop instance. You do this by calling the new event loop function directly from the uvloop module. Once you have that loop object in memory, you hand it over to asyncio by calling the set event loop function. Why would you choose one approach over the other? Setting the event loop policy is a global override. It guarantees that any third-party library, background task, or framework component in your process that asks asyncio for a new loop will safely receive a uvloop. It is the standard choice for a web server where you want uniform performance across the entire application stack. The explicit new event loop approach is narrow. It injects a specific instance rather than changing the factory rules. You use this explicit method when you are managing complex environments with multiple threads, or when you need strict control over exactly which loop is running in an isolated context without mutating the global process state. For standard web applications, the policy override is all you need. The exact mechanism you choose to drop in uvloop matters less than the timing of when you apply it. The event loop policy dictates the foundation of your entire async architecture, so it must be the very first instruction your application executes before any asynchronous context is ever established. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 3 of 17. You optimize your indexes, upgrade your network, and tune your queries, but your application is still burning CPU cycles on database communication. The biggest bottleneck in your database queries often is not network latency. It is the time your application spends parsing text. To eliminate this overhead, we are introducing asyncpg and its implementation of the PostgreSQL binary protocol. It is a common misconception that asyncpg is just an asynchronous wrapper around psycopg2. It is not. It is also not an adaptation of the standard Python DB-API. The DB-API specification inherently guides database drivers toward certain standardized data handling patterns. asyncpg ignores this specification entirely. It is a ground-up rewrite designed exclusively for asyncio and PostgreSQL, bypassing standard database interfaces to talk to Postgres on its own terms. To understand why this design matters, look at how traditional drivers handle data transfer. Most database drivers talk to PostgreSQL using a text-based format. When you query the database for a number, a timestamp, or a complex array, the database takes its internal memory representation of that data and converts it into a string. It then sends that string over the network. When your Python application receives it, the driver has to parse that text string back into a Python integer, datetime object, or list. Think of this traditional approach like a team relying on a translator for every internal conversation. The database reads its native data structures, writes them out as standardized text documents, and sends them across the wire. Your Python application receives these documents and painstakingly translates the text back into its own structured memory objects. All this encoding, stringifying, and parsing burns CPU time and consumes memory. asyncpg solves this problem by speaking the PostgreSQL frontend and backend binary protocol directly. It forces the database to use binary input and output exclusively. Instead of relying on a translator, the database and the driver speak the exact same native language. If you query for a sixty-four-bit integer, PostgreSQL sends the raw bytes representing that integer. asyncpg reads those bytes directly into a Python integer object. There is no string formatting. There is no text parsing. This native understanding extends to complex data. When you request a JSON block, a universally unique identifier, or a geometric data type, the binary protocol ensures the payload remains compact and strictly structured. The driver knows exactly how many bytes to read for each column without ever scanning for text delimiters. Here is the key insight. The speed of asyncpg does not primarily come from the non-blocking nature of Python asyncio. The massive performance gains come from stripping out the text translation layer entirely. You are doing significantly less work per row returned. By strictly enforcing binary data transfer, your application stops wasting resources reading text and spends that CPU time executing your actual business logic. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 4 of 17. If you are used to Python standard database drivers, the way you pass variables into queries is going to catch you off guard, and your code will immediately break. Standard Python drivers rely on string formatting markers, but this library speaks directly to the database engine. Today we cover Connecting and Basic Execution. To start talking to your database, you use the connect function from asyncpg. You await this function and pass it a Data Source Name, which is a standard Postgres connection URI. This looks exactly like a web address. It starts with postgresql colon slash slash, followed by your username, a colon, your password, an at symbol, the host address, and finally a slash with the database name. Awaiting this function establishes the network link and gives you an active connection object. Now you want to insert a user name and date of birth into a table. This is where the query syntax changes. Do not use percent s or question marks for your query parameters. Because asyncpg deliberately bypasses the standard Python database API, it forces you to use native Postgres placeholders. You write dollar sign one, dollar sign two, and so on. Your query string will look like a standard insert statement, but the values will be dollar one and dollar two. To run this query without asking for data back, you await the execute method on your connection object. You pass the query string first, followed by the actual variables for the name and the date of birth. The execute method runs the statement and discards any tabular data. It simply returns a status string from Postgres, something like insert zero one. It does not return the actual database rows. If you need the database to generate a unique ID for this new user, and you need that ID back in Python, execute is the wrong tool. You change your SQL query to add a returning id clause at the end. Because you now expect data back, you use the fetchval method. The fetchval method runs the query and returns exactly one specific value. It looks at the first row returned, grabs the first column, and gives you just that piece of data. This is perfect for grabbing a newly generated user ID. If you need more than just the ID, perhaps you want the database defaults for several columns, you use fetchrow instead. Awaiting fetchrow returns a single record object containing all the columns from that first row. You can access the data inside this record exactly like a Python dictionary, using the column names as keys. When you are done inserting your data, you must await the close method on the connection object to clean up the network socket and release the database resources. Here is the key insight. Forcing you to use native dollar sign placeholders is not just a stylistic quirk. It allows asyncpg to bypass client side string interpolation entirely, mapping Python types directly to Postgres binary formats for maximum speed and complete protection against SQL injection. Thanks for listening, happy coding everyone!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 5 of 17. You write a raw SQL query, execute it, and brace yourself to manually parse date strings, split comma-separated arrays, and cast currency values. But you do not have to. You do not need an ORM to get fully typed Python objects out of your database. Asyncpg handles this automatically through native type conversion. When asyncpg communicates with PostgreSQL, it uses the database binary protocol. It knows exactly what data type each column represents. Instead of handing you raw text strings that require secondary parsing in Python, asyncpg translates PostgreSQL types directly into standard Python library objects. This translation happens automatically in both directions. When you pass a Python object as a query parameter, asyncpg encodes it into the correct PostgreSQL binary format. When the database responds, asyncpg decodes the binary data back into the corresponding Python type. Consider a scenario where you fetch a user profile from your database. Your SQL query asks for a username, an array of user tags, the account creation time, and the last known IP address. In many database drivers, you would get back strings that you have to parse manually. With asyncpg, the result record is already typed. The username is a standard Python string. The tags column, which is an array in PostgreSQL, arrives as a native Python list of strings. The creation time is a standard datetime object. The IP address, stored as an inet type in the database, maps directly to Python built-in ipaddress objects. You write zero parsing logic to achieve this. There is a strict mapping for numbers that catches some developers off guard. If your PostgreSQL column is defined as numeric, it does not convert into a Python float. Asyncpg maps the PostgreSQL numeric type directly to the Python decimal dot Decimal class. This preserves exact precision. If you are querying financial records or precise measurements, you will not lose data to floating-point rounding errors. Standard floating-point types in Postgres, like real or double precision, do map to Python floats. Here is the key insight for other specific types. If you select a native UUID column, you receive a Python uuid dot UUID object, not a generic string representation. Dates become datetime dot date objects. Postgres intervals map perfectly to Python timedeltas. Binary data stored in a bytea column is converted directly into Python bytes. JSON and JSONB columns behave slightly differently. Asyncpg converts JSON and JSONB data into standard Python strings by default. It does not automatically parse them into Python dictionaries. You receive the raw string, which you can then pass to the standard Python json module if you need to manipulate the nested data. Relying on this binary type translation keeps your application logic clean and shifts the burden of type safety to the database driver where it belongs. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 6 of 17. You query a database for a user record and get a raw string back. Every single time, you write the same boilerplate code to parse that string into a dictionary before you can actually use the data. You can teach your database driver to speak your application data format natively, saving you from manual parsing on every query. This is done using Custom Type Codecs. When you query PostgreSQL using the driver, basic types like integers and text are translated automatically. But when you use rich database types like JSON, the driver needs to know how you want that data represented in Python. A custom type codec acts as a translation layer. It sits between the database connection and your application logic. To configure this, you use a method called set type codec. You call this method directly on an active connection object. It requires four main pieces of information. First, you provide the name of the database type, such as the string jsonb. Second, you specify the schema where this type lives. For built-in PostgreSQL types, this is the pg catalog schema. Next, you provide the translation logic by passing an encoder function and a decoder function. The encoder defines how Python sends data to the database. It takes your Python object and returns a format PostgreSQL understands, typically a string. If you are working with JSON, you can simply pass the standard library json dumps function. The decoder defines how data comes back from the database. It receives the raw string from PostgreSQL and returns your desired Python object. For JSON, you just pass the json loads function. Consider a system storing unstructured user preferences. In your database, the preferences column is defined as jsonb. In your Python application, you handle preferences as a standard dictionary. Once your codec is configured, you execute a basic select query for a user. The preferences column arrives in your application already structured as a Python dictionary. When you run an insert query, you pass a dictionary directly as a query argument. The driver automatically triggers your encoder, converts the dictionary to JSON, and sends it to the database. You never call an encoder or decoder manually in your query execution code. Here is the key insight. Custom type codecs do not apply globally to the entire database driver. The set type codec method only modifies the specific connection it is called on. If you configure a codec on one connection, a second connection will know nothing about it and will return raw strings again. This behavior frequently causes problems when developers introduce a connection pool. You cannot configure a pool with a single codec method call. Instead, you must register your custom codec every time a new connection is established. You achieve this by defining an initialization function. Inside that function, you accept the new connection object and call set type codec on it. You then pass this initialization function to your pool creation logic. The pool runs your function automatically whenever it opens a fresh connection, guaranteeing your codecs are always present and active. Pushing data serialization down into the driver layer through custom type codecs removes repetitive parsing logic and ensures your data formats remain perfectly synchronized across your entire application. Thanks for listening, happy coding everyone!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 7 of 17. Handling geographic coordinates usually means messy string parsing. You write a query, get back a giant text string of numbers, and then burn CPU cycles tearing it apart just to find a latitude and longitude. Advanced Codecs with PostGIS inside asyncpg completely resolves this friction. When you pull custom types from PostgreSQL, you are dealing with codecs. A codec tells asyncpg how to translate a PostgreSQL data type into a Python object. There is often confusion here between the text format and the binary format. Text format is the default for many database tools. With PostGIS, a text query returns Well-Known Text. This looks like the word POINT followed by the coordinates inside parentheses. It is human-readable, but reading it in code requires allocating strings, searching for parentheses, and casting characters to floating-point numbers. Parsing text is slow, and it scales poorly when you are processing thousands of rows. You want the binary format. PostGIS uses a standard called Well-Known Binary. When you configure your codec in asyncpg, you explicitly set the format argument to binary. The database skips the text generation and hands over raw bytes. Now, you need a way to translate those bytes into something your Python application can actually use. This is where a Python library like Shapely comes in. Shapely handles complex geometry, and it already knows exactly how to read Well-Known Binary. You tell asyncpg to use a custom type codec by calling the set type codec method directly on your database connection. You specify the PostgreSQL type name, which is geometry. Then you provide an encoder function and a decoder function. The decoder takes the raw byte string from PostgreSQL and passes it directly to Shapely's binary reader. Think about querying the location of the Empire State Building. Without a custom binary codec, your database returns a string, your application parses it, builds a dictionary, and eventually creates a geometry object. With the binary codec in place, you execute a standard select statement. Asyncpg intercepts the binary data, runs your decoder function, and hands you a fully formed Shapely Point object instantly. You can immediately access the x and y coordinates on the returned object. The process works in reverse for data going back into the database. Your encoder function prepares Python data to be sent to PostgreSQL. Shapely objects implement a standard called the geo interface. This is a common Python dictionary structure used for geometry. Your encoder takes any Python object that supports this interface, uses Shapely to serialize it as Well-Known Binary, and sends those raw bytes back to the database. You never touch a text representation. If you are finding these deep dives useful, you can support the show by searching for DevStoriesEU on Patreon. Here is the key insight. By strictly using the binary format for custom type codecs, you eliminate the serialization bottleneck, allowing your database and your Python application to communicate at memory speed. Thanks for listening, happy coding everyone!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 8 of 17. You execute an update query, then your script crashes before the next query runs. You check the database expecting nothing to have changed, but the first update is sitting right there, permanently saved. In asyncpg, if you do not explicitly ask for a transaction, every single query is committed the millisecond it finishes. Managing Transactions is how you fix this behavior. By default, asyncpg operates in auto-commit mode. This means if you write code that executes three queries one after the other, you are not running one block of logic. You are running three completely isolated operations. If the second query fails, the first query is already finalized in the database. Missing an explicit transaction block is a frequent cause of corrupted application state. Take a scenario where you are transferring money between two bank accounts. You have to deduct funds from account A, and then add those exact funds to account B. Both updates must succeed together, or both must fail together. If the deduction succeeds but the addition fails, the money just vanishes. To bind these operations together, you use the transaction method on your connection object. This method returns an asynchronous context manager. In your code, you write async with connection dot transaction, followed by a colon, and then you indent your related queries. When Python enters this block, asyncpg tells PostgreSQL to start a new transaction. Inside the block, you execute the deduction query, followed by the addition query. If both queries run without issue and Python reaches the bottom of the block, asyncpg automatically issues a commit command. The changes to both accounts become visible to the rest of the database at the exact same moment. Here is the key insight. If any problem occurs inside that block, the database remains safe. The problem could be a database constraint violation, a network timeout, or even a pure Python error like a missing variable or a division by zero. If an exception gets raised, the context manager intercepts it. It automatically sends a rollback command to PostgreSQL, erasing the deduction from account A, and then lets the Python exception continue bubbling up your call stack. You can also nest these blocks safely. If you open a new transaction context manager while you are already inside an active transaction block, asyncpg does not get confused. Instead, it automatically creates a database savepoint. A savepoint acts like a bookmark within an ongoing transaction. If the inner block hits an error, it rolls back the database state only as far as that bookmark. The outer block remains completely intact and can still commit its own work, or choose to fail based on your logic. You do not have to write manual savepoint commands, you just nest your async with blocks. Ultimately, the transaction context manager permanently binds your database state to your Python execution state, ensuring that an unhandled Python exception is an absolute guarantee against partial database updates. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 9 of 17. Opening a new database connection for every incoming web request is a highly effective way to completely crash your server. The network overhead alone will stall your application, and you will quickly exhaust the connection limit on your database. The solution to this is Connection Pooling. When you use a library like asyncpg to talk to PostgreSQL, establishing a bare connection is an expensive operation. It requires a TCP handshake, secure negotiation, and database authentication. If you run a high-traffic web service, you simply cannot afford to pay this latency tax on every single HTTP request. Instead, you need to maintain a steady collection of ready-to-use connections. In asyncpg, you achieve this using the create pool function. Typically, you call this function once during your application startup phase. You provide your database credentials, host, and port, and asyncpg spins up a set of idle connections in the background. From that point on, your route handlers and background tasks never create a new connection from scratch. They only borrow existing ones. There is a common trap here that trips up a lot of developers. Do not confuse borrowing a connection with opening a database transaction. They are entirely separate operations. When you borrow a connection from the pool, you are only reserving the network socket for your exclusive, temporary use. If your operation requires atomicity across multiple queries, you still have to explicitly start a transaction on that specific borrowed connection. Think about a high-traffic FastAPI or aiohttp web service. Let us say you have an endpoint that accepts an integer, queries the database to calculate the power of two for that number, and returns the result. When a request hits your endpoint, you call the acquire method on your pool object. You do this using an asynchronous context manager. This temporarily checks out one connection from the pool. You then use that specific connection instance to execute your database query to calculate the power of two. Once the code block finishes, the context manager automatically releases the connection. It wipes its state clean and hands it back to the pool, making it immediately available for the next incoming HTTP request. If a sudden spike in traffic hits your web server and all the connections in the pool are currently checked out, the next request does not crash. It waits. The acquire call will simply pause execution until another request finishes and returns its connection. Here is the key insight. The connection pool does not just save time on network handshakes. It acts as a strict, reliable concurrency throttle. It protects your PostgreSQL database from being overwhelmed by an unexpected flood of traffic. If you configure your pool to hold exactly twenty connections, the database will never see more than twenty concurrent active queries from that application instance, no matter how many thousands of simultaneous requests hit your web server. Thanks for tuning in. Until next time!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 10 of 17. Your application runs perfectly on your local machine. But the moment you deploy it to production behind a database connection router, you start seeing random crashes. The logs complain about statements that already exist, or statements that cannot be found. Nothing in your code changed. This happens because of a built-in optimization called Prepared Statement Caching. To understand the error, we first need to look at what asyncpg does behind the scenes. Every time you send a query through asyncpg, the library automatically translates it into a prepared statement on the PostgreSQL server. Normally, when a database receives a query, it has to parse the text, analyze the syntax, and build an execution plan. This takes time. By preparing the statement, PostgreSQL does this heavy lifting exactly once and assigns it an internal name. For all future executions of that exact query, asyncpg only sends the new parameters and the statement name. This skips the parsing phase entirely and provides a massive performance boost. asyncpg keeps a cache of these prepared statements in memory, tightly bound to the active database connection. Here is the key insight. The problem arises from a conflict between how asyncpg manages its internal connections, and how external poolers like PgBouncer operate. asyncpg assumes it has a dedicated, persistent physical connection to the Postgres server. When it creates a prepared statement, it trusts that the statement will remain available on that exact connection until the connection closes. Now introduce PgBouncer into the architecture, specifically running in transaction mode. PgBouncer sits between your application and the database. It maintains a small pool of actual Postgres connections and shares them among thousands of incoming client requests. In transaction mode, PgBouncer gives your application a physical database connection only for the duration of a single transaction. The moment that transaction commits, PgBouncer takes the physical connection back and hands it to a completely different client. This breaks the prepared statement cache. Your application sends a query. asyncpg prepares it and caches it on physical connection A. The transaction ends. A few seconds later, your application sends the exact same query. asyncpg remembers it already prepared this query, so it tells Postgres to execute the saved statement. But this time, PgBouncer routed your application to physical connection B. Connection B has no record of that prepared statement. The database throws an error saying the statement does not exist. The reverse is also true. A different client might get routed to connection A, try to prepare a statement with the same internal name, and trigger an error saying the statement already exists. The fix is simple but requires a trade-off. You must tell asyncpg to disable this optimization. When you initialize your asyncpg connection or connection pool, you pass a specific argument setting the statement cache size to zero. This completely turns off automatic prepared statement caching. Your queries will now be parsed by PostgreSQL every single time they run. You sacrifice a small amount of parsing performance, but your application will instantly become stable across distributed connections. If your database connections are being routed dynamically per transaction, your application can no longer assume that the database server remembers anything at all between queries. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 11 of 17. You write a standard SQL query to filter records. You pass a list of values into your query parameters, just like you have done in dozens of other database libraries. Postgres immediately throws a syntax error. The issue is not your data. The problem is how you handle Postgres Arrays and IN Clauses. This is the number one syntax error for developers migrating to asyncpg from other database interfaces. In many older libraries, the driver intercepts your query string. If you pass a Python list to an IN clause, the library rewrites the SQL string on the fly. It expands your list into a comma-separated string of individual parameters before sending it to the database. Asyncpg does not do this. It relies entirely on native Postgres server-side prepared statements. Here is the key insight. In standard Postgres SQL, the IN operator requires a comma-separated list of scalar values wrapped in parentheses. It does not accept a single array object. When you pass a Python list to asyncpg as a parameter, asyncpg maps that list directly to a native Postgres array. Your query ends up evaluating as an expression IN an array object, which is invalid syntax. Postgres expects an expression IN value one, value two. To fix this, you must stop using the IN operator for parameterized lists. Instead, use the Postgres any function. The logic changes from asking if a value is IN a list, to asking if a value equals any item inside an array. The any operator is designed specifically to work with Postgres array types. It evaluates the value on the left, checks the array on the right, and returns true if it finds a match. You also need to tell Postgres what kind of array it is receiving by casting the parameter. If you expect an array of text strings, you cast your parameter explicitly to a text array. This explicit type casting guarantees that Postgres knows exactly how to plan and execute the query without guessing the underlying data type of the incoming binary stream. Consider a scenario where you are filtering a list of products. You want to match the product category against a dynamically provided list of user-selected categories. You write a query to select products where the category equals any parameter one, and you cast parameter one to a text array. In your Python code, you call your database fetch method. You pass the query string as the first argument, and your Python list of strings—like electronics and books—as the second argument. Asyncpg packages your Python list into a binary Postgres text array and sends it over the wire as a single parameter. Postgres receives the query, sees the text array, and efficiently matches the categories using the any function. This approach is strictly better than string manipulation. Because the query structure never changes regardless of how many categories are in the list, Postgres parses and plans the statement exactly once. The database caches the query plan, saving execution time on subsequent calls. You also eliminate the risk of SQL injection, since the data is transmitted in binary form, completely separate from the query text. If you pass a Python list to a database parameter, treat it as a native array, cast it to the correct type, and evaluate it with the any function. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 12 of 17. You query your database, get a row back, and instinctively type the variable name dot id. Immediately, Python throws an attribute error. Your data is there, but you cannot access it the way you expect. This happens because of the deliberate design behind Record Objects vs Named Tuples. When you fetch data using asyncpg, it does not return standard Python dictionaries, and it does not return named tuples. Instead, it returns a highly optimized custom object called a Record. If you are used to other database drivers or object-relational mappers, you might expect dot-notation to work out of the box. With an asyncpg Record, it intentionally breaks. You might wonder why the driver does not simply use a standard Python named tuple, since named tuples natively support dot-notation. The reason is pure performance. A named tuple requires Python to generate a brand new class structure for every unique combination of columns returned by a query. If your application executes hundreds of differently shaped queries, generating those dynamic classes creates massive execution overhead. The library is built for absolute speed, so it bypasses that bottleneck completely by returning its own compiled Record type instead. This custom Record object acts like a fast hybrid. It supports integer indexing exactly like a standard tuple, meaning you can access the first column using index zero. But it also provides dictionary-like mapping. You access your columns using bracket notation, passing the column name as a string. Here is the key insight. The creators actively disabled dot-notation on these objects to protect your application from namespace clashes. Think about standard mapping methods in Python, like keys, items, values, or get. If your database table happens to have a column named keys, and the driver supported dot-notation, typing record dot keys would create a structural conflict. Python would not know if you wanted the database value or the built-in method. By enforcing string-based bracket notation, the driver guarantees that your column names will never collide with standard Python attributes. However, if you control your database schema entirely, you know for a fact you do not use reserved Python words as column names, and you require dot-notation for your codebase, you have a way out. You can override the default behavior. When you establish your database connection, you can provide a specific parameter called record class. To implement this, you write a custom class that inherits directly from the base asyncpg Record. Inside this new class, you implement the built-in Python method called double underscore getattr. You instruct this method to take the requested attribute name and simply look it up using the safe bracket notation fallback. Once you pass this custom class to your connection setup, every single row returned by your queries will be an instance of your custom object. Python will then allow you to use dot-notation, seamlessly routing the attribute request through your custom method to fetch the underlying column data. Ultimately, the strict bracket notation in a default Record is not an oversight, but a structural safety boundary ensuring your data access remains predictable no matter how your database schema changes. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 13 of 17. You need to export a one-million-row user table to a file, so you run a standard database fetch. Suddenly, your Python application eats up all available RAM and the server crashes. You cannot pull massive datasets into memory all at once, which is exactly why we use Streaming Results with Cursors. Normally, when you execute a query in asyncpg, the entire result set is pulled across the network from PostgreSQL and held in Python's memory. The database builds the complete response, ships it, and asyncpg constructs Python objects for every single row before your code can process the first one. This is perfectly fine for fifty rows. It is an immediate problem for five million rows. To avoid crashing the server, you need to sip the data rather than gulp it. A database cursor gives you a pointer to the query results on the PostgreSQL server. Instead of pulling everything, the cursor allows your Python application to fetch the data incrementally. In asyncpg, you do this by calling the cursor method on your connection. You provide your SQL query and any required arguments. This method returns an asynchronous iterator. You write an async-for loop to iterate over this cursor. Under the hood, asyncpg automatically fetches rows in small, manageable batches from PostgreSQL. Your code processes a few rows, writes them to your export file, and moves on. Python cleans up the old rows from memory, which keeps your application's total memory footprint completely flat, no matter how large the table is. There is a strict rule here that trips up many developers. If you try to iterate a cursor directly on a standard connection, asyncpg immediately throws an InterfaceError. The error message will state that cursors cannot be used outside of a transaction. Here is the key insight. PostgreSQL cursors are structurally bound to database transactions. When a transaction commits or rolls back, PostgreSQL destroys any active cursors associated with it. By default, asyncpg operates in auto-commit mode. This means every individual query you run is wrapped in its own tiny, invisible transaction that closes the moment the query finishes. If asyncpg allowed you to open a cursor in auto-commit mode, that implicit transaction would end instantly, and PostgreSQL would kill your cursor before you could fetch a single row. To make cursors work, you must explicitly manage the transaction boundary. You do this by opening an asynchronous context manager using the transaction method on your connection. Once you are safely inside that transaction block, you call the cursor method and start your async-for loop. Because the transaction stays open for the entire duration of the context manager block, your cursor stays alive on the PostgreSQL server, allowing you to stream all one million rows securely. There is one rare exception to this rule. PostgreSQL supports a feature where you can declare a raw SQL cursor with the phrase WITH HOLD. This tells the database engine to materialize the result and keep the cursor alive even after the transaction completes. Doing this eats up database resources, and it bypasses the efficiency of standard streaming. For almost all streaming tasks in asyncpg, the explicit transaction block is the required approach. If you find these episodes useful and want to support the show, search for DevStoriesEU on Patreon. Remember that a cursor transforms your database interaction from a massive, risky memory allocation into a controlled, persistent pipeline that can safely process any volume of data. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 14 of 17. You need to move a million rows into or out of your database. If your first instinct is to batch up a massive list of insert statements or fetch all rows into a giant Python list, you are choosing the slowest path available. There is a mechanism built specifically to bypass this overhead. Today, we are talking about Blazing Fast Ingestion with COPY. COPY is a Postgres-specific protocol for bulk data transfer. When you run standard insert or select statements, Postgres has to parse the query, plan it, and execute it. Doing this repeatedly adds massive overhead. The COPY protocol skips the standard query pipeline entirely. It opens a direct stream to the storage layer, moving data in a highly optimized format. Because of this, it is orders of magnitude faster than bulk inserts. In asyncpg, you push data into the database using a method called copy to table. You provide the target table name and a data source. That source can be a local file path, a file-like object, or an asynchronous iterator that yields records. If you point it to a local CSV file, Postgres handles the parsing natively. You do not need to open the file in Python, parse the rows, and map them to variables. The database driver streams the raw file bytes directly to the server. You can also pass a simple Python list of tuples if your data is already in memory, and asyncpg will stream it using the COPY protocol under the hood. Moving data out is just as fast. If you need a full export, you use copy from table. This takes the entire contents of a table and blasts it out to a file or stream. However, dumping an entire table is rarely what you actually need. Usually, you want filtered or joined data. This is where copy from query comes into play. A common misconception is that this method only dumps query results into a static file. That is simply not true. While it can write directly to a file path, you can also provide a callback function. Asyncpg will execute the query and stream the results in chunks to your callback, letting you process a massive dataset on the fly without ever holding the full result set in system memory. Consider a scenario where you need to generate a CSV report of all active users. A standard approach is to execute a select query, fetch a hundred thousand rows into Python, format them using the CSV module, and write them to disk. That consumes significant memory and CPU. Here is the key insight. You can skip the Python processing completely. You call copy from query, pass it your specific select statement, set the format parameter to CSV, and provide an output file path. Postgres executes the query, formats the results into CSV natively on the database server, and asyncpg streams the finished text straight to your hard drive. Your Python application acts as a simple pipe, doing almost zero data manipulation. You should keep using standard insert and select statements for everyday application logic, but the moment raw data volume becomes your bottleneck, switch to the COPY protocol to bypass the SQL parser entirely. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 15 of 17. You reach for a separate message broker like Redis the moment you need real-time events. But if your application already uses a database, you might be adding infrastructure complexity for absolutely no reason. PostgreSQL has a real-time message bus built right in. Today we are talking about Asynchronous Listen and Notify. Postgres supports a publish-subscribe pattern natively using two commands, listen and notify. The asyncpg library exposes this capability in Python through a method called add listener. Instead of writing a loop that polls a database table every few seconds to check for new data, you register an asynchronous Python callback function on a specific named channel. When an event occurs inside Postgres, it broadcasts a message to that channel, and your Python callback executes immediately. Here is the key insight. A listener is not attached to your application globally, and it is not attached to a connection pool. It is bound to one specific, individual database connection. This is a common point of failure. If you pull a connection from a pool, register your listener, and then release the connection back to the pool, the listener drops. To use this feature reliably, you must acquire a connection from asyncpg, call the add listener method on it, and hold that connection open indefinitely. It becomes a dedicated listening pipeline. Let us look at a practical scenario. You have a background worker that needs to wake up and process records whenever a new row is inserted into a jobs table. Instead of polling, you set up a database trigger. Whenever an insert occurs, the trigger executes a notify command on a channel called new jobs. It also sends a short text payload, such as the unique identifier of the new row. In your Python code, you write an asynchronous callback function. This function expects to receive four arguments from asyncpg: the connection object, the Postgres process identifier, the channel name, and the text payload itself. Next, you acquire your dedicated connection. You call add listener on that connection, passing in the channel string new jobs along with your callback function. Finally, you keep the script running, typically by awaiting an asynchronous event that never sets. Your Python process now sits completely idle. It uses almost zero CPU resources and stops hammering the database with empty queries. The moment a transaction commits a new job row, Postgres pushes the notification over the open network socket. Asyncpg reads that socket and immediately schedules your callback on the Python event loop, handing it the new job identifier. The true power of this pattern is transactional consistency. If a database transaction rolls back, any notify commands executed during that transaction are automatically discarded by Postgres. This guarantees your Python workers will only ever wake up and react to data that has successfully been saved to the disk. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 16 of 17. To find out why your database is slow, you do not have to guess. You might rely on server-side logs, but those miss network latency and Python-side overhead. Measuring performance manually around every database call quickly clutters your business logic. The solution is Telemetry and Query Logging. People often confuse log listeners and query loggers. Log listeners catch Postgres server notices. Query loggers catch client-side execution telemetry. They handle two completely distinct streams of information. Query loggers handle the telemetry. You attach a callback function to your connection using the add query logger method. Once attached, every time a query finishes executing, asyncpg automatically passes a LoggedQuery object to this callback. This happens globally for that connection, completely decoupled from the specific function making the database request. The LoggedQuery object contains three critical pieces of data. It holds the exact SQL query text, the arguments passed to that query, and the execution time. The arguments are captured exactly as your application provided them. This saves you from writing manual string formatting logic just to figure out what parameters caused a slow response. Consider a production environment where you need to catch any query taking longer than 500 milliseconds. You define a standard Python function that accepts two parameters: the database connection and the LoggedQuery object. Inside this function, you check the execution time attribute. If the time exceeds zero point five seconds, you write the query text, the arguments, and the exact duration to your application monitoring system. You then pass this callback function to the add query logger method. Now, your application automatically tracks slow queries silently in the background. If you ever need to stop this tracking, you simply pass the same callback function to the remove query logger method. Now, the second piece of this is the log listener. While the query logger handles client-side timing, the log listener handles server-side messaging. Sometimes, Postgres sends messages that are not errors and do not return data rows. These are asynchronous notices, warnings, or custom log messages generated directly by the database engine. To capture these messages, you attach a callback using the add log listener method. When Postgres emits a notice or a warning, asyncpg triggers this callback. It passes the connection and a specific message object to your function. This gives your application immediate visibility into database-level warnings, completely independent of your standard query results. Just like the query logger, you can detach this callback later using remove log listener. Here is the key insight. Client-side query logging gives you the true execution duration experienced by your Python application, completely sidestepping the guesswork between network delay and database processing. Thanks for listening. Take care, everyone.

Hi, this is Alex from DEV STORIES DOT EU. High-Performance Python Async, episode 17 of 17. Connecting to a managed cloud database without SSL is like shouting your database credentials across a crowded room. You need encryption, but setting it up correctly often leads to confusing connection errors or insecure defaults. Today, we are securing connections with SSL in asyncpg. People often get tripped up on how to configure SSL in their connection logic. If you use connection URIs, asyncpg natively parses standard PostgreSQL sslmode query parameters, like setting sslmode to require. That works for basic setups. But when you need precise control—like connecting securely to a managed cloud database using a custom Certificate Authority bundle—standard URI strings fall short. For that, you use the programmatic ssl parameter. The ssl parameter in asyncpg connection functions dictates how TLS is negotiated. It accepts two types of values. The first type is a string preset. You can pass the string prefer, which attempts an SSL connection but falls back to unencrypted if the server does not support it. You can pass require, which forces encryption but skips verifying the server identity. Or you can pass verify-full, which forces encryption and strictly validates the server certificate against trusted roots. Here is the key insight. When your scenario demands a custom Certificate Authority, do not rely on string presets. Instead, you create a standard Python SSLContext object. You configure this object with your custom certificate files, enforce strict verification, and then pass that Python object directly into the asyncpg ssl parameter. This gives you exact control over the cryptographic handshake, bypassing any default system certificates. That covers the encryption rules, but how does the connection actually start? That brings us to the direct TLS parameter. By default, PostgreSQL uses a protocol called STARTTLS. The client makes a plain-text connection, asks the server if it supports encryption, and if the server says yes, they upgrade the connection to TLS. However, modern proxy setups—like certain connection poolers or cloud load balancers—often expect a direct TLS connection from the very first byte. They do not want the plain-text negotiation. If your infrastructure is built this way, you pass true to the direct TLS connection parameter. When you do this, asyncpg skips the STARTTLS negotiation and immediately initiates a raw TLS handshake. Naturally, this only works if you also provide a valid ssl configuration. If you enable direct TLS but leave the ssl parameter empty, the connection will fail. When securing your database connections, remember that while string presets are convenient, passing an explicit SSLContext object is the only way to absolutely guarantee your application trusts the correct server identity. Since this is the final episode of the series, I encourage you to explore the official asyncpg documentation and try these connection parameters hands-on. You can visit devstories dot eu to suggest topics for our future series. That is all for this one. Thanks for listening, and keep building!