Nowadays, everyone talks about performance and how to make our applications faster, and one key factor is how we interact with our databases. Sometimes we get overwhelmed by small but important decisions, like choosing the right library to work with our database. Asking ourselves questions like: “Should I use an ORM or a query builder?”, “Should I use a sync or async library?” or “Is this one faster than the other one?”. I know, it’s a lot of questions, but don’t worry, I’m here to help you with one of those questions.

In the Python world, we have a lot of libraries to work with PostgreSQL, but two of the most popular are Psycopg 3 and Asyncpg. In this article, we will compare these two libraries and see which one is better for your project.

Setup

We will use a simple FastAPI (v0.109.2) application with some endpoints that will interact with a PostgreSQL (v16) database running in a Docker container.

For connecting with the database, we will use two different libraries: Psycopg 3 (v3.1.18) and Asyncpg (v0.29.0).

Source code

You can find the source code for this article in the fernandoarteaga/fastapi-postgres-benchmark repository on GitHub. In case you want to run the benchmarks yourself, you can follow the instructions in the repository’s README file.

Benchmarks

To evaluate the performance of the Psycopg 3 and Asyncpg libraries in handling various loads, we performed four different tests. Each test focused on reading a different number of rows (50, 250, 100, 500), and comparing the performance between Psycopg sync, Psycopg async and Asyncpg.

• Test 1: Read 50 users from the DB
• Test 2: Read 250 users from the DB
• Test 3: Read 1000 users from the DB
• Test 4: Read 5000 users from the DB

The tests were performed using the wrk tool, with the same number of requests, threads and duration for each test:

• Threads: 12
• Connections: 400 (total number of HTTP connections to keep open with each thread handling N = connections/threads)
• Duration: 2m

Test 1: Read 50 users from the DB

🥉psycopg sync connector

Parameters:

• Endpoint: /psycopg/sync
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/sync

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	304.00ms	247.62ms	967.04ms	76.47%
Req/Sec	80.18	74.86	333.00	76.60%

• 93656 requests in 2m, 126.21MB read
• Socket errors: connect 158, read 112, write 0, timeout 0
• Requests/sec: 779.80
• Transfer/sec: 1.05MB

🥈psycopg async connector

Parameters:

• Endpoint: /psycopg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	88.11ms	8.72ms	216.74ms	90.14%
Req/Sec	225.99	83.09	484.00	69.62%

• 324102 requests in 2m, 436.74MB read
• Socket errors: connect 158, read 106, write 0, timeout 0
• Requests/sec: 2698.62
• Transfer/sec: 3.64MB

🥇asyncpg async connector

Parameters:

• Endpoint: asyncpg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/asyncpg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	74.26ms	41.51ms	612.29ms	74.61%
Req/Sec	269.64	91.41	565.00	62.15%

• 386681 requests in 2m, 521.07MB read
• Socket errors: connect 158, read 106, write 0, timeout 0
• Requests/sec: 3219.51
• Transfer/sec: 4.34MB

Results Summary

Test 2: Read 250 users from the DB

🥉psycopg sync connector

Parameters:

• Endpoint: /psycopg/sync
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/sync

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	748.75ms	205.02ms	1.04s	67.27%
Req/Sec	28.07	18.80	150.00	76.28%

• 38006 requests in 2m, 236.68MB read
• Socket errors: connect 158, read 122, write 0, timeout 0
• Requests/sec: 316.47
• Transfer/sec: 1.97MB

🥈psycopg async connector

Parameters:

• Endpoint: /psycopg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	163.90ms	7.67ms	279.98ms	89.62%
Req/Sec	121.35	36.60	230.00	64.66%

• 174168 requests in 2m, 1.06GB read
• Socket errors: connect 158, read 102, write 0, timeout 0
• Requests/sec: 1450.35
• Transfer/sec: 9.03MB

🥇asyncpg async connector

Parameters:

• Endpoint: asyncpg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/asyncpg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	155.66ms	88.66ms	1.23s	74.67%
Req/Sec	128.67	59.72	333.00	65.48%

• 184649 requests in 2m, 1.12GB read
• Socket errors: connect 158, read 106, write 0, timeout 0
• Requests/sec: 1537.50
• Transfer/sec: 9.57MB

Results Summary

Test 3: Read 1000 users from the DB

🥉psycopg sync connector

Parameters:

• Endpoint: /psycopg/sync
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/sync

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	925.00ms	73.91ms	1.22s	72.15%
Req/Sec	28.07	18.80	150.00	76.28%

• 30764 requests in 2m, 755.09MB read
• Socket errors: connect 158, read 124, write 0, timeout 0
• Requests/sec: 256.15
• Transfer/sec: 6.29MB

🥇psycopg async connector

Parameters:

