Psycopg2 vs Psycopg3 Performance Benchmark

The new and improved PostgreSQL adapter for Python, psycopg3, brings significant improvements compared to its predecessor, psycopg2. It introduces built-in async support, enhancing efficiency for asynchronous tasks and an improved connection pooling feature. These enhancements make the PostgreSQL database interaction experience smoother for developers.

We commented on the latest version's supercharged performance in a previous blog post about building applications using PostgreSQL and psycopg3. In this blog post, we will actually test and compare the performance of psycopg2 and psycopg3 using various benchmarks. Let’s dive into it. 

Psycopg 3 vs. Psycopg2 Benchmark Configurations

Environment

• Cloud Provider: AWS
• EC2 Instance Type: t2.xlarge
• CPU Configuration: 4 cores
• RAM Allocation: 16 GB
• Hard Drive Capacity: 200 GB
• Hard Drive Performance: 3000 IOPS
• Operating System: Ubuntu 22.04 LTS
• Filesystem: ext4

Framework

• Tool: MagicStack/pgbench
• Pull Request: https://github.com/MagicStack/pgbench/pull/10 

Versions

• PostgreSQL = 16.2
• Psycopg2-binary = 2.9.9
• Psycopg[binary] = 3.1.18

Test Cases and Results

pg_type

A broad query retrieving all entries from the pg_type table (approximately 613 rows), similar to an average application query, aims to evaluate overall data decoding efficiency.

The difference is abysmal, with psycopg3 retrieving approximately half a million rows per second while psycopg2 handles 150,000.

generate_series

A query that generates 1,000 rows containing a single integer. This benchmark is used to test the overhead of creating and returning result records.

Psycopg3 does it again with approximately 1.2 million rows per second, double that of psycopg2. However, the true winner is the asynchronous version, slightly surpassing the 1.2 million mark.

large_object

A query returning 100 rows, each containing a 1 KB binary blob. This benchmark is used to stress the I/O and read buffers in particular and is one of three cases in our tests where psycopg2 fared better.

arrays

A query returns 100 rows, each containing an array of 100 integers. This benchmark is designed to test the performance of array decoding. Even if performing only slightly better, psycopg3 async is once again the winner.

copyfrom

This benchmark is used to measure the performance of copying data from standard input (STDIN) into a PostgreSQL table using the COPY command with specific row data and a large number of iterations (10,000). Psycopg3 async also performed better in this benchmark.

batch

This benchmark assesses the efficiency of batch-inserting data into a PostgreSQL table. We conducted 10,000 iterations to monitor the performance of the batch insertion process, with psycopg2 inserting more rows per second than the latest versions.

oneplusone

This benchmark simply executes the query "SELECT 1+1" to measure the time it takes to perform this basic arithmetic operation. Again, psycopg2 performed better.

Detailed Psycopg Benchmark Data

Here’s the detailed breakdown of results, where we can compare psycopg2 versus psycopg3 versus psycopg3 async.

Next Steps

If you are wondering whether to carry on using psycopg2 in your interactions with your PostgreSQL database, we hope this convinces you to upgrade. We know it’s hard to part ways with a decade-long friend, but the performance gains brought by psycopg3 will make a difference in your development—especially if you're interacting asynchronously with your database.

To learn more about leveraging PostgreSQL and Python, read the following resources:

• How to Work With Time Series in Python?
• Tools for Working With Time-Series Analysis in Python
• Jupyter Notebook Tutorial: Setting Up Python & Jupyter Notebooks on macOS for OpenAI Exploration
• A Python Library for Using PostgreSQL as a Vector Database in AI Applications

Create a free Timescale account today to experiment and start building with Python and our supercharged PostgreSQL.