Connection pooling maintains reusable network or database connections so multiple operations can share them. Reusing connections avoids the resource and time costs of creating one for every request or query. section 4.2.1 section 5.5.2
A pool provides a bounded workflow:
- Acquire an available connection before running an operation. section 5.5.2
- Run one query per connection; one SQL connection cannot safely process multiple concurrent queries at once. section 5.5.1
- Release the connection after the operation so another task can reuse it. section 5.5.2
- Wait when all finite connections are busy; waiting is part of connection acquisition. section 5.5.2
Concurrent product queries
An e-commerce service can create an asyncpg pool and let each query task use `async with pool.acquire() as connection`. `asyncio.gather` then runs independent product queries concurrently, while the pool limits database concurrency. In the demonstrated comparison, 10,000 queries took about 21.8 seconds sequentially and 4.8 seconds concurrently. section 5.5 section 5.5.2
Listing 5.7 Establishing a connection pool and running queries concurrently
import asyncio
import asyncpg
product_query = \
"""
SELECT
p.product_id,
p.product_name,
p.brand_id,
s.sku_id,
pc.product_color_name,
ps.product_size_name
FROM product as p
JOIN sku as s on s.product_id = p.product_id
JOIN product_color as pc on pc.product_color_id = s.product_color_id
JOIN product_size as ps on ps.product_size_id = s.product_size_id
WHERE p.product_id = 100"""
async def query_product(pool):
async with pool.acquire() as connection:
return await connection.fetchrow(product_query)
async def main():
async with asyncpg.create_pool(host='127.0.0.1',
port=5432,
user='postgres',
password='password',
database='products',
min_size=6,
max_size=6) as pool: #1
await asyncio.gather(query_product(pool),
query_product(pool)) #2
asyncio.run(main())
Use the asyncpg listing to see pool creation, scoped acquisition, query execution, and concurrent task scheduling together. section 5.5.2