• Endpoint: /psycopg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	517.43ms	35.68ms	705.28ms	74.58%
Req/Sec	46.16	33.04	161.00	60.40%

• 55102 requests in 2m, 1.32GB read
• Socket errors: connect 158, read 98, write 0, timeout 0
• Requests/sec: 458.79
• Transfer/sec: 11.26MB

🥈asyncpg async connector

Parameters:

• Endpoint: asyncpg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/asyncpg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	516.99ms	277.76ms	2.00s	73.42%
Req/Sec	39.04	21.50	150.00	75.20%

• 55070 requests in 2m, 1.32GB read
• Socket errors: connect 158, read 103, write 0, timeout 27
• Requests/sec: 458.63
• Transfer/sec: 11.26MB

Results Summary

Test 4: Read 5000 users from the DB

🥉psycopg sync connector

Parameters:

• Endpoint: /psycopg/sync
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/sync

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	1.05s	534.39ms	1.97s	60.14%
Req/Sec	8.01	5.95	50.00	79.42%

• 8716 requests in 2m, 1.04GB read
• Socket errors: connect 158, read 133, write 0, timeout 8578
• Requests/sec: 72.57
• Transfer/sec: 8.87MB

🥇psycopg async connector

Parameters:

• Endpoint: /psycopg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/psycopg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	1.08s	575.88ms	2.00s	57.81%
Req/Sec	11.19	6.59	50.00	60.92%

• 13263 requests in 2m, 1.58GB read
• Socket errors: 158, read 104, write 0, timeout 13026
• Requests/sec: 110.44
• Transfer/sec: 13.50MB

🥈asyncpg async connector

Parameters:

• Endpoint: asyncpg/async
• CLI command: wrk -t12 -c400 -d2m http://localhost:7755/asyncpg/async

Results:

Thread Stats	Avg	Stdev	Max	+/- Stdev
Latency	212.79ms	344.99ms	1.95s	91.28%
Req/Sec	11.42	8.74	80.00	82.07%

• 11376 requests in 2m, 1.36GB read
• Socket errors: connect 158, read 109, write 0, timeout 9851
• Requests/sec: 94.74
• Transfer/sec: 11.58MB

Results Summary

Conclusions

In this benchmark study, we sought to unravel the performance characteristics of two prominent Python libraries, Psycopg 3 and Asyncpg, when interacting with a PostgreSQL database in the context of a FastAPI application. Through a series of tests involving the retrieval of 50, 250, 1000, and 5000 rows from the database, we gained valuable insights into the behaviors of both synchronous and asynchronous connectors.

Psycopg sync connector

The synchronous implementation of Psycopg 3 exhibited commendable performance across all test scenarios. However, as the volume of rows to be retrieved increased, a gradual increase in latency was observed. This indicates that while Psycopg’s synchronous connector is robust, it may face challenges in handling larger datasets efficiently.

Psycopg async connector

The asynchronous counterpart of Psycopg 3 showcased improved responsiveness, particularly evident in scenarios with larger datasets. The reduction in latency and notable increase in requests per second make the asynchronous implementation a favorable choice for applications requiring enhanced scalability and responsiveness during database interactions. Async Psycopg demonstrates a tendency to better handle a larger number of rows, showcasing its potential for scenarios with increased data loads.

Asyncpg async connector

Asyncpg, a dedicated asynchronous library, emerged as the top performer in this benchmark. It consistently demonstrated low latency and high-request throughput across all test cases. Its asynchronous nature proved especially advantageous when dealing with increased data loads, solidifying its position as an optimal choice for projects demanding peak performance in asynchronous database operations.

Key Takeaways

1. Scalability Matters: As the dataset size grows, the choice of an asynchronous connector becomes increasingly critical. Asynchronous implementation shines in scenarios with larger datasets, offering superior performance compared to synchronous alternatives.
2. Application-Specific Considerations: The selection between Psycopg and Asyncpg should be guided by the specific requirements of your application. While Psycopg, both synchronous and asynchronous, presents robust performance, Asyncpg stands out when it comes to handling increased concurrency and larger data volumes.
3. Async Psycopg for Larger Datasets: The benchmark results suggest that Async Psycopg exhibits a tendency to better handle a larger number of rows, making it a promising choice for scenarios with extensive data retrieval requirements.
4. Paginate Database Queries: To avoid potential scenarios where a single request could block the entire database, it is advisable to implement pagination for database queries. This ensures efficient data retrieval without risking performance bottlenecks.

In conclusion, the choice between Psycopg 3 and Asyncpg should be tailored to the unique demands of your project. By aligning your selection with the scale, concurrency, and responsiveness requirements of your application and implementing best practices like query pagination, you can ensure optimal performance in database interactions, setting the foundation for a fast and efficient FastAPI application.